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

Поиск первой НЕ пустой ячейки

как должна выглядеть формула в ячейке С2, чтобы она осуществляла поиск значения ячейки A1 в первом столбце таблицы и от него справа искала первую НЕ пустую ячейку и собственно ее возвращала.

Вложения

Лист Excel — копия.xls (20.0 Кб, 161 просмотров)

Поиск вверх первой пустой ячейки в столбце
Добрый день! Есть файл с двумя листами. На листе один задан один столбец — список артикулов, к.

Поиск первой пустой ячейки строки и вставка
Привет, друзья. Вчера макрос нужный написал и не сохранил. Теперь пытаюсь вспомнить. Помогите.

Поиск пустой/непустой ячейки в меняющемся диапазоне
Добрый день, В формуле надо, чтобы ячейка в столбце B проверяла соседнюю ячейку из столбца A и.

Поиск первой пустой ячейки в столбце А
Помогите, пожалуйста, новичку. Есть таблица, в которую постоянно вручную добавляются заполненные.

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

Функция =ПЕРВОЕВСТРОКЕ(ЯЧЕЙКА) имеет один аргумент.

ЯЧЕЙКА — Ссылка на ячейку в строке которой необходимо найти первое непустое значение.

Ниже приведен пример работы данной формулы.

Пример

Определение значения 1-й непустой ячейки в строке.

Код на VBA

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

Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С:K (рис. 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. Формула находит первую непустую ячейку в каждой строке и возвращает значение этой ячейки

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

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

Как формулой в столбце Excel найти номер строки с первой пустой ячейкой?

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

Если ячейка пустая, то ЕПУСТО вернёт значение ИСТИНА

=ПОИСКПОЗ(ИСТИНА; ЕПУСТО(A:A); 0)

завершаем ввод нажатием комбинации клавиш CTRL+SHIFT+ENTER и Ехсеlавтоматически окружает эту формулу фигурными скобками, что означает что формула будет работать режиме формулы массива.