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

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

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

Задача

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

Решение

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

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

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

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

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

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

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

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

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

Связанные статьи

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