Excel má tisíce vestavěných funkcí. Pokud znáte alespoň některé z nich, můžete si ušetřit spoustu času při zpracování dat a vytváření sestav.
V tomto článku jsme pro vás připravili 10 základních funkcí, které se v Excelu používají nejčastěji.
#1. SUMA
Syntax: =SUMA(číslo1;[číslo2];...)
Číslo1 je povinný argument.
Funkce umožňuje zjistit součet jednotlivých číselných hodnot, rozsahů, odkazů na buňky s číselnými hodnotami nebo součet všech 3 typů. Často se používá při sčítání souhrnné hodnoty řádků nebo sloupců při formování výkazů.
Příklady použití
Dejme tomu, že máte pole číselných hodnot a potřebujete vypočítat součet některých z nich. Pomocí funkce SUMA nahradíme v argumentované oblasti funkce požadované odkazy na buňky a získáme odpověď -312.
V některých případech pole neobsahují hodnoty, které je třeba také sečíst, a místo odkazů na buňky můžeme přidat vlastní čísla. Odpověď v tomto případě -419.
Protože funkce pracuje nejen s číselnými hodnotami, ale také s celočíselnými rozsahy, je možné zjistit součet celého rozsahu.
Nemusíte se omezovat na počet hodnot, které chcete sečíst, ale spíše spočítat všechny hodnoty ve sloupcích nebo řádcích. Například sečtěte všechny hodnoty v prvních dvou sloupcích.
Pokud jedna nebo více buněk v rozsahu neobsahuje číslo, ale text, Excel tyto hodnoty přehodí na nulu.
#2. POČET
Syntax: =POČET(hodnota1;[hodnota2]; ...)
hodnota1- povinný argument
Tuto funkci lze použít k počítání buněk, které v seznamu argumentů obsahují pouze číselné hodnoty. Často se používá při výpočtech průměrů, kdy není praktické použít funkci PRŮMĚR v Excelu.
Následující vzorec vrací počet buněk v rozsahu A1:E4, které obsahují čísla.
Jak vidíme, náš seznam argumentů obsahuje rozsah pěti hodnot, ale funkce vrátí čtyři, protože číselné hodnoty jsou obsaženy pouze ve sloupcích A, B, C, D. Sloupec E je prázdná buňka.
Podobnosti při používání funkcí:
=POČET2(hodnota1,[hodnota2],...) - spočítá počet neprázdných hodnot v seznamu argumentů.
=COUNTBLANK(rozsah) - spočítá počet prázdných hodnot v daném rozsahu.
#3. MIN
Syntax: =MIN(číslo1;[číslo2;…])
číslo1 - povinný argument
Funkce najde minimální číselnou hodnotu v daném seznamu argumentů. Často se používá ve finančním výkazu, když chcete určit počáteční datum vykazovaného období, minimální částku a další parametry.
Příklad
Dejme tomu, že máme řadu čísel a textových výrazů a potřebujeme najít minimální hodnotu.
Například minimální hodnota mezi dvěma vybranými rozsahy A1:D2, A4:D4 a čísla 54 bude 21. Prázdná pole a textové výrazy jsou funkcí vyloučeny a ve výpočtech se nepoužívají.
Podobnosti používané funkce:
=MINA(hodnota1;[hodnota2];...) - najde minimální hodnotu v seznamu argumentů, přičemž textové a nepravdivé logické výrazy jsou nastaveny na nulu a logický výraz "PRAVDA" v buňce je nastaven na 1.
=MAX(číslo1,[číslo2],...) - najde maximální hodnotu v seznamu argumentů, přičemž text a prázdné výrazy jsou ignorovány.
=MAXA(hodnota1;[hodnota2];...) - najde maximální hodnotu v seznamu argumentů, textové a nepravdivé logické výrazy se rovnají nule a logický výraz "PRAVDA" v buňce se rovná 1.
#4. AVERAGEA
Syntax: =AVERAGEA(číslo1;[číslo2];...)
číslo1 - povinný argument
Tuto funkci lze použít k nalezení aritmetického průměru jednotlivých číselných hodnot, rozsahů, odkazů na buňky s číselnými hodnotami nebo průměru těchto tří typů. Vypočítá se tak, že se sečtou všechna čísla a součet se vydělí počtem stejných čísel. Textové a logické hodnoty v rozsahu jsou ignorovány.
Dejme tomu, že máme rozsah 6 buněk: 4 z nich jsou vyplněny čísly včetně 0, jedna hodnota je text a další je prázdná. Funkce sečte pouze číselné a součet vydělí celkovým počtem číselných - 4.
Výsledkem je průměrná hodnota 4. Zkontrolujme to pomocí vzorce:
(4 + 5 + TEXT + 7 + 0 + PRÁZDNÁ BUŇKA) / 4 = 16 / 4 = 4
TEXT a PRÁZDNÁ BUŇKA jsou ignorovány.
#5. ZAOKROUHLIT
Syntax: = ZAOKROUHLIT(číslo, číslice)
číslo - argument
číslice - na kolik číslic se číslo zaokrouhlí.
Funkce ZAOKROUHLIT slouží k zaokrouhlování reálných čísel na požadovaný počet desetinných míst a vrací zaokrouhlenou hodnotu podle matematického pravidla zaokrouhlování.
Například chceme zaokrouhlit číslo 2,57525 na 2 desetinná místa, můžeme zadat vzorec =ZAOKROUHLIT(2,57525;2), který vrátí hodnotu 2,58. Tato funkce se často používá při sestavování rozvahy a dalších typů výkazů.
#6. KDYŽ
Syntax:=KDYŽ (podmínka; hodnota když je podmínka splněna; hodnota když není splněna)
podmínka - je hodnota, kterou je třeba zkontrolovat
hodnota když je podmínka splněna - tato hodnota bude vrácena, pokud je podmínka pravdivá
hodnota když není splněna - tato hodnota bude vrácena, pokud je podmínka nepravdivá
Tato funkce je jednou z nejznámějších v Excelu. Kontroluje čísla a/nebo text, funkce a vzorce. Pokud hodnoty splňují danou podmínku, objeví se záznam z pole "value_if_truth", pokud ne - "value_if_false". Často se používá k rozdělení výrazů do kategorií, skupin.
Funkce podporuje následující operátory porovnání: = (rovná se), < (menší než), <= (menší nebo rovno), > (větší než), >= (větší nebo rovno), <> (nerovná se). Tato funkce se také často používá ve spojení s logickými operátory A a NEBO. Podívejme se na několik příkladů.
Příklad 1
Předpokládejme, že máme k dispozici tabulku cen potravin na 1 kg (l) a úkolem je rozdělit zboží do dvou skupin podle ceny:
- do 500 Kč
- nad 500 Kč
K vyřešení tohoto problému použijeme funkci KDYŽ.
Do buňky C2 vložte následující vzorec: =KDYŽ(D2<=500; "menší než 500"; "větší než 500"). Interpretuje se takto: pokud je číslo v zadané buňce menší nebo rovno 500 (1 na obrázku), pak by funkce měla vrátit textový výraz "menší než 500" (2 na obrázku), pokud se ukáže, že je jiné - "větší než 500" (3 na obrázku).
Rozšíříme tento vzorec dolů a zkontrolujeme, jak funguje.
Příklad 2
Předpokládejme, že ve výše uvedené tabulce potřebujeme ke každé záložce přiřadit cenovou kategorii: "vysoká cena" a "nízká cena".
Následující vzorec byl zapsán do buňky D2: = KDYŽ(C2="mensi nez 500"; "nízká cena"; "vysoká cena"). Vysvětlení: Pokud je hodnota textu v zadané buňce "mensi nez 500", pak by funkce měla vrátit textový výraz "nízká cena", pokud je jiná, měla by vrátit "vysoká cena".
Poznámka: při kontrole podmínek na textovou hodnotu musí být tato hodnota v uvozovkách uvedena v dalších uvozovkách. Podívejme se na výsledek roztažením vzorce na konec tabulky.
#7. SVYHLEDAT
Syntaxe: =SVYHLEDAT(vyhledávací hodnota; tabulka; číslo_sloupce; [interval_pohled])
Vyhledávací hodnota je hodnota, kterou je třeba najít ve sloupci dat. Argumenty mohou být číselné a textové. Hledaná hodnota musí být v levém sloupci rozsahu buněk zadané tabulky.
tabulka - odkaz na rozsah buněk. V levém sloupci se vyhledá hledaná hodnota a ve sloupcích vpravo se zobrazí odpovídající hodnota. Levý sloupec se také nazývá klíčový sloupec. Pokud tabulka neobsahuje hledanou hodnotu, bude vrácena chyba # N/A.
číslo_sloupce - je číslo sloupce tabulky, kde má být hledaná hodnota vyhledána
[interval_view] je nepovinný argument. Přijímá dvě hodnoty: PRAVDA a NEPRAVDA. Ve výchozím nastavení je nastavena hodnota PRAVDA a funkce předpokládá, že levý sloupec tabulky je seřazen vzestupně podle abecedy. Pokud je tento argument PRAVDA, funkce hledá hodnotu, která je nejblíže nebo stejná jako požadovaná hodnota, pokud je NEPRAVDA, hledá 100% shodu s požadovanou hodnotou.
Jedná se o funkci, která zjednoduší práci s velkými soubory dat a více tabulkami. Je to užitečné, pokud chcete vytáhnout příslušný sloupec z jiné tabulky (např. skupina, kategorie).
Příklad
Vraťme se k naší tabulce z funkce KDYŽ. Předpokládejme, že ke každé záložce nákupu je přiřazena skupina, do které patří (zelenina, ovoce, cukrovinky atd.), a máme adresář kategorií, ale v jiném sloupci/tabulce. U datových polí o tisíci řádcích je ruční zpracování velmi časově náročné, zatímco SVYHLEDAT to zvládne za zlomek sekundy.
Do buňky E2 napište vzorec: =SVYHLEDAT(A2;$F$2:$G$19;2;NEPRAVDA). Ujistěte se, že jste vložili značku zámku rozsahu $ - pokud tak neučiníte, přetažením vzorce dolů rozsah buněk v tabulce bude také posunut směrem dolů s hledanou hodnotou.
Jak jednoduše číst vzorec: najděte hodnotu z buňky A2 v levém sloupci tabulky G2:H19 (byl vložen nový sloupec, proto se námi dříve definovaná tabulka posunula o jeden sloupec doprava) a vypište odpovídající hodnotu ze sloupce 2 této tabulky, přičemž nalezená hodnota musí odpovídat údajům v levém sloupci tabulky (argument NEPRAVDA).
Přetáhněte vzorec směrem dolů a zobrazí se výsledek.
V některých případech vidíme hodnotu N/A - je tomu proto, že hledaná hodnota nebyla nalezena mezi výrobky ve sloupci G.
#8. IFERROR
Syntaxe: =IFERROR(hodnota, hodnota_při_chybě)
hodnota je argument, u kterého se kontroluje, zda nedošlo k chybě.
Hodnota_při_chybě - hodnota vrácená v případě chyby.
Tato funkce porovná argument s chybovými hodnotami #N/A, #VALUE!, #REF!, #DĚLENÍ_NULOU!, #NUM!, #NÁZEV?nebo #NULL!. Pokud výraz v kontrolované buňce obsahuje chybu, funkce vrátí hodnotu, která je v tomto případě definována. Pokud nedošlo k chybě, zobrazí se výsledek výpočtu nebo údaje v buňce. Často se používá při dělení nulou.
Příklad
Vezmeme si tabulku s hodnotami tržeb a počtu prodaných jednotek. Zjistěte výnosy na jednotku produkce. Tento problém vyřešíme tak, že tržby vydělíme počtem jednotek.
#9. PROČISTIT
Syntaxe: =PROČISTIT(text)
Text je hodnota textu, ze které chcete odstranit zbytečné mezery.
Tato funkce slouží ke zpracování textů z různých zdrojů. Pokud jsou v těchto textech zbytečné mezery, jsou odstraněny.
Příklad
Vzorec =PROČISTIT(" Tržby od počátku roku ") vrátí "Tržby od počátku roku", přičemž z výrazu odstraní všechny mezery navíc.
Nám je např. zřejmé, že slova "Ústí nad Labem" a " Ústí nad Labem " označují stejné město. Excel to ale jako jednu hodnotu samozřejmě nerozezná - kvůli mezerám.
Naštěstí existuje funkce TRIM / PROČISTIT, která provádí následující tři kroky:
- Odstraní všechny mezery na začátku textu.
- Odstraní všechny mezery na konci textu.
- Pokud se mezi částmi textu vyskytuje několik mezer za sebou, jsou změněny na jednu mezeru.
Z " Ústí nad Labem " tak funkce udělá spořádané "Ústí nad Labem".
Funkce má jen jeden parametr - text, který "čistím". Zápis je tedy velmi jednoduchý:
=PROČISTIT(A1).
#10. CONCATENATE
Syntaxe: =CONCATENATE(text1;[text2];[text3];...)
text1 je povinný argument.
Chcete-li spojit hodnoty z různých buněk do jedné hodnoty, použijte funkci CONCATENATE. Funkce se často používá ke kombinaci dat z více sloupců.
Příklad
Předpokládejme, že máme tabulku zaměstnanců s oddělenými jmény, pracovními pozicemi a věkem, a v sestavě potřebujeme zobrazit všechny tyto hodnoty v jedné buňce. Pro provedení tohoto úkolu použijte následující funkci
