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.