Súhrny a kontingenčné tabuľky (2. diel)

 

Týmto článkom doberieme zvyšné dôležité možnosti typu Group, Subtotal a taktiež si urobíme kontingenčnú tabuľku. Teda nasledujúcu časť záložky Data. Čo sa týka dát, ostaneme verní sample file-u, ktorý sme používali v minulých dieloch.

V prípade, ak máte veľkú tabuľku a pracujete súčasne so stĺpcami typu A, B apod. spolu so stĺpcami relatívne vzdialenými ako AL, AM. Ja som tieto súhrny používal napríklad, keď som písal komplexnejšie vzorce (kombinácia VLOOKUP s ISNA a iné) a potreboval spájať jednotlivé, takto vzdialené bunky.  

Označme si stĺpce E až N a použijeme možnosť Group. Objaví sa nám povedzme šedé rameno s koncovým štvorčekom so znamienkom mínus. Ak kliknete na tento štvorček, tak sa vyznačené stĺpce skryjú a naopak. Túto metódu samozrejme môžeme použiť aj pre riadky a v prípade, že ten súhrn chcete odstrániť, stačí označené stĺpce (riadky) odkliknúť možnosťou Ungroup.

Avšak omnoho užitočnejším nástrojom je Subtotal. Túto možnosť môžeme považovať ako derivát kontingenčnej tabuľky. Zoraďme si dáta podľa stĺpca edcat. V položke At each change in: zvoľte stĺpec, podľa ktorého  každej zmenenej hodnoty sa budú vytvárať definované súhrny, teda v našom prípade po každej zmene vzdelania (edcat). V Use function: vyberieme konkrétnu funkciu, v našom prípade chceme sumu príjmov, teda sum. V poli Add subtotal to: vyberieme stĺpce, v ktorých chceme identifikovať našu funkciu, sumu (income). Výsledok si môžete overiť na svojom sample file :-) . V prípade, že chcete zmazať tieto súhrny, použijete v dialógovom okne Remove All.

Tak a teraz vážení čitatelia prichádzame k veľmi dôležitému, pojmu kontigenčných tabuliek. Pre niekoho nočná mora, v lepšom prípade len veľká neznáma. No každopádne uvidíte, že sa nejedná o nič zložité! Ostaňme na liste, v ktorom sme robili predchádzajúce operácie. Avšak prejdime na záložku Insert a Pivot Table. Zobrazí sa nám dialógové okno, v ktorom nastavíme potrebné parametre. Table Range: potrebuje mať nadefinovanú vstupnú oblasť (užitočné sú pomenovania oblasti). Potom si môžete vybrať, kam chcete vložiť túto tabuľku. Ja väčšinou ukladám na nový list, takže ponechávam New Worksheet. Klikneme na OK a tým pádom sme vytvorili kontingenčnú tabuľku, pre zjednodušenie ju budeme volať pivot table. Vytvorí sa  nám nový list, ktorý si môžeme pomenovať a v ňom sa objavia nasledujúce tabuľky. Pre nás bude tzv. manipulačná tabuľka PivotTable Field List, v ktorej si nastavíme rozloženie pivot table. V nej sú k dispozícii 4 kvadranty. Report Filter je kvadrant, do ktorého priraďujeme premenné, podľa ktorých chceme filtrovať hodnoty (ako keď sme používali filtre pre jednotlivé stĺpce). V našom sample file som v ňom nadefinoval všetky binárne stĺpce (“Yes” a “No” hodnoty). Column a Row labels sú kvadranty, v ktorých budú premenné, podľa ktorých budú nadefinované riadky a stĺpce tabuľky. Values kvadrant je výpočtová časť tabuľky, v ktorej nadefinujete jednotlivé stĺpce a výpočty, ktoré potrebujete (súčty ,priemery, počty apod.) Pre lepšiu orientáciu by ste mali mať vytvorený Pivot table takto:

Všimnite si, že tabuľka je celkom úhľadne rozpracovaná, avšak vadí nám to ,že máme stĺpce pomenované ako Sum of coverage a Sum of claim_amount. Zmenu názvu urobíme kliknutím na dané položky v 4. kvadrante a potom na Value Field Settings… Nastane opäť tá istá procedúra, teda dialógové okno a v ňom si môžete okrem názvu nastaviť aj tú matematickú funkciu. V položke Custom Name: zmeníme názov na kumulatívny coverage. Možnosti ako Number Format alebo Show Values As si myslím ,že zvládnete pochopiť aj bez toho, aby som sa musel rozpisovať. Avšak ukážem vám jednu takú delikatesu, ktorá sa celkom môže hodiť. Zamerajme sa priamo na tabuľku a všimnime si, že máme rozbalené jednotlivé row labely. V záložke Options máme na výber rozbalenie alebo zabalenie dát. Kliknite na Collapse Entire Field a dostanete jednotlivé riadky podľa income. V mnohých prípadoch by bolo dobré takto vytvorené pivot tabuľky upraviť tak, aby sme mali income platy roztriedené do intervalov. Kliknite pravým tlačidlom na bunku v stĺpci Row Labels a vyberte možnosť Group. Potom stačí v okne nadefinovať minimum a maximum spolu s dĺžkou intervalov a máte vytvorené intervalové skupiny. Vidíme, že sa nám tabuľka zmenšila na 9 podskupín podľa income intervalov a tým pádom + vytvorili jednotlivé charakteristiky. Ďalšou, povedzme novinkou v rámci Excel programu sú Slicer-y. Kliknite v záložke Options na možnosť Insert Slicer a vyberte všetky stĺpce, ktoré ste nadefinovali vo Filter kvadrante a potvrďte OK. Zobrazia sa vám malé tabuľky, v ktorých môžete selektovať priamo dáta  a tým pádom nemusíte zbytočne preklikávať sa v pivot table. Ďalšie možnosti, vrátane Pivot grafu si myslím, že zvládnete sami, v konečnom dôsledku je to o praxi. Práve preto odporúčam si skúšať kombinovať rôzne typy pivot tabuliek a skúšať možnosti či už formátovania, tvorby slicerov alebo grafov. Týmto článkom by som ukončil sériu článkov o používaní programu Excel a zameral sa na oblasť Štatistiky a Financií.

Takže sa môžete tešiť ne jednotlivé kreatívnejšie články, v ktorých sa budeme venovať komplexnejšiemu rozboru a analýze dát.