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.
Video: Kedy používať kontingenčné tabuľky?
V nasledovnom videu Vám priblížime, kedy je vhodné kontingenčky používať.
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:
Video: Ako vytvoriť kontingenčnú tabuľku
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.
Video: Ako pridať do KT viac rovnakých hodnôt (stĺpcov) a zmena funkcií
Video: 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á.
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.
Video: Čo ponúka karta Návrh
Pozrite si video, kde Vám na príkladoch z praxe jednotlivé nástroje aj ukážeme.
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.
Video: Ako filtrovať v kontingenčke
V nasledujúcich videách Vám tieto možnosti problížime.
Video: Ako filtrovať pomocné stĺpce
Filtrovanie pomocných stĺpcov veďla kontingenčnej tabuľke je večný problém. V prípade, ak si mimo kontingenčnej tabuľky v pomocnom stĺpci niečo zadáte a budete filtrovať priamo v KT, tak sa budú filtrovať / skrývať len riadky v KT.
Pomocné stĺpce sa neprispôsobia a zostanú viditeľné.
Neviete ako tento problém vyriešiť? Pozrite si naše video.
Prostredníctvom nástrojov Rýchly filter a Časová os môžete filtrovať viaceré kontingenčné tabuľky súčasne. Stačí, ak si ich iba spojíte. Ak neviete ako, pozrite si naše video.
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ť.
Video: Vypočítavané pole v KT
Ako teda pracovať s nástrojom Vypočítavané pole, prečo je efektívny do praxe a kedy ho využiť, Vám ukážeme v nasledovnom videu.
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.
Video: Percentuálne pomery a podiely
Ako teda správne a efektívne vypočítať % podiel bez toho, aby ste museli zničiť kontingenčnú tabuľku a vypočítavali ste si to v pomocnom stĺci? Pozrite si naše video, kde Vám to vysvetlíme.
Video: Ako v kontingenčnej tabuľke vypisovať čísla
Nastanú prípady, kedy budete potrebovať čísla vypisovať a nie ich sumarizovať, priemerovať a pod. Ako to teda spraviť, ak Excel čísla automaticky pri vykliknutí hodí do hodnôť a aplikuje na tieto čísla funkciu SUM?
Pozrite si naše prehľadné video z praxe.
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.
Video: Ako zoskupovať textové hodnoty
V nasledujúcom videu Vám ukážeme ako, kedy a prečo by sa Vám mohlo hodiť zoskupovanie textových údajov v kontingenčných tabuľkách.
Video: Ako zoskupovať dátumové a časové hodnoty
V ďalšom videu Vám ukážeme akým spôsobom môžete zoskupiť dátumy do rokov, štvrťrokov alebo mesiacov a čas zoskupiť do hodín a minút.
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.
Video: Ako v kontingenčnej tabuľke obnoviť a rozšíriť údaje
Video: Ako rozšíriť zdrojové údaje pre viaceré KT
V tomo videu Vám ukážeme rozširovanie údajov cez nástroj Pole názvov, kde Vám stačí vykonať zmenu na jednom mieste a aktualizujete všetky kontingenčné tabuľky v celom zošite.
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ť.
Video: Presúvanie, vymazanie a označovanie KT
Pozrite si naše video, kde nájdete ukážky ako tieto možnosti fungujú.
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.
Video: Ako vytvoriť kontingenčný graf
Vytvorenie grafu a jeho možnosti Vám priblížime aj v nasledovnom videu.
Video: 2x (dvojklik) na číslo v kontingenčnej tabuľke
Stalo sa Vám niekedy, že ste omylom klikli v kontingenčnej tabuľke na nejaké číslo 2x a zrazu sa objevili nejaké dáta? Nevedeli ste čo a prečo sa tak stalo? Pozrite si naše video.
