当身份证遭遇EXCEL，怎样避开这些坑？

Jenny Excel表格学院

张老师的课代表林林帮班主任录入全班同学基本资料。在录入身份证时遇到了问题，于是来找张老师。

 

一、后面的数字不见了？

这是林林首先遇到的问题：输入的身份证号变成科学计数法的形式显示，而且在编辑栏中看到最后的几位数自动变成了0。

林林检查了很多次，也重输了很多次，结果都是如此。

 

张老师告诉林林不要紧张，这个问题很容易解决：将单元格格式改为文本，再输入就可以了。

 

      

为什么会出现这样的问题呢？张老师告诉林林：

在Excel表格里面，输入数字超过了11位，Excel则会自动转换为科学记数格式；输入如果超出了15位数值，Excel则会自动将15位以后的数值转换为“0”。

在录入时可以先录入一个英文单引号再录入，或者先修改单元格格式为文本后再录入就可以解决这个问题了。

 

二、身份证为何是重复的？

 

数据输入之后，林林担心不小心输入了重复的身份证，于是用COUNTIF函数统计每个身份证出现的次数，如果统计结果是“1”，身份证就是唯一“不重复”的，否则是“重复”。

 

统计的结果出乎林林预料，B3和B4的身份证号明明只有一个，COUNTIF的结果却是相同（重复）的？

 

张老师告诉林林，COUNTIF函数在处理时，会将文本型数值识别为数值进行统计。由于超过15位的数值只能保留15位有效数字，后3位全部视为0处理，因此COUNTIF函数会将B3、B4单元格中的身份证号码都识别为相同，计数结果为2。

 

解决这种误判的方法是在第二参数后加“*”，查找的数据就是文本而非数值，就不会出现前15相同的身份证被判断为相同了。

 

公式修改为：

=IF(COUNTIF(B:B,B3&"*")=1,"不重复","重复")

 

三、怎样避免身份证重复？

 

林林提出了问题：如果在输入的时候就可以判断是否输入了重复的身份证，可以从源头控制错误，而且身份证都是长度是18位，怎样避免输入的号码不是18位，或者与其他人相同？

 

张老师告诉林林，这个问题可以通过数据验证（数据有效性）的方法解决。

  

   

判断长度是否是18位：LEN(B3)=18

判断B3在B列中否是唯一的：COUNTIF(B:B,B3&"*")=1

上面两个要求都要满足 ，所以用AND函数——

=AND(LEN(B3)=18,COUNTIF(B:B,B3&"*")=1)

注意，为了保证能正常录入，一定要加上&“*”（截图未加）

这样，当再去录入身份证号时，一旦录入的不是18位就会提示错误，或者录入重复时也会提示错误。同时，又保证了哪怕当前17位都完全一样的情况下，也能正常录入。