martes, agosto 07, 2007

Tablas dinámicas en lugar de Autofiltro.

Uno de mis lectores me pregunta si se puede aplicar autofiltro simultáneamente a varias hojas. En sus propias palabras:

¿se puede aplicar autofiltro a varias hojas de una sola vez? Tengo 25 hojas y
quiero filtrar en todas desde una fecha determinada. ej: desde ej 01/06/2007.

La respuesta es no, no se puede. Pero podemos usar tablas dinámicas para lograr efectos parecidos a los de Autofiltro. Para esto usaremos tablas dinámicas con la opción "rangos de consolidación múltiples".

Supongamos un cuaderno Excel (que se puede filtro multiples hojasdescargar aquí) con listas de datos compuestas de fechas e importes, en tres hojas. Nuestro objetivo en consolidar los datos en una tabla y mostrar los datos de determinadas fechas.

Construimos la tabla dinámica con rangos de consolidación múltiples








Agregamos las listas de las distintas hojas



Y ponemos la tablas en una hoja aparte (Reporte)



Obtenemos



Ahora empezamos a mejorar el resultado. Eliminamos el elemento Columna arrastrándolo fuera de la tabla y movemos el elemento Página1 a la zona de campos de columna



Reemplazamos los rótulos de la tabla por otros más significativos



Para filtrar los datos según fechas abrimos el menú de ítems del campo



Y seleccionamos los elementos deseados



Este método es muy efectivo si queremos seleccionar sólo una fecha o un rango reducido. Si tenemos muchos elementos (fechas) en nuestras listas el método puede ser muy trabajoso.


Technorati Tags:

9 comentarios:

  1. Hola, antes que nada muchas gracias por la valiosa información.
    Me gustaría consultar acerca de la protección de una hoja con una tabla dinámica, mi problema es que al proteger la hoja no se activa el botón de actualización de la tabla dinámica sólo quedan algunos botones activos pero no lo más importante que es la actualización. ¿Cómo puedo solucionar éste problema?

    GraciasHola, antes que nada muchas gracias por la valiosa información.
    Me gustaría consultar acerca de la protección de una hoja con una tabla dinámica, mi problema es que al proteger la hoja no se activa el botón de actualización de la tabla dinámica sólo quedan algunos botones activos pero no lo más importante que es la actualización. ¿Cómo puedo solucionar éste problema?

    Gracias

    ResponderBorrar
  2. Hola
    depende con cual versión de Excel estés trabajando. Desde la versión Xp, Excel tiene doce niveles de seguridad en el menú de protección (Herramientas--Proteger). Tienes que asegurarte que la opción "usar informes tablas dinámicas" esté señalada.
    Si trabajas con alguna versión anterior tendrías que usar una macro.

    ResponderBorrar
  3. Gracias por contestar.Tengo la version xp y al bloquear la hoja,hay 15 comandos de distintos tipos de protección, activo el boton de usar tablas dinamicas e igualmente no se activa el botón de actualización, ¿ es posible que me muestres algún ejemplo de protección que permita ver el botón de actualización con una macro?, te lo agradecería mucho

    muchas gracias

    ResponderBorrar
  4. Hola Jorge,
    En primer lugar quisiera felicitarte por la asombrosa facilidad que tienes para enfrentarte a los retos en Excel y lo que es más complicado: explicarlo de manera sencilla. Lo que no acabo de entender es porqué no trabajas en Microsoft. Le ahorrarías mucho dinero en programadores al Sr. Gates.
    Bueno, mi pregunta está relacionada con las tablas dinámicas. Las uso bastante para analizar ventas, P&L, balance sheet,…. pero siempre me encuentro un problema con los acumulados del año (YTD). Mi solución es calcular en otra hoja esos acumulados y vincular la tabla a ese nuevo archivo. Habría alguna manera más sencilla de hacerlo?. Te pongo un ejemplo de la estructura que uso:
    Periodo Ref venta
    200801 A 100
    200801 B 200
    200802 A 300
    200802 B 200
    200803 C 50

    En otra hoja calculo el YTD y la vinculo de nuevo a la tabla dinamica..
    Periodo Ref ventaYTD
    200801 A 100
    200801 B 200
    200802 A 400
    200802 B 400
    200803 C 50
    Pero creo que este proceso es demasiado complicado. ¿Habría alguna alternativa.?
    Muchas gracias.
    Jim

    ResponderBorrar
  5. Hola Jim

    si tienes todos los datos en una hoja sobre la que construyes la tabla dinámica, y tienes un campo con fechas, no hay ninguna necesidad de crear una hoja auxiliar. Tienes que usar la opción Agrupar como indico en la nota sobre agrupar datos en tablas dinámicas.

    ResponderBorrar
  6. Hola como estan???
    quiero saber si es posible relacionar filtros de pagina en una tabla dinamica, es decir que al seleccionar una opcion en el primer filtro de pagina en el otro solo me aparezcan las que entrarian dentro del primer cirterio establecido.
    ej tengo dos filtros de pag
    mes y cliente
    lo que quiero es que al seleccionar un mes en el segundo solo me aparezcan como opcion los clientes que compraron en dicho mes

    ResponderBorrar
  7. Mónica

    si tienes Excel 2010 puedes usar segmentación, como muestro en esta nota.
    En versiones anteriores habría que buscar alguna solucipon con Vba.

    ResponderBorrar
  8. muy bueno blog sobre excel esta super sencillo la explicacion, mi consulta sobre este post si solo se puede hacer "rangos de consolidación múltiples".
    con solo 2 info, en el ejemplo que muestras es fecha e importe,si yo quiero por ejemplo me muestre fecha, marca y cantidad que sume enero y febrero? lo intente pero no me resulto como queria.
    muchas gracias.

    ResponderBorrar
  9. El método de rangos de consolidación múltiple no es muy flexible y en general conviene evitarlo.
    Por ejemplo, podrías consolidar todos tus datos en una única tabla y a partir de ella crear el reporte dinámico.

    ResponderBorrar

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