Kontingenční tabulka: souhrn tisíce řádků na pár kliknutí

Kontingenční tabulka seskupí řádky seznamu podle zvolených sloupců a pro každou skupinu spočítá souhrn – součet, počet nebo průměr. Z tisíce řádků objednávek tak během chvíle vznikne přehled kusů podle zákazníků a měsíců, a to bez jediného vzorce a bez zásahu do původních dat. Podmínkou je zdroj ve tvaru seznamu se záhlavím v jednom řádku.

V Excelu se vkládá z karty Vložení, v Tabulkách Google a v LibreOffice Calc z nabídky Vložit. Princip je ve všech třech programech shodný.

Čtyři oblasti: řádky, sloupce, hodnoty a filtry

Po vložení se objeví seznam polí, tedy názvů sloupců ze zdroje, a čtyři oblasti, do kterých se pole přetahují. Pole v oblasti Řádky vytvoří popisky po levé straně: přetáhnete-li tam Zákazníka, dostane každý zákazník jeden řádek, ať se ve zdroji vyskytuje jednou nebo stokrát. Oblast Sloupce dělá totéž vodorovně. Do Hodnot patří to, co se má počítat, obvykle číselný sloupec jako Počet kusů. Filtry pak omezí celou tabulku na vybranou část dat, například na jednu prodejnu.

Začal bych vždy jen jedním polem v řádcích a jedním v hodnotách. Další rozměr se přidává snadno, kdežto tabulka se třemi poli v řádcích a dvěma ve sloupcích bývá širší než obrazovka a nikdo ji nečte. Pokud potřebujete porovnávat dvě hlediska, dejte to s menším počtem položek do sloupců – měsíců je dvanáct, zákazníků mohou být stovky.

Užitečný detail Excelu: dvojklik na kterékoli číslo v oblasti hodnot vytvoří nový list se všemi zdrojovými řádky, ze kterých číslo vzniklo. Je to nejrychlejší způsob, jak ověřit podezřele vysoký součet.

Součet, počet, nebo průměr: volba souhrnné funkce

Program vybírá souhrnnou funkci sám podle obsahu sloupce. Je-li sloupec čistě číselný, nabídne Součet. Jakmile v něm najde text nebo prázdnou buňku, přepne na Počet – a výsledkem jsou čísla, která vypadají věrohodně, jen neříkají to, co čekáte. Záhlaví hodnot proto čtěte: „Součet z Počet kusů“ je něco jiného než „Počet z Počet kusů“.

Funkci změníte v nastavení pole hodnot, kde jsou k dispozici také Průměr, Maximum a Minimum. Stejné pole lze do hodnot vložit dvakrát a jednou ho sčítat, podruhé zobrazit jako podíl z celku v procentech. U průměru bych byl opatrný. Kontingenční tabulka průměruje jednotlivé řádky zdroje, takže průměrná cena za kus vyjde jako prostý průměr řádků bez ohledu na to, kolik kusů se na kterém řádku prodalo.

Seskupení dat podle měsíců a let

Sloupec s datem by v řádcích vytvořil položku pro každý den. Proto existuje seskupení: pravým tlačítkem na libovolné datum v tabulce a volbou Seskupit vyberete měsíce, čtvrtletí nebo roky. Novější verze Excelu seskupí datum samy hned po přetažení pole.

Dvě věci tu lidi zaskočí. Seskupíte-li jen podle měsíců a data pokrývají víc let, sečte se leden jednoho roku s lednem roku dalšího; je nutné zaškrtnout měsíce i roky současně. A druhá: seskupení odmítne pracovat, pokud je ve sloupci byť jediná prázdná buňka nebo datum zapsané jako text. Hláška o tom, že vybrané objekty nelze seskupit, tedy téměř vždy ukazuje na chybu ve zdroji, ne v kontingenční tabulce.

Proč se po změně zdroje nic nepřepočítá

Excel si při vytvoření kontingenční tabulky uloží kopii dat do mezipaměti a pracuje s ní. Když ve zdroji opravíte číslo, souhrn se nezmění, dokud nezvolíte Aktualizovat – najdete to v místní nabídce pod pravým tlačítkem nebo pod zkratkou Alt+F5. V LibreOffice Calc slouží stejnému účelu příkaz Obnovit. Tabulky Google se naopak přepočítávají průběžně.

Horší případ nastane, když přibydou nové řádky pod původní oblastí. Aktualizace je nenačte, protože zdroj je zapsaný jako pevný rozsah buněk a končí tam, kde končil v den vytvoření. Podle mě je jediné rozumné řešení převést seznam předem na excelovou tabulku příkazem Formátovat jako tabulku a kontingenční tabulku postavit nad ní. Rozsah pak roste s daty a po aktualizaci se nové řádky objeví samy. Jak takový seznam připravit, popisuje článek o tom, jak v tabulkovém procesoru vést seznam, ze kterého jde udělat přehled.

Prázdné řádky a čísla uložená jako text

Položka nazvaná „(prázdné)“ v řádcích znamená, že zdroj obsahuje řádky bez hodnoty v daném sloupci – často proto, že byl jako zdroj označen celý sloupec listu včetně nevyplněného zbytku. Úplně prázdný řádek uprostřed dat má jiný následek: při automatickém určení oblasti se zdroj zastaví nad ním a polovina dat chybí, aniž by program cokoli ohlásil.

Čísla uložená jako text se poznají podle zarovnání k levému okraji buňky a typicky přicházejí z exportů jiných systémů. V hodnotách se místo sčítání jen počítají, v řádcích se řadí abecedně, takže 100 skončí před 20. Kde takové hodnoty vznikají a jak jim předejít už při načítání, rozebírám u importu souborů CSV. Dodatečně pomůže nástroj Text do sloupců nebo vynásobení sloupce jedničkou.

Za nejspolehlivější kontrolu považuji srovnání celkového součtu kontingenční tabulky se součtem zdrojového sloupce. Když se obě čísla liší, je chyba ve zdroji nebo v jeho rozsahu, a souhrn nemá smysl dál zdobit ani z něj kreslit graf. Udělejte to u každé nové kontingenční tabulky jako první věc, zabere to půl minuty.

Zdroje

• Vytvoření kontingenční tabulky k analýze dat listu, Microsoft Support
• Seskupení nebo oddělení dat v kontingenční tabulce, Microsoft Support
• Vytváření a používání kontingenčních tabulek, Nápověda Editorů Dokumentů Google
• LibreOffice Calc Guide – kapitola Pivot Tables, The Document Foundation