Содержание:
Все классические функции поиска и подстановки типов ВПР (ВПР), GPR (Просмотр), БОЛЬШЕ ОТКРЫТОГО (СООТВЕТСТВОВАТЬ) и у подобных им есть одна важная особенность – они ищут от начала до конца, т.е. слева направо или сверху вниз в исходных данных. Как только будет найдено первое совпадение, поиск прекращается и находится только первое вхождение нужного нам элемента.
Что делать, если нам нужно найти не первое, а последнее вхождение? Например, последняя транзакция клиента, последний платеж, самый последний заказ и т. д.?
Метод 1: поиск последней строки с помощью формулы массива
Если в исходной таблице нет столбца с датой или порядкового номера строки (заказ, оплата…), то наша задача, по сути, найти последнюю строку, удовлетворяющую заданному условию. Это можно сделать с помощью следующей формулы массива:
Вот:
- Функция IF (ЕСЛИ) проверяет все ячейки в столбце одну за другой Клиент и выводит номер строки, если она содержит нужное нам имя. Номер строки на листе нам задает функция ЛИНИЯ (РЯД), но так как нам нужен номер строки в таблице, то нам дополнительно придется вычесть 1, потому что у нас в таблице есть заголовок.
- Тогда функция MAX (МАКСИМУМ) выбирает максимальное значение из сформированного набора номеров строк, т.е. номер самой последней строки клиента.
- Функция ИНДЕКС (ПОКАЗАТЕЛЬ) возвращает содержимое ячейки с найденным последним номером из любого другого необходимого столбца таблицы (Код заказа).
Все это необходимо ввести как формула массиват.е.
- В Office 365 с установленными последними обновлениями и поддержкой динамических массивов вы можете просто нажать Enter.
- Во всех остальных версиях после ввода формулы придется нажать сочетание клавиш Ctrl+Shift+Enter, что автоматически добавит к нему фигурные скобки в строке формул.
Способ 2. Обратный поиск с помощью новой функции ПРОСМОТР.
Я уже написал большую статью с видео о новой функции Перейти к товару (XПРОМ), появившийся в последних версиях Office взамен старого ВПР. (ВПР). С помощью ОБЗОРа наша задача решается достаточно элементарно, т.к. для этой функции (в отличие от ВПР) можно явно задать направление поиска: сверху вниз или снизу вверх – за это отвечает ее последний аргумент (-1):
Способ 3. Поиск строки с самой последней датой
Если в исходных данных у нас есть столбец с порядковым номером или датой, играющий аналогичную роль, то задача модифицируется – нам нужно найти не последнюю (самую низкую) строку с совпадением, а строку с самой последней ( максимум) дата.
Я уже подробно обсуждал, как это сделать с помощью классических функций, а теперь давайте попробуем использовать возможности новых функций динамических массивов. Для большей красоты и удобства также преобразуем исходную таблицу в «умную» таблицу с помощью сочетания клавиш. Ctrl+T или команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу).
С их помощью эта «парочка-убийца» очень изящно решает нашу задачу:
Вот:
- Функция первая ФИЛЬТР (ФИЛЬТР) выбирает из нашей таблицы только те строки, где в столбце Клиент – нужное нам имя.
- Тогда функция GRADE (СОРТИРОВАТЬ) сортирует выбранные строки по дате в порядке убывания, причем самая последняя сделка находится вверху.
- Функция ИНДЕКС (ПОКАЗАТЕЛЬ) извлекает первую строку, т.е. возвращает последнюю нужную нам сделку.
- И, наконец, внешняя функция ФИЛЬТР удаляет из результатов лишние 1-й и 3-й столбцы (Код заказа и Клиент) и оставляет только дату и сумму. Для этого используется массив констант. {0;1;0;1}, определяя, какие столбцы мы хотим (1) или не хотим (0) отображать.
Метод 4. Поиск последнего совпадения в Power Query
Что ж, для полноты картины давайте рассмотрим решение нашей проблемы обратного поиска с помощью надстройки Power Query. С ее помощью все решается очень быстро и красиво.
1. Давайте преобразуем нашу исходную таблицу в «умную» с помощью сочетания клавиш. Ctrl+T или команды Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу).
2. Загрузите его в Power Query с помощью кнопки Из таблицы/диапазона таб Данные (Данные — из таблицы/диапазона).
3. Сортируем (через выпадающий список фильтра в шапке) нашу таблицу по убыванию даты, чтобы самые последние транзакции были сверху.
4… Во вкладке трансформация выбрать команду Группа по (Преобразовать — Группировать по) и задайте группировку по клиентам, а в качестве функции агрегирования выберите опцию Все строки (Все строки). Вы можете назвать новый столбец как угодно, например Подробнее.
После группировки мы получим список уникальных имен наших клиентов и в столбце Подробнее — таблицы со всеми транзакциями каждого из них, где первой строкой будет самая последняя транзакция, что нам и нужно:
5. Добавьте новый вычисляемый столбец с помощью кнопки Пользовательский столбец таб Добавить столбец (Добавить столбец — Добавить пользовательский столбец)и введите следующую формулу:
Здесь Подробнее – это столбец, из которого мы берем таблицы по клиентам, и 0 {} — номер строки, которую мы хотим извлечь (нумерация строк в Power Query начинается с нуля). Получаем столбец с записями (Запись), где каждая запись представляет собой первую строку каждой таблицы:
Осталось развернуть содержимое всех записей кнопкой с двойными стрелками в заголовке столбца Последняя сделка выбрав нужные столбцы:
… а затем удалите столбец, который больше не нужен Подробнее щелкнув правой кнопкой мыши по его названию – Удалить столбцы (Удалить столбцы).
После загрузки результатов на лист через На главную — Закрыть и загрузить — Закрыть и загрузить (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…) получим вот такую симпатичную таблицу со списком последних транзакций, как мы и хотели:
При изменении исходных данных необходимо не забыть обновить результаты, щелкнув по ним правой кнопкой мыши – команда Обновить и сохранить (Обновить) или сочетание клавиш Ctrl+другой+F5.
- Функция ПРОСМОТР является потомком ВПР.
- Как использовать новые функции динамических массивов SORT, FILTER и UNIC
- Поиск последней непустой ячейки в строке или столбце с помощью функции ПРОСМОТР