jueves, enero 26, 2012

Sincronización de tablas dinámicas en Excel

En el foro EEE (Expertos en Excel en Español), Julio consultaba: “Quiero vincular tres tablas distintas en el mismo archivo, de forma que cuando filtre por mes, se me cambien todos los datos de todas las tablas a la vez”.

Con Excel 2010 podemos hacerlo con facilidad usando la nueva funcionalidad “Segmentación de Datos”. Pero si usamos versiones anteriores de Excel, tendremos que usar macros o, más precisamente, programar un evento.

Veamos el siguiente ejemplo (que se puede descargar aquí): en la hoja “bd” tenemos datos de la facturación de una empresa,



en la hoja “reporte” hemos construido tres tablas dinámicas



La primer tabla a la izquierda muestra las ventas por país, la del centro resume los datos por vendedor y la tabla a la derecha muestra los 10 clientes principales. Las tres tablas están filtradas por año (los datos en “bd” aparecen por fecha, por eso usamos la funcionalidad Agrupar para resumir los datos por año).

Si queremos resumir los datos del año 2008, por ejemplo, tenemos que cambiar el valor del filtro de cada uno de los informes.

Nuestro objetivo es programar un evento de manera que cuando cambiamos el valor del filtro de un informe, los otros dos cambien simultáneamente. En el módulo de Vba de la hoja “reporte” ponemos este código

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tdfecha As String
    Dim pt As PivotTable
 
    tdfecha = Target.PivotFields("Fecha").CurrentPage

    With Application
     .ScreenUpdating = False
     .EnableEvents = False
    End With

    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Fecha")
            .ClearAllFilters
            .CurrentPage = tdfecha
        End With
    Next pt
 
    With Application
     .ScreenUpdating = True
     .EnableEvents = True
    End With

End Sub


El evento Worksheet_PivotTableUpdate se dispara cuando cambiamos algo en la tabla que genera la actualización (en nuestro caso cambiar el valor del filtro).

Para captar el valor del filtro definimos la variable “tdfecha”

tdfecha = Target.PivotFields("Fecha").CurrentPage

Luego usamos Application.EnableEvents= False para evitar que se cree una reacción en cadena de eventos (queremos responder sólo al primer cambio).

El próximo paso es usar for – next para inicializar el valor del filtro de cada una de las tablas dinámicas en la hoja y fijar su valor con la variable “tdfecha”.

El último paso es restablecer los eventos y la actualización de pantalla que habíamos cancelado previamente.

En Excel 2003 tenemos que remplazar la sentencia .ClearAllFilters por .CurrentPage = "All"

El funcionamiento del evento puede verse en este video

40 comentarios:

  1. Es muy correcto la manera de exponer la solución. Otra opción es crear un listbox del filtro la tabla dinámica principal. Ocultar el filtro de la tabla dinámica y apuntar los demás filtros al listbox.

    Saludos.

    ResponderBorrar
  2. Esto es lo que andaba buscando. Muy agradecido

    ResponderBorrar
  3. Muy buen trabajo JLD, pero que pasa si queremso hacer el filtro con mas de una fecha?

    ResponderBorrar
  4. El código está diseñado para una sola fecha (año). Si se seleccionan dos o más años, el resultado es "Todas". Habrá que modificar el código.

    ResponderBorrar
  5. Estimado Jorge,

    Como siempre muy buen articulo, mi pregunta es si puedo hacer esto mismo pero en tablas que se encuentren en diferentes hojas.

    Gracias,
    Iván

    ResponderBorrar
  6. Agregue este código,

    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    With pt.PivotFields("Fecha")
    .CurrentPage = "All"
    .CurrentPage = tdfecha
    End With
    Next pt
    Next ws

    pero no me funciono ..sugerencias??

    ResponderBorrar
  7. corrigo, el codigo que pense sí funciona, era probelma de mi excel. Con este cambia todas las tablas en las diferentes hojas para la variable semana


    Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tdsemana As String
    Dim pt As PivotTable
    Dim ws As Worksheet

    tdsemana = Target.PivotFields("semana").CurrentPage

    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With

    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    With pt.PivotFields("semana")
    .CurrentPage = "All"
    .CurrentPage = tdsemana
    End With
    Next pt
    Next ws

    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    End Sub

    ResponderBorrar
  8. Estimado Jorge, cómo puedo cambiar el código para seleccionar más de una opción en el filtro?

    Agradezco por todas las soluciones.

    CAV

    ResponderBorrar
  9. Como pongo en la nota, lo más sencillo es usar Segmentación de Datos (Excel 2010). Supongo que estás usando esa versión de Excel.

    ResponderBorrar
  10. Estimado Jorge,

    por favor ayúdeme con este problema...

    tengo algunos archivos de excel concatenados en forma de cascada... el problema es que si cambio algún dato en el archivo inicial, el último archivo no se actualiza si es que no abro los archivos intermedios...

    agradeciendo de antemano por la ayuda...

    ResponderBorrar
  11. No puedo ayudarte excepto sugerirte organizar tus datos de manera que todo este en un sólo cuaderno.

    ResponderBorrar
  12. Saludos Jorge, quiero saber si has trabajado vincular dos tablas dinámicas pero con orígenes de datos diferentes;no he logrado hacerlo. Gracias de antemano por tu ayuda.


    José Fernández

    ResponderBorrar
  13. Si bien existe la posibilidad de Consolidación de rangos múltiples, es mejor unificar todos los datos en una única tabla de origen. Esto se puede hacer manualmente o con una macro.

    ResponderBorrar
  14. amigos
    favor vuestra ayuda.... al crear una tabla con segmentacion (vinculadas 2 tablas) al hacer los filtros y finalmente expandir uno de los valores obtenidos del total general ...... los expande todos (total general) y no el subvalor del total general.

    gracias..... que sera?

    foto: https://www.dropbox.com/s/8ngp6vbsob2zdyj/tabla.jpg

    ResponderBorrar
  15. Hola.. Antes que nada felicidades por tu blog.
    Existe alguna forma de sobre un mismo origen de datos crear una tabla dinamica sobre el campo fecha y agruparlo digamos por mes, luego crear otra tabla dinamica y que tenga diferente agrupacion?, sucede que si agrupo la segunda tabla por semana, me modifica la agrupacion de la primera... si cambio el origen de datos digamos a una columna mas.. en cualquier tabla ya no sucede eso, pero quisiera saber si existe una forma correcta de lograr esta desvinculacion respecto al agrupamiento.

    Saludos

    ResponderBorrar
  16. Saludos, Sr. Dunkelman.

    Estoy tratando de sincronizar tres tablas dinámicas que poseen la misma base de datos. Intente utilizar el código que ofrece en este blog y no me funciona.

    Tratare de ejemplificarle un poco mi problema con el ejemplo que colocó. Tengo tres tablas dinámicas que quiero sincronizarlas por "fecha", pero en mi base de datos la "fecha" de cada tabla dinámica, corresponden a columnas distintas, no se encuentran todas agrupadas en una sola columna.

    En mi caso, mi base de datos representa la posible combinación de tres productos metálicos, los cuales estan combinados bajo las mismas condiciones en una sola base de datos.

    Me gustaria saber si es posible lograr la sincronización de tres tablas dinámicas que poseen distintos filtros de informe, debido a la posición que tienen en la base de datos. Sin embargo, poseen los mismos datos, que en mi caso particular, serian los productos que he seleccionado para combinar.

    ResponderBorrar
  17. Me resulta difícil ver donde pueda estar el problema sin ver el archivo. Pedes enviarlo siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).

    ResponderBorrar
  18. Hola... una consulta, hay manera de agregar a este código una excepción? Es decir, lo que intento es que interrumpa la actualización si el cambio se dió en solamente en 1 filtro.

    ResponderBorrar
  19. Cristian, no termino de entender la consulta. La idea de código es cambiar todas las tablas cuando se filtra alguna de ellas.

    ResponderBorrar
  20. Lo que quiero decir si se puede realizar una excepción en el código. Actualmente el código me cambia todas las tablas cuando detecta un filtro. Por ejemplo, supongamos que tengo como filtros Año, Mes y Semana. Lo que quiero realizar es que si cambia el filtro de semana el código no se ejecute. Pero si cambia el año o mes se ejecute normalmente.

    ResponderBorrar
  21. Si te fijas en el código verás que

    With pt.PivotFields("Fecha")...

    determina el cambio. Tendrías que crear eventos o agregar al código la misma rutina para los otros filtros, ignorando el filtro Semana.

    ResponderBorrar
  22. Saludos, Sr. Dunkelman.
    Una consulta he tratado de utilizar la alternativa de "Segmentación de datos" pero no aparece habilitada, me podría indicar si esta falta habilitar algo. Estoy utilizando Excel 2010 y no he tenido ese problema con el archivo que ha dado de ejemplo.
    Muchas Gracias.

    ResponderBorrar
  23. La segmentación de datos aparece en la cinta en Insertar o en Herramientas de Tabala Dinámica-Opciones (que sólo es visible si seleccionamos algún elemento de una tabla dinámica).
    ¿Que quieres decir con "no habilitada", qué no es visible?

    ResponderBorrar
  24. Con "no habilitada" me refiero a que aparece la alternativa de segmentación pero no se puede utilizar. Ese problema se ha presentado cuando he tratado de realizar la segmentación con unos archivos que trabajo, lo extraño es que ese problema no se ha presentado con el archivo que ha dado de ejemplo.

    ResponderBorrar
  25. Es un poco obvio pero, ¿tienes tablas dinámicas definidas en la hoja?

    ResponderBorrar
  26. Hola,
    Necesito ayuda para vincular varios archivos xls que contienen diferentes bases de datos, en una unica tabla inamica.
    Es posible con el excel2010?

    ResponderBorrar
  27. Es posible con todas las versiones de Excel (desde la aparición de las tablas dinámicas). La pregunta es si todas las tablas de datos de los distintos archivos tienen la misma estructura y si se trata de una operación única o si debe repetirse en el futuro (con nuevos datos).
    La mejor opción es el Power Query por su flexibilidad y por la posibilidad de automatizar el proceso.
    Una opción más engorrosa es usar el MsQuery.
    Una tercera opción es usar Datos-Consolidar.

    ResponderBorrar
    Respuestas
    1. Gracias Jorge,
      los archivos tienen diferente estructura pero con algun dato en comun.

      Borrar
  28. Entinces tu mejor opción es el Power Query.

    ResponderBorrar
  29. Hola, utilice el codigo que indicas, y cuando aplico el filtro por fecha de la base de datos que tengo antigua me funciona perfecto (en la base tenia datos de septiembre para atras). Ahora agregué en la base de las dinamicas datos de octubre, y filtre la dinamica con el mes Octubre y me tira error "Run-time error 1004. application-defined or object-defined error"

    A que se puede deber esto?

    Tambien me gustaria saber cómo modificar este codigo para que me seleccione varias fechas en vez de una sola.

    Gracias!

    ResponderBorrar
  30. Benjamín
    tendrías que mandarme el cuaderno paa que pueda ver dónde está el problema (fijate en el enlace Ayuda, en la parte superior del blog).
    En cuanto a seleccionar varias fechas, si usas Excel 2010-2013 la mejor opción es usar segmentación de datos.

    ResponderBorrar
  31. Jorge, pude solucionar el problema.

    En cuanto a la seleccion de fechas multiples, no poseo Excel 2010 ni 2013, tengo 2007, por eso requiero de una macro. Es posible? Gracias por la ayuda!

    ResponderBorrar
  32. Benjamín, es posible. Te sugiero que consultes en alguno de los varios foros de Excel. En estos días no dispongo de mucho tiempo libre- Más adelante veré si puedo puiblicar el código.

    ResponderBorrar
  33. Hola Jorge, buenas Tardes, excelente blog y mis felicitaciones! tengo tiempo buscando un código para hacer la macro de selección múltiple, como lo indica el amigo Bejamin, que uno pueda filtrar varios datos en una TD y las demás TD tengan los mismos filtros seleccionados, pero no lo consigo por ningún lado y quiera saber si me puedes ayudar con eso. saludos!!

    ResponderBorrar
  34. Si usas Excel 2010 o 2013 te sugiero Segmentación de Datos. En cuanto al código para versiones anteriores veré si encuentro un poco de tiempo más adelante para ocuparme del tema.

    ResponderBorrar
  35. Muy agradecido. Justo en el clavo.

    ResponderBorrar
  36. Tengo un problema con segmentación de Datos, en el excel 2013. Por alguna razón cuando creo una segunda tabla dinámica copiando y pegando. Y luego a tabla dinámica (TD) 1 le agregaba la opción segmentación, está ultima por defecto agrupaba solo la TD 1, pero ahora agrupa la TD1, TD2, TD3... cuando solo quería q se conecte con una sola TD, al modificar esto y seleccionar una, se mueven de la misma manera todas los segmentos de datos de la hoja de excel. Que ocurre?? porque se agrupan todas cuando señalo una?. Agradezco una respuesta.

    ResponderBorrar
  37. Fijate en las conexiones del Slicer (en el menú de las propiedades) y verás que está conectado a todas las tablas. Esto sucede porque las cread usando copiar-pegar. En el menú de las propiedades puedes desconectar lo que necesites.

    ResponderBorrar
  38. Si revise y es por copiar y pegar. Desactive todas al pegar en otro lado. Y de esta manera no me arrastra la anterior conexión, pero son varios gráficos similares pero de diferentes productos. Gracias!

    ResponderBorrar

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