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

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

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

Сначала вкратце напомним работу ВПР() - 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. Ближайшее, т.е. та дата, которая ближе, чем все остальные (м.б. раньше или позже заданной). Если таких дат несколько, то учитывается нижняя и выводится соответствующая ей цена.

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

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

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


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

Аноним, 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
(только для авторизованных пользователей)

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