Excel pro podnikání: jak vytvořit kontingenční tabulku | Laba Czech
Blog
if locale != request.locale

Hledat

Excel pro podnikání: jak vytvořit kontingenční tabulku

Uživatelská příručka od analytika Laba.

cover-3-61d43ae954398381830139.webp

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 nových článků?

Jeden e-mail s nejlepším obsahem týdne. Přihlaste se k odběru, aby vám nic neuniklo.
Děkujeme za přihlášení k odběru!