Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в EXCEL. Аналог ВПР()

history

С помощью функции ВПР() можно выполнить поиск в столбце таблицы (называется ключевым столбцом), а затем вернуть значение из той же строки, но другого столбца. Здесь рассмотрим более сложный поиск: искать будем не среди всех значений ключевого столбца, а только среди значений удовлетворяющих дополнительному условию.

Точное совпадение

Сначала вкратце напомним работу ВПР() - VLOOKUP() с аргументом интервальный_просмотр = ЛОЖЬ (когда ищется значение в ключевом столбце точно соответствующее критерию).

Задача: Для заданного пользователем Товара найти в таблице соответствующую цену и количество (см. файл примера ).

Решение очевидно ( =ВПР(B10;B13:C15;2;0) ) и подробно рассмотрено в статье про ВПР() .

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

Теперь необходимо искать заданный Товар не по всему столбцу Товар, а лишь в строках, относящегося к заданному пользователем Заказу.

Найдем количество и цену для товара Мандарины в Заказе 2.

В ячейках А21 и В21 введем номер Заказа и наименование товара.

В ячейке С21 введем формулу массива :

=ЕСЛИ(МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];""));МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];""));"В заказе нет искомого Товара")

Аналогичную формулу можно использовать для поиска цены.

Ближайшее ЧИСЛО (ДАТА)



В предыдущей задаче предполагалось, что наименование Товара присутствует в Заказе в единственном экземпляре и точно соответствует критерию поиска. Однако, существует класс задач, когда это не так. Особенно часто встречаются задачи поиска ближайшего ЧИСЛА (если точное значение в ключевом столбце не найдено, то выводится ближайшее).

Примечание . Формулы, созданные для поиска ближайших ЧИСЕЛ, работают и для ДАТ, т.к. даты в MS EXCEL хранятся в числовом формате .

Сначала вкратце напомним работу ВПР() - VLOOKUP() с аргументом интервальный_просмотр = ИСТИНА (когда ищется значение в ключевом столбце точно или приблизительно совпадающее с критерием).

Задача: Для указанной пользователем Даты найти соответствующую ей цену (см. файл примера ).

Решение - формула =ВПР(B10;Табл2.1;2) , где Табл2.1 - таблица в формате MS EXCEL 2007 .

Напомним, что для ВПР() с аргументом интервальный_просмотр = ИСТИНА требуется сортированный по возрастанию ключевой столбец (Дата). При отсутствии в столбце точного совпадения выводится наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что ниже. Поэтому Цена выведена 220р., а не 240 и не 230.

Теперь необходимо для заданной пользователем Даты найти соответствующую ей цену, но эта Цена и дата должны относится к заданному пользователем Продавцу.

Другими словами, найдем цену на дату, ближайшей к заданной, но только для определенно продавца.

В ячейках А25 и В25 введем Продавца и дату.

Прежде чем писать формулу, определимся, что значит "ближайший". На самом деле можно дать несколько определений, нужный вариант выбирается исходя из условий решаемой задачи.

Вот несколько возможных определений:

1. В терминах функции ВПР() "ближайший" - это наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что расположено ниже в таблице. Понятно, что такое значение может быть не обязательно ближайшим.

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

3. Ближайшее, т.е. та дата, которая ближе, чем все остальные (м.б. раньше или позже заданной). Если таких дат несколько, то учитывается нижняя и выводится соответствующая ей цена.

Еще несколько вариантов предложено в файле примера .

Понятно, что для каждого определения ближайшего требуется своя формулы массива (все их можно найти в файле примера ).

Формулы для определения цены, как правило, используют результат формулы определяющей ближайшую дату.

Ближайшее время

Поскольку как и дата, так и время в EXCEL хранятся в числовом формате (см. здесь https://excel2.ru/articles/kak-excel-hranit-datu-i-vremya ), то формулы будут работать и для дат с указанием времени, например 03.12.2018 15:06.

В файле примера есть лист для демонстрации работы функции ВПР() для столбца данных со временем (требуется сортировка по возрастанию).

На том же листе решена и другая задача: в столбце, содержащих время в часах и минутах, выделяются строки, которые попадают в заданный интервал времени (сортировка не обязательна).



Комментарии

Только для авторизованных пользователей

Аноним, 23 апреля 2020 г.
Здравствуйте!!! Помогите, пожалуйста, разобраться, есть Лист 1 с таблицей B C D Дата товар цена 12.05.2019 мишка 100 20.05.2019 заяц 200 25.05.2019 мишка 120 26.05.2019 заяц 220 30.05.2019 мишка 150 На отдельном листе вывести Цену товара наибольшей даты. Т.е ввожу 17.06.2019 заяц и по идее должно вывести 220 Эксель пишет: "синтаксическая ошибка в формуле" =ARRAY_CONSTRAIN(ARRAYFORMULA(INDEX('Закуп'!$D$2:$D$1734,MAX(IF(MIN(IFERROR(ABS(IF(('Закуп'!$C$2:$C$1734=C230),'Закуп'!$B$2:$B$1734,"")-B230),""))=IFERROR(ABS(IF(('Закуп'!$C$2:$C$1734=C230),'Закуп'$B$2:$B$1734,"")-B230),""),ROW('Закуп'!$B$2:$B$1734)-ROW('Закуп'!$B$1),0)))), 1, 1)
Михаил, 23 апреля 2020 г.
Здравствуйте! Пусть Ваши данные в диапазоне H28:J32 В ячейке I24 определяем макс дату для зайца =МАКС((I28:I32=A25)*(H28:H32)) - формула массива, равно 26/5/19 Теперь в другой ячейке определяем цену на эту дату у зайца =МАКС(((H28:H32)=I24)*((I28:I32)=A25)*(J28:J32)) - тоже формула массива, равно 220 Если на эту дату 2+ зайцев, то будет выведена макс цена
Аноним, 23 апреля 2020 г.
[id295933596|Михаил], я может не так что-то делаю? Ошибка на скриншоте. И наверное не логично будет использовать MAX, т.к продажа по факту записана, при закупке зайцев по другой цене, в прошедших датах будут меняться значения... Тут все-таки ближе будет формула ближайшая дата к указанной.
Михаил, 27 апреля 2020 г.
[id18340433|Екатерина], можете в нашу группу написать? и файл подгрузить. https://vk.com/excel2ru
Ru, 7 ноября 2021 г.
Здравствуйте, ваша формула очень помогла, подскажите пожалуйста, а как создать правило поиска ближайшей даты с условием по времени, например 1 раз в 2 часа? Заранее спасибо.
Евгений, 9 ноября 2021 г.
Здравствуйте. Вы спрашиваете об округлении времени с точностью до 2х часов?
Ru, 11 ноября 2021 г.
Нет, в принципе как работать со временем, например мы имеет такой формат данных 01.01.2018 0:41:04, 01.01.2018 3:05:26 и т.д., и нужно указать ближайшее время, либо условие например 15 мин до и после события, не подскажите как это сделать? Как настроить кроме даты еще и поиск по времени. Заранее спасибо!
Евгений, 13 ноября 2021 г.
Добавил лист с примером для времени
Ru, 15 ноября 2021 г.
Спасибо Евгений, все четно и по делу, на основе вашего примера выведу свою формулу. Вы делаете очень полезное дело!
(только для авторизованных пользователей)

© Copyright 2013 - 2021 Excel2.ru. All Rights Reserved