13. diel - Kontingenčnej tabuľky v programe Excel

V predchádzajúcom cvičení, Riešené úlohy k 12. lekcii Excel pre začiatočníkov, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.

Vítam Vás pri poslednej lekcie e-learning kurzu programu Microsoft Excel, ktorý je určený pre začiatočníkov. V minulej lekcii, Riešené úlohy k 12. lekcii Excel pre začiatočníkov , sme si povedali viac o filtroch a ukázali sme si príklady základných filtrov. V tejto lekcii sa budeme venovať kontingenčným tabuľkám.

Kontingenčnej tabuľka

Kontingenčná tabuľka nám umožňuje pozrieť sa na dáta z rôznych uhlov pohľadu a tým je jednoduchšie analyzovať. Vďaka tomuto typu tabuľky si môžeme ľahko odpovedať na otázky, ako sú napr. Koľko kusov tovaru sme naskladnili v určitom mesiaci alebo koľko žiakov v triede nosí okuliare.

Veľkou výhodou kontingenčných tabuliek je ich jednoduché a variabilné použitie a tiež to, že pri ich používaní nezasahujeme do pôvodných dát, ktorá zostávajú nemenná. Tieto dáta bývajú vo forme tabuliek alebo databáz.

Dáta pre kontingenčnej tabuľku musí mať iba jeden riadok hlavičky a nemala by obsahovať prázdne bunky.

Oblasti kontingenčnej tabuľky

Kontingenčná tabuľka sa skladá zo 4 oblasti:

• oblasť filtrov - v tejto časti sa nachádzajú filtre, pomocou ktorých môžeme vyberať iba niektoré údaje
• oblasť riadkov - do tejto časti umiestňujeme polia, ktoré budú tvoriť popisky jednotlivých riadkov v tabuľke
• oblasť stĺpcov - do tejto časti umiestňujeme polia, ktoré budú tvoriť popisky jednotlivých stĺpcov v tabuľke
• oblasť hodnôt - do tejto časti umiestňujeme poľa, u ktorých chceme vykonávať výpočty (napr. súčet, priemer, ...)

Vytvorenie kontingenčnej tabuľky

Ako sme si už povedali, kontingenčnej tabuľky môžeme vytvoriť len vtedy, ak máme tabuľku alebo databázu s určitými dátami neobsahujúce prázdne bunky.

V prípade, že tieto dáta máme pripravená, kontingenčnej tabuľky vložíme nasledovne:

• Na karte Vloženie v skupine Tabuľky vyberieme Kontingenčná tabuľka

• Otvorí sa nám dialógové okno Vytvoriť kontingenčnej tabuľku, v rámci ktorého zvolíme tabuľku alebo oblasť dát
• Ďalej v tomto okne tiež volíme, či chceme tabuľku vložiť do existujúceho alebo nového listu zošita

• Vloženie tabuľky potom potvrdíme kliknutím na tlačidlo OK a tabuľka sa nám vloží
• Ďalej je potrebné upraviť Pole kontingenčnej tabuľky, čo je ponuka, ktorá sa nám otvorí v pravej časti listu

• V rámci tejto ponuky preťahujeme myšou jednotlivé polia do jednotlivých oblastí tabuľky
• Tabuľka sa nám podľa toho automaticky aktualizuje a zobrazujú sa v nej polia, ktoré sme vybrali
• Takto môžeme tabuľku upravovať podľa toho, aké dáta chceme práve zistiť

Kontingenčná tabuľka sa dá tiež vložiť z dát v inom zošite, čo ale vyžaduje pokročilú znalosť kontingenčných tabuliek.

Pre lepšiu predstavu, ako kontingenčné tabuľka funguje a na čo ju môžeme využívať, si ukážeme nasledujúce príklady spolu s úpravami tabuliek.

Príklady

V nasledujúcich príkladoch si vytvoríme jednoduché kontingenčnej tabuľky, na ktorých pochopíme princíp vkladania a ich úpravy. Budeme pracovať s tabuľkou, ktorú máme pripravenú z minulých lekcií. V reálnom používaní Excelu sa samozrejme pracuje aj s oveľa väčšími oblasťami dát, ale pre pochopenie princípu nám naša tabuľka bude stačiť.

Najprv chceme zistiť počet predaných kusov tovaru od dodávateľa Pekáreň Novák.

Na karte Vloženie v skupine Tabuľky klikneme na možnosť Kontingenčná tabuľka. Otvorí sa nám dialógové okno Vytvoriť kontingenčnej tabuľku, v ktorom vyberieme oblasť a to tak, že klikneme do prázdneho poľa v riadku Tabuľka / oblasť a označíme ťahaním myši oblasť buniek A1:J6.

Ďalej zvolíme umiestnenie tabuľky, v našom prípade označíme možnosť Nový list. Vloženie potvrdíme kliknutím na tlačidlo OK.

Týmto sa nám vytvorí nový hárok zošita, v ktorom nájdeme našu kontingenčnej tabuľku. Klikneme na kontingenčnej tabuľku, otvorí sa nám okno v pravej časti listu a môžeme prejsť k vkladanie polí do jednotlivých oblastí.

Do oblasti Filtre presunieme poľa Dodávateľ, čo nám neskôr umožní filtrovať dáta kontingenčnej tabuľky podľa dodávateľov. Do oblasti Riadky presunieme poľa Tovar, čo nám vypíše typy tovaru do jednotlivých riadkov. Do oblasti Hodnoty presunieme pole Počet predaných ks, čo nám bude ukazovať počty predaných kusov z jednotlivých produktov od vybraných dodávateľov.

Teraz už v tabuľke máme správne nastavené poľa, ale potrebujeme dáta ďalej vyfiltrovať. V bunke B2 vedľa bunky Dodávateľ klikneme na rozbaľovacie šípku a zvolíme dodávateľa Pekáreň Novák.

Vzhľadom k tomu, že je v predvolenom nastavení tabuľky zobrazovaný Celkový súčet, zobrazuje sa nám už finálny výsledok a to počet predaných kusov tovaru od dodávateľa Pekáreň Novák.

Ďalej chceme zistiť priemerný zisk z predaja dvoch typov produktu - cukru a trstinového cukru.

Vieme, že oba tieto produkty odoberáme od dodávateľa Cukrovar Štípa, vyfiltruje si teda len tohto dodávateľa. Z oblasti Hodnoty odstránime pole Počet predaných kusov kliknutím pravým tlačidlom myši a zvolením možnosti Odstrániť poľa.

Do oblasti si miesto predchádzajúceho poľa vložíme poľa Zisk z predaja. Vieme, že teraz potrebujeme počítať priemer. Na bunku B4 - Súčet z Zisk z predaja klikneme pravým tlačidlom myši a vyberieme položku Nastavenia polí hodnôt ....

Otvorí sa nám dialógové okno, v ktorom zvolíme možnosť Priemer a výber potvrdíme kliknutím na tlačidlo OK.

Teraz už vidíme, že priemerný zisk z predaja je 219,50 Sk za jeden typ cukru.

Chceme tiež vedieť, ktorý typ tovar je potrebné objednať a zároveň jeho aktuálnej skladovú zásobu.

Do oblasti Filter vložíme poľa Objednať. Do oblasti Riadky vložíme polia Tovar a do oblasti Hodnoty vložíme Počet ks skladom 22. 8.. Vyfiltruje si len tovar, ktoré je potrebné objednať.

V rozbaľovacej ponuke v bunke B2 vedľa bunky A2 - Objednať zvolíme teda možnosť ÁNO. Teraz vidíme, že je potrebné objednať chlieb, pretože jeho aktuálna skladová zásoba je iba 5 ks.

Vzhľadom k tomu, že v tomto prípade nepotrebujeme používať Celkový súčet, prejdeme na kartu Návrh a v skupine Rozloženie klikneme na ponuku Celkové súčty a vyberieme Vypnúť pre riadky a stĺpce. Týmto sa nám riadok Celkový súčet odstráni.

Na karte Návrh môžeme vykonávať tiež ďalšie úpravy tabuľky, napr. Meniť rozloženie, štýly tabuľky a ich možnosti.

Vzhľadom k tomu, že kontingenčnej tabuľky a ich možnosti sú naozaj rozsiahle, ukázali sme si v dnešnej lekcii len ich základy tak, aby sme pochopili princípy, ako s nimi pracovať. Ak chceme kontingenčnej tabuľky používať v bežnej praxi, je samozrejme potrebné venovať ich naštudovanie viac času.

To je z tohto e-learning kurzu programu Microsoft Excel pre začiatočníkov všetko. Ak vás zaujímajú ďalšie funkcie a príklady, môžete sa štúdiom pokračovať v kurzoch pre pokročilých.

V nasledujúcom cvičení, Riešené úlohy k 13. lekcii Excel pre začiatočníkov, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.