Jste zde: Úvodní stránka » excel » vlozit » excel-2010-kontingencni-tabulka-uvod

Kontingenční tabulka úvod - Excel 2010

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

Pro statistické zpracování velkého množství dat je v MS Excelu k dispozici kontingenční tabulka. Propadněte jejímu kouzlu efektivity a jednoduchosti.

Jak vytvářet kontingenční tabulku v MS Excel 2003 jsem sepsal v článku: Kontingenční tabulka Excel 2003 .

Co je cílem článku o kontingenční tabulce?

Co je kontingenční tabulka?
Proč použít kontingenční tabulku?
Využití kontingenční tabulky - kde všude se dá využít
Jak vypadá struktura kontingenční tabulky
Data pro kontingenční tabulku - bez dat kontingenční tabulku nevytvoříte.
Vložení kontingenční tabulky
Další možnosti kontingenční tabulky

Kontingenční tabulka je v anglickém textu označována jako Pivot table.

Co je kontingenční tabulka?

Kontingenční tabulka je zobrazení statistického vzájemného vztahu mezi daty.

Kontingenční tabulka se nejčastěji využívá pro zpřehlednění vztahu mezi dvěma údaji (ve statistice označováno jako znak). Kontingenční tabulka Vám tyto vzájemné vztahy přehledně zobrazí. Takže řádky odpovídají jednomu údaji/parametru (znaku) a sloupce druhému. V příslušném průsečíku (řádku a sloupce) naleznete odpovídající hodnotu, která vyhovuje příslušnému řádku i sloupci.

Příklad: Máte velkou tabulku kde je jméno osoby a pak údaj o osobě pohlaví (muž, žena) a dále kuřák (ANO, NE). Pokud udělate tabulku 2x2 buňky, kdy v řádcích bude (muž, žena) a ve sloupcích (kuřák, nekuřák). Postupně můžete tabulku procházet a dělat čárky muž kuřák, žena nekuřačka (vždy do příslušného průsečíku). Až zpracování dokončíte můžete říct kolik z tabulky je mužů nekuřáku, kolik je mužů kuřáku, kolik je žen nekuřaček, kolik je žen kuřáček.

Tuhle zdlouhavou práci zvládne udělat Excel pomocí kontingenční tabulky. Takto můžete zpracovat rozsáhle seznamy kde nepůjde o statistickou tabulku 2x2, ale třeba 30 x 70 (což provádět ručně je nemyslitelné).

V kontingenční tabulce se dají zjišťovat i další vzájmené vazby, ale o tom v dalším textu.

Proč kontingenční tabulka?

Pro statistický přehled, který rychle, přehledně a jednoduše vytvoříte.

Existují možnosti jak toto třídění a selekci provést v MS Excelu ručně, nebo si vytvořit makro ve VBA. Všechny tyto možnosti jsou složité, náročné na čas a na perfektní znalost MS Excelu. Proč se tedy trápit a nevyužít to, co již někdo naprogramoval a dal do Excelu k dispozici pod názvem Kontingenční tabulka v angličtině Pivot table.

Vytvření kontingenční tabulky je rychle, elegantní a jednoduché, což budu demonstrovat na praktickém příkladu.

Využití kontingenční tabulky

Využití je obrovské od statistických analýz, přes ekonomické a marketingové výpočty, až po pomoc při analýze klíčových slov při tvorbě SEO.

statistika - pro jednoduché i složitější analýzy
ekonomika
obchod - jak se kterému produktu obchodu daří
internetový marketing SEO, návštěvnost
marketing
atd.

Jak vypadá struktura kontingenční tabulky

Struktura je jednoduchá, skládá se z pouhých 4 části:

Oblast dat
Oblast sloupců
Oblast řádků
Oblast filtr (stránková)

Oblast filtrů (stránková)

	Oblast sloupců
Oblast řádků	Oblast dat

Pro nejjednodužší tabulku musí být vyplněno:

Oblast dat
Oblast řádků nebo sloupců

Data pro kontingenční tabulku

Pokud nemáte data, kontingenční tabulku nevytvoříte.

V teorii jsem popsal jednoduchý příklad pro osoby, u kterých víme pohlaví a zda kouří. Proto si vytvořte tabulku se sloupci (jméno, pohlaví, kuřák). Například:

Jméno	Pohlaví	Kuřák
Alžběta	žena	NE
Běta	žena	ANO
Cecilie	žena	ANO

Data nemusíte vytvářet, nabízím soubor zdarma ke stažení: Kontingenční tabulka - data pro Excel 2010 (2007).

Vložení kontingenční tabulky

Nejprve vyberte data, která chcete umístit do kontingenční tabulky.

Poté na kartě Vložení ve skupině Tabulky vyberete Kontingenční tabulka - Kontingenční tabulka.

MS Excel 2010 - Kontingenční tabulka - karta

Obdržíte dialogové okno: Vytvořit kontingenční tabulku

Data

Pokud jste si nevybrali data pro kontingenční tabulku, můžete tak učinit dodatečně v tomto okně v sekci Vybrat tabulku či oblast a vyplníte Tabulka/oblast

Poznámka: Jelikož má tabulka je pojmenovanou oblastí má název Tabulka 1, pokud tabulku pojmenovanou nebudete mít uvidíte rozsah například $A$1:$C$36.

Umístění

Kontingenční tabulku si umístěte na existující List.

Klikem na OK a pokud jste vše správně zadali, obdržíte boční panel: Seznam polí kontingenční tabulky.

Přetažení dat

Teď ej potřeba přetáhnout pole do příslušných oblastí. Klikem na pole (například pohlaví) a přetáhnete na pole popis řádků. Podobně s dalšími poli. Přetahat by mělo vypadat následovně:

Popis řádků Pohlaví
Popis sloupců Kuřák
Hodnoty Jméno - název se změní na Počet z Jméno

V pravé části můžete vidět, jak se kontingenční tabulka postupně tvoří. Po vložení všech polí bude vypadat takto.

Hotová kontingenční tabulka

Ve výsledné tabulce vidíte kolik je mužů kuřáků (2), ale také souhrny. Žen je 12. Případně celkem ve zdrojové tabulce je 25 osob.

Pokud Vám kontingenční tabulka úplně nefunguje, nabízím soubor zdarma ke stažení: Kontingenční tabulka - řešení pro Excel 2010 (2007).

Další možností kontingenční tabulky

Kontingenční tabulka má další rozsáhlejší možnosti statistické analýzy. K tomu je potřeba více dat. Složitější ukázku kontingenční tabulky jsem popsal v článku: Kontingenční tabulka - pro pokročilejší - Excel 2010, nebo článek jak získat jedinečná data (využítím kontingenční tabulky), takže pokud se Vám podařilo zpracovat tabulku popsanou v tomto článku, směle do následujících.

Závěrem

V komentářích můžete zmínit, zda kontingenční tabulku používáte, nebo jste pomocí článku vytvořili Vaší první kontingenční tabulku.

Článek byl aktualizován: 01.11.2014 13:45

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

Komentáře

Matajda

Přidáno: 07.11.12 12:54

tohle téma se mi líbí :)

Pavel Lasák

Přidáno: 12.11.12 20:06

To Matajda: Přesně tak kontingenční tabulka je parádní téma, umí toho hodně, rychle se používá, pracuje "nad" zdrojovou tabulkou, takže si nezničíte zdrojová data, atd. Jen škoda, že se tak málo využívá.

Jirka

Přidáno: 27.01.13 16:30

Kontingenční tabulka se mi líbí, dovede hezky usnadnit práci, když se to s ní umí. Bohužel jí stále moc nerozumím a nejsem schopen přetáhnout pole tak, aby mi vyšlo to, co potřebuji. Neumím rozlišit, co má být v popisech řádků/sloupců nebo v poli hodnot. Vždycky to zkouším tak dlouho, dokud mi nevyleze takový tvar tabulky, který potřebuji.

Markéta

Přidáno: 21.02.13 21:12

Super, zkusím i ve své účeetnické a auditorské praxi

Lukáš

Přidáno: 19.04.13 12:56

Pavle, chci se zeptat k tvorbě kontingenční tabulky, co přesně se zadává do pole "Umístění", když chci tabulku vytvořit na existujícím listu? Jak jste přišel na hodnotu "Data!$H$5" ? Děkuji

Pavel Lasák

Přidáno: 19.04.13 17:48

To Lukáš: Umístění je čistě na tobě. Zvolil jsem si Data!$H$5, jelikož se mi tato buňka líbila (byla opticky v optimální poloze) :) Klidně může být Data!$AA$1 nebo Data!$G$125.

Teresa

Přidáno: 01.05.13 19:58

Ahoj, potrebovala by som pomocť s kontigenčnou tabuľkou, ktorú by som potrebovala využit nasledovne: Mám zoznam obalov, ktoré používame vo výrobe a mám zoznam výrobkov, v ktorých sa jednotlivé obaly používajú...po rozliknutí jednotlivých obalov potrebujem aby sa rozlklikol tento zoznam výrobkov...možeš mi odpoveď prosím poslať na turova.teresa@gmail.com

Pavel Lasák

Přidáno: 01.05.13 21:57

To Teresa: Prosím o zaslání ukázky.

Bolek

Přidáno: 04.07.13 16:08

Díky, perfektně vysvětlené. Zajímalo by mě, zda se dají data "agregovat". Příklad - nadmořská výška u výskytu rostlin s přesností na 1 m. Šlo by někde nadefinovat rozpětí např. 100-300, 301-500 a v KT by pak byl počet výskytů v daném rozpětí?

Pavel Lasák

Přidáno: 04.07.13 19:33

To Bolek: Šlo využít seskupování dat v kontingenční tabulce jen místo datumu v popisu řádku, jak mám v článku ovedeno, použít pro popis řádku - počet výskytu. Zadává se od.... do.... a přírustek třeba 200

Veronika

Přidáno: 06.01.14 15:16

Ahoj, mám dotaz, pokud vytvořím KT, a potřebuji v ní změnit v x sloupcích (hodnoty) nastavení polí hodnot (změnit z počtu třeba na součet), lze to u všech hodnot najednou? Děkuji

Veronika

Přidáno: 31.01.14 14:41

Dobrý den chci se zeptat zdali by bylo možné, mi poradit ohledně té kontingenční tabulky nedaří se mi a nevím, zdali je možno vůbec ji požít k tomu co potřebuji pokud byste byl tak hodný a napsal jsem email, že bych vám to poslala a vy byste kouknu zdali je možné v tomto konkrétním případě ji vůbec využít jde mi o to že má rozdělené jednotlivé oblasti jako kuchyň atd a dále pak zvlášt kusy a ceny a druh a potřebuji aby mi to sčítalo a myslím si, že na toto je zbytečná kontingenční tabulka ale nevím to jistě. Předem děkuji za odpověď

Víťa

Přidáno: 08.04.14 14:46

Dobrý den, potřeboval bych radu mám kontingenční tabulku a z firemních důvodů není možné nechat kontingenční tabulku v surových podmínkách...proto jsem musel vytvořit tlačítka a k nim přiřadit makra pro filtrování...vše šlo dobře do chvíle kdy jsem zjistil, že makro neumí(resp hází mi chybu) ve chvíli kdy se snažim odebrat z filtru hodnot sloupec, který je tvořen vzorcem. HAV_proc_zrus Makro ' ' ActiveWorkbook.ShowPivotTableFieldList = True ActiveSheet.PivotTables("Kontingenční tabulka 3").PivotFields( _ "Součet z Penetrace_hav(%)").Orientation = xlHidden(zde mi to háže chybu) ActiveWorkbook.ShowPivotTableFieldList = False End Sub

Adéla

Přidáno: 25.04.14 19:53

Zdravím, chtěla jsem se zeptat, jak nastavit řazení (např. v řádku) podle data, aby to šlo chronologicky podle času. Řadí se mi to 1.1.2011, 1.1.2012, 1.2.2011, 1.2.2012 atd. Zkoušela jsem vše možné, ale ''seřadit podle času'' jsem tam nenašla.

Adéla

Přidáno: 25.04.14 20:08

A ještě jsem chtěla dodat, že hodnoty mám ve formátu datum. Děkuji za radu. Adéla

Hana

Přidáno: 21.09.14 00:29

Děkuji moc, zkusila jsem to podle vašeho návodu a jsem nadšená, napoprvé se mi podařilo kontingenční tabulku vytvořit a myslím, že jsem pochopila její princip, což se mi při předchozích pokusech podle jiných vysvětlení nepovedlo. Takže jsem moc ráda a děkuji.

Pavel Lasák

Přidáno: 06.08.15 23:21

To Hana: Rádo se stalo. Ať se daří.

Martin Švehla

Přidáno: 08.12.15 16:14

Dobrý den. Mám otázku. Dělám reporting ve firmě. V jednom dokumentu v excelu mám 10 reportů a v každém listu musím každý měsíc měnit hodnotu měsíce. Nelze nějak automaticky na jednom místě v kontingenčních tabulkách nastavit měsíc? Například pomocí makra? Například automatický filtr dle aktuálního dne v měsíci. Stačí odkaz :-) Mockrát Vám děkuji.