Общая формула
=INDEX(FILTER(data,data""),SEQUENCE(n,1,1,1))
Резюме
Для ФИЛЬТРАЦИИ и извлечения первых или последних n значений (т.е. первых 3 значений, первых 5 значений и т. Д.) Вы можете использовать функцию ФИЛЬТР вместе с ИНДЕКС и ПОСЛЕДОВАТЕЛЬНОСТЬ. В показанном примере формула в D5:
=INDEX(FILTER(data,data""),SEQUENCE(3,1,1,1))
где data - именованный диапазон B5: B15.
Объяснение
Работая изнутри, мы используем функцию ПОСЛЕДОВАТЕЛЬНОСТЬ, чтобы построить значение номера строки для ИНДЕКС следующим образом:
SEQUENCE(3,1,1,1)
Мы запрашиваем SEQUENCE для массива из 3 строк x 1 столбец, начиная с 1, со значением шага 1. Результатом является такой массив:
(1;2;3)
который возвращается непосредственно в функцию ИНДЕКС как аргумент row_num:
=INDEX(FILTER(data,data""),(1;2;3))
Чтобы создать массив для ИНДЕКС, мы используем функцию ФИЛЬТР, чтобы получить список непустых записей из данных именованного диапазона (B5: B15) следующим образом:
FILTER(data,data"")
Аргументом массива являются данные, а аргументом включения - данные выражения "". Это можно перевести буквально как «возвращаемые значения из данных именованного диапазона, где значения в данных не являются пустыми». Результатом является массив с 9 значениями, подобными этому:
("Atlanta";"Chicago";"Dallas";"Denver";"Los Angeles";"Miami";"New York";"Seattle";"Minneapolis")
Значения уведомлений, связанные с двумя пустыми ячейками, были удалены. Этот массив возвращается функции INDEX в качестве аргумента массива.
Наконец, ИНДЕКС возвращает 1-е, 2-е и 3-е значения из массива, возвращаемого ФИЛЬТРОМ:
("Atlanta";"Chicago";"Dallas")
Последние n значений
Чтобы получить последние n значений с помощью FILTER, вы используете ту же структуру формулы с измененными входными данными для SEQUENCE для создания массива «последних n» номеров строк. Например, чтобы получить последние 3 непустых значения в показанном примере, вы можете использовать следующую формулу:
=INDEX(FILTER(data,data""),SORT(SEQUENCE(3,1,SUM(--(data"")),-1)))
Основная хитрость здесь заключается в подсчете непустых записей в данных именованного диапазона следующим образом:
SUM(--(data""))
Мы используем двойное отрицание, чтобы заставить значения ИСТИНА ЛОЖЬ равняться 1 и 0, а затем используем функцию СУММ, чтобы получить счет. Результат возвращается как начальный аргумент внутри SEQUENCE. Мы указываем -1 для шага назад с самого начала.
Мы также оборачиваем функцию SORT вокруг SEQUENCE, чтобы возвращаемый массив был (7; 8; 9), а не (9; 8; 7). Это гарантирует, что значения возвращаются в том же порядке, в котором они появляются в исходных данных.
