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

!本文可能 超过1年没有更新,今后内容也许不会被维护或者支持,部分内容可能具有时效性,涉及技术细节或者软件使用方面,本人不保证相应的兼容和可操作性。

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

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

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

若无特别说明,本网站文章均为原创,原则上这些文章不允许转载,但是如果阁下是出于研究学习目的可以转载到阁下的个人博客或者主页,转载遵循创作共同性“署名-非商业性使用-相同方式共享”原则,请转载时注明作者出处谢绝商业性、非署名、采集站、垃圾站或者纯粹为了流量的转载。谢谢合作!

    • @shirne
      最后那个貌似用到了数学知识,由于数学很菜,我也搞不明白。不过实际应用发现有个缺陷,就是同一列利用格式刷校验完成后,只要删除任意一行,就会导致其余行校验错误,需要重新设置公式进行校验。

请稍后...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*