Kontingenční tabulky jsou výkonným nástrojem, který umožňuje rychle vytvářet výkazy o tisících, statisících nebo dokonce milionech řádků.
Pomocí kontingenčních tabulek můžete okamžitě změnit způsob analýzy:
- rychlým přesunem a změnou datových polí
- použití různých filtrů
- seskupování dat a vytváření nových metrik výpočtu
Práce s těmito tabulkami je velice intuitivní.
Jak mají vypadat zdroje dat
Předpokládejme, že chcete sestavit celkové měsíční náklady podle podkategorií nákladů s těmito parametry:
- možnost podrobnosti dat podle názvu zboží
- výběr filtrů kategorií produktů
- výběr města nákupu
- možnost filtrovat tabulku pomocí časové škály
Nejprve se ujistěte, že jsou základní data správně rozřazena:
#1. Každý sloupec musí obsahovat jeden datový typ - data musí být v poli s daty a mít formát data. Pole “Název” by nemělo obsahovat informace o městě nákupu.
#2. Není žádoucí ponechávat prázdné řádky. Souhrnná tabulka se vytvoří, ale vizuálně je přítomnost “prázdných” řádků nepříjemná. Lze je odstranit pomocí filtrů.
#3. Vyhýbejte se nečíselným hodnotám ve sloupcích, ze kterých budou sestaveny vypočítané metriky tabulky. Pokud taková data existují, budou při agregaci nastavena na nulu - a výsledná hodnota může být zkreslená.
#4. Jako zdroj použijte dynamické tabulky. Tímto způsobem nemusíte neustále měnit rozsah dat před aktualizací souhrnné tabulky. Jednoduchým a praktickým nástrojem, který toto umožňuje, jsou “Chytré tabulky” v Excelu.
Označte libovolnou buňku v již “chytré” tabulce a dejte jí vhodný název.
Jak vytvořit kontingenční tabulku
Surová data byla připravena, nyní vytvoříme souhrnnou tabulku. Přejdeme na záložku “Vložit” a v oddílů “Tabulky” vybereme možnost “Kontingenční tabulka” nebo “Doporučené kontingenční tabulky”.
Pokud kliknete na tlačítko “Doporučené kontingenční tabulky”, Excel vám nabídne své možnosti polí pro analýzu dat a tabulka se vytvoří v novém pracovním listu v sešitě.
Pokud vybereme tlačítko “Kontingenční tabulka” otevře se nabídka pro výběr dalších parametrů.
Projděme si body:
#1. Výběr tabulky, ze které chcete sestavit souhrnnou zprávu.
#2. Použití vnějších zdrojů dat - může jít o připojení přes vestavěné v Excelu (od verze 2013) prostředí Power Query nebo pomocí jiných doplňků. V našem případě není třeba tuto funkci používat.
#3. Vybereme list, na kterém budeme vytvářet tabulku.
#4. Schopnost integrovat data ve výkaz z několika tabulek do sestavy na základě vytváření vztahů mezi tabulkami, obdobně jako u relačních databází.
Klikněte na tlačítko OK. Tabulka je vytvořena.
Rozdělíme list do bloků a zjistíme, co je kde.
#1. “Analýza kontingenční tabulky”. Panel nástrojů, na kterém najdete další funkce pro zpracování a formátování dat.
#2. “Návrh”. Zde si můžete vybrat jiný styl tabulky nebo si vytvořit vlastní, přizpůsobit typ zobrazení dat, souhrnná pole a další užitečné funkce.
#3. Umístění souhrnné tabulky.
#4. Nastavení zobrazení seznamu polí tabulky.
#5. Rychlé vyhledávání sloupců nebo výpočtů.
#6. Sloupce a výpočty tabulky.
#7. Blok filtrů. Slouží k filtrování dat. Chcete-li například omezit výběr podle města nebo kategorie zboží. Přesunutím datového pole do tohoto bloku získáme řádek s rozevíracím seznamem pro výběr dat, na jehož základě budeme tabulku filtrovat.
#8. Blok sloupů. Slouží k vytvoření jedinečných názvů polí původní datové sady s možností jejich seskupení do více úrovní. Ty budou základem pro agregaci hodnot.
#9. Blok řádků. Jedinečné hodnoty vybraného datového pole. Pracuje stejným způsobem jako blok sloupců, pouze ve sloupci, který je úplně nalevo.
#10. Blok hodnot. Hlavní část souhrnné tabulky, ve které se výpočty provádějí agregací dat.
Chcete-li vytvořit tabulku, přetáhneme sloupce výchozích dat do požadovaných bloků.
Upozornění:
#1. Po přetažení pole “Datum” do bloku sloupců se data automaticky seskupí podle měsíců - v rozevíracím seznamu souhrnné tabulky můžete vidět hodnoty podle data.
Podle podmínky není potřeba sledovat tak podrobné detaily, takže tuto úroveň smažeme a necháme jen měsíce.
#2. Ve výchozím nastavení pro číselné hodnoty dochází k agregaci sčítáním, ale to lze změnit: klikněte na šipku požadovaného výpočtu a vyberte “Nastavení pole hodnot”.
Poté se otevře následující okno, ve kterém můžete vybrat požadovanou operaci výpočtu hodnot.
Můžete také použít nabídku “Nastavení pole hodnot”:
- Změňte jméno výpočtu. Pojmenujme pole “Součet”. Tento název se nesmí shodovat s existujícími názvy sloupců kontingenční tabulky.
- Proveďte další výpočty.
- Zvolte číselný formát hodnot. Dejme dvě desetinná místa a oddělovač číslic - vizuálně je to tak hezčí.
#3. Pohrajeme si s filtry - vybereme město "Kyjev" a kategorii "Potraviny", sbalíme názvy produktů v řádcích (klikneme pravým tlačítkem myši na ikonu "-" vlevo od názvu podkategorie v souhrnné tabulce).
Doplňkové funkce
Pokud zvýrazníte jakoukoli buňku patřící do kontingenční tabulky, zobrazí se na panelu záložek nová záložka, na které s ní můžete pracovat.
Podíváme se, co je k dispozici na záložce “Analýza kontingenční tabulky”.
#1. Zde můžete souhrnnou tabulku pojmenovat, nakonfigurovat základní možnosti zobrazení dat a vybrat další nastavení při tisku.
#2. Nastavení parametrů zvýrazněného pole hodnoty. Například, v mezisoučtu můžete místo součtu zobrazit jiný výpočet Excel. Nebo můžete hodnoty z mezisoučtu zcela skrýt.
Nyní jsou při rozbalení úrovně “Podkategorie” hodnoty řádků mezisoučtů prázdné, zatímco hodnoty sbalených součtů zůstávají zachovány.
#3. Funkce seskupování umožňuje seskupovat výbrané hodnoty do skupiny.
#4. Blok “Filtr” umožňuje vkládat do tabulky různé pohledy na data a připojovat se k filtrům kontingenční tabulky. Vytvoříme časovou škálu pro filtrování dat.
Vybereme 3 měsíce - období, za které chceme filtrovat data.
#5. Aktualizace dat: Tlačítko “Aktualizovat ” aktualizuje hodnoty aktivní souhrnné tabulky a tlačítko “Aktualizovat vše” aktualizuje všechny tabulky, které se v sešitě nacházejí.
#6. V bloku “Akce” se provádějí akce pro přesun a vymazání tabulky (filtry).
#7. Nástroj “Pole, prvky a sady” umožňuje vytvářet další výpočty v tabulce.
#8. Pole “Zobrazit” umožňuje skrýt/zobrazit seznam polí tabulky, tlačítek nasazení a záhlaví
Veškerý obchodní obsah v pohodlném formátu. Rozhovory, případy, Life hacking korp. světa - na našich sociálních sítích. Připoj se k nám!
Chcete se přihlásit k odběru novinek?
