用Excel函数公式升位15位、校验18位居民身份证号码

提醒：本页面将不再更新、维护或者支持，文章、评论所叙述内容存在时效性，涉及技术细节或者软件使用方面不保证能够完全有效可操作，请谨慎参考！

我在培训班讲课的内容，在这里再简单的说一下：

根据国标，18位身份证号码的最后一位是校验码，这位校验码是由前面17位数字经过数学运算得出来的，通过这位校验码，可以检测出18位数字是否存在录入错误，具体的算法如下：

1. 17位数字自身加权求和，公式是S=SUM(Ai*Wi), (0<i<18)这里的i也就是第1~17位的索引，比如A1就是第1个数字，依次类推。Wi是加权因子，对应的也是17位，数值是{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2}，也就是说身份证号码前17位依次与这里17位相乘，最后求和，求和的结果为S。

2. 求余（求模），将S与11求模。Y=MOD(S,11)，得到Y。

3. 通过Y的值查找表{1,0,X,9,8,7,6,5,4,3,2}，最后得到校验位，为什么会有X，X是罗马数字10，因为10是两位，所以只好用X代替，但是不要认为带有X的身份证号码是错误的哦，还有不要将身份证号码字段设计成数字的哦。好，有了上面的算法，简单的来说Excel函数也就知道怎么写了：

MID(A1, 1, 1) * 7
MID(A1, 2, 1) * 9
MID(A1, 3, 1) * 10
MID(A1, 4, 1) * 5
MID(A1, 5, 1) * 8
MID(A1, 6, 1) * 4
MID(A1, 7, 1) * 2
MID(A1, 8, 1) * 1
MID(A1, 9, 1) * 6
MID(A1, 10, 1) * 3
MID(A1, 11, 1) * 7
MID(A1, 12, 1) * 9
MID(A1, 13, 1) * 10
MID(A1, 14, 1) * 5
MID(A1, 15, 1) * 8
MID(A1, 16, 1) * 4
MID(A1, 17, 1) * 2

这里依次取得17位每一位的加权值，然后用SUM进行求和，求和的值还要与11求模。

=MOD(SUM(MID(A1, 1, 1) * 7,MID(A1, 2, 1) * 9,
MID(A1, 3, 1) * 10,MID(A1, 4, 1) * 5,
MID(A1, 5, 1) * 8,MID(A1, 6, 1) * 4,
MID(A1, 7, 1) * 2,MID(A1, 8, 1) * 1,
MID(A1, 9, 1) * 6,MID(A1, 10, 1) * 3,
MID(A1, 11, 1) * 7,MID(A1, 12, 1) * 9,
MID(A1, 13, 1) * 10,MID(A1, 14, 1) * 5,
MID(A1, 15, 1) * 8,MID(A1, 16, 1) * 4,
MID(A1, 17, 1) * 2), 11)

最后查找表{1,0,X,9,8,7,6,5,4,3,2}得到校验位，用CHOOSE函数，注意CHOOSE索引是从1开始的，所以我们需要加上1。

=CHOOSE(MOD(SUM(MID(A1, 1, 1) * 7,MID(A1, 2, 1) * 9,
MID(A1, 3, 1) * 10,MID(A1, 4, 1) * 5,
MID(A1, 5, 1) * 8,MID(A1, 6, 1) * 4,
MID(A1, 7, 1) * 2,MID(A1, 8, 1) * 1,
MID(A1, 9, 1) * 6,MID(A1, 10, 1) * 3,
MID(A1, 11, 1) * 7,MID(A1, 12, 1) * 9,
MID(A1, 13, 1) * 10,MID(A1, 14, 1) * 5,
MID(A1, 15, 1) * 8,MID(A1, 16, 1) * 4,
MID(A1, 17, 1) * 2), 11)+1, 1,0,"X",9,8,7,6,5,4,3,2)

好， 下面为了叙述方便，我将上面公式简写为 K() ，那么15位升18位就可以写成这样：

=MID(A1, 1, 6) & "19" & MID(A1, 7, 15) & K()

对于文本叙述正确还是错误可以这样：

=IF(K()=UPPER(MID(A1,18,1)), "正确", "错误")

这里需要取出18位身份证号码的最后一位，当然为了兼容大小写X，还要大小写转换一次，利用UPPER函数。

好，到这里应该算是结束了，但是有童鞋会问，哇，这个公式好长哎，有没有办法简化一下呢？本人Excel公式属于弱项，不过我在网上找到一位高手的Excel公式，简短很多，而且功能一点也不弱，建议使用，K()公式如下：

=MID("10X98765432",1+MOD(SUM(MID(C4,ROW($1:$17),1)*2^(18-ROW($1:$17))),11),1)

怎么样，不愧是牛人，据说用到了点数学知识，目前我还看不出什么名堂，有知道的朋友可以说一下。

2 thoughts on this post