Meerdere draaitabellen tegelijk filteren

Bij het maken van complexe rapporten en vooral dashboards in Microsoft Excel is het vaak nodig om meerdere draaitabellen tegelijk te filteren. Laten we eens kijken hoe dit kan worden geïmplementeerd.

Methode 1: General Slicer voor het filteren van pivots op dezelfde gegevensbron

Als de pivots zijn gebouwd op basis van één brongegevenstabel, dan is de eenvoudigste manier om ze te gebruiken om ze tegelijkertijd te filteren sectie is een grafische knopfilter die in één keer met alle draaitabellen is verbonden.

Om het toe te voegen, selecteert u een cel in een van de samenvattingen en op het tabblad Analyse selecteer team Plak plak (Analyseren — Slicer invoegen). Vink in het geopende venster de vakjes aan voor de kolommen waarop u gegevens wilt filteren en klik op OK:

De gemaakte slicer filtert standaard alleen de spil waarvoor deze is gemaakt. Echter, met behulp van de knop Rapporteer verbindingen (Rapport verbindingen) tab Plak (Plakjes) we kunnen gemakkelijk andere samenvattende tabellen toevoegen aan de lijst met gefilterde tabellen:

Methode 2. Algemeen segment voor het filteren van samenvattingen op verschillende bronnen

Als uw pivots niet volgens één, maar volgens verschillende brongegevenstabellen zijn gebouwd, dan zal de bovenstaande methode niet werken, omdat in het venster Rapporteer verbindingen alleen die samenvattingen die met dezelfde bron zijn gemaakt, worden weergegeven.

U kunt deze beperking echter gemakkelijk omzeilen als u het gegevensmodel gebruikt (we hebben het in dit artikel in detail besproken). Als we onze tabellen in het model laden en ze daar koppelen, wordt de filtering op beide tabellen tegelijk toegepast.

Laten we zeggen dat we twee tabellen hebben voor verkoop- en transportkosten als invoergegevens:

Stel dat we voor de taak staan ​​om voor elk van hen onze eigen samenvatting te maken en ze vervolgens tegelijkertijd te filteren op steden met een gemeenschappelijke snit.

Wij doen het volgende:

1. Onze originele tabellen veranderen in dynamische slimme tabellen met een sneltoets Ctrl+T of commando's Home – Opmaak als tabel (Home - Opmaken als tabel) en geef ze namen tablProdaji и tabVervoer tab Constructeur (Ontwerp).

2. Laad beide tabellen om de beurt in het model met de knop Toevoegen aan gegevensmodel op het tabblad Power Pivot.

Het is niet mogelijk om deze tabellen rechtstreeks in het model te koppelen, omdat Power Pivot weliswaar alleen een-op-veel-relaties ondersteunt, dwz vereist dat een van de tabellen geen duplicaten heeft in de kolom waarnaar we linken. We hebben hetzelfde in beide tabellen in het veld Plaats er zijn herhalingen. We moeten dus nog een tussentijdse opzoektabel maken met een lijst met unieke stadsnamen uit beide tabellen. De eenvoudigste manier om dit te doen is met de Power Query-invoegtoepassing, die sinds de 2016-versie in Excel is ingebouwd (en voor Excel 2010-2013 wordt deze gratis gedownload van de Microsoft-website).

3. Nadat we een cel in de "slimme" tabel hebben geselecteerd, laden we ze één voor één in Power Query met de knop Van tafel/reeks tab Data (Gegevens — Uit tabel/bereik) en selecteer vervolgens in het Power Query-venster aan De belangrijkste teams Sluiten en laden – Sluiten en laden (Home — Sluiten&Laden — Sluiten&Laden naar…) en importoptie Maak gewoon een verbinding (Alleen verbinding maken):

4. We voegen beide tabellen samen tot één met het commando Gegevens – Zoekopdrachten combineren – Toevoegen (Gegevens — Zoekopdrachten combineren — Toevoegen). Kolommen met dezelfde namen in de kop zullen onder elkaar passen (zoals een kolom Plaats), en degenen die niet overeenkomen, worden in verschillende kolommen geplaatst (maar dit is niet belangrijk voor ons).

5. Verwijder alle kolommen behalve kolom Plaatsdoor met de rechtermuisknop op de titel te klikken en de opdracht te selecteren Andere kolommen verwijderen (Verwijder andere kolommen) en verwijder vervolgens alle dubbele stadsnamen door nogmaals met de rechtermuisknop op de kolomkop te klikken en de opdracht te selecteren Verwijder duplicaten (Verwijder duplicaten):

6. De aangemaakte referentielijst wordt geüpload naar het datamodel via Home — Sluiten en laden — Sluiten en laden (Home — Sluiten&Laden — Sluiten&Laden naar…) en kies de optie Maak gewoon een verbinding (Alleen verbinding maken) en het belangrijkste! – schakel het selectievakje in Voeg deze gegevens toe aan het gegevensmodel (Voeg deze gegevens toe aan het gegevensmodel):

7. Nu kunnen we, terugkerend naar het Power Pivot-venster (tab powerpivot - knop Management), overschakelen naar Grafiekweergave (Diagramweergave) en koppel onze tabellen met verkoop- en transportkosten via de aangemaakte tussenmap van steden (door velden tussen tabellen te slepen):

8. Nu kunt u alle vereiste draaitabellen voor het gemaakte model maken met behulp van de knop samenvattingstabel (draaitabel) on De belangrijkste (Home) tabblad in het Power Pivot-venster en, door een cel in een draaipunt te selecteren, op het tabblad Analyse plakknop toevoegen Plak plak (Analyseren - Slicer invoegen) en kies om te segmenteren in de keuzelijst Plaats in de toegevoegde map:

Door nu op de bekende knop te klikken Rapporteer verbindingen on Tabblad Segment (Slicer — Rapportverbindingen) we zullen al onze samenvattingen zien, omdat ze nu zijn gebouwd op gerelateerde brontabellen. Het blijft om de ontbrekende selectievakjes in te schakelen en op . te klikken OK - en onze slicer begint alle geselecteerde draaitabellen tegelijkertijd te filteren.