Получение первого непустого значения в столбце или строке

Чтобы получить первое значение (первую непустую ячейку, игнорируя ошибки) из диапазона одного столбца или одной строки, можно использовать формулу на основе функций ИНДЕКС и ПОИСКПОЗ. Однако если вы не хотите игнорировать ошибки в своём диапазоне, к указанной выше формуле можно добавить функцию ЕПУСТО.

Получение первого непустого значения в столбце или строке с игнорированием ошибок
Получение первого непустого значения в столбце или строке с учётом ошибок

Получение первого непустого значения в столбце или строке с игнорированием ошибок

Чтобы получить первое непустое значение в списке, как показано выше, игнорируя ошибки, используйте функцию ИНДЕКС внутри формулы ПОИСКПОЗ: «INDEX((range0),0)» — она поможет найти непустые ячейки. Затем примените функцию ПОИСКПОЗ, чтобы определить позицию первой непустой ячейки, и передайте эту позицию во внешнюю функцию ИНДЕКС для получения значения.

Общий синтаксис

=INDEX(range,MATCH(TRUE,INDEX((range<,>,0),0),0))

Чтобы получить первое непустое значение в списке с игнорированием ошибок, скопируйте или введите приведённую ниже формулу в ячейку E4и нажмите клавишу Enter, чтобы получить результат:

=ИНДЕКС()B4:B15;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(()))B4:B150);0);0))

Пояснение формулы

=INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<,>,0),0),0))

• INDEX((B4:B150),0): Этот фрагмент формулы проверяет каждое значение в диапазоне B4:B15. Если ячейка пуста — возвращается ЛОЖЬ; если содержит ошибку — возвращается сама ошибка; если же ячейка содержит число или текст — результатом будет ИСТИНА. Поскольку аргумент row_num функции ИНДЕКС здесь равен 0, фрагмент возвращает массив значений для всего столбца следующим образом: {ЛОЖЬ;#ССЫЛ!;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА}.
• ПОИСКПОЗ(ИСТИНА;)INDEX((B4:B150),0);0)=ПОИСКПОЗ(ИСТИНА;){ЛОЖЬ;#ССЫЛ!;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА};0): Параметр тип_сопоставления 0 заставляет функцию ПОИСКПОЗ вернуть позицию первого точного совпадения со значением ИСТИНА в массиве. Таким образом, функция вернёт значение 3.
• ИНДЕКС(B4:B15;)ПОИСКПОЗ(ИСТИНА;)INDEX((B4:B150),0);0)) = ИНДЕКС(B4:B15;3): Затем функция ИНДЕКС возвращает 3-е значение из диапазона B4:B15, то есть extendoffice.

Получение первого непустого значения в столбце или строке с учётом ошибок

Чтобы получить первое непустое значение в списке, даже если в нём есть ошибки, просто используйте функцию ЕПУСТО для проверки ячеек на пустоту. Затем функция ИНДЕКС вернёт первое непустое значение по позиции, определённой с помощью функции ПОИСКПОЗ.

Общий синтаксис

=INDEX(range,MATCH(FALSE,ISBLANK(range),0))

√ Примечание: Это формула массива, которую необходимо вводить с помощью сочетания клавиш Ctrl + Shift + Enter, за исключением Excel 365 и Excel 2021.

Чтобы получить первое непустое значение в списке с учётом ошибок, скопируйте или введите приведённую ниже формулу в ячейку E7 и нажмите Ctrl + Shift + Enter, чтобы получить результат:

=ИНДЕКС()B4:B15;ПОИСКПОЗ(ЛОЖЬ;ЕПУСТО())B4:B15);0))

Пояснение формулы

=INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

• ISBLANK(B4:B15):Функция ЕПУСТО проверяет, пусты ли ячейки в диапазоне B4:B15. Если да — возвращается ИСТИНА, если нет — ЛОЖЬ. В результате функция формирует массив следующего вида:{ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ}.
• ПОИСКПОЗ(ЛОЖЬ;)ISBLANK(B4:B15);0)=ПОИСКПОЗ(ЛОЖЬ;){ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ};0): Параметр тип_сопоставления 0 заставляет функцию ПОИСКПОЗ вернуть позицию первого вхождения точного значения ЛОЖЬ в массиве. Таким образом, функция вернёт значение 2.
• ИНДЕКС(B4:B15;)ПОИСКПОЗ(ЛОЖЬ;)ISBLANK(B4:B15);0)) = ИНДЕКС(B4:B15;2): Затем функция ИНДЕКС возвращает 2-е значение в диапазоне B4:B15, то есть #ССЫЛ!.

Связанные функции

Связанные формулы