EXCEL中批量将15位身份证号码变更为18位身份证号码.doc
EXCEL中批量将15位身份证号码变更
为18位身份证号码
一、假设15位号码在C列,在D列中输入数组公式(公式输入后须同时
按下Ctrl+Shift+Enter组合键方可完成):
=IF(LEN(D6)=15,REPLACE(D6,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(D6,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),D6)
二、15位的老身份证号码插入年份即可升到17位,假设15位号码在C
列,在D列输入公式=IF(LEN(C6)=15,MID(C6,1,6)&"19"&MID(C6,7,9))就可以在年份前添加19。
假设17位号码在C列,在D列中输入数组公式(公式输入后须同时按下
Ctrl+Shift+Enter组合键方可完成):
=C6&LOOKUP(MOD(SUM(MID(C6,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})
即可得到18位(上述公式为函数大师 陈军 的作品)。
参考资料:
公民身份号码是特征组合码,由十七位数字本体码和一位校验码
组成。排列顺序从左至右依次为:六位数字地址码,八位数字出生日
期码,三位数字顺序码和一位数字校验码。
校验
:
(1)十七位数字本体码加权求和公式
S = Sum(Ai * Wi), i = 0, … , 16 ,先对前17位数字的权求和
Ai:
示第i位置上的身份证号码数字值
Wi:表示第i位置上的加权因子
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2)计算模
Y = mod(S, 11)
(3)通过模得到对应的校验码
Y: 0 1 2 3 4 5 6 7 8 9 10
校验码: 1 0 X 9 8 7 6 5 4 3 2