Több PivotTable egyidejű szűrése

Ha összetett jelentéseket és különösen irányítópultokat hoz létre a Microsoft Excel programban, nagyon gyakran szükséges egyszerre több pivot tábla egyidejű szűrése. Lássuk, hogyan lehet ezt megvalósítani.

1. módszer: Általános szeletelő ugyanazon adatforráson lévő pivotok szűrésére

Ha a pivotok egy forrásadattábla alapján épülnek fel, akkor a legegyszerűbb, ha egyidejűleg szűrjük őket. szakasz egy grafikus gombszűrő, amely egyszerre kapcsolódik az összes pivot táblához.

Hozzáadásához jelöljön ki egy cellát az összegzésben és a lapon Elemzés Válassz csapatot Paste Slice (Elemzés – Szeletelő beszúrása). A megnyíló ablakban jelölje be azon oszlopok jelölőnégyzeteit, amelyek alapján szűrni szeretné az adatokat, és kattintson rá OK:

A létrehozott szeletelő alapértelmezés szerint csak azt a pivotot szűri, amelyhez létrehozta. A gomb használatával azonban Kapcsolatok jelentése (Kapcsolatok jelentése) lap Szelet (szeletek) a szűrt táblázatok listájához egyszerűen hozzáadhatunk további összefoglaló táblázatokat:

2. módszer. Általános szelet a különböző forrásokból származó összefoglalók szűrésére

Ha a pivotok nem egy, hanem különböző forrásadattáblázatok szerint készültek, akkor a fenti módszer nem fog működni, mert az ablakban Kapcsolatok jelentése csak azok az összefoglalók jelennek meg, amelyek ugyanabból a forrásból készültek.

Ezt a korlátozást azonban könnyen megkerülheti, ha az adatmodellt használja (ezt ebben a cikkben részletesen tárgyaltuk). Ha a tábláinkat betöltjük a Modellbe, és ott linkeljük, akkor a szűrés mindkét táblára egyszerre vonatkozik.

Tegyük fel, hogy bemenő adatként két táblánk van az értékesítési és szállítási költségekhez:

Tegyük fel, hogy azzal a feladattal állunk szemben, hogy mindegyikhez készítsünk saját összefoglalót, majd egyidejűleg szűrjük azokat városok szerint, közös vágással.

A következőket tesszük:

1. Varázsolja eredeti táblázatainkat dinamikus intelligens asztalokká egy billentyűparancs segítségével Ctrl+T vagy parancsokat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása) és adj nekik nevet tablProdaji и tabKözlekedés lap építész (Tervezés).

2. Mindkét táblázatot egymás után töltse be a Modellbe a gombbal Hozzáadás az adatmodellhez a Power Pivot lapon.

Ezeket a táblákat nem lehet majd közvetlenül összekapcsolni a Modellben, mert míg a Power Pivot csak egy-a-többhöz kapcsolatokat támogat, vagyis megköveteli, hogy az egyik tábla ne legyen ismétlődés abban az oszlopban, amelyre hivatkozunk. Nálunk mindkét táblázatban ugyanaz van a mezőnyben Város vannak ismétlések. Tehát létre kell hoznunk egy másik közbülső keresési táblát mindkét tábla egyedi városneveinek listájával. Ennek legegyszerűbb módja a Power Query bővítmény, amely a 2016-os verzió óta be van építve az Excelbe (Excel 2010-2013 esetén pedig ingyenesen letölthető a Microsoft webhelyéről).

3. Miután kiválasztottunk egy cellát az „okos” táblán belül, egyenként betöltjük a Power Queryben a gombbal Asztalból/tartományból lap dátum (Adatok – táblázatból/tartományból) majd a Power Query ablakban válassza a Be lehetőséget A fő csapat Zárja be és töltse be – Zárja be és töltse be (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…) és importálási lehetőség Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása):

4. Mindkét táblát egyesítjük a paranccsal Adatok – Lekérdezések kombinálása – Hozzáadás (Adatok – Lekérdezések kombinálása – Hozzáfűzés). A fejlécben azonos nevű oszlopok egymás alá illeszkednek (mint egy oszlop Város), a nem egyezők pedig különböző oszlopokba kerülnek (de ez számunkra nem fontos).

5. Törölje az összes oszlopot, kivéve az oszlopot Városjobb gombbal kattintson a címére és válassza ki a parancsot Törölje a többi oszlopot (További oszlopok eltávolítása) majd távolítsa el az összes duplikált városnevet úgy, hogy ismét jobb gombbal kattintson az oszlop fejlécére, és válassza ki a parancsot Távolítsa el a másolatokat (Az ismétlődések eltávolítása):

6. A létrehozott referencialista feltöltődik az Adatmodellbe a következőn keresztül Kezdőlap — Zárja be és töltse be — Zárja be és töltse be (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…) és válassza ki az opciót Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása) és ami a legfontosabb! – kapcsolja be a jelölőnégyzetet Adja hozzá ezeket az adatokat az adatmodellhez (Adja hozzá ezeket az adatokat az adatmodellhez):

7. Most megtehetjük, visszatérve a Power Pivot ablakhoz (lap powerpivot - gomb Támogató), váltani Diagram nézet (Diagram nézet) és kapcsolja össze értékesítési és szállítási költségtáblázatainkat a létrehozott köztes városi címtáron keresztül (mezők áthúzásával a táblázatok között):

8. Most a gomb segítségével létrehozhatja az összes szükséges pivot táblát a létrehozott modellhez összefoglaló táblázat (Pivot tábla) on A fő (Itthon) lapon a Power Pivot ablakban, és bármelyik cella kiválasztásával a lapon Elemzés szelet hozzáadása gombot Paste Slice (Elemzés – Szeletelő beszúrása) és válassza ki a szeletelést a listamezőben Város a hozzáadott könyvtárban:

Most az ismerős gombra kattintva Kapcsolatok jelentése on Szelet fül (Szeletelő – Kapcsolatok jelentése) látni fogjuk az összes összefoglalónkat, mert ezek most kapcsolódó forrástáblázatokra épülnek. Továbbra is be kell kapcsolnia a hiányzó jelölőnégyzeteket, és kattintson a gombra OK – és a szeletelőnk egyszerre kezdi el szűrni az összes kiválasztott pivot táblát.