王红然 王书旺

摘要 基于公民身份证编码规则，本文主要介绍了利用Excel函数提取个人基本信息的方法，对身份证号码有效性的验证进行案例研究，并进一步探讨防止身份证号码输入错误的具体方法。

【关键词】Excel函数 身份证号码 数据有效性

在用Excel进行个人基本信息录入时，除了姓名、性别、出生日期外，身份证号码也是至关重要的内容。但是由于各种原因，经常会出现身份证号码位数不对、性别或出生日期与身份证号码对应信息不一致的情况。Excel函数为我们提供了解决这类问题的有效途径。

1 个人基本信息的提取

1.1 出生日期的提取

出生日期位于身份证号码的7-14位，通常需要使用字符串提取函数来取出这8位。Excel中常用的字符串提取函数有LEFT、RIGHT和MID。其中LEFT是从左边提取，RIGHT是从右边提取，而MID是从中间提取。出生日期位于身份证号码的中间位置，因此用MID函数来提取。假设身份证号码位于B2单元格，出生日期位于D2单元格，可在D2单元格输入公式“-MID（B2，7，8）”，得到的结果是文本格式。为了便于后续数据处理，可以先将年月日分别提取出来，然后用DATE函数组合成日期格式，即在D2单元格输入公式“=DATE（MID（B2.7，4），MID（B2.11.2）.MID（B2，13，2》”。

1.2 性别的提取

根据身份证编码规则，顺序码的奇数分配给男性，偶数分配给女性。顺序码位于身份证号码的第15-17位，而判断一个数的奇偶性只需要判断该数个位的奇偶性。因此，根据身份证号码的第17位就可以判断性别。先用MID函数提取身份证号码的第17位，然后用ISODD函数判读该位的奇偶性，最后用IF函数判断性别。假设性别位于C2单元格，可在C2单元格输入公式“=IF（ISODD（MID（B2，17，1》，“男”，“女”）”。

1.3 身份证地址的提取

身份证地址对应的是身份证号码的地址码，而地址码位于身份证号码的前6位，要取出这6位可以用MID函数，也可以用LEFT函数。假设身份证地址位于E2单元格，可在E2单元格输入公式“-LEFT（B2，6）”。得到的结果是6位地址码，要想得到身份证地址信息，需要查询身份证地址码对照表。假设身份证地址码对照表位于另一张名为“地址码对照表”的工作表，地址码和地址的数据范围为A2：B3466，可在E2单元格输入公式“-VLOOKUP（LEFT（B2，6），地址码对照表！A2：B3466，2，0）”。输入测试身份证号码，提取结果如图1所示。

2 身份证号码的有效性验证

根据[中华人民共和国国家标准GB11643-1999]及“IS0 7064：1983.MOD 11-2校验码计算法”，位于身份证号码第18位的校验码是根据前面17位数字码按照一定的算法生成的。如果通过Excel函数计算得到的校验码和从身份证号码中提取出的校验码一致，则该身份证号码就是一个有效的号码。

2.1 身份证校验码的计算方法

基本的身份证校验码计算方法如下：

（l）将身份证号码的前17位数分别乘以不同的系数，第1-17位的系数分别为：7 9 105 842163 7910 5 8 42;

（2）将得到的17个乘积相加;

（3）将相加后的和除以11并得到余數;

（4）余数有11种情况：0123456789 10，它们对应的身份证最后一位校验码为1OX98765432。

2.2 基于Excel函数的身份证校验码计算

根据上述身份证校验码的计算方法，用Excel函数进行计算。

第一步，新建一个工作表，设计工作表结构如图2所示。其中，A列为序号，B列为身份证号码，C-T列分别为身份证号码的第1-18位，U列为身份证校验码计算结果，V列显示身份证是否有效。为了便于处理，将第1-17位的系数放在C14：S14中，将乘加结果放在X列，将取余结果放在Y列，将余数和校验码对照表放在AA2：AB12中。

第二步，取出身份证号码的第1-17位。为了统一处理，这里采用MID函数。在C2单元格输入公式“-MID（A2，1，1）”，得到的结果是文本型。为了便于后面自动填充，可将第一个参数地址改成引用A列，第二个参数地址改成引用第一行，即把公式改为“-MID（$A2，B$1，1）”。为了下一步进行乘加运算，需要用VALUE函数将该结果转换成数值型，即把公式改为“-VALUE（MID（$A2，B$1，l》”。向右填充到S2即可得到身份证号码的第1-17位。

第三步，取出身份证号码的第18位。该位是身份证号码的最后一位，而且可能含有字符，也不需要参与乘加运算，因此不需要用VALUE函数，只用RIGHT函数即可，即在T2单元格输入公式“-RIGHT（B2，1）”。

第四步，乘加运算，即将身份证号码的前17位分别乘以相应的系数，然后将得到的17个乘积相加。这里可将身份证号码的前17位看作一个数组，这17位对应的系数看作另一个数组，显然，这两个数组的维数是相同的。因此，可以用数组公式实现乘加运算，即在X2单元格输入公式“=SUMPRODUCT（B2：R2.B14：R14）”， 为了后面能自动填充，可将系数数组地址改为绝对引用，即把公式改为“-SUMPRODUCT（B2：R2，$B$14：$R$141。

第五步，取余运算。用MOD函数将乘加运算的结果对II取余，即在Y2单元格输入公式“=MOD（X2，11）”。

第六步， 计算校验码。 用VLOOKUP函数根据取余运算的结果计算校验码，即在U2单元格输入公式“=VLOOKUP（Y2，AA2：AB12，2.0）。为了后面能自动填充，可将校验码对照表地址改为绝对引用，即把公式改为“=VLOOKUP（Y2，$AA$2： $AB$12，2，0）”。 由于身份证号码第18位的计算结果是文本格式，为了下一步能进行比较，需要把校验码改成文本格式，可用连接符“&”连接一个空字符串，即把公式改为“=VLOOKUP（Y2，$AA$2：$AB$12，2，0）&……”。

2.3 身份证号码的有效性验证

将计算得到的校验码和身份证号码的第18位进行比较，如果二者一致，则该身份证号码就是有效的。可以用IF函数来实现，即在Y2单元格输入公式“-IF（T2-U2，”有效”，”无效”）”。为了突出显示无效结果，可在无效后加“×”，即把公式改为“=IF（T2=U2，”有效”，”无效×”）”。输入一个测试身份证号码，验证结果如图3所示。

3 防止身份证号码输入错误的方法

身份证号码比普通数字位数多，难以记忆，很容易输入错误。除了在输入时细心外，还可以借助Excel提供的各种功能来防止身份证号码输入错误。

3.1 设置单元格格式

直接输入身份证号码经常会以科学计数法显示，可在输入前将需要输入身份证号码的单元格设置成文本格式，数据量较少时可在输入身份证号码前输入一个半角的单引号…。

3.2 限制文本长度

目前，我国公民身份证号码统一为18位。可利用Excel提供的數据有效性功能限制输入文本的长度。

3.3 避免重复输入

有的身份证号码非常相似，输入错误后不容易发现。可利用Excel提供的数据有效性功能避免重复输入。假设身份证号码位于B2单元格，在输入前选中该单元格，设置数据有效性，在数据有效性窗口的公式栏输入公式“=COUNTIF（B：B，B2）=1”。其他需要设置数据有效性的单元格可以用选择性粘贴功能，粘贴选项选择“有效性验证”项。

3.4 录入后检查

如果在设置数据有效性之前已经录入了部分身份证号码，可在录入完成后选中身份证号码区域，设置条件格式，规则类型选择“仅对唯一值或重复值设置条件格式”。

4 结论

掌握了身份证号码的秘密和相关的Excel函数，就可以利用Excel函数来进行个人信息处理，这样不仅减少了大量的数据录入，还避免了因身份证号码错误带来的诸多问题。其实，生活中有很多证件和卡片的编码都是有一定规律的，只要我们掌握了这个规律，找到了编码规则，就可以利用Excel函数来帮助我们提高工作效率。

参考文献

[1]闫治良，王宇晨，利用Excel函数判断学生身份证信息的准确性[J].电脑编程技巧与维护，2016 （03）：47-48.

[2]申习身，使用Excel验证身份证号校验码的设计方法[J].天津职业院校联合学报，2014 （02）：65-67.

[3]陈大银，基于Excel的身份证号码中信息提取的设计[J].滁州职业技术学院学报，2012 （02）：56-57.