V dnešním videu si ukážeme nejen to, jak pomocí několika jednoduchých excelových funkcí můžete najít nejnižší nebo nejvyšší hodnoty v seznamu, ale třeba i to, jak pomocí funkcí najít například tři nejvyšší nebo tři nejnižší hodnoty ze seznamu. K vyřešení jednotlivých příkladů použijeme funkce MIN, MAX, SMALL a LARGE. Nakonec si ještě ukážeme způsob, jak tyto funkce zkombinovat s jednoduchou podmínkou KDYŽ pro větší flexibilitu. Jako vždy si funkce ukážeme na jednoduchých a praktických příkladech.
Excelový soubor ke stažení
Nejvyšší a nejnižší hodnoty v seznamu v Excelu včetně podmínky
Pro dnešní ukázku poslouží jednoduchá tabulka dat, ve které máme pouze produkty a jejich hodnoty. Naším úkolem bude najít několik nejnižších a nejvyšších hodnot. Pořadí hodnot jsme se věnovali v jiném videu.
Funkce MIN
Funkce MIN je funkce, která vrátí ze série hodnot nejnižší hodnotu. Funkce MIN je velmi jednoduchá funkce, která má jako parametry pouze čísla.
= MIN (číslo1; [číslo2]; [číslo3];…)
Do funkce MIN můžeme napsat jednotlivá čísla, oddělená středníkem a když funkci potvrdíme, tak funkce MIN vrátí nejnižší hodnotu ze zadaných hodnot. Hodnoty do funkce MIN ovšem nemusíme ručně doplňovat, stačí ve funkci MIN označit sérii hodnot, ze kterých chceme minimální hodnotu určit. Z následující tabulky hodnot (B4:B10) máme najít nejnižší hodnotu. Vedle tabulky tedy do buňky E3 napíšeme funkci MIN, kde označíme celou sérii hodnot. Funkci potvrdíme a funkce MIN vrátí nejnižší hodnotu ze seznamu.
Funkce MAX
Protějškem funkce MIN je funkce MAX, která naopak vyhledá nejvyšší hodnotu v listu hodnot. Stejně jako funkce MIN má i funkce MAX jako parametry pouze čísla, ze kterých chceme nejvyšší hodnotu najít.
= MAX (číslo1; [číslo2]; [číslo3];…)
A stejně jako u funkce MIN do funkce MAX můžete ručně napsat hodnoty, oddělené středníkem a po potvrzení vám funkce MAX vrátí nejvyšší hodnotu. Z tabulky hodnot (B4:B10) máme najít nejvyšší hodnotu, takže vedle tabulky do buňky E5 napíšeme funkci MAX, kde opět označíme celou sérii hodnot a funkci potvrdíme. Funkce MAX vrátí nejvyšší číslo z listu hodnot.
Tyto dvě jednoduché funkce vám ovšem pomohou najít pouze nejnižší a nejvyšší hodnotu. Pokud ovšem chceme najít například třetí nejvyšší nebo druhou nejnižší hodnotu z listu, tak nám s tímto úkolem funkce MIN a MAX nepomohou. Funkce MIN a MAX vždy najdou pouze první nejvyšší a první nejnižší hodnotu. Pro najití x-té nejnižší nebo nejvyšší hodnoty musíme použít jiné funkce, kterými jsou funkce SMALL a LARGE, což v překladu znamená malý a velký.
Funkce LARGE
Funkce LARGE vrátí x-tou nejvyšší hodnotu ze seznamu hodnot. Funkce LARGE má pouze dva povinné parametry, a těmi jsou pole a parametr k. Pole představuje pole hodnot, ze kterých chceme x-tou nejvyšší hodnotu najít a parametr k určuje, kolikátou hodnotu chceme najít.
= LARGE (pole; k)
Budeme pokračovat s naší tabulkou hodnot a tentokrát chceme najít třetí nejvyšší hodnotu ze seznamu. Chceme být ale u výběru hodnoty flexibilní, takže to, kolikátou hodnotu chceme najít určuje buňka E7, kterou můžeme měnit. Momentálně zde máme vyplněnou trojku, takže chceme najít třetí nejvyšší hodnotu. Pro najití nejvyšších hodnot použijeme funkci LARGE.
Do buňky G7 napíšeme funkci LARGE, kde jako pole označíme hodnoty (B4:B10) a jako parametr k označíme, kolikátou hodnotu chceme najít (E7). Funkci potvrdíme a vrátila se třetí nejvyšší hodnota ze seznamu. Pokud bychom číslo v buňce E7 změnili na jedničku, tak funkce LARGE vrátí identické číslo jako funkce MAX.
Funkce SMALL
Opakem funkce LARGE je funkce SMALL, která najde x-tou nejnižší hodnotu ze seznamu. Parametry funkce jsou stejné jako u funkce LARGE. I u funkce SMALL máme jako povinné parametry pole a parametr k.
= SMALL (pole; k)
Stejně jako v předchozím příkladu, chceme najít třetí nejnižší hodnotu ze seznamu. V buňce G9 použijeme tedy funkci SMALL, kde jako pole označíme hodnoty (B4:B10) a jako parametr k označíme pořadové číslo, tedy kolikátou hodnotu hledáme (E9). Funkci potvrdíme a funkce SMALL vrátila třetí nejnižší hodnotu ze seznamu. Pokud bychom hledali první nejnižší hodnotu, tak bychom do buňky E9 vyplnili jedničku a vrátilo by se stejné číslo, jako vrátila funkce MIN.
Tři nejvyšší a tři nejnižší hodnoty
Funkce SMALL a LARGE můžete použít i k najití listu hodnot. V dalším příkladu máme za úkol vrátit list tří nejvyšších a nejnižších hodnot ze seznamu. Stejně jako v předchozím příkladu k vyřešení úkolu použijeme funkce LARGE a SMALL.
Nejprve najdeme tři nejvyšší hodnoty. Klikneme do buňky F11 a napíšeme funkci LARGE, kde jako pole opět označíme všechny hodnoty (B4:10) a jako parametr k označíme jedničku (E11), jelikož chceme najít nejvyšší hodnotu. Ještě před potvrzením funkce ale správně zafixujeme buňky, jelikož máme v plánu funkci stahovat směrem dolů. Musíme tedy zafixovat parametr pole a to jak pro sloupce, tak řádky ($B$4:$B$10). Parametr k naopak fixovat nemusíme, jelikož se buňka bude posouvat spolu s tím, jak potáhneme funkci dolů. Teď můžeme funkci potvrdit a stáhnout dolů. Funkce LARGE doplnila tři nejvyšší hodnoty.
To samé bychom udělali i pro tři nejnižší hodnoty. Do buňky J11 napíšeme funkci SMALL, kde jako pole označíme všechny hodnoty ($B$4:$B$10), buňky rovnou zafixujeme klávesou F4 jak pro sloupce, tak řádky a jako parametr k označíme první pořadové číslo (I11). Funkci potvrdíme, stáhneme dolů a funkce SMALL vrátila tři nejnižší čísla ze seznamu.
Nejnižší a nejvyšší hodnoty s podmínkou
A co když potřebujete najít nejnižší a nejvyšší hodnotu ze seznamu, ale potřebujete při tom ověřit i nějakou podmínku? Ve verzích Excelu od roku 2019 a výše existují funkce MINIFS a MAXIFS, které dovolují najít nejvyšší a nejnižší hodnotu na základě více kritérií. Jelikož tyto funkce ale nejsou dostupné všem, tak si ukážeme i způsob, jak najít nejnižší a nejvyšší hodnoty s podmínkovou funkcí KDYŽ.
Řekněme, že z následující tabulky počtu prodejů chceme najít nejnižší hodnotu ze seznamu, ale v seznamu máme i nuly, jelikož se některé produkty neprodávají. Funkce MIN samozřejmě v takovém seznamu vždy pro nejnižší hodnotu najde nulu. Pokud bychom měli v seznamu hodnot záporné hodnoty, tak by funkce MIN našla zápornou hodnotu. Nás by ale zajímalo, jaká byla nejnižší hodnota prodaných produktů mimo produktů, kterých se prodalo nula. Použijeme tedy kombinaci funkce MIN a podmínky KDYŽ.
Začneme s funkcí KDYŽ, která bude jádrem funkce MIN. V buňce E21 napíšeme funkci KDYŽ, kde nejprve ověříme podmínku, že hodnoty v seznamu (B21:B27) musí být vyšší než nula. Následuje středník a co chceme vrátit, pokud bude podmínka splněná? Chceme vrátit seznam těchto hodnot (B21:B27). Ukončíme závorku a podíváme se na funkci KDYŽ s klávesou F9. Označíme tedy funkci a zmáčkneme kláves F9. Na řádcích, kde je nula vrací funkce KDYŽ nepravdu, a na ostatních řádcích vrací hodnoty. Vrátíme se do funkce stisknutím kláves CTRL+Z. Teď stačí tuto funkci KDYŽ zabalit do funkce MIN. Funkce KDYŽ doručí funkci MIN pouze hodnoty nad nulu, a funkce MIN bude nepravdy, tedy hodnoty nula ignorovat.
Pro potvrzení funkce budou muset starší verze Excelu zmáčknout CTRL+SHIFT a ENTER, jelikož se jedná o maticový vzorec. Excely od verze 2019 mohou funkci potvrdit stisknutím klávesy ENTER. Funkci potvrdíme a funkce MIN v kombinaci s funkcí KDYŽ doručila nejnižší hodnotu ze seznamu při ignoraci nul.
Tento postup by fungoval i v případě, že by se v seznamu vyskytly záporné hodnoty. Pokud nějakou hodnotu změníme na zápornou, tak ji funkce MIN bude ignorovat a vrátí nejnižší hodnotu z hodnot nad nulu.
Stejně tak můžete s podmínkou použít i funkci MAX. V dalším příkladu chceme najít nejvyšší hodnotu, ale pouze u hodnot pro produkt A. Začneme s funkcí KDYŽ, kde jako podmínku označíme sloupec s produkty a zeptáme se, zda se rovná produktu A, jelikož se jedná o textovou hodnotu, tak musíme produkt A zabalit do uvozovek. Pokud se produkt bude rovnat produktu A, tak chceme vrátit list hodnot. Funkci opět zabalíme do funkce MAX. Opět, starší verze Excelu musí pro potvrzení funkce použít klávesy CTRL+SHIFT a ENTER. Novějším Excelům stačí ENTER. A funkce MAX v kombinaci s funkcí KDYŽ vrátila nejvyšší hodnotu pouze pro produkt A.
A co by se stalo, kdybychom měli v seznamu hodnot duplicity? Pokud bychom měli v seznamu dvě nejvyšší hodnoty duplicitní, tak by funkce LARGE vrátila na prvním i druhém místě duplicitu. O tom, jak si poradit v tomto případě s duplicitami se podívejte na navazující bonusové video Jak najít nejvyšší a nejnižší hodnoty, když máte v seznamu duplicity.
