Filtrado de múltiples tablas dinámicas simultáneamente

Al crear informes complejos y, especialmente, tableros en Microsoft Excel, muy a menudo es necesario filtrar simultáneamente varias tablas dinámicas a la vez. Veamos cómo se puede implementar esto.

Método 1: General Slicer para filtrar pivotes en la misma fuente de datos

Si los pivotes se construyen sobre la base de una tabla de datos de origen, entonces la forma más fácil es usarlos para filtrarlos simultáneamente. . es un filtro de botón gráfico conectado a todas las tablas dinámicas a la vez.

Para agregarlo, seleccione cualquier celda en uno de los resumen y en la pestaña ECONOMÉTRICOS selecciona un equipo Pegar rebanada (Analizar — Insertar segmentación). En la ventana que se abre, marque las casillas de las columnas por las que desea filtrar los datos y haga clic en OK:

La segmentación creada filtrará, de forma predeterminada, solo el pivote para el que se creó. Sin embargo, usando el botón Informe de conexiones (Reportar conexiones) de la pestaña. Rebanada (Rebanadas) podemos agregar fácilmente otras tablas de resumen a la lista de tablas filtradas:

Método 2. Segmento general para filtrar resúmenes en diferentes fuentes

Si sus pivotes no se construyeron de acuerdo con uno, sino de acuerdo con diferentes tablas de datos de origen, entonces el método anterior no funcionará, porque en la ventana Informe de conexiones solo se muestran los resúmenes creados a partir de la misma fuente.

Sin embargo, puede sortear fácilmente esta limitación si usa el modelo de datos (lo discutimos en detalle en este artículo). Si cargamos nuestras tablas en el modelo y las vinculamos allí, el filtrado se aplicará a ambas tablas al mismo tiempo.

Digamos que tenemos dos tablas para costos de ventas y transporte como datos de entrada:

Supongamos que nos enfrentamos a la tarea de construir nuestro propio resumen para cada uno de ellos y luego filtrarlos simultáneamente por ciudades con un corte común.

Hacemos lo siguiente:

1. Convertir nuestras mesas originales en mesas inteligentes dinámicas con un atajo de teclado Ctrl+T o comandos Inicio – Formato como tabla (Inicio — Formatear como tabla) y darles nombres TablProdaji и tabTransporte de la pestaña. Constructor (Diseño).

2. Cargue ambas tablas a su vez en el Modelo usando el botón Agregar al modelo de datos en la pestaña Power Pivot.

No será posible vincular directamente estas tablas en el Modelo, porque mientras que Power Pivot solo admite relaciones de uno a varios, es decir, requiere que una de las tablas no tenga duplicados en la columna en la que estamos vinculando. Tenemos lo mismo en ambas mesas en el campo. Ciudad hay repeticiones. Entonces, necesitamos crear otra tabla de búsqueda intermedia con una lista de nombres de ciudades únicos de ambas tablas. La forma más sencilla de hacerlo es con la funcionalidad del complemento Power Query, que se ha integrado en Excel desde la versión 2016 (y para Excel 2010-2013 se descarga de forma gratuita desde el sitio web de Microsoft).

3. Habiendo seleccionado cualquier celda dentro de la tabla “inteligente”, las cargamos una a una en Power Query con el botón De tabla/rango de la pestaña. Datos (Datos — De la tabla/rango) y luego en la ventana de Power Query seleccione en El principal equipos Cerrar y cargar – Cerrar y cargar (Inicio — Cerrar y cargar — Cerrar y cargar a…) y opción de importación Solo crea una conexión (Solo crear conexión):

4. Unimos ambas tablas en una sola con el comando Datos – Combinar Consultas – Agregar (Datos — Combinar consultas — Agregar). Las columnas con los mismos nombres en el encabezado encajarán una debajo de la otra (como una columna Ciudad), y los que no coincidan se colocarán en columnas diferentes (pero esto no es importante para nosotros).

5. Eliminar todas las columnas excepto la columna Ciudadhaciendo clic derecho en su título y seleccionando el comando Eliminar otras columnas (Eliminar otras columnas) y luego elimine todos los nombres de ciudades duplicados haciendo clic derecho en el encabezado de la columna nuevamente y seleccionando el comando Eliminar duplicados (Eliminar duplicados):

6. La lista de referencia creada se carga en el modelo de datos a través de Inicio — Cerrar y cargar — Cerrar y cargar (Inicio — Cerrar y cargar — Cerrar y cargar a…) y elige la opción Solo crea una conexión (Solo crear conexión) y lo más importante! – active la casilla de verificación Agregar estos datos al modelo de datos (Agregue estos datos al modelo de datos):

7. Ahora podemos, volviendo a la ventana de Power Pivot (pestaña PowerPivot - botón Administración), cambiar a Vista de carta (Vista de diagrama) y vincular nuestras tablas de costos de ventas y transporte a través del directorio intermedio de ciudades creado (arrastrando campos entre tablas):

8. Ahora puede crear todas las tablas dinámicas requeridas para el modelo creado usando el botón tabla resumen (Tabla dinámica) on El principal (Home) pestaña en la ventana de Power Pivot y, al seleccionar cualquier celda en cualquier pivote, en la pestaña ECONOMÉTRICOS añadir botón de corte Pegar rebanada (Analizar — Insertar segmentación) y elija cortar en el cuadro de lista Ciudad en el directorio añadido:

Ahora, haciendo clic en el botón familiar Informe de conexiones on Pestaña Sector (Slicer — Informe de conexiones) Veremos todo nuestro resumen, porque ahora se basan en tablas de origen relacionadas. Queda por habilitar las casillas de verificación que faltan y hacer clic en OK – y nuestra segmentación comenzará a filtrar todas las tablas dinámicas seleccionadas al mismo tiempo.