Filtrando varias táboas dinámicas simultáneamente

Cando se crean informes complexos e, especialmente, paneis de control en Microsoft Excel, moitas veces é necesario filtrar simultaneamente varias táboas dinámicas á vez. Vexamos como se pode implementar isto.

Método 1: Slicer xeral para filtrar pivotes na mesma fonte de datos

Se os pivotes están construídos a partir dunha táboa de datos de orixe, entón o xeito máis sinxelo é usalos para filtralos simultaneamente sección é un filtro de botón gráfico conectado a todas as táboas dinámicas á vez.

Para engadilo, seleccione calquera cela nun dos resumos e na pestana Análise seleccionar equipo Pegar porción (Analizar - Inserir cortador). Na xanela que se abre, marque as caixas das columnas polas que desexa filtrar os datos e prema OK:

O slicer creado, por defecto, filtrará só o pivote para o que foi creado. Non obstante, usando o botón Informe de conexións (Informar conexións) aba Porción (Fanxas) podemos engadir facilmente outras táboas resumo á lista de táboas filtradas:

Método 2. Porción xeral para filtrar resumos en diferentes fontes

Se os teus pivotes non foron construídos segundo un, senón segundo diferentes táboas de datos de orixe, entón o método anterior non funcionará, porque na xanela Informe de conexións só se mostran os resumos que se crearon a partir da mesma fonte.

Non obstante, pode evitar esta limitación facilmente se utiliza o Modelo de datos (compartimos en detalle neste artigo). Se cargamos as nosas táboas no Modelo e as vinculamos alí, o filtrado aplicarase a ambas as dúas táboas ao mesmo tempo.

Digamos que temos dúas táboas de custos de vendas e transporte como datos de entrada:

Supoñamos que estamos ante a tarefa de construír o noso propio resumo para cada un deles e despois filtralos simultaneamente por cidades cun corte común.

Facemos o seguinte:

1. Convertendo as nosas táboas orixinais en táboas intelixentes dinámicas cun atallo de teclado Ctrl+T ou comandos Inicio - Formato como unha táboa (Inicio — Formato como táboa) e ponlles nomes tablProdaji и pestanaTransporte aba Construtor (Deseño).

2. Cargue ambas táboas á súa vez no Modelo usando o botón Engadir ao modelo de datos na pestana Power Pivot.

Non será posible enlazar directamente estas táboas no Modelo, porque aínda que Power Pivot só admite relacións de un a varios, é dicir, require que unha das táboas non teña duplicados na columna na que estamos enlazando. Temos o mesmo nas dúas táboas do campo cidade hai repeticións. Polo tanto, necesitamos crear outra táboa de busca intermedia cunha lista de nomes únicos de cidades de ambas as táboas. O xeito máis sinxelo de facelo é coa funcionalidade do complemento Power Query, que se incorpora a Excel desde a versión de 2016 (e para Excel 2010-2013 descárgase gratuitamente desde o sitio web de Microsoft).

3. Despois de seleccionar calquera cela dentro da táboa "intelixente", cargámolas unha a unha en Power Query co botón Da táboa/rango aba Data (Datos: da táboa/rango) e, a continuación, na xanela de Power Query, seleccione activado A principal equipos Pechar e cargar: pechar e cargar (Inicio — Pechar e cargar — Pechar e cargar en…) e opción de importación Só ten que crear unha conexión (Só crear conexión):

4. Unimos as dúas táboas nunha soa co comando Datos - Combinar consultas - Engadir (Datos — Combinar consultas — Anexar). As columnas co mesmo nome na cabeceira encaixarán unhas debaixo das outras (como unha columna cidade), e os que non coincidan colocaranse en columnas diferentes (pero isto non é importante para nós).

5. Elimina todas as columnas excepto a columna cidadefacendo clic co botón dereito sobre o seu título e seleccionando o comando Elimina outras columnas (Eliminar outras columnas) e, a continuación, elimine todos os nomes de cidade duplicados facendo clic co botón dereito do rato no encabezado da columna de novo e seleccionando o comando Eliminar duplicados (Eliminar duplicados):

6. A lista de referencia creada cárgase ao modelo de datos mediante Inicio — Pechar e cargar — Pechar e cargar (Inicio — Pechar e cargar — Pechar e cargar en…) e elixe a opción Só ten que crear unha conexión (Só crear conexión) e o máis importante! - activa a caixa de verificación Engade estes datos ao modelo de datos (Engadir estes datos ao modelo de datos):

7. Agora podemos, volvendo á xanela Power Pivot (tab PowerPivot - botón xestión), cambiar a Vista gráfica (Vista de diagrama) e vincular as nosas táboas de custos de vendas e transporte a través do directorio intermedio creado de cidades (arrastrando campos entre táboas):

8. Agora podes crear todas as táboas dinámicas necesarias para o modelo creado usando o botón táboa resumo (Táboa dinámica) on A principal (Inicio) na xanela Power Pivot e, seleccionando calquera cela en calquera pivote, na pestana Análise botón engadir porción Pegar porción (Analizar - Inserir cortador) e escolle cortar na caixa de lista cidade no directorio engadido:

Agora, premendo no botón familiar Informe de conexións on Pestana porción (Slicer — Informar conexións) veremos todo o noso resumo, porque agora están construídos sobre táboas fonte relacionadas. Queda por activar as caixas de verificación que faltan e facer clic en OK – e o noso slicer comezará a filtrar todas as táboas dinámicas seleccionadas ao mesmo tempo.