V dnešním videu si ukážeme jak pracovat s funkcemi, které vám dovolují počítat, sčítat nebo třeba průměrovat hodnoty a to na základě několika různých kritérií. Jedná se o funkce SUMIFS, COUNTIFS, AVERAGEIFS, MAXIFS a MINIFS. To, že v těchto funkcích můžete pracovat s více kritérii poznáte podle písmena S na konci těchto funkcí, což značí více kritérií nebo více podmínek. Některé z těchto funkcí mají i své protějšky pro použití s jednou podmínkou, jako například funkce SUMIF, COUNTIF nebo AVERAGEIF. U funkce MIN a MAX není verze s jednou podmínkou, takže neexistují funkce jako MINIF a MAXIF, když chcete funkce MIN a MAX použít s jednou podmínkou, tak to musíte obejít kombinací s funkcí KDYŽ.
Excelový soubor ke stažení
Funkce SUMIFS, COUNTIFS, MAXIFS, MINIFS, AVERAGEIFS
Všechny funkce v dnešním videu jsou dostupné od verze Excelu 2010. Až na funkce MAXIFS a MINIFS, které jsou dostupné zatím pouze pro Excely 2019 a novější verze.
V dnešním příkladu máme tabulku, ve které máme oblast s městy, druh produktu, množství prodaných kusů a tržby. Vedle tabulky máme v rozbalovacích seznamech uvedená dvě kritéria. Prvním kritériem je oblast Praha a druhým kritériem je Produkt A. Pomocí funkcí chceme spočítat, kolikrát se daná kombinace, tedy produkt A a oblast Prahy, nachází v tabulce. Dále chceme spočítat, kolik se dohromady prodalo u této kombinace prodalo produktů, jaké byly průměrné tržby a jaká byla minimální a maximální tržba pro produkt A v Praze.
COUNTIFS
Začneme nejprve počtem. Pro počítání s několika podmínkami použijeme funkci COUNTIFS. Parametry funkce COUNTIFS:
= COUNTIFS (oblast kritérií1; kritérium1; oblast kritérií2; kritérium2; …..)
Funkce COUNTIFS má stejná kritéria jako funkce COUNTIF, jediným rozdílem od funkce COUNTIF je to, že COUNTIFS dovoluje zadat více než jedno kritérium. Chceme spočítat, kolikrát se zadaná kombinace vyskytuje v tabulce, takže napíšeme funkci COUNTIFS, kde je jako první kritérium oblast, ve které se vyskytuje první kritérium. Začneme s produktem, takže nejprve označíme sloupec s produkty. Následuje středník a první kritérium, což je hledaný produkt. Jelikož máme i druhé kritérium, tak napíšeme další středník a opět se přepneme do oblasti kritérií, tentokrát pro druhé kritérium, kterým je oblast. Označíme tedy celý sloupec s oblastí, středník a jako druhé kritérium je oblast Prahy. Ukončíme závorku u funkce COUNTIFS a potvrdíme. Funkce správně vrátila hodnotu tři, tedy že se zadaná kombinace v tabulce vyskytuje celkem třikrát.
SUMIFS
V další buňce chceme spočítat součet tržeb pro zadané parametry. K tomu využijeme funkci SUMIFS. Parametry funkce SUMIFS:
=SUMIFS (oblast_součtu; oblast_kritérií1; kritérium1; oblast_kritérií2; kritérium2; …..)
Parametry funkce SUMIFS jsou opět stejná jako u funkce SUMIF. Na co si ovšem musíte dát pozor je to, že od funkce SUMIF se funkce SUMIFS liší v pořadí kritérií. Na rozdíl od funkce SUMIF se ve funkci SUMIFS nejprve označuje oblast součtu a až následně se označují oblasti s kritérii a samotné kritérium.
Ve funkci SUMIFS tedy nejprve označíme hodnoty, které chceme sčítat, tedy tržby. Následuje sloupec s prvním kritériem, což je sloupec s produkty. Následuje kritérium, což je produkt v rozbalovacím seznamu. Středník a druhá oblast kritérií, což je sloupec s oblastí a jako poslední druhé kritérium, tedy vybraná oblast. Ukončíme závorku u funkce a funkci potvrdíme. Funkce SUMIFS vrátila správný součet tržeb pro zadanou kombinaci produktu a oblasti.
AVERAGEIFS
V dalším poli máme spočítat průměrnou výši tržeb. Pro výpočet průměru tržeb s dvěma kritérii použijeme funkci AVERAGEIFS. Parametry funkce AVERAGEIFS:
= AVERAGEIFS (oblast_pro_průměr; oblast_kritérií1; kritérium1; oblast_kritérií2; kritérium2….)
I v této funkci jsou parametry velmi podobné ostatním funkcím. Stejně jako u funkce SUMIFS se jako první ve funkci AVERAGEIFS označuje oblast buněk, ze kterých chceme spočítat průměr. V našem případě tedy označíme sloupec s tržbami. Následuje parametr oblasti prvního kritéria, takže označíme oblast s hodnotami ve sloupci produkty a jako první kritérium označíme hledaný produkt. Napíšeme středník a jelikož máme i druhé kritérium, tak označíme nejprve sloupec s oblastí a následně hledanou oblast. Funkci potvrdíme a funkce AVERAGEIFS vrátila průměrnou hodnotu tržeb pro tržby u Produktu A, které se prodaly v Praze.
MINIFS
Ve verzi pro více podmínek existují i funkce pro výpočet minimální a maximální hodnoty. Tedy funkce MINIFS a MAXIFS. U těchto dvou funkcí je zajímavé, že ale neexistuje žádná verze s jednou podmínkou, tedy funkce MINIF a MAXIF. Pokud chcete použít funkci MAX a MIN použít s jednou podmínkou, musíte to obejít v kombinaci s funkcí KDYŽ. Druhá věc je to, že tyto dvě funkce MAXIFS a MINIFS jsou dostupné zatím pouze od verze Excelu 2019 a výše.
Začneme s funkcí MINIFS, pomocí které najdeme nejnižší hodnotu tržeb pro zadanou kombinaci produktu a oblasti. Parametry funkce MINIFS:
= MINIFS (min_oblast; oblast_kritérií1; kritérium1; oblast_kritérií2; kritérium2;…)
Funkce MINIFS má jako první parametr oblast, ze které chceme určit minimální hodnotu, tedy v našem příkladu sloupec s tržbami. Následuje oblast pro první kritérium, což je sloupec s produkty a kritériem je tedy produkt. Napíšeme středník a následuje druhá oblast, což je sloupec s oblastí a jako druhé kritérium oblast. Funkci potvrdíme a vrátila se nejnižší hodnota tržeb pro zadanou kombinaci produktu a oblasti.
MAXIFS
Stejně by fungovala i funkce MAXIFS. Funkce MAXIFS má stejné parametry jako funkce MINIFS, akorát s tím rozdílem, že najde nejvyšší hodnotu. Parametry funkce MAXIFS:
=MAXIFS (max_oblast; oblast_kritérií1; kritérium1; oblast_kritérií2; kritérium2;….)
Napíšeme tedy funkci MAXIFS, kde nejprve označíme oblast, pro kterou chceme určit nejvyšší hodnotu, tedy sloupec s tržbami. Následuje oblast, ve které hledáme první kritérium, tedy sloupec s produkty. Prvním kritériem je produkt, středník a oblast pro druhé kritérium, středník a druhé kritérium, tedy oblast. Funkci potvrdíme a funkce MAXIFS vrátila nejvyšší hodnotu tržby pro zadanou kombinaci produktu a oblasti.
Ti z vás, kteří nepoužívají alespoň Office 2019, tak k těmto dvěma funkcím nemají přístup. Nicméně to můžete obejít použitím funkce KDYŽ a funkce MIN a MAX.
Všechny funkce jsou samozřejmě plně dynamické, takže pokud teď v rozbalovacím seznamu změníme parametry, tak se funkce automaticky přepočítají.
Tři podmínky ve funkci COUNTIFS a SUMIFS
Naprosto stejně by všechny funkce fungovaly i s více než dvěma kritérii. V dalším příkladu máme kromě parametru oblasti a produktu ještě navíc i parametr, že tržby musí u této kombinace být vyšší než 25 000 Kč. I pro tyto kombinace zkusíme spočítat počet kusů, součet a průměr tržeb a nakonec najdeme nejnižší a nejvyšší tržbu pro tato kritéria.
Začneme počtem, tedy funkcí COUNTIFS, kde označíme první oblast, kterou je sloupec s produkty, středník, kritérium je hledaný produkt, středník, druhou oblastí je sloupec s oblastí, středník a druhé kritérium je hledaná oblast, středník a třetí oblastí je sloupec s tržbami, jelikož potřebujeme ověřit kritérium, že tržby jsou vyšší než 25 000. Napíšeme středník a kritérium je znaménko vyšší v uvozovkách, ampersand a buňka s 25 000. Konec závorky a funkci potvrdíme.
Dále máme spočítat celkovou sumu tržeb. Použijeme funkci SUMIFS, kde nejprve označíme sloupec, který chceme sčítat, tedy sloupec s tržbami. Následuje sloupec s produkty, středník a jako kritérium hledaný produkt. Středník a sloupec s oblastí, středník a jako další kritérium oblast, středník a jako poslední oblastí je opět sloupec s tržbami, kde je kritérium opět znaménko větší v uvozovkách spojené ampersandem a buňkou s 25 000. Funkci potvrdíme a vrátil se správný výsledek.
Ten samý postup bychom použili i pro výpočet průměrné tržby a najití minimální a maximální tržby.
Pokud se vám video líbilo, budeme rádi, když se stanete členem Akademie Excelu na našem webu. Dejte nám rovněž v komentáři pod videem vědět, jaké excelové funkce používáte nejčastěji a s kterými máte naopak problém. A já se zatím budu těšit u dalšího videa.
Jedna odpověď
👍❤️