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

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft 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)).

Результат: 3

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

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

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

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

Источник.

24 комментария для “Найти первое непустое значение в строке”

  1. Спасибо за статью. Но история будет неполной без формулы для поиска значения последнего непустого элемента списка. Например, в А1 формула: =ПРОСМОТР(2;1/(C1:K1);C1:K1), где C1:K1 — диапазон поиска.

  2. Спасибо большое! Отличная идея, здорово выручили)

    Кстати, можно и не преобразовывать массив в числовую последовательность: =ПОИСКПОЗ(ЛОЖЬ;ИНДЕКС(ЕПУСТО(C2:K2);1;0);0)

  3. Наталья

    Добрый день. А подскажите, какая будет формула если файле будет не пусто, а 0? и нужно к ячейке А2 найти первую цифру больше 0

      1. Наталья

        Добрый день. У меня была вот такая {=ИНДЕКС(C19:K19;1;ПОИСКПОЗ(1; --(C19:K19 0);0))}, не удобно… Большое вам спасибо за формулу.

      2. Скажите, а как трансофрмировать эту формулу,если я ищу первое не нулевое значение, но не в строке, как в примере, а в столбце? Тобишь мне надо выдать значение первой строчки в столбце, в которой значение не нулевое.

        1. Дарья, если ваши данные расположены в диапазоне А1:А20, то формула будет =ИНДЕКС(A1:A20;ПОИСКПОЗ(1; ИНДЕКС(1-ЕОШИБКА(1/A1:A20);;1);0))

  4. Скажите пожалуйста, какая должна быть формула, что бы в ячейке было текстовое значение первой не пустой ячейки

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

  5. Наталья

    Добрый день! Данные формулы ищут первые не пустые значения в диапазоне. Но как быть с последующими значениями. В диапазоне (A1:A200) есть не пустые ячейки. Первая такая ячейка находится например в А5 (формула ее находит и возвращает данное значение), следующая не пустая ячейка через неопределенное количество ячеек например А16 (т.к. таблица меняется каждый день). И так по всему диапазону. Получается данная формула проставляет по строкам данные первой не пустой ячейки до последующей новой не пустой ячейки. И так далее, т.е. значение А5 повторяется пока не появится новое значение из ячейки А16. А А16 будет повторяться до последующего нового значения не пустой ячейки. Подскажите выход из данной ситуации.

  6. Добрый день.
    Все замечательно работает!
    А как формулой найти второе (третье) не пустое значение в столбце?

  7. Ксения, это нетривиальная задача. Сделал пример для поиска второго вхождения. Формула сначала ищет первое вхождение, затем ограничивает область поиска, и опять ищет первое вхождение в новой области.

  8. ОЙ, у меня эксель 2003……
    Я попыталась массивом по формуле
    =ИНДЕКС(A1:A22;НАИМЕНЬШИЙ(ЕСЛИ(A1:A22""; СТРОКА(A1:A22));3))
    но не работает……

  9. {=ИНДЕКС(A1:A22;НАИМЕНЬШИЙ(ЕСЛИ(A1:A22""; СТРОКА(A1:A22));3))}
    по такой формуле вроде должна искать 3-е значение, но если ячейки A1:A22 из формул, то не работает….

  10. Виталий

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

    1. Виталий

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

  11. Евгений

    Эта формула у меня не работает: ИНДЕКС(C2:K2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(C2:K2);1;0);0))

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

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