表格里如何检查身份证号是否正确(检查身份证号码是否正确的函数)
身份证号码的验证在网络上有许多公式,往往比较复杂,今天给大家介绍我经常用到的一个验证公式,非常简单,希望对大家有所帮助,也希望有网友有更为简洁的方法,欢迎留言探讨。
在日常excel使用中,最常用,也是最头痛的就是身份证号码的操作,我国身份证采用18位的数字来标识身份信息,容易被excel当做长数字处理,直接用科学记数法显示出来,造成数据丢失。另外,身份证号码中隐藏了许多有用的信息,有户籍信息,出生日期信息,年龄信息,性别信息,校验信息等,我们可以通过公示轻松提取出来。
另外,由于身份证号码比较长,在录入过程中容易出现错误,对号码进行正确性验证就非常有必要。下面我讲一个我经常用的一个校验公式。
一、有必要先简单说一下校验码的计算步骤。
(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, ... , 16 ,先对前17位数字的权求和
Ai:表示第i位置上的身份证号码数字值(0~9)
Wi:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 (表示第i位置上的加权因子)
(2)计算模
Y = mod(S, 11)
(3)根据模,查找得到对应的校验码
Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2
公式如下:
=IF(MID("10X98765432",MOD(SUMPRODUCT((--MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1)),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11) 1,1)=MID(A1,18,18),"正确","错误")
注:公式中红色“A1”表示身份证号码所在的单元格。
解释:这个公式用到if、 mid、mod、sumproduct四个函数,--操作符。
(1)--MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1))这个函数表示把A1单元格中的身份证号码前17位信息转换成内存数组,例如身份证号码为:412901197708151011,转换后为{4;1;2;9;0;1;1;9;7;7;0;8;1;5;1;0;1},便于下一步计算。操作符“--”表示把字符转换成数值。
(2)SUMPRODUCT函数表示把两个数组进行乘积和。
(3)mod函数表示取模,就是求余数。将计算出来的数字对11求余数,出来就是校验码,也就是身份证号码的最后一位。