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.

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

 

Je krásny začiatok dňa ja som sa po prvých pondelkových minútach rozhodol začať s ďalším článkom. Sample file pre zmenu ostane ten istý, aký sme použili v minulom článku a tým pádom sa môžeme začať hrať.

V nasledujúcich dieloch sa budeme pohybovať po záložkách Insert a Data. Začneme zložkou Data, ktorá disponuje oblasťami Data Tools a Outline. V Data Tools sme si možnosť Text to Columns už rozobrali a taktiež aj nahrali naň makro v minulom článku.

Odstránenie duplicít sme si taktiež popísali pomocou Advanced Filter, no v konečnom dôsledku môžeme použiť Remove Duplicates. Táto vymoženosť nám umožní zbaviť sa duplicít vo vyznačenej oblasti. V našom prípade sa môžeme zbaviť duplicít v jednotlivých stĺpcoch. Po kliknutí sa nám zobrazí okno, v ktorom môžeme označiť (Select All) alebo odznačiť všetky stĺpce, no HLAVNOU možnosťou je zvolenie si konkrétnych stĺpcov, ktoré si prajete mať bez duplicít a potom OK.

Ďalšou užitočnou možnosťou je Data Validation. Asi najviac používanou vlastnosťou je preddefinovanie možných hodnôt v konkrétnom stĺpci. Vezmime v úvahu stĺpec edcat. Vieme, že máme len 5 typov vzdelania a nechceme sa vystaviť možnosti, že by sme my alebo niekto v budúcnosti nechtiac nadefinovali ďalšiu formu ukončeného štúdia. Alebo chceme evidovať všetky, okrem postgraduálneho. Takto vysoko vzdelaných ľudí je prevažne málo a pravdepodobnosť, že sa zúčastnia poistnej udalosti je veľmi malá. Kliknime teda na Data Validation, potom zvoľme možnosť Settings a objaví sa nám okno, v ktorom si môžeme vybrať ako chceme nadefinovať premenné(položka Allow:). V našom prípade vyberieme spôsob List a v možnosti Source: zvolíme oblasť buniek, v ktorej sa nachádzajú špecifické hodnoty (viď obrázok). Klikneme na OK a  v tabuľke si môžeme všimnúť, že pri bunkách sa nám zobrazuje taká bočná šípka. Po jej rozkliknutí sa nám zobrazia možnosti zvolenia, aké sme si prednastavili. V prípade ak nás trápia postgraduálni klienti a chceme ich mať nejako vyznačených, môžeme si zvoliť možnosť Circle Invalid Data. Bohužiaľ, vývojári sa  ňou moc nezaoberali, lebo vám zakrúžkuje len prvých 255 neplatných buniek, takže ju používajte len v prípade malého množstva dát.  Pre odstránenie krúžkov jednoducho zvolíme Clear Validation Circles.

Nasledujú položky ako Consolidate a What-If Analysis. Priznám sa, za svoju povedzme pracovnú kariéru som nikdy nemal za potrebu použiť tieto možnosti. Consolidate  sa používa v prípade ak máte v rôznych listoch, poprípade rôznych súboroch rovnaké typy dát, ktoré potrebujete buď spočítať, spriemerovať apod. Po kliknutí sa zobrazí okno v ktorom zvolíte matematický úkon (funkciu). V položke Reference: vyberiete oblasť koherentných dát a potvrdíte Add alebo odoberiete pomocou Remove. výsledok sa vám zobrazí od označenej bunky. V prípade ak chcete len premiestniť istý počet stĺpcov bez dodatočných výpočtov, zaškrtnite možnosť Top row. Takže po poriadku, pre nakopírovanie použijeme túto možnosť s Top row a pre stĺpec, rovný sume jednotlivých premenných nepoužijeme Top row. Práve sme sa naučili jeden z ďalších spôsobov, ako kopírovať dáta a ešte k tomu elegantným spôsobom. Problematika What-If Analysis je komplexnejšia, preto odporúčam sa jej venovať neskôr, keď budeme riešiť konkrétne výpočty a problematiky. Nasledujúci diel začneme naplno očakávanými súhrnmi a kontingenčnými tabuľkami.