Excel罕见高招:[5]验证身份证号码校验码真伪
我们知道第二代身份证号码最后一位,是校验码。本系列最后一篇文章,分享给大家校验码的生成公式,以及如何通过Excel,自动判断校验码的真伪,从而间接判断身份证号码的真假。
分步得到身份证号码的校验码
- 01
开始之前,我们了解一下二代身份证号码中校验码生成过程中,所使用的算法原理。 算法: 1、身份证号码的前17位号码,各位号码分别乘以不同的权重,然后得到加权的和。 2、将加权和,取模11的余数(加权和除以11,得到余数) 3、根据余数,检索对应表 4、得到校验码。
- 02
例如,身份证号码:420181199510040436 1、前17位号码:4,2,0,1,8,1,1,9,9,5,1,0,0,4,0,4,3 分别乘以不同的权重7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2 然后得到加权的和248。 2、将加权和,取模11的余数6 3、根据余数,检索对应表 4、得到校验码6。 该校验码确实等于该身份证号码的最后一位是6
- 03
我们来看看用Excel公式如何计算校验码。 首先,我们使用数组公式,来计算加权和。 在单元格C2输入数组公式 =SUMPRODUCT(VALUE(MID($A2,ROW($1:$17),1)),B$2:B$18) 注意, 1、输完公式后,用同时按下Ctrl+Shift+Enter,才能被Excel识别为数组公式。 2、可以看出Excel中数组公式显示为,一对大括号括住(但输入时不需要输入这对大括号)的公式
- 04
接下来,在单元格D2中,取加权后除以11后(模11)的余数。 取余数可以直接使用Excel中的函数MOD。 为了不依赖于上一步得到的值,我们在公式中不使用单元格引用,而是直接使用数组公式(同时按下快捷键Ctrl+Shift+Enter) =MOD(SUMPRODUCT(VALUE(MID($A2,ROW($1:$17),1)),B$2:B$18),11)
- 05
得到余数后,我们通过Excel中查询函数VLOOKUP,来查询相应的校验码。 同样,在单元格E2直接使用数组公式(同时按下快捷键Ctrl+Shift+Enter) =VLOOKUP(MOD(SUMPRODUCT(VALUE(MID($A2,ROW($1:$17),1)),B$2:B$18),11),H:I,2,FALSE)
直接得到身份证号码的校验码(集成公式)
- 01
为了保持Excel的简洁高效,我们希望化简一下公式,能够直接快速得到身份证号码的校验码。 不仅如此,要求还要更严格一点: 不在原表建立权重表,及校验码对应表。 也不使用数组公式。 方法: 直接在单元格B2,输入普通公式: =CHOOSE(MOD(SUM((MID(A2,1,1)+MID(A2,11,1))*7+(MID(A2,2,1)+MID(A2,12,1))*9+(MID(A2,3,1)+MID(A2,13,1))*10+(MID(A2,4,1)+MID(A2,14,1))*5+(MID(A2,5,1)+MID(A2,15,1))*8+(MID(A2,6,1)+MID(A2,16,1))*4+(MID(A2,7,1)+MID(A2,17,1))*2+MID(A2,8,1)+MID(A2,9,1)*6+MID(A2,10,1)*3),11)+1,1,0,"X",9,8,7,6,5,4,3,2) 然后往下拉,即可迅速计算剩下的身份证号码的校验码。
使用校验码验证身份证号码的真伪
- 01
最后,我们将上面的校验码公式,集成一下,放到最终的身份证号码验证表格中。 如果公式计算的校验码,与身份证号码的实际最后1位号码相同,则验证通过,否则为假。 注意,一代身份证,只有15位,无校验码,所以不可校验。