作者:乔山办公网日期:
返回目录:excel表格制作
首先需要知道身份证号中各个数字的含义,以18位身份证号主讲,
前边6个数字,每2个数字一组,分别是省(自治区、直辖市)、市(盟、自治州)、县(县级市、区)的代码,
接下来的8位数字(15位身份证是6位数字)是出生年月日(15位身份证号的年是两位数,省略了19),比如1975年6月5日出生的,这数字就是19750605(15位身份证中就是750605),
再往后的3个数是顺序码,这3个数的最后一个数,奇数为男,偶数为女。636f7079e799bee5baa6364
最后1位是效验码(15位身份证号没有,就是为了防止身份证重号,而增设的)
总结:15位身份证号与18位身份证号的区别:
区别一:年份少个“19”,
区别二:最后少一个验证码。加“19”很容易,关键就在最后一个效验码的计算。
效验码是根据ISO 7604:1983.MOD11-2计算的,具体的计算规则看下图
至此可以写出身份证号升级公式了=REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW($1:$17),1)*2^(18-ROW($1:$17))),11)+1,1)
上边公式中没有直接引用“加权因子”,而是使用了“2^(18-ROW($1:$17))”,用这个去乘身份证的每一个数,再求余数,结果是一样的,自己可以验证下。
假设身份证zd在A1单元回格
在B1单元格输入公式
=replace(a1,7,,19)&mid("10x98765432",mod(sumproduct(mid(replace(a1,7,,19),row(1:17),1)*2^答(18-row(1:17))),11)+1,1)
下拉填充公式
用公式进行转化,假设数据在A列,百从A2开始:
1、B2用公式:
=IF(LEN(A2)=15,TEXT(A2,"00000019000000000")&INDEX({1,0,"X",9,8,7,6,5,4,3,2},MOD(SUMPRODUCT(--(MID(TEXT(A2,"00000019000000000"),TRANSPOSE(ROW($1:$17)),1)*{7,9,10,5,8,4,2,1,6,3,7,9,10,5,8,4,2})),11)+1),A2)
下拉;度
2、选中版B列,右击,复制B列,然后右击B1,“选择性粘贴”,在对话框中选择“数值”,把公式权结果,以数值的形式粘贴回原区域;
3、删除A列。
假设知数据道在A1,用专公式:属
=REPLACE(A1,7,0,"19")&INDEX({0;"X";9;8;7;6;5;4;3;2;1},MOD(SUMPRODUCT(MID(REPLACE(A1,7,0,"19"),ROW($1:$17),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11))