我在培训班讲课的内容,在这里再简单的说一下:
根据国标,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函数也就知道怎么写了:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | 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求模。
1 2 3 4 5 6 7 8 9 | =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。
1 2 3 4 5 6 7 8 9 | =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位就可以写成这样:
1 | =MID(A1, 1, 6) & "19" & MID(A1, 7, 15) & K() |
对于文本叙述正确还是错误可以这样:
1 | =IF(K()=UPPER(MID(A1,18,1)), "正确", "错误") |
这里需要取出18位身份证号码的最后一位,当然为了兼容大小写X,还要大小写转换一次,利用UPPER函数。
好,到这里应该算是结束了,但是有童鞋会问,哇,这个公式好长哎,有没有办法简化一下呢?本人Excel公式属于弱项,不过我在网上找到一位高手的Excel公式,简短很多,而且功能一点也不弱,建议使用,K()公式如下:
1 | =MID("10X98765432",1+MOD(SUM(MID(C4,ROW($1:$17),1)*2^(18-ROW($1:$17))),11),1) |
怎么样,不愧是牛人,据说用到了点数学知识,目前我还看不出什么名堂,有知道的朋友可以说一下。
最后那个公式吧,我始终没闹明白那里面sum怎么算的。
我也没试验成功.
excel很菜
@shirne
最后那个貌似用到了数学知识,由于数学很菜,我也搞不明白。不过实际应用发现有个缺陷,就是同一列利用格式刷校验完成后,只要删除任意一行,就会导致其余行校验错误,需要重新设置公式进行校验。