Excel函数技巧检测输入的身份证号码是否合法

原创 kaixinit  2018-10-21 11:40  阅读 1,086 次
  • 1.防止输入人员随手输入一个18位数字冒充身份证号码
  • 2.防止输入手误,有时候并非故意输错
  • 注意本方法无法检测身份证是否真实存在,只能从格式上检查号码是否合法
Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

身份证号码校验基本原理

第1步:把号码的前17位分别乘以不同的系数,系数表如下

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

第2步:把17位数字和系数相乘的结果相加,加和值再除以11,取其余数。用得到的余数与身份证第18位号码进行对照,余数与身份证号码第18位对应关系如下表

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

例如:某男士的身份证号码是622924199605211012。首先我们得出前17位与系数的乘积和是320,除以11的余数是1,对应第18位数是0。所以这是一个不合法的身份证号码。

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

判断身份证号码是否合法的函数公式

计算第18位数

=IF(LEN($C2)=18,MID("10X98765432",MOD(SUMPRODUCT(VALUE(MID($C2,ROW($1:$17),1)),$B$2:$B$18),11)+1,1),"长度错误")

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

判断是否合法

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

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

其中,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)

Excel中检测输入的身份证号码是否合法 函数校验方法大揭秘

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

本文地址:http://www.kaixinit.com/info/office/1258.html
版权声明:本文为原创文章,版权归 kaixinit 所有,欢迎分享本文,转载请保留出处!

发表评论


表情