14. diel - Kontingenčnej tabuľky v programe Excel
V predchádzajúcom cvičení, Riešené úlohy k 13. lekcii Excel pre začiatočníkov, sme si precvičili získané skúsenosti z predchádzajúcich lekcií.
V tomto tutoriáli základov Excelu sa budeme venovať kontingenčným tabuľkám.
Kontingenčná tabuľka
Kontingenčná tabuľka nám umožňuje pozrieť sa na dáta z rôznych uhlov pohľadu a tým ich jednoduchšie analyzovať. Vďaka tomuto typu tabuľky si môžeme ľahko odpovedať na otázky, ako sú napríklad:
- Koľko kusov tovaru sme naskladnili v určitom mesiaci?
- 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čnú tabuľku musia mať iba jeden riadok hlavičky a nemali by obsahovať prázdne bunky.
Oblasti kontingenčnej tabuľky
Kontingenčná tabuľka sa skladá zo štyroch oblastí:
- Oblasť Filtrov – obsahuje filtre, pomocou ktorých možno zobraziť iba vybrané dáta.
- Oblasť Riadkov – sem sa presúvajú polia, ktoré budú tvoriť popisky jednotlivých riadkov v tabuľke.
- Oblasť Stĺpcov – sem sa presúvajú polia, ktoré budú tvoriť popisky jednotlivých stĺpcov v tabuľke.
- Oblasť Hodnôt – sem sa umiestňujú polia, pri ktorých chceme vykonávať výpočty (napr. súčet, priemer a pod.).
Vytvorenie kontingenčnej tabuľky
Kontingenčnú tabuľku možno vytvoriť iba z dátovej oblasti bez prázdnych buniek, najčastejšie vo forme tabuľky alebo databázy.
Ak máme dáta pripravené, klikneme na karte Vložiť v skupine Tabuľky na možnosť Kontingenčná tabuľka (prípadne spresníme voľbou Z tabuľky alebo rozsahu):
Otvorí sa dialógové okno Kontingenčná tabuľka z tabuľky alebo rozsahu, v ktorom vyberieme tabuľku alebo oblasť dát, z ktorej má byť kontingenčná tabuľka vytvorená. Potom zvolíme, či sa má tabuľka vložiť do existujúceho alebo nového hárka zošita:
Vloženie potvrdíme kliknutím na tlačidlo OK. Kontingenčná tabuľka sa vloží do hárka a zároveň sa v pravej časti zobrazí ponuka Polia kontingenčnej tabuľky:
V tejto ponuke presúvame myšou jednotlivé polia do príslušných oblastí kontingenčnej tabuľky. Tabuľka sa podľa zvolených polí automaticky aktualizuje. Zobrazené dáta potom možno kedykoľvek meniť podľa aktuálnych potrieb.
Kontingenčnú tabuľku je možné vytvoriť aj z dát uložených v inom zošite, čo však už vyžaduje pokročilejšiu prácu s kontingenčnými tabuľkami.
Pre lepšie pochopenie fungovania kontingenčných tabuliek a ich praktického využitia si v nasledujúcej časti ukážeme konkrétne príklady a úpravy tabuliek.
Príklady práce s kontingenčnou tabuľkou
V nasledujúcich príkladoch si vytvoríme niekoľko jednoduchých kontingenčných tabuliek, na ktorých si vysvetlíme základný princíp ich tvorby a úprav. Pracovať budeme s tabuľkou pripravenou z predchádzajúcich lekcií. V praxi sa síce často stretneme s oveľa väčším objemom dát, na pochopenie princípu však táto ukážková tabuľka plne postačí.
Počet predaných kusov podľa dodávateľa
Najprv zistíme počet predaných kusov tovaru od
dodávateľa Pekáreň Novák:
- Na karte Vložiť v skupine Tabuľky zvolíme možnosť Kontingenčná tabuľka.
- Otvorí sa dialógové okno Kontingenčná tabuľka z tabuľky alebo
rozsahu, v ktorom klikneme do poľa Tabuľka alebo rozsah a
označíme oblasť buniek
A1:J6. - Ako umiestnenie tabuľky zvolíme Nový hárok a voľbu potvrdíme tlačidlom OK:
V zošite sa vytvorí nový hárok s prázdnou kontingenčnou tabuľkou. Po kliknutí do tabuľky sa v pravej časti hárka zobrazí ponuka Polia kontingenčnej tabuľky. V tejto ponuke:
- Do oblasti Filtre presunieme pole Dodávateľ.
- Do oblasti Riadky presunieme pole Tovar.
- Do oblasti Hodnoty presunieme pole Počet predaných ks.
Tabuľka sa automaticky aktualizuje a zobrazí súčty predaných kusov podľa jednotlivých typov tovaru.
Následne v bunke B1 vedľa položky Dodávateľ
otvoríme rozbaľovaciu ponuku, vyberieme dodávateľa
Pekáreň Novák a výber potvrdíme.
Pretože je v predvolenom nastavení zapnutý Celkový súčet, zobrazí sa rovno výsledný počet predaných kusov tovaru od tohto dodávateľa:
Výpočet priemerného zisku z predaja
Ďalej zistíme priemerný zisk z predaja dvoch produktov – cukru a trstinového cukru.
Oba produkty odoberáme od dodávateľa Cukrovar Trnava. Pole
Dodávateľ máme vložené v oblasti Filtre, takže v
kontingenčnej tabuľke (v rozbaľovacej ponuke filtra vedľa položky
Dodávateľ, v bunke B1) vyberieme iba tohto
dodávateľa.
Z oblasti Hodnoty odstránime pole Počet predaných ks (v poli pod názvom Súčet z Počet predaných ks) kliknutím pravým tlačidlom myši a voľbou Odstrániť pole:
Do oblasti Hodnoty následne vložíme pole Zisk z predaja.
Pretože chceme namiesto súčtu počítať priemer, klikneme pravým tlačidlom myši na položku Súčet z Zisk z predaja v kontingenčnej tabuľke a zvolíme Nastavenie poľa hodnoty….
Ponuku možno alternatívne otvoriť kliknutím ľavým tlačidlom myši na šípku vedľa položky Súčet z Zisk z predaja v okne Polia kontingenčnej tabuľky.
V dialógovom okne vyberieme funkciu Priemer a voľbu potvrdíme tlačidlom OK:
Kontingenčná tabuľka teraz zobrazuje, že priemerný zisk z
predaja predstavuje 9,35 €:
Kontrola skladových zásob a tovaru na objednanie
Na záver zistíme, ktorý tovar je potrebné objednať a aká je jeho aktuálna skladová zásoba.
- Do oblasti Filtre vložíme pole Objednať.
- Do oblasti Riadky vložíme pole Tovar.
- Do oblasti Hodnoty vložíme pole Počet ks na sklade 22.9.
V rozbaľovacej ponuke filtra Objednať zvolíme hodnotu
ÁNO.
Výsledkom je zobrazenie tovaru, ktorý je potrebné objednať. V našej ukážke ide o chlieb, ktorého aktuálna skladová zásoba predstavuje iba 5 ks.
Pretože tu nepotrebujeme riadok Celkový súčet, prejdeme na kartu Návrh, v skupine Rozloženie otvoríme ponuku Celkové súčty a zvolíme Vypnúť pre riadky a stĺpce. Tým sa riadok celkového súčtu odstráni:
Na karte Návrh možno vykonávať aj ďalšie úpravy kontingenčnej tabuľky, napríklad meniť jej rozloženie, štýly alebo zobrazované prvky.
Kontingenčné tabuľky ponúkajú veľmi široké možnosti práce s dátami. V tejto lekcii sme sa zamerali iba na ich základné použitie, aby sme pochopili hlavné princípy fungovania. Pre ich efektívne využitie v bežnej praxi je vhodné venovať sa im podrobnejšie.
Týmto sa končí časť e-learningového kurzu Základy Microsoft Excel. Ak vás práca s Excelom zaujala a chcete si svoje znalosti ďalej rozšíriť, môžete pokračovať nadväzujúcim kurzom Microsoft Excel pre pokročilých.
V nasledujúcom cvičení, Riešené úlohy k 14. lekcii Excel pre začiatočníkov, si precvičíme nadobudnuté skúsenosti z predchádzajúcich lekcií.
