编按:哈喽,大家好!对于刚学习excel的小白,很多老师都会叮嘱他们,要注意表格规范、函数参数格式等等,这些固然重要,但是今天,苗老师还要告诉大家另一个需要引起重视的东西:小心超过15位的数字。这是什么意思呢?一起来看看下面的文章,你就清楚了!
小美:“苗老师,快救救我,我发现我用COUNTIFS函数判断身份证号码是否重复的时候,有好几个不是重复的值,用函数得到的结果却是2。”
苗老师:“截图给我看看?”
小美:“图片在这里。”如下所示:
小美:“你看,这里面的两个身份证号码,明明尾号不同,但是却被判断成一样的了。”
苗老师:“我原来有教过你,超过15位的数需要用什么格式?否则会怎么样?”
小美:“我就是按照你教的方法输入的,像身份证号,银行卡号这些,我都是用文本格式输入的,不然会变成科学计数法的样式,然后超过15位之后的数字也会变成0。”
苗老师:“不错,记得很牢,正因为Excel的计算精度极限是15位,所以在输入超过15位的数字时,需要把数字变成文本来统计。COUNTIFS在常规计算时,同样也只能计算前15位。上面的这两个身份证号码,就是因为前15位相同,导致COUNTIFS认为它们是一致的。”
小美:“那要怎么解决呢?”
苗老师:“好办,在函数里加点东西。”如下图所示:
小美:“这是什么?”
苗老师:“由于常规公式中无法识别超过15位的数字,所以这里在英文双引号里面加一个星号,使其从数值统计,强行变为文本统计,从而进行完整的判断。说到这呀,我还要提醒你一下,条件格式里也有这个问题。”如下图所示:
你看,这两个身份证号码,在用条件格式判断重复值的时候,也被判断成了重复。
小美:“你不说我以前还真没注意过这个问题。那要怎么解决?”
苗老师:“同样,我们在条件格式里,输入公式:=COUNTIFS(A:A,A1&"*")>1即可。”如下图所示:
这样,它们就不会被判定为重复项了。
小美:“太棒了,今天又学会了一招。”
苗老师:“说到这里,我还要再提醒你一下,在做工资或报销的时候,如果是用SUMIFS函数通过判断身份证号码来统计每个人的金额合计,也需要注意这个问题。给你看看这个。”如下图所示:
小美:“哇,一个超过15位的数字,居然有这么多的陷阱,不过这个我知道该怎么解决了,就是把A2后面也加上' *’对吧。”如下图所示:
苗老师:“厉害厉害,都学会举一反三了,我们在工作中,经常会碰到各种问题,发现问题的共同点,时间久了,再难的问题也难不住你。”
****部落窝教育-excel公式中的长数字****
原创:苗旭/部落窝教育(未经同意,请勿转载)
联系客服