Поиск и подстановка с учётом периода
Постановка задачи
Поиск и подстановка данных из одной таблицы в другую по совпадению ключевых значений в общих столбцах - это классическая задача, с которой сталкивается любой пользователь Microsoft Excel. Решить её можно множеством разных способов: функциями ВПР (VLOOKUP) или ПРОСМОТРХ (XLOOKUP), с помощью Power Query и т.д.
Однако часто возникает ситуация, когда данные, которые нужно подтянуть из справочника, меняются со временем. Например, необходимо подтянуть цены в продажи из прайс-листа, но эти цены действуют только в каком-то определённом периоде дат:
Как легко сообразить, эта задача, на самом деле, не только про цены, а про множество других подобных ситуаций, когда значения, которые нам нужно подтягивать из справочника меняются со временем. Например:
- в клиентской базе у людей иногда меняются контактные данные
- служебный автомобиль может быть закреплён сначала за одним сотрудником, а потом - за другим, третьим...
- в разные периоды времени могут действовать различные рекламные акции и скидки
- и т.д.
Требования к справочнику
Прежде, чем начинать, стоит явно озвучить требования к справочнику, откуда мы тянем данные. Чтобы все описанные далее способы успешно работали, для него должны выполняться следующие условия:
- в пределах одной категории (товара) временные интервалы не должны пересекаться (иначе будет конфликт цен)
- в нём должен быть столбец с уникальным номером каждой строки (будет нужен нам далее)
- пустые ячейки в столбце Финиш для цен, которые действуют в данный момент, нужно заполнить текущей датой - например, с помощью автоматически обновляющейся функции СЕГОДНЯ (TODAY).
Способ 1. Подстановка чисел функцией СУММЕСЛИМН
Если данные, которые нам нужно подтянуть из справочника представляют из себя числа (как в случае с ценами), то наша задача решается функцией СУММЕСЛИМН (SUMIFS) с тремя условиями:
- товар совпадает с проданным,
- дата продажи после даты начала,
- дата продажи до даты конца
Поскольку под все три условия в нашем прайс-листе попадает только одна строка - с нужным товаром в нужном интервале дат, то функция, по факту, ничего не суммирует, а просто выводит единственное числовое значение цены, которое нам и нужно.
Способ 2. Подстановка текста
Если же из справочника необходимо извлечь не числовые, а текстовые данные (например, страну поставщика), то формула чуть-чуть усложняется - можно сначала предыдущей формулой найти порядковый номер строки (столбец ID), который нам нужен, а уже потом с помощью функции ИНДЕКС (INDEX) вытащить из столбца Поставщик требуемое по порядку значение.
Если, для разнообразия, использовать динамические "умные" таблицы (с именами Продажи и Прайс) вместо обычных диапазонов, то такая формула будет выглядеть как:
Способ 3. Новая функция ФИЛЬТР
Если вы используете версию Excel 2021 или новее, то можно решить задачу чуть более изящно - использовать новую функцию ФИЛЬТР (FILTER) с теми же тремя условиями: название товара совпадает с исходным, дата продажи попадает между стартом и финишем. Чтобы все эти три условия работали одновременно (т.е. в связке AND между собой), их нужно перемножить во втором аргументе функции ФИЛЬТР:
Способ 4. Power Query
Принципиально другой способ решения нашей задачи с подтягиванием данных из медленно меняющегося измерения - это использовать объединение запросов Power Query. Алгоритм действий будет следующим:
- Конвертируем по очереди обе таблицы в динамические "умные" сочетанием клавиш Ctrl+T или используя команду Главная - Форматировать как таблицу (Home - Format as Table)
- Загружаем их в Power Query командой Данные - Из таблицы/диапазона (Data - From Table/Range) и сохраняем как подключения через Главная - Закрыть и загрузить - Закрыть и загрузить в - Только создать подключение (Home - Close&Load - Close&Load to - Only create connection). Если лень делать это всё вручную, то можно воспользоваться готовым макросом из моей надстройки PLEX.
- Дополнительно в запросе, который загружает прайс заменяем пустые ячейки в столбце Финиш на любую дату, щёлкая по шапке столбца правой кнопкой мыши и выбирая команду Замена значений (Replace values). Затем в строке формул можно будет вручную заменить жёстко прописанную дату вида #date(2026,3,5) на функцию обновляемой текущей даты языка М DateTime.LocalNow() - аналог функции СЕГОДНЯ() в Excel.
- Объединяем обе таблицы по товару через Данные - Получить данные - Объединить запросы - Объединить (Data - Get data - Merge queries - Merge). Выделяем в обеих таблицах столбец с наименованием в качестве ключевого и жмём ОК.
- Разворачиваем вложенные таблицы в добавившемся после слияния столбце с помощью кнопки с двойной стрелкой в шапке. Помечаем галочками все столбцы кроме наименования - оно у нас в таблице уже есть.
- Фильтруем по столбцу Дата продажи строки, попадающие между любыми двумя датами.
- Исправляем М-код в строке формул, чтобы отфильтровались те строки, где дата продажи попадает между датами из столбцов Старт и Финиш вместо жёстко прописанных дат-констант:
Задача решена! Останется лишь выгрузить результаты на новый лист, используя команду Главная - Закрыть и загрузить (Home - Close & Load).
Ссылки по теме
- Продвинутые техники использования функции ПРОСМОТРХ (XLOOKUP)
- Самый быстрый способ подстановки данных
- Как использовать функцию ВПР (VLOOKUP) для подстановки данных в Excel
Я занимаюсь тарификацией телефонного трафика и там тоже приходится находить данные по подходящей дате из справочников. Помню, ещё в группе на фейсбуке (внесён в список запрещённых организаций в России) мне подсказали способ связывания таблицы фактов и таблицы справочника с несколькими датами через PowerPivot. Но у меня не было столбца Финиш, приходилось отбирать по признаку "текущая дата >= максимальное значение даты из дат справочника, которые меньше или равны текущей дате в таблице фактов". В общем, всю голову сломал, пока удалось связать таблицы в модели данных по составному ключу.
Надо будет попробовать ваш способ в Power Query.
вроде бы всё уже знаешь,
но каждый раз находится всё новые и новые мелочи, которые всегда нужны в "самый не подходящий момент" )
Как бы можно конечно подставить в столбец "Финиш" что-то типа такого =ТДАТА()+10. Но возможно есть какой-то более элегантный способ, чтобы ячейка оставалась пустой и таким образом видно было, что период еще не закрыт