Excel按优先级取多状态列首个非空值的公式正确性及优化咨询

现有公式问题诊断 #

你写的嵌套IF逻辑完全颠倒，不符合需求：
你当前的公式逻辑是「如果BZ2为空则返回BV2，否则判断BV2是否为空」，但正确逻辑应该是「如果BZ2非空则直接返回BZ2，否则才判断BV2是否为空」，所以现有公式在最高优先级列有值的情况下也不会返回正确结果，需要调整。

如果要沿用嵌套IF写法，正确的结构应该是：

=IF(NOT(ISBLANK(BZ2)),BZ2,IF(NOT(ISBLANK(BV2)),BV2,IF(NOT(ISBLANK(BR2)),BR2,IF(NOT(ISBLANK(BN2)),BN2,IF(NOT(ISBLANK(BJ2)),BJ2,IF(NOT(ISBLANK(BF2)),BF2,IF(NOT(ISBLANK(BA2)),BA2,IF(NOT(ISBLANK(AW2)),AW2,IF(NOT(ISBLANK(AS2)),AS2,IF(NOT(ISBLANK(AO2)),AO2,IF(NOT(ISBLANK(AK2)),AK2,IF(NOT(ISBLANK(AG2)),AG2,IF(NOT(ISBLANK(AC2)),AC2,IF(NOT(ISBLANK(Y2)),Y2,U2)))))))))))))

但这种写法层级多、难维护，更推荐下面的简洁方案。

更简洁的实现方案 #

适用Excel 365/2021及以上版本 #

直接用TOCOL函数忽略空值后取第一个元素即可，公式简洁易维护：

=IFERROR(INDEX(TOCOL({BZ2,BV2,BR2,BN2,BJ2,BF2,BA2,AW2,AS2,AO2,AK2,AG2,AC2,Y2,U2},1),1),"无有效状态")

参数说明：

• 大括号内按优先级从高到低排列所有待校验列，和你需求的校验顺序完全一致
• TOCOL的第二个参数传1，表示自动忽略所有空值
• 外层套INDEX取处理后数组的第一个元素，就是优先级最高的非空状态值
• IFERROR用于所有列都为空时返回自定义提示，可根据需求调整

适用所有Excel版本（兼容2019及更早版本） #

用LOOKUP的经典匹配逻辑实现：

=IFERROR(LOOKUP(1,0/NOT(ISBLANK({BZ2,BV2,BR2,BN2,BJ2,BF2,BA2,AW2,AS2,AO2,AK2,AG2,AC2,Y2,U2})),{BZ2,BV2,BR2,BN2,BJ2,BF2,BA2,AW2,AS2,AO2,AK2,AG2,AC2,Y2,U2}),"无有效状态")

逻辑说明：

• NOT(ISBLANK(列))会生成布尔数组，非空列对应TRUE，空列对应FALSE
• 用0/运算后，非空列对应0，空列对应错误值
• LOOKUP找小于查找值1的最大值，也就是第一个0的位置，返回对应位置的列值

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