Filtrovanie dát

Dnes si vážení čitatelia povieme niečo o filtrovaní dát. Táto činnosť je alfou a omegou práce s reportmi.  Vo väčšine prípadov je situácia taká, že analytik musí vedieť si dáta exportovať do Excelu, tam ich ošetriť a vymedziť tie potrebné pre prácu. Práve preto budeme mať k dispozícii reálnejší sample file, v ktorom tých hodnôt bude viac.

Ako ste si už určite stihli z obrázka vydedukovať, áno je to tak, budeme

sa celý čas pohybovať v záložke Data a oblasti Sort & Filter. 

Začnime možnosťami Sort A-Z a Z-A.

Ak nasmerujete kurzor na akúkoľvek bunku daného stĺpca a kliknete na  Sort A-Z alebo Sort Z-A, usporiada sa Vám celý súbor podľa abecedy (číselnej veľkosti) stĺpca, v ktorom sa nachádza označená bunka (viď video).

Prejdime k tlačidlu Filter. Čo sa stane, ak ho použijeme? Ak si naň kliknete, objavia sa na bunkách, definujúcich stĺpce matice rozbaľujúce tlačidlá, ktoré ak individuálne rozbalíte, dostanete nové – rozsiahlejšie možnosti filtrovania. Okrem zmienených Sort A-Z a Z-A možností si v prípade farebných buniek v stĺpci môžete filtrovať dáta podľa farieb, teda Sort by Color alebo modifikovať konkrétnejší filter cez Number Filters, v prípade dátumového stĺpca Date Filters. Nesmieme zabudnúť taktiež na textové stĺpce a pre nich špecifické Text Filters. Prejdite na stĺpec deductible a aktivujte filter. Potom si pomocou Number Filters môžete definovať numerické podmienky a pomocou farby na pozadí v stĺpci Filter by Cell Color alebo prostredníctvom farby textu – Filter by Font Color.  Je to užitočné, hlavne ak budete pracovať na liste, ktorý vytvoril esteticky založený analytik. No asi najviac využívanou možnosťou je výber jednotlivých skupín, podľa potrieb od (Select All), ktoré sú defaultne nastavené až po jednotlivé hodnoty alebo skupiny hodnôt.

V prípade viacnásobného selektovania si môžete kliknúť na Sort a následne selektovať podľa jednotlivých stĺpcov (premenných). Usporiadajte dáta podľa claim_amount, coverage od najmenších po najväčšie a zároveň podľa income od najvyššieho po najmenšie (viď obrázok). Jednotlivé podmienky pridávate pomocou Add Level a odoberáte pomocou Delete Level.

Musím však poukázať aj na možnosť Advanced. Na úvod by ste si pri použití tohto filtra mali vytvoriť nový list, v ktorom budete vytvárať jednotlivé Criteria range, teda výberové kritéria. V tomto prípade sa taktiež oplatí pomenovať maticu, z ktorej chcete filtrovať dáta. Sami musíte uznať, že je lepšie v List range napísať jednoducho názov matice insurance, než manuálne označovať oblasť ako napr. A1:S4416. V sample file máte pripravené tri listy pre výstup z Advanced Filter a v liste parametre zase nadefinované jednotlivé kritéria, ktoré som pomenoval exaktne podľa názvu listu, v ktorom budú výstupy filtrovania (tri listy, tri typy kritérií).  Celý postup vypíšem nasledovne:

• Určím si, či chcem filtrovať v základnej matici (Filter the list, in-place) alebo chcem požadované dáta dostať na nový list(Copy to another location). Ja som v sample file vybral možnosť druhú, nakoľko hrať sa v pôvodnej matici môžeme v konečnom dôsledku prostredníctvom už opísaných možností. Poznámka: v prípade selektovania vyfiltrovaných dát na nový list musíte filter spustiť z listu, kde tie dáta chcete mať a taktiež nový list už musí obsahovať nadefinované stĺpce, ktoré chcete mať vyfiltrované.
• Vyberiem List range: teda oblasť, ktorej dáta chcem modifikovať. V tomto odporúčam používať pomenovanie databázy (insurance pred A1:S4416).
• Criteria range: oblasť kritérií, ktorú som v sample file taktiež pre jednoduchosť pomenoval data1, data3 a data3.
• Copy to: používame v prípade, ak filtrujeme dáta na nový list, sem označíme pomenované stĺpce.

Celý tento proces pre náročnosť si môžete pozrieť na nasledujúcom videu:

Filter & Advanced Filter

Nabudúce pokračujeme v bádaní v Exceli a posvietime si na neslávne známe kontingenčné tabuľky a iné podobnosti, ktorými Excel disponuje.

Funkcie (5. diel)

 

Som veľmi rád, že môžem týmto článkom ukončiť kapitolu Funkcie v Exceli a konečne nabudúce začať filtrovaním alebo kontingenčnými tabuľkami.

V dnešnom sample file sa na rozdiel od predošlých nachádzajú hneď dva listy. Práve pomocou týchto listov by som chcel rozdeliť článok na dôležitý a životne dôležitý pre prácu s reportmi.  Štatistické funkcie, resp. časť triviálnych  funkcií som sa rozhodol prezentovať už teraz avšak tie “pravé” prídu na rad až v dieloch, ktoré budú zamerané na Štatistiku.

Funkcie Countif, Averageif a viacnásobne podmienené výpočty Countifs a Averageifs počítajú podmienený priemer alebo počet (average – priemer, count – počet). Sú podobné funkciám Sumif a Sumifs, resp. majú rovnakú metodiku. Viac si môžete prezrieť na obrázku alebo sample file.

Celkom zaujímavou funkciou, a to hlavne pri kontrole kvality dát je Countblank. Tá jednoducho vráti počet prázdnych buniek v označenej oblasti. Nasledujúce funkcie sú viac štatistické. Funkcia Frequency umožňuje vytvoriť početnosť výberu podľa skupín. V našom príklade sme rozdelili servisné náklady do skupín podľa nákladovosti. Ak by sme počty jednotlivých skupín predelili celkovým počtom, teda 12 mesiacov, získali by sme relatívne početnosti a mohli by sme si pre úplnosť zostrojiť histogram. Histogramom sa taktiež budeme zaoberať v článkoch o Štatistike.

V obrázku na ľavo si môžete všimnúť funkciu Rank. Táto funkcia sa hodí vtedy ak potrebujete zistiť poradie jednotlivých riadkov matice podľa veľkosti (ktorý mesiac bol najviac nákladový na zásobovanie skladu – viď sample file). Realita je však taká, že to poradie nehrá veľkú rolu, lebo si môžete dáta usporiadať podľa poradia pomocou filtra. Dúfam, že ste sa moc nezapotili, pretože odteraz budete potrebovať maximálnu pozornosť, lebo nasledujúce funkcie sú smrteľne dôležité pre prácu a tvorbu reportov!

Upozornenie: bez znalosti funkcií Vlookup a Hlookup sa neodvažujte posielať životopis na pozíciu analytika, tvorcu reportov ani kontrolóra!

Prenesieme sa na list Vyhľadávacie a sústredíme sa na tabuľky: Vlookup je funkcia, ktorá umožňuje nájsť hodnotu v stĺpci a následne vrátiť akúkoľvek hodnotu z riadka, v ktorom sa hľadaná hodnota nachádza. Všimnite si tabuľku s výrobkami, množstvom a cenou v rôznych menách. Nás zaujíma cena v domácej mene, ktorú musíme urobiť tak, že zmeníme zahraničnú menu na domácu, tržby získame vynásobením ceny v domácej mene a množstva. Ale ako by sme mohli efektne konvertovať cenu v zahraničnej mene na cenu v domácej? Riešenie vidím jednoznačne vo funkcii Vlookup!

Lookup_value je hodnota, ktorú potrebujem nájsť. V našom prípade to bude mena (EUR). Do Table_array pridáme oblasť, ktorú chceme prehľadať (názov oblasti, ktorú som popisoval v minulom článku). V prípade ak označíme konkrétne bunky, napr. A1:V100 musíme si pri kopírovaní dávať pozor na ukotvenie riadkov a stĺpcov. Otázka ukotvenia je vlastne otázkou umiestnenia “$” pri označení riadku a stĺpca. Ukotvuje sa klávesom F4. Podrobnejší spôsob uvidíte na videu. Col_index_num je zase názov stĺpca, z ktorého chcete vybrať hodnotu. Môže to byť buď číslo alebo “názov”. Range_lookup je zase položka pomocou ktorej si zvolíte exaktné vyhľadávanie hodnoty 0 alebo false, pre približné hodnoty 1 alebo true. Ako príklad uvediem syntax   z prvej tabuľky =VLOOKUP(C5;$H$5:$J$7;2;0), ktorý platí pre túto tabuľku konverzných kurzov:

Čo je potrebné urobiť ak tabuľka je transponovaná, teda nemôžeme hľadať Lookup_value v stĺpcoch, ale riadkoch? V tom prípade použijeme Hlookup. Je to prakticky ten istý syntax =HLOOKUP(C5;$O$4:$Q$6;2;0), rozdiel je však len v index_num, teda Row_index_num. Tieto funkcie striktne odporúčam ovládať. V konečnom dôsledku sami uvidíte v budúcich článkoch, že budú súčasťou skoro každej analýzy a práce s dátami!

Funkcia Index je vhodná vtedy, ak máte veľkú maticu a potrebujete hodnotu z konkrétneho riadku a stĺpca matice. Syntax funkcie je nasledovný =INDEX(B20:C26;3;2). Akýmisi doplnkovými funkciami sú bez pochýb Row a Column, ktoré zistia poradie riadku a stĺpca, ktoré po dosadení do funkcií ako Address a Indirect vrátia konkrétnu hodnotu. Z uvedených funkcií rozoberiem len Indirect, ktorá má nasledujúce požiadavky: Ref_text je adresa bunky, ktorej obsah chcete vrátiť a A1 je zase zvolenie typu adresy bunky. Nám bude stačiť ak typ adresy nevyplníme, tým pádom nám ostane konvencia, na ktorú sme zvyknutí. Referenčný text môžeme zostaviť napríklad vsunutím funkcie Vlookup a to takto:=INDIRECT(VLOOKUP(G21;I19:J25;2)&F21). Vyhľadávacou funkciou sme transformovali názov stĺpca, ktorý vrátila funkcia Column vo forme čísla na písmeno. Potom sme k písmenu pripojili výsledok funkcie Row. Iná možnosť je vytvoriť adresu pomocou príslušnej funkcie Address a tú potom vložiť do vzorca. Address vyžaduje  numerické hodnoty riadku a stĺpca a v neposlednom rade aj určenie typu ukotvenia riadku a stĺpca. Ukotvenie sa nastavuje pomocou číslic 1,2,3,4 (viď sample file).

Asi najuniverzálnejšie je spojenie funkcií Match a Index. Match má totiž jednoduché požiadavky =MATCH("Hodnota";B32:B38) a je jedno či sa jedná o hľadanie v stĺpci alebo riadku. Potom stačí do Indexu nadefinovať získané hodnoty a maticu, v ktorej hľadať, napr. =INDEX(POLE1;J34;K34).

VLOOKUP & HLOOKUP

Týmto by som problematiku funkcií uzavrel, nakoľko sa jedná o prehľad Excelu, myslím si že uvádzať väčšie množstvo typov funkcií by nebolo efektné, práve preto sa máte na čo tešiť pri rozoberaní štatistiky a financií, kde si ukážeme, čo sa okrem toho čo som spomenul používa v praxi. Na budúce sa pustíme do filtrov, kontingenčných tabuliek a nezabudol som aj na grafy.

Funkcie (4. diel)

 

Po dnešnom dni, plnom pozitívnej energie z popoludňajšieho behu som sa rozhodol učiniť krátky proces s matematickými funkciami. Výsledok si samozrejme môžete stiahnuť tu.

Matematické funkcie patria spolu s textovými a databázovými k povezme najpoužívanejším v aplikácií. Ja preto spomeniem len niektoré, aby ste sa dokázali oboznámiť a zároveň nezamotali ako v bežných učebniciach alebo skriptách. Každopádne sa nemáte čoho báť, pretože väčšinu funkcií v praxi budeme používať pri aplikovanej štatistike a financiách.

Začnime prvým problémom a to je zaokrúhlenie čísel. V praxi som sa pri tvorbe nestretol so špeciálnymi požiadavkami na zaokrúhlenie, avšak pri technickej analýze alebo iných sférach môžete potrebovať funkciu, ktorá číslo zaokrúhli hore alebo dole. Pri tvorbe reportov možno potrebujete len celú časť čísla, teda bez desatinných miest. Ak to zhrniem, funkcie Round, Rounddown a Roundup sú klasické funkcie zaokrúhľovania, u ktorých si určíte počet desatinných miest na zaokrúhlenie. Funkcie Int a Trunc sa na prvý pohľad nelíšia, rozdiel je však v tom, že Int zaokrúhli číslo na dolnú celú časť (napr. 7,9 zaokrúhli na 7) a Trunc doslova oddelí len celú časť, bez ohľadu na desatinné miesta. Všetky funkcie sú k dispozícii v sample file, takže odporúčam precvičovať, precvičovať a ešte raz precvičovať.

Prejdime k zaujímavejšej a v praxi rozhodne používanejšej problematike. Matice, pre tých ktorí sa s nimi nikdy v živote nestretli, máte problém! Tak hurá k jeho vyriešeniu, nenechám vás predsa v štichu! Začnem nudnou teoretickou definíciou, takže matica je kombinácia prvkov z množiny R do m riadkov a n stĺpcov. V praxi si môžete predstaviť napríklad vašu nemocnicu, ktorá je v hroznom stave! Táto nemocnica získa nového a skúseného managera, ktorý nenechá nič na náhodu a rozhodne sa optimalizovať všetko čo sa len dá a preto začne prieskumom kvality služieb, poskytovaných nemocnicou. Z terénneho prieskumu bude mať k dispozícii tabuľku, kde stĺpce budú predstavovať rôzne body šetrenia a riadky zase jednotliví respondenti, teda pacienti. Táto tabuľka o n stĺpoch a m riadkoch je matica.  

V praxi nikdy nie je na škodu si maticu pomenovať, označte si prosím v sample file maticu o rozmeroch B14:E17. Potom máte dve možnosti ako jej priradiť názov. Prvá je prostredníctvom ľavého, horného okienka, v ktorom sa zobrazuje poloha kurzora, resp. ktorá bunka je označená. Stačí iba ak zmažete pôvodnú polohu bunky, napíšete pomenovanie matice a stlačíte Enter. Pomenujme si názov matice podľa krstného mena. Mňa momentálne napadlo meno EVA. Po pomenovaní matice si môžete výsledok samozrejme overiť a to jednoduchým kliknutím na Name Manager v záložke Formulas. Po kliknutí sa objaví nové dialógové okno, v ktorom môžete vytvoriť nový názov matice (oblasti) alebo editovať niektorý z pôvodných názvov, poprípade vymazať niektorý z nich. Práve táto možnosť cez záložku Formulas je zároveň tou druhou možnosťou pomenovania oblasti. Určite si kladiete otázku, prečo to všetko je vlastne potrebné. Ak si predstavíte bunku, v ktorej budete mať kombináciu veľkého množstva funkcií, pre lepšiu orientáciu je vhodné oblasti výpočtu formulovať menom, ako exaktnou adresou v liste! Ako demonštráciu uvediem funkciu Minverse, ktorá vráti inverznú hodnotu bunky, poprípade matíc.Čo je podľa vás prehľadnejšie: =MINVERSE(EVA) alebo =MINVERSE(B14:E17) ? Odpoveď je myslím si, že jednoznačná. Pre úplnosť, musím dodať, že ak chcete vytvoriť inverznú maticu k matici EVA, potrebujete si označiť potrebné množstvo riadkov a stĺpcov (4 riadky a stĺpce), po označení napísať syntax, teda =MINVERSE(EVA) a stlačiť Ctrl + Shift + Enter. Takto by ste mali dostať inverznú maticu, pre overenie si po kliknutí na ňu všimnite v príkazovom riadku, že syntax je v {…} zátvorke, to znamená platnosť pre celú oblasť, takže nebudete môcť zmazať časť matice, ale iba celú. Inverznú maticu si pomenujem napríklad ADAM. Pre kontrolu správnosti, po vynásobení ADAM-a a EVA-y by sme mali dostať nula-jednotkovú maticu (=MMULT(EVA;ADAM)).

Funkcie Sumif a Sumifs patria spolu s blízkymi príbuznými Countif a Countifs k veľmi obľúbeným, my si však názorne ukážeme len podmienené sčítanie, nakoľko Countif a Countifs sú kvázi skoro totožné, rozdiel je v tom, že oni nesčítavajú obsah buniek, ale množstvo buniek s adekvátnym obsahom (počet VW model Golf). Sumif  umožňuje využiť len jednu podmienku, =SUMIF(B22:B36;"VW";D22:D36), na rozdiel od užitočnejšieho Sumifs s možnosťou viacnásobného podmieneného sčítania =SUMIFS(D22:D36;B22:B36;"VW";C22:C36;"Golf").

Celkom zábavná časť Excelu je použitie náhodných čísel (nie ich generácia). Predstavte si, že potrebujete mať k dispozícii náhodné čísla z konkrétneho intervalu hodnôt. Pre výber od 0 do 100 stačí použiť =Rand()*100. Ak však hovoríme o pestrejšom intervale, vďačnejšia je funkcia Randbetween, stačí len zadať začiatok a koniec intervalu vyhovujúcich čísel. Ak si myslíte, že pomocou Rand funkcie sa to nedá, vyvediem vás z omylu takto: =RAND()*(Horné číslo intervalu-Dolné číslo intervalu)+Dolné číslo intervalu (viď obrázok). V prípade aktualizácie dát použite klávesu F9.

Hovorí vám niečo pojem Skalárny súčin? Ak nie, tak znova začnem teoretickou definíciou, ktorá znie: Majme dva vektory U a V. Skalárny súčin týchto vektorov je súčin veľkosti vektorov a kosínusu uhla, ktorý zvierajú.

 

V praxi ak máme tie dva vektory definované takto: U = (u₁, u₂) a V = (v₁, v₂), potom ich skalárny súčin je reálne číslo, NIE vektor!

A tým pádom môžeme po dosadení do predchádzajúceho vzorca odvodiť výpočet kosínusu uhla:

Tak to by bola matematika, v Exceli nám bude postačovať funkcia Sumproduct, s definovanými ROVNAKO veľkými vektormi, napr. =SUMPRODUCT(D55:D63;E55:E63). Ako posledné funkcie zmienim, povedzme delikatesy typu najmenší spoločný násobok Lcm, najväčší spoločný deliteľ Gcd a taktiež prevod čísla na rímske prostredníctvom Roman. Uvedomujem si, že v tomto článku je málo obrázkov a žiadne video, no vzhľadom na komplexnosť vzorcov som to nepovažoval za potrebné, ale v prípade akýchkoľvek nezrovnalostí som ochotný vaše pripomienky riešiť. Takže netreba sa báť, stačí len čítať, skúšať a hrať sa s číslami vo vzorcoch. Dúfam, že to nebolo ťažké a v budúcom poste pokračujeme maratónom funkcií.

Funkcie (3.diel)

 

Na úvod začnem trocha netradične a to pozitívnym nádychom. Dátumy sú totiž pre mňa dosť neobľúbenou problematikou. Vždy, keď mám v súbore dátumy a  čas tak skoro s istotou viem, že ma s ním čaká boj. Práve dátumy a čas sú údaje, v ktorých ľudia pri manipulácii robia mnoho chýb a stačí nevinná zmena formátu, poprípade odlišný zápis dátumu alebo času a máte vážny problém. Preto si myslím, že by nebolo na škodu aby som vám predstavil niektoré kvázi, dátumové funkcie. Pre vlastné pohodlie si môžete stiahnuť sample file.

Začnime pekne krásne dátumami. Ak potrebujete mať na liste umiestnený dátum, ktorý sa dneškom aktualizuje, môžete použiť funkciu Today, ktorej syntax je =TODAY().  Ako príklad uvediem dnešok + 14 dní, viď obrázok. Celkom užitočné sú funkcie, ktoré dokážu z jednotlivých dátumov vyselektovať buď deň, mesiac alebo rok.

V praxi je to vhodné ak dostanete výkaz s dátumami a potrebujete, resp. chcete ich rozdeliť do stĺpcov (môžete taktiež použiť možnosť Text to Columns podľa predchádzajúceho článku, no v prípade dátumov je elegantnejšie ak pri ich doplnení do stĺpca len skopírujete a vložíte relevantnú funkciu do adekvátnej bunky). V prípade dňa použijete funkciu Day so syntaxom =DAY("dd.mm.yyyy"). Taktiež môžete skombinovať túto funkciu s Today podľa vlastných potrieb, napríklad takto: =DAY(TODAY()+14). Obdobne fungujú aj nasledujúce funkcie Month a Year.

Možnou substitúciou textových funkcií Concatenate pri tvorbe dátumu je funkcia Date. Jej použitie je totožné s Concatenate, no zadáte tam len tri hodnoty a to v poradí: rok, mesiac a deň.

 

Trocha nešťastným riešením je využitie funkcie Weekday, ktorá vráti poradie dátumu v rámci dní v týždni (=WEEKDAY("dátum";2), kde druhé číslo predstavuje možnosť očíslovania dní v týždni, takže pre našu konvenciu použijeme 2). Ak potrebujete vedieť, aký deň prináleží dátumu, musíte si nadefinovať stĺpec s dňami a použiť funkciu Index (=INDEX(H2:H8;F3;0)). Kompletnú formuláciu vzorcov máte k dispozícii v sample file.

Avšak nemôžem si pomôcť, ale musím v tomto diele spomenúť možnosť formátovania bunky. Formátovanie buniek si budeme preberať o niečo neskôr, avšak už dnes vám prezradím, že ak zmeníme formát bunky dátumu v záložke Home na Custom potom definujeme ako dddd, dostaneme hotový názov dňa, platný pre dátum. Výhoda? Asi najväčšia je v tom ,že nemusíme vytvoriť nový stĺpec s ďalšou funkciou, stačí len pozmeniť formát, ba dokonca si túto zmenu môžeme nahrať ako makro a nestrácať zbytočný čas. Takto pozmenený formát buniek môžeme využiť, ak chceme definovať dátum spolu s časom pomocou Custom a d.m.yyyy h:mm alebo potrebujete len čas, teda h:mm. Vo formáte Custom si môžete formáty ľubovoľne nastaviť podľa potrieb, (##" sec", ##" min" alebo ##" hod", ktoré v podstate zobrazujú čísla spolu s výrazom v úvodzovkách. Ak sa vám nepáčia preddefinované názvy v úvodzovkách, môžete ich pokojne zmeniť.

No vráťme sa ešte k niektorým užitočným funkciám. V prípade ak potrebujete zistiť, v ktorom týždni sa stala udalosť, alebo v akom týždni v poradí (rok má 53 týždňov) sa nachádzate, použite funkciu Weeknum so syntaxom =WEEKNUM(“dd.mm.yyyy”), v prípade aktuálneho týždňa =WEEKNUM(TODAY()). Edate vám zase umožní vrátiť konkrétny dátum, posunutý o istý počet mesiacov. Ak by ste mali tabuľku s evidenciou firemných telefónov a mali by ste dátum zakúpenia a viete, že telefóny majú záruku 2 roky, teda 24 mesiacov, stačí vám v stĺpci Koniec záruky použiť =EDATE(“bunka dátumu zakúpenia”;24).

Veľmi praktickým pomocníkom je Networkdays funkcia, pomocou ktorej môžete zistiť počet odpracovaných dní v časovom rozmedzí, v prípade ak niekto mal neplatené voľno apod. tak sa tieto dni zadávajú do vzorca a tým pádom dostanete očistené číslo odpracovaných dní.

Syntax vzorca je vidieť na obrázku, ako príklad uvediem =NETWORKDAYS(B28;C28;D28:J28), ktorý som použil v cvičnom súbore.  Na záver by som chcel upozorniť, že vo väčšine funkcií pri zadávaní dátumu do vzorca dbajte na to aby bol v “úvodzovkách”. V prípade, ak by ste na to zabudli, tak by vás aplikácia mala upozorniť na chybu vo vzorci, čo v prípade dlhého algoritmu môže byť problém. Ak by som sa mohol vyjadriť všeobecne o funkciách a článkoch, Excel samozrejme disponuje väčším množstvom funkcií v porovnaní s tými, o ktorých som sa zmienil. Preto vo vlastnom záujme a ak na to máte čas a chuť, objavujte nové funkcie a HLAVNE kombinujte tie, ktoré už poznáte. V praxi to totiž prinesie svoje ovocie. V Ďalšom článku samozrejme pokračujeme funkciami. Pravdepodobne matematickými, ale nie som si istý. Nechajte sa prekvapiť.

Funkcie (2.diel) – Doplnok

 

Milí čitatelia, tak dostali sme sa do fázy, v ktorej musím dodržať to, čo som sľúbil. Preto prosím nestrácajme čas a stiahnime si sample file a začnime na začiatok tým, aby sme sa nezľakli formátu, v akom nám databáza vyexportovala dáta.

Všimnite si, že jednotlivé stĺpcové hodnoty sa s istotou oddeľujú bodkočiarkou a preto môžeme s čistým svedomím označiť oblasť buniek, ktorú chceme rozdeliť do stĺpcov, prejsť na záložku Data a v sekcii Data Tools klikneme na Text to Columns. Objaví sa nám dialógové okno, v ktorom si vyberieme kritéria rozdelenia obsahu buniek. Možnosť Fixed width sa využíva v prípade ak sme si istí, že dáta sú rovnakého charakteru (nie je diferencia medzi jednotlivými stĺpcovými hodnotami v počte znakov). S touto možnosťou som sa stretol veľmi málo, osobne používam častejšie možnosť Delimited, ktorá vyžaduje definovať deliace znamienko.

Vráťme sa k sample file-u kde máme stĺpcové hodnoty delené bodkočiarkou, takže použijeme možnosť Delimited, potvrdíme Next > potom si vyberieme oddeľovacie znamienko, teda v časti Delimiters označíme Semicolon, ostatné nastavenia nemeníme. V ukážke Data preview si môžeme všimnúť ako hodlá program rozdeliť text, na základe danej predlohy si môžeme meniť nastavenia tak, aby sme čo najlepšie optimalizovali rozdelenie do stĺpcov. Následne potvrdíme Next >  a dostaneme sa do časti, v ktorej si môžeme nastaviť formát buniek alebo stačí rozdelenie potvrdiť možnosťou Finish. Výsledok vás asi potešil ale určite nie na 100%. Dáta by vyzerali oveľa lepšie, ak by neobsahovali tie strašidelné percentá. V tomto prípade, si už určite budete vedieť s nimi poradiť (Ctrl + F tak ako som to opisoval v prvom článku).

Nakoľko sa vám tento článok určite zdal byť zaujímavý a obohacujúci, moje svedomie mi nedovolí nepoužiť jeden, povedzme trik, s ktorým by ste si túto pekelne monotónnu prácu mohli v budúcnosti uľahčiť. Preto som sa rozhodol prvýkrát ukázať MAKRO. Skúste si ten súbor zavrieť a neuložiť. V tom prípade si ho znova otvorte. Potom stlačte Alt (ľavý) + F11 a objaví sa vám zvláštne okno. Áno je to tak, dostali ste sa do prostredia VBA. Toto prostredie a celkovo problematiku makier rozoberieme v budúcnosti, až budeme ovládať všetko potrebné z Excel možností. Pre dnešok postačí ak kliknete na Insert, potom vyberiete Module a objaví sa vám také malé okno, v ktorom bliká kurzor. V tomto poli napíšte, resp. vložte nasledujúci kód:

Sub Rozdelit()
    Selection.TextToColumns , DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True
End Sub

Potom môžete zavrieť VBA prostredie a v záložke Developer kliknite na Macros. Objaví sa dialógové okno a v ňom označte makro s názvom Rozdelit a zvoľte Options… V Shortcut key priraďte k Ctrl napríklad písmeno “b” a Potvrďte OK. Zavrite všetky dialógové okná a potom budete mať pripravené makro na to, aby ste ho použili v sheet-e. Potom si môžete označiť bunky na rozdelenie a aplikovať makro klávesmi Ctrl + b. Pre tých, ktorí sú zvedaví ešte viac, ponúkam video s kompletným postupom tvorby makra tak, aby si nemusel pamätať ten škaredý algoritmus.

Prvé makro :)

Dúfam, že tento “doplnok” textových funkcií zaujal a na budúce budeme pokračovať ďalšími typmi funkcií v Exceli.

Funkcie (2.diel)

 

Dúfam, že každý z vás má už v malíčku základ z minulého článku a tak sa môže s čistým svedomím začať zaoberať textovými funkciami. Ako ste si už zvykli, sample file nájdete tu.

V priloženom súbore nájdete pomenovanie a príklad použitia jednotlivých funkcií, prípadne aj zložených funkcií tak, aby dávali reálny zmysel.

Veľmi často som sa v praxi stretol s potrebou rozdeliť alebo spojiť text v reportoch tak aby som mohol porovnávať jednotlivé tabuľky. Začnime spojením textu.

Vybraný text v bunkách môžeme spojiť funkciou Concatenate, stačí do zátvorky vkladať jednotlivé bunky s textovým obsahom.

Ak potrebujete mať text oddelený čiarkou alebo medzerou stačí v zátvorke medzi bunkami vložiť “,” alebo “ “ . V prípade ak potrebujete k bunkám s textom pridať taký, ktorý v žiadnej bunke nie je, použite “text”. 

Stáva sa a mnohí mi možno dajú za pravdu, že efektívnejší spôsob (pri malom množstve textových buniek) spájania buniek s textom alebo ich kombinácie s čiarkami, medzerami a iným textom mimo bunky je pomocou tohto operátora: &. Ako príklad uvediem vzorový algoritmus =A1&B1&” “&C1&”-“&”Ľubovoľný text”&AB1.

Ak potrebujete rozdeliť text do niekoľkých buniek, existujú na to funkcie Část alebo v našej anglickej verzii MID. V sample file som ju nepoužil a nakoľko vo väčšine prípadov sa rozdeľuje veľké množstvo dát (veľa riadkov) do viacerých stĺpcov, použijeme inú metódu, ale až v budúcom doplňujúcom článku. Každopádne syntax vyzerá takto: =MID(“Bunka”;poradie začiatočného písmena;počet písmen) teda ako príklad: =MID(B46;2;4).

Funkcie Left, Right spolu s Len sa používajú v praxi ak máte napríklad sheet s vyexportovanými dátami z SQL alebo SNOW apod., no po rozdelení do stĺpcov alebo finálny export dát obsahujú po stranách znaky, ktoré sú zbytočné a môžu znepríjemňovať prácu s reportom. Preto použijeme Left a Right na očistenie dát a funkciu Len (vráti počet znakov v bunke) na to, aby algoritmus pracoval obecne. Syntax = LEFT(RIGHT("Bunka";LEN("Bunka")-počet nevyhovujúcich znakov vpravo);LEN(RIGHT("Bunka";LEN("Bunka")-počet nevyhovujúcich znakov vpravo))-počet nevyhovujúcich znakov vľavo) alebo pre lepšie pochopenie príklad zo sample file-u: =LEFT(RIGHT(B12;LEN(B12)-2);LEN(RIGHT(B12;LEN(B12)-2))-2).

Zaujímavá funkcia je Text, s ktorou sa oplatí hrať hlavne pri dátumoch. Ona vlastne konvertuje hodnotu na text podľa nami určeného formátu. Ak máme napríklad dátum 21.11.2001 a chceme z neho použiť názov dňa, urobíme to takto: =TEXT(“Bunka, v ktorej je dátum”,”dddd”). Pri určovaní formátu je dôležité rozlišovať podmienky formátovania. Pre “dd” vyselektujete číslo dňa. Vo väčšom počte písmen “d” dostávate už konkrétne názvy dní: “ddd” – deň v skratke (Ut ako Utorok). Na obrázku som použil formát, v ktorom sa zobrazuje celý názov dňa.

Pre zmenu veľkosti písmen (Malé alebo veľké) použijeme funkciu Upper (všetky  veľké) a Lower (všetky malé) poprípade ak potrebujeme začiatočne písmená každého slova veľké, použijeme funkciu Proper. Stačí jednoducho definovať bunku, v ktorej text si želáte zmeniť (viď sample file).

Pri komplexnejších formulách, kde chcete vytvoriť takú, ktorá by pracovala všeobecne pre akýkoľvek dlhý text, je potrebné niekedy zistiť pozíciu cieľového znaku. Pre zistenie poradia daného znaku sa dá použiť funkcia Find.  Postačí zadať hľadaný znak, bunku v ktorej sa nachádza a začiatočný znak pre hľadanie. Príklad: =FIND(",";B39;1).

Ak by sme potrebovali hľadaný znak vymeniť za iný, použili by sme Replace ošetrenú funkciou Iferror a to takto: =IFERROR(REPLACE("Bunka";FIND(",";"Bunka");1;".");"Bunka"). Menej všeobecné avšak jednoduchšie riešenie by bolo s Substitute formulou. Stačí zadať bunku spolu so znakom, ktorý zamieňame a novým nahradzujúcim znakom: SUBSTITUTE(“Bunka”;",";"-").

Funkcie Rept a Exact sú pomerne jednoduché a nebudem ich podrobne rozoberať (viď sample file). Užitočnou sa javí možnosť získania ASCII kódu na znak v konkrétnej bunke. Použite Code pre získanie ASCII čísla a neskôr jeho použitím vo funkcii Char získajte špecifický symbol naspäť. Tým pádom by som povedzme, že ukončil etapu textových funkcií aj keď Excel ich ponúka oveľa viac. Práve preto je potreba aby ste čo najviac sa stretávali s riešením problémov cez Excel a postupne nachádzali ďalšie funkcie, ktoré vám môžu uľahčiť analytický život. Nasledujúci článok bude doplnkovým k textovým funkciám a rozoberiem v ňom metódu komplexného rozdelenia buniek do viacerých stĺpcov.

Funkcie (1.diel)

 

Problematika používania funkcií patrí k relatívne rozsiahlejším a preto sa pokúsim vytvoriť koncept viacerých článkov, v ktorých budeme používať jednotlivé typy.

Túto časť si preto dovolím začať úvodom do základných matematických funkcií, ktoré sa objavujú už pri paneli Home kde v oblasti Editing si môžete kliknutím vybrať, pre niektorých používateľov najviac populárne funkcie. V tomto prípade sa bude jednať o povedzme odľahčený typ článku. Avšak najlepšie by bolo ak by ste mali k dispozícii cvičný súbor, pomocou ktorého by ste si mohli overiť poznatky z článku a tým pádom pochopiť logiku jednotlivých funkcií. Takže uvedený súbor si môžete stiahnuť tu.

Ako si môžete na obrázku všimnúť, osvojíme si práve tieto funkcie a neskôr v ďalších vydaniach prejdeme na komplexnejšie funkcie, ktoré budete využívať hlavne tí, ktorí sa zaoberajú štatistikou, financiami, v prípade textových funkcií by som upriamil pozornosť hlavne pre ľudí, tvoriacich reporty.

Sum: asi najznámejšia funkcia, používaná každým, kto chce niečo sčítať. Ak potrebujete sčítať jednotlivé hodnoty istej oblasti (niekoľko buniek), napíšete =sum(“oblasť buniek”).

Average: vypočíta aritmetický priemer požadovanej oblasti. Môžete ju aplikovať pre bunku kliknutím na Average možnosť v oblasti Editing na záložke Home alebo do bunky vypíšete =average(“oblasť buniek”).

Count Numbers: sa na prvý pohľad zdá byť “zbytočná”, ale v praxi sa dosť často používa pri kontrole dát, keď potrebujete hlavne pri VEĽKÝCH tabuľkách overiť platnosť čísel (viete, že máte napríklad 1000 zákaziek, potom tým pádom v stĺpci cena musí byť 1000 čísel). Syntax funkcie vyzerá asi takto: =count(“oblasť buniek”).

Funkcie Max a Min, ako sami tušíte vracajú maximálnu, respektíve minimálnu hodnotu do vyznačenej bunky. Syntax vyzerá nasledovne: =max(“oblasť buniek”) alebo =min(“oblasť buniek”). Možnosť More Functions… môžeme definovať ako rozšírené nastavenie fungovania funkcie s jej podrobnejším popisom vo forme dialógového okna. V ňom si môžete do funkcie doplniť dáta pre výpočet poprípade sa uistiť, ak si niektorou funkciou nebudete istí, že algoritmus je nastavený správne.

V priloženom dokumente som použil aj medián pre každý mesiac. Tento pojem, nemusí byť známy každému a preto prichádza čas predstaviť vám niečo zo štatistiky. V štatistike sa stáva, že máte za úlohu dozvedieť sa čo najviac zo známych informácií a na základe nadobudnutých poznatkov určiť charakter skúmaného javu, procesu. Medián patrí spolu so strednou hodnotou alebo modusom k takzvaným charakteristikám polohy.

My sa vybranými charakteristikami budeme podrobnejšie zaoberať neskôr, zatiaľ si medián predstavíme ako hodnotu náhodnej veličiny, ktorú prekročí alebo nadobudne viac než 50% entít z výberového súboru (Mzdový medián vo firme XYZ s počtom 100 zamestnancov rovný 550€ nám hovorí, že 50 zamestnancov má mzdu vyššiu alebo rovnú 550€). V Exceli je syntax takýto: =median(“oblasť buniek”).  Túto vlastnosť som uviedol hlavne preto, aby ste si uvedomili, aký doplňujúci pohľad na veličinu môže priniesť pozorovateľovi v porovnaní s priemerom. V priloženom súbore tým chcem naznačiť, že spoliehať sa na priemerné hodnoty nemusí byť vždy prospešné, lebo už len jedna hodnota, ktorá je takzvaným extrémom vo výbere, môže vytvoriť dramatické skreslenie reality (prípad pána Michala), ktoré aritmetický priemer nemusí dostatočne zachytiť. POUŽÍVAJTE preto okrem priemerných hodnôt aj takzvané kvantily, ktoré si rozoberieme neskôr.