统计重复值,经常会用到 countif 函数,但是最近有读者在使用这个函数的时候结果却出错了,而公式明明是对的。
下图 1 是每个客户编号对应的最终销售,每个客户可能在不同的销售间转手多次,但最终的销售只有一位。
请查找出客户编号有重复的所有单元格,在 C 列中显示 dup,效果如下图 2 所示。
1. 选中 C2:C12 区域 --> 输入以下公式 --> 按 Ctrl+Enter:
=IF(COUNTIF($A$2:$A$12,A2)>1,'dup','')
公式释义:
* 请注意:$A$2:$A$12 需要绝对引用。
但是结果有点让人意外,所有结果都是“dup”,也就是说每个客户编号都有重复?明明没有啊。要了解出错原因,就要从 countif 函数的特性说起。
从图上可以明显看出,客户编号列的格式是文本,因为这么长的数值,只有改成文本格式才能完整显示。
而 countif 在计算时,会将文本型数值识别为数值,再进行计算。问题恰恰就在这里,Excel 最多只能显示 15 位数值,超过的部分全部自动变为 0。这样的话,countif 统计出所有编号当然都是重复的。
那是不是在这种情况下就要摒弃 countif,而另辟蹊径?非也,还是用上面的的公式,只要稍微作一点点修改。
2. 选中 C2 单元格 --> 在原公式的基础上,在 A2 后面加上 &'*' --> 下拉复制公式:
=IF(COUNTIF($A$2:$A$12,A2&'*')>1,'dup','')
公式释义:
类似的参数变通用法,在 vlookup 中也可以举一反三,详情可参阅 Excel vlookup 函数的几种高难度错误及解决方案。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,文中专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。
联系客服