• 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),”合法”,”不合法”),”长度错误”)