Potrebujete pomôcť s Excelom?
Kontingenčná tabuľka v Exceli
Kontingenčná tabuľka je v Exceli veľmi silný a efektívny nástroj. Vďaka kontingenčným tabuľkám vieme rýchlo organizovať, zoskupovať a sumarizovať údaje rozsiahlych tabuliek a databáz. Nástroje kontingenčných tabuliek umožňujú porovnávať údaje, odhaľovať vzťahy a analyzovať trendy.
Taktiež umožňuje získavanie rôznych pohľadov na dáta, ktoré analyzujete.
Na aké úlohy je kontingenčka vhodná?
Predstavte si, že máte v hárku veľkú tabuľku, kde sa nachádzajú stovky až tisícky objednávok (položiek) a Vy by ste tieto objednávky potrebovali analyzovať, vytvoriť report, zosumarizovať alebo porovnávať a dostať z nej rôzne údaje, ako napríklad:
-
Koľko ste zarobili / predali v jednotlivých mestách, pobočkách.
-
Koľko kusov jednotlivých produktov ste predali za určité obdobie.
-
Ako by sa Vaše príjmy zvýšili, ak by ste ceny plošne zvýšili alebo, ak by ste predali väčšie množstvo tovarov.
-
Aké % zastúpenie majú jednotlivé produkty na celkovom počte predaných produktov.
-
a mnoho ďalších úloh.
Na čo si pri vytvorení kontingenčných tabuliek dávať pozor?
Ešte pred samotným vytvorením kontingenčnej tabuľky si treba dať pozor na štandardné chyby, ktoré väčšina používateľov Excelu robí.
- Súčtový riadok na konci zdrojovej tabuľky – v kontingenčnej tabuľke tak vzniknú duplicitné dáta, ak si nedáte pozor pri vytváraní kontingenčnej tabuľky, môžete vytvoriť zlé analýzy alebo reporty.
✅ Tip: Oddeľujte si súčtové riadky od zdrojovej tabuľky prázdnym riadkom. - Každý stĺpec musí byť označený vhodným pomenovaním – inak Excel upozorní na chybu. V starších Excelu verziách sa stĺpce v zdrojovej tabuľke nesmeli volať rovnako.
- Prázdny riadok alebo stĺpec v tabuľke, ktorý tabuľku rozdelí na viac častí.
Ako vytvoriť kontingenčnú tabuľku?
Ak je Vaša zdrojová tabuľka v poriadku a nemá nedostatky, prípadne ste ich odstránili, môžete pristúpiť k samotnému vytvoreniu kontingenčnej tabuľky.
-
Označíte zdrojovú tabuľku alebo nastavíte aktívnu bunku do tabuľky, z ktorej si želáte kontingenčnú tabuľku vytvoriť.
-
Na karte Vložiť kliknite na možnosť Kontingenčná tabuľka (Pivot Table).
-
Otvorí sa Vám dialógové okno, kde je možné skontrolovať z akých buniek bude kontingenčná tabuľka vytvorená, a kde bude umiestnená. Umiestnenie môže byť v rámci už existujúceho hárku alebo na novom hárku. Ideálne je dávať kontingenčné tabuľky na nový hárok, ak s nimi začínate.
Keď vytvoríte kontingenčnú tabuľku na nový hárok, dostane +/- podobne vyzerajúci nástroj. Vľavo by ste mali vidieť pole, kde sa Vám bude kontingenčná tabuľka zobrazovať. Vpravo by ste mali byť Polia kontingenčnej tabuľky, čiže nástroj, kde ju viete vyskladať.
Polia kontingenčnej tabuľky
V tejto časti by ste mali vidieť 5 polí. V hornom poli máte názvy stĺpcov Vami načítanej zdrojovej tabuľke. V našom prípade je to ID, Priezvisko, Kategória… Nižšie ďalšie 4 polia a to: Filtre, Stĺpce, Riadky a Hodnoty.
- Filtre – do tohto pola sa dávajú hodnoty, podľa ktorých chceme hodnoty filtrovať.
- Riadky – do tohto pola sa dávajú hodnoty, ktoré budú vypísané do riadkov.
- Stĺpce – do tohto pola sa dávajú hodnoty, ktoré budú vypísané do stĺpcov.
- Hodnoty – do tohto pola sa dávajú prevažne číselné hodnoty, z ktorých sa robí súčet, priemer, min, maximum, počet…
Príklad z praxe
Zo zdrojovej tabuľky by sme chceli zistiť informácie o tom: Aké boli tržby (Cena celkom) na jednotlivých pobočkách a za jednotlivé kategórie nákupu.
- Vyklikneme si všetky potrebné stĺpce v našom prípade: Pobočka, Kategória nákupu a Cena celkom.
- Pobočky necháme vypisovať v riadkoch.
- Kategóriu nákupu presunieme myšou do stĺpcov.
- Cenu celkom necháme v hodnotách, kde Excel spraví automaticky súčet – funkciu SUM.
- Výsledné čísla ešte naformátujeme ako eurá.
Výsledok, ktorý dosiahneme by mohol vyzerať následovne:
Zmena funkcií v tabuľke
Excel Vám v kontingenčnej tabuľke pri číselach v poli hodnota nastaví funkciu SUM. Avšak, nie vždy je táto funkcia žiadúca. Niekedy potrebujete vypočítať aj priemer, maximum, minimum, počet a pod.
Možností ako zmeniť funkciu je viacero:
- 2x klik (dvojklikom) na hľavičku daného stĺpca,
- pravým tlačidlom myši na čísla, kde funkciu chcete zmeniť a vyberte možnosť: Nastavnie poľa hodtnoty,
- kliknúť ľavým tlačidlom myši na hodnotu, kde chcete zmeniť funkciu v poli hodnoty a vyberte: Nastaveie poľa hodnoty.
V dialógovom okne je následne možné vybrať funkciu, ktorú potrebujete.
Ako pridať do KT viac rovnakých hodnôt (stĺpcov)
Ak budete jednotlivé polia (stĺpce) zo zdrojovej tabuľky iba vyklikáť, tak Vám Excel pridá daný stĺpec do KT iba 1x.
Ak potrebujete napríklad Cenu celkom pridať do Hodnôť viackrát, je nutné pole Cena celkom chytiť ľavým tlačidlom myši a presunúť ho do pola Hodnoty.
Ako v kontingenčke pracovať s identifikátormi
V prípade, ak potrebujete do KT pridávať identifikátory, ako napríklad: číslo objednávky, faktúry, osobné číslo zamestnanca a pod.
Tak Excel tieto čísla automaticky zráta, použije funkciu SUM, keďže tá je prednastavená. Treba nastaviť funkciu COUNT.
Karta NÁVRH a jej možnosti
V kontingenčných tabuľkách slúži karta Návrh na rozloženie, navrhnutie a nadizajnovanie kontingenčnej tabuľky.
Excel na karte návrh vo verzii 365 ponúka aktuálne možnosti:
- Medzisúčty
- Celkové súčty
- Rozloženie zostavy
- Prázdne riadky
- Hlavička riadkov a stĺpcov
- Pruhované riadky a stĺpce
- Štýl kontingenčnej tabuľky
Medzisúčty slúžia na zistenie medzisúčtov za Vami pridané polia, resp. stĺpce. Ak máte napríklad v KT pridané 2 a viac polí (stĺpcov / hodnôt z pôvodnej tabuľky), máte možžnosž zapnúť medzisúčty hore alebo dole, prípadne ich vypnúť (nezobrazovať).
Celkové súčty slúžia ne celkovú sumazizáciu buď v danom riadku, stĺpci alebo všetkých hodnôt v tabuľke. Môžete ich vypnúť, zapnúť aj pre riadky, aj pre stĺpce alebo zapnúť len pre riadky, prípadne len pre stĺpce.
Rozloženie zostavy slúži na navrhnutie a rozloženie danej kontingenčnej tabuľky, či budú dáta v jednom stĺpci, či vo viacerých stĺpcov, prípadne či sa budú jednotlivé položky vypisovať alebo či sa nebudú.
Prázdne riadky je nástroj, cez ktorý môžete pridať alebo odstrániť prázdny riadok za každou skupinou. Ide o možnosť, ktorá dokáže KT sprehľadniť.
Ostatné nástroje slúžia na nadizajnovanie Vašej KT. Nastavenie farby, orámovania, výplne pre nepárne riadky alebo stĺpce, prípadne zviditeľenie hlavičky a pod.
Filtrovanie v kontingenčných tabuľkách
Kontingenčné tabuľky ponúkajú používateľom viaceré možnosti filtrovania:
- Klasické fitrovanie priamo v KT.
- Filtrovanie cez pole Filtre, kde sa dá filtrovať nad KT.
- Filrovanie prostredníctvom pomocných stĺpcov.
- Filtrovanie cez násjtro Rýchly filter a Časová os.
Vzorce a výpočty v kontingenčnej tabuľke
Veľa používateľov kontingenčných tabuliek robí výpočet a vzorce v KT zle.
Dodatočné výpočty si vzorcujú vo vedľajších stĺpcoch cez klasické vzorce (=A4+B4) a pod.
Pričom práca s takýmito pomocnými stĺpcami je v KT veľmi neefektívna, existuje totiž nájstroj Vypočítavané pole, ktorý sa nachádza na karte Analyzovať.
Percentuálne % podiely a pomery
Rovnako ako pri bežných výpočtoch, tak aj pri percentuálnych pomeroch väčšina používateľov siahne po výpočtoch v pomocnýc stĺpcoch.
Prípadne ešte kontingenčnú tabuľku zničia -> použijú nástroj Prilepiť špeciálne – Hodnoty. Následne spravia v pomocnom stĺpci výpočet % pomeru.
Zoskupovanie údajov v KT
Kontingenčné tabuľky ponúkajú veľmi rýchly a efektívny spôsob ako zoskupovať textové, dárumové a časové údaje bez toho, aby ste ich museli upravovať v zdrojovej tabuľke.
Tento nástroj je v praxi veľmi žiadaný, ak treba údaje zoskupiť podľa nejakej lokality, podľa mesiacov, štvrťrokov, rokov, hodín, minút a podobne.
Nájdete ho na karte Analyzovať
Zoskupiť výber: Zoskupuje textové hodnoty, ktoré treba pred zoskupením označiť.
Zoskupiť polia: Zoskupuje dátumové a časové hodnoty, stačí, ak budete kliknutí v nejakom dátume alebo čase.
Obnovenie a rozširovanie údajov
Obnovenie a rozširovanie údajov nájdete na karte Analýza.
Obnoviť: slúži na obnovenie kontingenčných tabuliek, ak sa zmenia údaje v zdrojovej tabuľke. Často sa používa možnosť Obnoviť všetko, kedy Excel obnoví všetky kontingenčné tabuľky v danom zošite.
Zmeniť zdroj údajov: tento nástroj ponúka rozšírenie zdrojových údajov pre kontingenčnú tabuľku. Pozor, že rozšírenie údajov sa aplikuje iba na danú kontingenčnú tabuľku, nie na všetky.
Na túto informáciu treba myslieť už pri smotnom vytvorení KT. Jeden zo spôsobov ako sa vyhnúť viacnásobnému rozširovaniu viacerých KT je preťaženie vstupných dát, čo nie je do praxe vhodné, druhým je možnosť naformátovať zdrojovú tabuľku cez nástroj Formátovať ako tabuľku alebo tretí spôsob je pomocou nástroja Pole názvov.
Ako vymyzať, presunúť alebo označiť kontingenčnú tabuľku
Tieto úkony s KT môžete robiť napríklade cez klávesové skratky:
- Označenie: kliknite do kontingenčnej tabuľky a stlačte CTRL + A.
- Vymazanie: ako prvé treba kontingenčku označiť a následne môžete použiť klávesu DELETE.
- Presunutie: najprv je nutné kontingenčku označiť. Na vystrihnutie sa používa klávesová skratka CTRL + X. Následne kliknite do bunke, kde chcete KT presunúť a stlačte CTRL + V.
Rovnako sa tieto možnosti nachádzajú aj na karte Analyzovať.
Kontingenčný graf
Zjednodušene povedané, kontingnenčný graf je úplne rovnaký graf ako klasický, akurát je vytvorený z kontingenčnej tabuľky. Ponúka rovnaké možnosti a nastavenie.
Táto možnosť sa nachádza na karte Analyzovať, kde je nástroj Kontingenčný graf.
Aké typy kontingenčných grafov je možné vytvoriť:
- Stĺpcový
- Čiarový
- Koláčový
- Pruhový
- Plošný
- X Y (bodový)
- Mapa
- Burzový
- Povrchový
- Radarový
- Stromová mapa
- Lúčový
- Histogram
- Škatuľový
- Vodopádový
- Lievikový
- Kombinovaný
Potom ako vytvoríte kontingenčný graf Vám Excel sprístupní 3 nové karty:
- Analýza kontingenčného grafu: tu je možné vkladať Rýchly filter, Časovú os, pripájať filtre, obnovovať a meniž rozsah zdrojových údajov a pod..
- Návrh: táto karta slúži na navrhnutie grafu a pridávanie jednotlivých prvkov a nastavení.
- Formát: táto karta ponúka možnosti formátovania daného grafu.
Karty Návrh a Formát sú rovnaké ako pri klasických grafoch.
