使用Excel验证身份证号校验码的设计方法

(天津海运职业学院，天津 300350)

一、身份证号校验码的计算规则

根据【中华人民共和国国家标准 GB 11643-1999】中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。出生日期码表示编码对象出生的年、月、日，按GB/T7408的规定执行，其中年份用四位数字表示，年、月、日之间不用分隔符。顺序码表示同一地址码所标识的区域范围内，对同年、月、日出生的人员编定的顺序号。顺序码的奇数分给男性，偶数分给女性。校验码是根据前面十七位数字码，按照ISO 7064:1983.MOD 11-2的校验码规则计算出来的检验码。下面举例说明该校验码计算方法。

某男性公民身份号码17位本体码为12010519800101001，首先按照公式⑴计算：

(1)

公式(1)中：

i—表示号码字符从右至左包括校验码在内的位置序号；Ai—表示第i位置上的号码字符值；Wi—表示第i位置上的加权系数，其数值依据公式Wi=2(i-1)(mod 11)计算得出。

i181716151413121110987654321Ai12010519800101001A1Wi7910584216379105842Ai×Wi71805020294800905002

根据公式(1)进行计算：

∑(ai×Wi) =(7+18+0+5+0+20+2+9+48++0+0+9+0+5+0+0+2) = 125

125 ÷ 11 = 11 余数为4

∑(ai×Wi)(mod 11) = 4

然后根据计算结果的余数，从下面的表中查出相应的校验码，其中X表示校验码为10，因为每个人的身份证号码都是18位，所以10只能用罗马数字X表示。

计算结果的余数012345678910得到的校验码10X98765432

根据上表，查出计算结果为4对应的校验码为8，所以该人员的公民身份证号码应该为 120105198001010018。

二、教学设计的实现过程

(一)加权系数的计算

1.如图1中所示，在B20单元格内输入2，在B19单元格内输入公式“=B20*2”，然后拖动B19单元格右下角的填充柄向上复制公式到B18:B4，利用公式中相对地址变化就可以计算出2(i-1)。

2.在C20单元格内输入公式“=MOD(B20,11)”,也可以用插入菜单——函数来实现，然后拖动C20单元格右下角的填充柄向上复制公式到C19:C4，利用公式中相对地址变化就可以计算出加权系数Wi=MOD(2(i-1),11),其中i=表示2到18。这样实现身份证号码对应加权系数的自动生成不需要死记硬背。

(二)身份证号码与加权系数分别乘积之和

在E20单元格内输入公式“=SUMPRODUCT(C4:C20,D4:D20)”， 利用SUMPRODUCT函数求身份证号码与对应加权系数乘积之和。

也可以在E20单元格内利用插入菜单——函数SUMPRODUCT的对话框来实现，如图2所示。

图1 身份证号校验位计算

图2 插入函数SUMPRODUCT界面

(三)求模取余计算

在F20单元格内输入公式“=MOD(E20,11)”得到计算出的余数。

(四)定义余数与身份证校验码对应关系

1.在K4:L13单元格区域内输入余数与身份证校验码对应关系，实现反向移位2位的校验方法。

2.选中K4:L13单元格区域，利用菜单插入——名称——定义的对话框来实现，定义选中的单元格名称为AAA，名称定义可以实现公式复制中的绝对地址保持不变。

(五)求出校验码

在F21单元格中输入公式“=VLOOKUP(F20,AAA,2,TRUE)”求出身份证号码的校验位值。也可以在F21单元格内利用菜单插入-函数VLOOKUP来实现，如图2所示。利用VLOOKUP找到余数对应的校验码。

图3 单元格区域名称

图4 插入函数VLOOKUP界面

(六)身份证号码的正确性检验

在E7单元格中输入IF函数，“=IF($D$21=$F$21,“您输入的身份证号码:正确”,“您输入的身份证号码:错误”)”，这样就可以利用IF函数判断输入身份证号码是否正确。

(七)身份证号校验位计算

在D4:D21单元格区域输入身份证号18位,在F21单元格就可以计算出身份证号的校验位值了，并且能判定你输入身份证号码是否正确。

三、所用Excel函数介绍

MOD函数：求模取余，返回两数相除的余数。SUMPRODUCT函数：返回相应数组或区域乘积之和。VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。IF函数：执行条件判断，根据逻辑计算的真假值，返回不同结果，可以使用函数 IF 对数值和公式进行条件检测。

四、结束语

在本教学设计中巧妙利用了常见的身份证号校验位计算做为实践教学目标，能吸引学生的学习兴趣，综合运用了公式计算中相对地址与绝对地址、MOD、SUMPRODUCT、VLOOKUP、IF等函数、公式复制、单元格区域名称定义等学习内容。经过笔者在课堂实践教学中应用充分调动了学生学习Excel较复杂功能的热情，取得了良好的教学效果。举一反三，本教学设计思路同样可以应用在集装箱箱号核对数字计算和条形码校验位计算上。

参考文献：

[1]肖波.在EXCEL中添加身份证号码校验功能[J].长沙大学学报，2004,(04).

[2]杨润标.用Excel生成身份证号码校验码[J].电脑知识与技术,2010,(22).