二维码 购物车
部落窝在线教育欢迎您!

明明没有重复,Excel却判定数据重复,这是怎么回事?

 

作者:老菜鸟来源:部落窝教育发布时间:2022-05-31 17:35:43点击:13701

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

日常工作中,有时候会遇到筛选重复值出错的情况,明明没有重复,Excel却判定数据重复,这是怎么一回事呢?我们来看看吧!

 

如图所示,我们想要筛选这一列数据的重复值。

首先选中这一列数据,点击开始-【条件格式】-【突出显示单元格规则】-【重复值】,然后选中数据,给这些数据升序排列。

 

图形用户界面, 应用程序, 表格, Excel
描述已自动生成

 

大家可以看到,这一列银行卡号(模拟数据)经过简单的排序后,很多明明没有重复的数据,也被标注成了重复。

基础比较薄弱的同学会说,难道Excel也会出错吗?

其实这不是Excel出了问题,而是一种比较特殊的情况,咱们先说解决方法,再一探究竟。

 

解决方法

不能直接使用条件格式标注重复项,而是应该新建规则。

选中E列数据,然后点击【条件格式】-【突出显示单元格规则】-【其他规则】-【使用公式确定要设置格式的单元格】,然后输入公式=COUNTIF($E:$E,E1&"*")>1即可。

 

图形用户界面, 应用程序
描述已自动生成

 

注意,若之前有设置其他规则,应先将之前的规则取消掉。

取消步骤:选中数据,点击开始-条件格式-清除规则。

 

经过简单的升序排列,我们可以看见,E列使用公式判断重复后的结果,与直接判断重复是有差异的。

 

表格, 日程表
描述已自动生成

 

下面,咱们用COUNTIF函数来统计每个卡号出现的次数。

直接使用公式=COUNTIF(B:B,B2),结果如图所示。

 

表格
描述已自动生成

 

这个结果和直接使用条件格式标注重复项的效果一致,虽然与实际数据不符。

之所以出现这样的情况,是因为COUNTIF函数把任何类似于数字的内容统统按照数字对待,而对于卡号这种位数比较多的数据,按照数字统计是有问题的。

有点经验的同学都知道,超过15位的数字必须以文本格式呈现,否则就会向下图这种形式演变。

 

 

即数据的最后几位自动变成0

也就是说,在COUNTIF看来,这些卡号的第15位以后都是0,并不是表格里实际看到的数据。

为了解决这个问题,就必须将第二参数改成B2&"*",星号的作用是通配符,这样写的意思是每个卡号后面不管有任何内容,都视为同一个卡号。

修改公式为=COUNTIF(E:E,E2&"*"),现在,我们得到的结果是正确的,与实际数据一致。

 

表格
描述已自动生成

 

温馨提示:

COUNTIF函数加&"*"并不适用于任何情况,比如下面这种情况:

 

 

注意这三个卡号,尾号3831实际是2个,但是结果是3,这是因为将3831111也看做是一样的。

所以&"*"这种用法,并不是强制转为文本的原理。

只有要判断的数据位数是一样的情况下,这种用法才是准确的,一旦位数不同,也有可能出错。

最后,总结一下,用条件格式或者COUNITFSUMIF这类支持通配符的统计函数时,在遇到长数字的情况要格外小心,数字位数相同的时候,加&"*"可以保证正确,如果位数不同时则要留神,极有可能出错。

好啦,以上就是今天的所有内容,感谢你的观看。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

提取不重复值并统计数量的三个方法,一秒完成!

如何提取唯一值?试试TEXTJOIN函数搭配VBA自定义!

Excel教程:如何制作带有层次和透视感的图表?

气泡图和条形图如何做组合图,这个Excel图表太适合年终汇报!

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。