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
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.
ResponderBorrarSaludos.
Esto es lo que andaba buscando. Muy agradecido
ResponderBorrarMuy buen trabajo JLD, pero que pasa si queremso hacer el filtro con mas de una fecha?
ResponderBorrarEl 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.
ResponderBorrarEstimado Jorge,
ResponderBorrarComo siempre muy buen articulo, mi pregunta es si puedo hacer esto mismo pero en tablas que se encuentren en diferentes hojas.
Gracias,
Iván
Agregue este código,
ResponderBorrarFor 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??
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
ResponderBorrarPrivate 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
Estimado Jorge, cómo puedo cambiar el código para seleccionar más de una opción en el filtro?
ResponderBorrarAgradezco por todas las soluciones.
CAV
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.
ResponderBorrarEstimado Jorge,
ResponderBorrarpor 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...
No puedo ayudarte excepto sugerirte organizar tus datos de manera que todo este en un sólo cuaderno.
ResponderBorrarSaludos 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.
ResponderBorrarJosé Fernández
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.
ResponderBorraramigos
ResponderBorrarfavor 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
Hola.. Antes que nada felicidades por tu blog.
ResponderBorrarExiste 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
Fijate en la técnica que muestro en esta nota.
ResponderBorrarSaludos, Sr. Dunkelman.
ResponderBorrarEstoy 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.
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).
ResponderBorrarHola... 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.
ResponderBorrarCristian, no termino de entender la consulta. La idea de código es cambiar todas las tablas cuando se filtra alguna de ellas.
ResponderBorrarLo 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.
ResponderBorrarSi te fijas en el código verás que
ResponderBorrarWith 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.
Saludos, Sr. Dunkelman.
ResponderBorrarUna 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.
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).
ResponderBorrar¿Que quieres decir con "no habilitada", qué no es visible?
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.
ResponderBorrarEs un poco obvio pero, ¿tienes tablas dinámicas definidas en la hoja?
ResponderBorrarHola,
ResponderBorrarNecesito ayuda para vincular varios archivos xls que contienen diferentes bases de datos, en una unica tabla inamica.
Es posible con el excel2010?
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).
ResponderBorrarLa 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.
Gracias Jorge,
Borrarlos archivos tienen diferente estructura pero con algun dato en comun.
Entinces tu mejor opción es el Power Query.
ResponderBorrarHola, 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"
ResponderBorrarA 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!
Benjamín
ResponderBorrartendrí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.
Jorge, pude solucionar el problema.
ResponderBorrarEn 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!
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.
ResponderBorrarHola 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!!
ResponderBorrarSi 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.
ResponderBorrarMuy agradecido. Justo en el clavo.
ResponderBorrarTengo 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.
ResponderBorrarFijate 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.
ResponderBorrarSi 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