Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в EXCEL. Несортированный список

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

Разовьем идеи из статьи Поиск ЧИСЛА ближайшего к заданному. Несортированный список в MS EXCEL . Поскольку даты в MS EXCEL хранятся в числовом виде , то формулы из этой статьи будут работать и для дат.

Задача

В таблице введен товар, цена и дата, с которой начинает действовать данная цена. Необходимо для заданного товара получить цену, актуальную на заданную дату.

Решение

Задача решается в 2 этапа (см. файл примера ):

1. Сначала для определенного товара ищется наибольшая дата, которая меньше или равна заданной.
2. Затем, по этой дате и товару ищется номер соответствующей строки в таблице и выводится текущая цена.

Пользователь задает Товар, цену на который требуется определить, и Дату, на которую требуется определить цену (см. желтые ячейки в строке 6).

Ближайшую дату найдем с помощью формулы массива :

= МАКС(ЕСЛИ((Таблица4[Дата]<=B6)*(Таблица4[Товар]=A6);Таблица4[Дата];""))

Текущую цену найдем с помощью еще одной формулы массива:

= ИНДЕКС(Таблица4[Цена]; НАИБОЛЬШИЙ(ЕСЛИ((Таблица4[Дата]=C6)*(Таблица4[Товар]=A6);СТРОКА(Таблица4[Дата])-СТРОКА(Таблица4[[#Заголовки];[Дата]]);"");1);1)

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

Данные в таблице могут быть не отсортированы.