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