1.防止输入人员随手输入一个18位数字冒充身份证号码
2.防止输入手误,有时候并非故意输错
注意本方法无法检测身份证是否真实存在,只能从格式上检查号码是否合法
第1步:把号码的前17位分别乘以不同的系数,系数表如下
第2步:把17位数字和系数相乘的结果相加,加和值再除以11,取其余数。用得到的余数与身份证第18位号码进行对照,余数与身份证号码第18位对应关系如下表
例如:某男士的身份证号码是622924199605211012。首先我们得出前17位与系数的乘积和是320,除以11的余数是1,对应第18位数是0。所以这是一个不合法的身份证号码。
计算第18位数
=IF(LEN($C2)=18,MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1),"长度错误")
判断是否合法
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"长度错误")
其中,C2为身份证号码,$B$2:$B$18为第1到17个系数。这个一般存到另一个sheet里或者顶一个名称,与实际使用的表分开
VALUE(MID($C2,ROW($1:$17),1))
ROW函数返回一个1到17的数组,配个mid函数依次取出C2号码中前17个数字,并将截取的文本用value转换为数值
SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)
用SUMPRODUCT函数返回MID($C2,ROW($1:$17),1)数组和$B$2:$B$18(校验表)区域对应相乘的和
MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)
用mod对上一步求乘积之取11的余数
MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)
用MID函数截取10X98765432中的1位数,即身份证号码的第18位。例如余数是0,0+1=1,截取第1位;余数是2,2+1=3,截取第3位
IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法")
IF函数判断计算值是否等于身份证号码第18位,相等则合法,不相等则不合法
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"长度错误")
在计算第18位数之前先判断输入的身份证号码是否是18位,如果是则往下计算,不是则直接显示长度错误
这个就是用Excel检查身份证号码合法性的方法了,实际使用可直接复制公式,$C2改为身份证所在单元格,$B$2:$B$18改为你自己存放的对照表区域或者自定义名称
=IF(LEN($C2)=18,IF(MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1)=RIGHT($C2,1),"合法","不合法"),"长度错误")
联系客服