Как найти первую непустую ячейку в строке excel
Перейти к содержимому

Как найти первую непустую ячейку в строке excel

Найти первое непустое значение в строке

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 1).

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Рис. 1. Формула находит первую непустую ячейку в каждой строке и возвращает ее номер в массиве

Скачать заметку в формате Word или pdf, примеры в формате Excel

Решение: формула в А2: =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0). Хотя эта формула имеет дело с массивом ячеек, она в конечном счете возвращает одно значение, так что использовать при вводе нажатие Ctrl+Shift+Enter не требуется (о формулах массива см. Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel).

Рассмотрим работу формулы подробнее. Функция ЕПУСТО возвращает ИСТИНА, если ячейка является пустой, и ЛОЖЬ, если ячейка – не пустая. Посмотрите на строку данных в С2:К2. ЕПУСТО(С2:К2) возвратит массив: <ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА>.

Обратите внимание, что далее этот массив вычитается из 1. При попытке использовать значения ИСТИНА и ЛОЖЬ в математической формуле, значение ИСТИНА интерпретируется как 1, а значение ЛОЖЬ – как 0. Задавая 1-ЕПУСТО(С2:К2), вы преобразуете массив логических значений ИСТИНА/ЛОЖЬ в числовую последовательность нулей и единиц: <0;0;1;0;0;0;0;0;0>.

Итак, фрагмент формулы 1-ЕПУСТО(С2:К2) возвращает массив <0;0;1;0;0;0;0;0;0>. Это немного странно, так как от такого фрагмента Excel ожидает, что вернется одно значение. Странно, но не смертельно. Функция ИНДЕКС также обычно возвращает одно значение. Но вот, что написано в Справке Excel: Если указать в качестве аргумента номер_строки или номер_столбца значение 0 (ноль), функция ИНДЕКС возвратит массив значений для целого столбца или целой строки соответственно. Чтобы использовать значения, возвращенные как массив, введите функцию ИНДЕКС как формулу массива в горизонтальный диапазон ячеек для строки и в вертикальный — для столбца.

Если функция ИНДЕКС возвращает массив, ее можно использовать внутри других функций, ожидающих, что аргумент является массивом.

Итак, указав в качестве третьего аргумента функции ИНДЕКС(1-ЕПУСТО(C2:K2);1;0) значение ноль, мы получим массив <0;0;1;0;0;0;0;0;0>.

Функция ПОИСКПОЗ выполняет поиск искомого значения в одномерном массиве и возвращает относительную позицию первого найденного совпадения. Формула =ПОИСКПОЗ(1,МАССИВ,0) просит Excel найти номер ячейки в МАССИВЕ, которая содержит первую встретившуюся единицу. Функция ПОИСКПОЗ определяет в каком столбце содержится первая непустая ячейка. Когда вы просите ПОИСКПОЗ найти первую 1 в массиве <0;0;1;0;0;0;0;0;0>, она возвращает 3.

Итак =ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0) превращается в =ПОИСКПОЗ(1;<0;0;1;0;0;0;0;0;0>;0) и возвращает результат 3.

В этот момент, вы знаете, что третий столбец строки С2:К2 содержит первое непустое значение. Отсюда довольно просто, используя функцию ИНДЕКС, узнать само это первое непустое значение: =ИНДЕКС(МАССИВ;1;3) или =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0)).

Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

Рис. 2. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

Дополнительные сведения: если все ячейки пустые, то формула возвращает ошибку #Н/Д.

Альтернативные стратегии: когда вы из единицы вычитаете значение ЕПУСТО, вы преобразуете логические значения ИСТИНА/ЛОЖЬ в числовые 1/0. Вы могли бы пропустить этот шаг, но тогда вам придется искать ЛОЖЬ в качестве первого аргумента функция ПОИСКПОЗ: =ИНДЕКС(C2:K2;1;ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)).

Как найти первую непустую ячейку в строке excel

  • Советы по Excel
  • Функции Excel
  • Формулы Excel
  • Советы по словам
  • Советы по Outlook

Как вернуть первую / последнюю непустую ячейку в строке или столбце?

Когда вы работаете с листом, иногда вам может понадобиться получить первое или последнее непустое значение ячейки строки или столбца. Вам легко получить значение, если сразу несколько данных в строке или столбце, но вернуть данные в длинном столбце или строке будет болезненной задачей. Здесь я могу рассказать вам о некоторых полезных методах решения этой задачи.

Вернуть первую непустую ячейку в строке или столбце с формулой

Чтобы извлечь первую ячейку с данными, примените следующую формулу:

1. Введите следующую формулу в пустую ячейку помимо ваших данных: =INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0)) , см. снимок экрана:

doc-return-first-data-cell-1

2, Затем нажмите Enter key, и вы получите первое непустое значение ячейки следующим образом:

doc-return-first-data-cell-1

Ноты:

1. В приведенной выше формуле A1: A13 — это диапазон столбцов, который вы хотите использовать, вы можете изменить его по своему усмотрению. Эта формула также может применяться для получения первого непустого значения ячейки в строке, вам просто нужно изменить диапазон столбцов на диапазон строк.

2. Эта формула правильно работает с одной строкой или одним столбцом.

Вернуть последнюю непустую ячейку в строке или столбце с формулой

Если вы хотите вернуть последнее непустое значение ячейки, вот еще одна формула, которая может оказать вам услугу.

1. Введите эту формулу =LOOKUP(2,1/(A1:A13<>""),A1:A13) в пустую ячейку помимо ваших данных, см. снимок экрана:

doc-return-first-data-cell-1

2, Затем нажмите Enter ключ, последнее непустое значение ячейки будет извлечено сразу. Смотрите скриншот:

doc-return-first-data-cell-1

Ноты:

1. В приведенной выше формуле A1: A13 — это диапазон столбцов, который вы хотите использовать, вы можете изменить его по своему усмотрению. Эта формула также может применяться для получения последнего непустого значения ячейки в строке, вам просто нужно изменить диапазон столбцов на диапазон строк.

2. Приведенная выше формула правильно работает с одной строкой или одним столбцом.

Добавить комментарий

Ваш адрес email не будет опубликован.