Vytvoření kontingenční tabulky z více tabulek

Excel od verze 2013 nabízí užitečnou funkcionalitu nazývající se Datový model, který lze mj. využít při tvorbě kontingenčních tabulek. Datový model umožňuje vytvoření kontingenční tabulky z více tabulek a jejich propojení relacemi (vztahy).

Výchozí situace:

Řekněme, že bychom potřebovali vytvořit report z níže uvedených dat, přičemž bychom potřebovali sumarizovat celkové množství naskladněného zboží podle kategorií zboží a krajů. Níže uvedená tabulka ale členění dat na kraje a kategorie neumožňuje, tyto údaje jsou obsaženy v pomocných tabulkách na samostatných listech. Pokud byste neměli pomocné tabulky, je potřeba dle komplexnosti situace vytvořit vaše vlastní.

Na listech Kategorie zboží a Kraje je patrné, do jaké kategorie příslušné zboží patří a v jakém kraji se nachází sklad, který má uvedené zboží naskladněno.

Pro správné vytvoření kontingenční tabulky je potřeba jednotlivé zdrojové tabulky převést z obyčejných tabulek na „objektové“ tabulky (v tabulce zvolit kartu VLOŽENÍ, příkaz Tabulka, nebo zkratku CTRL + T) a ideálně tyto tabulky pro přehlednost pojmenovat. Tabulky jsem v našem případě pojmenoval Zboží (hlavní tabulka), Kategorie (tabulka definující kategorie zboží) a Kraje (tabulka definující, v kterém kraji se nachází příslušný sklad).

Nyní z kterékoli tabulky vytvoříme kontingenční tabulku. Pro pořádek ji v našem případě vytvoříme na „objektové“ tabulce na listu Zboží, tabulku máme pojmenovanou taktéž názvem Zboží.

Při tvorbě kontingenční tabulky je potřeba zatrhnout volbu Přidat tahle data do datového modelu (anglicky Ad this data to the Data Model). Pokud tuto volbu nezvolíte, nebude možné vytvořit mezi tabulkami relace.
V nově vytvořené prázdné kontingenční tabulce je potřeba vytvořit relace, které budou členit data z hlavní tabulky na kategorie a kraje. Vytvoření relací provedeme v kontextových NÁSTROJÍCH KONTINGENČNÍ TABULKY, kde na kartě ANALÝZA vybereme volbu Relace, viz obrázek níže.

Pokud se bude jednat o složitou strukturu, bude potřeba vytvořit relací více. V našem případě chceme vytvořit relace na dvě další tabulky a jejich sloupce. Propojujeme tedy:

• tabulku Zboží a sloupec Zboží na tabulku Kategorie a sloupec Zboží
• a tabulku Zboží, sloupec Pobočka na tabulku Kraje, sloupec Pobočka.

Výsledné nastavení relací v našem případě vypadá takto.

Pokud máme definované relace, stačí již velmi jednoduše vybrat pole sumarizační kategorie a pole, z kterého provedeme výpočet.
Všechna pole se nachází v Polích kontingenční tabulky na záložce VŠE.

Pozor: pokud předem nevytvoříte relace, výsledná kontingenční tabulka bude ukazovat nesprávné hodnoty. Relace lze vytvořit i dodatečně, ale nepovažuji to za systémové řešení.
Výsledek může vypadat například takto:

Příklad A: Kontingenční tabulka vytvořená z více zdrojů sumarizující finanční hodnotu zboží po kategoriích a krajích.

Příklad B: Kontingenční tabulka vyjadřující počty zboží po kategoriích a krajích.

Oba příklady kontingenčních tabulek jsou pro následnou snadnou manipulaci doplněny o průřezy (slicers) a časovou osu (timeline).
Oba příklady prosím považujte za principiální, v reálu bude zdrojová tabulka pravděpodobně mnohem více rozsáhlá.
Toto téma podrobně probíráme na našem školení kontingenčních tabulek. Výše uvedená data si můžete stáhnout a prakticky procvičit na příkladu RelaceExcelKT.xlsx.