基于BOM与采购价历史的Excel产品成本动态计算优化及日期维度问题

• 在Lambda函数中新增参数，支持用户传入表名和列名
• 已更新Lambda函数以适配上述参数
• 虽已找到解决方案，但测试发现整个程序的资源/内存占用极高

必定存在优化/简化的方法

基于物料清单（BOM）和各物料（组件）的采购价格历史，计算指定日期范围内各产品的成本

流程 #

拆解产品至底层采购级组件并获取对应数量，再将数量乘以各物料对应月份的采购单价

源数据 #

TableBOM（存储各产品所需的组件组合，Q=数量） #

TablePurchaseComponent（存储各物料的历史采购价格） #

Lambda函数（已定义名称） #

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
)

prox #

参数：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))
)

公式 #

拆解BOM清单（展开产品所需的所有物料及对应数量） #

=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（溢出范围的日期列表）作为参数，计算每个产品在各月份的成本。目前SUMPRODUCT计算的是所有月份的总成本，尝试使用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
)

期望得到按产品、月份拆分的成本结果。

内容的提问来源于stack exchange，提问作者Ricardo Diaz