Lekce 14 - Kontingenční tabulky v aplikaci Excel

V předešlém cvičení, Řešené úlohy k 13. lekci Excel pro začátečníky, jsme si procvičili nabyté zkušenosti z předchozích lekcí.

V tomto tutoriálu základů Excelu se budeme věnovat kontingenčním tabulkám.

Kontingenční tabulka

Kontingenční tabulka nám umožňuje podívat se na data z různých úhlů pohledu a tím je jednodušeji analyzovat. Díky tomuto typu tabulky si můžeme lehce odpovědět na otázky jako jsou například:

• Kolik kusů zboží jsme naskladnili v určitém měsíci?
• Kolik žáků ve třídě nosí brýle?

Velkou výhodou kontingenčních tabulek je jejich jednoduché a variabilní použití a také to, že při jejich používání nezasahujeme do původních dat, která zůstávají neměnná. Tato data bývají ve formě tabulek nebo databází.

Data pro kontingenční tabulku musí mít pouze jeden řádek záhlaví a neměla by obsahovat prázdné buňky.

Oblasti kontingenční tabulky

Kontingenční tabulka se skládá ze čtyř oblastí:

• Oblast Filtrů – obsahuje filtry, pomocí kterých lze zobrazit pouze vybraná data.
• Oblast Řádků – sem se přesouvají pole, která budou tvořit popisky jednotlivých řádků v tabulce.
• Oblast Sloupců – sem se přesouvají pole, která budou tvořit popisky jednotlivých sloupců v tabulce.
• Oblast Hodnot – sem se umisťují pole, u kterých chceme provádět výpočty (např. součet, průměr apod.).

Vytvoření kontingenční tabulky

Kontingenční tabulku lze vytvořit pouze z datové oblasti bez prázdných buněk, nejčastěji ve formě tabulky nebo databáze.

Pokud máme data připravena, klikneme na kartě Vložení ve skupině Tabulky na možnost Kontingenční tabulka (případně upřesníme volbou Z tabulky nebo oblasti):

Otevře se dialogové okno Kontingenční tabulka z tabulky nebo oblasti, ve kterém vybereme tabulku nebo oblast dat, ze které má být kontingenční tabulka vytvořena. Poté zvolíme, zda se má tabulka vložit do existujícího nebo nového listu sešitu:

Vložení potvrdíme kliknutím na tlačítko OK. Kontingenční tabulka se vloží do listu a zároveň se v pravé části zobrazí nabídka Pole kontingenční tabulky:

V této nabídce přetahujeme myší jednotlivá pole do příslušných oblastí kontingenční tabulky. Tabulka se podle zvolených polí automaticky aktualizuje. Zobrazená data lze pak kdykoli měnit podle aktuálních potřeb.

Kontingenční tabulku je možné vytvořit také z dat uložených v jiném sešitě, což už však vyžaduje pokročilejší práci s kontingenčními tabulkami.

Pro lepší pochopení fungování kontingenčních tabulek a jejich praktického využití si v následující části ukážeme konkrétní příklady a úpravy tabulek.

Příklady práce s kontingenční tabulkou

V následujících příkladech si vytvoříme několik jednoduchých kontingenčních tabulek, na kterých si vysvětlíme základní princip jejich tvorby a úprav. Pracovat budeme s tabulkou připravenou z předchozích lekcí. V praxi se sice často setkáme s mnohem větším objemem dat, pro pochopení principu však tato ukázková tabulka plně postačí.

Počet prodaných kusů podle dodavatele

Nejprve zjistíme počet prodaných kusů zboží od dodavatele Pekárna Novák:

• Na kartě Vložení ve skupině Tabulky zvolíme možnost Kontingenční tabulka.
• Otevře se dialogové okno Kontingenční tabulka z tabulky nebo oblasti, ve kterém klikneme do pole Tabulka/oblast a označíme oblast buněk A1:J6.
• Jako umístění tabulky zvolíme Nový list a volbu potvrdíme tlačítkem OK:

V sešitu se vytvoří nový list s prázdnou kontingenční tabulkou. Po kliknutí do tabulky se v pravé části listu zobrazí nabídka Pole kontingenční tabulky. V této nabídce:

• Do oblasti Filtry přesuneme pole Dodavatel.
• Do oblasti Řádky přesuneme pole Zboží.
• Do oblasti Hodnoty přesuneme pole Počet prodaných ks.

Tabulka se automaticky aktualizuje a zobrazí součty prodaných kusů podle jednotlivých typů zboží.

Následně v buňce B2 vedle položky Dodavatel otevřeme rozbalovací nabídku, vybereme dodavatele Pekárna Novák a výběr potvrdíme.

Protože je ve výchozím nastavení zapnutý Celkový součet, zobrazí se rovnou výsledný počet prodaných kusů zboží od tohoto dodavatele:

Výpočet průměrného zisku z prodeje

Dále zjistíme průměrný zisk z prodeje dvou produktů – cukru a třtinového cukru.

Oba produkty odebíráme od dodavatele Cukrovar Štípa. Pole Dodavatel máme vložené v oblasti Filtry, takže v kontingenční tabulce (v rozbalovací nabídce filtru vedle položky Dodavatel, v buňce B2) vybereme pouze tohoto dodavatele.

Z oblasti Hodnoty odstraníme pole Počet prodaných ks (v poli pod názvem Součet z Počet prodaných ks) kliknutím pravým tlačítkem myši a volbou Odstranit pole:

Do oblasti Hodnoty následně vložíme pole Zisk z prodeje.

Protože chceme místo součtu počítat průměr, klikneme pravým tlačítkem myši na položku Součet z Zisk z prodeje v kontingenční tabulce a zvolíme Nastavení polí hodnot….

Nabídka lze alternativně otevřít kliknutím levým tlačítkem myši na šipku vedle položky Součet z Zisk z prodeje v okně Pole kontingenční tabulky.

V dialogovém okně vybereme funkci Průměr a volbu potvrdíme tlačítkem OK:

Kontingenční tabulka nyní zobrazuje, že průměrný zisk z prodeje činí 219,50 Kč:

Kontrola skladových zásob a zboží k objednání

Na závěr zjistíme, které zboží je potřeba objednat a jaká je jeho aktuální skladová zásoba.

• Do oblasti Filtry vložíme pole Objednat.
• Do oblasti Řádky vložíme pole Zboží.
• Do oblasti Hodnoty vložíme pole Počet ks skladem 22.9.

V rozbalovací nabídce filtru Objednat zvolíme hodnotu ANO.

Výsledkem je zobrazení zboží, které je potřeba objednat. V naší ukázce se jedná o chléb, jehož aktuální skladová zásoba činí pouze 5 ks.

Protože zde nepotřebujeme řádek Celkový součet, přejdeme na kartu Návrh, ve skupině Rozložení otevřeme nabídku Celkové součty a zvolíme Vypnout pro řádky a sloupce. Tím se řádek celkového součtu odstraní:

Na kartě Návrh lze provádět i další úpravy kontingenční tabulky, například měnit její rozložení, styly nebo zobrazované prvky.

Kontingenční tabulky nabízejí velmi široké možnosti práce s daty. V této lekci jsme se zaměřili pouze na jejich základní použití, abychom pochopili hlavní principy fungování. Pro jejich efektivní využití v běžné praxi je vhodné věnovat se jim podrobněji.

Tímto končí část e-learningového kurzu Základy Microsoft Excel. Pokud vás práce s Excelem zaujala a chcete své znalosti dále rozšířit, můžete pokračovat navazujícím kurzem Microsoft Excel pro pokročilé.

V následujícím cvičení, Řešené úlohy k 14. lekci Excel pro začátečníky, si procvičíme nabyté zkušenosti z předchozích lekcí.