返回目录:excel表格制作
要点如下:
1。将年份由4位变成2位,第7,8位
2。将末尾数去掉,第18位
如18位身份证在A1,则公式如下:
=LEFT(A1,6)&MID(A1,9,9)
假设A列自A2起是身份抄证号(15位或18位)。
1、身份证号全部改为百18位,输入数组公式:
=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)
注意:数组公式输度入方法:输入公式后不要按回车,而是按Ctrl+Shift+Enter。
2、身份证号全部改为15位,输问入公式:
=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))
3、计算出生日期:
=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)
4、判断性别:答
=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)
最终结果如图:
首先需要知道身份证e5a48de588b6e79fa5e98193365号中各个数字的含义,以18位身份证号主讲,
前边6个数字,每2个数字一组,分别是省(自治区、直辖市)、市(盟、自治州)、县(县级市、区)的代码,
接下来的8位数字(15位身份证是6位数字)是出生年月日(15位身份证号的年是两位数,省略了19),比如1975年6月5日出生的,这数字就是19750605(15位身份证中就是750605),
再往后的3个数是顺序码,这3个数的最后一个数,奇数为男,偶数为女。
最后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))”,用这个去乘身份证的每一个数,再求余数,结果是一样的,自己可以验证下。
方百法一
A1单元格度是18位身知份证号,B1单元格输入“=LEFT(REPLACE(A1,7,2,""),15)”即可。
道方法二版
在权B2单元格输入“=REPLACE(LEFT(A1,17),7,2,)”亦可。