Общая формула
=FILTER(data,range>=LARGE(range,n))
Резюме
Чтобы отфильтровать набор данных для отображения первых n значений, вы можете использовать функцию ФИЛЬТР вместе с функцией НАИБОЛЬШИЙ. В показанном примере формула в F5:
=FILTER(data,score>=LARGE(score,3))
где данные (B5: D16) и оценка (D5: D16) являются именованными диапазонами.
Объяснение
Эта формула использует функцию ФИЛЬТР для извлечения данных на основе логического теста, построенного с помощью функции НАИБОЛЬШИЙ.
Функция НАИБОЛЬШИЙ - это простой способ получить n-е наибольшее значение в диапазоне. Просто укажите диапазон для первого аргумента (массива) и значение для n в качестве второго аргумента (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
В этом примере логика FILTER построена с помощью этого фрагмента:
score>=LARGE(score,3)
который возвращает ИСТИНА, если оценка больше или равна 3-й наивысшей оценке. Поскольку в диапазоне D5: D16 12 ячеек, это выражение возвращает массив из 12 значений TRUE и FALSE в таком массиве:
(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Этот массив возвращается непосредственно в функцию FILTER в качестве аргумента включения, где он используется для фильтрации данных. Только строки, результат которых ИСТИНА, попадают в окончательный результат. Результат формулы в F5 включает в себя 3 наивысших значения в данных, которые попадают в диапазон F5: H7.
Сортировать результаты по баллам
По умолчанию FILTER возвращает совпадающие записи в том же порядке, в котором они появляются в исходных данных. Чтобы отсортировать результаты в порядке убывания по количеству очков, вы можете вложить исходную формулу ФИЛЬТР в функцию СОРТИРОВАТЬ следующим образом:
=SORT(FILTER(data,score>=LARGE(score,3)),3,-1)
Здесь FILTER возвращает результаты непосредственно в функцию SORT в качестве аргумента массива. Для индекса сортировки установлено значение 3 (оценка), а для порядка сортировки установлено значение -1 для убывания.
