Думаю многие, если не большинство, в курсе, что такое ВПР и его неоспоримая сила при поиске и объединении данных из разных таблиц. Те же, кто достиг просветления, используют не менее полезную функцию ИНДЕКС, чтобы не париться, где там идентификатор: слева или справа.
Ниже будет пост о том, каким еще извращенным способом можно надругаться над excel и вытащить данные из другой таблицы по нестандартным условиям без регистрации и смс дополнительных фишек типа VBA и т.п., только штатный функционал excel.
Итак, что мы имеем:
Таблица раз - со списком значений, который надо обогатить данными
В другой таблице имеем данные по статусам, которые могут повторяться, и которые связаны с первой таблицей по идентификатору обращения
Обычная задача для ВПР или ИНДЕКС звучит, как "мне надо добавить данные из одной таблицы в другую по какому-то критерию или критериям", после чего берем критерий первой таблицы и начинаем искать по нему первое совпадающее значение из другой таблицы и возвращаем значение из искомого столбца:
=ВПР(A2;Лист2!$C$1:$C$170;2;0), где A2 - критерий, Лист2!$C$1:$I$13 - диапазон в котором ищем, 2 - номер столбца, из которого возвращаем значение, 0 - тип сопоставлениятип или вот так:
=ИНДЕКС(Лист2!$B$1:$I$170;ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0);3), где - Лист2!$B$1:$I$170 - диапазон, в кокотором ищем, ПОИСКПОЗ(Лист1!A3;Лист2!$C$1:$C$170;0) - строка, которую ищем, 3 - столбец, откуда возвращаем данные, В целом двух этих формул хватит для 90% ситуаций при работе с excel, чтобы подтянуть данные из другой таблицы. Хватит ровно до того момента, пока к нам не присоединится условие с датой, при этом дата будет неизвестной переменной.
Собственно сама задача:
Имеем таблицу с идентификатором, в которую надо вернуть данные из другой таблицы (для моего примера - последний статус и последняя дата статуса) при условии, что запись должна быть последняя из всех записей, связанных с идентификатором
То есть мы оперируем идентификатором и условием, что дата должна быть самая поздняя из возможных записей с совпадающими идентификаторами. Когда меня попросили изобразить это в excel, я сначала начал думать в сторону VBA, так как Googlение проблемы приводило в основном к примерам, как искать записи по нескольким критериям, то есть из исходной таблицы мы берем эти несколько критериев и пытаемся по ним найти соответствующие записи в другой таблицы. Эти варианты не подходили, так как определение последней даты записи должно было происходить также в формуле, и заранее ее значение было неизвестно, ну и плюс она должна быть определена для записей с конкретным идентификатором. VBA тоже не походил, так как человеку надо было отправить просто формулу, чтобы можно было вставить в ячейку.
В итоге с помощью того Googlа и небольшой доработки напильником была рождена формула следующего типа:
{=ИНДЕКС(Лист2!$B$2:$I$170;ПОИСКПОЗ(A4&МАКС(ЕСЛИ(A4=Лист2!$C$2:$C$170;Лист2!$B$2:$B$170;""));Лист2!$C$2:$C$170&Лист2!$B$2:$B$170;);ПОИСКПОЗ("Статус";Лист2!$B$1:$I$1;0))}Вот он - ВПР на максималках) тут использовано все, чтобы вернуть ту искомую запись в таблице: и массивы, и индекс, и несколько критериев, и условие по дате.
Как это работает:
Используем в формуле ИНДЕКС массивы. При вводе формулы используем сочетание клавиш ctrl+shift+enter
Выделяем просматриваемый диапазон, в моем случае от B2 до I170
Искомую строку определяем по формуле ПОИСКПОЗ, при этом поиск осуществляем по двум условиям A4&МАКС (идентификатор A4 исходной таблицы и максимальное значение даты при равном идентификаторе, значение даты берется из функции ЕСЛИ). Тут важно не забыть, что поиск по нескольким критериям можно задать через & перечислив критерии, а также надо через & перечислить диапазоны, в которых excel будет искать эти критерии, в той же последовательности, что и критерии
Искомый столбец оформил тоже через формулу ПОИСКПОЗ, где формула ищет позицию искомого значения "Статус" в просматриваемом диапазоне заголовков второй таблицы и возвращает таким образом номер столбца, из которого нам надо вытащить значение
Таким способом я смог вытянуть необходимо значения строк для обогащения исходной таблицы
Зачем этот пост?
Хотел поделиться в сети информацией для будущих искателей решения схожей проблемы, так как мой ТОП операций и функций excel ctrl+c и ctrl+v :) Но мне не подвернулось готового решения, когда я искал. Может кому-то повезет больше с моей помощью.