Excel统计不相邻空白单元格时COUNTBLANK公式计算错误如何解决

问题原因 #

• 最常见的原因是视觉上的空白单元格不是真空白，单元格内存在空格、换行符、制表符等不可见字符。COUNTBLANK仅会统计完全无内容、包含空文本""或公式返回空值的单元格，带有不可见字符的单元格不会被判定为空白，因此不会被计入统计。
• 其次可能是公式引用行号错误：你给出的示例公式引用的是第4行的单元格，如果要统计第6行的结果，需要确认公式内的行号是否同步更新为6，是否误加了行绝对引用符号$导致引用了其他行的单元格。
• 少数情况是单元格设置了隐藏内容的自定义格式，实际单元格有值但显示为空白，也会导致统计偏差。

解决方法 #

1. 先排查问题根源：任选一个未被统计的视觉空白单元格，输入公式=LEN(目标单元格地址)查看返回值，如果返回值大于0，即可确认单元格内存在隐藏内容。同时可以右键单元格打开「设置单元格格式」，检查自定义格式规则是否存在隐藏内容的设置。
2. 修正统计公式：如果确认是假空白导致的统计偏差，可以改用兼容不可见空白字符的统计公式，两种写法均可：
   • 单单元格逐个判定写法：

     =SUMPRODUCT(--(TRIM(BE6)&""=""))+SUMPRODUCT(--(TRIM(BG6)&""=""))+SUMPRODUCT(--(TRIM(BH6)&""=""))+SUMPRODUCT(--(TRIM(BI6)&""=""))
     

   • 精简批量写法：

     =SUMPRODUCT(--(TRIM(CHOOSE({1,2,3,4},BE6,BG6,BH6,BI6))&""=""))
     

   上述公式会先清理单元格前后的空白字符再做判定，包含空格、换行符的单元格也会被正常统计。
3. 修正引用错误：如果你是将第4行的公式下拉复用，选中第6行的公式单元格，检查编辑栏内的引用是否对应第6行的目标单元格，如有错误手动修改行号即可。

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