Загальна формула
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Резюме
Щоб фільтрувати дані, щоб показати верхні n значень, які відповідають певним критеріям, ви можете використовувати функцію ФІЛЬТР разом із функціями LARGE і IF. У наведеному прикладі формула в F5 має вигляд:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
де дані (B5: D16), група (C5: C16) та оцінка (D5: D16) називаються діапазонами.
Пояснення
Ця формула використовує функцію FILTER для отримання даних на основі логічного тесту, побудованого з функціями LARGE і IF. Результат - найкращі 3 бали в групі B.
Функція FILTER застосовує критерії з аргументом include. У цьому прикладі критерії будуються з логічною логікою, як це:
(score>=LARGE(IF(group="b",score),3))*(group="b")
Ліва сторона виразу націлена на оцінки, більші або рівні 3-му найвищому балу в групі В:
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)
Зверніть увагу, що єдині бали, які пережили операцію, належать до групи B. Цей масив повертається безпосередньо до 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 повертає відповідні записи в тому самому порядку, в якому вони відображаються у вихідних даних. Щоб сортувати результати за спаданням за балами, ви можете вкласти оригінальну формулу ФІЛЬТР всередину функції СОРТУВАННЯ так:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Тут FILTER повертає результати безпосередньо до функції SORT як аргумент масиву. Для індексу сортування встановлено значення 3 (оцінка), а для порядку сортування - -1 для спадання.
