Общая формула
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Резюме
Чтобы отфильтровать данные для отображения первых n значений, соответствующих определенным критериям, вы можете использовать функцию ФИЛЬТР вместе с функциями НАИБОЛЬШИЙ и ЕСЛИ. В показанном примере формула в F5:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
где данные (B5: D16), группа (C5: C16) и оценка (D5: D16) являются именованными диапазонами.
Объяснение
Эта формула использует функцию ФИЛЬТР для извлечения данных на основе логического теста, построенного с помощью функций НАИБОЛЬШИЙ и ЕСЛИ. Результат - 3 лучших результата в группе B.
Функция ФИЛЬТР применяет критерии с аргументом включения. В этом примере критерии конструируются с помощью такой логической логики:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Левая часть выражения нацелена на оценку, превышающую или равную 3-й наивысшей оценке в группе B:
score>=LARGE(IF(group="b",score),3)
Функция ЕСЛИ используется, чтобы убедиться, что НАИБОЛЬШИЙ работает только с оценками группы B. Поскольку всего у нас 12 оценок, IF возвращает массив с 12 такими результатами:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Обратите внимание, что единственные оценки, оставшиеся после операции, относятся к группе B. Все остальные оценки - ЛОЖЬ. Этот массив возвращается непосредственно в LARGE в качестве аргумента массива:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE игнорирует значения FALSE и возвращает 3-е место, 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 возвращает совпадающие записи в том же порядке, в котором они появляются в исходных данных. Чтобы отсортировать результаты в порядке убывания по количеству очков, вы можете вложить исходную формулу ФИЛЬТР в функцию СОРТИРОВАТЬ следующим образом:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Здесь FILTER возвращает результаты непосредственно в функцию SORT в качестве аргумента массива. Для индекса сортировки установлено значение 3 (оценка), а для порядка сортировки установлено значение -1 для убывания.
