viernes, diciembre 06, 2013

Generar hojas a partir del filtro de una tabla dinámica de Excel.

Excel tiene muchas funcionalidades que hacen la vida del usuario más fácil; pero no todas son evidentes. Parafraseando a Antoine de Saint-Exupery, podríamos decir que "mucho de lo útil en Excel (lo esencial) es invisible a los ojos".

En este caso nuestro "principito" resultó ser José María Murillo que respondiendo a una consulta en el foro de Excel en castellano en Linkedin, menciona la funcionalidad "mostrar páginas de filtros de informes" en tablas dinámicas.

La situación es la siguiente: tenemos una base de datos con las ventas del año por cliente y por agente de ventas. Creamos una tabla dinámica para resumir las ventas; en el área de filtro usamos el campo "Agente de Ventas" para generar un informe para cada vendedor

tabla dinamica con filtro



Si queremos imprimir un informe para cada vendedor, tendríamos que seleccionar el vendedor, imprimir el informe, seleccionar el próximo vendedor, imprimir el informe y así sucesivamente hasta el último.

En lugar de esta tarea sisífica, podemos usar la funcionalidad "Mostrar páginas…" que se oculta en Herramientas-Analizar-Tabla Dinámica-Opciones (en Opciones hay que asegurarse de hacer clic en el triángulo invertido a la derecha de la opción)

hojas de una tabla dinamica


Al seleccionar la opción "Mostrar…" se abre una ventanilla con los campos del filtro (en nuestro ejemplo hay sólo uno)

elegir valor del filtro


Al seleccionar el campo, Excel crea automáticamente una hoja para cada valor (agente de ventas en nuestro ejemplo) en el campo del filtro



Ahora podemos imprimir todas las hojas de los agentes en una única operación.

Si queremos enviar los informes por correo electrónico debemos recordar que cada informe refleja todos los datos de la base de datos y el agente puede cambiar el valor del filtro para "espiar" las ventas de sus colegas.

29 comentarios:

  1. Jorge, te ha quedado "principesco", esta clarisimo (por lo menos para mi). Saludos
    Jose Mª Murillo

    ResponderBorrar
  2. Perfecto.......aprovecho para preguntar si con excell se pueden combinar correspondencia como lo hace word

    ResponderBorrar
  3. El mecanismo de combinar correspondencia existe en Word. Puede usar Excel como fuente para los datos, pero no existe un mecanismo de ese tipo de Excel.
    Si te refieres a enviar hojas o cuadernos electrónicas desde Excel, , te recomiendo ver esta página de Ron de Bruin.

    ResponderBorrar
  4. Hola Jorge

    ¿Qué significa cuando en una tabla dinámica no se activa esta opción?. ¿hay alguna condición especial? ¿Se requiere una estructura específica?

    Gracias

    ResponderBorrar
  5. Hola María Luisa,
    La función se activa sólo si se han definido filtros en el informe.

    ResponderBorrar
  6. Buenas tardes,
    tengo una hoja excel con datos de texto que no quiero calcular...es posible aprovechar la tabla dinámica para los informes o bien todos los campos deben ser calculados?

    Por ejemplo:

    Fecha Tanque Temperatura
    21/02/2014 5 20
    22/02/2014 7 31


    Si hago una tabla dinámica Tanque y Temperatura deberian ser campos calculados y yo únicamente quiero mostrarlos y aprovecharme de la agilidad de la tabla dinámica.

    Gracias

    Saludos

    Laura

    ResponderBorrar
  7. Si, se puede hacer no incluyendo valores en el área de los valores (supongo que con campor calculados te refieres a los valores que aparecen en el área de los valores).

    ResponderBorrar
  8. Buenos días:
    Tengo una hoja Excel con 7 columnas:
    Provincia Factura Cuenta Fecha Liquido Combustible Litros

    Lo que quiero hacer es crear una hoja Excel diferente cada vez que me cambia el campo cuenta.
    Gracias

    ResponderBorrar
  9. Tal como se explica en la nota. Mantener una hoja con todos los datos, crear una tabla dinámica con el campo Cuenta como filtro de la tabla y activar la opción Mostrar páginas de filtro... cada vez que se requiera.

    ResponderBorrar
  10. Y si tienes un grafico dinámico puedes hacer que salga en todas las páginas, a mi no me sale el gráfico

    ResponderBorrar
  11. No solamente a vos, la funcionalidad no incluye copiar los gráficos dinámicos, solo las hojas.
    Habría que echar mano a Vba (macros), para lograrlo.

    ResponderBorrar
  12. Si la opción "Mostrar paginas de filtro de Informes" esta deshabilitada..¿Por donde o como la activo".....de antemano Gracias

    ResponderBorrar
  13. Hola John, ¿has definido filtro en el informe dinámico? Si no se han definido filtros, la funcionalidad no estará activa.

    ResponderBorrar
  14. Jorge una consulta, cuando quiero imprimir los reportes, me repite en cada reporte de agente la primera fila de datos del primer agente de la lista que esta bajo el encabezado, porque se da este error y que puedo hacer para eliminarlo

    ResponderBorrar
  15. Tendría que ver tu cuaderno para hacerme una idea del problema. En principio no debería suceder.

    ResponderBorrar
  16. Hola Jorge, ante todo muchas gracias por la gran variedad de soluciones que tan gentilmente compartís en tu blog como usuario desde hace años.
    Ahora la consulta, necesitaría hacer exactamente lo que explicás en este post, con una diferencia: necesitaría separar en hojas pero no para imprimir, sino para compartir cada hoja a un interesado diferente. La dificultad está en que cada destinatario de cada hoja, no debería tener la posibilidad de ver la información de los "otros vendedores" al seleccionarlos en el filtro. Por si no me expliqué lo intento con un ejemplo:Separo en una hoja para José, otra para Roberto y otra para Juan. Le envío a cada uno la suya. José no debería poder ver la información de ventas que corresponde a Roberto ni a Juan.
    Será esto posible?
    Muchas Gracias.
    Cesar.

    ResponderBorrar
  17. Se puede, con un poco de trabajo manual o automatizándolo con una macro. Una vez creadas las hojas, hay que desconectar la tabla dinámica de la base de datos. Para hacerlo hay que seleccionar todas la hoja y realizar Copiar-Pegar Valores.
    Para conservar los formatos al pegar puedes usar la técnica que muestro en este post.

    ResponderBorrar
  18. y si yo agrego un formato adicional en la tabla dinamica como hago para mantenerlo cuando haga la generacion de filtro de informes

    ResponderBorrar
  19. Lashojas generadas mantienen los formatos de la tabla. Asegurate que la opcion "conservar formatos" en las definiciones de la tabla esté señalada.

    ResponderBorrar
  20. y se pueden sacar con gráficos dinámicos también?
    Diego

    ResponderBorrar
  21. Excelente post.

    Una consulta, es posible que en vez de que las hojas se generen en el mismo libro, estas se graben en diferentes archivos? Pongo un ejemplo, si tengo las ventas de Juan, Pedro y Jose podrían generarse tres archivos excel diferentes? (hago la consulta dado que hablo no de generar tres archivos sino alrededor de 70, y este proceso manual puede resultar engorroso). Gracias!

    Félix

    ResponderBorrar
  22. Hola Félix, tendrías que usar una macro. Ésta empezaría por generar las hojas y luego usando un loop convertirlas en archivos independientes.
    Si necesitas ayuda con la macro, ponte en contacto conmigo por mail privado (fijate en el enlace Aydua, en la parte superior del blog)-

    ResponderBorrar
  23. en excel 2016 donde encuentro esta opcion

    ResponderBorrar
  24. Exactamente en el mismo lugar. Pero tiene que haber una tabla dinámica activa para que el menú aparezca.

    ResponderBorrar
  25. Buenas tardes y muchas gracias por el aporte.

    Comentas que después se puede imprimir todas las hojas de los agentes en una única operación.

    Quiero que sea cada hoja de forma independiente en un pdf ¿como podría hacerlo?

    Es mas, o si es posible, directamente guardar cada hoja en un pdf sin necesidad de que se genere otra hoja de calculo.

    Gracias anticipadas
    Rosa

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.