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

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


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

Задача

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

Решение



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

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

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

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

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

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

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

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

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

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

Аноним, 11 января 2018 г.
Подскажите, а как сделать в формуле запись Таблица4[Дата],если имя Таблица 4 присвоено только соответствующему диапазону?
Аноним, 11 января 2018 г.
и еще просьба подсказать с аналогичными составляющими формулы цены,как, например, СТРОКА(Таблица4[Дата])
Аноним, 11 января 2018 г.
Михаил, 12 января 2018 г.
Преобразовал таблицу в диапазон, создал 2 имени и обновил файл примера, см. лист Диапазон
(только для авторизованных пользователей)

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