Lekce 13 - Kontingenční tabulky v aplikaci Excel

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

V této lekci 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ř. kolik kusů zboží jsme naskladnili v určitém měsíci nebo 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 4 oblastí:

• oblast filtrů - v této části se nacházejí filtry, pomocí kterých můžeme vybírat pouze některá data
• oblast řádků - do této části umisťujeme pole, která budou tvořit popisky jednotlivých řádků v tabulce
• oblast sloupců - do této části umisťujeme pole, která budou tvořit popisky jednotlivých sloupců v tabulce
• oblast hodnot - do této části umisťujeme pole, u kterých chceme provádět výpočty (např. součet, průměr, ...)

Vytvoření kontingenční tabulky

Jak jsme si již řekli, kontingenční tabulku můžeme vytvořit pouze tehdy, pokud máme tabulku nebo databázi s určitými daty neobsahující prázdné buňky.

V případě, že tato data máme připravena, kontingenční tabulku vložíme následovně:

• Na kartě Vložení ve skupině Tabulky vybereme Kontingenční tabulka

• Otevře se nám dialogové okno Vytvořit kontingenční tabulku, v rámci kterého zvolíme tabulku nebo oblast dat
• Dále v tomto okně také volíme, zda chceme tabulku vložit do existujícího nebo nového listu sešitu

• Vložení tabulky poté potvrdíme kliknutím na tlačítko OK a tabulka se nám vloží
• Dále je potřeba upravit Pole kontingenční tabulky, což je nabídka, která se nám otevře v pravé části listu

• V rámci této nabídky přetahujeme myší jednotlivá pole do jednotlivých oblastí tabulky
• Tabulka se nám podle toho automaticky aktualizuje a zobrazují se v ní pole, která jsme vybrali
• Takto můžeme tabulku upravovat podle toho, jaká data chceme právě zjistit

Kontingenční tabulka se dá také vložit z dat v jiném sešitě, což ale vyžaduje pokročilou znalost kontingenčních tabulek.

Pro lepší představu, jak kontingenční tabulka funguje a k čemu ji můžeme využívat, si ukážeme následující příklady spolu s úpravami tabulek.

Příklady

V následujících příkladech si vytvoříme jednoduché kontingenční tabulky, na kterých pochopíme princip vkládání a jejich úpravy. Budeme pracovat s tabulkou, kterou máme připravenou z minulých lekcí. V reálném používání Excelu se samozřejmě pracuje i s mnohem většími oblastmi dat, ale pro pochopení principu nám naše tabulka bude stačit.

Nejprve chceme zjistit počet prodaných kusů zboží od dodavatele Pekárna Novák.

Na kartě Vložení ve skupině Tabulky klikneme na možnost Kontingenční tabulka. Otevře se nám dialogové okno Vytvořit kontingenční tabulku, ve kterém vybereme oblast a to tak, že klikneme do prázdného pole v řádku Tabulka/oblast a označíme tažením myši oblast buněk A1:J6.

Dále zvolíme umístění tabulky, v našem případě označíme možnost Nový list. Vložení potvrdíme kliknutím na tlačítko OK.

Tímto se nám vytvoří nový list sešitu, ve kterém nalezneme naši kontingenční tabulku. Klikneme na kontingenční tabulku, otevře se nám okno v pravé části listu a můžeme přejít k vkládání polí do jednotlivých oblastí.

Do oblasti Filtry přesuneme pole Dodavatel, což nám později umožní filtrovat data kontingenční tabulky podle dodavatelů. Do oblasti Řádky přesuneme pole Zboží, což nám vypíše typy zboží do jednotlivých řádků. Do oblasti Hodnoty přesuneme pole Počet prodaných ks, což nám bude ukazovat počty prodaných kusů z jednotlivých produktů od vybraných dodavatelů.

Nyní již v tabulce máme správně nastavená pole, ale potřebujeme data dále vyfiltrovat. V buňce B2 vedle buňky Dodavatel klikneme na rozbalovací šipku a zvolíme dodavatele Pekárna Novák.

Vzhledem k tomu, že je ve výchozím nastavení tabulky zobrazován Celkový součet, zobrazuje se nám již finální výsledek a to počet prodaných kusů zboží od dodavatele Pekárna Novák.

Dále chceme zjistit průměrný zisk z prodeje dvou typů produktu - cukru a třtinového cukru.

Víme, že oba tyto produkty odebíráme od dodavatele Cukrovar Štípa, vyfiltrujeme si tedy pouze tohoto dodavatele. Z oblasti Hodnoty odstraníme pole Počet prodaných kusů kliknutím pravým tlačítkem myši a zvolením možnosti Odstranit pole.

Do oblasti si místo předchozího pole vložíme pole Zisk z prodeje. Víme, že nyní potřebujeme počítat průměr. Na buňku B4 - Součet z Zisk z prodeje klikneme pravým tlačítkem myši a vybereme možnost Nastavení polí hodnot....

Otevře se nám dialogové okno, ve kterém zvolíme možnost Průměr a výběr potvrdíme kliknutím na tlačítko OK.

Nyní již vidíme, že průměrný zisk z prodeje je 219,50 Kč za jeden typ cukru.

Chceme také vědět, který typ zboží je potřeba objednat a zároveň jeho aktuální skladovou zásobu.

Do oblasti Filtr vložíme pole Objednat. Do oblasti Řádky vložíme pole Zboží a do oblasti Hodnoty vložíme Počet ks skladem 22. 8.. Vyfiltrujeme si pouze zboží, které je potřeba objednat.

V rozbalovací nabídce v buňce B2 vedle buňky A2 - Objednat zvolíme tedy možnost ANO. Nyní vidíme, že je potřeba objednat chléb, protože jeho aktuální skladová zásoba je pouze 5 ks.

Vzhledem k tomu, že v tomto případě nepotřebujeme používat Celkový součet, přejdeme na kartu Návrh a ve skupině Rozložení klikneme na nabídku Celkové součty a vybereme Vypnout pro řádky a sloupce. Tímto se nám řádek Celkový součet odstraní.

Na kartě Návrh můžeme provádět také další úpravy tabulky, např. měnit rozložení, styly tabulky a jejich možnosti.

Vzhledem k tomu, že kontingenční tabulky a jejich možnosti jsou opravdu rozsáhlé, ukázali jsme si v dnešní lekci pouze jejich základy tak, abychom pochopili principy, jak s nimi pracovat. Pokud chceme kontingenční tabulky používat v běžné praxi, je samozřejmě potřeba věnovat jejich nastudování více času.

To je z tohoto e-learning kurzu aplikace Microsoft Excel pro začátečníky vše. Pokud vás zajímají další funkce a příklady, můžete se studiem pokračovat v kurzech pro pokročilé.

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