Níže naleznete seznam nejčastějších otázek a odpovědí k excel funkcím a různým chybám, na které můžeme při jejich používání narazit. Součástí jsou i odkazy, 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ážeme své 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ěří). Seznam všech funkcí v excelu najdete v dokumentaci Microsoftu k funkcím.
Význam 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 čí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é Excel funkce
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čitý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 v excelu.
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í Excel funkce a kategorie?
Funkcí a kategorií excel funkcí je opravdu mnoho, základní kategorie jsou tyto
Funkce na 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é excel 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 v odkaze.
Vyhledávací excel 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é excel funkce
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é excel 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é excel 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í excel 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í excel 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.
Chyba #NENÍ_K_DISPOZICI (#N/A)
V anglické 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 kdy hledaná hodnota není v oblasti hledání k dispozici.
Chyba #DĚLENÍ_NULOU! (#DIV/0!)
Z angličtiny a v aj verzi excelu zkratka pro “division error”. Pokoušíme se dělit 0 což nelze
Chyba #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 – např. =”Ahoj”+1
Chyba #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 a ve vzorci obsahujícím chybu reference pak nalezneme místo adresy smazané buňky (oblasti) #REF
Chyba #NÁZEV (#NAME)
Typicky chyba v syntaxi funkce. Excel nám dává najevo, že nezná danou funkci nebo nějaký argument.
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.
