Sincronización de tablas dinámicas en Excel

jueves, enero 26, 2012

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

41 comments:

Solme Omar | Tablas Dinamicas Excel 2010 26 enero, 2012 16:24  

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.

Claudio,  31 enero, 2012 17:11  

Esto es lo que andaba buscando. Muy agradecido

Anónimo,  01 febrero, 2012 10:53  

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

Jorge L. Dunkelman 01 febrero, 2012 17:33  

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.

Anónimo,  27 marzo, 2012 18:26  

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

Anónimo,  27 marzo, 2012 23:13  

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??

Anónimo,  28 marzo, 2012 14:54  

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

Carlos Valerio 17 julio, 2012 05:27  

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

Jorge L. Dunkelman 17 julio, 2012 20:04  

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.

David Serrano Vincenti 20 julio, 2012 07:54  

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...

Jorge L. Dunkelman 20 julio, 2012 13:58  

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

Anónimo,  15 agosto, 2012 01:57  

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

Jorge L. Dunkelman 17 agosto, 2012 16:48  

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.

Blog Personal 01 octubre, 2012 15:03  

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

Anónimo,  06 noviembre, 2012 03:56  

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

Jorge L. Dunkelman 06 noviembre, 2012 06:41  

Fijate en la técnica que muestro en esta nota.

Los Pasteles de Daisy 06 noviembre, 2012 10:09  

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.

Jorge L. Dunkelman 06 noviembre, 2012 21:55  

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).

Cristian Carvelli 06 diciembre, 2012 15:35  

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.

Jorge L. Dunkelman 07 diciembre, 2012 15:44  

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

Cristian Carvelli 07 diciembre, 2012 18:53  

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.

Jorge L. Dunkelman 09 diciembre, 2012 18:55  

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.

Anónimo,  11 abril, 2014 14:45  

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.

Jorge Dunkelman 11 abril, 2014 18:21  

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?

Anónimo,  02 mayo, 2014 17:38  

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.

Jorge Dunkelman 02 mayo, 2014 18:18  

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

Anónimo,  14 agosto, 2014 23:20  

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

Jorge Dunkelman 15 agosto, 2014 12:34  

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.

Anónimo,  16 agosto, 2014 18:59  

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

Jorge Dunkelman 16 agosto, 2014 23:20  

Entinces tu mejor opción es el Power Query.

Benjamin Fernandez 18 noviembre, 2014 21:43  

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!

Jorge Dunkelman 19 noviembre, 2014 08:04  

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.

Benjamin Fernandez 19 noviembre, 2014 20:37  

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!

Jorge Dunkelman 21 noviembre, 2014 16:33  

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.

Anónimo,  22 febrero, 2015 23: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!!

Jorge Dunkelman 23 febrero, 2015 07:01  

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.

mavallejo 11 mayo, 2016 09:34  

Muy agradecido. Justo en el clavo.

Daniel Rosales 04 junio, 2016 19:59  

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.

Jorge Dunkelman 05 junio, 2016 10:07  

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.

Daniel Rosales 08 junio, 2016 19:21  

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!

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP