17. nodaļa · 17.16
Top 5 parādīšana katrai kategorijai
Vislabāk pārdotie produkti katrā nodaļā, lielākie klienti katrā reģionā, noslogotākās dienas katrā mēnesī: Jums nav vajadzīgs visa saraksta top 5, bet gan top 5 vienas kategorijas ietvaros. LARGE darbojas ar visu kolonnu, un vienādu summu gadījumā tā divreiz parāda vienu un to pašu produktu. Euro-Office to var atrisināt ar vienu formulu.
Kā piemēru izmantosim uzņēmuma Bloemisterij Het Zonnetje gada apgrozījumu pa produktiem: kolonnā A ir produkts, kolonnā B kategorija (Rozen, Tulpen, Planten) un kolonnā C apgrozījums, no 2. līdz 18. rindai. Šūnā F1 ir kategorija, kuru vēlaties redzēt: Rozen.
Top 5 vienā formulā
Šūnā H2 ierakstiet:
=TAKE(SORT(FILTER(A2:C18;B2:B18=F1);3;-1);5)
Lasiet to no iekšpuses uz āru:
- FILTER(A2:C18;B2:B18=F1) atstāj tikai tās kategorijas rindas, kas norādīta šūnā F1, ar visām trim kolonnām.
- SORT(...;3;-1) sakārto šīs rindas pēc trešās kolonnas (apgrozījuma) no lielākā uz mazāko (-1).
- TAKE(...;5) atstāj augšējās 5 rindas.
Nospiediet Enter: pieci produkti ar kategoriju un apgrozījumu parādās šūnās no H2 līdz J6. Ja šūnā F1 ierakstīsiet Planten, saraksts uzreiz mainīsies.

Vienādas summas nerada problēmas: piemērā gan Grand Prix, gan Ecuadoriaanse roos apgrozījums ir 3 400, un abi ir sarakstā. Izmantojot divreiz LARGE ar INDEX un MATCH, divreiz parādītos Grand Prix.
Tikai summas, katra kategorija blakus
Ja vēlaties tikai piecas lielākās summas, piemēram, vairākām kategorijām blakus, izmantojiet LARGE filtrētam sarakstam. Ievadiet kategorijas šūnās F1, G1 un tālāk, atlasiet F2:G6, ierakstiet:
=IFERROR(LARGE(FILTER($C$2:$C$18;$B$2:$B$18=F$1);ROWS($1:1));"")
un nospiediet Ctrl+Enter. FILTER atstāj tikai tās kategorijas summas, kas norādīta 1. rindā. ROWS($1:1) pirmajā rindā ir 1, bet zemākajās rindās kļūst par 2, 3, 4 un 5: lielākā, otrā lielākā un tā tālāk. IFERROR atstāj šūnu tukšu, ja kategorijā ir mazāk nekā pieci produkti; kategorijā Tulpen to ir tikai četri.
Ņemiet vērā: Excel tīmekļa vietnēs bieži ir norādīts =LARGE(IF($B$2:$B$18=F$1;$C$2:$C$18);ROWS($1:1)). Euro-Office, nospiežot parasto Enter, šī formula dod nepareizu rezultātu: IF tad neskatās uz visu kolonnu, tāpēc Jūs saņemat visu kategoriju lielākās summas vai #NUM!. Ja formulu pabeidzat ar Ctrl+Shift+Enter, tā iegūst figūriekavas {…}, un rezultāts ir pareizs. Iepriekš aprakstītā FILTER metode ir vienkāršāka.
Tikai lielākā summa
Vienam skaitlim tas ir vienkāršāk: =MAXIFS(C2:C18;B2:B18;F1) parāda lielāko apgrozījumu kategorijā, šeit 6 150.
Biežākās kļūdas
- Mazāk nekā 5 produkti kategorijā: bez IFERROR funkcija LARGE rindās, kuru nav, parāda #NUM!. TAKE tas netraucē, tā vienkārši parāda mazāk rindu.
- Formula ar TAKE paliek tukša: 5 rindās un 3 kolonnās, kur jāparādās rezultātam, jau kaut kas ir ierakstīts. Skatiet Saraksti, kas atjaunojas paši, ar FILTER, UNIQUE un SORT.
- Kolēģis ar Excel 2019 vai vecāku versiju pie TAKE, SORT un FILTER redz #NAME?.
