Глава 17 · 17.16
Показване на топ 5 по категория
Най-продаваните продукти по отдел, най-големите клиенти по регион, най-натоварените дни по месец: не Ви трябва топ 5 на целия списък, а топ 5 в рамките на една категория. LARGE работи върху цялата колона и при еднакви суми връща един и същ продукт два пъти. В Euro-Office решавате това с една формула.
Като пример използваме годишния оборот по продукт на Bloemisterij Het Zonnetje: в колона A е продуктът, в B е категорията (Rozen, Tulpen, Planten), а в C е оборотът, от ред 2 до ред 18 включително. В F1 е категорията, която искате да видите: Rozen.
Топ 5 с една формула
Въведете в H2:
=TAKE(SORT(FILTER(A2:C18;B2:B18=F1);3;-1);5)
Четете я отвътре навън:
- FILTER(A2:C18;B2:B18=F1) оставя само редовете от категорията в F1, с трите колони.
- SORT(...;3;-1) сортира тези редове по третата колона (оборота), от най-висок към най-нисък (-1).
- TAKE(...;5) оставя горните 5 реда.
Натиснете Enter: петте продукта с категорията и оборота се появяват в H2 до J6. Ако въведете в F1 Planten, списъкът се променя веднага.

Еднаквите суми не са проблем: в примера Grand Prix и Ecuadoriaanse roos имат оборот от по 3 400, и двата присъстват в списъка. При двукратно използване на LARGE с INDEX и MATCH би се появил два пъти Grand Prix.
Само сумите, по категории една до друга
Ако искате само петте най-високи суми, например за няколко категории една до друга, използвайте LARGE върху филтриран списък. Поставете категориите в F1, G1 и нататък, маркирайте F2:G6, въведете:
=IFERROR(LARGE(FILTER($C$2:$C$18;$B$2:$B$18=F$1);ROWS($1:1));"")
и натиснете Ctrl+Enter. FILTER оставя само сумите от категорията в ред 1. ROWS($1:1) е 1 в първия ред и става 2, 3, 4 и 5 в редовете под него: най-голямата, втората по големина и т.н. IFERROR оставя клетката празна, ако дадена категория има по-малко от пет продукта; Tulpen има само четири.
Внимание: на сайтове за Excel често се среща =LARGE(IF($B$2:$B$18=F$1;$C$2:$C$18);ROWS($1:1)). В Euro-Office с обикновен Enter тя дава грешен резултат: IF тогава не разглежда цялата колона, така че получавате най-големите суми от всички категории или #NUM!. Ако я въведете с Ctrl+Shift+Enter, формулата получава фигурни скоби {…} и резултатът е правилен. FILTER, както е показано по-горе, е по-лесният начин.
Само най-високата сума
За едно число е по-лесно: =MAXIFS(C2:C18;B2:B18;F1) връща най-високия оборот в категорията, тук 6 150.
Капани
- По-малко от 5 продукта в категория: без IFERROR функцията LARGE връща #NUM! в редовете, които липсват. TAKE няма този проблем и просто показва по-малко редове.
- Формулата с TAKE остава празна: в 5-те реда и 3-те колони, където трябва да се появи резултатът, вече има нещо. Вижте Списъци, които се обновяват сами, с FILTER, UNIQUE и SORT.
- Колега с Excel 2019 или по-стара версия вижда #NAME? при TAKE, SORT и FILTER.
