V dnešním videu se podíváme na 20 základních funkcí, které byste v Excelu měli znát, pokud si chcete práci v Excelu usnadnit. Vedle kontingenčních tabulek se rovněž jedná o nejčastější funkce, na které narazíte při pracovním pohovoru. Ve videu vynecháme zcela základní excelové funkce jakými jsou například funkce SUMA a PRŮMĚR. Funkci SUMA jsme se podrobně věnovali ve dvou videích, a to Funkce SUMA pro začátečníky a Tajné triky s funkcí SUMA. Místo toho se zaměříme na ostatní excelové funkce, které by měl znát každý uživatel Excelu.
Excelový soubor ke stažení
20 excelových funkcí, které musí znát každý uživatel Excelu
SVYHLEDAT a VVYHLEDAT / VLOOKUP a HLOOKUP
Jedná se o dvě základní vyhledávací funkce v Excelu. Rozdíl mezi funkcemi SVYHLEDAT a VVYHLEDAT, anglicky funkcemi VLOOKUP a HLOOKUP, je v tom, že funkce SVYHLEDAT vyhledává ve svisle orientované tabulce a funkce VVYHLEDAT vyhledává v horizontálně orientované tabulce. Parametry obou funkcí jsou přitom totožné.
V následujícím příkladu máme zdrojovou tabulku s produkty, prodaným množstvím a cenami. Do druhé tabulky potřebujeme ze zdrojové tabulky dohledat k jednotlivým produktům ceny. Jelikož je tabulka rozložená svisle, tak využijeme funkci SVYHLEDAT neboli VLOOKUP.
Do první buňky napíšeme funkci SVYHLEDAT, kde jako první parametr hledat označíme první produkt, který hledáme. Jako druhý povinný parametr tabulka označíme celou zdrojovou tabulku, ve které produkt hledáme. Jelikož máme v plánu vzorec stahovat směrem dolů, tak celou tabulku zafixujeme klávesou F4. Do parametru sloupce musíme vyplnit pořadové číslo sloupce, ve kterém se nachází odpověď, kterou hledáme. V našem případě se cena nachází ve druhém sloupci ve zdrojové tabulce, takže vyplníme číslo dva. Posledním nepovinným parametrem je typ, tedy jaký typ shody hledáme. Hledáme přesnou shodu produktu, takže vyplníme nulu, ukončíme závorku a funkci potvrdíme. Více o tom, jak se používá přesná a přibližná shoda najdete ve videu Přibližná shoda ve funkci SVYHLEDAT a POZVYHLEDAT. Dokončenou funkci stáhneme pro všechny buňky dolů a funkce SVYHLEDAT doplnila k hledaným produktům správné ceny.
Pokud by byla tabulka orientovaná horizontálně, tak bychom pro vyřešení příkladu použili funkci VVYHLEDAT neboli funkci HLOOKUP. Do první buňky napíšeme funkci VVYHLEDAT, kde opět jako parametr hledat označíme první hledaný produkt. Následuje tabulka, kde označíme celou zdrojovou tabulku, kterou zafixujeme klávesou F4. Odpověď, kterou hledáme se nachází ve druhém řádku, takže vyplníme dvojku a opět hledáme přesnou shodu. Ukončíme závorku, funkci potvrdíme a protáhneme pro ostatní buňky v tabulce.
Největším omezením funkcí SVYHLEDAT a VVYHLEDAT je to, že obě funkce vyhledávají pouze směrem doprava, což znamená, že se položka, dle které hledáte musí vyskytovat vždy nalevo od hledaných hodnot. Pokud bychom v našem příkladu měli sloupec s cenou vlevo od sloupce s produkty, tak by funkce SVYHLEDAT a VVYHLEDAT vrátily chybu. Více o funkci SVYHLEDAT najdete ve videu 10 důvodů, proč funkce SVYHLEDAT nefunguje. Dalším omezením je, že pokud se v tabulce vyskytují duplicity, tak funkce SVYHLEDAT a VVYHLEDAT najdou vždy jen první položku v tabulce. Jak obejít toto omezení najdete ve videu Jak vyhledávat v tabulce s duplicitami pomocí funkce SVYHLEDAT.
INDEX & POZVYHLEDAT / INDEX & MATCH
Mnohem flexibilnější vyhledávací funkcí je kombinace funkcí INDEX a POZVYHLEDAT, anglicky INDEX a MATCH. Na rozdíl od funkcí SVYHLEDAT a VVYHLEDAT vyhledává tato kombinace i doleva a nerozlišuje se přitom to, zda máte tabulku orientovanou horizontálně nebo vertikálně. Na obě tabulky můžete využít kombinace funkcí INDEX a POZVYHLEDAT.
Kombinaci těchto funkcí si ukážeme na následujícím příkladě. Hlavním úkolem funkce POZVYHLEDAT je najít číslo řádku, na kterém se nachází hledaná položka. Funkce INDEX následně k číslu řádku přiřadí hledanou hodnotu. Začneme funkcí POZVYHLEDAT neboli funkcí MATCH. Do první buňky napíšeme funkci POZVYHLEDAT, kde jako první povinný parametr co označíme první hledaný produkt. Následuje parametr prohledat, což je sloupec, ve kterém se hledaná hodnota nachází. V našem případě hledáme produkt, takže označíme sloupec s produkty. Nezapomeneme zafixovat klávesou F4. Na rozdíl od funkce SVYHLEDAT se ve funkci POZVYHLEDAT neoznačuje celá tabulka, ale pouze sloupec. Nepovinný parametr shoda je stejný jako u funkce SVYHLEDAT. Hledáme opět přesnou shodu takže vyplníme nulu. Funkci potvrdíme a stáhneme pro všechny buňky dolů. Funkce POZVYHLEDAT u prvního produktu vrátila číslo jedna, jelikož se produkt A nachází na prvním řádku ve zdrojové tabulce. U produktu E vrátila číslo pět, což znamená, že se produkt E ve zdrojové tabulce nachází na pátém řádku. Funkci POZVYHLEDAT teď zabalíme do funkce INDEX. Ve funkci INDEX označíme jako pole sloupec, ve kterém se nacházejí odpovědi, které hledáme. Opět hledáme cenu, takže označíme sloupec s cenami a nezapomeneme sloupec zafixovat klávesou F4. Parametrem řádek ve funkci INDEX je celá funkce POZVYHLEDAT. Ukončíme závorky, funkce potvrdíme a stáhneme pro všechny řádky dolů. Kombinace funkcí INDEX a POZVYHLEDAT správně doplnila ceny u hledaných produktů. O funkcích INDEX a POZVYHLEDAT máme rovněž detailní video Funkce INDEX a POZVYHLEDAT pro vyhledávání v Excelu.
Funkce KDYŽ / IF
Podmínková funkce KDYŽ, neboli anglicky funkce IF, je nejběžnější funkcí, která se vedle vyhledávacích funkcí v excelu používá. Její základní použití si ukážeme na následujícím příkladu. Ve zdrojové tabulce máme produkty a jejich prodané množství. Vedle tabulky máme uvedený limit prodeje. Ve sloupci Popis bychom chtěli, aby se u produktů, kterých se prodalo méně než 20 kusů objevilo slovo Podprůměr a u ostatním slovo OK. Využijeme k tomu jednoduchou funkci KDYŽ. Do první buňky napíšeme funkci KDYŽ, anglicky funkci IF, jde jako parametr podmínka napíšeme, pokud je množství menší než toto číslo, nezapomeneme přitom zafixovat limitní číslo klávesou F4, tak chceme, aby se vyplnilo slovo Podprůměr. Jelikož se jedná o text, tak ho musíme uvést do uvozovek. A v případě, že podmínka splněná nebude, tak chceme vrátit slovo OK, opět v uvozovkách. Ukončíme závorku, potvrdíme funkci a pošleme ji dolů. Funkce KDYŽ správně doplnila dle zadané podmínky na každý řádek správné slovo. Více příkladů na funkci KDYŽ najdete ve videu Funkce KDYŽ pro začátečníky.
A a NEBO / AND a OR
V kombinaci s podmínkovou funkcí KDYŽ se velmi často používají logické funkce A a NEBO, anglicky funkce AND a OR. A to zejména v případech, kdy potřebujeme ověřit více logických podmínek najednou. V následujícím příkladě chceme u produktů, které splní podmínku uvést slovo splněno. Podmínky jsou, že se muselo produktu prodat více než 20 kusů a zároveň musí být marže u produktu vyšší než 20 %. Začneme tedy funkcí KDYŽ, kde v podmínce rovnou uplatníme funkci A neboli funkci AND, jelikož obě podmínky musí platit zároveň. Ve funkci A bude první logická podmínka, že počet musí být vyšší než limitní číslo, limitní číslo musíme zafixovat klávesou F4, středník a druhá podmínka je, že marže produktu musí být vyšší než toto číslo, opět zafixujeme klávesou F4. Ukončíme závorku u funkce A, napíšeme středník a přepneme se do parametru ano u funkce KDYŽ. Pokud podmínky ve funkci A budou splněny, tak chceme vrátit slovo „Splněno“, nesmíme zapomenout na uvozovky, jelikož se jedná o textovou hodnotu, v opačném případě chceme vrátit slovo „Nesplněno“, opět v uvozovkách. Ukončíme závorku a pošleme funkci dolů.
To samé bychom mohli udělat i u funkce NEBO. Řekněme, že nám ve druhém příkladu stačí, aby byla splněná pouze jedna z těchto podmínek. Ve funkci KDYŽ tedy použijeme funkci NEBO. První logickou podmínkou je, že množství musí být vyšší než toto číslo zafixované klávesou F4 a druhou podmínkou je, že marže musí být vyšší než toto číslo, buňka je opět zafixovaná. Ukončíme závorku u funkce NEBO a pokud podmínky splněné budou, tak chceme vrátit slovo Splněno, v uvozovkách a pokud ne, tak chceme vrátit slovo Nesplněno, opět v uvozovkách. Ukončíme závorku, funkci potvrdíme a pošleme funkci dolů i pro ostatní řádky. Více o kombinacích podmínkových funkcí KDYŽ, A a NEBO najdete ve videu Složené podmínky s funkcí KDYŽ.
SUMIF/SUMIFS
Funkce SUMIF je v podstatě sloučení funkcí SUMA a podmínkové funkce KDYŽ. Jak již toto spojení napovídá, tak funkce SUMIF umožňuje sčítat položky dle určité podmínky. V následujícím příkladu chceme sečíst položky u produktu C. Využijeme k tomu funkci SUMIF. Prvním parametrem je oblast, tedy sloupec, dle kterého hledáme, jinými slovy, ve kterém se nachází naše kritérium. V našem příkladu se jedná o sloupec s produkty. Kritérium je hledaný produkt. A parametr součet je pole, které chceme sčítat. Ukončíme závorku a funkci potvrdíme. V poli množství se sečetly pouze položky u Produktu C. Pokud produkt C v rozbalovacím seznamu změníme, tak se automaticky přepočítá i celkový součet.
Od verze 2010 má Excel v nabídce i funkci SUMIFS, kde poslední písmeno S signalizuje více podmínek. Je to tedy funkce, která dovoluje sečíst položky na základě více než jednoho kritéria. V následujícím příkladu máme dvě kritéria, produkt a pobočku. Použijeme funkci SUMIFS, kde je jako první parametr oblast součet, tedy sloupec, který chceme sčítat. Druhým parametrem je oblast kritérií, tedy sloupec, kde hledáme první kritérium, tedy sloupec s produkty. Následuje první kritérium, tedy hledaný produkt. Napíšeme středník a můžeme pokračovat s dalším kritériem. Zase jako první označíme oblast kritérií, tedy tentokrát sloupec s pobočkami a jako kritérium označíme pobočku. Ukončíme závorku funkce a potvrdíme. Funkce SUMIFS nám vrátila správný součet položek na základě dvou podmínek. Potřebujete použít více podmínek? Není problém, funkce SUMIFS vás nechá zadat až 127 podmínek.
COUNTIF/COUNTIFS
Velmi podobně jako funkce SUMIF a SUMIFS fungují i funkce COUNTIF a COUNTIFS. Zatímco funkce SUMIF sčítá položky, funkce COUNTIF položky počítá. V následujícím příkladu chceme spočítat, kolik produktu C se vyskytuje v tabulce. Použijeme tedy funkci COUNTIF. Prvním parametrem funkce COUNTIF je oblast. Jedná se o sloupec, ve kterém položku hledáme, v tomto příkladu se jedná o sloupec s produkty. Druhým kritériem je hledaný produkt. Ukončíme závorku a funkci potvrdíme. Opět, funkce je dynamická, pokud změníme položku v rozbalovacím seznamu, tak se počet změní dle hledaného produktu.
U funkce COUNTIF existuje rovněž verze COUNTIFS. Tedy funkce, která počítá na základě více podmínek. Opět chceme spočítat, kolik máme v tabulce produktů, které vyhovují dvěma kritériím. Začneme s funkcí COUNTIFS, kde jako oblast kritérií označíme nejprve sloupec s produkty, následuje kritérium, kterým je hledaný produkt. Napíšeme středník a můžeme pokračovat s další oblastí kritérií, tedy se sloupcem s pobočkami a jako kritérium označíme hledanou pobočku. Ukončíme závorku a funkci potvrdíme. Stejně jako u funkce SUMIFS i ve funkci COUNTIFS můžete použít až 127 zadaných podmínek. Pro více informací o funkcích SUMIF a COUNTIF se podívejte na video Funkce SUMIF a COUNTIF základy v Excelu.
AVERAGEIF/AVERAGEIFS
Pokud potřebujete spočítat průměrné hodnoty na základě podmínek, budou se vám hodit funkce AVERAGEIF a AVERAGEIFS. V následujícím příkladu máme opět tabulku s produkty a tržbami. V prvním příkladu chceme spočítat průměrnou tržbu u vybraného produktu. Začneme s funkcí AVERAGEIF. Prvním kritériem je oblast, tedy opět sloupec, proti kterému hledáme hledaný produkt. Jako další je parametr kritérium, tedy hledaný produkt. Posledním parametrem je oblast pro průměr. Tedy sloupec s tržbami. Ukončíme závorku a funkci potvrdíme. Funkce AVERAGEIF spočítala průměrnou tržbu u vybraného produktu.
Stejně jako u předchozích funkcí existuje i zde verze pro vícenásobné podmínky, tedy funkce AVERAGEIFS. Tentokrát hledáme průměrnou tržbu u produktu, který splňuje dvě kritéria. Začneme s funkcí AVERAGEIFS. Jako první parametr oblast pro průměr označíme sloupec s hodnotami, které chceme průměrovat. Následuje první oblast kritérií, tedy sloupec s produkty. Následuje první kritérium, kterým je hledaný produkt. Následně můžeme vyplnit další podmínky, tedy druhá oblast kritérií, tedy sloupec s pobočkami a jako kritérium pobočka. Funkci potvrdíme. Funkce AVERAGEIFS vrátila hodnotu průměrné tržby pro vybraný produkt, který splňuje hledaná kritéria.
POČET / POČET2
K počítání se vám budou hodit i funkce POČET a POČET2, v angličtině funkce COUNT a COUNTA. Zatímco funkce POČET počítá pouze číselné hodnoty, tak funkce POČET2 neboli COUNTA počítá všechny neprázdné buňky.
Rozdíl si ukážeme na následujícím příkladu. Ve sloupci položky máme mix textových, číselných a datumových hodnot. Mezi hodnotami máme dokonce i jednu chybovou hlášku. Pokud na tento sloupec použijeme funkci POČET, neboli funkci COUNT, tak funkce vrátí počet číselných hodnot v seznamu. Všimněte si, že kromě obecných čísel započítala tato funkce i datumy, jelikož z pohledu Excelu je datum pouze číslo. Pokud na stejný sloupec použijeme funkci POČET2, neboli funkci COUNTA, tak se vrátí počet řádků, které jsou neprázdné, což jsou vlastně všechny řádky v tabulce, včetně chybové hlášky.
SUBTOTAL
Mezi základní funkce, i přesto, že není až tak často využívána, patří i funkce SUBTOTAL. Funkce SUBTOTAL se nejčastěji používá u filtrovaných oblastí, nebo oblastí, kde skrýváme řádky, jelikož tato vrací výsledky pouze za filtrovanou nebo viditelnou oblast dat. Tato funkce v sobě obsahuje celkem 11 funkcí. Přičemž nejčastěji se využívají funkce SUMA a PRŮMĚR. Ve funkci SUBTOTAL máme na výběr nejen z 11 funkcí, ale rovněž si můžeme vybrat, zda chceme do výsledku zahrnout i skryté řádky.
V příkladě máme tabulku, na kterou nejprve uplatníme funkci SUMA. Následně když tabulku vyfiltrujeme, tak funkce SUMA zůstane nezměněná, a to i přesto, že se obsah tabulky změnil. Funkce SUMA pořád sčítá všechny buňky, a to i ty, které jsme odfiltrovali. Proto použijeme místo funkce SUMA funkci SUBTOTAL. Napíšeme funkci SUBTOTAL, kde jako funkci vybereme SUMU, což je číslo 9. Následuje oblast, kterou chceme sčítat. Tedy sloupec s množstvím. Funkci potvrdíme. Zkusíme vyfiltrovat tabulku a celkový součet reaguje na náš filtr.
Pokud bychom chtěli, aby funkce SUBTOTAL nezapočítávala skryté řádky, tak bychom vybrali ve funkci SUBTOTAL funkci SUMA, nikoliv však s číslem 9, ale s číslem 109. Pokud ve funkci SUBTOTAL vyberete funkci od čísla 101 do 111, tak to znamená, že funkce SUBTOTAL nebude započítávat skryté řádky.
TRANSPOZICE
Velmi užitečnou funkcí je i funkce TRANSPOZICE, anglicky funkce TRANSPOSE. Tato funkce je schopná převrátit data z horizontálního rozložení do vertikálního a obráceně. Řekněme, že chceme tato data v ukázce obrátit do horizontální polohy. U funkce TRANSPOZICE se liší její použití pro Excely 2021 a Office 365 a starší verze Excelu. U starších verzí Excelu se nejprve označí buňky, do kterých se mají data obrátit. Musí se přitom označit stejný počet buněk jako je ve zdroji. Když máme buňky označené, tak napíšeme funkci TRANSPOZICE a označíme v ní data, která chceme obrátit. Teď stačí funkci potvrdit kombinací kláves CTRL+SHIFT+ENTER, jelikož se jedná o maticovou funkci. Novější verze Excelu to mají snazší, nemusejí dopředu označovat přesný počet buněk a pro potvrzení funkce stačí zmáčknout ENTER. Více o tom, jak lze převracet data v Excelu najdete ve videích Transpozice v Excelu a Jak v Excelu překlopit tabulku.
IFERROR
Další užitečnou funkcí je funkce IFERROR. Jedná se o skvělou funkci, která zajistí, že v případě, že by výsledkem vaší funkce byla chybová hláška, že se zobrazí zpráva nebo výpočet, který zadáte a nikoliv chybová hláška. Vyhnete se tím tak situacím, kdy výsledkem funkce je chybová hláška, která zanechaná v excelovém sešitu nepůsobí profesionálně a rovněž může způsobit další výpočetní chyby. Řekněme, že v následujícím příkladu chceme mezi sebou vydělit čísla ve dvou sloupcích. Nicméně nemůžeme vyloučit, že jedním z těchto čísel nebude nula, což by samozřejmě způsobilo chybovou hlášku. Jednoduše stačí výpočet nebo funkci, jejímž výsledkem může být chybová hláška, zabalit do funkce IFERROR a v argumentu Hodnota v případě chyby specifikovat, co se v případě chyby má objevit. Například, že se v případě chyby má objevit „NA“ nebo třeba nic.
CONCATENATE
Funkce CONCATENATE je užitečná funkce na spojování textu. Řekněme, že máme ve dvou sloupcích jméno a příjmení a rádi bychom ve třetím sloupci měli celé jméno. Samozřejmě to můžeme vyřešit kombinací ampersandů a mezery. Tímto manuálním způsobem bychom napsali rovná se a označili první buňku se jménem, následuje ampersand, a jelikož chceme mezi jménem a příjmením mezeru, tak musíme mezeru uvést do uvozovek, následuje druhý ampersand a příjmení. Nebo můžeme použít funkci CONCATENATE, v novějších verzích Excelu funkci CONCAT, kde jako první text uvedeme jméno, následuje opět mezera v uvozovkách a příjmení.
PROČISTIT
Velmi užitečnou funkcí pro začátečníky je i excelová funkce na úpravu textu, funkce PROČISTIT, anglicky funkce TRIM. Tato funkce odstraní z textové hodnoty nadbytečné mezery. Funkce PROČISTIT má pouze jeden parametr, a to buňku s textem. Pokud máme tedy podezření, že buňka obsahuje přebytečné mezery, například před textem, ale i mezi textem nebo přímo v textu, tak očistíme text přes funkci PROČISTIT. Funkce PROČISTIT přijde vhod zejména, když používáte vyhledávací funkce, jelikož jakákoliv nadbytečná mezera v textu způsobí, že se hledaná hodnota nemusí shodovat s hodnotou dle které hledáte. Mezera je totiž z pohledu Excelu stále znak. V následujícím příkladě chceme k těmto společnostem vyhledat tržby ze zdrojové tabulky. Použijeme základní funkci SVYHLEDAT, nejprve označíme co hledáme, následuje tabulka, sloupec a přesná shoda. Funkci potvrdíme a pošleme ji dolů. U dvou společností se zdá být nějaký problém i přesto, že vidíme, že tyto společnosti ve zdrojové tabulce máme. Když ovšem klikneme na společnosti, tak zjistíme, že u nich máme přebytečné mezery. Místo toho, abychom text manuálně upravovali, tak první argument, co hledáme, ve funkci SVYHLEDAT zabalíme do funkce PROČISTIT. To zajistí, že než se hodnota bude hledat, že ji Excel očistí o nadbytečné mezery. Funkci potvrdíme a napodruhé se doplnily všechny tržby správně. Více o textových funkcích najdete ve videu Textové funkce v Excelu.
Jedna odpověď
Poznámka:
Pro názornost a lepší zapamatování by mohlo být uvedeno u vyhledávacích funkcí SVYHLEDAT = Svisle (anglicky VLOOKUP = Vertical), resp. VVYHLEDAT = Vodorovně (anglicky HLOOKUP = Horizontal)