【正文】 excel常用函數(shù)公式及技巧搜集（常用的）【身份證信息？提取】從身份證號碼中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),0000)+0=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),0000)*1=IF(A2,TEXT((LEN(A2)=15)*19amp。MID(A2,7,6+(LEN(A2)=18)*2),0000)+0,)顯示格式均為yyyymd。（最簡單的公式，把單元格設置為日期格式）=IF(LEN(A2)=15,19amp。MID(A2,7,2)amp。amp。MID(A2,9,2)amp。amp。MID(A2,11,2),MID(A2,7,4)amp。amp。MID(A2,11,2)amp。amp。MID(A2,13,2)) 顯示格式為yyyymmdd。（如果要求為“1995/03/29”格式的話，將”” 換成”/”即可）=IF(D4=,IF(LEN(D4)=15,TEXT((19amp。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日”改成“00000000”,則顯示格式為yyyymmdd）=IF(LEN(A1:A2)=18,MID(A1:A2,7,8),19amp。MID(A1:A2,7,6))顯示格式為yyyymmdd。=TEXT((LEN(A1)=15)*19amp。MID(A1,7,6+(LEN(A1)=18)*2),0000)+0=IF(LEN(A2)=18,MID(A2,7,4)amp。MID(A2,11,2),19amp。MID(A2,7,2)amp。MID(A2,9,2))=MID(A1,7,4)amp。年amp。MID(A1,11,2)amp。月amp。MID(A1,13,2)amp。日=IF(A1,TEXT((LEN(A1)=15)*19amp。MID(A1,7,6+(LEN(A1)=18)*2),0000))從身份證號碼中提取出性別=IF(MOD(MID(A1,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*19amp。MID(A3,7,6+(LEN(A3)=18*2),”0000”),TODAY(),”Y”),)=DATEDIF（A1，TODAY（），“Y”）（以上公式會判斷是否已過生日而自動增減一歲）=YEAR(NOW())MID(E2,IF(LEN(E2)=18,9,7),2)1900=YEAR(TODAY())IF(LEN(A1)=15,19amp。MID(A1,7,2),MID(A1,7,4))=YEAR(TODAY())VALUE(MID(B1,7,4))amp。歲=YEAR(TODAY())IF(MID(B1,18,1)=,CONCATENATE(19,MID(B1,7,2)),MID(B1,7,4))按身份證號號碼計算至今天年齡 =DATEDIF(TEXT((LEN(A1)=15)*19amp。MID(A1,7,6+(LEN(A1)=18)*2),0000),TODAY(),y)以2006年10月31日為基準日,按按身份證計算年齡(周歲)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),0000)*1,20061031,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=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)amp。/amp。MID(K1,11,2)amp。/amp。MID(K1,13,2),TODAY(),y)={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【年齡和工齡計算】根據(jù)出生年月計算年齡＝DATEDIF(A1,TODAY(),y)＝DATEDIF(A1,TODAY(),y)amp。周歲＝DATEDIF(A1,NOW(),y)根據(jù)出生年月推算生肖中國人有12生肖，屬什么可以推算出來。即用誕生年份除以12，再用除不盡的余數(shù)對照如下：0→猴，1→雞，2→狗，3→豬，4→鼠，5→牛，6→虎，7→兔，8→龍，9→蛇，10→馬，11→羊例如：XXX出生于1921年，即用1921年除以12，商得數(shù)為160，余數(shù)為1，對照上面得知余數(shù)1對應生肖是雞，XXX就屬雞。=MID(猴雞狗豬鼠?；⑼谬埳唏R羊,MOD(YEAR(A2),12)+1,1) （2007）如何求出一個人到某指定日期的周歲？=DATEDIF(起始日期,結束日期,Y)計算距離退休年齡的公式=IF(E2=,IF(E2=V2,已經(jīng)退休,距離退休還有amp。DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),Y)amp。年amp。DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),YM)amp。個月amp。DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),Md)amp。天))其中E2為年齡（可用身份證號碼的公式生成）；V2為法定退休年齡（男60，女50）公式為：=IF(D2=,IF(D2=男,60,50))D2為男或女（可用身份證號碼的公式生成）；U2為出生年月日（可用身份證號碼的公式生成）。求工齡=DATEDIF(B2,TODAY(),y) =DATEDIF(B2,TODAY(),ym) =DATEDIF(B2,TODAY(),md) =DATEDIF(B2,TODAY(),y)amp。年amp。DATEDIF(B2,TODAY(),ym)amp。月amp。DATEDIF(B2,TODAY(),md)amp。日 計算工齡=DATEDIF(C6,C8,y)求兩日期間的年數(shù)=DATEDIF(C6,C8,ym)求兩日期間除去整年數(shù)剩余的月數(shù)=DATEDIF(C6,C8,m)求兩日期間的總月數(shù)如果只需要算出周年的話，可以用=datedif(19788,20065,Y)年齡及工齡計算有出生年月如何求年齡？有工作時間如何求工齡？（求出的結果為多少年另幾個月，如：0303的形式，即3年零3個月）。a1是出生年月或工作時間：=datedif(a1,today(),y)=text(datedif(a1,today(),y),00)amp。text(datedif(a1,today(),m),00)如 [B2]=196491 則:=TEXT(DATEDIF(B2,TODAY(),y),00)amp。TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00) 39。顯示 4009=TEXT(DATEDIF(B2,TODAY(),y),00年)amp。TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00月)39。顯示 40年09月如果你找不到 DATEDIF 函數(shù)，也可以不用 DATEDIF 函數(shù),如 [B2]=196491 則:=TEXT(RIGHT(YEAR(NOW()B2),2),00)amp。TEXT(MOD(MONTH(NOW()B2)1,12),00) 39。顯示 4009=TEXT(RIGHT(YEAR(NOW()B2),2)amp。年amp。MOD(MONTH(NOW()B2)1,12)amp。個月,) 39。顯示 40年09個月自動算出工齡日期格式為()能否用：（）這種格式來計算出工齡有多長呢~？ 以前用這樣一段（ =TEXT(RIGHT(YEAR(NOW()A1),2)amp。年amp。MOD(MONTH(NOW()A1)1,12)amp。個月,) ）。但這種方法只能用：（yyyymmdd）這樣的日期格式才能實現(xiàn)！你不妨把“.”替換成“”，不就行了嗎，再說后者是日期的一種標準格式，=TEXT(RIGHT(YEAR(NOW()SUBSTITUTE(A1,.,)),2)amp。年amp。MOD(MONTH(NOW()SUBSTITUTE(A1,.,))1,12)amp。個月,)㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜【時間和日期應用】自動顯示當前日期公式=YEAR(NOW()) 當前年=MONTH(NOW()) 當前月=DAY((NOW())) 當前日如何在單元格中自動填入當前日期Ctrl+。如何判斷某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,aaaa)=MOD(A1,7)2某個日期是星期幾比如2007年2月9日，在一單元格內(nèi)顯示星期幾。=TEXT(A1,aaa) （五）=TEXT(A1,aaaa) （星期五）=TEXT(A1,ddd) （Fri）=TEXT(A1,dddd) （Friday）什么函數(shù)可以顯示當前星期如：星期二 10:41:56=TEXT(NOW(),aaaahh:mm:ss)求本月天數(shù)設A1為200684求本月天數(shù)A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))也有更簡便的公式：=DAY(EOMONTH(NOW(),0)) 需加載分析工具箱。當前月天數(shù)： =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)DATE(YEAR(TODAY()),MONTH(TODAY()),1)用公式算出除去當月星期六、星期日以外的天數(shù)=SUMPRODUCT((MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)amp。:amp。DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)1))顯示昨天的日期每天需要單元格內(nèi)顯示昨天的日期，但雙休日除外。例如，今天是7月3號的話，就顯示7月2號，如果是7月9號，就顯示7月6號。=IF(TEXT(TODAY(),AAA)=一,TODAY()3,IF(TEXT(TODAY(),AAA)=日,TODAY()2,TODAY()1))=IF(TEXT(TODAY(),AAA)=一,TODAY()3,TODAY()1)關于取日期怎么設個公式使A1在年月日向后推5年,變成2011715=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))=EDATE(A1,12*5)如何對日期進行上、中、下旬區(qū)分=LOOKUP(DAY(A1),{0,11,21,31},{上旬,中旬,下旬,下旬})如何獲取一個月的最大天數(shù)=DAY(DATE(2002,3,1)1)或=DAY(B11),B1為20010301日期格式轉換公式將 “01/12/2005” 轉換成“20050112”格式＝RIGHT(A1,4)amp。MID(A1,4,2)amp。LEFT(A1,2)＝YEAR($A2)amp。TEXT(MONTH($A2),00)amp。TEXT(DAY($A2),00) 該公式不用設置數(shù)據(jù)有效性,但要設置儲存格格式。也可以用下列兩方法：先轉換成文本, 然后再用字符處理函數(shù)。[數(shù)據(jù)][分列] [日期][MDY]將“2005年9月”轉換成“200509”格式先用公式：=text(a1,yyyymm)+0 然后將單元格格式為常規(guī)。將“200586”格式轉換為“20050806”格式用公式：=TEXT(A1,YYYYMMDD)反之，將20050806轉為日期200586格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))另四種公式：＝text(a1,00000000) 顯示：20050806＝TEXT(A1,0000)，把單元格設置為日期格式 顯示：200586