Když se v tabulce hromadí stovky řádků, ruční sčítání a filtrování rychle přestane stačit. Kontingenční tabulka v Excelu umí z připravených dat vytvořit souhrn podle kategorií, období nebo dalších vlastností. V tomto návodu projdeme přípravu zdroje, sestavení pohledu, změnu výpočtů i aktualizaci výsledků.
Kontingenční tabulka není samostatná databáze ani náhrada správně vedené evidence. Je to přehledový nástroj, který z již existujících záznamů počítá souhrny a umožňuje měnit jejich uspořádání bez přepisování původních řádků. Stejný zdroj tak můžete jednou zobrazit podle regionů, podruhé podle měsíců a potřetí podle typu produktu.
Hodí se například při vyhodnocení prodeje, docházky, přihlášek na školení, výdajů nebo objemu dokončených úkolů. Pokud potřebujete připravit pravidelný měsíční přehled, můžete si pohled nastavit jednou a později pouze doplnit zdrojová data a tabulku obnovit.
Nejdříve připravte zdrojová data
Výsledek závisí na kvalitě zdroje. Každý řádek by měl představovat jeden záznam a každý sloupec jednu vlastnost. V evidenci objednávek může jeden řádek popisovat jednu položku objednávky; sloupce pak obsahují datum, číslo objednávky, region, kategorii, množství a tržbu. Kontingenční tabulka z těchto polí sestaví součty nebo počty.
- Jednoznačné záhlaví: každý sloupec pojmenujte odlišně, bez prázdných buněk v řádku názvů.
- Souvislá oblast: nevkládejte do zdroje mezisoučty, oddělovací řádky ani poznámky mezi záznamy.
- Jednotný zápis: stejné kategorie pište vždy stejným způsobem; „Praha“, „praha“ a „Praha “ mohou skončit jako různé položky.
- Správný datový typ: datum uložte jako datum a částku jako číslo, ne jako text s ručně připsanou měnou.
- Jedna hodnota v buňce: oddělte například jméno, oddělení a stav do vlastních sloupců.
Praktické zásady pro strukturu zdrojové tabulky rozebírá také náš článek jak vytvořit přehlednou tabulku v Excelu. Pokud zdroj často rozšiřujete, vyplatí se převést data na excelovou tabulku, například klávesovou zkratkou Ctrl+T. Při doplnění nových řádků se rozsah obvykle rozšíří automaticky a kontingenční tabulka se s ním snáze udržuje.
Jak vytvořit první kontingenční tabulku
Klikněte do připravených dat nebo označte celou oblast. Na kartě Vložení vyberte možnost Kontingenční tabulka. Excel nabídne rozpoznaný rozsah a umístění výsledku; pro první pokus bývá přehledné vložit tabulku na nový list. Před potvrzením zkontrolujte, že rozsah zahrnuje všechna data i záhlaví, ale neobsahuje titulní řádky nebo souhrny.
Po vytvoření se obvykle zobrazí prázdná kontingenční tabulka a seznam polí odpovídající názvům zdrojových sloupců. Pole přetahujete nebo zaškrtáváte do oblastí pro řádky, sloupce, hodnoty a filtry. Názvy a umístění panelů se mohou podle verze Excelu mírně lišit, základní princip ale zůstává stejný: vybíráte, podle čeho chcete data seskupit a jakou hodnotu chcete vypočítat.
Začněte jednoduchou otázkou. Chcete vědět, jaké tržby připadají na jednotlivé regiony? Přesuňte Region do oblasti řádků a Tržba do oblasti hodnot. Výsledkem je součet tržeb pro každou oblast. Když přidáte Kategorii do sloupců, uvidíte vedle sebe rozdělení podle kategorií. Změna pohledu nevyžaduje kopírování ani přeskupování původních záznamů.
Rozumějte oblastem řádky, sloupce, hodnoty a filtry
Řádky tvoří hlavní seznam kategorií, například regionů, zaměstnanců nebo typů kurzů. Sloupce rozdělí souhrn do srovnatelných skupin, například podle roku nebo stavu. Hodnoty obsahují vypočítávané údaje, nejčastěji součet částek, počet záznamů nebo průměrnou hodnotu. Filtry omezí celý pohled na vybranou část zdroje, například na jeden rok nebo konkrétní oddělení.
Nemusíte používat všechny oblasti najednou. Pro jednoduchou evidenci kurzů může stačit počet přihlášených podle názvu kurzu. Když chcete porovnat počty v jednotlivých měsících, přidejte měsíc do sloupců. Pokud pak potřebujete sledovat pouze firemní zakázky, vložte typ zákazníka do filtru. Začněte s jedním řádkovým polem a jednou hodnotou; další rozměr přidávejte až tehdy, když odpovídá konkrétní otázce.
Zkontrolujte, zda Excel počítá správnou funkci
Excel zvolí souhrn podle typu pole a obsahu buněk. U čísel bývá výchozí funkcí součet, u textu počet. Pokud se u částek zobrazí počet místo součtu, zkontrolujte, zda jsou hodnoty skutečně číselné a zda v nich nejsou mezery, měnové znaky nebo chybný formát. Ve zdroji opravte problém a kontingenční tabulku obnovte.
Funkci můžete změnit přes nastavení pole hodnot. Podle potřeby vyberte součet, počet, průměr, maximum nebo minimum. Průměr odpovídá jiné otázce než celkový součet; u docházky například můžete chtít počet návštěv, zatímco u nákladů spíše celkovou částku. Výsledek vždy ověřte na několika známých záznamech, abyste si potvrdili, že tabulka počítá právě to, co zamýšlíte.
U finančních hodnot nastavte přiměřený číselný formát, třeba počet desetinných míst a měnu. Čitelný formát nemění původní hodnotu, jen způsob jejího zobrazení. Pokud potřebujete srovnat podíl jednotlivých kategorií, můžete využít zobrazení hodnot jako procenta z celkového součtu. I v takovém případě se vyplatí ponechat jasný název a popisek, aby čtenář věděl, jaký základ procenta používají.
Seskupte datumy a zjednodušte dlouhé seznamy
Obsahuje-li zdroj správně uložená data, můžete je v kontingenční tabulce seskupit například po měsících, čtvrtletích nebo rocích. Klikněte na datum v přehledu a podle dostupných možností zvolte seskupení. Je-li v jednom sloupci kombinace textů a dat, nebo některé řádky obsahují prázdnou hodnotu, seskupení nemusí nabídnout očekávané možnosti. V takovém případě nejprve zkontrolujte zdroj.
Seskupení pomáhá také u číselných rozsahů nebo dlouhých seznamů kategorií, pokud chcete vytvořit větší přehledové celky. Nepoužívejte je bez rozmyslu: kategorie, které se v souhrnu spojí, musí mít pro čtenáře společný význam. Pojmenujte výsledné skupiny tak, aby bylo jasné, co zahrnují, a ověřte, že se v nich neztratily důležité rozdíly.
Filtry, řazení a průřezy
Filtry v záhlaví umožní zobrazit pouze část položek. Řadit můžete podle názvu i podle vypočtené hodnoty, například od nejvyššího součtu. U delších reportů se hodí průřez, tedy přehledné tlačítkové ovládání, kterým vyberete jednu nebo více kategorií. Pro časové údaje může některá verze nabídnout také časovou osu. Průřezy jsou užitečné i pro kolegy, kteří s kontingenční tabulkou běžně nepracují.
Filtr však mění to, co je v přehledu vidět, nikoli zdrojová data. Před odesláním reportu zkontrolujte, zda není aktivní výběr, který by vyloučil část výsledků. Pokud budete souhrn prezentovat, uveďte použitá kritéria nebo je ponechte viditelná v názvu reportu. Čtenář tak pozná, zda se dívá na všechny záznamy, nebo pouze na vybraný region, období či stav.
Formátujte přehled srozumitelně
Nechte vyniknout strukturu, nikoli ozdoby. Použijte stručné názvy polí a jednotný formát čísel. Upravte šířku sloupců, aby byly kategorie čitelné, a přidejte jasný název přehledu. Pokud tabulka slouží k pravidelnému reportování, sjednoťte barvy, popisky a umístění filtrů s ostatními výstupy týmu. Před tiskem nebo exportem zkontrolujte zalomení stránek a opakování záhlaví.
Kontingenční graf lze vytvořit z vybraného souhrnu, pokud potřebujete zdůraznit trend nebo srovnání. Zvolte typ grafu podle otázky: sloupce bývají vhodné k porovnání kategorií, čára pro změnu v čase. Omezte počet prvků a ověřte, že legenda, osy a jednotky dávají smysl. Graf by měl doplnit tabulku, ne skrýt její důležité podrobnosti.
Obnovujte data po změně zdroje
Kontingenční tabulka se nemusí sama přepočítat po každé úpravě zdrojových řádků. Po doplnění nebo změně dat použijte příkaz Obnovit, případně obnovte celý sešit, pokud v něm máte více souhrnů. Má-li být rozsah zdroje stabilní, převeďte jej na excelovou tabulku ještě před vytvořením kontingenční tabulky. Při práci s pevným rozsahem zkontrolujte, zda se po přidání dalších záznamů jeho hranice rozšířila.
Pokud změníte názvy sloupců, odstraňte pole, která už neexistují, nebo upravíte jejich obsah, obnovte tabulku a ověřte, že nedošlo k chybě. U reportu, který se opakuje každý měsíc, si poznamenejte krátký postup: kde jsou zdrojová data, kdo je aktualizuje, jak se provádí obnova a které souhrny je třeba zkontrolovat. Pro další tipy k opakovanému měsíčnímu výkaznictví navazuje článek jak připravit přehledný měsíční report v Excelu.
Typické potíže a rychlá kontrola
- Součet se zobrazuje jako počet: část hodnot může být uložená jako text. Opravte datový typ ve zdroji a obnovte přehled.
- Nové řádky chybí: ověřte zdrojový rozsah nebo používejte excelovou tabulku, která se při doplňování rozšiřuje.
- Jedna kategorie se rozdělila do více položek: sjednoťte zápis, odstraňte přebytečné mezery a překlepy ve zdroji.
- Výsledek působí neúplně: zkontrolujte aktivní filtry, skryté položky a prázdné buňky.
- Souhrn po úpravě neodpovídá datům: proveďte obnovu a porovnejte několik známých hodnot s původní evidencí.
U důležitého reportu si před sdílením projděte zdroj, nastavené filtry, výpočtovou funkci, formát čísel a datum poslední aktualizace. Je-li výstup určen dalším lidem, přidejte krátkou poznámku, co zobrazuje a jaké období pokrývá. Tím předejdete tomu, že bude správný souhrn vykládán jako úplný, přestože se vztahuje jen k vybranému filtru.
Příklad: měsíční přehled přihlášek na kurzy
Představte si evidenci s datem přihlášky, názvem kurzu, typem účastníka, oddělením a stavem. Po vložení dat do souvislé tabulky vytvořte novou kontingenční tabulku. Do řádků vložte kurz, do sloupců měsíc a do hodnot počet přihlášek. Filtr podle stavu vám umožní zobrazit například jen potvrzené účastníky. Druhý souhrn může ukázat počet přihlášek podle oddělení.
Než výsledek použijete pro plánování, zkontrolujte, zda každá přihláška představuje jeden záznam. Pokud je jedna osoba uvedena vícekrát, rozhodněte, zda chcete počítat přihlášky, nebo unikátní účastníky. Jde o různé ukazatele. Srozumitelný název přehledu a popisek období pomůže odlišit je od sebe. Když se v dalším měsíci přidají nové řádky, zdroj obnovte a ověřte, že se nový měsíc objevil v souhrnu.
Jak si usnadnit opakovanou práci
Jakmile máte funkční strukturu, uložte si kopii sešitu jako šablonu nebo sepište krátký návod pro kolegy. Zachovejte čistý zdroj a oddělte jej od výsledného přehledu. Pokud některé kroky pravidelně opakujete, můžete zvážit jejich automatizaci. Článek jak automatizovat práci v Excelu popisuje, jak přemýšlet o rutinních úlohách a které postupy se vyplatí zjednodušit.
Nezačínejte automatizací nepřehledných dat. Nejprve sjednoťte záhlaví, hodnoty a pravidla aktualizace. Poté si zapište opakované kroky a ověřte, které z nich lze bezpečně zkrátit. Automatický postup má přinést opakovatelný výsledek a měl by být srozumitelný tomu, kdo sešit převezme. U firemního týmu může být užitečné probrat konkrétní proces v rámci firemních kurzů MS Office.
Časté otázky
Je nutné umět složité vzorce?
Pro základní kontingenční tabulku ne. Potřebujete především dobře uspořádaná data a jasnou otázku, na kterou má souhrn odpovědět. Excel běžné součty, počty a průměry vypočítá podle vybraných polí. Vzorce mohou pomoci při přípravě zdroje, nejsou však podmínkou pro první přehled.
Proč se mi nová data po doplnění neobjevila?
Nejčastěji je potřeba kontingenční tabulku obnovit nebo rozšířit její zdrojový rozsah. Pokud pracujete s excelovou tabulkou, rozsah se při přidávání záznamů snáze přizpůsobí. Po obnově ověřte, že se nové položky skutečně promítly.
Lze vytvořit více pohledů ze stejné evidence?
Ano. Z jednoho zdroje můžete sestavit několik kontingenčních tabulek, z nichž každá odpovídá jiné otázce. Jedna může shrnovat částky podle regionů, druhá počty podle měsíců. U každé pojmenujte výpočet i použité období, aby se výstupy nepletly.
Začněte jednou konkrétní otázkou
Kontingenční tabulka je nejpřínosnější tehdy, když vám pomůže rychleji zodpovědět konkrétní otázku. Začněte u čistého zdroje, vyberte dvě nebo tři pole a ověřte, že souhrn odpovídá známým hodnotám. Potom přidejte další členění, filtr nebo graf, pokud opravdu pomohou vysvětlit výsledek. Pravidelná struktura a kontrola po aktualizaci jsou důležitější než složitý vzhled.
Pokud si osvojíte přípravu zdroje a rozmístění polí, můžete stejný postup použít pro evidenci objednávek, kurzů i týmových úkolů. U větších sešitů si udržujte přehled o filtrech a zdrojových rozsazích a stručně popište pravidla aktualizace. Tak zůstane report srozumitelný i tehdy, když jej po čase otevře někdo jiný.