利用Excel公式将15位身份证号转换成18位，你尝试过吗？

利用Excel公式将15位身份证号转换成18位，你尝试过吗？

今天上班时有一项工作，具体来说就是需要把一些15位的老身份证号码维护成18位，我们毕竟不是公安部门，不能直接从户籍系统调取，所以只能自行想办法解决，不过好在数据量不大，通过各种方法顺利完成了工作，但完成工作之余我想，如果数据量很大怎么办，一定会有方法可以快捷高效的进行转换，所以我决定尝试研究一下。

要把15位身份证号码转换成18位号码，首先需要搞清楚15位号码与18位号码差别在哪里，这个大家应该都知道，差的3位中，其中2位是年份，即“19”，而第3位，也就是18位身份证的最后一位，是校验位。

现存的15位身份证号码都是2000年之前签发的，所以可以直接将“19”加入到15位号码中，形成“XXXXXX19XXXXXXXXX”这样的17位号码，所以我们需要做的就是确定最后一位校验位。

校验位如何确定，我们可以查询一下，通过百度我们可以知道，将17位号码从第1位至第17位分别乘以对应系数7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2，然后结果相加除以11，得出余数，这个余数只可能是0、1、2、3、4、5、6、7、8、9、10中的一个，而这11个数分别对应的身份证校验位为1、0、X、9、8、7、6、5、4、3、2，至此，我们已经完全了解了转换的规则，下一步就是具体选择工具、设计思路等具体工作了。

工具我选择使用Excel，原因有二，一是工作中常用，对这个比较熟悉，二是Excel本身功能也非常强大，完全可以满足需要。当然也可以使用一些软件编程进行转换，作为业余爱好曾经自学过按键精灵，我相信我也可以使用按键精灵做出来，但是毕竟是曾经，做起来肯定没有Excel这么方便直接，做什么都得讲一个效率不是。

至于思路，大致是下面这个顺序，最终目标是除第一步外，其余全部自动完成。

手动输入15位号码

自动加入年份“19”

将17位号码拆分

分别乘以系数后求和

求余数

根据余数判断返回检验位

合并检验位，形成18位号

具体操作步骤：

1、在B2单元格填入需要转换的15位身份证号码。

2、在B4单元格中使用replace函数加入年份“19”。replace函数是替换函数，以B4单元格为例，它的格式是“=REPLACE(B2,7,0,19)”，括号里的4个值得意义分别是需要替换的位置是B2单元格、从B2单元格中第七个字符开始替换、替换的字符数量为0、用于替换的值是19。

3、使用MID函数将B4单元格中生成的17位号码分别拆分到C6到S6单元格中。MID函数是一个提取指定字符的函数，以C6单元格为例，它的格式是=MID(B4,1,1)，括号里的3个值得意义分别是需要拆分的位置是B4单元格、从第1个字符开始、提取1个字符。那么D6单元格的格式是=MID(B4,2,1)，括号里的3个值得意义分别是需要拆分的位置是B4单元格、从第2个字符开始、提取1个字符，以此类推。图片3中公式B4前加了“$”，是绝对引用的意思，是因为我是拖拽填充的公式，不绝对引用B4单元格的话就会出现错误，如果从C6到S6单元格一一设置MID公式的话可以不用，就是能麻烦一些。

4、拆分后的数值分别乘以对应系数，求和。这个没什么可说的了，比较简单。

5、在C16单元格中使用MOD函数求余数，以C16单元格为例，它的格式是=MOD(C12,C14)，括号里的2个值得意义分别是被除数是C12单元格、除数是C14单元格，因为我们知道除数是固定值11，所以也可以将C14直接写成11。上一步求和是259，259除11得23余6，所以C16单元格公式结果为6。

6、使用IF函数，根据得到的余数判断返回对应校验位。它的格式是IF(条件，条件成立则返回值1，条件不成立则返回值2)，白话来说就是如果这个条件成立，那么返回值1，否则返回值2。如果遇到比较复杂的条件时，可以嵌套多个IF函数，即如果这个条件成立，那么返回值1，否则如果……。以C18单元格为例，它的公式为=IF(C16=7,5,IF(C16=0,1,IF(C16=1,0,IF(C16=2,X,IF(C16=3,9,IF(C16=4,8,IF(C16=5,7,IF(C16=6,6,IF(C16=8,4,IF(C16=9,3,IF(C16=10,2,"无数据")))))))))))，解读一下就是如果C16=7，那么返回值为5，否则如果C16=0，那么返回值为1，否则如果C16=1，那么返回值为0，否则如果C16=2，那么返回值为X，否则如果……，知道C16=10，那么返回值为2，否则返回值为“无数据”，这个无数据是为了满足公式条件，实际上是不会出现返回值为“无数据”的情况的。