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

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

Tento článek popisuje tvorbu základní kontingenční tabulky.

Co to je kontingenční tabulka?

Kontingenční tabulka je 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 v autobazaru.

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.

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í tabulka (Pivot table). 

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.

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.

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

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

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

Klikneme pravým tlačítkem na některé z čísel a v Souhrn dat vybereme 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

Videonávod:

Základní prací s kontingenčními tabulkami se věnuje tahle lekce našeho elearningového kurzu:

Elearning středně pokročilý Excel:

S tímto tématem se setkáte na našich kurzech ExcelTown.
Aktuálně: kurzy můžete absolvovat jak online, tak prezenčně.

1 Komentář

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

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

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

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

  10. 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. dobrý den
    jakým způsobem prosím zajistit automatickou aktualizaci, když např. přidám řádky ve zdrojové tabulce.
    děkuji
    JTO

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

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

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

  19. Dobrý den,

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

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

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

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

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

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

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

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

  27. Dobrý den,
    chtěla bych se zeptat zda lze také v kontingenčí tabulce pracovat s barevnými buňkami. Mám tabulku kde vlevo v řádcích jsou popisy činností, nahoře jsou dny v měsíci a zaměstnanci jsou odlišeni právě barvou buněk. A dle výkazů práce se zaznamenávají data do tabulky jakou činnost, jaký den a kolik hodin zaměstnanec vykonal (zaměstnanci jsou rozlišeni barvou buňky). Dále jsou činnosti rozlišené na různé odvětví, tudíž v jednom sešitě mám 10 listů. A potřebovala bych vždy u daného pracovníka zjistit za celý měsíc kolik odpracoval na jaké činnosti. Omlouvám se jestli to píši nesrozumitelně. A předem děkuji.

  29. Dobrý den,
    prosím o radu.
    Příklad: mám tabulku se 100 řádky a 5 sloupci.
    Je možné řadit podle abecedy (nebo 0-9) 20 řádku podle hodnot ve 3. sloupce.
    Když označím pouze 3. sloupec (s 20. řádky) seřadí se mi všech 100 řádků.
    Předem děkuji za odpověď.
    Jirka

  30. Dobrý den,
    pokud chcete řadit pouze vybrané řádky, tak to myslím Excel standardně nedělá. (Samozřejmě když nemluvím o ručním výběru a pak řazení…)
    Takže asi jedině makro, nebo si nějak pohrát s víceúrovňovým řazením, ale nevím přesně jak…
    JB

Komentáře není možné přidávat