17. peatükk · 17.16
Top 5 kuvamine kategooria kaupa
Iga osakonna enimmüüdud tooted, iga piirkonna suurimad kliendid, iga kuu kõige tihedamad päevad: sa ei soovi kogu loendi top 5, vaid top 5 ühe kategooria piires. LARGE töötab kogu veeruga ja annab võrdsete summade korral sama toote kaks korda. Euro-Office'is lahendad selle ühe valemiga.
Näiteks võtame Bloemisterij Het Zonnetje toodete aastakäibe: veerus A on toode, veerus B kategooria (Rozen, Tulpen, Planten) ja veerus C käive, ridadel 2 kuni 18. Lahtris F1 on kategooria, mida soovid näha: Rozen.
Top 5 ühe valemiga
Sisesta lahtrisse H2:
=TAKE(SORT(FILTER(A2:C18;B2:B18=F1);3;-1);5)
Loe valemit seestpoolt väljapoole:
- FILTER(A2:C18;B2:B18=F1) jätab alles ainult lahtris F1 oleva kategooria read, koos kõigi kolme veeruga.
- SORT(...;3;-1) sorteerib need read kolmanda veeru (käibe) järgi, suuremast väiksemani (-1).
- TAKE(...;5) jätab alles 5 ülemist rida.
Vajuta Enter: viis toodet koos kategooria ja käibega ilmuvad lahtritesse H2 kuni J6. Kui sisestad lahtrisse F1 Planten, muutub loend kohe.

Võrdsed summad ei ole probleem: näites on nii Grand Prix kui ka Ecuadoriaanse roos käive 3 400 ja mõlemad on loendis. Kahe LARGE'i puhul koos INDEX ja MATCH funktsiooniga ilmuks kaks korda Grand Prix.
Ainult summad, kategooriad kõrvuti
Kui soovid ainult viit suurimat summat, näiteks mitme kategooria jaoks kõrvuti, kasuta LARGE funktsiooni filtreeritud loendil. Pane kategooriad lahtritesse F1, G1 ja edasi, vali F2:G6 ja sisesta:
=IFERROR(LARGE(FILTER($C$2:$C$18;$B$2:$B$18=F$1);ROWS($1:1));"")
ning vajuta Ctrl+Enter. FILTER jätab alles ainult rea 1 kategooria summad. ROWS($1:1) on esimesel real 1 ja muutub allolevatel ridadel 2, 3, 4 ja 5: suurim, suuruselt teine ja nii edasi. IFERROR jätab lahtri tühjaks, kui kategoorias on alla viie toote; Tulpen kategoorias on neid ainult neli.
Tähelepanu: Exceli veebilehtedel on sageli valem =LARGE(IF($B$2:$B$18=F$1;$C$2:$C$18);ROWS($1:1)). Euro-Office'is annab see tavalise Enter klahviga vale tulemuse: IF ei vaata siis kogu veergu, nii et saad kõigi kategooriate suurimad summad või #NUM!. Kui lõpetad valemi klahvidega Ctrl+Shift+Enter, saab valem looksulud {…} ja tulemus on õige. Ülaltoodud FILTER on lihtsam.
Ainult suurim summa
Ühe arvu jaoks on see lihtsam: =MAXIFS(C2:C18;B2:B18;F1) annab kategooria suurima käibe, siin 6 150.
Lõksud
- Kategoorias on alla 5 toote: ilma IFERROR funktsioonita annab LARGE puuduvatel ridadel #NUM!. TAKE seda probleemi ei tunne ja näitab lihtsalt vähem ridu.
- TAKE valem jääb tühjaks: 5 reas ja 3 veerus, kuhu tulemus peaks minema, on juba midagi. Vaata Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT.
- Kolleeg, kellel on Excel 2019 või vanem, näeb TAKE, SORT ja FILTER funktsiooni puhul #NAME?.
