Kontingenční tabulka | Vše, co o nich potřebujete vědět | Pivot tables

Kontingenční tabulka v Excelu shrnuje obrovské množství dat do přehledných tabulek bez toho, abyste se museli trápit s excelovými funkcemi. Mimo to je kontingenční tabulka a tvorba kontingenční tabulky i častým tématem u pracovních pohovorů, kde se od vás očekává práce v Excelu. V dnešním videu si projdeme vše, co byste o tvorbě kontingenční tabulky měli vědět, včetně zásad správné práce s kontingenčními tabulkami. Ukážu vám, jak si připravit data pro kontingenční tabulku, jak vytvořit kontingenční tabulku, jak kontingenční tabulku zformátovat, jak v ní počítat a dokonce jak vložit do kontingenční tabulky dynamické filtry neboli průřezy.

Excelový soubor ke stažení:

Kontingenční tabulky základy

Kontingenční tabulky vám přináší ucelený pohled na data, ze kterého se můžeme dozvědět informace, které v původních datech pouhým okem nevidíte, a to za zlomek času, než kdybyste ke stejnému výsledku použili excelové funkce. Kontingenční tabulky jsou většinou i základem pro tvorbu interaktivních excelových dynamických přehledů neboli dashboardů.

Pro vytvoření kontingenčních tabulek a jejich správné fungování musíte dodržet několik základních pravidel.

Kontingenční tabulka má určité předpoklady:

  • Tabulkový formát dat – Zdrojová tabulka by měla mít tabulkový formát, to znamená, že každý sloupec má svůj vlastní nadpis a datový typ
  • Každý sloupec má vlastní datový typ – To znamená, že nemáme v jednom sloupci zároveň číselné hodnoty spolu s textovými, nebo datumy spolu s textem
  • Sloupce by vždy měly mít název v záhlaví tabulky – Pokud vaše data nemají v tabulce záhlaví s názvem, tak kontingenční tabulka doplní vlastní záhlaví s názvy Sloupec 1, sloupec 2, atd
  • Ve zdrojové tabulce by neměly být žádné prázdné sloupce a řádky
  • Ve zdrojové tabulce nesmějí být žádné sloučené buňky
  • Stejně tak ve zdrojové tabulce nesmějí být žádné mezisoučty a celkové součty

Jak vytvořit kontingenční tabulku?

Kontingenční tabulka se tvoří tak, že si klikneme do tabulky a v horní liště Excelu na kartě Vložení se vybere možnost Kontingenční tabulka. Existují dvě možnosti jak vytvořit kontingenční tabulku, a to z oblasti nebo z tabulky.

Pokud vybereme možnost z oblasti, tak se vám označí celá tabulka se zdrojovými daty. Nicméně nevýhodou tohoto přístupu je, že rozsah tabulky je ve zdroji kontingenční tabulky natvrdo zafixovaný, to znamená, že pokud v budoucnosti přidáte nová data do zdrojové tabulky, tak se kontingenční tabulka automaticky neaktualizuje.

Mnohem lepší je vytvořit z tabulky zdrojových dat oficiální excelovou tabulku, a to ještě před vytvořením samotné kontingenční tabulky. Tabulku zdrojových dat si označíte tím, že do ní kliknete myší a za pomoci klávesové zkratky CTRL+A ji celou označíte a přes klávesovou kombinaci CTRL+T převedete tabulku na oficiální excelovou tabulku. Největší výhodou tvorby kontingenční tabulky tímto způsobem je to, že oficiální excelová tabulka používá jako referenci jméno tabulky a nikoliv její rozsah. Pokud tedy změníte zdrojová data na oficiální excelovou tabulku a na kartě Vložení vyberu opět Vložit kontingenční tabulku, tak se následně vybere možnost nikoliv z oblasti, ale z tabulky. Do zdroje se nezafixuje rozsah tabulky, ale její název. A proto se kontingenční tabulka, která odkazuje na oficiální excelovou tabulku automaticky aktualizuje.

Odkud si kontingenční tabulka bere data?

Pokud potřebujeme zjistit, odkud si kontingenční tabulka bere data, tak to uděláte z horní lišty v Excelu z karty Analýza kontingenční tabulky. Zde vyberete možnost Změnit zdroj dat a přepnete se na tabulku, odkud si naše kontingenční tabulka bere data.

Pokud jste vytvořili kontingenční tabulku přes oblast, tak v poli Tabulka/Oblast uvidíte rozsah kontingenční tabulky. Pokud jste kontingenční tabulku vytvořili přes tabulku, tak v poli Tabulka/Oblast uvidíte název zdrojové tabulky.

Možnost vytvořit kontingenční tabulku z Oblasti je tak vhodné v případě, kdy víte, že se vám zdrojová tabulka nebude rozrůstat o nová data. V opačném případě je lepší zvolit tvorbu kontingenční tabulky z oficiální excelové tabulky.

Aktualizace dat ve zdrojové tabulce

Pokud je vaše kontingenční tabulka vytvořená přes oblast, a následně jste do zdrojové tabulky dodali nová data, tak na horní liště v Excelu vyberete možnost Analýza kontingenční tabulky a možnost Změnit zdroj dat. V poli Tabulka/Oblast vidíte aktuální rozsah tabulky. Pokud potřebujete rozsah tabulky rozšířit, tak stačí kliknout do pole Tabulka/Oblast a znovu označit zdrojová data, tentokrát už i o nově přidaná data.  

Kontingenční tabulka – vložení

Máte na výběr, zda bude kontingenční tabulka vložená na existující list nebo na nový list. Vždy je lepší vkládat kontingenční tabulku na samostatný list, aby se nám nemíchala zdrojová data se samotnou kontingenční tabulkou. Pokud máte v plánu tvořit více kontingenčních tabulek, tak je dokonce lepší každou kontingenční tabulku vložit na samostatný list.

Kontingenční tabulka totiž při analýze dat různě mění tvar, zvětšuje svůj rozsah nebo se zmenšuje, na základě toho, jak s daty v kontingenční tabulce pracujete. Pokud máte více kontingenčních tabulek na jednom listu blízko u sebe, tak hrozí, že by se vám mohly kontingenční tabulky překrývat, což Excel nedovolí a vyhodí vám chybovou hlášku.

Pokud byste chtěli kontingenční tabulku vložit již na nějaký existující list v Excelu, tak vyberete možnost vložit na existující list a na daném listu vyberete buňku, ve které chcete, aby kontingenční tabulka začínala.

Hned po vložení kontingenční tabulky na list je nejlepší tuto kontingenční tabulku pojmenovat. Kontingenční tabulka se pojmenuje ze záložky Analýza kontingenční tabulky, kde vlevo vidíte pole Název kontingenční tabulky. Na rozdíl od pojmenování oficiálních excelových tabulek, v jejichž názvu se nesmí vyskytovat mezery, tak u názvů kontingenčních tabulek mezery mít můžete.

Rozložení kontingenční tabulky

Po vložení kontingenční tabulky se vám vpravo zobrazí Pole kontingenční tabulky, což je nejdůležitější ovládací prvek kontingenční tabulky, kde se odehrává celé kouzlo kontingenčních tabulek. Tato ovládací část je tvořena pěti oblastmi. V poli vidíte jednotlivé názvy sloupců ze zdrojové tabulky. 

Práce s kontingenčními tabulkami je velmi jednoduchá a celá jednoduchost spočívá v tom, že jednotlivé sloupce ze zdrojové tabulky můžete přetahovat, tzv. drag and drop systém, do jednoho ze čtyř polí v dolní části.  

Nastavení pole zobrazení můžete měnit. Stačí vybrat ozubené kolo, neboli tlačítko pro nastavení v Poli kontingenční tabulky a vybrat si jinou variantu zobrazení polí kontingenční tabulky. Jednotlivá pole můžete rovněž myší zvětšovat a zmenšovat podle potřeby.

Někdy se vám u práce s tabulkami může stát, že vám ovládací panel Pole kontingenční tabulky zmizí. V takovém případě stačí pouze kliknout do jakékoliv kontingenční tabulky a ovládací pole se vám vrátí. Druhou možností je kliknout pravým tlačítkem myši do kontingenční tabulky a vybrat Zobrazit seznam polí. Stejně tak můžete ovládající pole i skrýt. Stačí zde vybrat možnost Skrýt seznam polí.

Kontingenční tabulka – trik pro rychlé vložení tabulky

Velmi snadný způsob, jak vložit kontingenční tabulku je i přes vložení doporučených kontingenčních tabulek. V tomto případě se vrátíte ke zdrojovým datům, kliknete do nich myší a na horní liště v Excelu vyberete místo Kontingenční tabulka Doporučené Kontingenční tabulky. Zde dostanete automaticky návrh, které kontingenční tabulky by z vašich dat bylo možné vytvořit. Pokud si nějakou kontingenční tabulku vyberete, tak ji označte a potvrďte OK. Následně můžete i tuto kontingenční tabulku dále upravovat.

Kontingenční tabulka – základy práce

Kontingenční tabulka se tvoří tak, že jednotlivé sloupce z oblastí přetahujeme do polí. Kontingenční tabulka má celkem 4 pole. Základem kontingenční tabulky jsou řádky a sloupce, samotnou kontingenční tabulku potom tvoří hodnoty. Nad rámec toho můžete v kontingenční tabulce vytvářet i filtry.

Řekněme, že chcete vytvořit jednoduchou kontingenční tabulku, kde se nám zobrazí produkty na řádcích, a tržby ve sloupcích. Chytnete tedy sloupec produkty a přetáhnete ho do pole Řádky, následně chytnete sloupec tržby a přesunete tento sloupec do pole Hodnoty. Kontingenční tabulka vložila agregovaná data ze zdrojové tabulky, a to dle hodnot na řádcích. V záhlaví kontingenční tabulky se rovněž automaticky vloží filtr tabulky. Tento filtr můžete vypnout z karty Analýza kontingenční tabulky, kde v poli Zobrazit vyberete Záhlaví polí. Tímto vám filtr kontingenční tabulky zmizí, stejným způsobem ho můžete i zase zapnout.

Tvar a zobrazení kontingenční tabulky měníte přetahováním sloupců do jednotlivých polí. Tím se vám kontingenční tabulka rozšiřuje nebo zmenšuje. Stejně tak můžete libovolné sloupce přetahovat do pole filtr a filtrovat zdrojová data.

Do jednoho pole můžete vložit i více sloupců, tím se tabulka stává členitější a strukturovanější.

Kontingenční tabulka – získání detailního náhledu dat

Pokud potřebujete zjistit z jakých dat se skládá agregovaná hodnota v kontingenční tabulce, tak stačí na vybranou hodnotu dvakrát kliknout myší. V excelovém souboru se vám vytvoří automaticky další list, na kterém se vám vyfiltrují data, ze kterých se skládá hodnota, na kterou jste poklepali myší. Velmi snadno se tak podíváte na vybraná data bez toho, abyste je museli složitě filtrovat ve zdrojové tabulce. Když jste s analýzou dat hotovi, můžete tento pomocný list v Excelu klidně smazat, vaše data to neovlivní, jelikož data do kontingenční tabulky se berou ze zdrojové tabulky.

Kontingenční tabulka – řazení dat

Jakékoliv číselné a textové hodnoty můžete v kontingenční tabulce řadit. Řádky a sloupce můžete filtrovat i ze záhlaví kontingenční tabulky. Stačí vybrat záhlaví a zde možnost seřadit data. Číselné hodnoty v poli hodnoty můžete jednoduše seřadit tím, že na hodnoty kliknete pravým tlačítkem a vyberete Seřadit. Zde dostanete na výběr seřadit data od nejmenší po největší nebo obráceně. Stejně tak zde máte možnost i vlastního řazení.

Kontingenční tabulka – formát

Po vložení kontingenční tabulky na list se vám na horní liště objeví nové záložky, ze kterých můžete formátovat nebo ovládat vaše kontingenční tabulky. Pro formátování kontingenční tabulky nás zajímá záložka Návrh.

Zde si můžete vybrat třeba barevný Styl kontingenční tabulky. Pokud by vám žádný barevný styl nevyhovoval, tak se můžete v možnosti Styly kontingenční tabulky vybrat možnost Návrh nového stylu a vytvořit si svou vlastní barevnou kombinaci.

Obecné barevné schéma kontingenční tabulky vychází z barevného schématu, který máte nastavený v Excelu. Nastavení barevné palety zjistíte z horní lišty Excelu ze záložky Rozložení stránky a Motivy a Barvy.

Formát čísla – pro formátování čísel v kontingenční tabulce klikněte na čísla pravým tlačítkem myši a vyberte možnost Formát čísla. Pokud vyberete Formát buněk, tak se formát v případě rozšiřování kontingenční tabulky nepřenese, proto je lepší používat možnost Formát čísla.

Přejmenování záhlaví – záhlaví v kontingenční tabulce můžete jednoduše přejmenovat tím, že na název kliknete a jednoduše ho přepíšete. Každý název v záhlaví musí být jedinečný. Pokud byste potřebovali mít více stejných názvů v tabulce, tak za/před název napište mezeru.

Kopírování, přesouvání kontingenční tabulky – kontingenční tabulka může být jednoduše přesouvána, kopírována a vkládána na nové listy. Stačí kontingenční tabulku označit a přesouvat, kopírovat tak, jako by se jednalo o klasickou tabulku.

Souhrny – z této záložky máte možnost vypnout nebo zapnout souhrnná data pro mezisoučty. Máte zde možnost Nezobrazovat souhrny vůbec, nebo máte na výběr zobrazit souhrny pro mezisoučty nad kategorií (Zobrazovat souhrny v horní části tabulky) nebo pod kategorií (Zobrazovat souhrny v dolní části tabulky). 

Celkové součty – z této záložky máte možnost ovládat zobrazení celkových součtů v kontingenční tabulce. Máte na výběr, zda chcete celkové součty zobrazit, stejně tak si můžete vybrat, že chcete celkové součty zobrazit pouze pro řádky nebo sloupce.

Rozložení sestavy – standardně se kontingenční tabulka zobrazuje v tzv. kompaktním rozložení, rozložení kontingenční tabulky můžete změnit v kartě Rozložení sestavy.

Aktualizace kontingenční tabulky

Obrovskou výhodou práce s kontingenčními tabulkami a oficiálními excelovými tabulkami je to, že můžeme data na jedno kliknutí aktualizovat. Pokud tedy do zdrojové tabulky vložíme nová data a přepneme se do kontingenční tabulky, tak máte dvě možnosti, jak data aktualizovat. Buď kliknete pravým tlačítkem do kontingenční tabulky a vyberete možnost Obnovit.

A nebo na kartě Analýza kontingenční tabulky vyberete možnost Aktualizovat.

Pokud přenastavíte šířku sloupců v kontingenční tabulce, tak se po každé aktualizaci kontingenční tabulky změní šířka sloupců na původní nastavení. Pokud tuto možnost chcete vypnout, tak klikněte pravý tlačítkem myši do kontingenční tabulky a vyberete Možnosti. Zde máte pole Při aktualizaci upravit šířky sloupců. Stačí toto pole odškrtnout a šířka sloupců se vám již nebude měnit.

Kontingenční tabulka a funkce

Kontingenční tabulka vám pomůže i s výpočty. Stačí na vybrané číselné hodnoty kliknout v poli Hodnota a vybrat možnost Nastavení polí hodnot. Zde si v nastavení můžete vybrat, zda chcete hodnoty zobrazit jako součty, počty nebo třeba průměry. Další možností je použít variantu Zobrazit hodnoty jako, kde můžete provádět i složitější procentuální kalkulace.

Průřezy v kontingenční tabulce

Ke každé kontingenční tabulce můžete vložit vlastní filtr, kterému se říká průřez. Pro vložení průřezu si klikněte do kontingenční tabulky, kterou chcete ovládat průřezem a z karty Analýza kontingenční tabulky a vyberte Vložit průřez. Průřez kontingenční tabulky je v podstatě filtr, který vám dovoluje filtrovat data z kontingenční tabulky. Excel vám nabídne, z kterých sloupců chcete vytvořit průřez. Můžete vybrat jedno pole, ale i více polí. Při výběru více polí se vám vloží více průřezů.

Kontingenční tabulka se bude filtrovat dle výběru, který provedete v průřezu. V každém průřezu můžete vybrat i více násobný výběr.

FAQ kontingenční tabulky

Kontingenční tabulky jsou speciální tabulky v Excelu, které vám poskytnou rychlý náhled na vaše data. Kontingenční tabulky vám dovolí obrovské množství dat analyzovat, agregovat a shrnovat rychle do přehledných tabulek.

Kontingenční tabulka v Excelu shrnuje obrovské množství dat do přehledných tabulek bez toho, abyste se museli trápit s excelovými funkcemi. Kontingenční tabulky vám přináší ucelený pohled na data, ze kterého se můžeme dozvědět informace, které v původních datech pouhým okem nevidíte, a to za zlomek času, než kdybyste ke stejnému výsledku použili excelové funkce. Kontingenční tabulka v podstatě agreguje velké množství dat. Navíc vám kontingenční tabulka dovoluje zobrazit i vztahy mezi veličinami. 

Kontingenční tabulka se vytvoří tak, že klikneme do zdrojové tabulky a na kartě Vložení vybereme možnost Kontingenční tabulka. Existují dvě možnosti, jak kontingenční tabulku vložit, a to buď na existující list nebo nový list v sešitu.  

Kontingenční graf je interaktivní graf, který můžete vytvořit z kontingenční tabulky. Pro vložení kontingenčního grafu stačí kliknout do kontingenční tabulky a na kartě Analýza kontingenční tabulky vybrat Kontingenční graf.

Pokud vám nejde ze zdrojových dat vytvořit kontingenční tabulka, může to být proto, že vám ve zdrojové tabulce chybí záhlaví, máte v tabulce prázdné řádky nebo sloupce a nebo nemáte zdrojovou tabulku ve správném formátu. 

MOHLO BY VÁS ZAJÍMAT

3 komentáře

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *