Příručka kontingenční tabulky – jak vytřískat z dat co nejvíce informací – PDF

Uvod

Tato příručka vznikla jako kompilace návodů týkajících se kontingenčních tabulek z webu www.vyuka-excelu.cz. Některé kapitoly na sebe tedy nemusí přesně navazovat nebo nemusí být úplně konzistentní, originální texty je možné najít přímo na www.vyuka-excelu.cz.

Tuto příručku je možné používat pro soukromé účely, není ale možné ji dále sdílet.

Kontingenční tabulky

Základní návod:

Přehled všech návodů

Příručka Kontingenční tabulky - jak vytřískat z dat co nejvíc informací je ke stažení tady.


Základní návod pro vytvoření kontingenční tabulky

Tento článek popisuje tvorbu základní kontingenční tabulky. Je vytvořený pro Excel 2016, ale obdobně platí pro všechny starší bězně používané verze Excelu.

Co to je kontingenční tabulka?

Kontingenční tabulka je v zásadě pohled na data, ze kterého se můžeme dozvědět informace, které v původních datech nevidíme. 

V našem příkladu vyjdeme z této tabulky, která popisuje přehled aut ve fiktivním autobazaru.

původní tabulka pro kontingenční tabulku

Díky kontingenční tabulce můžeme rychle odpovědět např. na následující otázky:

  • Kolik celkem stojí auta různých značek?
  • Kolik aut jsme naskladnili ve kterém měsíci?
  • Liší se nějak průměrné ceny dle barev?

V našem příkladu vyřešíme první otázku, tedy zjistíme celkové ceny aut dle značek.

Tabulka ke stažení a vyzkoušení je tady

Jak na to?

Začneme tak, že klikneme kamkoliv do tabulky - není třeba nic označovat. Dále klikneme v kartě Vložení (Insert) na Kontingenční tabuka (Pivot table).

vložení kontingenční tabulky

Následující dialog můžeme nechat jak je a jen ho potvrdit "OK". Pouze pokud bychom chtěli použít jiná data, než vybral Excel, vybereme je tady.

dialog při vkládání kontingenční tabulky

(O možnosti použít externí data (Use an external data source) více zde a o přidání dat do datového modelu zde)

Tímto vložením vznikne nový list s kontingenční tabulkou. Není třeba se tedy bát, že původní tabulka zmizela - můžeme se k ní vždy vrátit na původní list.

prázdná kontingenční tabulka po vložení

Všimněte si pravého sloupečku s nabídkou - nahoře jsou v řádcích vypsané názvy sloupců z původní tabulky. Tím, jak je budeme přesouvat do levé tabulky nebo do spodních obdélníků, budeme vytvářet / upravovat kontingenční tabulku.

Naším úkolem bylo zjistit, kolik celkem stojí auta, v rozdělení dle značek.

Proto přetáhneme "Značka" z horního obdélníku vpravo do obdélníku "Řádky" vpravo dole. Tím se v levé části tabulky vypíší všechny značky aut v seznamu.

vložená řádková pole

Teď ještě zjistit, kolik tyto značky dohromady stojí. Přetáhneme "Cena" do "Hodnoty".

kontingenční tabulka po vložení polí hodnot

Teď je již u každé značky vidět, kolik dohromady stojí auta této značky.

Teď si přidáme další úkol. Zajímá nás, kolik aut té které značky v seznamu je. Tedy ne kolik dohromady stojí, ale počet kusů.

Dvojklikneme na Součet z cena a v nabídce změníme Součet (Sum) na Počet (Count).

změna ze součtu na počet

K tomuto dialogu se lze dostat také kliknutím vpravo dole na Součet z Cena / Nastavení polí hodnot.

Pokud bychom chtěli obojí, součet i počet, přitáhneme do pole hodnot Cenu dvakrát - a jednou změníme součet na Počet. A to je všechno. Pár tipů navíc:

  • Když "zmizí" okno pro tvorbu kontingenční tabulky vpravo, stačí kliknout do tabulky - a zase se objeví.
  • Z tabulky je možno snadno kontingenční udělat graf - pouhým kliknutím na ikonku grafu a vybráním typu grafu.
  • Další návody týkající se kontingenčních tabulek

Video

Přiložený videonávod obsahuje zhruba to, co je popsané ve výše uvedeném textu, jen na jiných datech.


Kontingenční tabulky na jedné stránce

Návody na kontingenční tabulky patří na tomto webu k těm nejnavštěvovanějším. Pokusil jsem se tedy shrnout nejzákladnější logiku na jeden obrázek - pro ty, kteří neradi čtou dlouhé texty.

Snad to je přehledné. Pokud někoho napadne, jak tento návod vylepšit, napište to prosím do diskuse.

Klikněte pro stažení

kontingenční tabulky allin1-page-001 (1)


Kontingenční tabulky a nástroj Průřez / Slicer

Tento návod se týká nástroje Průřez / Slicer, který umožňuje rychle a velmi elegantně filtrovat ve vložené tabulce i kontingenční tabulce.

Příklad

Jsem v situaci, kdy mám kontingenční tabulku a potřebuji ji zobrazovat pro různé segmenty.
Například v této tabulce:
původní tabulka
sleduji pomocí kontingenční tabulky celkové ceny aut podle značek:
kontingenční tabulka vytvořená
ale chci je ještě dále segmentovat podle barev. 
Mohl bych to udělat filtrem, ale zdá se mi to moc pomalé.

Návod na průřez

Použiji nástroj Průřez - v anglické verzi Slicer.
V kartě Nástroje kontingenční tabulky a v kartě Možnosti vyberu Vložit průřez / Vložit průřez.
Zaškrtnu kategorii (sloupec v původních datech), podle kterého chci data zobrazovat. Kliknu na OK.
průřez v kontingenční tabulce
Pak klikám na jednotlivé řádky v Průřezu a tím filtruji hodnoty zobrazované v kontingenční tabulce. Tím se mi tabulka segmentuje. výsledek průřezu
Poznámky
  • Tabulku pro vyzkoušení je možné stáhnout tady
  • Průřez nepracuje se soubory ve formátu xls - pouze se soubory ve formátu xlsx. Pokud máte soubor ve formátu xls, stačí jej přeuložit do xlsx. To, že jste v xls poznáte mimo jiné podle toho, že Průřez je neaktivní.
  • Od verze Excelu 2013 je možné Průřez / Slicer používat i mimo kontingenční tabulku.
  • Pokud chci v Průřezu vybrat více položek, použiji klávesu Ctrl
  • Průřez může být jak svislý, tak vodorovný - změna je popsaná tady.

Kontingenční tabulky v Google Apps

Na těchto stránkách jsou sice uváděné především návody pro Excel, ale tento návod se výjimečně bude týkat tabulkového procesoru v Google Apps / Google Apps.

Ten funguje v mnoha ohledech podobně jako zjednodušený Excel, ale kontingeční tabulky se chovají trochu odlišně.

Vyjdeme z tabulky dostupné tady.

Vytvoření kontingenční tabulky

Podobně jako když vytvoříme kontingenční tabulku v Excelu, začneme tím, že do původních dat klikneme, a jdeme na Data / Přehled kontingenčních tabulek (proboha, kdo to překládal?).

Tím se objeví základní rozvržení kontingenční tabulky. Na rozdíl od Excelu ale vpravo nevidíme seznam záhlaví sloupců z původních dat a čtyři pole kam je vkládáme, ale jen seznam polí.

prázdná kontingenční tabulka

Řekněme, že v našem příkladu budeme nejprve chtít zjišťovat celkové ceny aut podle značek. Do řádkových polí tedy dáme značku.

Obrázek1

V tabulce už jsou vypsané všechny dostupné značky.

řádková pole kontingenční tabulky

My ale chceme sledovat jejich cenu. Do hodnot tedy přidáme cenu aut.

hodnoty v kontingenční tabulce

A je hotovo. Obdobně fungují i popisky sloupců nebo filtry, nemá moc smysl se o tom rozepisovat. Je však třeba varovat, že velká část nástrojů kontingenčních tabulek v Excelu v Google Docs v době psaní tohoto článku zatím nebyla. Třeba počítaná pole a položky nebo seskupování polí.


Kontingenční tabulky v Libre Office Calc

Na tomto webu už jsou návody na kontingenční tabulky v Excelu i v Google Docs. Přichází tedy čas na další aplikaci, která kontingenční tabulky umí - Libre Office (Libre Office beru jako aktuální větev Open Office - i když původní Open Office ještě existuje).

Začneme tím, že klikneme do původní tabulky s daty. Pak jdeme na Data / Pivot table / Create (tady si můžeme vzpomenout na Excel 2003, kde to bylo podobné). Výběr dat v kontingenční tabulce Libre Office V dalším dialogu můžeme potvrdit, že opravdu chceme zpracovávat vybranou oblast. Potvrzení výběru dat v kontingenční tabulce Zobrazí se dialog, který zhruba odpovídá Excelu. Důležitým rozdílem je, že v Excelu se změny projevují ihned, zatímco v Libre Office až po potvrzení. rozlozeni kontingencnich tabulek Takže do Row Fields dáváme to, podle čeho chceme členit směrem dolů, do Column Fields to, podle čeho chceme členit směrem doprava, a do Data Fields to, co chceme počítat. Page Fields používáme pro filtrování - což je mimochodem také pozůstatek starých verzí Excelu. hotova kontingencni tabulka Pro mně jako uživatele Excelu je trochu nešikovné, že když chci tabulku upravit, musím na ni kliknout pravým tlačítkem a dát Edit layout.


Počítaná pole (Calculated fields) v kontingenční tabulce

Příklad

Potřebuji v kontingenční tabulce zobrazit pole, které není v původních datech, ze kterých je tabulka vytvořená. Např. v této tabulce:

pocitana_pole_kontingencni_tabulky_zdrojova_data

jsou zakázky, kterých dosáhla firma. U každé zakázky je obchodník, který zakázku získal, a tržba za zakázku.

Ve firmě platí pravidlo, že každý obchodník, který získal v celém sledovaném období zakázky za pět a více milionů korun, dostane bonus 3% z celkových tržeb.

Obchodník, který získal zakázky za méně než pět milionů, nedostane nic. Naším úkolem je zjistit, jak velký bonus který obchodník získá.

Návod

Nejprve vytvořím obyčejnou kontingenční tabulku, ve které jsou zobrazené tržby za jednotlivé obchodníky.

prvni_verze_kontingencni_tabulky

Teď už tedy mám pole, od kterého se bude odvíjet výpočet bonusů. Následně jdu do karty Možnosti a v Pole, položky a sady vyberu Počítané pole.

navigace_pro_pocitana_pole

V následujícím dialogu si pojmenuji nové pole Bonus a do výpočtu zadám vzoreček, který se počítá.

Vzorečky, které používáme ve výpočtových polích, jsou obdobné jako standardní funkce. Tedy i funkce KDYŽ/IF má syntaxi, kterou známe. =když( Tržba>5000000;Tržba*0,03;0)

vzorec_pocitaneho_pole

Uvědomím si, že jsem v tabulce, která je (na základě toho, co jsem dal do řádkových polí) členěna podle jmen obchodníků. Proto i tržba, se kterou pracuji, je členěna podle jmen obchodníků.

Až tabulku budu členit podle něčeho jiného, bude se i tržba počítat podle něčeho jiného. Potvrdím a je hotovo - vidím tržby obchodníků i jejich bonusy.

vysledek_pocitaneho_pole_kontingencni_tabulky

Všimnu si, že v polích kontingenční tabulky, které mohu používat, mi přibyl Bonus - a rovnou se přidal do polí hodnot.

Z pilnosti pak mohu tabulku ještě nějak hezky naformátovat. Varování: Počítaná pole někdy nefungují společně se seskupenými poli.

Tabulka je ke stažení a k procvičení tady: 

pocitana_pole


Počítané položky (Calculated items) v kontingenční tabulce

Příklad

Kontingenční tabulku někdy potřebuji členit (řádkovými nebo sloupcovými poli) i podle kritérií, která nejsou obsažena v původních datech. Například v této tabulce:

opravena_puvodni_data

jsou tržby jednotlivých poboček určité firmy. U každé pobočky je informace o tom, v jaké zemi je, a jakých tržeb dosáhla. Bylo by velmi jednoduché udělat kontingenční tabulku, kde by byly tržby rozdělené podle států. Je chci ale tržby sledovat podle kontinentů. Chci tedy, aby výsledek vypadal takto:

hotove_pocitane_polozky

K tomu použiji počítané položky. Počítané položky fungují podobně jako počítaná pole, nicméně počítané položky se v zásadě týkají řádkových a sloupcových polí, zatímco počítaná pole se týkají polí hodnot.

Návod

Nejprve vytvořím jednoduchou kontingenční tabulku, kde sleduji tržby podle zemí.

pocitane_polozky_prvni_tabulka

Pak kliknu do hotové tabulky někam do řádkových polí (na název jednoho ze státu) a jdu na Nástroje kontingenční tabulky / Možnosti / Pole, položky a sady / Počítaná položka.

pocitane_polozky_cesta

V následujícím dialogu postupně nadefinuji, jak se počítají jednotlivé kontinenty. Začnu např. Evropou a napíšu (nebo naklikám) že Evropa je součtem ČR, Maďarska, Německa, Polska a Rakouska.

po_pocitanych_polozkach_pred_filtrem

Obdobně to provedu i s Asií a Amerikou. Potvrdím a vyjde mi takováto tabulka.

pridane_pocitane_polozky

Už mám kromě zemí i kontinenty s hodnotami odpovídajícími součtu zemí. Teď je čas zbavit se jednotlivých zemí. To udělám prostřednictvím obyčejného filtru.

filtrovani_pocitane_polozky

A tabulka je hotová.
 
hotove_pocitane_polozky (1)

Varování: Počítané položky někdy nejdou dělat společně se skupinovými poli.


Zobrazení procent v kontingenční tabulce

Tento článek je pouze o zobrazování procent v kontingenčních tabulkách. Není o běžných výpočtech procent, to je zase popsané jinde.

Příklad

Někdy v kontingenční tabulce potřebuji mít nejen součty (průměry, počty..) z hodnot, ale také například procentní podíl na celku. Jinými slovy nestačí mi toto:

tabulka_bez_procent

Ale potřebuji toto:

tabulka_s_procenty

Řešení

V tabulce dám do pole hodnot stejnou hodnotu dvakrát. Je to proto, že jedna mi bude později zobrazovat hodnoty, druhá procenta. (Samozřejmě kdybych chtěl pouze procenta a nepotřeboval hodnoty, stačí jednou.)

dva_sloupce

Po přidání hodnot jdu do jednoho ze sloupečků a kliknu v horní liště v kartě možnosti na Zobrazit hodnoty jako... a % z celkového součtu.

vyber_procent_v_menu
A je to.

Zobrazení procent nebo přírůstků v kontingenční tabulce

Tento článek je o počítání a zobrazování procent nebo přírůstků v kontingenční tabulce. Není o formátování procent ani o výpočtech s procenty.

Příklad

Ve své firmě sleduji tržby. V každém měsíci (1-12) mám několik tržeb. Základní data vypadají takto:

zdrojova_data_pro_slucovani (1)

Udělal jsem si kontingenční tabulku, ve které vidím celkové tržby za jednotlivé měsíce:

kontingencni_tabulka_pro_prirustky_a_procenta

Sleduji tedy, kolik jsem v jednotlivých měsících utržil celkově. Mě ale zajímá, kolik procent jsem utržil ve kterém měsíci (ne kolik celkově, ale jde mi o to, jak se který měsíc podílel na celoročních tržbách).

Jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / % z celkového součtu

procenta_v_kontingecni_tabulce

Takto vypadá výsledek:

kontingencni_tabulka_hotova_procenta

Mohlo by mě ale také zajímat, jaké přírůstky jsem v jednotlivých měsících realizoval proti předchozím měsícům. Pak jdu na Nástroje kontingenční tabulky / Možnosti / Zobrazit hodnoty jako / Rozdíl mezi...

kontingencni_tabulka_rozdil_mezi

Jako základní položku vyberu "Předchozí". Takto vypadá výsledek:

kontingencni_tabulka_hotova_s_prirustky

Také bych ale mohl chtít vidět v jedné kontingenční tabulce všechno najednou - tedy celkové hodnoty, procenta i přírůstky. Pak si prostě naskládám do Pole hodnot položku Tržba třikrát, jednomu sloupečku nenastavím nic, druhému procenta a třetímu přírůstky. Výsledek vypadá takto:

vsechny_zpusoby_zobrazeni_kontingencni_tabulky_dohromady

Příklad k vyzkoušení je tady: Stáhnout příklad k vyzkoušení procent nebo přírůstků v kontingenční tabulce


Funkce ZÍSKATKONTDATA / GETPIVOTDATA

Příklad

V buňce potřebuji odkazovat na hodnotu, která je v kontingenční tabulce. To je v principu problém - protože když s tabulkou pracuji, mění se její tvar a tak to, co bylo v určité buňce, může být za chvilku úplně jinde. Potřebuji tedy odkaz, který bude nezávislý na tom, jak je tabulka zrovna upravená.

O tom, jak tuto funkci použít v praxi, pojednává tento článek.

Návod

V takové situaci mi pomůže funkce ZÍSKATKONTDATA, anglicky GETPIVOTDATA.
Oproti jiným funkcím má jednu velkou výhodu - zadává se téměř sama. Prostě v buňce, která má na kontingenční tabulku odkazovat, napíšete =, a pak myší kliknete na příslušnou hodnotu kontingenční tabulky. A funguje to.
Pokud potřebujete funkci zapsat jinak, nadefinujete tyto parametry:

  • Datové_pole - co je v tabulce v poli hodnot
  • Kontingenční_tabulka - odkaz ně nějakou buňku z tabulky - doporučuji nějakou ze záhlaví nebo zápatí, jiné mohou zlobit
  • Pole1 (a další pole) - určuje, podle čeho se třídí
  • Položka 1 (a další položky) - určuje, co přesně se vybírá

Konkrétní zadání

Navazuje na popis kontingenční tabulky zde.
V tomto obrázku dostávám v kontingenční tabulce do buňky E5 celkovou cenu auta značky Citroen.
Získat data z kontingenční tabulky


Použití jednoho filtru nebo jednoho průřezu pro více kontingenčních tabulek

V tomto článku si ukážeme, jak na dashboardu současně filtrovat více kontingenčních tabulek.

Vyjdeme z úvodní tabulky. V té je seznam firem, u kterých budeme sledovat počty zaměstnanců dle kritérií města, zaměření a toho, jestli jsou nebo nejsou potenciálními zákazníky.

puvodni tabulka

Z této tabulky vytvoříme malý dashboard obsahující dvě kontingenční tabulky, Nejrychleji tak, že vytvoříme jednu, a tu rozkopírujeme. V každé tabulce nastavíme jiné členění (dáme jinou položku do řádkových polí). V našem případě jedna tabulka člení počty zaměstnanců podle měst a druhá podle oboru. 

dve kontingencni tabulky

K tabulkám můžeme ještě přidat grafy.

dve kontingencni tabulky s grafem

Teď bychom chtěli obě tabulky sledovat zvlášť podle toho, jestli se jedná nebo nejedná o potenciální klienty. Není problém dát k jedné z tabulek průřez, ale když jej používáme, aplikuje se jen na tuto jednu tabulku. 

prurez

Jak tedy zařídit, aby fungoval pro obě tabulky najednou? Kliknu na průřez, vyberu Připojení sestavy

pripojeni sestavy

a zaškrtám tabulky, ke kterým se má průřez vázat (tedy i druhou tabulku).

zaskrtnuti druhe KT

Od teď funguje jeden průřez pro více tabulek najednou, což je přehlednější a snazší pro ovládání.

prurez pro dve tabulky

Obdobně bude fungovat i filtr, pokud ho teď do tabulky přidám.

jeden filtr pro dve tabulky

 


Kontingenční tabulka pro zobrazení externích dat

Příklad

Většina z nás používá kontingenční tabulky k tomu, aby analyzovala data z excelové tabulky. Jinými slovy - máme velkou tabulku s daty a z ní si vyrábíme kontingenční tabulku ve stejném souboru na jiném listu.

V některých případech ale může být opravdu hodně užitečné mít v Excelu tabulku, která zobrazuje data z úplně jiného zdroje. Takovým datovým zdrojem může být účetní systém, ERP, jiný Excel,  databáze Oracle nebo MS SQL - víceméně jakákoliv běžná relační databáze.

Prakticky se tedy můžete připojit např. do účetnictví Vaší firmy a pomocí kontingenční tabulky analyzovat data.

Návod

V prázdném Excelu kliknu na Vložení - Kontingenční tabulka (nebo nějak podobně - dle verze). V následujícím dialogu vyberu Použít zdroj externích dat. Kliknu na Zvolit připojení.

vyber_datoveho_zdroje

Kliknu na Vyhledat další (samozřejmě jen v případě, že zatím žádné připojení nemám připravené)

Vyberu databázi (zde pro jednoduchost ukazuji accessovský soubor). Z nabídky vyberu tabulku nebo pohled, který chci analyzovat - teď už se mi zobrazují tabulky a pohledy z připojeného zdroje.

tabulky_a_pohledy

Klliknu na OK. A dále už mohu pracovat jako s jakoukoliv jinou kontingenční tabulkou.

hotova_tabulka

Chcete-li postup vyzkoušet, můžete použít tento accessovský soubor.


Kontingenční tabulka s automaticky přizpůsobovanou oblastí zdrojových dat

Kontingenční tabulky mají spoustu výhod a máme je rádi. Mají ale jeden dost nepříjemný zádrhel.

Vytvoříme například kontingenční tabulku z buněk A1 až D100. Všechno funguje, jak má, dokonce můžeme i změnit některé z hodnot v původních datech, pak stačí tabulku obnovit, a je zase aktuální.

Problém nastává, když k původním datům přidáme nové řádky, například řádek 101. Ten se ani po aktualizaci logicky nezohlední, protože kontingenční tabulka čerpá jen z řádků 1 až 100. Samozřejmě je možné po každém přidání řádku změnit zdrojová data, ale to je dost pracné.

Jsou v zásadě dvě možnosti, jak elegantně zařídit, aby se zdrojová oblast kontingenční tabulky měnila podle skutečného počtu řádků.

Funkce POSUN /OFFSET + POČET2

Můžeme zkombinovat funkci POSUN / OFFSET a POČET2. Tento postup je popsaný zde: http://www.excelentnitriky.com/2014/03/dynamicka-definice-oblasti-bunek.html V našem případě bychom zapsali: =POSUN(A1;0;0;POČET2(A:A);4) To je ale trochu složité, a navíc by nastal problém v případě, že by v tabulce byly prázdné řádky.

Vložení tabulky

Vkládání tabulek do Excelu je řešené v těchto dvou článcích:

Proč to zmiňuju tady? Uvažujte se mnou. Tabulka (myšleno vložená tabulka) je pojmenovanou oblastí, která se automaticky rozšiřuje při doplňování. Kontingenční tabulku je možné vytvořit nad vloženou tabulkou. Takže pokud vytvořím kontingenční tabulku nad vloženou tabulkou, bude se automaticky rozšiřovat i oblast vstupních dat tabulky.

Takže jak na to? Prostě vložíme tabulku, klikneme do ní, a vložíme kontingenční tabulku. Ta pak bude svoji oblast vstupních dat automaticky rozšířovat.    


Skupinová pole v kontingenční tabulce použitá pro shlukování datumů

Tento článek je o tom, jak v tabulce shlukovat datumy (např. dny) do větších celků (např. měsíců)

V této tabulce jsou jednotlivé prodeje mé firmy. Potřebuji zjistit, kolik jsem utržil za jednotlivé měsíce. Problém samozřejmě je, že znám sice datum, ale neznám konkrétní měsíc.

zdrojova_data_pro_slucovani

Kdysi jsem tento problém obcházel tak, že jsem v původních datech přidal další sloupec, do kterého jsem pomocí funkce MONTH (MĚSÍC) odvodil z data číslo měsíce.

Dá se to ale dělat i elegantněji.

Návod

Vytvořím základní kontingenční tabulku, kde mám v řádkových polích data a v polích hodnot

slucovani_datumu_pripravena_kongingencni_tabulka

Kliknu myší do některého z dat v kontingenční tabulce a v kartě Možnosti kliknu na Skupinové pole.

skupinove_pole_pro_datumy

Vyberu Měsíce (a třeba ještě Čtvrtletí) a kliknu na OK. A je hotovo.

vyber_mesicu_a_citvrtleti_pro_slucovaci_pole_kontingencni_tabulky

Obdobný postup se dá použít i pro sdružování čísel při tvorbě histogramů.



Časová osa v kontingenční tabulce

Časová osa je (od verze Excelu 2013) jedním z filtrů, které můžeme použít v kontingenční tabulce. Jedná se o:

  • Pole Filtry (jedno ze čtyř základních polí kontingenční tabulky)
  • Průřez (rychlý, klikací filtr)
  • Časová osa - popsaná v tomto článku (je dostupná až od verze Excelu 2013)

Jak použít nástroj Časová osa

Časová osa má smysl v kontingenčních tabulkách, které chceme filtrovat dle času.
Např. z této tabulky prodejů mohu chtít vytvořit kontingenční tabulku omezenou pouze na prodeje realizované v druhé polovině ledna 2012í:
 
casova_osa_puvodni_data
Začnu tím, že vytvořím základní kontingenční tabulku. Řekněme, že budu sledovat, jak velké tržby připadají na jednotlivé zaměstnance:
casova_osa_kontingencni_tabulka
Teď budu chtít omezit tabulku např. pouze na období druhé poloviny ledna 2012.
Kliknu na Analýza a dále na Vložit časovou osu a v dialogu zaškrtnu Den - to je jediné pole z tabulky, které obsahuje data.
casova_osa_definice_casove_osy
Už se mi vložila časová osa. Já ale potřebuji podrobnější členění než měsíce, proto se přepnu na dny.
casova_osa_zmena_mesicu_na_dny
Roztáhnu časovou osu na příslušné dny. Všimnu si, že čísla v tabulce se zmenšila - protože zobrazují tržby za kratší časové období než původní tabulka.
casova_osa_definice_obdobi
Dalším roztahováním si pak mohu toto období upravovat. Pokud máte raději videonávody, tak tenhle popisuje to, co je obsahem tohoto článku, v necelé minutě. Doporučuji zvětšit na celou plochu.

Dynamické řazení v kontingenční tabulce

Příklad

Potřebuji seřadit data. A potřebuji to udělat tak, aby se veškeré změny, které provedu v původních datech, projevily i v seřazených datech. Jinými slovy budu mít jedna data neseřazená a druhá data seřazená, dynamicky upravovaná podle měnících se hodnot prvních dat.

Např. v této tabulce jsou časy závodníků po jednotlivých kolech. Potřebuji průběžně sledovat jejich pořadí - současně s tím, jak přibývají časy za jednotlivá kola v tabulce.

zadani_kontingencni_tabulka_pro_razeni

Návod

  • Přidám součtový sloupec (pokud je třeba)
  • Přidám kontingenční tabulku (v tomto příkladu v řádkových polích budou jména závodníků a v polích hodnot pak součet výsledného času - sečtený však pouze z jedné hodnoty)
  • Seřadím tabulku podle součtového času
  • Pak už jen aktualizuji kontingenční tabulku po každé změně vstupních dat

Řešení spolu se zadáním je tady: http://www.vyuka-excelu.cz/wp-content/uploads/2014/10/vysledkova_listina_kontingencni_tabulka.xlsx


Kontingenční tabulka počítající unikátní, jedinečné hodnoty

V jednom reportu jsem narazil na zajímavý problém. Měl jsem seznam jazykových kurzů ve škole. Ve sloupečcích bylo jméno lektora, jazyk kurzu a nějaký identifikátor kurzu. Vypadalo  to zhruba takto:

Obrazek1

Úkolem bylo zjistit, kolik jazyků který lektor vyučuje. A zjistit to kontingenční tabulkou. Zjistit, kolik kurzů lektor vyučuje, nebo které jazyky vyučuje, by zabralo asi tak vteřinu a půl. Pokud ale chci, aby tabulka počítala počty unikátních jazyků a vypadala takto:

 Obrazek6
tak už to taková legrace není. Prošel jsem nějaké návody na webu a následující postup se mi zdá nejsnazší.

Je třeba vytvořit si a do tabulky s daty použít nový sloupec, a tam zapsat takovýto (nebo obdobný) vzorec:

Vysvětlení:

Výstupem je 0 nebo 1, což závisí na dvou kritériích - na jméně lektora a na jazyce. Protože tato kritéria mají ve funkci COUNTIFS mezi sebou vztah AND (tedy musí platit obě), je výstupem funkce COUNTIFS v určitém řádku celkový počet řádků tohoto lektora na tomto jazyce. U buňky Novák / Angličtina je to počet buněk Novák / Angličtina ve sledované oblasti. Všimněte si, že oblast funkce COUNTIFS je díky šikovným absolutnm odkazům "roztahovací" a sama funkce tedy zjišťuje, kolikrát se tato kombinace lektora a jazyka objevila pouze odshora až k příslušnému řádku (tedy ne v celé oblasti, ale jen od buňky nahoru). S použitím samotné funkce COUNTIFS by byl výsledek takovýto:

 Obrazek2
My si ale pro kontingenční tabulku potřebujeme připravit data tak, aby se za každou kombinaci lektora  a jazyka "pričetla" jen jednička.

Proto z funkce COUNTIFS vytvoříme podmínku - její výsledek budeme porovnávat s jedničkou. Funkce tedy bude zobrazovat 1 v případě, že se jedná o první výskyt, a 0 v ostatních případech. Tím zaručíme, že v novém sloupci bude 1 pouze u prvního výskytu, a jinde budou nuly. Výsledek bude vypadat takto:

 Obrazek3
Když pak poslední sloupec dáme do pole hodnot kontingenční tabulky, číslo bude ukazovat, kolik jazyků lektor vyučuje.
Obrazek4
 

Kontingenční tabulka s relační vazbou – datový model

I když se to nezdá, s Excelem se dá pracovat jako s relační databází. Jednou z možností je PowerPivot, doplněk pro tvorbu datových skladů. To je ale docela těžká váha a navíc v některých licenčních verzích není dostupný. Je však možnost prostě jen vytvořit relaci v kontingenční tabulce. Tato možnost je v Excelu od verze 2013 a v tomto článku si ukážeme, jak na to.

Příklad

Mějme tyto dvě tabulky:

1 obě tabulky

V zelené tabulce jsou záznamy o odpracovaných hodinách zaměstnanců na projektech. V modré tabulce jsou jména zaměstnanců přiřazená k jejich číslům. My ale chceme v kontingenční tabulce vidět hodiny ze zelené tabulky přiřazené ke jménům zaměstnanců z modré tabulky.

Návod

Nejprve vytvořte kontingenční tabulku z první tabulky (např. ze zelené). Tabulka bude vypadat takto: 2 prvni KT Tedy zobrazuje to, co chceme, ovšem bez jmen. Teď se pokusíme vytvořit kontingenční tabulku i z modré tabulky. Klikneme tedy do ní a jdeme na Vložit / Kontingenční tabulka. Ve skutečnosti ale nechceme vložit novou kontingenční tabulku, chceme tu novou připojít ke staré. Proto ve vkládacím dialogu zaškrtneme "Přidat tahle data do datového modelu".

3 pridani do datoveho modelu

Na první pohled to vypadá, že v nově vzniklé kontingenční tabulce jsou pouze ID čísla se jmény - tedy obsah druhé tabulky. Stačí však přepnout se z Aktivní na Vše a v seznamu polí se objeví dva rozkliknutélné "Rozsahy". V nich jsou pole z obou kontingenčních tabulek. 4 zobrazit vše Excel ale ještě neví, který sloupec z jedné tabulky se má propojit s jiným sloupcem z jiné tabulky - není vytvořena relace (pokud bychom se teď pokusili vytvořit kontingenční tabulku, bude ukazovat nesmyslné hodnoty). Klikneme na Relace / Nová a naklikáme, co je s čím propojené.

5 tvorba relace

Zavřeme. S kontingenční tabulkou se nestalo zdánlivě nic. Na rozdíl od situace předtím teď ale mohu vytvářet kontingenční tabulku z obou tabulek současně.

6 kontingenční tabulka nad dvěma tabulkami

Ve výsledku jsou pak informace z obou tabulek - jména z jedné, hodiny a projekty z druhé. Tabulky jsou vzájemně propojené přes sloupec s ID. Data pro tabulku si můžete stáhnout odsud.


Ploché (tabulkové) zobrazení kontingenčních tabulek

Kontingenční tabulky mají jeden zajímavý způsob zobrazení, který se občas velmi hodí. Nejsem schopný to popsat srozumitelně teoreticky, takže hned přejdu k příkladu.

Příklad

Mám takovouto tabulku, ve které sleduji tržby za prodejce a druhy zboží.
1_puvodni_data
Nebyl by problém vytvořit kontingenční tabulku sledující, kolik který prodejce utržil na různých druzích zboží.
2_spatna_tabulka
Když se ale na tabulku podíváte, vidíte problém. Jméno a příjmení je jsou nesmyslně ve dvou různých řádcích. Mnohem lépe by tabulka vypadala takto:
3_hotova_tabulka

Návod

Jak na to? Vytvořím obyčejnou kontingenční tabulku, jako je ta na druhém obrázku. V Nástroje kontingenční tabulky / Návrh / Rozložení sestavy vyberu Zobrazit ve formě tabulky:
4_prepnuti_na_tabulkove_zobrazeni
Ve stejné kartě v Souhrny kliknu na Nezobrazovat souhrny.
5_vypnuti_souhrnu
A je hotovo - tabulka je přehlednější a položky, které k sobě logicky patří, jsou opravdu vedle sebe.
vysledek

Použití kontingenční tabulky na zpracování dat z Google Analytics

Google Analytics je skvělý nástroj pro analýzu návštěvnosti webových stránek. Pokud ho správně nastavíte a správně používáte, budete mít relativně dost informací o tom, kdo kdy a snad i proč se na vašem webu pohyboval.
V tomto článku si ukážeme příklad, jak vzít statistiku z Google Analytics a zpracovat ji pomocí kontingenční tabulky v Excelu.
Půjde spíše o příklad možností analýzy než o konkrétní řešený úkol.
Nejprve si v Google Analytics vybereme statistiku, kterou chceme vyhodnocovat, a nastavíme požadované časové období. google analytics puvodni graf - Copy U této statistiky (v našem případě počet návštěv webu), pak můžeme odlišit různé segmenty. Já odliším např. návštěvnost z mobilů a návštěvnost z vyhledávání. 
Zobrazí se mi upravený graf. google analytics graf rozdělený na segmenty - Copy Výsledky stáhnu. Je vcelku jedno, do kterého formátu, nejjednodušší to bude do xlsx. google analytics stažení ve formátu pro Excel - Copy Otevřu soubor a jdu na list "Datová sada 1". stažená data Než se pustím do analýzy, jdu na konec tabulky (např. Ctrl a šipka dolů), a odmažu součty - ty by jinak dělaly ve statistikách neplechu.
Vložím kontingenční tabulku.
Co teď? Mohu např. sledovat vývoj v segmentech (Segmenty tedy přesunu do sloupcových polí) a vložit graf. podrobný graf Graf je sice technicky správný, ale podrobný až k nečitelnosti. Můžeme tedy seskupit datumy do měsíců a roků. graf rozdělený na měsíce - Copy Tady už začíná být graf zajímavý - jasně vidíme sezónní pokles v létě (a mohli bychom třeba sledovat i meziměsíční přírůstky).
Nám se ale na grafu nelíbí, že nejnižší datová řada (mobilní přístupy), je titěrně malá. Abychom tedy mohli sledovat každou řadu zvlášť (a tedy ve správném měřítku), použijeme průřez a klikáním sledujeme různé segmenty. průřez A dál si můžeme nekonečně hrát s kontingenční tabulkou, můžeme si dotáhnout hodnoty z dalších systémy a porovnat je, zkrátka můžeme použít funkce Excelu nad daty z Google Analytics.


Procvičení kontingenčních tabulek – první díl

Tento příspěvek je opakovací - kontingenční tabulky jsou popsané zde.
Chcete-li si procvičit práci s nimi, stáhněte si tento soubor:
V souboru jsou pracovní výkazy jednotlivých pracovníků firmy. Pokuste se vytvořit následující tabulky:
  • Součet odpracovaných hodin za jednotlivé zaměstnance - práce o víkendu
kontingencni_tabulka_procvicovani (2)
  • Kdo na čem odpracoval kolik hodin - pouze v únoru v pracovní dny
kontingencni_tabulka_procvicovani (1)
  • Průměrný počet hodin odpracovaných v jednotlivé dny, rozdělený mezi kluky a holky
kontingencni_tabulka_procvicovani (3)
Pokud se Vám některá z tabulek nepodařila, zde je řešení.
Jestli jste úkoly snadno zvládli, pak umíte s kontingenčními tabulkami slušně pracovat.

Procvičení kontingenčních tabulek – druhý díl

S tímto příspěvkem si můžete znovu do hloubky projít a vyzkoušet, jak umíte s kontingenční tabulkou reálně pracovat.
  • Je zde sedm úkolů - pokud je zvládnete všechny, máte kontingenčí tabulky slušně zmáknuté.
  • Některé úkoly lze řešit i bez kontingenčních tabulek, to ale teď můžeme pominout.
  • V úkolech budete pracovat s těmito daty - zadání i řešení je tady
  • Jedná se o databázi prodejů zboží ve firmě - s něčím podobným (ve větším) se můžete setkat v řadě firem a na pracovních pohovorech do řady firem.
  • Soubor s řešeními všech tabulek je ke stažení na konci. Přeji příjemnou práci, pokud Vám něco nejde, neváhejte a napište dolů do diskuse nebo přímo na můj email.
  • Pro vypracování úkolu by mělo stačit přečíst otázku - pokud chcete nápovědu, zvětšete obrázek.
Úkoly
  • Zjistěte, kolik je v každém nákupu jednotlivých druhů zboží (v tržbách)
prvni
  • Zjistěte, který prodejce prodal o víkendech nejméně mrkve
druhy
  • Zjistěte, jaké byly tržby za pomeranče a citrony podle dní v týdnu
treti
  • Zjistěte, který prodejce prodával nejvíce zeleninu. Rozdělte podle dní v týdnu a případně doplňte o odpovídající podmíněné formátování
ctvrty
  • V jednotlivých měsících seřaďte dny podle tržeb - od nejvyšších po nejnižší
paty
  • Zjistěte, jaké byly celkové tržby za jednotlivé dny v týdnu podle druhů zboží
sesty
  • Zjistěte (a seřaďte), ve kterých dnech toho Eva nejvíce prodala

Procvičení kontingenčních tabulek – díl třetí, pokročilejší

Chcete si procvičit logické uvažování s kontingenčními tabulkami? Nabízím čtyři úkoly. Zdrojová tabulka ke stažení je tady. Jedná se o přehledy prodejů ve firmě. U všech úkolů platí, že je třeba je zvládnout bez úpravy vstupních dat - jen s pomocí nástrojů kontingenční tabulky.

Histogram

Vytvořte histogram, který popisuje rozložení velikostí tržeb ovoce a zeleniny zvlášť. Přesně takový jako je tento.

grafik

Přírůstky

Zjistěte, o kolik procent se v jednotlivých měsících změnily průměrné velikosti tržeb (vždy oproti předchozímu měsíci).

2+změny

Rozložení

Vytvořte přesně takto rozloženou tabulku (na obrázku je jen začátek).

3+rozložení

Nejúspěšnější prodejce

Stanovte umístění prodejců podle toho, kolik prodejů uskutečnili v březnu (největší počet prodejů = jednička).

 4+pořadí


Cvičná data pro kontingenční tabulky

Pokud si někdo chce procvičit práci s kontingenčními tabulkami, filtry obyčejnými i rozšířenými, s podmíněným formátováním, s řazením nebo prostě s analýzou dat v Excelu, nabízím tři dlouhé tabulky, na kterých je možné si nejen toto snadno zkoušet.