countif函数统计身份证号码出错的解决方法

 

  最近连续有两个同学提问，关于countif统计身份证号码结果出错的问题。如下面的示例，在B列计算A列身份证的出现次数，但公式计算结果却有误，象A2的身份证共出现2次，计算结果却是8.如上图所示。

  分析：

    公式=COUNTIF(A:A,A2)中，就countif函数的参数设置是没有任何问题的，结果出错的原因我们一点点道来：

    在excel中我们输入超过15位的数字时，后面的数字会自动变成0。这是因为excel只能精确到的15位数，15位后的数会按0计算。

       本例中身份证号码前15位都相同，15~18位的数字excel全按0计算，所以在excel眼里前15位相同的数字就是相同的，无论15位后的是否相同。countif计算错误是必然的了。

    解决方案：

       excel不认识数字的15位后数字，但如果数字被当成文本格式，excel就能识别15位后的数字。有同学会说，这里本来就是文本格式，要不然也无法输入完整的身份证号。excel有点精明过头，在用公式计算时，文本性的数字有时也会当数值型来对待，本例中就是。那么怎么让excel确认是字符串类型呢？

      *（星号)在excel公式里是通配符，“A*”表示以A开头后面跟任意长度的字符串。只有文本格式才适用于通配符。所以如果我们让身份证号连接*号，就可以明确的传达给countif信息，你要统计的是文本格式，不是数值。

     根据以上分析，我们可以把公式设置为

        =COUNTIF(A:A,A2&'*')

     正确的计算结果如下图所示。

    

      还有同学问如何对比两个表的身份证号码，在两个表中各设置COUNTIF函数公式，统计本表的身份证号码在另一个表中的个数，如果大于0，说明在另一个表中存在。

     如当前表是sheet1，查找在sheet2表A列中是否存在本表A列的身份证号码。

        =COUNTIF(Sheet2!A:A,A2&'*')

 

    兰色说： 同学们平时工作有什么excel疑难问题可以回复提问,其中有代表性的问题将作为示例讲解共享给全体同学们。