Kontingenční tabulky v Excelu 2013

Tento základní návod na kontingenční tabulky platí pro Excel 2013 a (na 99%) také pro Excel 2010. Pokud hledáte návod pro Excel 2007 nebo starší, klikněte sem.

Příklad

Mám neuspořádaná data a chci z nich získat užitečné informace. V tomto případě se chci (s pomocí kontingenční tabulky) dozvědět, kolik je v seznamu (nabídka autobazaru) aut určité značky (např. Ford) a kolik dohromady stojí.

Návod

Tabulka ke stažení
Začnu tak, že kliknu kamkoliv do tabulky - nemusím nic označovat. Dále kliknu v kartě Vložení (Insert) na Kontingenční tabuka (Pivot table).

Následující dialog mohu nechat jak je a jen ho potvrdit "OK". Pouze pokud bych chtěl použít jiná data, než mi vybral Excel, vyberu je.

 

O možnosti použít externí data (Use an external data source) více zde
Tím vznikne nový list s kontingenční tabulkou. Nemusím se tedy bát, že původní tabulka zmizela - mohu se k ní vždy vrátit na původní list.

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 budu přesouvat do levé tabulky nebo do spodních obdélníků, budu upravovat kontingenční tabulku.

Mým úkolem bylo zjistit, kolik je v seznamu Fordů a kolik dohromady stojí. Udělám to tak, že v tabulce nechám vypsat součty cen za všechny značky - tedy i za Ford.

"Ford" je jedna ze značek aut v seznamu. Proto přetáhnu "Značka" z horního obdélníku vpravo do obdélníku "Sem přetáhněte řádková pole" v tabulce nebo do pole "Řádky" vpravo dole

Tím se Vám v levé části tabulky vypíší všechny značky aut v seznamu. 

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

Teď již u každé značky vidím, kolik dohromady stojí. 

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

Dvojkliknu na Součet z cena a v nabídce změním Součet (Sum) na Počet (Count). K tomuto dialogu se mohu dostat také kliknutím vpravo dole na Součet z Cena / Nastavení polí hodnot. 
Pokud bych chtěl obojí, součet i počet, přitáhnu do pole hodnot Cenu dvakrát - a jednou změním 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í.
• Ve verzích Excelu od 2007 je možné místo do samotné tabulky přetahovat záhlaví sloupečků do čtyř polí dole v pravém pruhu. Pole odpovídají polím tabulky a je jedno, kam záhlaví přetáhnete - jestli přímo do tabulky nebo do "chlívečků" vpravo.
• 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

Kam dál?

Pokud máte raději videonávody, tak tady jeden je. Pokud máte nějaký dotaz, napište jej prosím do diskuse.
http://www.youtube.com/watch?v=rZ3XbdkGqZE&list=PLFCPUmgA-NOPpTsYyBrf0DmY3sM4-7oZ4&index=6

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ží.

Nebyl by problém vytvořit kontingenční tabulku sledující, kolik který prodejce utržil na různých druzích zboží.

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:

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:

Ve stejné kartě v Souhrny kliknu na Nezobrazovat souhrny.

A je hotovo - tabulka je přehlednější a položky, které k sobě logicky patří, jsou opravdu vedle sebe.

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

Příklad

Kontingenční tabulku 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:

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:

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í.

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.

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.

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

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.

A tabulka je hotová.

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:

  jsou zakázky, kterých dosáhla nějaká 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.

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.

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)

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.

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.
Tabulka je ke stažení a k procvičení tady.

Kontingenční tabulky za jeden večer

Na www.vyuka-excelu.cz nabízíme nový, jednovečerní kurs zaměřený výhradně na kontingenční tabulky. Kontingenční tabulky jsou nesmírně silný analytický nástroj - pokud ho umíme používat. Chcete-li se jejich používání opravdu dobře naučit a můžete tomu věnovat jeden večer, rádi Vás na kursu uvítáme.
Více informací zde.

Chcete být sexy? Naučte se pracovat s kontingenční tabulkou.

Myslíte si, že abyste byli sexy, je třeba dobře vypadat?
Pak nejspíše žijete v minulém století. Podle tohoto článku v Harward Business Review je v našem století tím nejvíce sexy ten, kdo umí analyzovat data.
Takže - klidně jezte, nesportujte a kašlete na svůj vzhled, ale kontingenční tabulky se učte :)

Praktické použití skupinových polí v kontingenční tabulce

Příklad - slučování datumů

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.

Dříve 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 tržby.

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

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

Příklad - histogram

Nemusím ale slučovat jen data - mohu slučovat i běžná čísla. A z toho může vzniknout (kromě jiných možností) např. histogram.

Vrátím se k předchozímu případu. Dejme tomu, že bych chtěl v tabulce zjistit, jak vysoké byly tržby - a to tak, že bych chtěl vidět, kolik tržeb bylo v různých pásmech od-do.

Návod

Vytvořím kontingenční tabulku, kde mám v řádkových polích tržby a v polích hodnot např. Den (protože sleduji počet, je vlastně jedno, které pole do hodnot dám.

Kliknu do některé z hodnoty v Popisky řádku a pak zase na Skupinové pole.

Vyberu, jak velká mají být pásma - já např. nechám 1000.

A hotovo - vidím, že např. tržeb v objemu mezi jedním a dvěma tisíci bylo 2295. Mohu si vytvořit i přehledný kontingenční graf.

Histogram lze v Excelu dělat i přes analytické nástroje - ale je to dost za trest a tady uvedený způsob je výrazně šikovnější.

Kontingenční tabulky konečně pohromadě

V horní liště přibylo tlačítko, které odkazuje na všechny návody týkající se kontingenčních tabulek. Je to proto, že se podle návštěvnosti jedná o velmi důležité téma. Doufám, že je to takto pro návštěvníky stránek přehlednější.

Návody na kontingenční tabulky

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

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:

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

Sleduji tedy, kolik jsem v jednotlivých měsících utržil celkově.
Mně 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

Takto vypadá výsledek:

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...

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

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:

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

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

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:

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

Sleduji tedy, kolik jsem v jednotlivých měsících utržil celkově.
Mně 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

Takto vypadá výsledek:

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...

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

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:

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