Загальна формула
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Резюме
Щоб ФІЛЬТРУВАТИ та витягти перше або останнє значення n (тобто перші 3 значення, перші 5 значень тощо), ви можете використовувати функцію ФІЛЬТР разом із INDEX та SEQUENCE. У наведеному прикладі формула в D5 має вигляд:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
де дані - іменований діапазон B5: B15.
Пояснення
Працюючи зсередини, ми використовуємо функцію SEQUENCE для побудови значення номера рядка для INDEX таким чином:
SEQUENCE(3,1,1,1)
Ми просимо SEQUENCE для масиву з 3 рядків x 1 стовпець, починаючи з 1, зі значенням кроку 1. Результатом є такий масив:
(1;2;3)
який повертається безпосередньо до функції INDEX як аргумент row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Щоб побудувати масив для INDEX, ми використовуємо функцію ФІЛЬТР для отримання списку непорожніх записів з іменованих даних діапазону (B5: B15) таким чином:
FILTER(data,data"")
Аргументом масиву є дані, а аргументом include - вираз data "". Це можна перекласти буквально як "повернення значень із названих даних діапазону, де значення в даних не порожні". Результат - масив із 9 значеннями, подібними до цього:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Значення повідомлень, пов’язані з двома порожніми клітинками, були видалені. Цей масив повертається функції INDEX як аргумент масиву.
Нарешті, INDEX повертає перше, друге та третє значення з масиву, що повертається FILTER:
("Atlanta";"Chicago";"Dallas")
Останні n значень
Щоб отримати останні n значень за допомогою FILTER, ви використовуєте ту саму структуру формули, із вхідними даними SEQUENCE, зміненими для побудови масиву "останніх n" рядків. Наприклад, щоб отримати останні 3 непусті значення у наведеному прикладі, ви можете скористатися такою формулою:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Основна хитрість тут полягає в підрахунку непустих записів у названих даних діапазону, як це:
SUM(--(data""))
Ми використовуємо подвійний негатив, щоб примусити значення TRUE FALSE до 1s і 0s, а потім використовувати функцію SUM, щоб отримати рахунок. Результат повертається як початковий аргумент всередині SEQUENCE. Ми пропонуємо -1 за крок, щоб зробити крок назад від початку.
Ми також обертаємо функцію СОРТУВАННЯ навколо SEQUENCE, щоб повернутий масив мав значення (7; 8; 9), а не (9; 8; 7). Це гарантує повернення значень у тому самому порядку, в якому вони відображаються у вихідних даних.
