如何处理TEXTSPLIT结果：消除漏登事务标识及单元格溢出问题

现有基础逻辑 #

通过TEXTSPLIT拆分单元格数据，提取操作名称和对应重量：

=INDEX(TEXTSPLIT($W16,{"[","]"},{",","WorkCenter[ActualQty]:"},TRUE),,)

基于拆分结果，用SWITCH实现规则：当操作重量低于订单重量（$M16）的90%时，将操作转换为特定标识；已完成且有重量记录的操作不显示标识；指定操作（如FINALINSP、SHIPAPPR）即使重量为0也显示空白，现有公式：

=IF(INDEX(TEXTSPLIT($W16,{"[","]"},{",","WorkCenter[ActualQty]:"},TRUE),,2)*1<(0.9*$M16),(SWITCH(INDEX(TEXTSPLIT($W16,{"[","]"},{",","WorkCenter[ActualQty]:"},TRUE),,1),"M400CLEAN","C/","PICKLE","C/","11X3COAT","C","10X2COAT","C","11X1COAT","","DRAW4SHAVE","D","WIREDRAW","D","SHAVE","SH","C/C","C/C","BOXANNEAL","A","PITANNEAL","PA","TANK24","M","COPPER","CU","FINDRAW","DF","SCRAP","","ENGINSP","","PULLMAT","PULL - ","STRIP4SHAV","","STRIP","","FINALINSP","","SHIPAPPR","","IGAINSP","","BLOCK3","D","LABINSP","","S/C&G","S/C&G")),"")

待解决问题 #

• 消除漏登事务的标识输出：漏登事务指当前操作重量为0，但后续操作重量>0（说明该操作已完成但未录入系统），例如示例中的"C/C"、"PICKLE"、"WIREDRAW"，这类情况需设为空白
• 解决单元格溢出问题：无法直接引用溢出单元格的结果

解决方案 #

1. 处理漏登事务逻辑 #

将拆分结果先存入溢出数组，再通过BYROW结合FILTER判断后续操作重量：

1. 先定义拆分后的溢出数组（比如在单元格X16输入）：

=TEXTSPLIT($W16,{"[","]"},{",","WorkCenter[ActualQty]:"},TRUE)

2. 用BYROW遍历每一行操作数据，叠加漏登判断：

=BYROW(X16#,LAMBDA(row,
    LET(
        opName,INDEX(row,1),
        opQty,INDEX(row,2)*1,
        order90Pct,0.9*$M16,
        // 获取当前行之后的所有操作重量
        subsequentQtys,FILTER(INDEX(X16#,0,2),ROW(X16#)>ROW(row)),
        hasSubsequentNonZero,OR(subsequentQtys>0),
        // 基础规则计算
        baseResult,IF(opQty<order90Pct,SWITCH(opName,"M400CLEAN","C/","PICKLE","C/","11X3COAT","C","10X2COAT","C","11X1COAT","","DRAW4SHAVE","D","WIREDRAW","D","SHAVE","SH","C/C","C/C","BOXANNEAL","A","PITANNEAL","PA","TANK24","M","COPPER","CU","FINDRAW","DF","SCRAP","","ENGINSP","","PULLMAT","PULL - ","STRIP4SHAV","","STRIP","","FINALINSP","","SHIPAPPR","","IGAINSP","","BLOCK3","D","LABINSP","","S/C&G","S/C&G"),""),
        // 漏登判断：当前重量为0且后续有非0重量时返回空白，否则用基础结果
        IF(AND(opQty=0,hasSubsequentNonZero),"",baseResult)
    )
))

2. 解决溢出引用问题 #

• 直接使用溢出数组的引用格式X16#（假设拆分结果在X16开始溢出），替代重复调用TEXTSPLIT，既减少计算量，又解决无法引用溢出单元格的问题
• 如果需要固定引用溢出范围，可将拆分结果定义为命名区域（公式选项卡→定义名称），比如命名为OpSplitData，后续公式中直接用OpSplitData引用

关键逻辑说明 #

• 用LET函数封装变量，简化公式结构，提升可读性
• BYROW+LAMBDA实现逐行处理，结合FILTER筛选后续行重量，判断是否存在非0值
• 先计算基础规则结果，再叠加漏登事务的判断，覆盖特殊场景

内容的提问来源于stack exchange，提问作者HF at BP