Поиск последнего вхождения (инвертированный ВПР)

Все классические функции поиска и подстановки типа ВПР (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).

С их помощью этой "убойной парочки" наша задача решается весьма изящно:

Обратный поиск на динамических массивах в Excel

Здесь:

  • Сначала функция ФИЛЬТР (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). Назвать новый столбец можно как угодно - например Подробности.

Группировка строк в Power Query

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

Результаты группировки

5. Добавляем новый вычисляемый столбец кнопкой Настраиваемый столбец на вкладке Добавить столбец (Add column - Add custom column) и вводим следующую формулу:

Добавляем вычисляемый столбец для извлечения 1 строки

Здесь Подробности - это столбец, откуда мы берем таблицы по клиентам, а {0} - это номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями (Record), где каждая запись - первая строка из каждой таблицы:

Полученные записи

Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в шапке столбца Последняя сделка, выбрав нужные столбцы:

Разворачиваем записи в столбцы

... и удалить потом ненужный более столбец Подробности щёлкнув по его заголовку правой кнопкой мыши - Удалить столбцы (Remove columns).

После выгрузки результатов на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to...) получим вот такую симпатичную таблицу со списком последних сделок, как и хотели:

Результаты запроса Power Query

При изменении исходных данных результаты нужно не забыть обновить, щёлкнув по ним правой кнопкой мыши - команда Обновить (Refresh) или сочетанием клавиш Ctrl+Alt+F5.

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



16.02.2021 14:12:12
Добрый день, Николай. Обнаружил, что в таблице ДМ с датой если была только одна сделка то не тянет ее данные, а показывает #ЗНАЧ... Как это исправить?
16.02.2021 18:05:37
Доброго дня, Николай! Спасибо за очередную отличную статью, и добавлю немножко отсебятинки.
В общем мы тут на форуме немножко пообщались и пришли к выводу, что сортировка до группировки это не факт что всегда полезно. Лучше всего сортировки и поиски мин/максов делать сразу внутри группировки ибо результат может потом удивить (не всегда, но может).
Поэтому предлагаю вот такой вариант поиска последнего совпадения, он от всяких неожиданностей застрахован - проверено.
let
    Source = Excel.CurrentWorkbook(){[Name="Поставки"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Код заказа", type text}, {"Дата покупки", type datetime}, {"Клиент", type text}, {"Сумма", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Клиент"}, {{"таб", each _, type table [Код заказа=nullable text, Дата покупки=nullable datetime, Клиент=nullable text, Сумма=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "таб2", each Table.Max([таб], "Дата покупки" )),
    таб2 = Table.FromRecords( #"Added Custom"[таб2], type table [Код заказа=nullable text, Дата покупки=nullable datetime, Клиент=nullable text, Сумма=nullable number] )
in
    таб2
17.02.2021 11:24:56
В копилку к варианту с функцией ПРОСМОТР, вариант с классическими функциями 8-)

{=ИНДЕКС($A$2:$A$24;ПОИСКПОЗ(2;1/($G5=$B$2:$B$24);1))}
27.02.2021 08:21:05
Здравствуйте, Николай!
С интересом посмотрел этот трюк по обратному поиску нужной строки.
Единственное, что смутило, а зачем себя загонять в рамки поиска только последнего значения?
Позволю себе дать небольшой совет.
Можно найти любое 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))
03.03.2021 21:57:50
спасибо, очень помогло!
Наверх