excel最常用函數公式技巧搜集大全更新.docVIP

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)*19MID(A2,7,6+(LEN(A2)=18)*2),#-00-00)+0,) 显示格式均为yyyy-m-d。（最简单的公式把单元格设置为日期格式=IF(LEN(A2)=15,19MID(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((19MID(D4,7,6)),0000年00月00日),IF(LEN(D4)=18,TEXT(MID(D4,7,8),0000年00月00日)))) 0000年00月00日0000-00-00”,则显示格式为yyyy-mm-dd） =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)+0 =IF(LEN(A2)=18,MID(A2,7,4)-MID(A2,11,2),19MID(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)*19MID(A1,7,6+(LEN(A1)=18)*2),#-00-00)) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),男,女)=IF(MOD(RIGHT(LEFT(A1,17)),2),男,女)” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,男,女) 从身份证号码中进行年龄判断 =IF(A3””,DATEDIF(TEXT((LEN(A3)=15*19MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),) =DATEDIF（A1，TODAY（），“Y”）=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,19MID(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)*19MID(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)按身份证号分男女年龄段=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),y),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1))) =SUM(--(DATEDIF(MID(K1,7,4)/MID(K1,11,2)/MID(K1,13,2),TODAY(),y)={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2))) ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 【年龄和工龄计算】 根据出生年月计算年龄 ＝DATEDIF(A1,TODAY(),y) DATEDIF(A1,TODAY(),y)周岁DATEDIF(A1,NOW(),y) 根据出生年月推算生肖 中国人有12生肖，属什么可以推算出来。即用诞生年份除以12，再用除不尽的余数对照如下：0猴，1鸡，2狗，3猪，4鼠，5牛，6虎，7兔，8龙，9蛇，10马，11羊例如：XXX出生于1921年，即用1921年除以12，商得数为160，余数为1，对照上面得知余数