Níže naleznete seznam nejčastějších otázek a odpovědí. Odkazy směřují pouze do článků na tomto webu, kde je daná funkce vysvětlena i s příkladem.
Mistrovství zvládnutí funkcí v Excelu v pracovních podmínkách spočívá v tom, že si dokážu mé pravidelně opakující se procesy pomocí Excelu zautomatizovat. Pokud například každý týden sestavuji nějaký report s využitím exportu dat z účetnictví a trvá mi to celý den, tak vězte, že pokud si Excel zautomatizujete, je to práce na 15 minut i skontrolou (i když tomu často lidé nevěří).
Co je to funkce v Excelu a jaký je rozdíl mezi funkcí a vzorcem?
Excel se dělí na buňky (cells) a každá buňka v sobě může uchovávat určitou hodnotu (například nějaké číslo). Současně platí, že každá buňka má v Excel sešitu jasně danou svoji pozici – podívejte se na obrázek kde máme 2 čísla.
- První číslo je v buňce A1 a má hodnotu = 1
- Druhé číslo je v buňce B1 a má hodnotu = 5
- Vzorec (formula) – pokud chci vypočítat průměr z obou čísel, tak jej mohu vypočítat tak, že napíšu vzorec =(A1+B1)/2 a výsledkem bude hodnota 3
- Funkce (function) – stejného výsledku mohu dosáhnout s využitím vestavěných funkcí, které Excel nabízí. Například pro průměrnou hodnotu můžu využít funkci PRŮMĚR (AVGERAGE) – tzn píšu vzorec a využívám funkci =PRŮMĚR(A1:B1) a výsledkem je opět 3
Info: Vzorec je jakýkoliv výraz, který napíšu do určené oblasti a funkce je termín pro předdefinovaný objekt, kterému předáme odkaz na buňku nebo oblast buněk a on nám vrátí určitý výsledek.
Nejčastěji používané funkce v Excelu?
Pomineme tzv. operátory (+, -, *, / a další), které nejsou funkcemi. Obecně mezi nejčastěji používané funkce patří:
- SUMA (SUM) a jiné agregační funkce – Základní excelovská funkce, která vrátí součet hodnot z určité oblasti. Mezi podobné agregační funkce patří například AVG, MIN, MAX a podobně
- KDYŽ (IF) – umožňuje pracovat s podmínkami typu když má buňka nějakou hodnotu, tak… a když ne, tak…
- SVYHLEDAT (VLOOKUP) nebo VVYHLEDAT (HLOOKUP) – pomocí této funkce hledáme určitou hodnotu v nějakém seznamu na základě shody. V případě SVYHLEDAT hledáme hodnoty v sloupci a naopak u VVYHLEDAT hledáme hodnoty v řádku.
- COUNTIF (COUNTIFS) – vrátí počet výskytů určité hodnoty v tabulce na základě podmínky
- SUMIF (SUMIFS) – vrátí součet určytých hodnot v tabulce na základě splnění 1 nebo více podmínek.
- IFERROR – Pomocí této funkce dokážeme dosadit námi zvolenou hodnotu do buňky v případě, že hodnota nějakého výrazu je Error
- POWER – použíjeme, pokud chceme vrátit mocninu nebo odmocninu.
Info: K plnému osvojení funkcí tohoto typu je potřeba trénink. Excel nabízí opravdu velkou spoustu funkcí a není zcela jednoduché bez dostatečného tréninku poznat ve které situaci jakou funkci použít. Pokud to ale zvládneme, tak úspory času při každodenních úkonech dosahují v některých případech i 50% a více času.
Jaké existují typy funkcí v Excelu + příklady nejpoužívanějších z dané kategorie?
Funkcí a kategorií excel funkcí je opravdu mnoho, základní kategorie jsou tyto
- Datum a čas – tyto funkce nám umožňují pracovat s datumem nebo časem. Umožňují například získat z určitého datumu číslo měsíce, týdne nebo jestli se jedná o pracovní den. Přiklady – ROK (YEAR), MĚSÍC (MONTH), DEN (DAY), HODINA (HOUR), MINUTA (MINUTE), SEKUNDA (SECOND), WEEKNUM, apod
- Statistické funkce – Statistické funkce umožňují analytovat nějaký soubor dat a popsat ho. Jednoduchá statistická funkce je například PRŮMĚR (AVERAGE) nebo MEDIAN. Velký přehled statistických funkcí najdete v článku Statistika v Excelu není nuda
- Vyhledávací funkce – tyto funkce umí najít určitou hledanou hodnotu ze souboru dat a vrátit požadovanou informaci. Mezi základní vyhledávací funkci patří SVYHLEDAT, SLOUPEC (COLUMN) – vrátí číslo sloupce, ŘÁDEK (ROW) – vrátí číslo řádku, INDEX – vrátí určitou hodnotu z oblasti při zadání pořadí hodnoty (např třetí hodnotu z oblasti) a mnoho dalších
- Textové funkce – umožnují pracovat s textem – například sloučit textové hodnoty z několika buněk (CONCAT), zjistit na které pozici se vyskytuje určitý znak NAJÍT (FIND), převést hodnoty na velká nebo malá písmena (UPPER/LOWER), pročistit mezery v textu PROČISTIT (TRIM), nahradit určitou hodnotu jinou hodnotou (REPLACE) a mnoho dalších.
- Logické funkce – pomocí logických funkcí dokážeme ověřit splnění 1 nebo více podmínek. Patří sem KDYŽ (IF), A (AND) – ověří jestli platí zároveň nekolik podmínek, NEBO (OR) – ověří jestli platí alespoň 1 podmínka, již zmiňovaná IFERROR a mnoho dalších.
- Matematické funkce – velká kategorie funkcí, patří sem veškeré funkce, které provádějí nějakou matematickou operaci – SUMA (SUM), PRŮMĚR (AVERAGE), SUMIFS, COUNTIFS, POWER – Mocniny a odmocniny, RAND, RANDBETWEEN – generování náhodných čísel, logaritmy, ABS – absolutní hodnota, SIN (sinus), COS (cosinus) a další
- Finanční funkce – používají se ve finanční matematice. Například pokud chceme vypočítat splátkový kalendář k úvěru a podobně. O finančních funkcích jsem tu napsal spoustu návodů – navigaci naleznete v přehledu – Seznam článků – finanční matematika
- Informační funkce – informační funkce typicky vrací hodnotu PRAVDA nebo NEPRAVDA a pomocí nich se dotazujeme na nějakou informaci. Např. JE.ČÍSLO (ISNUMBER) – pokud je v dané buňče číslo tak výsledkem bude PRAVDA a pokud ne (bude tam třeba text) tak NEPRAVDA.
Tip: Zdaleka není přínosem znát všechny funkce. Spíše je potřeba najít a zapamatovat si ty, které se nejčastěji používají a nebo ty, které jsou zrovna pro mě užitečné.
Chyby v Excelu a jak se s nimi vypořádat?
Při používání Excelovských funkcí se dostáváme do situace, kdy nám funkce vrátí z nějakého důvodu error. Chybových hodnot máme celou řadu viz např níže.
- #NENÍ_K_DISPOZICI (#N/A) – v aj verzi excelu zkratka pro “not avaiable”. Nastane v případě, pokud pomocí funkce hledáme nějakou hodnotu, která není dostupná. Typickým příkladem je například použití funkce SVYHLEDAT
- #DĚLENÍ_NULOU! (#DIV/0!) – v aj verzi excelu zkratka pro “division error”. Pokoušíme se dělit 0
- #HODNOTA! (#VALUE!) – v aj verzi excelu zkratka pro pro “value error”. Vyskytne se případě, že máme nějaký problém s datovým typem. Například pokud se pokoušíme sečíst hodnoty s datovým typem text a druhou hodnotu s typem číslo
- #ODKAZ (#REF!) – v aj verzi excelu zkratka pro “reference error”. Méně častá chyba, ale stává se v případě, kdy máme vzorec, který odkazuje na určitou buňku a následně tuto buňku (na kterou je odkazováno) vymažeme => Excel nás upozorní touto chybou a vznikne reference error.\
- #NÁZEV (#NAME) – typicky chyba v syntaxi funkce
Pokud nechceme, aby se nám chyby zobrazovaly a místo toho chceme zvolit nějakou jinou hodnotu (třeba 0 nebo prázdný text), tak používáme funkci IFERROR(<výraz u kterého může nastat error>; <hodnota, kteoru má být error nahrazen>).
Tip: IFERROR používejte pokud víte co děláte. Excel to s námi myslí dobře a na chyby upozorňuje z nějakého důvodu.
Ing. Jan Zedníček - BI Developer, Finance controller
Jmenuji se Honza Zedníček a nejčastěji se se mnou v civilu můžete potkat v kancelářích Kentico Software v Brně, kde působím jako BI Developer. Částečně pracuji také jako freelancer. Tuto práci dělám pro různé firmy již přes 5 let, ale Kentico je moje srdcovka ♡. Před tím jsem pracoval jako finanční controller třeba v Aero Vodochody a Sberbank nebo jako manažer dluhopisového programu v investiční skupině Unicapital. Po práci si rád zahraju tenis, volejbal, šachy, zajdu do posilovny a rád ochutnávám dobré rumy. Svoje znalosti se snažím zapisovat na tento web - abych je nezapomněl (působením rumu ^^) a sloužily i někomu dalšímu. Nebojte se položit dotaz nebo reagovat do komentáře. Přihlašte se do naší nové Excel facebook skupiny Excel CZ/SK diskuse »
