Excel将15位身份证号码转为18位的方法探讨

Excel将15位身份证号码转为18位的方法探讨

缪景洪

云南省马龙县水务局云南马龙655199

摘要：本文从15位身份证号码的仍然大量存在的事实入手，紧紧围绕校验码字符值，利用Excel函数，巧妙构造校验码字符值的方法。从函数分解、公式原理等方面详细解析，从而得到15位身份证号码升为18位身份证号码的方法。

关键词：身份证世纪数校验码字符值

一、15位身份证号码转为18位的的意义

身份证是每个人身份的凭证，是公民进行社会活动、维护社会秩序、保障公民合法权益、证明公民身份的法定证件。第一代身份证是1985年开始使用，自2013年1月1日起停止使用，第二代证是2005年11月1日正式启用。从2005年起，我国启动第二代居民身份证换发工作，至2008年我国基本完成第二代居民身份证的换发。

在扶贫工作所接触的证件中，虽然第一代身份证已经停止使用，但也还有部分存在。有的贫困对象根本没有第二代身份证，更有极端的，有的连身份证都没有，仅仅是户口册上有个号码。除此之外，还有一部分的资格证书上的身份证号码是15位，但他本人的证号却是18位。那么，如何得到他的18位身份证号码的呢？为了得到他的18位身份证号码，可以到当地派出所查询，或者到网上进行转换，当上述路径行不通时，则可以用Excel进行转换，然后与现在的身份证号码进行对比，就能得到他的18位身份证号码。那么，如何转换？我们首先来看看身份证号码的结构。

二、身份证号码的结构

身份证是每个人身份的凭证，了解身份证号码的组成是很有必要。身份号码是特征组合码，第二代居民身份证号码共18位，由17位数字本体码和1位校验码组成。排列顺序从左至右依次为：前6位是地址码，第7-14位是生日期码，第15-17位是顺序码，第18位为校验码。顺序码的奇数分配给男性，偶数分配给女性。

第一代居民身份证只有15位。出生日期只有6位，没有数字校验码。前6位为地址码，第7-12为生日期码，第13-15位为顺序号，顺序码的奇数分配给男性，偶数分配给女性。

居民身份证的有效期限分为5年、10年、20年、长期四种。16岁以下的有效期为5年，16周岁至25周岁的为10年，26周岁至45周岁的为20年，46周岁以上的为长期。证件有效期限从签发之日起计算。

三、15位身份证号码转化为18位的方法

15位身份证号码转化为18位的方法，首先在旧身份证的6位至7位中间加入世纪数，再在最后增加一位校检码。其计算关键是如何计算最后1位校检码。

校验码的计算是采用国际标准化组织ISO订立的《ISO7064:1983》中的“MOD11-2”校验码系统。公民身份号码中各个位置上的号码字符值应满足下列公式的校验：

四、15位转化18位的算法实现

从身份证号码的规则中，我们就可以的出身份证的15位转化需要两步。首先把15位身份证号补全为17位，然后再补全最后一位。我们经过整理后可以得出身份证补全算法：第一步，将15位身份证号码加入出世纪数变为17位；第二步，将第一步得到的身份证17位数分别取出来；第三步，将这17位数字和加权因子相乘的结果相加；第四步，将求和的结果除以11得出余数，第五步，用上述所求的余数与校验码字符值对应换算关系得到检验码值，最后用“&”连接字符再将原来的17位号码和校验码进行字符串连接起来，就得到18位号码了。

上述步骤，可以利用Excel中REPLACE、MID、MOD、SUM、INDEX等函数来实现。

五、函数解析

利用Excel将15位身份证号码转为18位的方法，主要用到REPLACE、MID、MOD、SUM、INDEX等函数。它们的用法等如下。

REPLACE函数。使用其他文本串并根据所指定的字符数替换另一文本串中的部分文本。语法：REPLACE(old_text，start_num，num_chars，new_text)。参数：Old_text是要替换其部分字符的文本，Start_num是要用new_text替换的old_text中字符的位置，Num_chars是希望REPLACE使用new_text替换old_text中字符的个数。

MID函数。返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。语法：MID(text，start_num，num_chars)。参数：Text是包含要提取字符的文本串，Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推，Num_chars指定希望MID从文本中返回字符的个数；

MOD函数。返回两数相除的余数，结果的正负号与除数相同。语法：MOD(number,pisor)。参数：Number为被除数，pisor为除数。

SUM函数。返回某一单元格区域中所有数字之和。语法：SUM(number1，number2，…)。参数：Number1，number2，…为1到若干个需要求和的数值。

INDEX函数。返回表格或区域中的数值或对数值的引用。函数INDEX有两种形式：数组和引用。数组形式通常返回数值或数值数组，引用形式通常返回引用。

数组形式语法：INDEX(array，row_num，column_num)返回数组中指定的单元格或单元格数组的数值。参数：Array为单元格区域或数组常数，Row_num为数组中某行的行序号，函数从该行返回数值，如果省略row_num，则必须有column_num，Column_num是数组中某列的列序号，函数从该列返回数值，如果省略column_num，则必须有row_num。

引用形式语法：INDEX(reference，row_num，column_num，area_num)返回引用中指定单元格或单元格区域的引用。参数：Reference是对一个或多个单元格区域的引用，如果为引用输入一个不连续的选定区域，必须用括号括起来。Area_num是选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，则INDEX函数使用区域1。

六、将15位身份证转化为18位的方法

我们以Excel表为例，假设在A2处存放的是15位身份证号码，需要在B2处转换为18位身份证号码：

一、在15为身份证上，利用REPLACE函数插入年份的前面两位数“19”，变为17位数，函数表达式为“REPLACE(A2,7,,19)”。

二、用MID函数分别将17位数取出来，函数表达式分别为：“MID(REPLACE(A2,7,,19),1,1)”,“MID(REPLACE(A2,7,,19),2,1),“MID(REPLACE(A2,7,,19),3,1)”……“MID(REPLACE(A2,7,,19),17,1)”。

三、将这17位数字和系数相乘的结果相加，函数表达式为：“SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+...+MID(REPLACE(A2,7,,19),17,1)*2)”。

四、将求和结果求余，函数表达式为：“MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+...+MID(REPLACE(A2,7,,19),17,1)*2),11)”。

五、用上述所求的余数与校验码字符值匹配求出校检码，用INDEX函数取值来实现。公式变为：

“INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+...+MID(REPLACE(A2,7,,19),17,1)*2)，11))+1)”，这就可以求出效验码了。

我们来解读下这个公式：这个公式的第一个参数是数据区域，第二个参数是相对数据区域中的第几行，因为只有一行，所以我们填1，第三个参数是第几列，所取的值为上述第四步的求余值+1，为什么加1？因为上面的校验码字符值匹配区域的序列是从0开始的，所以要在原基础上加1。

六、之后用“&”连接字符再将原来的17位号码和校验码进行字符串连接起来，就变为18位号码了。公示表达式为：“(REPLACE(A2,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+...+MID(REPLACE(A2,7,,19),17,1)*2),11))+1))”。

最后为了说明公式是正确的，我们把公式放在下面的某个单元格中，已把最后的身份证号码显示出来为证：

当然，Excel在输入公式时不能有省略号“...”的，所以最后完整的公式是：=(REPLACE(A2,7,,19))&(INDEX({1,0,"X",9,8,7,6,5,4,3,2},1,(MOD(SUM(MID(REPLACE(A2,7,,19),1,1)*7+MID(REPLACE(A2,7,,19),2,1)*9+MID(REPLACE(A2,7,,19),3,1)*10+MID(REPLACE(A2,7,,19),4,1)*5+MID(REPLACE(A2,7,,19),5,1)*8+MID(REPLACE(A2,7,,19),6,1)*4+MID(REPLACE(A2,7,,19),7,1)*2+MID(REPLACE(A2,7,,19),8,1)*1+MID(REPLACE(A2,7,,19),9,1)*6+MID(REPLACE(A2,7,,19),10,1)*3+MID(REPLACE(A2,7,,19),11,1)*7+MID(REPLACE(A2,7,,19),12,1)*9+MID(REPLACE(A2,7,,19),13,1)*10+MID(REPLACE(A2,7,,19),14,1)*5+MID(REPLACE(A2,7,,19),15,1)*8+MID(REPLACE(A2,7,,19),16,1)*4+MID(REPLACE(A2,7,,19),17,1)*2),11))+1))

计算实例：假设在A2处存放的是15位身份证号码为“522634520829128”，需要在B2处转换为18位身份证号码，则计算结果为“522634195208291285”。

七、结语

第一代身份证虽然于自2013年停止使用，但是15位身份证号码的仍然大量存在，由15位身份证号码得到他的18位身份证号码仍然具有实用价值。本文紧紧围绕校验码字符值，利用Excel函数，巧妙构造校验码字符值的方法。从函数分析、公式步骤等方面详细解析，从而得到15位身份证号码升为18位身份证号码的方法。

参考文献：

1、《中华人民共和国居民身份证法》辽宁电子图书有限责任公司

2、《公民身份号码》GB11643-1999中华人民共和国国家标准

3、曹飞羽、王正旭《小学数学基础理论和教法》人民教育出版社

4、宋乃庆《初等数学选读》西南师范大学出版社

5、李敏《随手查——Excel函数与公式应用技巧》电子工业出版社

6、张迎新《Excel2003函数应用完全手册》

7、互联网上相关的各种方法、文章、论坛等