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