Обща формула
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Обобщение
За да филтрирате данните, за да покажете най-добрите n стойности, които отговарят на определени критерии, можете да използвате функцията FILTER заедно с функциите LARGE и IF. В показания пример формулата във F5 е:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
където данните (B5: D16), групата (C5: C16) и резултатът (D5: D16) са наречени диапазони.
Обяснение
Тази формула използва функцията FILTER за извличане на данни въз основа на логически тест, конструиран с функциите LARGE и IF. Резултатът е топ 3 резултата в група Б.
Функцията FILTER прилага критерии с аргумента за включване. В този пример критериите се изграждат с логическа логика по следния начин:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Лявата страна на израза цели резултати, по-големи или равни на третия най-висок резултат в група Б:
score>=LARGE(IF(group="b",score),3)
Функцията IF се използва, за да се увери, че LARGE работи само с резултати от група B. Тъй като имаме общо 12 резултата, IF връща масив с 12 резултата като този:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Забележете, че единствените резултати, оцелели след операцията, са от група Б. Всички останали резултати са НЕВЯРНИ. Този масив се връща директно на LARGE като аргумент на масива:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE игнорира FALSE стойностите и връща третия най-висок резултат, 83.
Вече можем да опростим формулата, за да:
=FILTER(data,(score>=83)*(group="b"))
което решава до:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
И накрая, FILTER връща записи за Мейсън, Ани и Касиди, които се разливат в диапазона F5: H7.
Сортирайте резултатите по резултат
По подразбиране FILTER ще върне съвпадащи записи в същия ред, в който се появяват в изходните данни. За да сортирате резултатите в низходящ ред по резултат, можете да вложите оригиналната формула FILTER във функцията SORT по следния начин:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Тук FILTER връща резултатите директно към функцията SORT като аргумент на масива. Индексът за сортиране е зададен на 3 (резултат) и редът за сортиране е зададен на -1 за низходящ ред.
