|
大家在处理员工信息的时候，应该都有处理过身份证号码吧。身份证号码里包含了很多信息，包括出生地、年龄、性别、生日、生肖、星座等等。今天我们不说如何提取这些信息，说一个更重要的身份证号码录入，毕竟有了数据才能提取，而且录入过程也有很多问题，让我们一起来看看如何解决这些问题。  1、显示问题 我们以现在主流的18位身份证号码为例，当我们在单元格中随便输入一个18位数字比如：123456789012345678。  我们发现身份证号码显示变成科学计数展示了，而且当我们再次选中身份证号码会发现最后3位数字都变成了0。这是因为Excel中数字的精度是15位，当数字超过15位时会用0代替。 处理方法也很简单，这里给两个小方法：①输入法切换至英文状态下，先输入单引号「’」再接着输入身份证号码即可；②右击单元格，设置单元格格式为文本格式即可。  2、长度验证 身份证号码18位数字，录入多了难免会眼花缭乱，一不小心就会多一位或少一位。 「数据验证」可以很好的解决这种情况，选中单元格区域，点击「数据」选项卡下「数据工具」命令组中「数据验证」，调出数据验证对话框，然后点击「设置」选项卡，允许选择「文本长度」，数据选择「等于」，长度选择「18」，最后点击确定即可。  演示如下：  3、唯一性验证 当位数验证完以后，又会有一个问题：重复输入。 选中不能重复输入单元格区域；选择「数据」→「数据工具」→「数据验证」调出验证对话框；验证对话框中「设置」选项卡下「验证条件」中「允许」为"自定义"，公式为「=COUNTIF(B$5:B$7,B5&"*")=1」；  4、合法性验证 我们知道身份证号码是有一套计算规则的，下面我们就来一步一步讲解计算规则。 ①首先身份证号码前17位数字各自都有其对应的系数，然后把17位数字分别和各自的系数相乘并把结果相加；  ②把第一步计算的结果除以11并得到余数。而每一个余数都有其对应身份证号码第18位应该是什么数字；  ③我们把上面计算出来的身份证号码尾数和实际的对比一下即可得出这个身份证号码是否合法。 我们举个例子，比如身份证号码为：321324199005161058。 ① 首先计算前17位数与其系数乘积之和：3*7+2*9+1*10+3*5+2*8+4*4+1*2+9*1+9*6+0*3+0*7+5*9+1*10+6*5+1*8+0*4+5*2=264； ② 然后计算第一步结果除以11的余数：264/11余数为0，其对应的第18位数字应该是1； ③ 计算出来的数字和我们实际数字不匹配，因此这个身份证号码不合法。 其实知道了计算规则以后，我们可以用很多种方式来验证身份证的合法性，下面我们提供一个公式：「=IF(MID("10X98765432",MOD(SUMPRODUCT(MID(B5,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(B5),"合法","不合法")」，只需要把B5单元格换成自己需要的单元格即可。 下面我们来看看演示效果：  当然还有很多其他公式或方法验证身份证号码合法性，大家有兴趣的可以自己去尝试尝试。 我们依次解决了显示问题、长度问题、重复问题、合法问题。但是其实整个过程还不完美，比如我们还可以对前6位所在地验证，是不是一个合法的所在地；对8位日期验证，是不是一个正确的日期。这样才能最大限度的保证身份证的正确性。后面我会用VBA写一个我自认为比较完美的自定义函数来验证身份证号码，到时候分享出来，有兴趣的可以一起讨论哦。 |
|
|
