【用Excel校验身份证号码是否合法】

为什么要校验身份证号码？

身份证号码校验方法

第一步：将身份证号码前17位分别乘以不同的系数。

第二步：将17位数字和系数相乘的结果相加，加和值再除以11，得到余数。余数与身份证号码第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个系数。

VALUE(MID($C2,ROW($1:$17),1))

ROW函数返回1-17，分别截取C2单元格中的前17位，并将截取的文本转换为数值

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)

求乘积之和除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校验身份证号码合法性的方法了，使用时可直接复制公式，修改系数所在区域和身份证号码所在单元格格即可。