Перейти к содержимому

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

Это глава из книги Билла Джелена Гуру 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)).

Источник.

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

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

  2. Это всё конечно здорово, но как применить это к PIVOT таблице кто-нибудь знает?

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

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

  4. Выражаю огромную благодарность за идею!

  5. Наталья

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

  6. =ИНДЕКС(C1:K1;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕОШИБКА(1/C1:K1);1;0);0))

  7. Наталья

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

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

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

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

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

  12. Наталья

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

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

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

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

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

  17. Виталий

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

  18. Виталий

    С этим разобрался.
    Другой вопрос: возможно ли на тех же принципах =ПОИСКПОЗ(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?

  19. Евгений

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

  20. Сергей Багузин

    Евгений, пришлите файл на s_bag@mail.ru с описанием, что требуется сделать.

  21. Огромное спасибо! не только задачу решил, но и открыл глаза на новый способ рамоты с формулами, аргументы которых массивы! В частности Поискпоз!

  22. Не работает эта формула, если последнее значение равно «0». Для данной формулы пустая ячейка и значение «0» это одно и тоже

  23. Ребята, сейчас вообще все просто.

    =XMATCH(FALSE;ISBLANK(A1:H1);0;1) — Первый непустой в строке A1:H1

    =XMATCH(FALSE;ISBLANK(A1:H1);0;-1) — Последний непустой в строке A1:H1

  24. Сергей Багузин

    Действительно, в версии Excel 365 появилась новая функция ПОИСКПОЗX(Искомое_значение;Просматриваемый_массив;Режим_сопоставления;Режим_поиска)
    Так что поиск первого непустого значения в строке может выполнить формула:
    =ПОИСКПОЗX(ЛОЖЬ;ЕПУСТО(A1:H1);0;1),
    здесь Искомое_значение = ЛОЖЬ
    Просматриваемый_массив = A1:H1
    Режим_сопоставления = 0 (точное совпадение)
    Режим_поиска = 1 (поиск с первого до последнего элемента)

  25. Здравствуйте, Сергей
    Формула для поиска 2 значения работает =ИНДЕКС($A2:$I2;1;ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО($A2:$I2);1;0);0)+ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО(ДВССЫЛ("RC"&ПОИСКПОЗ(1;ИНДЕКС(1-ЕПУСТО($A2:$I2);1;0);0)+1&":RC9";ЛОЖЬ));1;0);0))

    А как быть если мне требуется 3 значение, потом в другую ячейку 10 и в следующую 15?

    Я только начал разбираться с excel, пока сложноваты подобные моменты.

  26. Нашел сам для себя решение вот в такой формуле:
    =ИНДЕКС($A$1:$M$1;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$M$1"";СТОЛБЕЦ($A$1:$M$1)-СТОЛБЕЦ($A$1)+1);3))
    где 3, это значение по счету, которое надо найти

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *