巧用Excel函数校验身份证

摘 要本文介绍了我国身份证号的编制规则，结合实例详细阐述了在Excel中利用函数校验身份证号的方法，并对所用到的函数做了详细说明。

【关键词】Excel 身份证号 函数

在各类包含人员信息的表格中，身份证号是一个很重要的数据，本文详细介绍了利用Excel函数对身份证号进行校验的方法，具有很强的实用价值。

我国居民身份证的号码是按照国家的标准编制的，由18位组成，排列顺序从左至右依次为：6位数字地址码，8位数字出生日期码，3位数字顺序码和1位数字校验码。在身份证号编制规则中，最后一位就是起校验作用的。校验规则是将身份证号前17位取出，分别乘以相应加权因子再求和得到S，用S除以11求得余数Y，再经比对校验码对应表得到身份证号的第18位数字。校验方法即将计算得到的校验码与原身份证号的第18位比对，一致即通过验证，否则身份证号错误。

通过这样初步的校验可以有效避免输入方面的错误，甚至可以识别部分伪造的身份证。下面举例分步实现。

1 加权求和

首先需要提取身份证号的前17位数字，这一功能可以用MID函数实现：

MID（单元格，起始位置，字符长度）：从文本字符串中指定的起始位置起返回指定长度的字符。

利用MID函数提取出的数字需要加权求和。加权数如表1所示。

若身份证号所在单元格为D3，则表达式可写为：

=MID（D3，1，1）*7+MID（D3，2，1）*9+MID（D3，3，1）*10+MID（D3，4，1）*5+MID（D3，5，1）*8+MID（D3，6，1）*4+MID（D3，7，1）*2+MID（D3，8，1）*1+MID（D3，9，1）*6+MID（D3，10，1）*3+MID（D3，11，1）*7+MID（D3，12，1）*9+MID（D3，13，1）*10+MID（D3，14，1）*5+MID（D3，15，1）*8+MID（D3，16，1）*4+MID（D3，17，1）*2

函数会返回加权后的和值S。

2 求余数

在Excel中求取余数可用MOD函数实现：

MOD（被除数，除数） ：返回两数相除的余数。

将第一步的求和结果嵌套在MOD函数中作为参数，表达式为：

=MOD（MID（D3，1，1）*7+MID（D3，2，1）*9+… +MID（D3，17，1）*2）

函数会返回和值S除以11的余数。

3 查找校验码

求得余数之后需要查询对应校验码，将余数与校验码对应关系做成一个表格存储在另一个工作表中，工作表命名为“校验数据”，如图1所示。

在这个工作表中根据余数查找校验码可以利用VLOOKUP函数实现：

VLOOKUP（线索，查找区域，目标列，逻辑值）：搜索查找区域中首列满足条件的元素，确定待检索单元格在区域的行序号，再进一步返回该行中目标列的值。

根据函数使用规则，第一个参数在本例中可以确定为之前步骤返回的余数，查找区域为“校验数据”工作表的D2至E12区域，引用方式可表述为：

校验数据！$D$2：$E$12

第三个参数为查找区域目标所在列，本例中是2，最后一个参数逻辑值的意义为是否启用模糊查找，本例中不需要启用，直接填0或FALSE即可。因此综合表达式为：

=VLOOKUP（MOD（MID（D3，1，1）*7+MID（D3，2，1）*9+… +MID（D3，17，1）*2），校验数据！$D$2：$E$12，2，0）

函数会返回比对后的校验码。

4 比对验证

最后一步需要将得到的校验码与身份证号第18位比较，检查是否一致。检查一致性的功能可以用EXACT函数实现：

EXACT（字符串1，字符串2）：比较两个字符串是否完全一致，一致返回TRUE，不一致返回FALSE。

利用EXACT函数比对得到的校验码与身份证号第18位，表达式可写为：

=EXACT（MID（D3，18，1），VLOOKUP（MOD（MID（D3，1，1）*7+MID（D3，2，1）*9+… +MID（D3，17，1）*2），校验数据！$D$2：$E$12，2，0））

函数会返回比对后的最终结果，结果为TRUE的表示身份证号通过校验，结果为FALSE的表示身份证号有错误。

以上就是在Excel中校验身份证号的方法，希望对大家的工作、学习有所启发。

参考文献

[1]肖凤亭，王云沼.计算机应用基础[M].北京：机械工业出版社，2012.

[2]李继兵.Excel公式与函数应用范例[M].北京：中国青年出版社，2005.

作者简介

孟宪宇（1987-），男，河北省保定市人。大学本科学历。现为武汉军械士官学校指挥控制系军事信息教研室助教。研究方向为计算机软件及网站开发。

作者单位

武汉军械士官学校指挥控制系军事信息教研室 湖北省武汉市 430075