• 注册
  • 推广免费拿钻石

    • 查看作者
    • 用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校验身份证号码合法性的方法了,使用时可直接复制公式,修改系数所在区域和身份证号码所在单元格格即可。

    • 0
    • 0
    • 0
    • 2
    • 单栏布局 侧栏位置: