Office 365 Excel多层BOM动态展开表格实现求助

针对你19000行、3-4层嵌套的BOM表格，利用Office 365的动态数组和LAMBDA递归功能，可实现自动展开到底层Base Item并汇总数量，生成动态扩展表格，步骤如下：

1. 准备基础定义（名称管理器） #

打开「公式」选项卡→「名称管理器」，添加以下两个自定义函数：

1.1 获取Assembly对应的组件与数量 #

定义名称：GetComponents
公式：

=LAMBDA(asm, LET(
    row, XMATCH(asm, Table1[Assembly], 0),
    comps, FILTER(Table1[row, 3:32], Table1[row, 3:32]<>"", ""),
    qtys, FILTER(Table1[row, 4:33], Table1[row, 3:32]<>"", ""),
    HSTACK(comps, qtys)
))

作用：根据输入的Assembly，提取其所有非空组件及对应数量，返回两列的数组。

1.2 递归展开BOM到Base Item #

定义名称：ExpandBOM
公式：

=LAMBDA(asm, qty, LET(
    isBase, ISNA(XMATCH(asm, Table1[Assembly], 0)),
    IF(isBase,
        HSTACK(asm, qty),
        LET(
            compQtys, GetComponents(asm),
            expanded, BYROW(compQtys, LAMBDA(x, ExpandBOM(INDEX(x,1), INDEX(x,2)*qty))),
            expandedStack, VSTACK(expanded),
            grouped, GROUPBY(expandedStack[#All], expandedStack[#All], SUM, 0, 1),
            grouped
        )
    )
))

作用：递归判断当前项是否为底层Base Item（未在Assembly列出现的项），若是则直接返回；若不是则展开其组件，递归处理后汇总相同Base Item的总数量。

2. 生成动态扩展表格 #

在新工作表的A1单元格输入以下公式，自动生成所有成品对应的底层采购需求：

=LET(
    finishedGoods, FILTER(Table1[Assembly], Table1[Finished Good]=TRUE),
    allExpanded, BYROW(finishedGoods, LAMBDA(fg, HSTACK(fg, ExpandBOM(fg, 1)))),
    result, VSTACK({"Finished Good", "Base Item", "Total Quantity"}, allExpanded),
    result
)

该公式会自动：

• 筛选出所有标记为Finished Good的成品
• 逐个展开每个成品的BOM到Base Item
• 合并所有结果并添加表头，生成动态扩展的表格（新增成品或修改BOM时，表格会自动更新）

关键注意事项 #

• 确保原BOM数据已转换为结构化表格（命名为Table1），避免因单元格引用变动导致错误
• 需使用支持LAMBDA、GROUPBY、BYROW等函数的Office 365最新版本（订阅版）
• 3-4层嵌套完全在Excel递归限制内（默认100层），无需额外调整
• 空组件列已通过FILTER自动过滤，不会影响结果

内容的提问来源于stack exchange，提问作者Cody Schram