告别手工算料：动态数组打造智能BOM替代分析 - 2

抖音推荐 2025年08月08日 00:55 2 admin

在昨天的文章中，我们已经完成了单行BOM数据从二维结构到一维结构的转换。然而，在实际业务中，一个产品通常包含多个子件，每条记录都可能存在替代料。因此，仅处理单行是远远不够的。接下来，我们需要将这一转换过程模块化，并利用 VSTACK 函数对所有行进行垂直堆叠，从而实现整张BOM表的批量自动化转换。

公式转换

在构建可复用的 LAMBDA 自定义函数时，必须确保输入参数的维度一致性。如果直接引用固定单元格（如 A4），会导致每行处理逻辑不统一，无法实现批量处理。为此，我们可以使用 OFFSET 函数，以某一基准单元格为“锚点”，动态提取每一行所需的数据区域，确保结构统一、可迭代。

基于 'BOM'!A4 的动态区域提取我们以 'BOM'!A4 为基准单元格，分别构建四个辅助提取公式，用于获取该行所需的关键字段：

=OFFSET('BOM '!A4,,,,4)返回结果：{"A",3,"A03",2}

=OFFSET('BOM '!A4,,1)返回结果：{3}

=OFFSET('BOM '!A4,,4,,3)返回结果：{"A03-1","A03-2","A03-3"}

=OFFSET('BOM '!A4,,3)返回结果：2

将上述 OFFSET 提取逻辑整合，并代入之前的 VSTACK + CHOOSE 结构，得到完整的单行转换公式：

=LET(A,'BOM '!A4,VSTACK(OFFSET(A,,,,4),CHOOSE({1,2,3,4},A,OFFSET(A,,1),TOCOL(OFFSET(A,,4,,3)),OFFSET(A,,3))))

公式说明：

LET(A, 'BOM'!A4, ...)：定义变量 A 为当前行的起始单元格，提升可读性与复用性；

VSTACK(...)：将主料行与替代料明细行合并；

CHOOSE + TOCOL：重构替代料为结构化数组；

所有字段均通过 OFFSET 动态获取，结构清晰、易于封装。

堆叠数据

我们通过 OFFSET 函数实现了单行数据的动态提取，构建了一个结构统一、可复用的 BOM 行转换逻辑。但这还只是“单点突破”。要真正实现整张BOM表的自动化处理，我们需要将每一行的转换结果逐行合并，最终生成一个完整的一维替代料清单。这就需要用到 Excel 强大的 REDUCE 函数。录入动态数组公式：

=DROP(REDUCE("",'BOM '!A2:A11,LAMBDA(X,Y,VSTACK(X,LET(A,Y,VSTACK(OFFSET(A,,,,4),CHOOSE({1,2,3,4},A,OFFSET(A,,1),TOCOL(OFFSET(A,,4,,3)),OFFSET(A,,3))))))),1)

函数解释：

REDUCE("", 'BOM'!A2:A11, LAMBDA(X,Y,...)) X：累积结果（初始为空字符串）Y：当前处理的单元格（如 A2, A3, ..., A11）遍历 A2:A11 每一行，执行括号内的逻辑

LET(A, Y, ...) 将当前行起始单元格 Y 定义为变量 A，便于后续引用

OFFSET(A,,,,4) 提取当前行的主料数据（A~D列：父件、序号、子件、用量）

OFFSET(A,,4,,3) 提取替代料区域（E~G列）

TOCOL(OFFSET(A,,4,,3)) 将横向替代料转为垂直数组

CHOOSE({1,2,3,4}, ...) 重构替代料为四列结构，与主料对齐

VSTACK(主料, 替代料) 合并当前行的主料与所有替代料

VSTACK(X, ...) 将当前行结果追加到累积结果 X 中

DROP(..., 1) 去掉第一行——因为初始值 "" 会生成一个空的首行

实现了全表自动化转换，无需逐行操作，真正迈向“全自动BOM分析”。

筛选数据

在完成上一节的全表堆叠后，我们发现一个常见但关键的问题：当某子件没有替代料时（即E~G列为空），TOCOL 函数会返回 {0}，导致结果中出现无效的“用量为0”记录。这些空值或零值数据不仅干扰后续分析，还可能引发齐套判断错误。

此外，为了支持未来BOM数据的动态扩展（如新增行或列），我们也需要对输出结构进行规范化管理，避免因范围不足或冗余导致公式失效。因此，本节我们将引入数据筛选与结构裁剪机制，进一步优化输出结果的准确性和可维护性。

=LET(A,DROP(REDUCE("",TRIMRANGE('BOM '!A2:A300000),LAMBDA(X,Y,VSTACK(X,LET(A,Y,VSTACK(OFFSET(A,,,,4),CHOOSE({1,2,3,4},A,OFFSET(A,,1),TOCOL(OFFSET(A,,4,,3)),OFFSET(A,,3))))))),1),FILTER(A,INDEX(A,,3)<>0))