Filtrering av flere pivottabeller samtidig

Når du lager komplekse rapporter og spesielt dashbord i Microsoft Excel, er det svært ofte nødvendig å filtrere flere pivottabeller samtidig. La oss se hvordan dette kan implementeres.

Metode 1: General Slicer for filtrering av pivoter på samme datakilde

Hvis pivotene er bygget på grunnlag av én kildedatatabell, er den enkleste måten å bruke dem til å filtrere dem samtidig seksjon er et grafisk knappefilter koblet til alle pivottabeller samtidig.

For å legge det til, velg en hvilken som helst celle i et av sammendraget og på fanen Analyse velg team Lim inn skive (Analyser - Sett inn skjæremaskin). I vinduet som åpnes, merk av i boksene for kolonnene du vil filtrere data etter, og klikk OK:

Den opprettede sliceren vil som standard filtrere bare pivoten den ble opprettet for. Men ved å bruke knappen Rapporter tilkoblinger (Rapporter tilkoblinger) tab Slice (skiver) vi kan enkelt legge til andre sammendragstabeller i listen over filtrerte tabeller:

Metode 2. Generell skive for filtrering av sammendrag på forskjellige kilder

Hvis pivotene dine ble bygget ikke i henhold til en, men i henhold til forskjellige kildedatatabeller, vil metoden ovenfor ikke fungere, fordi i vinduet Rapporter tilkoblinger bare de sammendragene som ble bygget fra samme kilde vises.

Du kan imidlertid enkelt omgå denne begrensningen hvis du bruker datamodellen (vi diskuterte den i detalj i denne artikkelen). Hvis vi laster tabellene våre inn i modellen og kobler dem dit, vil filtreringen gjelde for begge tabellene samtidig.

La oss si at vi har to tabeller for salgs- og transportkostnader som inputdata:

Anta at vi står overfor oppgaven med å bygge vår egen oppsummering for hver av dem og deretter filtrere dem samtidig etter byer med et felles snitt.

Vi gjør følgende:

1. Gjør om de originale bordene våre til dynamiske smarte bord med en tastatursnarvei Ctrl+T eller kommandoer Hjem – Formater som en tabell (Hjem — Formater som tabell) og gi dem navn tablProdaji и kategorien Transport tab Constructor (Design).

2. Last inn begge bordene etter tur inn i modellen ved å bruke knappen Legg til datamodell på Power Pivot-fanen.

Det vil ikke være mulig å koble disse tabellene direkte i modellen, fordi mens Power Pivot kun støtter en-til-mange-relasjoner, dvs. krever at en av tabellene ikke har noen duplikater i kolonnen vi lenker på. Vi har det samme i begge tabellene i feltet City det er repetisjoner. Så vi må lage en annen mellomoppslagstabell med en liste over unike bynavn fra begge tabellene. Den enkleste måten å gjøre dette på er med tilleggsfunksjonen Power Query, som har vært innebygd i Excel siden 2016-versjonen (og for Excel 2010-2013 lastes den ned gratis fra Microsofts nettsted).

3. Etter å ha valgt en hvilken som helst celle i den "smarte" tabellen, laster vi dem en etter en i Power Query med knappen Fra bord/sortiment tab Data (Data – Fra tabell/område) og velg deretter på i Power Query-vinduet Den viktigste lag Lukk og last – Lukk og last inn (Hjem — Lukk&last — Lukk&last til...) og importalternativ Bare opprette en forbindelse (Bare opprette forbindelse):

4. Vi slår sammen begge bordene til ett med kommandoen Data – Kombiner spørringer – Legg til (Data – Kombiner spørringer – Legg til). Kolonner med samme navn i overskriften vil passe under hverandre (som en kolonne City), og de som ikke samsvarer vil bli plassert i forskjellige kolonner (men dette er ikke viktig for oss).

5. Slett alle kolonner unntatt kolonne Cityved å høyreklikke på tittelen og velge kommandoen Slett andre kolonner (Fjern andre kolonner) og fjern deretter alle dupliserte bynavn ved å høyreklikke på kolonneoverskriften igjen og velge kommandoen Fjern duplikater (Fjern duplikater):

6. Den opprettede referanselisten lastes opp til Datamodellen via Hjem — Lukk og last — Lukk og last inn (Hjem — Lukk&last — Lukk&last til...) og velg alternativet Bare opprette en forbindelse (Bare opprette forbindelse) og det viktigste! – slå på avmerkingsboksen Legg til disse dataene i datamodellen (Legg til disse dataene i datamodellen):

7. Nå kan vi gå tilbake til Power Pivot-vinduet (tab Powerpivot - knapp Administrasjon), bytte til Kartvisning (Diagramvisning) og koble våre tabeller over salgs- og transportkostnader gjennom den opprettede mellomkatalogen over byer (ved å dra felt mellom tabeller):

8. Nå kan du opprette alle nødvendige pivottabeller for den opprettede modellen ved å bruke knappen oppsummeringstabell (pivottabell) on Den viktigste (Hjem) i Power Pivot-vinduet, og ved å velge en hvilken som helst celle i en hvilken som helst pivot, på fanen Analyse legg til skive-knappen Lim inn skive (Analyser - Sett inn skjærer) og velg å dele i listeboksen City i den tillagte katalogen:

Nå, ved å klikke på den kjente knappen Rapporter tilkoblinger on Skjærflik (Slicer – Rapporter tilkoblinger) vi vil se alle sammendragene våre, fordi de nå er bygget på relaterte kildetabeller. Det gjenstår å aktivere de manglende avmerkingsboksene og klikke på OK – og sliceren vår vil begynne å filtrere alle valgte pivottabeller samtidig.