多IF嵌套引发运行时错误1004的解决咨询

问题场景 #

在VBA代码中给单元格批量设置嵌套IF映射公式时触发1004运行时错误，原需求是将F列的多个状态值映射为缩写（如ReadyToPick和ReadyToPickUnconstrained都映射为RTP），以便后续用CountIfs和SumIfs处理数据。

错误原因 #

并非重复映射导致错误，而是Excel旧版本（2019及更早）对嵌套IF的层级限制——最多支持7层嵌套，而原公式包含了8层IF判断，超出限制触发1004错误。

解决方案 #

以下两种方法均可替代原嵌套IF逻辑，避免层级超限问题：

方案1：使用TEXTJOIN合并相同映射条件（适用于Excel 2019/365）

通过TEXTJOIN将同一缩写对应的多个状态合并判断，大幅减少嵌套层级：

With Sheet12 'Sheet12 = Raw Data - DS
    .Columns("C:D").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row).FormulaR1C1 = _
        "=IF(TEXTJOIN("""",TRUE,IF(RC[-1]={""Crossdock""},""CD"",""""))<>"""",""CD""," & _
        "IF(TEXTJOIN("""",TRUE,IF(RC[-1]={""ReadyToPick"",""ReadyToPickUnconstrained""},""RTP"",""""))<>"""",""RTP""," & _
        "IF(TEXTJOIN("""",TRUE,IF(RC[-1]={""PickingPicked"",""PickingPickedAtDestination""},""PP"",""""))<>"""",""PP""," & _
        "IF(TEXTJOIN("""",TRUE,IF(RC[-1]={""PickingNotYetPickedPrioritized"",""PickingNotYetPickedNotPrioritized""},""PNYP"",""""))<>"""",""PNYP""," & _
        "IF(RC[-1]=""Loaded"",""LD"",""""))))"
End With

方案2：使用LOOKUP函数实现映射（兼容所有Excel版本）

将状态与缩写的映射关系做成数组，用LOOKUP直接匹配，逻辑更简洁且无层级限制：

With Sheet12 'Sheet12 = Raw Data - DS
    .Columns("C:D").Insert shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("G2:G" & .Cells(.Rows.Count, "F").End(xlUp).Row).FormulaR1C1 = _
        "=LOOKUP(RC[-1]," & _
        "{""Crossdock"",""ReadyToPick"",""ReadyToPickUnconstrained"",""PickingPicked"",""PickingPickedAtDestination"",""PickingNotYetPickedPrioritized"",""PickingNotYetPickedNotPrioritized"",""Loaded""}," & _
        "{""CD"",""RTP"",""RTP"",""PP"",""PP"",""PNYP"",""PNYP"",""LD""})"
End With

效果验证 #

两种方法均能实现原需求的状态映射，且不会触发1004错误，后续可正常使用CountIfs和SumIfs对G列的缩写数据进行统计分析。

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