Ako sa robí kontingenčná tabuľka v Exceli?

Tento článok napísal lektor z Easy Excel, autor obľúbených video kurzov Excelu. Ak aj vy máte záujem učiť sa z pohodlia domova, otvorte si www.easyexcel.sk.

Kontingenčná tabuľka v Exceli je šikovným pomocníkom hlavne pre užívateľov pracujúcich s veľkým množstvom dát. Jej vytvorenie trvá pár minút a výsledok je skutočne prehľadný a nápomocný. Teraz sa dozviete, čo to vlastne kontingenčná tabuľka je, na čo sa využíva a ako ju pomocou programu Excel vytvoriť.

Čo je kontingenčná tabuľka?

Kontingenčná tabuľka obsahuje skupinu prehľadne zhrnutých hodnôt pochádzajúcich z tabuľky zdrojovej.

Zdrojovou tabuľkou rozumieme vo väčšine prípadov celkový súbor dát.

Kontingenčná tabuľka je vlastne novou, jednoduchšou tabuľkou čerpajúcou vybrané hodnoty z rozsiahlejšieho zdroja. Pomocou niekoľkých argumentov vytiahne len pre nás potrebné stĺpce a riadky pričom ich dokáže jednoducho prepájať spôsobom iným ako tabuľka zdrojová. Ide v podstate o novú tabuľku obsahujúcu len vybrané dáta z pôvodného súboru.

Na čo sa využíva kontingenčná tabuľka?

Kontingenčná tabuľka je šikovným pomocníkom pri práci s veľkým množstvom dát. Využíva sa hlavne v prípadoch, kedy máme záujem iba o konkrétne stĺpce zdrojovej tabuľky a ich vzájomné prepojenia s ohľadom na iné ako pôvodné vzťahy. Orientácia vo veľkých databázach je často náročná.

Pomocou kontingenčnej tabuľky vieme prehľadne prenášať vybrané stĺpce a riadky s cieľom poukázať na prepojenie a zhrnutie obsiahnutých hodnôt, ktoré zdrojová tabuľka neposkytuje. Vyhotovujeme tak novú, prehľadnú tabuľku bez stĺpcov a riadkov, ktoré pre nás v danom probléme nemajú význam. Kontingenčná tabuľka tým umožňuje hlbšiu analýzu dát.

Ako v Exceli vytvoriť kontingenčnú tabuľku?

Pre vytvorenie kontingenčnej tabuľky v Exceli je v prvom rade potrebné mať kompletnú tabuľku zdrojovú. Základným pilierom jednoducho a správne vyhotovenej kontingenčnej tabuľky je pomenovanie stĺpcov zdroja. Systém totiž pomocou týchto pomenovaní rozpoznáva jednotlivé dáta.

Pri vybraní obsahu si dávame pozor na nechcené riadky v stĺpcoch ako napríklad celkový súčet či aritmetický priemer. Excel tieto riadky pri prenose do kontingenčnej tabuľky zohľadňuje akoby patrili medzi jednotlivé hodnoty. To pochopiteľne vedie k nesprávnemu výsledku.

Príkaz kontingenčná tabuľka nájdeme na karte vložiť. Po kliknutí program vráti okno s počiatočnými argumentami, ďalej s výberom stĺpcov hodnôt a ich rozmiestnením v novej tabuľke.

Kontingenčná tabuľka v praxi

Kontingenčnú tabuľku v praxi zrozumiteľne ilustruje príklad nižšie.

Na úvod si skúsme predstaviť, že sme predajcovia ruží. V našej zdrojovej tabuľke vidíme súhrn objednávok za kalendárny rok. V stĺpci A vidíme číslo objednávky, v stĺpci B objednávateľa, stĺpec C obsahuje mestá kam sme kvetiny doručili, stĺpec D dátum objednávky, v E vidíme druh ruže, ďalej v stĺpci F počet kusov a v stĺpcoch G a H cenu za kus a cenu za celkovú objednávku.

Naša zdrojová tabuľka obsahuje veľké, priam neprehľadné množstvo dát. Chceli by sme zistiť, aký zisk sme celkovo vykázali v jednotlivých mestách za kalendárny mesiac. Vyčítať takéto hodnoty priamo zo zdrojovej tabuľky by bolo časovo pomerne náročné.

Problém možno jednoducho riešiť pomocou kontingenčnej tabuľky. Kontingenčná tabuľka vytiahne potrebné hodnoty pomocou zadania stĺpcov, roztriedi ich do príslušných kategórií, prepojí a vyhodnotí zisky.

Na karte vložiť si navolíme príkaz kontingenčná tabuľka. Systém vyvolá okno s názvom vytvorenie kontingenčnej tabuľky. Prvým argumentom našej kontingenčnej tabuľky bude riadok tabuľka alebo rozsah. Tu v našom prípade označíme celé pole zdrojovej tabuľky.

Systém od nás v podstate požaduje zvolenie súboru hodnôt, z ktorých má pri vytváraní novej, kontingenčnej tabuľky čerpať. Možno tu taktiež navoliť externý zdroj, čo znamená, že chceme údaje čerpať z databázy inej ako tá na našom hárku. Ďalej zaškrtneme umiestnenie kontingenčnej tabuľky. Tu volíme z dvoch jednoduchých možností. Pre lepšiu prehľadnosť sme si v našom prípade navolili umiestnenie na nový hárok v Exceli.

Po potvrdení nás systém presmeruje na nový prázdny hárok a ihneď požaduje zadanie ďalších argumentov. Tu je potrebné v zozname v pravej časti obrázka zaškrtnúť názvy stĺpcov, ktoré budeme chcieť do novej tabuľky preniesť. Nás teda zaujíma stĺpec s názvom Mesto, Cena celkom a Dátum. V prípade dátumu si možno rozkliknutím riadka navoliť či nás budú zaujímať dni, roky alebo mesiace. My si jednoznačne volíme mesiace.

Systém automaticky roztriedi, ktoré z našich hodnôt sa v kontingenčnej tabuľke zobrazia v riadkoch, a ktoré naopak v stĺpcoch. Automatickú voľbu zmeníme pretiahnutím do príslušného poľa. Do poľa hodnoty sme si pretiahli stĺpec s názvom Súčet z cena celkom. Hodnoty znamenajú to čo bude laicky povedané tvoriť triedený obsah našej tabuľky.

Mestá si necháme zobraziť v riadkoch, mesiace v stĺpcoch a celkové súčty cien objednávok logicky priradené v príslušných bunkách z tela tabuľky. Excel automaticky doplnil stĺpec N s celkovým ziskom pre jednotlivé mestá a riadok 14 s celkovým ziskom za daný mesiac.

Po malej kozmetickej úprave dostávame hotovú kontingenčnú tabuľku. Za pár sekúnd sme si dokázali vytvoriť stručný prehľad ziskov. Najviac sme zarobili v Bratislave naopak najmenej ruží sme predali v Martine. V riadku vidíme, že najmenší zisk sme vykázali v novembri naopak najväčší v septembri.

Tento článok napísal lektor z Easy Excel, autor obľúbených video kurzov Excelu. Ak aj vy máte záujem učiť sa z pohodlia domova, otvorte si www.easyexcel.sk.