Блог

У меня есть таблица с конкретными продуктами и датами, и я хочу получить значения затрат, соответствующие этой дате. Исходная таблица имеет диапазон дат, а не фактическое совпадение (это моя проблема). Вот задача: мы пытаемся заполнить столбец «Стоимость» на основе листа 2

ЛИСТ 1:

На другом листе указаны диапазоны дат с желаемыми результатами (цены продажи), например:

ЛИСТ 2:

PS. У меня разные затраты на разные продукты за одну и ту же дату. Итак, нам нужно было также добавить параметр, который будет сопоставлять продукт с одного листа с продуктом другого.

Если указанные диапазоны начинаются с A1 и заканчиваются на D3, то в Sheet1!C2 : =INDEX(Sheet2!D:D,MATCH(1,(B2>Sheet2!B:B)*(B2<Sheet2!C:C)*(A2=Sheet2!A:A),0))

Это формула массива для ввода с ctrl shift enter

Он индексирует столбец D листа 2 и выполняет поиск первого совпадения, в котором все указанные условия являются истинными (= 1 ). Каждое условие выдает 1 или 0 для каждой ячейки в диапазоне и умножает его на результат ячейки из следующего диапазона в той же строке. Если любое из условий равно false, оно умножается на 0, в результате чего получается 0. Если все условия выполнены, это приведет к 1 (1 1 1). В целом создается массив {0,0,1,0,…}, а функция сопоставления возвращает N-е вхождение первого 1, которое равно номеру строки условий, являющихся истинными.

Поскольку вы упомянули таблицы, я предполагаю, что вы имеете в виду реальную таблицу Excel, а не просто ячейки, отформатированные в виде таблицы.

• Таблица листа 1 называется: tbl_ProductPrice
• Таблица листа 2 называется: tbl_ProductCost

Формула столбца «Стоимость» на листе 1:

  =SUMIFS(tbl_ProductCost[Cost],[Date],">="amp;tbl_ProductCost[Start Date],[Date],"<="amp;tbl_ProductCost[End Date])
 

Объяснение

Первый параметр SUMIFS, столбец «Стоимость», — это то, что будет суммировано, если все критерии верны.

Сначала, если:

• Второй параметр — это критерии даты для проверки.
• Третий параметр — это то, что нужно проверить, больше или равно начальной дате.

Во-вторых, если:

• Четвертый параметр снова является датой для второго оператора if
• Пятый параметр проверяет, меньше или равно конечной дате.

Результаты:

Редактировать

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

 =INDEX(tbl_ProductCost[Cost],MATCH(1,([@Product]=tbl_ProductCost[Product])*([@Date]>=tbl_ProductCost[Start Date])*([@Date]<=tbl_ProductCost[End Date]),0))
 

Введите формулу с Ctrl ShiftEnter, поскольку это формула массива.

Я также добавил соответствие продукта, поскольку вы указали несколько диапазонов дат для каждого типа продукта.

Результаты