Поиск последнего значения в строке или столбце функцией ПРОСМОТР

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

Поиск последней непустой ячейки в строке или столбце Excel

Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.

Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.

Универсальным решением будет использование функции ПРОСМОТР (LOOKUP):

Поиск последней непустой ячейки функцией ПРОСМОТР

У этой функции хитрая логика:

  • Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
  • Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
  • Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.

Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*1011 или сто миллиардов).

Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":

Поиск последнего текстового значения в строке

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

  1. латиница прописные (A-Z)
  2. латиница строчные (a-z)
  3. кириллица прописные (А-Я)
  4. кириллица строчные (а-я)

Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.

Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".

Ссылки по теме



MCH
13.06.2016 22:38:19
Если в таблице с числами вместо пустых значений указаны нули, то последнее ненулевое значение можно получить формулой:

=ПРОСМОТР(2;1/(B2:M2<>0);B2:M2)
14.06.2016 08:43:13
Спасибо, Михаил! Ваши комментарии, как всегда, в точку :)
21.04.2017 16:39:55
Николай, спасибо за идею!

Сразу возникаем мысль, а как найти значение первой непустой ячейки через ПРОСМОТР?

...Логично нужно перевернуть массив, например, так:

Для чисел:
=ПРОСМОТР(9999999;ИНДЕКС(B2:M2;Ч(ИНДЕКС(ЧИСЛСТОЛБ(B2:M2)+1-СТОЛБЕЦ(B2:M2);))))
Для текста:
=ПРОСМОТР("яяяяя";ИНДЕКС(B2:M2;Ч(ИНДЕКС(ЧИСЛСТОЛБ(B2:M2)+1-СТОЛБЕЦ(B2:M2);))))
09.09.2016 12:04:04
Поясните, пожалуйста, смысл параметра 1/(B2:M2<>0).  
17.09.2016 15:14:08
Это выражение равняется либо единице, либо ошибке деления на ноль, – а функция "просмотр" ищет двойку (из первого параметра), пока не закончатся все единицы (во втором параметре). На последней найденной единице она выдаёт значение (из третьего параметра).
17.09.2016 18:06:51
Спасибо за отклик. Я, похоже, вопрос криво сформулировал, попробую еще раз. Второй параметр функции ПРОСМОТР это просматриваемый вектор, тогда как в моем понимании, выражение 1/(B2:M2<>0) - это результат деления единицы на булево выражение, причем проверяемое только в B2 и дающее в результате True|False, но никак не вектор.  Вот этого и не понимаю, а оно работает.  
24.09.2016 08:07:32
В подобных функциях оно работает итеративно – для каждой строки берётся соответствующее по порядку значение из диапазона B2:M2. Прояснилось?

Для проверки можете убедиться, что диапазон считается вектором: напишите формулу =1/(B2:M2<>0) и нажмите Ctrl-Shift-Enter, а затем растяните формулу вниз.
20.06.2018 10:40:14
Булево выражение - это тоже число. TRUE=1, FALSE=0
24.02.2017 18:33:39
МСН, спасибо Вам огромнейшее!!!!!!! формула просто чудо!!!!! правда, даже после объяснений не до конца понимаю ее работу :D
до этого я в макросе перебором делала - а так намного проще и удобней!!!

а не подскажете как ее интерпретировать если значения меньше 1 (дробное) - на досуге, когда у Вас время будет....
спасибо еще раз! уличная магия какая-то но работает круто!! :) :) :)
03.08.2016 12:50:28
Спасибо. Помогло!!!;)
05.08.2016 11:12:05
А что делать, если в просматриваемой строке стоят даты? И нам, соответственно, нужно вытащить последнюю дату?
05.08.2016 12:44:12
Я поняла. Для дат это тоже работает. Просто не стоит указывать слишком большой диапазон ячеек - не более 1млн.
13.03.2017 13:40:35
если указаны одинаковые значения, как найти номер столбца последнего ?  
25.05.2017 12:06:28
Можно использовать формулу вида
=INDEX($B$1:$M$1, 1, MATCH(9E+307, B2:M2, 1))
=ИНДЕКС($B$1:$M$1; 1; ПОИСКПОЗ(9E+307; B2:M2; 1))

Как работает:
В функции ПОИСКПОЗ мы ищем число в диапозоне B2:M2 которое больше (т.е. последний параметер функции = 1), чем оооочень большое число 9Е+307 (самое большое число, которое поддерживает эксель)

Такого числа, понятное дело, нет, поэтому поиск останавливается на самом последнем числе диапазона перед пустыми ячейками.

Если же между заполненными ячейками нет пустых значений, то можно использовать функцию СЧЁТЗ() :
=ИНДЕКС($B$1:$M$1; 1; СЧЁТЗ(B2:M2))
(в примере в статье этот метод не сработает)
08.09.2017 14:24:53
Как настроить функцию чтоб показывала результат отличный от 0 ?
Напр:
78
98
0
65
32
0
0
0

надо чтоб ответ был 32
21.09.2017 09:48:06
Добрый день.
Работает ли этот метод для дат в столбце. Вроде всё ввожу правильно, а значение всё равно некорректное  
05.04.2018 11:10:55
Здравствуйте!
А как в отдельную ячейку вывести список месяцев в котором, в котором были сделаны записи?
Например, для первой задачи в каких месяцах отмечалась активность Самарского филиала.  
Наверх