Kapitola 17 · 17.16
Zobrazení prvních 5 položek v každé kategorii
Nejprodávanější produkty v každém oddělení, největší zákazníci v každém regionu, nejrušnější dny v měsíci: nechcete prvních 5 z celého seznamu, ale prvních 5 v rámci jedné kategorie. Funkce LARGE pracuje s celým sloupcem a při stejných částkách vrátí dvakrát stejný produkt. V Euro-Office to vyřešíte jediným vzorcem.
Jako příklad použijeme roční obrat podle produktů firmy Bloemisterij Het Zonnetje: ve sloupci A je produkt, v B kategorie (Rozen, Tulpen, Planten) a v C obrat, v řádcích 2 až 18. V buňce F1 je kategorie, kterou chcete zobrazit: Rozen.
Prvních 5 položek jedním vzorcem
Do buňky H2 zadejte:
=VZÍT(SORT(FILTER(A2:C18;B2:B18=F1);3;-1);5)
Čtěte ho zevnitř ven:
- FILTER(A2:C18;B2:B18=F1) ponechá pouze řádky kategorie z buňky F1, a to se všemi třemi sloupci.
- SORT(...;3;-1) seřadí tyto řádky podle třetího sloupce (obratu) od nejvyššího po nejnižší (-1).
- VZÍT(...;5) ponechá horních 5 řádků.
Stiskněte Enter: pět produktů s kategorií a obratem se zobrazí v buňkách H2 až J6. Pokud do F1 zadáte Planten, seznam se okamžitě změní.

Stejné částky nejsou problém: v příkladu mají Grand Prix i Ecuadoriaanse roos shodně obrat 3 400 a v seznamu jsou obě. Při dvojím použití LARGE s INDEX a POZVYHLEDAT by se dvakrát zobrazil Grand Prix.
Pouze částky, kategorie vedle sebe
Chcete-li pouze pět nejvyšších částek, například pro více kategorií vedle sebe, použijte LARGE na filtrovaný seznam. Zadejte kategorie do buněk F1, G1 a dalších, vyberte F2:G6 a napište:
=IFERROR(LARGE(FILTER($C$2:$C$18;$B$2:$B$18=F$1);ŘÁDKY($1:1));"")
a stiskněte Ctrl+Enter. FILTER ponechá pouze částky kategorie z řádku 1. ŘÁDKY($1:1) má v prvním řádku hodnotu 1 a v řádcích pod ním 2, 3, 4 a 5: největší, druhá největší a tak dále. IFERROR ponechá buňku prázdnou, pokud má kategorie méně než pět produktů; Tulpen jich má jen čtyři.
Pozor: na webech o Excelu často najdete =LARGE(KDYŽ($B$2:$B$18=F$1;$C$2:$C$18);ŘÁDKY($1:1)). Ten v Euro-Office při obyčejném stisku Enter vrátí chybný výsledek: KDYŽ pak nebere v úvahu celý sloupec, takže dostanete největší částky ze všech kategorií nebo #NUM!. Pokud vzorec potvrdíte klávesami Ctrl+Shift+Enter, dostane složené závorky {…} a výsledek je správný. FILTER, jak je uvedeno výše, je jednodušší.
Pouze nejvyšší částka
Pro jediné číslo je to jednodušší: =MAXIFS(C2:C18;B2:B18;F1) vrátí nejvyšší obrat v rámci kategorie, zde 6 150.
Na co si dát pozor
- Méně než 5 produktů v kategorii: bez IFERROR vrátí LARGE #NUM! u řádků, které neexistují. Funkci VZÍT to nevadí a jednoduše zobrazí méně řádků.
- Vzorec s funkcí VZÍT zůstane prázdný: v 5 řádcích a 3 sloupcích, kam se má výsledek vypsat, už něco je. Viz Seznamy, které se samy aktualizují, s funkcemi FILTER, UNIQUE a SORT.
- Kolega s Excelem 2019 nebo starším uvidí u funkcí VZÍT, SORT a FILTER chybu #NAME?.
