Filtrovanie viacerých kontingenčných tabuliek súčasne

Pri vytváraní komplexných zostáv a najmä dashboardov v programe Microsoft Excel je veľmi často potrebné súčasne filtrovať viacero kontingenčných tabuliek naraz. Pozrime sa, ako sa to dá implementovať.

Metóda 1: General Slicer na filtrovanie pivot v rovnakom zdroji údajov

Ak sú pivoty zostavené na základe jednej tabuľky zdrojových údajov, najjednoduchším spôsobom je použiť ich na súčasné filtrovanie časť je grafický tlačidlový filter pripojený ku všetkým kontingenčným tabuľkám naraz.

Ak ho chcete pridať, vyberte ľubovoľnú bunku v jednom súhrne a na karte Analýza vyberte tím Prilepiť plátok (Analyzovať – vložte rezač). V okne, ktoré sa otvorí, začiarknite políčka pri stĺpcoch, podľa ktorých chcete filtrovať údaje, a kliknite OK:

Vytvorený prierez bude štandardne filtrovať iba pivot, pre ktorý bol vytvorený. Avšak pomocou tlačidla Nahlásiť pripojenia (Nahlásiť pripojenia) pútko Plátok (Plátky) do zoznamu filtrovaných tabuliek môžeme jednoducho pridať ďalšie súhrnné tabuľky:

Metóda 2. Všeobecná časť na filtrovanie súhrnov na rôznych zdrojoch

Ak vaše pivoty neboli zostavené podľa jednej, ale podľa rôznych tabuliek zdrojových údajov, potom vyššie uvedená metóda nebude fungovať, pretože v okne Nahlásiť pripojenia zobrazujú sa iba súhrny, ktoré boli vytvorené z rovnakého zdroja.

Toto obmedzenie však ľahko obídete, ak použijete Dátový model (podrobne sme sa mu venovali v tomto článku). Ak načítame naše tabuľky do Modelu a tam ich prepojíme, potom sa filtrovanie bude vzťahovať na obe tabuľky súčasne.

Povedzme, že máme dve tabuľky pre predajné a dopravné náklady ako vstupné údaje:

Predpokladajme, že stojíme pred úlohou zostaviť pre každú z nich vlastný súhrn a následne ich súčasne filtrovať podľa miest so spoločným strihom.

Robíme nasledovné:

1. Premena našich originálnych tabuliek na dynamické inteligentné tabuľky pomocou klávesovej skratky ctrl+T alebo príkazy Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku) a dať im mená tablProdaji и tabDoprava pútko staviteľ (Dizajn).

2. Načítajte postupne oba stoly do Modelu pomocou tlačidla Pridať do dátového modelu na karte Power Pivot.

Tieto tabuľky nebude možné priamo prepojiť v modeli, pretože zatiaľ čo Power Pivot podporuje iba vzťahy typu one-to-many, tj vyžaduje, aby jedna z tabuliek nemala žiadne duplikáty v stĺpci, na ktorý sa pripájame. To isté máme v oboch tabuľkách v teréne City existujú opakovania. Potrebujeme teda vytvoriť ďalšiu prechodnú vyhľadávaciu tabuľku so zoznamom jedinečných názvov miest z oboch tabuliek. Najjednoduchší spôsob, ako to urobiť, je funkcia doplnku Power Query, ktorá je zabudovaná do Excelu od verzie 2016 (a pre Excel 2010-2013 je stiahnutá zadarmo z webovej stránky Microsoftu).

3. Po výbere ľubovoľnej bunky v „inteligentnej“ tabuľke ich jednu po druhej načítame do Power Query pomocou tlačidla Z tabuľky/rozsahu pútko dátum (Údaje – z tabuľky/rozsahu) a potom v okne Power Query vyberte možnosť Zap Hlavné tímy Zavrieť a naložiť – Zavrieť a naložiť (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...) a možnosť importu Stačí vytvoriť spojenie (Iba vytvoriť spojenie):

4. Príkazom spojíme obe tabuľky do jednej Údaje – Kombinovať dopyty – Pridať (Údaje – Kombinovať dopyty – Pripojiť). Stĺpce s rovnakým názvom v hlavičke sa zmestia pod seba (ako stĺpec City), a tie, ktoré sa nezhodujú, budú umiestnené v rôznych stĺpcoch (to však nie je pre nás dôležité).

5. Odstráňte všetky stĺpce okrem stĺpca Citykliknutím pravým tlačidlom myši na jeho názov a výberom príkazu Odstráňte ostatné stĺpce (Odstrániť ďalšie stĺpce) a potom odstráňte všetky duplicitné názvy miest opätovným kliknutím pravým tlačidlom myši na záhlavie stĺpca a výberom príkazu Odstrániť duplikáty (Odstrániť duplikáty):

6. Vytvorený referenčný zoznam sa nahrá do dátového modelu cez Domov — Zavrieť a načítať — Zavrieť a načítať (Domov — Zavrieť&Načítať — Zavrieť&Načítať do...) a vyberte možnosť Stačí vytvoriť spojenie (Iba vytvoriť spojenie) a to najdôležitejšie! – začiarknite políčko Pridajte tieto údaje do dátového modelu (Pridajte tieto údaje do dátového modelu):

7. Teraz sa môžeme vrátiť do okna Power Pivot (karta powerpivot - tlačidlo management), prepnúť na Zobrazenie grafu (Zobrazenie diagramu) a prepojte naše tabuľky predajných a dopravných nákladov cez vytvorený prechodný adresár miest (presúvaním polí medzi tabuľkami):

8. Teraz môžete pomocou tlačidla vytvoriť všetky potrebné kontingenčné tabuľky pre vytvorený model súhrnná tabuľka (kontingenčná tabuľka) on Hlavné (Domov) v okne Power Pivot a výberom ľubovoľnej bunky v ľubovoľnom pivote na karte Analýza tlačidlo pridať rez Prilepiť plátok (Analyzovať – vložiť rezač) a v zozname vyberte možnosť rezu City v pridanom adresári:

Teraz kliknutím na známe tlačidlo Nahlásiť pripojenia on Karta Slice (Slicer – nahlásiť pripojenia) uvidíme celé naše zhrnutie, pretože sú teraz postavené na súvisiacich zdrojových tabuľkách. Zostáva povoliť chýbajúce začiarkavacie políčka a kliknúť na OK – a náš slicer začne filtrovať všetky vybrané kontingenčné tabuľky súčasne.