Filtrering af flere pivottabeller samtidigt

Når du opretter komplekse rapporter og især dashboards i Microsoft Excel, er det meget ofte nødvendigt at filtrere flere pivottabeller på én gang. Lad os se, hvordan dette kan implementeres.

Metode 1: General Slicer til filtrering af pivoter på den samme datakilde

Hvis pivoterne er bygget på basis af én kildedatatabel, så er den nemmeste måde at bruge dem til at filtrere dem samtidigt sektion er et grafisk knapfilter forbundet til alle pivottabeller på én gang.

For at tilføje den skal du vælge en hvilken som helst celle i en af ​​oversigten og på fanen Analyse vælg hold Paste Slice (Analyser - Indsæt skæremaskine). Marker afkrydsningsfelterne for de kolonner, som du vil filtrere data efter, i vinduet, der åbnes, og klik OK:

Den oprettede slicer vil som standard kun filtrere den pivot, som den blev oprettet for. Men ved at bruge knappen Rapportér forbindelser (Rapportér forbindelser) fanen Slice (skiver) vi kan nemt tilføje andre oversigtstabeller til listen over filtrerede tabeller:

Metode 2. Generelt udsnit til filtrering af resuméer på forskellige kilder

Hvis dine pivoter ikke blev bygget i henhold til en, men i henhold til forskellige kildedatatabeller, vil ovenstående metode ikke fungere, fordi i vinduet Rapportér forbindelser kun de resuméer, der er bygget fra den samme kilde, vises.

Du kan dog nemt komme uden om denne begrænsning, hvis du bruger datamodellen (vi diskuterede det i detaljer i denne artikel). Hvis vi indlæser vores tabeller i modellen og forbinder dem der, så vil filtreringen gælde for begge tabeller på samme tid.

Lad os sige, at vi har to tabeller for salgs- og transportomkostninger som inputdata:

Antag, at vi står over for opgaven med at bygge vores eget resumé for hver af dem og derefter filtrere dem samtidigt efter byer med et fælles snit.

Vi gør følgende:

1. Gør vores originale borde til dynamiske smarte borde med en tastaturgenvej Ctrl+T eller kommandoer Hjem – Formater som en tabel (Hjem — Formater som tabel) og giv dem navne tabelProdaji и fanenTransport fanen Constructor (Design).

2. Indlæs begge borde på skift i modellen ved hjælp af knappen Tilføj til datamodel på fanen Power Pivot.

Det vil ikke være muligt at linke disse tabeller direkte i modellen, for mens Power Pivot kun understøtter en-til-mange relationer, så kræver det, at en af ​​tabellerne ikke har nogen dubletter i den kolonne, vi linker på. Vi har det samme i begge tabeller i feltet By der er gentagelser. Så vi skal oprette endnu en mellemliggende opslagstabel med en liste over unikke bynavne fra begge tabeller. Den nemmeste måde at gøre dette på er med tilføjelsesfunktionen Power Query, som har været indbygget i Excel siden 2016-versionen (og til Excel 2010-2013 downloades den gratis fra Microsofts hjemmeside).

3. Efter at have valgt en hvilken som helst celle i den "smarte" tabel, indlæser vi dem en efter en i Power Query med knappen Fra bord/sortiment fanen data (Data — Fra tabel/område) og vælg derefter til i vinduet Power Query Den væsentligste hold Luk og indlæs – Luk og læs ind (Hjem — Luk&indlæs — Luk&indlæs til...) og importmulighed Bare opret en forbindelse (Opret kun forbindelse):

4. Vi samler begge borde til ét med kommandoen Data – Kombiner forespørgsler – Tilføj (Data — Kombiner forespørgsler — Tilføj). Kolonner med de samme navne i overskriften vil passe under hinanden (som en kolonne By), og dem, der ikke matcher, vil blive placeret i forskellige kolonner (men det er ikke vigtigt for os).

5. Slet alle kolonner undtagen kolonne Byved at højreklikke på dens titel og vælge kommandoen Slet andre kolonner (Fjern andre kolonner) og fjern derefter alle duplikerede bynavne ved at højreklikke på kolonneoverskriften igen og vælge kommandoen Fjern duplikater (Fjern dubletter):

6. Den oprettede referenceliste uploades til Datamodellen via Hjem — Luk og indlæs — Luk og indlæs (Hjem — Luk&indlæs — Luk&indlæs til...) og vælg indstillingen Bare opret en forbindelse (Opret kun forbindelse) og det vigtigste! – slå afkrydsningsfeltet til Tilføj disse data til datamodellen (Tilføj disse data til datamodel):

7. Nu kan vi vende tilbage til Power Pivot-vinduet (faneblad powerpivot - knap Management), skifte til Kortvisning (Diagramvisning) og link vores tabeller over salgs- og transportomkostninger gennem det oprettede mellemliggende bibliotek over byer (ved at trække felter mellem tabeller):

8. Nu kan du oprette alle de nødvendige pivottabeller til den oprettede model ved hjælp af knappen oversigtstabel (pivottabel) on Den væsentligste (Hjem) fanen i Power Pivot-vinduet og, ved at vælge en celle i en hvilken som helst pivot, på fanen Analyse knappen tilføj skive Paste Slice (Analyser — Indsæt skæremaskine) og vælg at skære i listeboksen By i den tilføjede mappe:

Nu ved at klikke på den velkendte knap Rapportér forbindelser on Udsnitsfane (Slicer — Rapportér forbindelser) vi vil se hele vores oversigt, fordi de nu er bygget på relaterede kildetabeller. Det er tilbage at aktivere de manglende afkrydsningsfelter og klikke på OK – og vores slicer begynder at filtrere alle valgte pivottabeller på samme tid.