Excel中IF+AND组合与COUNTIF函数的性能差异咨询

公式对比 #

公式1：

=IF(AND(A1="Completed",B1="Completed",C1="Completed"),"Completed",
     IF(AND(A1="Not started",B1="Not started",C1="Not started"),"Not started","In Progress"))

公式2：

=IF(COUNTIF(A1:C1,"Completed")=3,"Completed",
     IF(COUNTIF(A1:C1,"Not started")=3,"Not started","In Progress"))

性能差异拆解 #

• 公式1的计算特性：
  依赖AND函数的短路求值逻辑——只要有一个单元格不满足条件，就立即停止后续单元格的判断。比如第一个AND判断中，如果A1不是"Completed"，直接跳过B1、C1的检查，进入下一层IF。在表格引用场景下，每个单元格引用都是独立解析，没有额外的区域遍历开销。

• 公式2的计算特性：
  每次COUNTIF都会完整遍历目标区域（A1:C1）。第一个COUNTIF统计"Completed"数量要扫3个单元格，第二个COUNTIF统计"Not started"数量还要再扫一遍。最坏情况（最终返回"In Progress"）下，公式2会对同一区域遍历两次，表格引用的结构化解析会进一步放大这个开销。

数千个公式场景下的影响 #

当存在数千个此类公式时，性能差异会非常明显：

• 公式1在多数非极端场景（既不全是Completed也不全是Not started）下，仅需判断1-2个单元格就会得出结果，计算量远小于公式2；
• 公式2的重复区域遍历会累积大量计算开销，尤其是表格引用时，Excel需要额外处理结构化数据的关联逻辑，整体运算效率会显著低于公式1。

可选优化方案 #

如果想要平衡可读性和性能，可以改用SUMPRODUCT减少一次区域遍历（仅在最坏场景下生效）：

=IF(SUMPRODUCT(--(A1:C1="Completed"))=3,"Completed",
     IF(SUMPRODUCT(--(A1:C1="Not started"))=3,"Not started","In Progress"))

不过从性能最优角度，公式1仍是首选。

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