Найти первое непустое значение в строке
Задача: требуется формула, которая позволяла найти первое непустое значение в строке, т.е., возвращала бы номер первой непустой ячейки в строке. Предположим, что данные представлены в столбцах С: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
- Советы по Excel
- Функции Excel
- Формулы Excel
- Советы по словам
- Советы по Outlook
Как вернуть первую / последнюю непустую ячейку в строке или столбце?
Когда вы работаете с листом, иногда вам может понадобиться получить первое или последнее непустое значение ячейки строки или столбца. Вам легко получить значение, если сразу несколько данных в строке или столбце, но вернуть данные в длинном столбце или строке будет болезненной задачей. Здесь я могу рассказать вам о некоторых полезных методах решения этой задачи.
Вернуть первую непустую ячейку в строке или столбце с формулой
Чтобы извлечь первую ячейку с данными, примените следующую формулу:
1. Введите следующую формулу в пустую ячейку помимо ваших данных: =INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0)) , см. снимок экрана:
2, Затем нажмите Enter key, и вы получите первое непустое значение ячейки следующим образом:
Ноты:
1. В приведенной выше формуле A1: A13 — это диапазон столбцов, который вы хотите использовать, вы можете изменить его по своему усмотрению. Эта формула также может применяться для получения первого непустого значения ячейки в строке, вам просто нужно изменить диапазон столбцов на диапазон строк.
2. Эта формула правильно работает с одной строкой или одним столбцом.
Вернуть последнюю непустую ячейку в строке или столбце с формулой
Если вы хотите вернуть последнее непустое значение ячейки, вот еще одна формула, которая может оказать вам услугу.
1. Введите эту формулу =LOOKUP(2,1/(A1:A13<>""),A1:A13) в пустую ячейку помимо ваших данных, см. снимок экрана:
2, Затем нажмите Enter ключ, последнее непустое значение ячейки будет извлечено сразу. Смотрите скриншот:
Ноты:
1. В приведенной выше формуле A1: A13 — это диапазон столбцов, который вы хотите использовать, вы можете изменить его по своему усмотрению. Эта формула также может применяться для получения последнего непустого значения ячейки в строке, вам просто нужно изменить диапазон столбцов на диапазон строк.
2. Приведенная выше формула правильно работает с одной строкой или одним столбцом.