excel常用函数公式及技巧搜集.doc

excel 常用函数公式及技巧搜集【身份证信息?提取】从身份证号码中提取出生年月日= TEXT ( MID ( A1 ,7,6+( LEN ( A1 )=18)*2),"#-00-00") +0 = TEXT ( MID ( A1 ,7,6+( LEN ( A1 )=18)*2),"#-00-00") *1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为 yyyy-m-d 。( 最简单的公式, 把单元格设置为日期格式) =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)& "-"&MID(A2,11,2)&"-"&MID(A2,13,2) ) 显示格式为 yyyy-mm-dd 。( 如果要求为“ 1995/03/29 ”格式的话,将”-”换成”/”即可) =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000 年00月00日"),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000 年00月00日")))) 显示格式为 yyyy 年 mm 月 dd 日。( 如果将公式中“ 0000 年 00月 00日”改成“ 0000 - 00- 00”, 则显示格式为 yyyy-mm-dd ) =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为 yyyymmdd 。=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&" 年"&MID(A1,11,2)&" 月"&MID(A1,13,2)&" 日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别=IF(MOD(MID( A 1,15,3),2)," 男"," 女")(最简单公式) =IF(MOD(RIGHT(LEFT(A1,17)),2)," 男"," 女") =IF(A2<> ””,IF(MOD(RIGHT(LEFT(A2,17)),2), ”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1," 男"," 女") 从身份证号码中进行年龄判断=IF (A3<> ””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2), ”#-00-00 ”),T ODAY(), ”Y”),) =DATEDIF ( A1 , TODAY (), “Y”) (以上公式会判断是否已过生日而自动增减一岁) =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-VALUE(MID(B1,7,4))&" 岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以 2006 年 10月 31 日为基准日, 按按身份证计算年龄( 周岁) 的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y") 按身份证号分男女年龄段按身份证号分男女年龄段,身份证号在 K列,年龄段在 J列(身份证号为 18位) 男性 16周岁以下为 1 男性 16周岁(含 16周岁)以上至 50周岁为 2 男性 50周岁(含 50周岁)以上至 60周岁为 3 男性 60周岁(含 60周岁)以上为 4 女性 16周岁以下为 1 女性 16周岁(含 16周岁)以上至 45周岁为 2 女性 45周岁(含 45周岁)以上至 55周岁为 3 女性 55周岁(含 55周岁)以上为 4 =MATC