Seskupovaní dat v kontingenční tabulce

Vysvětlíme si seskupování dat v tabulce a výpočtové pole v Excelu. Jako příklad použijeme soubor KT05.xlsx. Zdrojová data, ze kterých budeme tabulku vytvářet, vypadají takto:

Ukázka.

Ukázka.

Tabulka obsahuje přehled obchodních případů za čtyři obchodníky až do 31.3. Jsou zde zapsána všechna kalendářní data, kdy proběhly jednotlivé obchodní aktivity. To tabulku však velmi prodlužuje, potřebovali bychom vidět výsledky spíše po měsících. Vytvoříme si tedy kontingenční tabulku, kde použijeme toto uspořádání:

Ukázkové uspořádání kontingenční tabulky.

Ukázkové uspořádání kontingenční tabulky.

Data v tabulce se nám zobrazují takto:

Zobrazení dat z příkladu.

Zobrazení dat z příkladu.

Nyní kalendářní data seskupíme, abychom viděli v tabulce pouze výsledky podle měsíců. Umístíme kurzor na kterékoli kalendářní datum. V nabídce Nástroje kontingenční tabulky Možnosti potvrdíme na kartě Skupina příkaz Výběr skupiny.

Výběr skupiny.

Výběr skupiny.

Zobrazí se dialogové okno, které je již prakticky přednastavené:

Dialog pro seskupení dat.

Dialog pro seskupení dat.

Označíme zde v seznamu položku Měsíce a potvrdíme OK. Vzhled dat v tabulce se změní takto:

Zobrazení údajů po provedené změně.

Zobrazení údajů po provedené změně.

Kdykoli však budeme chtít opět rozpis podle kalendářních dat, můžeme na kartě Skupina potvrdit příkaz  Oddělit a tabulka se vrátí do předchozí podoby. Tabulku se seskupenými údaji si můžete prohlédnout na listě Seskupení v připojeném příkladu.

Výpočtová pole

Kontingenční tabulku můžeme dále využít pro výpočet odměny obchodníků – provize. Dejme tomu, že obchodník dostává za každý obchodní případ stabilní odměnu 150 Kč a dále dostane 2 % (tj. 0,02) z objemu obchodu. Vytvoříme výpočtové pole, které zobrazí výši provize. V nabídce Nástroje kontingenční tabulky je karta Nástroje. Na ní najdeme ikonu Vzorce a z její nabídky potvrdíme příkaz Výpočtové pole:

Výpočtové pole.

Výpočtové pole.

Zobrazí se dialogové okno, kde nastavíme parametry pro výpočet:

Parametery pro výpočtové pole.

Parametery pro výpočtové pole.

Do pole název zadáme výstižný název. Vzorec vytváříme tak, že v seznamu označíme pole a stiskneme tlačítko Vložit pole – tím se název pole přenese do vzorce. Matematická znaménka a číselné hodnoty zapisujeme do tohoto řádku z klávesnice. Dialogové okno zavřeme tlačítkem OK.

Jako další výpočtové pole do této tabulky můžeme ještě připojit výpočet průměrného finančního objemu na jeden obchodní případ. Vyvoláme opět dialogové okno Vložit výpočtové pole a vzorec sestavíme takto:

Použití vzorce ve výpočtovém poli.

Použití vzorce ve výpočtovém poli.

Průměr je podílem dvou čísel a vychází tedy většinou na desetinná místa. Desetinná místa v tomto sloupci tedy upravíme, aby se zde zobrazovala čísla celá. Poklepeme na nadpis „Součet z Průměr na 1 obch. případ“, tím se zobrazí dialogové okno Nastavení polí hodnot. Známe jej z předchozího pojednání, stiskneme tedy tlačítko Formát čísla a nastavíme v seznamu Druh položku Číslo a zadáme 0 desetinných míst. Výslednou tabulku si můžete prohlédnout na listě Provize.

Připomeňme si ještě, že nadpis pole můžeme rovněž změnit – buď opět vyvoláme dialogové okno Nastavení polí hodnot a do pole Vlastní název zapíšeme např. Ochodní případ – průměr nebo umístíme kurzor na buňku s nadpisem, stiskneme klávesu F2 a nadpis můžeme dle potřeby editovat.

Kontingenční tabulku můžeme ještě upravit  pomocí sbalení pole. K tomu využijeme kartu Aktivní pole, kterou najdeme v nabídce Nástroje kontingenční tabulkyMožnosti:

Sbalení pole.

Sbalení pole.

Aktivní pole zobrazuje název pole, na kterém je právě umístěn kurzor. Potvrdíme-li příkaz Sbalit celé pole, pak se skryjí podrobnosti právě aktivního pole. Tabulka může vypadat takto:

Ukázka tabulky se sbalenými poli.

Ukázka tabulky se sbalenými poli.

Jestliže příkaz potvrdíme ještě jednou, sbalí se úroveň pole Kategorie a bude viditelná pouze její nadřazená úroveň, tj. pole Obchodník:

Ukázka tabulky se sbalenými poli.

Ukázka tabulky se sbalenými poli.

Detaily polí si opět můžeme zobrazit klepnutím na ikonku se znaménkem + (před jménem obchodníka) nebo potvrzením příkazu Rozbalit celé pole na kartě Aktivní pole.

Autor textu: Jana Prokopová, Zdroj: Verlag Dashöfer