V dnešním videu se podíváme na to, jak a kdy použít excelové funkce. Spousta lidí se v Excelu zaměřuje na to, aby se naučili, co nejvíce excelových funkcí, až nakonec skončí rozhodovací paralýzou, kterou funkci použít. Dnes si na praktickém příkladu ukážeme a ukážeme, jak a podle čeho se rozhodnout, jakou excelovou funkci použít.
Excelový soubor ke stažení
Excelové funkce
A začneme s řešením dnešního úkolu. Máme zde zdrojovou tabulku dat, vedle které máme otázky, na které máme odpovědět. Při zodpovídání otázek si ukážeme, kdy jakou excelovou funkci použít a proč.
Jednoduché počty
Jako první máme zjistit, jaká je celková a průměrná tržba v tabulce. Když potřebujeme v Excelu sčítat nebo počítat průměr, tak využijeme základní funkce jako SUMA, anglicky funkce SUM nebo PRŮMĚR, anglicky AVERAGE. Jedná se o jednoduché funkce, ve kterých stačí akorát označit oblast buněk, se kterými chcete počítat. Celkové tržby spočítáme pomocí funkce SUMA, kde označíme hodnoty tržeb a funkci potvrdíme. Funkce SUMA vrátila celkový součet tržeb. Ještě spočítáme průměrnou tržbu. Použijeme funkci PRŮMĚR, kde opět označíme hodnoty tržeb a funkci potvrdíme. Funkce PRŮMĚR vrátila průměrnou výši tržeb v tabulce.
Dále nás zajímá, jaká je nejnižší a nejvyšší tržba v tabulce. Pro najití nejnižších a nejvyšších hodnot slouží funkce MIN a MAX. Začneme s funkcí MIN, která najde nejnižší tržbu. Stejně jako v ostatních funkcích stačí pouze označit rozpětí buněk. Funkci potvrdíme a funkce MIN vrátila nejnižší tržbu v tabulce. To samé platí pro její protějšek, funkci MAX. Napíšeme funkci MAX, kde označíme hodnoty, funkci potvrdíme a funkce MAX vrátila nejvyšší tržbu z tabulky.
U těchto jednoduchých funkcí si musíte dát v zásadě pozor na dvě věci. První, co si musíte pamatovat je, že pokud budete mít mezi hodnotami chybovou hlášku, tak tyto funkce vrátí rovněž chybu. Jednu z tržeb přepíšeme na 1/0, což vrátí chybovou hlášku dělení nulou. Potvrdíme a jak vidíme, tak všechny čtyři funkce vrátily chybu. Ani jedna z těchto funkcí neumí pracovat, pokud se mezi hodnotami vyskytuje chybová hláška. Pokud na takový případ narazíte, musíte nejprve vyčistit data a nahradit chybové hlášky třeba nulami.
Druhá věc, na kterou si musíte dát pozor je to, pokud pracujete s těmito výpočty v tabulce, kterou plánujete filtrovat. Řekněme, že do tabulky vložíme filtr, klikneme do tabulky a zmáčkneme kombinaci kláves CTRL+SHIFT a písmeno L nebo na kartě Domů vybereme Seřadit a filtrovat a vybereme Filtr. Do záhlaví tabulky se vloží filtr. Pokud teď vyfiltrujeme tabulku třeba pro druh produktu Oblečení, tak celková tržba, kterou jsme spočítali pomocí funkce SUMA zůstane nezměněná. Funkce SUMA nebo i PRŮMĚR budou u filtrované tabulky ukazovat nesprávné hodnoty, jelikož tyto funkce i nadále na pozadí počítají s odfiltrovanými hodnotami. Pokud tedy víte, že budete hodnoty filtrovat, musíte použít funkci SUBTOTAL, která umí pracovat s filtry. Nad sloupec s tržbami tedy vložíme funkci SUBTOTAL, ve které nejprve vybereme správnou funkci, funkce SUMA má číslo 9 a označíte hodnoty. Funkci potvrdíme. A zkusíme znovu vyfiltrovat tabulku pro Oblečení. A funkce SUMA stále ukazuje celkové tržby, kdežto součet ve funkci SUBTOTAL ukazuje pouze součet tržeb pro Oblečení. Pokud tedy víte, že může dojít k filtrování zdrojových hodnot a funkce na tento filtr mají reagovat, tak vždy použijte funkci SUBTOTAL.
POČET / POČET2
V dalších dvou krocích máme spočítat, kolik tržeb a kolik produktů s vyskytuje v tabulce. V Excelu existují dvě základní funkce, které počítají hodnoty. Je mezi nimi ale zásadní rozdíl. Funkce POČET, anglicky funkce COUNT, počítá pouze číselné hodnoty. Druhá funkce, POČET2 naopak počítá s neprázdnými buňkami. Rozdíl si ukážeme. V prvním případě chceme spočítat počet tržeb, tedy číselných hodnot. Použijeme funkci POČET, kde označíme hodnoty ve sloupci tržba. Funkci potvrdíme a vrátila se hodnota 30, což je správně. Pokud bychom ale některou tržbu smazali, tak funkce POČET bude tuto prázdnou buňku ignorovat a nezahrne ji do výpočtu. Stejně tak by nezahrnula chybovou hodnotu. Pokud opět jednu tržbu změníme na chybu, tak na rozdíl od funkcí jako SUMA a PRŮMĚR, funkce POČET počítá, ale buňku ignoruje. Stejně tak by ignorovala tato funkce text, místo jedné tržby napíšeme text a funkce vrací o číslo méně.
Naopak pokud chceme spočítat textové hodnoty, tak musíme použít funkci POČET2 neboli anglicky funkci COUNTA. Tato funkce počítá textové hodnot a číselné hodnoty. Prakticky se dá říct, že počítá neprázdné buňky. Máme spočítat počet produktů. Jedná se o text, takže napíšeme funkci POČET2, kde označíme hodnoty. Funkci potvrdíme a vrátil se správný počet. Pokud opět nějaký produkt smažeme, tak funkce POČET2 bude tuto prázdnou buňku ignorovat. Pokud do prázdné buňky ale napíšeme číslo, tak funkce POČET2 tuto buňku započítá. Na rozdíl od funkce POČET by funkce POČET2 započetla i chybovou hlášku. Přepíšeme jeden produkt na chybu a funkce POČET2 výsledek nezměnila. Stále se jedná o neprázdnou buňku, takže s í funkce počítá.
Podmínková funkce KDYŽ
V dalším kroku máme přijít na to, které produkty mají tržbu vyšší než 15 000 Kč. V tomto případě tedy potřebujeme ověřit podmínku, že tržba na řádku je vyšší než 15 000 Kč. Zároveň máme barevně označit řádky, pokud tržba splnila podmínku. Nejhorší, co můžete udělat je, začít manuálně barevně označovat řádky, kde je tržba vyšší než 15 000 Kč. Možná tak označíte správné produkty, ale nejen, že můžete udělat chybu, jelikož se přehlídnete, ale Excel nemá funkci, která by uměla spočítat nebo sečíst barevné buňky, takže takový způsob není všeobecně vhodný. Ideálním řešením je v takovém případě přidat sloupec do tabulky, ve kterém ověříme podmínku pomocí podmínkové funkce. Pokud potřebujete v Excelu ověřit podmínku, využijete funkci KDYŽ, anglicky funkci IF, případně její vylepšenou verzi IFS, která je vhodná pro více podmínek.
V tabulce chceme označit produkty, jejichž tržba je vyšší než 15 000 Kč. Řekněme, že u produktů, které podmínku splní chceme na řádku slovo Splněno a u ostatních chceme prázdnou buňku. V pomocném sloupci začneme s funkcí KDYŽ, kde ověříme podmínku, že tržba na řádku je vyšší než 15 000 Kč. Pokud je podmínka splněná, chceme vrátit slovo Splněno v uvozovkách a pokud splněná není chceme vrátit prázdnou buňku. Funkci ukončíme a potvrdíme a stáhneme ji pro všechny řádky. Teď máme označené řádky a produkty, které splnili podmínku.
Zbývá barevně řádky označit. Opět, chybou by bylo začít manuálně obarvovat řádky, kde je podmínka splněná. Pokud je to jen trochu možní, tak vždy k takovým účelům využijeme podmíněné formátování. Chceme obarvit celé řádky, takže označíme celou tabulku a na kartě Domů vybereme Podmíněné formátování. Zde vybereme Nové pravidlo a v nabídce vybereme formátovat pomocí vzorce. Podle čeho chceme obarvovat řádky? Podle toho, zda ve sloupci s podmínkou je slovo Splněno. Takže podmínka pro podmíněné formátování je, že buňka ve sloupci s podmínkou, která je zafixovaná pro sloupec, se rovná slovu Splněno v uvozovkách. To je celá podmínka. Ještě vybereme formát a vybereme třeba světle modrou barevnou výplň. Potvrdíme formát a buňky se označili správně.
Podmíněné formátování je na rozdíl od manuálního označování dynamické, to znamená, že bude reagovat na jakékoliv změny podkladových hodnot. Pokud se změní hodnota v tabulce, podmíněné formátování včetně funkce KDYŽ se automaticky přizpůsobí.
Součty, počty a průměry s jednou podmínkou
V praxi se setkáte s tím, že nepotřebujete pouze jednoduché počty a součty, ale že potřebujete pracovat se součty a počty s podmínkou. Tak jako v následujících úkolech. Máme spočítat celkovou tržbu pro produkty, které spadají do sekce oblečení. To znamená, že máme provést součet na základě podmínky, kde podmínkou je, že produkt spadá pod oblečení. Pokud se dostanete do situace, že máte počítat, sčítat nebo průměrovat s podmínkou, tak využijete funkce jako SUMIF, COUNTIF a AVERAGEIF, což jsou vlastně funkce SUMA, PRŮMĚR a POČET v kombinaci s podmínku KDYŽ. Začneme součty a použijeme funkci SUMIF. Ve funkci SUMIF se nejprve označí sloupec, kde je kritérium. Chceme sčítat tržby pro oblečení, takže označíme sloupec s druhem produktů. Následuje kritérium, což je „Oblečení“ v uvozovkách. Posledním parametrem je oblast součtu, takže sloupec s tržbami. To je celá funkce SUMIF. Funkci ukončíme a potvrdíme. Funkce SUMIF vrátila součet tržeb pro oblečení.
V dalším kroku máme spočítat průměrnou tržbu doplňků. Stejný princip. Máme spočítat průměrnou tržbu s podmínkou, že se jedná o doplňky. K tomu využijeme funkci AVERAGEIF. Tedy průměr s podmínkou. Kritéria jsou stejná jako u funkce SUMIF. Nejprve označíme sloupec, kde najdeme kritérium, tedy sloupec s druhem produktů. Následuje kritérium, což jsou „Doplňky“. Posledním sloupcem je sloupec s tržbami, jelikož chceme sečíst tržby. Funkci ukončíme a potvrdíme. A funkce AVERAGEIF vrátila průměrnou tržbu pro produkty doplňků.
V posledním kroku máme spočítat, kolik tržeb v tabulce přesáhlo 15 000 Kč. Zase zde máme výpočet s podmínkou. Použijeme funkci COUNTIF, kde jsou stejná kritéria jako u ostatních funkcí. Nejprve označíme sloupec, kde je kritérium. Tentokrát to není sloupec s druhem produktů, ale chceme počítat s tržbami nad 15 000 Kč, takže sloupec jsou tržby. Kritérium je vyšší než 15 000 Kč. Znaménko vyšší musí být v uvozovkách a spojené ampersandem s číslem 15 000 Kč. Ukončíme závorku a potvrdíme. Funkce COUNTIF vrátila počet produktů, jejichž tržby jsou vyšší než 15 000 Kč.
Součty, počty a průměry s více podmínkami
Pokud potřebujete pracovat s počty, součty a průměry, ale potřebujete použít více podmínek, tak na to existují funkce jako SUMIFS, COUNTIFS a AVERAGEIFS. Ty využijeme v dalších případech, kde máme spočítat celkový součet tržeb u obuvi, ale jen u těch produktů, které mají tržbu vyšší než 12 000 Kč. Máme zde tedy dvě podmínky. Jendou je, že produkt patří pod obuv a druhou je, že tržby jsou vyšší než 12 000 Kč. Chceme sčítat tržby, takže použijeme funkci SUMIFS. Na rozdíl od funkce SUMIF, ve funkci SUMIFS označujeme nejprve sloupec, který chceme sčítat, tedy sloupec s tržbami. Následuje sloupec s prvním kritériem, začneme s obuví, označíme tedy sloupec druh produktu, a jako kritérium je „Obuv“. Máme ale další kritérium, a tím jsou tržby, takže označíme sloupec s tržbami a kritérium je, že tržby mají být vyšší než 12 000 Kč. Funkci ukončíme a potvrdíme.
V dalším kroku chceme spočítat, kolik tržeb u produktů v oblečení je vyšších než 12 000 Kč. Máme zase dvě podmínky, že produkt spadá pod oblečení a že tržby jsou vyšší než 12 000 Kč. A chceme počítat, takže zvolíme funkci COUNTIFS. Parametry jsou stejné jako ve funkci SUMIFS, s tím rozdílem, že zde není sloupec pro součet. Takže začínáme se sloupcem s kritérii, označíme sloupec s druhem produktů, kritérium je „Oblečení“, následuje druhé kritérium, takže sloupec s tržbami a kritérium je, že tržby jsou vyšší než 12 000 Kč. Funkci máme hotovou, ukončíme ji a potvrdíme.
Kdykoliv tedy potřebujete počítat, sčítat nebo průměrovat s podmínkou, ta zvolíte funkce jako SUMIF, COUNTIF nebo AVERAGEIF. Pokud potřebujete počítat s více podmínkami, tak zvolíte funkce jako SUMIFS, COUNTIFS nebo AVERAGEIFS.
LARGE / SMALL
Dalším úkolem je najít druhou nejvyšší tržbu a třetí nejnižší tržbu v tabulce. Pro nejvyšší a nejnižší hodnoty používáme funkce jako MIN a MAX. Pokud ale potřebujeme najít třeba druhou nejvyšší nebo nejnižší hodnotu, tak použijeme funkce SMALL a LARGE. Pro druhou nejvyšší hodnotu použijeme funkci LARGE. Ve funkci LARGE se nejprve označuje rozsah hodnot, hledáme druhou nejvyšší tržbu, takže označíme hodnoty tržeb a hledáme druhou nejvyšší hodnotu, takže do druhého parametru napíšeme dvojku. Funkci potvrdíme a máme druhou nejvyšší hodnotu v tabulce. Pro třetí nejnižší hodnotu použijeme funkci SMALL. Opět označíme hodnoty tržeb a hledáme třetí hodnotu, takže do druhého parametru napíšeme trojku. Funkci potvrdíme a funkce SMALL vrátila třetí nejnižší hodnotu.
Vyhledávání v Excelu
V dalším kroku máme dva úkoly s vyhledáváním, což je po práci s podmínkami druhý nejčastější úkon v Excelu. Pro vyhledávání v Excelu existuje několik různých funkcí, od funkce SVYHLEDAT, máme i funkce INDEX & POZVYHLEDAT nebo funkci XLOOKUP. V prvním případě máme najít tržbu pro produkt 7. Jakou vyhledávací funkci zvolíme? Záleží na pořadí sloupců ve zdrojové tabulce. Pokud chcete použít funkci SVYHLEDAT neboli funkci VLOOKUP, tak potřebujete, aby sloupec, dle kterého vyhledáváte byl od vyhledávaných hodnot nalevo. Vyhledávání s INDEX & POZVYHLEDAT nebo XLOOKUP taková omezení nemá. V tomto případě máme sloupec produktů od tržeb nalevo, takže můžeme použít funkci SVYHLEDAT. Napíšeme funkci SVYHLEDAT, kde nejprve označíme, co hledáme. Hledáme tržbu pro produkt 7, takže hledáme „Produkt 7“, následuje tabulka, kde hodnoty hledáme, prvním sloupcem musí být sloupec s produkty, takže označíme pouze část tabulky od produktů po tržby. Následuje sloupec, ve kterém se nachází odpověď, takže čtvrtý sloupec. A hledáme přesnou shodu, takže nulu. Funkci potvrdíme a funkce SVYHLEDAT vrátila tržbu pro produkt 7.
Dalším úkolem je dohled, který produkt má nejvyšší tržbu. Můžeme v tomto případě použít funkci SVYHLEDAT? Nemůžeme. Protože máme dohledat produkt od tržeb, a produkty jsou nalevo od tržeb, takže nemůžeme použít funkci SVYHLEDAT. Pro takové případy je dobré znát i jiné vyhledávací funkce jako INDEX & POZVYHLEDAT neboli INDEX & MATCH. Začneme s funkcí INDEX, ve které se nejprve označuje, co chceme vrátit. Hledáme produkt, takže označíme sloupec s produkty. Následuje funkce POZVYHLEDAT, kde nejprve hledáme, co hledáme. Hledáme nejvyšší tržbu, kterou jsme určili pomocí funkce MAX. Dalším parametrem je, kde tuto hodnotu hledáme, což je sloupec s tržbami. A hledáme přesnou shodu, takže nulu. Ukončíme závorky a funkci potvrdíme. Kombinace funkcí INDEX & POZVYHLEDAT našla správný produkt.
V případě vyhledávacích funkcí je tedy dobré umět využívat alespoň dvě funkce, jelikož funkce SVYHLEDAT ne vždy funguje.
Jedna odpověď
😊