Excel中常见与身份证有关的公式.doc

1•根据身份证号计算年龄=IF( D2<>"",DA TEDIF(TEXT((LEN( D2)=15)*19&MID( D2,7,6+(LEN( D2)=18)*2),"#-00-00"),TODAY(),"y"),)注意：D2单元格为身份证号，且为文本格式2•根据身份证号计算出生年月日第一种，计算出来的格式为XX年XX月XX日=IF(LEN( D2)=15,"19"&MID( D2 ,7,2)&"年"&MID( D2,9,2)&"月"&MID( D2,11,2)&"日",MID( D2,7,4)&"年"&MID( D2,11,2)&"月"&MID( D2 ,13,2)&"日")或者=IF(LEN( D2 )=15,19,"")&TEXT(MID D2,7,8-(LEN( D2)=15)*2),"# 年 00 月 00 日")第二种，计算出来的格式为XX年XX月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&" 年"&MID(A2,9,2)&"月 ”,MID(A2,7,4)&"年"&MID(A2,11,2)&" 月")第三种计算出来的格式为 2011/1/1=MID(B11,7,4) &"/"&MID(B11,11,2) &"/"&MID(B11,13,2)3•根据身份证号计算性别=IF(MOD(IF(LEN( D2)=15,MID( D2,15,1),MID( D2,17,1)),2)=1,"男","女")4•身份证号全部改为18位(输出正确18位)，输入数组公式(ctrl+shift+enter 结尾)：=IF(LEN( A2)=15,REPLACE( A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE( A2,7,,19),ROW(INDIRECT("1:17")),1)*2A(18-ROW(INDIRECT("1:17")))),11)+1,1), A2)5•以18位身份证为准输出15位身份证号：=IF(LEN( G2)=15, G2,LEFT(REPLACE( G2,7,2,),15))6•验证身份证号(G2为输入18位身份证号，H2为输出正确18位身份证号)：=IF( G2<>0,IF(LEN( G2)=15," 一代身份证号",(IF(LEN( G2)=18,IF( G2=H2,"正确","未通过验证 "),IF(LEN( G2)>18,"超过18位，请核查","身份证号不完整")))),)18位身份证号码转换成出生日期的函数公式：如果 E2中是身份证，在F2中求出出生日期，F2=DATE(MIDB( E2,7,4),MIDB( E2,11,2),MIDB( E2,13,2))自动录入男女：=IF(MOD((IF(LEN( e2)=18,MID( e2,17,1),MID( e2,15,1))) ,2)=0,"女","男")根据身份证号快速录入男女性别“记住”15/18位都可以的公式：转换出生日期:=IF(LEN( e2)=18,TEXT(MID( e2,7,8),"#-00-00"),"19"&TE XT(MID( e2,7,6),"#-00-00"))自动录入男女：=IF( E2="","",IF(MOD(RIGHT(LEFT( E2,17),1),2)=0,"女","男"))计算年龄(新旧身份证号都可以)=IF(AND( E2=""),"",IF(MIDB( E2,7,2)="19",107-MIDB( E2 ,9,2),107-MIDB( E2,7,2)))。