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.

26 comments on “Základní návod pro vytvoření kontingenční tabulky
  1. CSCSCS napsal:

    Cs, najsa navot:))

  2. xrorysek@seznam.cz napsal:

    Dobrý den,prosím o radu jak začít vytvoření tabulky nikdy jsem to nedělal moc děkuji Milan.

  3. Jiří Beran napsal:

    Dobrý den Milane,
    pokud máte na mysli vytvoření tabulky, ze které se bude dělat kontingenční tabulka (v našem případě tedy přehled aut), tak prostě vyplníte buňky příslušnými hodnotami. Nic jiného není třeba dělat.

  4. Petr Mauer napsal:

    Dobrý den.
    Chtěl bych se zeptat, i když to asi tak úplně nesouvisí. Jde mo o to, vytvořil jsem tabulku, nastavil veškeré formátování buněk. Nyní bych chtěl tu tabulku sdílet pomocí odkazu na OneDrive, aby všichni, co jim dám ten odkaz, mohli do tabulky zapisovat předem dané údaje. No a já bych chtěl to, abych mohli vyplňovat text, ale nemohli jakkoliv tabulku měnit, co se týče barev, velikosti, podmíněné formátování atd. Je to možné? Pokud ano, tak jak? děkuji

  5. Jiří Beran napsal:

    Dobrý den Petře, mělo by stačit úplně obyčejné zamčení částí tabulky před nahráním na OneDrive…
    http://vyuka-excelu.cz/navody/postupy-a-spinave-triky/zamceni-casti-tabulky-v-excelu/

  6. Petr Mauer napsal:

    Asi jsem to napsal špatně, za to se omlouvám, ale když ty buňky zamknu, tak do nich nebude moci nikdo psát. Já však potřebuji, aby normální text do nich psát mohli, jen jim bylo zabráněno jakkoliv buňky upravovat, nebo celou tabulku. Mám tam totiž přednastavený druh písma, barvu, podmíněné formátovaní atd. Třeba do jednoho sloupce mají zapisovat pouze dosažené body a já ještě musím přijít na to, jak to udělat, aby se ty řádky automaticky řadili od největšího po nejmenší a nemusel to dělat ručně, tím je myšleno to, že jakmile někdo u svého jména (na svém řádku) změní údaj, tak aby se to aktualizovalo ihned a srovnalo. Omlouvám se za pozdní odpověď a dotaz, ale neuložil jsem si tuto stránku 🙂

  7. Milan napsal:

    Dobrý den,

    po volbě „OK“ při „vytvořit kont.tabulku“ mi to píše „název pole kont. tabulky není platný“ a proces se zastaví…

  8. Milan napsal:

    Problém vyřešen. (způsobeno sloučenými sloupci)

  9. Jiří Beran napsal:

    Jj, podobný problém se stane i když je někde prázdné záhlaví…

  10. Martin napsal:

    dobrý den řada zaměstnavatelů kontingenční tabulky zbožňuje, ale dělat to růčo v exelu je divný měli by mít předdefinované databáze, kde jen přetahujete položky co jim na to říct

  11. Jirka napsal:

    dobrý den
    jakým způsobem prosím zajistit automatickou aktualizaci, když např. přidám řádky ve zdrojové tabulce.
    děkuji
    JTO

  12. Kundertová napsal:

    Dobrý den,
    chci se zeptat, zda je možné zpracovávat přes kontoíngenční tabulky větší množství dat, než jaké zvládne kontingenční tabulka. Popřípadě jestli jsou nějaké triky, jak to obejít.
    Děkuji
    nstrom@centrum.cz

  13. Jiří Beran napsal:

    Dobrý den,
    tam, kde kapacitně nestačí kontingenční tabulky, je možné používat doplněk Power Pivot:
    http://www.power-pivot.cz

  14. Katka napsal:

    Dobrý den,

    Ráda bych se zeptala jak vyřešit pomocí kontingenční tabulky vícečetné hodnoty na jeden řádek… Konkrétněji když každý řádek představuje jednoho člověka a sloupec jsou odpovědi ve kterých mediích nás viděli…Tedy příklad Věk: 0 – 17, Místo: Praha, Media: Billboard, Leták + další sloupec je také vícečetný… Jak to řešit? Mohu dát každou informaci na jiný řádek tedy dát více řádků pro jednoho člověka? Jak potom zjistit celkový počet dotazovaných? A Nebude problém, když v dalším sloupci bude řádek prázdný? Doufám, že jsem popsala dostatečně srozumitelně. Děkuji

  15. Jiří Beran napsal:

    Dobrý den, úplně nerozumím zadání. Můžeme to buď nějak vyřešit v rámci placené konzultace, nebo zkuste sama – mohou se Vám hodit např. počítaná pole…
    http://vyuka-excelu.cz/navody/kontingencni-tabulky-prehled-navodu/pocitana-pole-calculated-fields-v-kontingencni-tabulce/

  16. Ondřej Spurý napsal:

    Dobrý den, je možno na výsledek kontingenční tabulky napasovat vzorec, který by se automaticky přizpůsobil výsledkům? Zkusím napasovat příklad na vzorový autobazar. Berme v potaz, že naše tabulka obsahuje i údaj počet poruch vozidla. Vozidlo A najelo 100.000km a mělo 1 poruchu. Vozidlo B najelo 50.000km a mělo dvě poruchy. Chci získat údaj průměrný počet km na 1 poruchu. A podmínka je zachovat výhody kontingenční tabulky, tedy místo vozidel A a B si dát např. všechny fordy.

  17. Ondřej Spurý napsal:

    Dobrý den,

    přesně tohle jsem potřeboval, jste kapacita 🙂

    Děkuji a přeji pěkný den

  18. Ondřej Spurý napsal:

    Dobrý den,

    měl bych prosím ještě dotaz na filtraci datumu v kontingenční tabulce. U obyčejné tabulky lze filtrovat datum dle kalendářních hodnot od do. U kontingenčního filtru lze jen vybírat jednotlivá data konkrétních dnů. Lze to nějak obejít?

    Konkrétní příklad: mám sloupec datum a tam údaje všech od 1.1.2015 do 10.4.2016. Chci vybrat pouze data roku 2015, nebo chci pouze 2. kvartál 2015 a podobně.

    Předem díky.

  19. Jiří Beran napsal:

    Dobrý den,
    myslím že přímo v kontingenční tabulce to nejde. Obvykle to řeším tak, že si v původních datech udělám nový sloupec, ve kterém se určuje, jestli tento datum spadá nebo nespadá do určitého intervalu (tedy současně je větší než spodní konec intervalu a současně nižší než spodlní konec). Pak v tabulce vzniknou dvě možnosti – v intervalu nebo mimo interval. Tyto dvě možnosti se pak filtrují snadno.

  20. Ondřej Spurý napsal:

    Dobrý den,

    děkuji za odpověď. Nakonec jsem to vyřešil datumovýma vzorcema. Přidal jsem sloupečky kde se ze zadaného data rovnou určí měsíc a pak si mohu kontingenční data agregovat dle měsíců. Když chci kvartál tak vyberu 3 měsíce a je to.

    Měl bych ještě otázku, slibuji že poslední:)
    Chci si dát v kontingenční tabulce do řádků pole číslo faktury a určit počet unikátních hodnot(číslo faktury se opakuje a tudíž počet hodnot nezafunguje). V klasické tabulce to lze pomocí maticového vzorce =SUMA(1/COUNTIF(A2:A20;A2:A20)) po potvrzení ctrl+shift+enter.

    Obávám se ale, že to už v možnostech kontigenční tabulky nebude.

  21. Pepiš napsal:

    Diki za navod, moz to pomohlo more dik

  22. Monika napsal:

    Skvělý videonávod, jasně a stručně vysvětleno, děkuji.

  23. Sára Vernerová napsal:

    Dobrý den
    Chtěla bych se zeptat. když se mi v excelu zobrazí v pravé straně sloupec s možnostmiúprav kontingenční tabulky (filtry,sloupce,řádky,hodnoty), do čeho mám přetáhnout údaje o kategoriích,ceně a počtu výrobků? Snad jsem to napsala trochu srozumitelně.
    Děkuji

  24. Jiří Beran napsal:

    Sáro – jak to myslíte? Když se zobrazí, tak je to v pořádku, ne? Pokud jste se upsala a měla na mysli, že se „nezobrazí“, tak by stačilo buď kliknout do tabulky (a on se zobrazí), nebo kliknout do tabulky, jít na Analýza / Seznam polí a zobrazí se určitě.

Napsat komentář

Vaše emailová adresa nebude zveřejněna.

Firemní kurzy

  • Přemýšlíte o firemním školení Excelu?
  • Školíme Excel od začátečníků po experty, ale také PowerPoint, PowerPivot nebo Access.
Mám zájem o kurz

Individuální výuka

Mám zájem o výuku

Vývoj aplikací v Excelu (makra - VBA)

  • Vyvíjíme aplikace pro potřeby firemních zákazníků
  • Reporty, vizualizace dat, harmonogramy, plánování
  • Slučování dat ze souborů, agregace, exporty
Zajímám se o vytvoření aplikace

Kontakt

  • Tel: +420 602 274 999
  • Mail: info@vyuka-excelu.cz
Kontaktní formulář
TOPlist