IN与OUT文本列匹配判定：现有公式求更优解决方案

需求说明 #

• 数据集包含IN和OUT两列文本，核心匹配值为：Yes、NA、N/A、No、Partial
• 判定规则：
  • 两列核心值匹配返回0，不匹配返回1
  • NA或N/A需等价于Yes
  • 文本可能附带注释（如后缀说明文字）

现有公式 #

=N(IF(ISERROR(IFERROR(SEARCH("Yes",E3),IFERROR(SEARCH("n/A",E3),SEARCH("NA",E3)))),IF(ISERR(SEARCH("Partial",E3)),"No","Partial"),"Yes")<>IF(ISERROR(IFERROR(SEARCH("Yes",F3),IFERROR(SEARCH("N/A",F3),SEARCH("NA",F3)))),IF(ISERR(SEARCH("Partial",F3)),"No","Partial"),"Yes"))

示例数据 #

优化方案 #

方案1：Excel 365/2021及以上版本（推荐） #

利用LET+LAMBDA封装核心值提取逻辑，大幅提升可读性和维护性：

=N(LET(
    getCoreVal, LAMBDA(cell, SWITCH(TRUE,
        ISNUMBER(SEARCH({"Yes","NA","N/A"}, cell)), "Yes",
        ISNUMBER(SEARCH("Partial", cell)), "Partial",
        ISNUMBER(SEARCH("No", cell)), "No"
    )),
    getCoreVal(E3) <> getCoreVal(F3)
))

逻辑说明：

1. 定义getCoreVal函数，通过SWITCH判断单元格文本是否包含指定关键词：
   • 优先匹配Yes/NA/N/A，统一返回Yes
   • 匹配到Partial返回Partial
   • 匹配到No返回No
2. 对比IN和OUT列的核心值，不相等则返回1，相等返回0

方案2：兼容旧版Excel #

通过分层判断减少嵌套，提升可读性：

=N(
    IF(OR(ISNUMBER(SEARCH({"Yes","NA","N/A"},E3)),ISNUMBER(SEARCH({"Yes","NA","N/A"},F3))),
        NOT(AND(ISNUMBER(SEARCH({"Yes","NA","N/A"},E3)),ISNUMBER(SEARCH({"Yes","NA","N/A"},F3)))),
        IF(OR(ISNUMBER(SEARCH("Partial",E3)),ISNUMBER(SEARCH("Partial",F3))),
            ISNUMBER(SEARCH("Partial",E3))<>ISNUMBER(SEARCH("Partial",F3)),
            ISNUMBER(SEARCH("No",E3))<>ISNUMBER(SEARCH("No",F3))
        )
    )
)

逻辑说明：

1. 先判断是否涉及Yes/NA/N/A，只要其中一列属于该范畴、另一列不属于则返回1，否则返回0
2. 再判断Partial，两列是否一者为Partial另一者不是
3. 最后判断No，两列是否不一致

优化优势 #

• 可读性：避免多层嵌套IFERROR，逻辑分层清晰
• 维护性：新增/修改匹配值时，只需调整关键词列表
• 鲁棒性：SEARCH不区分大小写，兼容带注释的文本

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