Excel | Funkce a #Chyby – Přehled nejčastějších otázek a odpovědí o Excel funkcích

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 »