Filtrera flera pivottabeller samtidigt

När du skapar komplexa rapporter och särskilt instrumentpaneler i Microsoft Excel, är det mycket ofta nödvändigt att samtidigt filtrera flera pivottabeller samtidigt. Låt oss se hur detta kan genomföras.

Metod 1: General Slicer för att filtrera pivoter på samma datakälla

Om pivoterna är byggda på basis av en källdatatabell är det enklaste sättet att använda dem för att filtrera dem samtidigt avsnitt är ett grafiskt knappfilter kopplat till alla pivottabeller samtidigt.

För att lägga till den, välj valfri cell i en av sammanfattningarna och på fliken Analys Välj lag Klistra in skiva (Analysera - Sätt i skärmaskin). I fönstret som öppnas, markera rutorna för de kolumner som du vill filtrera data efter och klicka OK:

Den skapade slicern kommer som standard att filtrera endast pivoten för vilken den skapades. Men med hjälp av knappen Rapportera anslutningar (Rapportera anslutningar) fliken Skiva (skivor) vi kan enkelt lägga till andra sammanfattningstabeller till listan över filtrerade tabeller:

Metod 2. Allmän del för filtrering av sammanfattningar på olika källor

Om dina pivoter inte byggdes enligt en, utan enligt olika källdatatabeller, kommer metoden ovan inte att fungera, eftersom i fönstret Rapportera anslutningar endast de sammanfattningar som byggts från samma källa visas.

Du kan dock enkelt komma runt denna begränsning om du använder datamodellen (vi diskuterade det i detalj i den här artikeln). Om vi ​​laddar in våra tabeller i modellen och länkar dem dit, kommer filtreringen att gälla båda tabellerna samtidigt.

Låt oss säga att vi har två tabeller för försäljnings- och transportkostnader som indata:

Anta att vi står inför uppgiften att bygga en egen sammanfattning för var och en av dem och sedan filtrera dem samtidigt efter städer med ett gemensamt snitt.

Vi gör följande:

1. Förvandla våra originalbord till dynamiska smarta bord med en kortkommando ctrl+T eller kommandon Hem – Formatera som en tabell (Hem — Formatera som tabell) och ge dem namn tablProdaji и tabTransport fliken Konstruktör (Design).

2. Ladda båda borden i tur och ordning i modellen med knappen Lägg till i datamodell på fliken Power Pivot.

Det kommer inte att vara möjligt att direkt länka dessa tabeller i modellen, eftersom Power Pivot endast stöder en-till-många-relationer, dvs kräver att en av tabellerna inte har några dubbletter i kolumnen vi länkar på. Vi har samma i båda tabellerna i fältet Stad det finns upprepningar. Så vi måste skapa en annan mellanliggande uppslagstabell med en lista med unika stadsnamn från båda tabellerna. Det enklaste sättet att göra detta är med tilläggsfunktionen Power Query, som har byggts in i Excel sedan 2016-versionen (och för Excel 2010-2013 laddas den ned gratis från Microsofts webbplats).

3. Efter att ha valt valfri cell i den "smarta" tabellen, laddar vi dem en efter en i Power Query med knappen Från bord/sortiment fliken Data (Data — från tabell/intervall) och välj sedan på i Power Query-fönstret Den huvudsakliga lag Stäng och ladda – Stäng och ladda in (Hem — Stäng&Ladda — Stäng&Ladda till...) och importalternativ Skapa bara en anslutning (Skapa bara anslutning):

4. Vi sammanfogar båda tabellerna till ett med kommandot Data – Kombinera frågor – Lägg till (Data — Kombinera frågor — Lägg till). Kolumner med samma namn i rubriken kommer att passa under varandra (som en kolumn Stad), och de som inte matchar kommer att placeras i olika kolumner (men detta är inte viktigt för oss).

5. Ta bort alla kolumner utom kolumn Stadgenom att högerklicka på dess titel och välja kommandot Ta bort andra kolumner (Ta bort andra kolumner) och ta sedan bort alla dubbletter av stadsnamn genom att högerklicka på kolumnrubriken igen och välja kommandot Ta bort dubbletter (Ta bort dubbletter):

6. Den skapade referenslistan laddas upp till Datamodellen via Hem — Stäng och ladda — Stäng och ladda in (Hem — Stäng&Ladda — Stäng&Ladda till...) och välj alternativet Skapa bara en anslutning (Skapa bara anslutning) och det viktigaste! – slå på kryssrutan Lägg till dessa data till datamodellen (Lägg till denna data i datamodellen):

7. Nu kan vi, återgå till Power Pivot-fönstret (tab PowerPivot - knapp Verksamhetsledningen), byta till Diagramvy (Diagramvy) och länka våra tabeller över försäljnings- och transportkostnader genom den skapade mellanliggande katalogen över städer (genom att dra fält mellan tabellerna):

8. Nu kan du skapa alla nödvändiga pivottabeller för den skapade modellen med knappen Översiktstabell (pivottabell) on Den huvudsakliga (Hem) fliken i Power Pivot-fönstret och, genom att markera valfri cell i valfri pivot, på fliken Analys lägg till skiva-knappen Klistra in skiva (Analysera - Infoga skivare) och välj att dela i listrutan Stad i den tillagda katalogen:

Nu genom att klicka på den välbekanta knappen Rapportera anslutningar on Skivfliken (Slicer – Rapportera anslutningar) vi kommer att se alla våra sammanfattningar, eftersom de nu är byggda på relaterade källtabeller. Det återstår att aktivera de saknade kryssrutorna och klicka på OK – och vår slicer börjar filtrera alla valda pivottabeller samtidigt.