EXCEL函数公式大全.docx

《EXCEL函数公式大全.docx》由会员分享，可在线阅读，更多相关《EXCEL函数公式大全.docx（83页珍藏版）》请在第一文库网上搜索。

1、exce1常用函数公式及技巧搜集(常用的)【身份证信息？提取】从身份证号码中提取出生年月日=TEXT(MID(A1,7,6+(1EN(AI)=I8)*2)J#0000)+0=TEXT(M1D(AI,7,6+(1EN(AI)=I8)*2)J#-O(H)(T)*1=IF(A2o,TEXT(1EN(A2)=15)*19&MID(A2,7,6+(1EN(A2)=18)*2)J#0000)+0J显示格式均为yyyymd(最简单的公式，把单元格设置为日期格式)=IF(1EN(A2)=15JI9&MID(A2,7,2)&”“&MID(A2,9,2)&”&MID(A2,2),MID(A2,7,4)&MID(A

2、2,11,2)&,&MID(A2,13,2)显示格式为yyyymmdd。(如果要求为“1995/03/29”格式的话，将，“换成7”即可)=IF(D4=,h,hh,IF(1EN(D4)=15,TEXT(h19MID(D4,7,6),0000年00月00日”),1F(1EN(D4)=18,TEXT(MiD(D4,7,8)JooOo年OO月00日”)显示格式为yyyy年mm月dd日。(如果将公式中“()000年00月00日改成“00000000”,则显示格式为yyyy-mm-dd)=IF(1EN(A1:A2)=18,MID(A1:A2,7,8)J19&MID(A1:A2,7,6)显示格式为yyyy

3、mmdd,=TEXT(1EN(A1)=15)*19&MID(A1,7,6+(1EN(A1)=18)*2),#-00-001)+0=IF(1EN(A2)=18,MID(A2,7,4)&-MID(A2,H,2),19&MID(A2,7,2)&-MID(A2,9,2)=M1D(AI,7,4)&”年&MID(A1,U,2)&“月”&MID(A1,13,2)&”日”=IF(A1oHH,TEXT(1EN(A1)=15)*19&MID(A1,7,6+(1EN(A1)=18)*2),n#-00-00H)从身份证号码中提取出性别=IF(MoD(M1D(A1,15,3),2)J男V女”)(最简单公式)=IF(MO

4、D(R1GHT(1EFT(A1,17),2)J男“J女”)=IF(A2m,IF(MOD(R1GHT(1EFT(A2,17),2),“男”,“女”),)=IF(VA1UE(1EN(RoUND(R1GHT(AI,1)/2,2)=IJ男“J女”)从身份证号码中进行年龄判断=IF(A3o,DATEDIF(TEXT(1EN(A3)=15*19&MID(A3,7,6+(1EN(A3)=18*2),#-00-00,),TODAYOJY”),)=DATEDIf(A1,TODAYO，“Y”)(以上公式会判断是否已过生日而自动增减一岁)=YEAR(NOW()-MID(E2,IF(1EN(E2)=18,9,7),2

5、)-1900=YEAR(ToDAY()IF(1EN(A1)=I5J19&MID(A1,7,2),MID(A1,7,4)=YEAR(TODAY()VA1UE(MID(B1,7,4)&”岁=YEAR(TODAY()-IF(MID(B1,18,1)=n,CONCATENATE(,19n,MID(B1,7,2),MID(B1,7,4)按身份证号号码计算至今天年龄=DATEDIF(TEXT(1EN(A1)=15)*19&MID(A1,7,6+(1EN(A1)=18)*2)；#-00-00n),TODAY(),nyn)以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式=DATED1F(TE

6、XT(M1D(AI,7,6+(1EN(AI)=I8)*2)J#00OO)*1J200610-31Jy)按身份证号分男女年龄段按身份证号分男女年龄段，身份证号在K歹U,年龄段在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=MATCH(DATED1F(DATE(Mn)(KI,7,4),MID(KIJ1,2),M1D(K1,13,2),To

7、DAY()Jy),0,16,50,600,0,5$*ISEVEN(M1D(KIj7,1)=SUM(-(DATEDIF(MID(K1,7,4)&,7H&MID(K1,11,2)&,7,&MID(K1,13,2),TODAY(),nyM)=0,16,45,55+0,0息5*MoD(MnXKI,17,1),2)mmmmnmmmmnmmmmmnmmmnmmmnmnmnmmmmmnm)【年龄和工龄计算】根据出生年月计算年龄=DATEDIF(A1TODAYOJy)=DATEDIF(A1,TODAY()Jy)&“周岁”=DATEDIF(A1,NOW(),y)根据出生年月推算生肖中国人有12生肖，属什么可以推

8、算出来。即用诞生年份除以12,再用除不尽的余数对照如下：Of猴，If鸡，2-狗，3-*猪，4鼠，5牛，6-虎，7-*兔，8-*龙，9*蛇，10一马，11-羊例如：XXX出生于1921年，即用1921年除以12,商得数为160,余数为1对照上面得知余数1对应生肖是鸡，XXX就属鸡。=M1De猴鸡狗猪鼠牛虎兔龙蛇马羊ZMOD(YEAR(A2),12)+1,1)(2007)如何求出一个人到某指定日期的周岁？=DATED1F(起始日期,结束日期JY)计算距离退休年龄的公式=IF(E2=“J”,IF(E2=V2J已经退休”J距离退休还有“&DATEDIF(ToDAY(),DATE(YEAR(U2)+(V

9、2),MONTH(U2),DAY(U2)JY)&”年&DATEDIF(ToDAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)JYM”)&个月&DATEDIF(ToDAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)JMd)&“天”)其中E2为年龄(可用身份证号码的公式生成)；V2为法定退休年龄(男60,女50)公式为：=IF(D2=IF(D2=男”,60,50)D2为男或女(可用身份证号码的公式生成)；U2为出生年月日(可用身份证号码的公式生成)。求工龄=DATEDIF(B2,ToDAY()Jy”)=DATEDIF(B2,ToD

10、AY()Jym”)=DATED1F(B2,ToDAY()Jmd)=DATED1F(B2,TODAYojy“)&”年&DATEDIF(B2,TODAY()Jym”)&”月“&DATEDIF(B2,TODAY()Jmd”)&”日”计算工龄=DATEDIF(C6,C8Jy”)求两日期间的年数=DATED1F(C6,C8Jym)求两日期间除去整年数剩余的月数=DATEDIF(C6,C8Jm”)求两日期间的总月数如果只需要算出周年的话，可以用=datedif(U9788T20065“JY”)年龄及工龄计算有出生年月如何求年龄？有工作时间如何求工龄？(求出的结果为多少年另几个月，如：0303的形式，即3年

11、零3个月)。a1是出生年月或工作时间：=datedif(a1,today。Jy)=text(datedif(a1,today。,y)JOO)&text(Iatedif(a1,today()Jm)JOO)如B2=1964-9-1则:二TEXT(DATED1F(B2,ToDAY()Jy”)JO(T)&TEXT(MOD(DATEDIF(B2,TODAYoJm),12),n00)显示4009=TEXT(DATEDIF(B2,ToDAY()Jy”)JOO年)&TEXT(MoD(DATEDIF(B2,ToDAY()Jin”),12)JOO月)，显示40年09月如果你找不到DATEDIF函数，也可以不用DA

12、TEDIF函数，如IB2=1964-9-1则:=TEXT(RIGHT(YEAR(NOW()-B2),2),mOOm)TEXT(MOD(MONTH(NOW()-B2)-1,12),hOOm)显示4009=TEXT(R1GHT(YEAR(NoW()B2),2)&“年&MOD(MoNTH(NOW()B2)4,12)&,个月V”)，显示40年09个月自动算出工龄日期格式为以前用这样一段(=TEXT(RIGHT(YEAR(NOW()-A1),2)&”年”&MOD(MoNTH(NOWoA1)1,12)&“个月V”)。但这种方法只能用：(yyyy-mm-dd)这样的日期格式才能实现！你不妨把、.替换成、“，

13、不就行了吗，再说后者是日期的一种标准格式，=TEXT(RIGHT(YEAR(NoW()SUBSrTUTE(A12)&”年”&MOD(MONTH(NoW()SUBSTUTE(A1jJJj)个月J”)Innnnn1InnI1nin1nn1nnnInnIn1InnInIn1nnnInInnnn1n1IIin1n1nnnnnInIIIInIn)InnInnnInInnnnn11nIninIn【时间和日期应用】自动显示当前日期公式=YEAR(NOWo)当前年=MONTH(NOW()当前月=DAY(NOW()当前日如何在单元格中自动填入当前日期Ctr1+;如何判断某日是否星期天=WEEKDAY(A2,2)=TEXT(A1Jaaaa)=MOD(A1,7)1)显示昨天的日期每天需要单元格内显示昨天的日期，但双休日除外。例如，今天是7月3号的话，就显示7月2号，如果是7月9号，就显示7月6号。=IF(TEXT(TODAYoJAAA)=JzToDAY().3,IF(TEXT(TODAY()JAAA)=日m,TODAY()-2,TODAYO-I)=IF(TEXT(TODAY()JAAA)=,TODAY。-3,TODAY。，)关于取日期怎么设个公式使A1在年月日向后推5年,变成2011-7-15=DATE(YEAR(A1)+