Články v kategórii Funkcie a vzorce:

Pokročilé funkcie Excelu pre profesionálov

Takmer každý používateľ programu Excel sa raz dostane do momentu, kedy bude musieť použiť aj pokročilé funkcie na to, aby si poradil a úspešne vedel dokončiť svoje úlohy.

My vám prinášame 10 pokročilých funkcií Excelu, ktoré sa vám určite zídu.

Tie najdôležitejšie Excel funkcie a ich využitie na konkrétnych príkladoch sa môžete pohodlne naučiť v našich video kurzoch.

Napísali sme tiež článok Excel pre pokročilých, kde nájdete špeciálne tipy pre Excel mimo funkcií.

1. Funkcia INDEX MATCH

Funkcia INDEX MATCH je jednou z najpoužívanejších vyhľadávacích funkcií programu Excel. Ide o akúsi pokročilú náhradu za veľmi obľúbenú funkciu VLOOKUP. Na rozdiel od nej však INDEX MATCH môže napríklad vyhľadávať sprava doľava, umožňuje bezpečné vkladanie a odstraňovanie stĺpcov, urýchľuje proces spracovania a nemá problém s veľkosťou vyhľadávaných hodnôt.

Treba však zdôrazniť, že nejde o samostatnú funkciu ale o kombináciu funkcií INDEX a MATCH. Funkcia INDEX vracia hodnotu bunky v tabuľke na základe stĺpca a čísla riadka, no a funkcia MATCH vráti pozíciu bunky do riadku alebo stĺpca.

Prvým argumentom funkcie INDEX je pole v ktorom hľadáme, teda A1:C6. Druhým argumentom je číslo riadka. Toto číslo zistíme pomocou funkcie MATCH, kde hľadáme, v ktorom riadku v poli A1:A6 sa nachádza meno Robo. Tretím argumentom je číslo stĺpca. Toto číslo tiež zistíme pomocou funkcie MATCH, pričom hľadáme, v ktorom stĺpci v poli A1:C1 sa nachádza Výška.

Funkcia INDEX nám teda vráti hodnotu zo štvrtého riadku a druhého stĺpca poľa A1:C6, čiže 1.78.

2. Funkcia IF kombinovaná s AND/OR

Mnoho ľudí má z nej strach, no treba uznať, že funkcia IF je jedna z najpoužívanejších v programe Excel. Táto funkcia má široké využitie a dokážete si pomocou nej uľahčiť a zefektívniť prácu.

Funkciu IF však môžeme kombinovať s funkciami AND/OR a stáva sa tak niečím novým a zároveň aj jednoduchším na pochopenie. Presvedčiť sa môžete o tom na nasledujúcom príklade, kde sme túto kombináciu funkcií použili. Výsledok bude 100, keďže je splnená prvá aj druhá podmienka.

3. Funkcia OFFSET kombinovaná so SUM/AVERAGE

Možno sa pýtate, prečo sme medzi pokročilé funkcie Excelu pre profesionálov zaradili aj obyčajnú funkciu OFFSET. Ide síce o jednoduchú funkciu, no pokiaľ ju spojíme s inými, ako sú napríklad SUM alebo AVERAGE, tak dostaneme pokročilú funkciu na prácu s Excelom.

Kombináciou týchto dvoch funkcií dostaneme vzorec, pomocou ktorého hravo vypočítame premenlivý počet buniek. Ukážku si môžete pozrieť na nasledujúcom príklade:

Do bunky F3 sme vložili funkciu, ktorá nám zisťuje súčet poľa buniek od bunky A2 až po bunku, ktorej číslo stĺpca zadáme do bunky F2. Toto číslo stĺpca môžeme meniť a funkcia SUM sa prispôsobí práve vďaka funkcií OFFSET.

Do tejto funkcie sme dali tri argumenty:

1. Začínajúcu funkciu, od ktorej sa ideme odrážať, teda A2.

2. Počet riadkov, o ktoré chceme adresu tejto bunky posunúť dole, v našom príklade chceme zostať v druhom riadku, preto zvolíme 0.

3. Počet stĺpcov, o ktoré chceme adresu tejto bunky posunúť doprava, v našom príklade chceme posunúť o 1 stĺpec menej, ako zadané číslo v bunke F2, teda F2-1.

4. Funkcia CHOOSE

Ďalšou dôležitou funkciou programu Excel je funkciu s názvom CHOOSE. Tú môžeme použiť vtedy, keď potrebujeme k daným číslam priradiť nejaký text, alebo na základne daných čísel urobiť nejaký výpočet.

Funkcia CHOOSE sa zapisuje nasledovne: CHOOSE(index_číslo; hodnota1; [hodnota2;])

Index_číslo - určuje, ktorý prvok zo zoznamu má byť vybraný. Jeho hodnota sa musí nachádzať v číselnom rozmedzí 1 až 254. Ide o povinný argument.

hodnota1; hodnota2; … - Podľa zadaného argumentu index_číslo sa vráti príslušná hodnota. Hodnôt môže byť až 254. Ide o povinný argument.

V tomto príklade sme ako prvý argument zvolili bunku C7. Do tejto bunky môžeme dávať rozličné hodnoty, a tým je sa stáva naša funkcia CHOOSE dynamická. Do druhého, tretieho a štvrtého argumentu sme dali bunky D3,D4 a D5. Teda ak do bunky C7 napíšeme číslo 2, funkcia CHOOSE nám vráti hodnotu 14,00%.

5. Funkcia XNPV a XIRR

Ak pracujete v bankovníctve, v účtovníctve, či proste s peniazmi, tak sa s najväčšou pravdepodobnosťou určite stretnete so vzorcami XNPV a XIRR. Možno ste už zaregistrovali podobné vzorce s názvami NPV a IRR. Tie možno použiť pre výpočet v prípade, ak sú finančné toky inkasované vždy k tomu istému dátumu.

Častokrát je to však tak, že obdobie medzi finančnými tokmi nie je rovnaké a v takomto prípade musíme použiť funkcie XNPV a XIRR.

Funkciu XNPV môžeme zapísať pomocou troch parametrov nasledovne: =XNPV(diskontná sadzba, finančné toky, dátumy)

Napríklad máme tri finančné toky po 1 eure v troch dátumoch, 1. júla, 14. septembra a 24. novembra. Diskontná sadzba je 10 percent. Pre zistenie čistej súčasnej hodnoty použijeme funkciu XNPV, pri ktorej dáme do prvého argumentu diskontnú sadzbu, do druhého pole, v ktorom máme finančné toky a do tretieho argumentu pole s dátumami.

Podobne môžeme zistiť aj vnútornú mieru návratnosti pomocou funkcie XIRR. Do prvého argumentu tejto funkcie zadáme pole s finančnými tokmi a do druhého pole s korešpondujúcimi dátumami, a funkcia nám vráti vnútornú mieru návratnosti.

6. Funkcia SUMIF a COUNTIF

Ďalšími pokročilými funkciami pre prácu s programom Excel sú funkcie SUMIF a COUNTIF. Ich použitie nie je vôbec náročné a je jednoduché na pochopenie. Funkcia SUMIF dokáže vypočítať súčet hodnôt určitej oblasti, ktoré spĺňajú požadovanú podmienku.

Funkcia COUNTIF zas umožňuje vypočítať presný počet buniek v určitej oblasti, ktoré spĺňajú požadovanú podmienku.

7. Funkcia PMT a IPMT

Funkcie PMT a IPMT sú finančné funkcie, ktoré sa určite oplatí poznať. Pomocou funkcie PMT vypočítame splátku pôžičky pri konštantných platbách a konštantnej úrokovej sadzbe. Pomôže nám tak dostať presnú hodnotu, ktorú budeme musieť splácať počas doby trvania úveru.

Pri splácaní úveru je však aj dobré vedieť, koľko zo splátky tvorí úrok a s tým nám pomôže funkcia IPMT. Pomocou nej dokážeme zistiť výšku úroku v určitom úrokovom období pri pravidelných konštantných splátkach a konštantnej úrokovej sadzbe.

Ukážeme si to na príklade. Pri pôžičke 50 000€ s úrokovou sadzbou 2%, ktorú sme si vybrali na 60 mesiacov, bude v 5. mesiaci z mesačnej splátky 876€ zaokrúhlene 78€ tvoriť úrok.

8. Funkcia LEN a TRIM

Pokiaľ pracujete s veľkým množstvom údajov, v ktorých nemáte prehľad a chýba vám v nich organizácia, tak je správny čas použiť funkcie LEN a TRIM. Funkcia LEN vám umožní vrátiť počet znakov v textovom reťazci. Napríklad ak máte v bunke A1 slovo Slniečko, funkcia =LEN(A1) vráti číslo 8.

Pokročilá funkcia TRIM vám zas pomôže odstrániť z textu všetky medzery okrem jednoznakových medzier medzi slovami. Pomocou tejto funkcie tak môžete odstrániť všetky nepotrebné medzery, častokrát najmä na začiatku a na konci textu.

9. Funkcia CONCATENATE

Ide o textovú funkciu, ktorá je veľmi praktická a mnoho ľudí ju často využíva. Dokáže totiž spojiť niekoľko textových reťazcov do jedného. Spájať je pritom možné textové hodnoty, čísla, či odkazy na bunku.

V tomto prípade sme spojili text z bunky B3, pridali sme čiarku a medzeru, a pridali text z bunky D3.

10. Funkcie CELL, LEFT, MID, RIGHT

Posledné pokročilé funkcie Excelu pre profesionálov, ktoré sme pre vás vybrali, sú CELL, LEFT, MID a RIGHT. Ide o funkcie, ktoré vám uľahčia prácu s Excelom a nie sú náročné na pochopenie.

Funkcia CELL vracia informácie o obsahu bunky, ako sú napríklad formátovanie, názov, umiestnenie, ...

Funkcia LEFT vráti text zľava doprava podľa určeného počtu znakov. Funkcia MID vráti časť textu z ľubovoľného bodu bunky podľa zadanej pozície a podľa zadaného počtu znakov. Ako prvý argument použijeme bunku, z ktorej text chceme čerpať. Ako druhý argument použijeme počiatočnú pozíciu a ako tretí argument počet znakov od danej pozície. A funkcia RIGHT je podobná ako funkcia LEFT, akurát nám vráti text sprava doľava podľa určeného počtu znakov.

Použitie týchto funkcií si môžete pozrieť na nasledujúcom príklade:

V tomto príklade je vo funkcií MID ako druhý argument 10 a ako tretí 8, pretože sme chceli pred slovom Mikuláš nechať aj medzeru.

Záver

Učíme sa celý život. Objavujeme svet, objavujeme aj svoje limity a vďaka prirodzenej povahe človeka, ktorá nás stále núti bádať sa zvyčajne neuspokojíme len s jednoduchým skĺznutím po povrchu problému. Rovnako je to aj s objavovaním nových funkcií Excelu.

Keď začíname, často máme pocit, že to, čo sa naučíme na úplnom začiatku pokryje naše potreby a už nebude potrebné, aby sme sa učili ďalej. Nie je to vždy však tak.

Často nás nové možnosti uchvátia a my máme obrovskú chuť pokračovať ďalej a často si kladieme otázky, ako by sa dalo pokračovať, aká funkcia by mi umožnila rýchlejšie vyriešiť daný problém, ktorý sa v riešení objavil. Inak to nie je ani pri učení sa programu Excel.

Začíname úplne na začiatku s jednoduchou predstavou toho, čo sa chceme naučiť. Pomaly sa dostávame problému stále viac na koreň a objavujeme nové možnosti a ani sa nenazdáme a odrazu klopeme na dvere, na ktorých je napísané pokročilé funkcie Excelu pre profesionálov. A stáva sa z nás skutočný profesionál. Ale aké pokročilé funkcie si máme predstaviť pod týmto označením? Čo všetko objavíme ukryté v tomto všeobecne pomenovanom balíčku? A na čo ho dokážeme využiť?

Pod programom Excel si väčšina z nás predstaví tabuľkový softvér, ktorý nám umožní vytvárať tabuľky, upravovať ich, prípadne spracovať naše získané dáta grafickým spôsobom. Pod základnými funkciami si určite môžeme predstaviť formátovanie a kopírovanie buniek, základné použitie vzorcov či vytvorenie grafu.

Program Excel však ukrýva oveľa viac tajomstiev, ako si vôbec dokážeme z pohľadu začiatočníka predstaviť. Pokročilé funkcie programu Excel pre profesionálov dokážu robiť zázraky, ktoré sa môžete naučiť z pohodlia svojej domácnosti aj vy.

Verím, že tieto pokročilé funkcie Excelu pre profesionálov vám pomôžu zefektívniť vašu prácu. V našom kompletnom video kurze Excelu si ukážeme aj ďalšie najpoužiteľnejšie funkcie a pozrieme sa na príkladoch, ako sa dajú využiť v náš prospech.

Prajem vám veľa úspechov s programom Excel!