Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР
На практике часто возникает необходимость быстро найти значение последней (крайней) непустой ячейки в строке или столбце таблицы. Предположим, для примера, что у нас есть вот такая таблица с данными продаж по нескольким филиалам:
Задача: найти значение продаж в последнем месяце по каждому филиалу, т.е. для Москвы это будет 78, для Питера - 41 и т.д.
Если бы в нашей таблице не было пустых ячеек, то путь к решению был бы очевиден - можно было бы посчитать количество заполненных ячеек в каждой строке и брать потом ячейку с этим номером. Но филиалы работают неравномерно: Москва простаивала в марте и августе, филиал в Тюмени открылся только с апреля и т.д., поэтому такой способ не подойдет.
Универсальным решением будет использование функции ПРОСМОТР (LOOKUP):
У этой функции хитрая логика:
- Она по очереди (слева-направо) перебирает непустые ячейки в диапазоне (B2:M2) и сравнивает каждую из них с искомым значением (9999999).
- Если значение очередной проверяемой ячейки совпало с искомым, то функция останавливает просмотр и выводит содержимое ячейки.
- Если точного совпадения нет и очередное значение меньше искомого, то функция переходит к следующей ячейке в строке.
Легко сообразить, что если в качестве искомого значения задать достаточно большое число, то функция пройдет по всей строке и, в итоге, выдаст содержимое последней проверенной ячейки. Для компактности, можно указать искомое число в экспоненциальном формате, например 1E+11 (1*1011 или сто миллиардов).
Если в таблице не числа, а текст, то идея остается той же, но "очень большое число" нужно заменить на "очень большой текст":
Применительно к тексту, понятие "большой" означает код символа. В любом шрифте символы идут в следующем порядке возрастания кодов:
- латиница прописные (A-Z)
- латиница строчные (a-z)
- кириллица прописные (А-Я)
-
кириллица строчные (а-я)
Поэтому строчная "я" оказывается буквой с наибольшим кодом и слово из нескольких подряд "яяяяя" будет, условно, "очень большим словом" - заведомо "большим", чем любое текстовое значение из нашей таблицы.
Вот так. Не совсем очевидное, но красивое и компактное решение. Для поиска последней непустой ячейки в столбцах работает тоже "на ура".
Сразу возникаем мысль, а как найти значение первой непустой ячейки через ПРОСМОТР?
...Логично нужно перевернуть массив, например, так:
Для чисел:
=ПРОСМОТР("яяяяя";ИНДЕКС(B2:M2;Ч(ИНДЕКС(ЧИСЛСТОЛБ(B2:M2)+1-СТОЛБЕЦ(B2:M2);))))Для проверки можете убедиться, что диапазон считается вектором: напишите формулу =1/(B2:M2<>0) и нажмите Ctrl-Shift-Enter, а затем растяните формулу вниз.
до этого я в макросе перебором делала - а так намного проще и удобней!!!
а не подскажете как ее интерпретировать если значения меньше 1 (дробное) - на досуге, когда у Вас время будет....
спасибо еще раз! уличная магия какая-то но работает круто!!
Если не получится применить, задайте вопрос на форуме, приложив пример
=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))
(в примере в статье этот метод не сработает)
Напр:
78
98
0
65
32
0
0
0
надо чтоб ответ был 32
Работает ли этот метод для дат в столбце. Вроде всё ввожу правильно, а значение всё равно некорректное
А как в отдельную ячейку вывести список месяцев в котором, в котором были сделаны записи?
Например, для первой задачи в каких месяцах отмечалась активность Самарского филиала.
Подскажите а как сделать чтобы в результате поиска определялось не последнее значение в ячейке, а последний месяц в котором имеется это значение? т.е. для второй строки результатом поиска должно выдавать "сентябрь" а не "78"
Как найти последнее значение в строке отличного от значения равного 0, значение высчитывается формулой.