Analýza dat aplikace Excel: Vytvoření kontingenční tabulky aplikace Excel

Kontingenční tabulky Excel jsou tak velmi univerzální, protože umožňují snadno analyzovat souhrny velkého množství dat pomocí různých souhrnných funkcí (ačkoli součty vytvořené pomocí funkce SUM pravděpodobně zůstanou vaším starým pohotovostním režimem). Při nastavování původní kontingenční tabulky Excelu se rozhodujete o několika rozhodnutích: jakou souhrnnou funkci použít, na které sloupce (pole) se použije souhrnná funkce a se kterými sloupci (polemi) jsou tyto výpočty tabulkové.

Excelové kontingenční tabulky jsou ideální pro křížové řazení jedné sady dat v seznamu dat s jinou. Můžete například vytvořit kontingenční tabulku z databázové tabulky zaměstnanců, která shrnuje platy pro každou kategorii práce v křížové tabulce (uspořádané) podle oddělení nebo pracoviště.

Vytváření kontingenčních tabulek Excelu na základě doporučení

Pokud je pro vás vytvoření nové kontingenční tabulky pomocí nástroje Rychlá analýza příliš náročné, vygenerujete ji během okamžiku pomocí příkazového tlačítka Doporučené kontingenční tabulky. Chcete-li použít tuto metodu, postupujte podle těchto jednoduchých kroků:

Vyberte buňku v seznamu dat, pro kterou chcete vytvořit novou kontingenční tabulku.
Za předpokladu, že váš seznam dat obsahuje řadu záhlaví sloupců se souvislými řádky dat, může to být libovolná buňka v tabulce.

Vyberte příkazové tlačítko Doporučené kontingenční tabulky na kartě Vložit na pásu karet nebo stiskněte Alt+NSP.
Excel zobrazí dialogové okno Doporučené kontingenční tabulky. Toto dialogové okno obsahuje na levé straně pole se seznamem, které ukazuje ukázky všech navrhovaných kontingenčních tabulek, které může Excel 2019 vytvořit z dat ve vašem seznamu.

Vytvoření nové kontingenční tabulky z ukázkových kontingenčních tabulek zobrazených v dialogovém okně Doporučené kontingenční tabulky.

V seznamu nalevo vyberte ukázku kontingenční tabulky, kterou chcete vytvořit, a klikněte na OK.

Jakmile klepnete na OK, Excel vytvoří novou kontingenční tabulku následující po vybrané ukázce na vlastním listu (Sheet1) vloženém před ostatní v sešitu. Tato kontingenční tabulka je vybrána na novém listu, takže podokno úloh Pole kontingenční tabulky se zobrazí na pravé straně okna listu aplikace Excel a na pásu karet se zobrazí kontextová karta Nástroje kontingenční tabulky. K přizpůsobení nové kontingenční tabulky Excel můžete použít možnosti na tomto podokně úloh a kontextové kartě.

Ruční vytváření kontingenčních tabulek Excel

Někdy žádná z kontingenčních tabulek, které Excel 2019 navrhuje při vytváření nové tabulky pomocí nástroje Rychlá analýza nebo příkazového tlačítka Doporučené kontingenční tabulky, neodpovídá typu souhrnu dat, který máte na mysli. V takových případech můžete buď vybrat navrhovanou kontingenční tabulku, jejíž rozvržení se nejvíce blíží tomu, co máte na mysli, nebo se můžete rozhodnout vytvořit kontingenční tabulku od začátku (proces, který není tak obtížný ani časově náročný).

Chcete-li ručně vytvořit novou kontingenční tabulku z listu s daty, která mají být analyzována, umístěte ukazatel buňky někam do buněk tohoto seznamu a poté klikněte na příkazové tlačítko kontingenční tabulky na kartě Vložit na pásu karet nebo stiskněte Alt+NV.

Excel poté otevře dialogové okno Vytvořit kontingenční tabulku a vybere všechna data v seznamu obsahujícím kurzor buňky (označený rámečkem kolem oblasti buněk). Pak můžete upravit rozsah buněk v textovém poli Tabulka/Rozsah pod tlačítkem Vybrat tabulku nebo rozsah, pokud rámeček nezahrnuje všechna data pro shrnutí v kontingenční tabulce.

Ve výchozím nastavení Excel vytvoří novou kontingenční tabulku na novém listu, který přidá do sešitu. Pokud však chcete, aby se kontingenční tabulka objevila na stejném listu, klikněte na tlačítko Existující list a poté v textovém poli Umístění označte umístění první buňky nové tabulky. (Jen se ujistěte, že tato nová kontingenční tabulka nebude překrývat žádné existující tabulky dat.)

V dialogovém okně Vytvořit kontingenční tabulku uveďte zdroj dat a umístění kontingenční tabulky.

Pokud je zdrojem dat pro vaši kontingenční tabulku externí databázová tabulka vytvořená pomocí samostatného programu pro správu databází, jako je Access, musíte kliknout na tlačítko Použít externí zdroj dat, kliknout na tlačítko Vybrat připojení a poté kliknout na název připojení v dialogovém okně Existující připojení. Excel 2019 také podporuje analýzu dat z více souvisejících tabulek na listu (označovaném jako datový model). Pokud mají být data v nové kontingenční tabulce Excel, kterou vytváříte, analyzována spolu s jinou existující kontingenční tabulkou, nezapomeňte zaškrtnout políčko Přidat tato data do datového modelu.

Pokud v dialogovém okně Vytvořit kontingenční tabulku označíte jako umístění nové kontingenční tabulky nový list, po klepnutí na tlačítko OK program vloží nový list do přední části sešitu s prázdnou mřížkou pro novou kontingenční tabulku. Otevře také podokno úloh Pole kontingenční tabulky na pravé straně oblasti List a přidá na pás karet kontextovou kartu Nástroje kontingenční tabulky. Podokno úloh Pole kontingenční tabulky je rozděleno do dvou oblastí: seznam Vybrat pole k přidání do sestavy s názvy všech polí v seznamu dat, která můžete vybrat jako zdroj tabulky, před kterými jsou prázdná zaškrtávací políčka, a pole Přetažení mezi oblastmi pod oddíl rozdělený do čtyř zón přetažení (FILTRY, SLOUPCE, ŘÁDKY a HODNOTY).

Dokončená kontingenční tabulka Excel po přidání polí ze seznamu dat zaměstnanců do různých zón přetažení.

Můžete také vložit nový list s prázdnou mřížkou kontingenční tabulky výběrem tlačítka Prázdná kontingenční tabulka v dialogovém okně Doporučená kontingenční tabulka nebo na paletě možností nástroje Rychlá analýza (zobrazí se pouze v případě, že Rychlá analýza nemůže navrhnout kontingenční tabulky pro vaše data). Pamatujte, že když v tomto dialogovém okně nebo paletě vyberete tlačítko Prázdná kontingenční tabulka, Excel 2019 nejprve neotevře dialogové okno Vytvořit kontingenční tabulku. Pokud potřebujete při vytváření nové kontingenční tabulky použít kteroukoli z možností nabízených v tomto dialogovém okně, musíte kontingenční tabulku vytvořit pomocí příkazového tlačítka Kontingenční tabulka, nikoli pomocí příkazového tlačítka Doporučené kontingenční tabulky na kartě Vložit.

Chcete-li dokončit novou kontingenční tabulku Excel, stačí přiřadit pole v podokně úloh Pole kontingenční tabulky k různým částem tabulky. Provedete to přetažením názvu pole ze seznamu Vybrat pole k přidání do sestavy a jeho umístěním do jedné ze čtyř níže uvedených oblastí, které se nazývají zóny umístění:

• FILTRY: Tato oblast obsahuje pole, která vám umožňují listovat v souhrnech dat zobrazených ve skutečné kontingenční tabulce odfiltrováním sad dat – fungují jako filtry pro sestavu. Pokud například označíte pole Rok ze seznamu dat jako filtr sestavy, můžete v kontingenční tabulce zobrazit souhrny dat pro jednotlivé roky nebo pro všechny roky zastoupené v seznamu dat.
• SLOUPCE: Tato oblast obsahuje pole, která určují uspořádání dat zobrazených ve sloupcích kontingenční tabulky.
• ŘÁDKY: Tato oblast obsahuje pole, která určují uspořádání dat zobrazených v řádcích kontingenční tabulky.
• HODNOTY: Tato oblast obsahuje pole, která určují, která data jsou prezentována v buňkách kontingenční tabulky – jsou to hodnoty, které jsou shrnuty v jejím posledním sloupci (ve výchozím nastavení celkem).

Chcete-li pochopit, jak tyto různé zóny souvisí s kontingenční tabulkou, podívejte se na dokončenou kontingenční tabulku Excel.

U této kontingenční tabulky přiřadíte pole Pohlaví ze seznamu dat (pole, které obsahuje F (pro ženu) nebo M (pro muže), abyste uvedli pohlaví zaměstnance v zóně přetažení FILTRY. Také přiřadíte pole Odd (které obsahuje jména různých oddělení ve společnosti) do zóny přetažení SLOUPCE, pole Umístění (které obsahuje názvy různých měst s firemními kancelářemi) do zóny přetažení ŘÁDKŮ a pole Plat do zóny přetažení HODNOTY. Výsledkem je, že tato kontingenční tabulka nyní zobrazuje součet platů pro zaměstnance i ženy v každém oddělení (přes sloupce) a poté tyto součty uvádí podle jejich firemního umístění (v každém řádku).

Jakmile přidáte pole do nové kontingenční tabulky (nebo vyberete buňku existující tabulky v listu), Excel vybere kartu Analyzovat na kontextové kartě Nástroje kontingenční tabulky, která se automaticky zobrazí na pásu karet. Mezi mnoha skupinami na této kartě najdete na konci skupinu Zobrazit, která obsahuje následující užitečná příkazová tlačítka:

• Seznam polí, chcete-li skrýt a znovu zobrazit podokno úloh Seznam polí kontingenční tabulky na pravé straně oblasti List
• +/– Tlačítka pro skrytí a opětovné zobrazení tlačítek pro rozbalení (+) a sbalení (-) před konkrétními sloupcovými poli nebo řádkovými poli, která umožňují dočasně odstranit a poté znovu zobrazit jejich konkrétní souhrnné hodnoty v kontingenční tabulce
• Záhlaví polí pro skrytí a opětovné zobrazení polí přiřazených k štítkům sloupců a štítkům řádků v kontingenční tabulce