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

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

根据国标,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)

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