Поиск последнего вхождения (инвертированный ВПР)
Все классические функции поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP), ПОИСКПОЗ (MATCH) и им подобные имеют одну важную особенность - они ищут от начала к концу, т.е. слева-направо или сверху-вниз по исходным данным. Как только находится первое подходящее совпадение - поиск останавливается и найденным оказывается только первое вхождение нужного нам элемента.
Что же делать, если нам требуется найти не первое, а последнее вхождение? Например, последнюю сделку по клиенту, последний платёж, самую свежую заявку и т.д.?
Способ 1. Поиск последней строки формулой массива
Если в исходной таблице нет столбца с датой или порядковым номером строки (заказа, платежа...), то наша задача сводится, по сути, к поиску последней строки, удовлетворяющей заданному условию. Реализовать подобное можно вот такой формулой массива:
Здесь:
- Функция ЕСЛИ (IF) проверяет по очереди все ячейки в столбце Клиент и выводит номер строки, если в ней лежит нужное нам имя. Номер строки на листе нам даёт функция СТРОКА (ROW), но поскольку нам нужен номер строки в таблице, то дополнительно приходится вычитать 1, т.к. у нас в таблице есть шапка.
- Затем функция МАКС (MAX) выбирает из сформированного набора номеров строк максимальное значение, т.е. номер самой последней строки клиента.
- Функция ИНДЕКС (INDEX) выдаёт содержимое ячейки с найденным последним номером из любого другого требуемого столбца таблицы (Код заказа).
Всё это нужно вводить как формулу массива, т.е.:
- В Office 365 с последними установленными обновлениями и поддержкой динамических массивов - можно просто жать Enter.
- Во всех остальных версиях после ввода формулы придется нажимать сочетание клавиш Ctrl+Shift+Enter, что автоматически добавит к ней фигурные скобки в строке формул.
Способ 2. Обратный поиск новой функцией ПРОСМОТРХ
Я уже писал большую статью с видео про новую функцию ПРОСМОТРХ (XLOOKUP), которая появилась в последних версиях Office на замену старушке ВПР (VLOOKUP). При помощи ПРОСМОТРХ наша задача решается совершенно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задавать направление поиска: сверху-вниз или снизу-вверх - за это отвечает её последний аргумент (-1):
Способ 3. Поиск строки с последней датой
Если в исходных данных у нас есть столбец с порядковым номером или датой, играющей аналогичную роль, то задача видоизменяется - нам требуется найти уже не последнюю (самую нижнюю) строку с совпадением, а строку с самой поздней (максимальной) датой.
Как это сделать с помощью классических функций я уже подробно разбирал, а теперь давайте попробуем использовать мощь новых функций динамических массивов. Исходную таблицу для пущей красоты и удобства тоже заранее преобразуем в "умную" с помощью сочетания клавиш Ctrl+T или команды Главная - Форматировать как таблицу (Home - Format as Table).
С их помощью этой "убойной парочки" наша задача решается весьма изящно:
Здесь:
- Сначала функция ФИЛЬТР (FILTER) отбирает только те строки из нашей таблицы, где в столбце Клиент - нужное нам имя.
- Потом функция СОРТ (SORT) сортирует отобранные строки по убыванию даты, чтобы самая последняя сделка оказалась сверху.
- Функция ИНДЕКС (INDEX) извлекает первую строку, т.е. выдает нужную нам последнюю сделку.
- И, наконец, внешняя функция ФИЛЬТР убирает из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант {0;1;0;1}, определяющий какие именно столбцы мы хотим (1) или не хотим (0) выводить.
Способ 4. Поиск последнего совпадения в Power Query
Ну, и для полноты картины, давайте рассмотрим вариант решения нашей задачи обратного поиска с помощью надстройки Power Query. С её помощью всё решается очень быстро и красиво.
1. Преобразуем нашу исходную таблицу в "умную" с помощью сочетания клавиш Ctrl+T или команды Главная - Форматировать как таблицу (Home - Format as Table).
2. Загружаем её в Power Query кнопкой Из таблицы/диапазона на вкладке Данные (Data - From Table/Range).
3. Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние сделки оказались сверху.
4. На вкладке Преобразование выбираем команду Группировать по (Transform - Group By) и задаем группировку по клиентам, а в качестве агрегирующей функции выбираем вариант Все строки (All rows). Назвать новый столбец можно как угодно - например Подробности.
После группировки получим список уникальных имен наших клиентов и в столбце Подробности - таблицы со всеми сделками каждого из них, где первой строкой будет идти самая последняя сделка, которая нам и нужна:
5. Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column - Add custom column) и вводим следующую формулу:
Здесь Подробности - это столбец, откуда мы берем таблицы по клиентам, а {0} - это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями (Record), где каждая запись - первая строка из каждой таблицы:
Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в шапке столбца Последняя сделка, выбрав нужные столбцы:
... и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши - Удалить столбцы (Remove columns).
После выгрузки результатов на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to...) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:
При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши - команда Обновить (Refresh) или сочетанием клавиш Ctrl+Alt+F5.
Ссылки по теме
- Функция ПРОСМОТРХ - наследник ВПР
- Как использовать новые функции динамических массивов СОРТ, ФИЛЬТР и УНИК
- Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР
В общем
Поэтому предлагаю вот такой вариант поиска последнего совпадения, он от всяких неожиданностей застрахован - проверено.
{=ИНДЕКС($A$2:$A$24;ПОИСКПОЗ(2;1/($G5=$B$2:$B$24);1))}
С интересом посмотрел этот трюк по обратному поиску нужной строки.
Единственное, что смутило, а зачем себя загонять в рамки поиска только последнего значения?
Позволю себе дать небольшой совет.
Можно найти любое n-вхождение искомого текста в нужной колонке (2,3,4 ... 1 с конца, 2 с конца, и т.д.)
В вашем Способе 1 нужно заменить Функцию МАКС на НАИБОЛЬШИЙ (если будем искать n-вхождение с конца: последний, предпоследний и т.д.) или НАИМЕНЬШИЙ (если хотим искать с начала 1, 2, 3 и т.д.)
По Артуру поиск 2 позиции с конца будет выглядеть так:
=ИНДЕКС($A$2:$A$24;НАИБОЛЬШИЙ(ЕСЛИ($B$2:$B$24=$G5;СТРОКА($B$2:$B$24)-1);2))
Если можно, то у меня к Вам вопрос-просьба: не могли бы Вы в очередной обновке Plex (если, конечно же, Вы планируете обновление) написать/встроить функцию ПросмотрХ - ну уж очень хочется. Умел бы сам писать udf или (как там правильно?) пользовательские функции давно бы написал, но "не цыган я, не цыган"©:D
Пришлось допилить формулу напильником немного, при дописке "-1" формула показывала не последнее, а предпоследнее значение,при "-2" пред-пред последнее. Убрал цифру и стало показывать последнее.
Сформировал такой поиск но у меня в итоге в ячейке вот такое значение: #ССЫЛКА!
Это что то пропущено?
Вроде все условия выполнены и при F9 значение можно увидеть до ИНДЕКС, а после ИНДЕКС значение ячейки #ССЫЛКА!
Что то сейчас подумалось, может дело в полях Дата, которые я отбирал? Мне необходимо было отобрать самую последнюю дату счёта, выставленного клиенту. Видимо с датами это всё не работает?
Подскажите, пжл, а как выбрать несколько последних строк.
Скажем если фильтрация идет по столбику суммы, и необходимо отобрать строки, которые составляют необходимую сумму, при этом пограничную сумму необходимо включить в полученной таблицы.
А если ситуация, что надо найти номер столбца по номеру строки и значению под нужным нам столбцом?