Filtrování více kontingenčních tabulek současně

Při vytváření složitých reportů a zejména dashboardů v Microsoft Excelu je velmi často nutné současně filtrovat několik kontingenčních tabulek najednou. Podívejme se, jak to lze implementovat.

Metoda 1: Obecný průřez pro filtrování pivotů na stejném zdroji dat

Pokud jsou pivoty sestaveny na základě jedné zdrojové datové tabulky, pak je nejjednodušší použít je k současnému filtrování sekce je grafický tlačítkový filtr připojený ke všem kontingenčním tabulkám najednou.

Chcete-li jej přidat, vyberte libovolnou buňku v jednom ze souhrnu a na kartě Analýza vybrat tým Vložit plátek (Analyzovat – vložte kráječ). V okně, které se otevře, zaškrtněte políčka u sloupců, podle kterých chcete data filtrovat, a klikněte OK:

Vytvořený průřez bude ve výchozím nastavení filtrovat pouze pivot, pro který byl vytvořen. Nicméně pomocí tlačítka Přehled připojení (Nahlásit připojení) Karta Plátek (Plátky) do seznamu filtrovaných tabulek můžeme snadno přidat další souhrnné tabulky:

Metoda 2. Obecný řez pro filtrování souhrnů na různých zdrojích

Pokud vaše pivoty nebyly sestaveny podle jedné, ale podle různých tabulek zdrojových dat, pak výše uvedená metoda nebude fungovat, protože v okně Přehled připojení jsou zobrazeny pouze ty souhrny, které byly vytvořeny ze stejného zdroje.

Toto omezení však snadno obejdete, pokud použijete Datový model (podrobně jsme se mu věnovali v tomto článku). Pokud načteme naše tabulky do Modelu a tam je propojíme, pak se filtrování použije na obě tabulky současně.

Řekněme, že jako vstupní data máme dvě tabulky pro prodejní a přepravní náklady:

Předpokládejme, že stojíme před úkolem sestavit pro každý z nich vlastní souhrn a následně je současně filtrovat podle měst se společným střihem.

Provádíme následující:

1. Proměňte naše původní tabulky na dynamické chytré tabulky pomocí klávesové zkratky Ctrl+T nebo příkazy Domů – Formát jako tabulka (Domů — Formátovat jako tabulku) a dát jim jména tablProdaji и tabDoprava Karta Stavitel (Design).

2. Načtěte obě tabulky postupně do Modelu pomocí tlačítka Přidat do datového modelu na kartě Power Pivot.

Tyto tabulky nebude možné přímo propojit v Modelu, protože zatímco Power Pivot podporuje pouze vztahy one-to-many, tj. vyžaduje, aby jedna z tabulek neměla žádné duplikáty ve sloupci, na který se propojujeme. Totéž máme v obou tabulkách v terénu Město tam jsou opakování. Potřebujeme tedy vytvořit další přechodnou vyhledávací tabulku se seznamem jedinečných názvů měst z obou tabulek. Nejjednodušeji to uděláte pomocí funkce doplňku Power Query, která je v Excelu zabudována od verze 2016 (a pro Excel 2010-2013 je stažena zdarma z webu Microsoftu).

3. Po výběru libovolné buňky v „chytré“ tabulce je jednu po druhé načteme v Power Query pomocí tlačítka Z tabulky/rozsahu Karta Data (Data – z tabulky/rozsahu) a poté v okně Power Query vyberte zapnuto Hlavní týmy Zavřít a načíst – Zavřít a naložit (Domů — Zavřít&Načíst — Zavřít&Načíst do…) a možnost importu Stačí vytvořit spojení (Pouze vytvořit připojení):

4. Příkazem spojíme obě tabulky do jedné Data – Kombinovat dotazy – Přidat (Data — Kombinovat dotazy — Připojit). Sloupce se stejnými názvy v záhlaví se vejdou pod sebe (jako sloupec Město), a ty, které se neshodují, budou umístěny v různých sloupcích (to však pro nás není důležité).

5. Smazat všechny sloupce kromě sloupce Městokliknutím pravým tlačítkem na jeho název a výběrem příkazu Odstraňte další sloupce (Odstranit další sloupce) a poté odstraňte všechny duplicitní názvy měst opětovným kliknutím pravým tlačítkem na záhlaví sloupce a výběrem příkazu Odebrat duplikáty (Odstranit duplikáty):

6. Vytvořený referenční seznam se nahraje do datového modelu prostřednictvím Domů — Zavřít a načíst — Zavřít a načíst (Domů — Zavřít&Načíst — Zavřít&Načíst do…) a vyberte možnost Stačí vytvořit spojení (Pouze vytvořit připojení) a to nejdůležitější! – zaškrtněte políčko Přidejte tato data do datového modelu (Přidat tato data do datového modelu):

7. Nyní můžeme, vrátíme se do okna Power Pivot (karta Powerpivot - knoflík management), přepnout na Zobrazení grafu (Zobrazení diagramu) a propojte naše tabulky prodejních a dopravních nákladů prostřednictvím vytvořeného přechodného adresáře měst (přetažením polí mezi tabulkami):

8. Nyní můžete pomocí tlačítka vytvořit všechny potřebné kontingenční tabulky pro vytvořený model souhrnná tabulka (kontingenční tabulka) on Hlavní (Domov) v okně Power Pivot a výběrem libovolné buňky v libovolném pivotu na kartě Analýza tlačítko přidat plátek Vložit plátek (Analyzovat – vložit průřez) a v seznamu vyberte dělení Město v přidaném adresáři:

Nyní kliknutím na známé tlačítko Přehled připojení on Karta Slice (Slicer — Hlášení připojení) uvidíme celé naše shrnutí, protože jsou nyní postaveny na souvisejících zdrojových tabulkách. Zbývá povolit chybějící zaškrtávací políčka a kliknout na OK – a náš slicer začne filtrovat všechny vybrané kontingenční tabulky současně.