РЕДАКТИРОВАТЬ 1 → 30 марта 2024 г.:

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

Должен быть способ оптимизировать/упростить его.

Цель:

Рассчитать себестоимость продукции за диапазон дат на основе спецификации материалов и истории закупочных цен каждого материала (компонента).

Процесс:

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

Источник данных

TableBOM содержит набор компонентов, необходимых для каждого продукта (Q = количество).

TablePurchaseComponent содержит исторические цены на каждый материал (компонент).

Таблицы в листе:

Лямбда-функции (как определенные имена):

fxProcessVal:

Параметры → lookup_val;component1_cols;component2_cols;result_col

Код:

=LET(
    comp_res; VSTACK(
        FILTER(component1_cols; result_col = lookup_val);
        FILTER(component2_cols; result_col = lookup_val)
    );
    comp_res_fil; FILTER(comp_res; CHOOSECOLS(comp_res; 2) <> "");
    lookup_col; IFNA(EXPAND(lookup_val; ROWS(comp_res_fil)); lookup_val);
    IFERROR(HSTACK(lookup_col; comp_res_fil); "")
)

fxProcessCompRow:

Параметры → data;lookup_row;component1_cols;component2_cols;result_col

Код:

=LET(
    sourceData; fxProcessVal(
        INDEX(data; lookup_row; 2);
        component1_cols;
        component2_cols;
        result_col
    );
    res_val; INDEX(data; 1; 1);
    res_col; IFNA(EXPAND(res_val; ROWS(sourceData)); res_val);
    sourceRes; HSTACK(res_col; DROP(sourceData; 0; 1));
    IF(
        sourceData <> "";
        FILTER(sourceRes; CHOOSECOLS(sourceRes; 2) <> "");
        CHOOSEROWS(data; lookup_row)
    )
)

fxProcessComp:

Параметры → source;component1_cols;component2_cols;result_col

Код:

=LET(
    seq; SEQUENCE(ROWS(source));
    reducer; REDUCE(
        "";
        seq;
        LAMBDA(acc; curr;
            VSTACK(
                acc;
                IFNA(
                    fxProcessCompRow(source; curr; component1_cols; component2_cols; result_col);
                    HSTACK(""; ""; "")
                )
            )
        )
    );
    temp_res; DROP(reducer; 1);
    temp_res
)

fxCompCost:

Параметры → component1_cols;component2_cols;result_col

Код:

=IFNA(
    INDEX(
        unitprice_col;
        MATCH(
            MAXIFS(purchasedate_col; purchasedate_col; "< = " & date; purchasecomponent_col; comp) &
                comp;
            purchasedate_col & purchasecomponent_col;
            0
        )
    );
    0
)

прокси:

Параметры → seed;component1_cols;component2_cols;result_col

Код:

=LET(
    res; IF(
        COUNTA(seed) = 1;
        fxProcessVal(seed; component1_cols; component2_cols; result_col);
        fxProcessComp(seed; component1_cols; component2_cols; result_col)
    );
    comp; CONCAT(seed) = CONCAT(res);
    IF(comp; seed; prox(res; component1_cols; component2_cols; result_col))
)

Ссылка:

Формулы

Формула для развертывания списка спецификации (и дезагрегирования каждого материала и необходимого количества продукта):

=UNIQUE(DROP(REDUCE("";TableBOM[Resultado];LAMBDA(acc;curr;VSTACK(acc;prox(curr;TableBOM[[Componente 1]:[Q1]];TableBOM[[Componente 2]:[Q2]];TableBOM[Resultado]))));1))

Даты расчета стоимости каждого продукта динамически устанавливаются в диапазоне с использованием следующей формулы:

=DATE(YEAR(Q1);SEQUENCE(1;DATEDIF(Q1;Q2;"M")+1;MONTH(Q1);1);1)

ЗАСТРАИВАЕТСЯ ЗДЕСЬ:

Я хотел бы установить Q3 (диапазон дат в виде разлитого диапазона) и рассчитать стоимость каждого продукта в каждом месяце.

В настоящее время СУММПРОИЗВ вычисляет итоговую сумму за все месяцы (даты в диапазоне).

Я пробовал BYCOL и комбинацию MAP и Sequence, но так и не понял.

Любая помощь, я был бы признателен.

Ссылка на файл примера, доступный только для чтения (без макросов)

=LET(
     data;L4#;
     date;Q3#;
     res;INDEX(data;;1);
     comp;INDEX(data;;2);
     q;INDEX(data;;3);
     lab;UNIQUE(res);
     cost;q*fxCompCost(comp;date);
     rt;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*cost)));
     temp;HSTACK(lab;rt);
temp
)

Желаемый результат: