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