Excel公式故障排查：子件父件物料编号分配功能异常求助

问题概述 #

此前正常运行的Excel公式目前仅BOMUploadRelease工作表的父件列（S列）失效，无法从Engineering Release表正确获取父件物料编号并拼接Dispo后缀。其他列数据同步正常，需求为：父件列需匹配有数据行生成对应父件BOM名称，空行留空。

当前失效公式 #

=LET(PART_ORIG, A2, 
PART, IFERROR(MID(PART_ORIG, 1, FIND(".", PART_ORIG)-1), PART_ORIG),
DISPO, 'Engineering Release'!$K$6:$K$62,
OUTLINE,'Engineering Release'!$A$6:$A$62,
PARTS, 'Engineering Release'!$B$6:$B$62,
PARENT, IF(NOT(ISNUMBER(FIND(".", OUTLINE))), OUTLINE,LEFT(OUTLINE, LEN(OUTLINE)-2)),
IDENTIFY_PARENT, XLOOKUP(PART, PARTS, PARENT, "Error 1", 0),
PARENT_PART, XLOOKUP(IDENTIFY_PARENT, OUTLINE, PARTS, "Error 2", 0),
IDENTIFY_DISO, XLOOKUP(IDENTIFY_PARENT, OUTLINE,DISPO, "Error 3", 0),
TEXT(IF(IDENTIFY_DISO="Repair",CONCATENATE(PARENT_PART,".R"),IF(IDENTIFY_DISO="Reuse", CONCATENATE(PARENT_PART,".U"), IF(IDENTIFY_DISO="Modify", CONCATENATE(PARENT_PART,".M"), IF(IDENTIFY_DISO="Ref.", CONCATENATE(PARENT_PART,".REF"), IF(IDENTIFY_DISO="New",CONCATENATE(PARENT_PART, ".N"), ""))))),"0"))

工作表规则说明 #

Engineering Release表 #

通过A列Item编号区分层级（如1为顶级父件，1.1为其子件，1.1.1为1.1的子件），B列为物料编号，K列Dispo标记物料处理方式：

BOMUploadRelease表预期效果 #

A列为物料编号+Dispo后缀生成的BOM名称，S列子件行需填写对应父件BOM名称：

公式错误点分析 #

1. TEXT函数格式冲突：最后用TEXT(..., "0")强制数字格式，但物料编号包含字母（如A-458-461-78A），非数字文本无法转换为数字格式，直接返回#VALUE!错误。
2. Dispo值匹配不符：公式中判断IDENTIFY_DISO="Ref."，但实际Engineering Release表中Dispo值为Reference，两者不匹配，导致该类型父件名称无法生成，最终返回空值。
3. 固定区域范围受限：使用$K$6:$K$62等固定行范围，若数据行数超过62行，会无法匹配后续行数据。

修正后的公式 #

=LET(
    PART_ORIG, A2,
    PART, IFERROR(MID(PART_ORIG, 1, FIND(".", PART_ORIG)-1), PART_ORIG),
    // 动态获取数据最后一行，适配行数变化
    LAST_ROW, COUNTA('Engineering Release'!$A:$A),
    DISPO, 'Engineering Release'!$K$6:INDEX('Engineering Release'!$K:$K, LAST_ROW),
    OUTLINE, 'Engineering Release'!$A$6:INDEX('Engineering Release'!$A:$A, LAST_ROW),
    PARTS, 'Engineering Release'!$B$6:INDEX('Engineering Release'!$B:$B, LAST_ROW),
    // 优化父项Item编号提取，兼容多级子件
    PARENT, IF(NOT(ISNUMBER(FIND(".", OUTLINE))), 
        OUTLINE, 
        LEFT(OUTLINE, LEN(OUTLINE)-LEN(RIGHT(OUTLINE, FIND(".", TEXT(OUTLINE,"@"), LEN(OUTLINE)-1)-LEN(OUTLINE))))
    ),
    IDENTIFY_PARENT, XLOOKUP(PART, PARTS, PARENT, "", 0),
    PARENT_PART, XLOOKUP(IDENTIFY_PARENT, OUTLINE, PARTS, "", 0),
    IDENTIFY_DISO, XLOOKUP(IDENTIFY_PARENT, OUTLINE, DISPO, "", 0),
    // 匹配实际Dispo值，移除错误格式转换，空行返回空值
    IF(IDENTIFY_PARENT="", "",
        SWITCH(IDENTIFY_DISO,
            "Repair", PARENT_PART&".R",
            "Reuse", PARENT_PART&".U",
            "Modify", PARENT_PART&".M",
            "Reference", PARENT_PART&".REF",
            "New", PARENT_PART&".N",
            ""
        )
    )
)

修正说明 #

• 移除TEXT(..., "0")格式转换，避免非数字物料编号的错误。
• 将"Ref."改为"Reference"，匹配实际Dispo字段值。
• 改用INDEX+COUNTA实现动态数据范围，适配行数变化。
• 优化父项Item编号提取逻辑，更可靠处理多级子件。
• 将错误提示改为空值，符合空行留空的需求。

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