EXCEL函数实用公式大全

1excel常用函数公式及技巧搜集（常用的）【身份证信息？提取】从身份证号码中提取出生年月日====TEXTTEXTTEXTTEXT((((MIDMIDMIDMID((((A1A1A1A1,7,6+(,7,6+(,7,6+(,7,6+(LENLENLENLEN((((A1A1A1A1)=18)*2),#-00-00))=18)*2),#-00-00))=18)*2),#-00-00))=18)*2),#-00-00)+0+0+0+0====TEXTTEXTTEXTTEXT((((MIDMIDMIDMID((((A1A1A1A1,7,6+(,7,6+(,7,6+(,7,6+(LENLENLENLEN((((A1A1A1A1)=18)*2),#-00-00))=18)*2),#-00-00))=18)*2),#-00-00))=18)*2),#-00-00)*1*1*1*1=IF(A2,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)=IF(A2,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)=IF(A2,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)=IF(A2,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,)显示格式均为yyyy-m-dyyyy-m-dyyyy-m-dyyyy-m-d。（最简单的公式，把单元格设置为日期格式）=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&=IF(LEN(A2)=15,19&MID(A2,7,2)&-&MID(A2,9,2)&-&MID(A2,11,2),MID(A2,7,4)&=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)-&MID(A2,11,2)&-&MID(A2,13,2)-&MID(A2,11,2)&-&MID(A2,13,2)-&MID(A2,11,2)&-&MID(A2,13,2)))))显示格式为yyyy-mm-ddyyyy-mm-ddyyyy-mm-ddyyyy-mm-dd。（如果要求为“1995/03/291995/03/291995/03/291995/03/29”格式的话，将””””----””””换成””””////””””即可）=IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),0000=IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),0000=IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),0000=IF(D4=,,IF(LEN(D4)=15,TEXT((19&MID(D4,7,6)),0000年00000000月00000000日),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000年00000000月00000000日))))))))))))))))显示格式为yyyyyyyyyyyyyyyy年mmmmmmmm月dddddddd日。（如果将公式中“0000000000000000年00000000月00000000日”改成“0000000000000000----00000000----00000000”,,,,则显示格式为yyyy-mm-ddyyyy-mm-ddyyyy-mm-ddyyyy-mm-dd）=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6))=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6))=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6))=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19&MID(A1:A2,7,6))显示格式为yyyymmddyyyymmddyyyymmddyyyymmdd。=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0=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))=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2))=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,7,4)&=MID(A1,7,4)&=MID(A1,7,4)&年&MID(A1,11,2)&&MID(A1,11,2)&&MID(A1,11,2)&&MID(A1,11,2)&月&MID(A1,13,2)&&MID(A1,13,2)&&MID(A1,13,2)&&MID(A1,13,2)&日=IF(A1,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00))=IF(A1,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00))=IF(A1,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00))=IF(A1,TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),#-00-00))从身份证号码中提取出性别=IF(MOD(MID(=IF(MOD(MID(=IF(MOD(MID(=IF(MOD(MID(AAAA1,15,3),2),1,15,3),2),1,15,3),2),1,15,3),2),男,,,,女))))（最简单公式）=IF(MOD(RIGHT(LEFT(A1,17)),2),=IF(MOD(RIGHT(LEFT(A1,17)),2),=IF(MOD(RIGHT(LEFT(A1,17)),2),=IF(MOD(RIGHT(LEFT(A1,17)),2),男,,,,女))))=IF(A2=IF(A2=IF(A2=IF(A2””””””””,IF(MOD(RIGHT(LEFT(A2,17)),2),,IF(MOD(RIGHT(LEFT(A2,17)),2),,IF(MOD(RIGHT(LEFT(A2,17)),2),,IF(MOD(RIGHT(LEFT(A2,17)),2),””””男””””,,,,””””女””””),)),)),)),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,男,,,,女))))从身份证号码中进行年龄判断=IF=IF=IF=IF(A3(A3(A3(A3””””””””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),””””#-00-00#-00-00#-00-00#-00-00””””),T),T),T),TODAY(),ODAY(),ODAY(),ODAY(),””””YYYY””””),)),)),)),)=DATEDIF=DATEDIF=DATEDIF=DATEDIF（A1A1A1A1，TODAYTODAYTODAYTODAY（），““““YYYY””””）（以上公式会判断是否已过生日而自动增减一岁）=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900=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())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-IF(LEN(A1)=15,19&MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())-VALUE(MID(B1,7,4))&=YEAR(TODAY())-VALUE(MID(B1,7,4))&=YEAR(TODAY())-VALUE(MID(B1,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))=YEAR(TODAY())-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2)),MID(B1,7,4))=YEAR(TODAY())-IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2)),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