Excel BOM层级计算与父件分配公式故障求助

1. 层级计算错误修复 #

问题描述 #

原公式=IF('Engineering Release'!A6<>"",LEN('Engineering Release'!A6)-LEN(SUBSTITUTE('Engineering Release'!A6,".","")),"")在处理含多位数的Item No.（如1.5.11）时，易因单元格格式或隐藏字符导致计算偏差，无法准确返回“.”的数量（即实际层级）。

优化后公式 #

=IF('Engineering Release'!A6<>"",COUNTA(TEXTSPLIT('Engineering Release'!A6,"."))-1,"")

公式说明 #

• TEXTSPLIT('Engineering Release'!A6,".")：将Item No.按“.”拆分为文本数组（如1.5.11拆分为{"1","5","11"}）
• COUNTA(...)：统计数组中的元素个数
• 减1后得到“.”的数量，即部件的实际层级，该方法不受数字位数影响，计算更精准。

2. 父件分配错误修复 #

问题描述 #

原公式依赖子部件编号匹配父件，当BOM中存在相同子部件对应不同父部件时，会返回首个匹配的父件，而非与当前子件层级关联的正确父件。

优化后公式 #

=LET(
    parentItemNo, TEXTBEFORE(A2, ".", -1),
    itemNoCol, 'Engineering Release'!$A$6:$A$62,
    partNoCol, 'Engineering Release'!$B$6:$B$62,
    statusCol, 'Engineering Release'!$K$6:$K$62,
    statusCode, XLOOKUP(parentItemNo, itemNoCol, statusCol, ""),
    parentPartNo, XLOOKUP(parentItemNo, itemNoCol, partNoCol, ""),
    IF(parentPartNo<>"", parentPartNo & "." & SWITCH(statusCode, "Ref.","REF", "Repair","R", "Reuse","U", "Modify","M", "New","N", "Outsource","O"), "")
)

公式说明 #

• parentItemNo, TEXTBEFORE(A2, ".", -1)：从当前子件的Item No.中提取父件的Item No.（如1.5.11提取为1.5）
• 通过两次XLOOKUP，基于父件Item No.精准匹配对应的部件编号和状态代码，避免子部件编号重复导致的错误匹配
• SWITCH函数替代原IFS，更简洁地将状态文本转换为缩写代码
• 最后拼接部件编号与状态代码，返回正确的父件标识

内容的提问来源于stack exchange，提问作者Joshua Johnson