刘若冰

【摘要】企事业单位的员工信息表中必不可少的一项数据是身份证号，员工在输入数据的过程中难免会出现输入错误，使用人工排查错误无疑是项繁重的任务，而使用Excel中的函数，例如MID，COLUMN，VLOOKUP，MOD，SUM，IF等可以较为容易的进行错误排查。

【关键词】身份证号  余数校验  MID  VLOOKUP  MOD  SUM  IF

1.引言

企事业单位的人事部门通常要统计员工的各种信息数据，其中身份证号是必须要统计的一项重要数据，但是员工在输入数据的过程中难免会出现输入错误，如果想要人工检查出数据中的错误必然是项很繁重的工作，不过这项工作可以使用Excel中的函数，例如MID，COLUMN，VLOOKUP，MOD，SUM，IF等函数来完成。而想要使用函数完成这项信息核对工作，就一定要了解身份证号中的数字所指代的信息。

2.身份证号的结构

2.1身份证号蕴含的信息

每个中国公民自出生之日起就拥有一串独一无二的、由十八位数字组成的编码用来标示自己的身份，就是身份证号码。这十八位数字钟包含了每个人的出生地区、出生日期以及性别等信息。

其中的第一到六位数字表示出生所在地区，第七到十四位数字表示出生年份、月份和日子，第十五到十七位数字是对同年、月、日出生的人员编定的顺序号，第十七位用来表示性别，奇数为男性，偶数为女性，最后一位是数字校验码，用来校验前十七位数字是否正确。

2.2校验方法

第一步，将身份证号的前十七位，每一位分别乘以不同的校对系数，然后相加求和。

第二步，将第一步得到的和除以11，计算余数。

第三步，在余数和校验码的对应关系表中查找余数对应的校验码，和员工输入的身份证号第十八位进行比对，如果一直则输入正确，否则输入有误，需要修改。

3.身份证校验步骤

在进行校验之前，假定员工输入的身份证号码为37320619980124001X，放在工作表“身份证校对”的C3单元格内，将校对系数放在工作表“校对参数”的E4：U4区域内，将校验码放在工作表“校对参数”的B4：C14区域内。

3.1逐位提取身份证号中的数字

首先需要在工作表“身份证校对”中，将C3单元格内的身份证号的第一位到第十八位分别提取出来，逐次放在单元格D3到U3中。需要使用MID函数，MID函数有三个参数，此时的第一个参数是假定条件中的身份证号所在单元格C3，第二个参数需要分别填写数字1到18，第三个参数是指定长度1。此时D3单元格内的公式为：=MID（C3，1，1），其中只有第二个参数是会发生变化的，这个变化可以使用另一个函数COLUMN来实现，COLUMN的作用是返回单元格所在的列号，那么我们可以用单元格A1到R1分别代替数字1到数字18，此时D3单元格内的公式变为：=MID（C3，COLUMN（A1），1）。确定了D3单元格内的公式后，逐列向右，一直到U3单元格，可以采用自动填充的方式填充公式，但是，在公式填充的过程中公式内的单元格地址C3、A1会发生变化，逐列变为D3、B1，E3、C1……，而此时MID函数的第一个参数应该一直是单元格C3，不能发生变化，所以需要使用$将C3单元格固定住，此时D3单元格内的公式变为：=MID（$C3，COLUMN（A1），1），然后使用自动填充功能计算其他单元格内的数据即可，稍后用来比较校验结果的第十八位U3单元格内为X。

3.2计算校验码

在工作表“身份证校对”中，使用身份证号的第一位到十七位计算校验码，放在V3单元格内。需要用到三个函数，分别是VLOOKUP、MOD和SUM函数。

首先，在单元格V3内需要计算单元格区域D3：T3和工作表“校对参数”的单元格区域C4：C14逐次相乘、相加之和，此时，单元格V3内的公式为：=SUM（D3：T3*校对参数！E4：U4）。此时需要注意，由于SUM函数包含数组，所以需要按下ctrl+shift+enter组合键才能计算出正确结果，结果为134。

第二步，使用MOD函数对SUM函数计算的和除以11，计算余数。此时的SUM函数变为MOD函数的第一个参数，MOD函数的第二个参数是数字11，此时，单元格V3内的公式为：=MOD（SUM（D3：T3*校对参数！E4：U4），11），结果为2。

第三步，使用VLOOKUP函数，通过MOD函数计算的余数在工作表“校对参数”B4：C14区域内查找对应的校验码。

VLOOKUP函数有四个参数，第一个参数是MOD函数，第二个参数是工作表“校对参数”中校验码所在的区域B4：C14，第三個参数是第二个参数中满足条件的值所在的列号，即2，第四个参数指定是否是精确匹配，由于不确定校验码区域B4：C14是否经过排序操作，所以采取模糊匹配，取值FALSE。

此时，单元格V3内的公式为：=VLOOKUP（MOD（SUM（D3：T3*校对参数！E4：U4），11），校对参数！B4：C14，2，FALSE），结果为X。

由于员工的数据信息可能比较多，通常不止一行数据，后续所在行需要使用自动填充功能，同样在公式填充过程中会导致单元格地址的变化，所以，需要将校对系数所在区域E4：U4，校验码所在区域B4：C14锁定。

最后，单元格V3内的公式为：=VLOOKUP（MOD（SUM（D3：T3*校对参数！$E$4：$U$4），11），校对参数！$B$4：$C$14，2，FALSE）。

3.3 比较校验结果

在工作表“身份证校对”的W3单元格内，比较计算得到的校验码和提取的第十八位身份证号，即单元格V3和U3是否相等，如果相等，返回结果“正确”，否则返回结果“错误”。需要使用IF函数，由于V3单元格内数据为数值，U3单元格内数据为文本，所以需要使用“V3&""”将V3单元格转变为文本数据，在W3单元格内输入公式：=IF（U3=V3&""，"正确"，"错误"），判断结果为正确。

4.总结

判断身份证号是否输入正确的过程中用到的函数有MID，COLUMN，VLOOKUP，MOD，SUM，IF，其中SUM虽然常用，但出现了数组计算，所以需要使用ctrl + shift + enter组合键确认输入，在公式自动填充过程中需要使用$将必需固定的单元格地址或者单元格区域固定。

参考文献：

[1]杨润标.用Excel生成身份证号码校验码.电脑知识与技术软.2010（6）.

[2]闫治良.王宇晨.利用Excel函数判断学生身份证信息的准确性电脑编程技巧与维护.2016（3）.

[3]李庆.身份证号在Excel中的巧妙运用.电脑知识与技术（经验技巧）.2018（9）.