Pokročilé funkcie Excelu pre profesionálov

Funkcia INDEX MATCH | IF kombinovaná | OFFSET kombinovaná | CHOOSE | XNPV a XIRR | SUMIF a COUNTIF | PMT a IPMT | LEN a TRIM | CONCATENATE | CELL/LEFT/MID/RIGHT | Záver


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.

INDEX MATCH ukážka na obrázku

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.

Ukážka funkcie IF a AND


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:

Ukážka pokročilej funkcie OFFSET

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.

Pokročilé funkcie pre profesionálov, ukážka CHOOSE

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.


Ukážka finančných pokročilých funkcií

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.

Ukážka pokročilej funkcie COUNTIF


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.


Ukážka pokročilej funkcie PMT

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.


Excel funkcia IPMT


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.

Pokročilé funkcie pre profesionálov LEN a TRIM


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.

Ukážka funkcie CONCATENATE

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:

Pokročilé funkcie

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!

 
Easy Excel kurz Náš video kurz Excelu
v akcii na Zľavomate!

Naučte sa Excel online vďaka môjmu jednoduchému video kurzu!