EXCEL函數(shù)公式大全

1、excel常用函數(shù)公式及技巧搜集（常用的）查找A欄與B欄是否找到有相同的數(shù)據(jù)=IF(ISNA(MATCH(N1,$M$1:$M$3000,0),"無(wú)匹配",MATCH(N1,$M$1:$M$3000,0)1、查找重復(fù)內(nèi)容公式：=IF(COUNTIF(A:A,A2)>1,"重復(fù)","")。=IF(COUNTIF(J:J,J2&"*")>1,"重復(fù)","唯一")2、用出生年月來(lái)計(jì)算年齡公式：=TRUNC(DAYS360(H6,"2009/8/30&q

2、uot;,FALSE)/360,0)。3、從輸入的18位身份證號(hào)的出生年月計(jì)算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2)。4、從輸入的身份證號(hào)碼內(nèi)讓系統(tǒng)自動(dòng)提取性別，可以輸入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女")公式內(nèi)的“C2”代表的是輸入身份證號(hào)碼的單元格。&#

3、160;1、求和： =SUM(K2:K56)  對(duì)K2到K56這一區(qū)域進(jìn)行求和；2、平均數(shù)： =AVERAGE(K2:K56)  對(duì)K2 K56這一區(qū)域求平均數(shù)；3、排名： =RANK(K2，K$2:K$56)  對(duì)55名學(xué)生的成績(jī)進(jìn)行排名；4、等級(jí)： =IF(K2>=85,"優(yōu)",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")5、學(xué)期總評(píng)： =K2*0.3+M2*0.3+N2*0.4 假設(shè)K列、M列和N列分別存放著學(xué)生的“平時(shí)總評(píng)”、“期中”

4、、“期末”三項(xiàng)成績(jī)；6、最高分： =MAX(K2:K56) 求K2到K56區(qū)域（55名學(xué)生）的最高分；7、最低分： =MIN(K2:K56) 求K2到K56區(qū)域（55名學(xué)生）的最低分；8、分?jǐn)?shù)段人數(shù)統(tǒng)計(jì)：（1） =COUNTIF(K2:K56,"100") 求K2到K56區(qū)域100分的人數(shù)；假設(shè)把結(jié)果存放于K57單元格；（2）  =COUNTIF(K2:K56,">=95")K57 求K2到K56區(qū)域9599.5分的人數(shù)；假設(shè)把結(jié)果存放于K58單元格；（3）=COUNTIF(K2:K56,">=90")SUM(K

5、57:K58)  求K2到K56區(qū)域9094.5分的人數(shù)；假設(shè)把結(jié)果存放于K59單元格；（4）=COUNTIF(K2:K56,">=85")SUM(K57:K59)  求K2到K56區(qū)域8589.5分的人數(shù)；假設(shè)把結(jié)果存放于K60單元格；（5）=COUNTIF(K2:K56,">=70")SUM(K57:K60)  求K2到K56區(qū)域7084.5分的人數(shù)；假設(shè)把結(jié)果存放于K61單元格；（6）=COUNTIF(K2:K56,">=60")SUM(K57:K61)  求K2到K56

6、區(qū)域6069.5分的人數(shù)；假設(shè)把結(jié)果存放于K62單元格；（7） =COUNTIF(K2:K56,"<60") 求K2到K56區(qū)域60分以下的人數(shù)；假設(shè)把結(jié)果存放于K63單元格；說(shuō)明：COUNTIF函數(shù)也可計(jì)算某一區(qū)域男、女生人數(shù)。如：=COUNTIF(C2:C351,"男") 求C2到C351區(qū)域（共350人）男性人數(shù)；9、優(yōu)秀率： =SUM(K57:K60)/55*10010、及格率： =SUM(K57:K62)/55*10011、標(biāo)準(zhǔn)差： =STDEV(K2:K56) 求K2到K56區(qū)域(55人)的成績(jī)波動(dòng)情況（數(shù)值越小，說(shuō)明該班學(xué)生間的成績(jī)差

7、異較小，反之，說(shuō)明該班存在兩極分化）；12、條件求和： =SUMIF(B2:B56,"男"，K2:K56) 假設(shè)B列存放學(xué)生的性別，K列存放學(xué)生的分?jǐn)?shù)，則此函數(shù)返回的結(jié)果表示求該班男生的成績(jī)之和；13、多條件求和： =SUM(IF(C3:C322="男",IF(G3:G322=1,1,0) 假設(shè)C列（C3:C322區(qū)域）存放學(xué)生的性別，G列（G3:G322區(qū)域）存放學(xué)生所在班級(jí)代碼（1、2、3、4、5），則此函數(shù)返回的結(jié)果表示求一班的男生人數(shù)；這是一個(gè)數(shù)組函數(shù)，輸完后要按CtrlShiftEnter組合鍵(產(chǎn)生“”)。“”不能手工輸入，只能用組合鍵產(chǎn)生。

8、14、根據(jù)出生日期自動(dòng)計(jì)算周歲：=TRUNC(DAYS360(D3,NOW( )/360,0)假設(shè)D列存放學(xué)生的出生日期，E列輸入該函數(shù)后則產(chǎn)生該生的周歲。15、在Word中三個(gè)小竅門(mén)：連續(xù)輸入三個(gè)“”可得一條波浪線(xiàn)。連續(xù)輸入三個(gè)“-”可得一條直線(xiàn)。連續(xù)輸入三個(gè)“=”可得一條雙直線(xiàn)。一、excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色比如：A11時(shí)，C1顯示紅色0<A1<1時(shí)，C1顯示綠色A1<0時(shí)，C1顯示黃色方法如下：1、單元擊C1單元格，點(diǎn)“格式”>“條件格式”，條件1設(shè)為：公式 =A1=12、點(diǎn)“格式”->“字體”->“顏色”，點(diǎn)

9、擊紅色后點(diǎn)“確定”。條件2設(shè)為：公式 =AND(A1>0,A1<1)3、點(diǎn)“格式”->“字體”->“顏色”，點(diǎn)擊綠色后點(diǎn)“確定”。條件3設(shè)為：公式 =A1<0點(diǎn)“格式”->“字體”->“顏色”，點(diǎn)擊黃色后點(diǎn)“確定”。4、三個(gè)條件設(shè)定好后，點(diǎn)“確定”即出。二、EXCEL中如何控制每列數(shù)據(jù)的長(zhǎng)度并避免重復(fù)錄入1、用數(shù)據(jù)有效性定義數(shù)據(jù)長(zhǎng)度。用鼠標(biāo)選定你要輸入的數(shù)據(jù)范圍，點(diǎn)"數(shù)據(jù)"->"有效性"->"設(shè)置"，"有效性條件"設(shè)成"允許""文本

10、長(zhǎng)度""等于""5"（具體條件可根據(jù)你的需要改變）。還可以定義一些提示信息、出錯(cuò)警告信息和是否打開(kāi)中文輸入法等，定義好后點(diǎn)"確定"。        2、用條件格式避免重復(fù)。選定A列，點(diǎn)"格式"->"條件格式"，將條件設(shè)成“公式=COUNTIF($A:$A,$A1)>1”，點(diǎn)"格式"->"字體"->"顏色"，選定紅色后點(diǎn)兩次"

11、確定"。這樣設(shè)定好后你輸入數(shù)據(jù)如果長(zhǎng)度不對(duì)會(huì)有提示，如果數(shù)據(jù)重復(fù)字體將會(huì)變成紅色。三、在EXCEL中如何把B列與A列不同之處標(biāo)識(shí)出來(lái)？（一）、如果是要求A、B兩列的同一行數(shù)據(jù)相比較：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”->“條件格式”，將條件設(shè)為:“單元格數(shù)值” “不等于”=B2點(diǎn)“格式”->“字體”->“顏色”，選中紅色，點(diǎn)兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制。B列可參照此方法設(shè)置。（二）、如果是A列與B列整體比較（即相同數(shù)據(jù)不在同一行）：假定第一行為表頭，單擊A2單元格，點(diǎn)“格式”->“條件格式”，將條件設(shè)為:“公式”=COUNTI

12、F($B:$B,$A2)=0點(diǎn)“格式”->“字體”->“顏色”，選中紅色，點(diǎn)兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制。B列可參照此方法設(shè)置。按以上方法設(shè)置后，AB列均有的數(shù)據(jù)不著色，A列有B列無(wú)或者B列有A列無(wú)的數(shù)據(jù)標(biāo)記為紅色字體。四、EXCEL中怎樣批量地處理按行排序假定有大量的數(shù)據(jù)(數(shù)值)，需要將每一行按從大到小排序，如何操作？由于按行排序與按列排序都是只能有一個(gè)主關(guān)鍵字,主關(guān)鍵字相同時(shí)才能按次關(guān)鍵字排序。所以，這一問(wèn)題不能用排序來(lái)解決。解決方法如下：1、假定你的數(shù)據(jù)在A至E列，請(qǐng)?jiān)贔1單元格輸入公式：=LARGE($A1:$E1,COLUMN(A1)用填充柄將公式

13、向右向下復(fù)制到相應(yīng)范圍。你原有數(shù)據(jù)將按行從大到小排序出現(xiàn)在F至J列。如有需要可用“選擇性粘貼/數(shù)值”復(fù)制到其他地方。注：第1步的公式可根據(jù)你的實(shí)際情況（數(shù)據(jù)范圍）作相應(yīng)的修改。如果要從小到大排序,公式改為:=SMALL($A1:$E1,COLUMN(A1)五、巧用函數(shù)組合進(jìn)行多條件的計(jì)數(shù)統(tǒng)計(jì)例：第一行為表頭，A列是“姓名”，B列是“班級(jí)”，C列是“語(yǔ)文成績(jī)”，D列是“錄取結(jié)果”，現(xiàn)在要統(tǒng)計(jì)“班級(jí)”為“二”，“語(yǔ)文成績(jī)”大于等于104，“錄取結(jié)果”為“重本”的人數(shù)。統(tǒng)計(jì)結(jié)果存放在本工作表的其他列。公式如下：=SUM(IF(B2:B9999="二")*(C2:C9999>

14、=104)*(D2:D9999="重本"),1,0)輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)""。六、如何判斷單元格里是否包含指定文本？假定對(duì)A1單元格進(jìn)行判斷有無(wú)"指定文本",以下任一公式均可:=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","無(wú)")=IF(ISERROR(FIND("指定文本",A1,1),"無(wú)",&q

15、uot;有")求某一區(qū)域內(nèi)不重復(fù)的數(shù)據(jù)個(gè)數(shù)例如求A1:A100范圍內(nèi)不重復(fù)數(shù)據(jù)的個(gè)數(shù)，某個(gè)數(shù)重復(fù)多次出現(xiàn)只算一個(gè)。有兩種計(jì)算方法：一是利用數(shù)組公式：=SUM(1/COUNTIF(A1:A100,A1:A100)輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)""。二是利用乘積求和函數(shù)：=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)七、一個(gè)工作薄中有許多工作表如何快速整理出一個(gè)目錄工作表1、用宏3.0取出各工作表的名稱(chēng)，方法：Ctrl+F3出現(xiàn)自定義名稱(chēng)對(duì)話(huà)框，取名為X，在“引用位置”框中輸入：=MID(GET

16、.WORKBOOK(1),FIND("",GET.WORKBOOK(1)+1,100)確定2、用HYPERLINK函數(shù)批量插入連接，方法：在目錄工作表（一般為第一個(gè)sheet）的A2單元格輸入公式：=HYPERLINK("#'"&INDEX(X,ROW()&"'!A1",INDEX(X,ROW()將公式向下填充，直到出錯(cuò)為止，目錄就生成了【身份證信息？提取】從身份證號(hào)碼中提取出生年月日=TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0=TEXT

17、(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。（最簡(jiǎn)單的公式，把單元格設(shè)置為日期格式）=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2

18、),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2) 顯示格式為yyyy-mm-dd。（如果要求為“1995/03/29”格式的話(huà)，將”-” 換成”/”即可）=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日")顯

19、示格式為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,

20、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")從身份證號(hào)碼中提取出性別=IF(MOD(MID(A1,15,3),2),"男","女") （最簡(jiǎn)單公式）=IF(MOD(RIGHT(LEFT(A1,17),2)

21、,"男","女")=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17),2),”男”,”女”),)=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)=1,"男","女")從身份證號(hào)碼中進(jìn)行年齡判斷=IF(A3<>”,DATEDIF(TEXT(LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),TODAY(),”Y”),)=DATEDIF（A1，TODAY（），“Y”）（以上公式會(huì)判斷是否已過(guò)生

22、日而自動(dòng)增減一歲）=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)按身份證號(hào)號(hào)碼計(jì)算至今天年齡 =DATEDIF(TEXT(LEN(A

23、1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")以2006年10月31日為基準(zhǔn)日,按按身份證計(jì)算年齡(周歲)的公式=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")按身份證號(hào)分男女年齡段按身份證號(hào)分男女年齡段，身份證號(hào)在K列，年齡段在J列（身份證號(hào)為18位）男性16周歲以下為1男性16周歲（含16周歲）以上至50周歲為 2男性

24、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)&"/"&MID(K1,11,2)&&q

25、uot;/"&MID(K1,13,2),TODAY(),"y")>=0,16,45,55+0,0,5,5*MOD(MID(K1,17,1),2)【年齡和工齡計(jì)算】根據(jù)出生年月計(jì)算年齡DATEDIF(A1,TODAY(),"y")DATEDIF(A1,TODAY(),"y")&"周歲"DATEDIF(A1,NOW(),"y")根據(jù)出生年月推算生肖中國(guó)人有12生肖，屬什么可以推算出來(lái)。即用誕生年份除以12，再用除不盡的余數(shù)對(duì)照如下：0猴，1雞，2狗，3豬，4鼠，5牛，

26、6虎，7兔，8龍，9蛇，10馬，11羊例如：XXX出生于1921年，即用1921年除以12，商得數(shù)為160，余數(shù)為1，對(duì)照上面得知余數(shù)1對(duì)應(yīng)生肖是雞，XXX就屬雞。=MID("猴雞狗豬鼠?；⑼谬埳唏R羊",MOD(YEAR(A2),12)+1,1) （2007）如何求出一個(gè)人到某指定日期的周歲？=DATEDIF(起始日期,結(jié)束日期,"Y")計(jì)算距離退休年齡的公式=IF(E2="","",IF(E2>=V2,"已經(jīng)退休","距離退休還有"&DATEDIF(TODAY

27、(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),"Y")&"年"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),"YM")&"個(gè)月"&DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2),"Md")&"天")其中E2為年齡（可用身份證號(hào)碼的公式生成）；V2為法定退休年齡（男60，女50）公式為：

28、=IF(D2="","",IF(D2="男",60,50)D2為男或女（可用身份證號(hào)碼的公式生成）；U2為出生年月日（可用身份證號(hào)碼的公式生成）。求工齡=DATEDIF(B2,TODAY(),"y") =DATEDIF(B2,TODAY(),"ym") =DATEDIF(B2,TODAY(),"md") =DATEDIF(B2,TODAY(),"y")&"年"&DATEDIF(B2,TODAY(),"ym&qu

29、ot;)&"月"&DATEDIF(B2,TODAY(),"md")&"日" 計(jì)算工齡=DATEDIF(C6,C8,"y")求兩日期間的年數(shù)=DATEDIF(C6,C8,"ym")求兩日期間除去整年數(shù)剩余的月數(shù)=DATEDIF(C6,C8,"m")求兩日期間的總月數(shù)如果只需要算出周年的話(huà)，可以用=datedif("1978-8","2006-5","Y")年齡及工齡計(jì)算有出生年月如何求年齡？有工

30、作時(shí)間如何求工齡？（求出的結(jié)果為多少年另幾個(gè)月，如：0303的形式，即3年零3個(gè)月）。a1是出生年月或工作時(shí)間：=datedif(a1,today(),"y")=text(datedif(a1,today(),"y"),"00")&text(datedif(a1,today(),"m"),"00")如 B2=1964-9-1 則:=TEXT(DATEDIF(B2,TODAY(),"y"),"00")&TEXT(MOD(DATEDIF(B2,

31、TODAY(),"m"),12),"00")      '顯示 4009=TEXT(DATEDIF(B2,TODAY(),"y"),"00年")&TEXT(MOD(DATEDIF(B2,TODAY(),"m"),12),"00月")  '顯示 40年09月如果你找不到 DATEDIF 函數(shù)，也可以不用 DATEDIF 函數(shù),如 B2=1964-9-1 則:=TEXT(RIGHT(YEAR(NO

32、W()-B2),2),"00")&TEXT(MOD(MONTH(NOW()-B2)-1,12),"00")      '顯示 4009=TEXT(RIGHT(YEAR(NOW()-B2),2)&"年"&MOD(MONTH(NOW()-B2)-1,12)&"個(gè)月","")       '顯示 40年09個(gè)月自動(dòng)算出工齡日期格式為(yyyy.mm.dd)能否用：（yyyy.

33、mm.dd）這種格式來(lái)計(jì)算出工齡有多長(zhǎng)呢？ 以前用這樣一段（ =TEXT(RIGHT(YEAR(NOW()-A1),2)&"年"&MOD(MONTH(NOW()-A1)-1,12)&"個(gè)月","") ）。但這種方法只能用：（yyyy-mm-dd）這樣的日期格式才能實(shí)現(xiàn)！你不妨把“.”替換成“-”，不就行了嗎，再說(shuō)后者是日期的一種標(biāo)準(zhǔn)格式，=TEXT(RIGHT(YEAR(NOW()-SUBSTITUTE(A1,".","-"),2)&"年"&a

34、mp;MOD(MONTH(NOW()-SUBSTITUTE(A1,".","-")-1,12)&"個(gè)月","")【時(shí)間和日期應(yīng)用】自動(dòng)顯示當(dāng)前日期公式=YEAR(NOW() 當(dāng)前年=MONTH(NOW() 當(dāng)前月=DAY(NOW() 當(dāng)前日如何在單元格中自動(dòng)填入當(dāng)前日期Ctrl+;如何判斷某日是否星期天=WEEKDAY(A2,2)=TEXT(A1,"aaaa")=MOD(A1,7)<2某個(gè)日期是星期幾比如2007年2月9日，在一單元格內(nèi)顯示星期幾。=TEXT(A1,"aa

35、a") （五）=TEXT(A1,"aaaa") （星期五）=TEXT(A1,"ddd") （Fri）=TEXT(A1,"dddd") （Friday）什么函數(shù)可以顯示當(dāng)前星期如：星期二 10:41:56=TEXT(NOW(),"aaaa  hh:mm:ss")求本月天數(shù)設(shè)A1為2006-8-4  求本月天數(shù)A1=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)也有更簡(jiǎn)便的公式：=DAY(EOMONTH(NOW(),0) 需加載分析工具箱。當(dāng)前月天數(shù)：

36、 =DATE(YEAR(TODAY(),MONTH(TODAY()+1,1)-DATE(YEAR(TODAY(),MONTH(TODAY(),1)用公式算出除去當(dāng)月星期六、星期日以外的天數(shù)=SUMPRODUCT(-(MOD(ROW(INDIRECT(DATE(YEAR(NOW(),MONTH(NOW(),1)&":"&DATE(YEAR(NOW(),MONTH(NOW()+1,0),7)>1)顯示昨天的日期每天需要單元格內(nèi)顯示昨天的日期，但雙休日除外。例如，今天是7月3號(hào)的話(huà)，就顯示7月2號(hào)，如果是7月9號(hào)，就顯示7月6號(hào)。=IF(TEXT(TODAY

37、(),"AAA")="一",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日",TODAY()-2,TODAY()-1)=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1)關(guān)于取日期怎么設(shè)個(gè)公式使A1在年月日向后推5年,變成2011-7-15=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)=EDATE(A1,12*5)如何對(duì)日期進(jìn)行上、中、下旬區(qū)分=LOOKUP(DAY(A1),0,11,2

38、1,31,"上旬","中旬","下旬","下旬")如何獲取一個(gè)月的最大天數(shù)"=DAY(DATE(2002,3,1)-1)"或"=DAY(B1-1)",B1為"2001-03-01日期格式轉(zhuǎn)換公式將 “01/12/2005” 轉(zhuǎn)換成“20050112”格式RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),&q

39、uot;00") 該公式不用設(shè)置數(shù)據(jù)有效性,但要設(shè)置儲(chǔ)存格格式。也可以用下列兩方法：1、先轉(zhuǎn)換成文本, 然后再用字符處理函數(shù)。2、數(shù)據(jù)-分列 日期-MDY將“2005年9月”轉(zhuǎn)換成“200509”格式先用公式：=text(a1,"yyyymm")+0 然后將單元格格式為常規(guī)。將“2005-8-6”格式轉(zhuǎn)換為“20050806”格式用公式：=TEXT(A1,"YYYYMMDD")反之，將20050806轉(zhuǎn)為日期2005-8-6格式，可用公式：=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)另四種公式：text(a1

40、,"0000-00-00") 顯示：2005-08-06-TEXT(A1,"#-00-00")，把單元格設(shè)置為日期格式 顯示：2005-8-6TEXT(20050806,"0000-00-00")*1，單元格設(shè)置日期型 顯示：2005-8-6VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2) 顯示：2005-8-6將“20060501”轉(zhuǎn)換為“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5

41、,2),RIGHT(A2,2)將“199306”轉(zhuǎn)換為“1993-6”公式1：=LEFT(A3,4)&"-"&RIGHT(A3,2)*1公式2：=-TEXT(A3*100+1,"#-00-00") 公式2需要設(shè)置單元格格式，自定義：e-m公式3：=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")把198405轉(zhuǎn)換成1984.05 一、查找1984，替換1984.二、如果全部是年月的話(huà)，我個(gè)人建議， 1、采取輔助mid(xxxxxx,1,4) &

42、amp; "." & right(xxxxxx,2) 2、選中這列，用數(shù)據(jù)中的分列。然后三、單元格格式數(shù)字自定義，類(lèi)型下面輸入：#"."#將文本“2004.01.02” 轉(zhuǎn)換為日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)將2005-8-6轉(zhuǎn)換為2005年8月6日格式=TEXT(A1,"yyyy""年""m""月""d""日""")象22怎樣轉(zhuǎn)換成

43、22日？轉(zhuǎn)成當(dāng)年當(dāng)月的日子公式為：=date(year(now(),month(now(),22)將“2006年5月”轉(zhuǎn)換成“2006年05月” 公式為：=TEXT(A8,"yyyy""年""mm""月""")也可以這樣處理：選中單元格，設(shè)置單元格公式數(shù)字自定義，將yyyy“年”m“月”改為：yyyy“年”mm“月”，即可。但這方法打印出來(lái)顯示為：2006/5/將“1968年6月12日”轉(zhuǎn)換為“1968/6/12”格式=YEAR(A1)&"/"&MONTH(A

44、1)&"/"&DAY(A1) 顯示：1968/6/12=TEXT(A1,"yyyy/mm/dd") 顯示：1968/06/12將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1) 顯示：1968-6-12=TEXT(A1,"yyyy-mm-dd") 顯示：1968-06-12將1993-12-28的日期格式轉(zhuǎn)換成1993年12月=CONCATENATE(YEAR(A1),&

45、quot;年",MONTH(A1),"月") =YEAR(A1)&"年"&MONTH(A1)&"月"也可以自定義格式 $-404e"年"m"月" 將“1978-5-2”包含年月日的日期轉(zhuǎn)換成“197805”只有年月的格式y(tǒng)ear(A1)&text(month(A1),"00")要將“99.08.15” 格式轉(zhuǎn)換成“1999.08.15”如何做選中列，數(shù)據(jù)菜單中選分列，分列過(guò)程中“格式”選“日期YMD”，結(jié)束。要保持2005/

46、8/6格式當(dāng)輸入2005/8/6后系統(tǒng)自動(dòng)變成2005-8-6，要保持2005/8/6格式，可以使用強(qiáng)制文本（前面加'號(hào)）或使用公式=TEXT(A1,"YYYY/MM/DD")。也可以用另一種公式：=IF(ISERROR(TEXT(A1,"yyyy/mm/dd"),TEXT(A1,"0000!/00!/00"),TEXT(A1,"yyyy/mm/dd")將“二三年十二月二十五日”轉(zhuǎn)為“2003-12-25”格式，1、可以用數(shù)組公式將中文日期轉(zhuǎn)化為日期系列數(shù)=14610+MATCH(SUBSTITUTE(A3

47、,"元","一"),TEXT(ROW($14611:$55153),"DBNum1yyyy年m月d日"),0)該公式速度較慢。2、改進(jìn)后的公式，速度要快的多：=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),"DBNum10000"),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"DBNum1m月d日"),0),DAY(M

48、ATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:$366),"DBNum1m月d日"),0)要設(shè)置為1900年的日期格式。日期格式轉(zhuǎn)換如A列是月份數(shù)為8，B列是日期數(shù)為18，如何在C列顯示“8月18日”=A1&"月"&B1&"日"反之，要將C列的“8月18日” 直接分別到D、E列，顯示月份和日期，月數(shù)份=LEFT(C5,FIND("月",C5)-1)日期數(shù)=MID(C5,FIND("月&quo

49、t;,C5)+1,FIND("日",C5)-FIND("月",C5)-1)也可分別用公式：=month(-c5)=day(-c5)日期格式轉(zhuǎn)換問(wèn)題輸入的日期是:04-07-26. 與另一格的"001"合并,合并出來(lái)是:040726001.=TEXT(A1,"YYMMDD")&"001"要想自動(dòng)取得“編制日期：XXXX年X月X日”可在該單元格輸入 ="編制日期："&TEXT(TODAY(),"yyyy年m月d日")【排名及排序篩選】一個(gè)具有1

50、1項(xiàng)匯總方式的函數(shù)SUBTOTAL=SUBTOTAL(9,$B$2:B2)在數(shù)據(jù)篩選求和上有意想不到的功能，11項(xiàng)功能為：1、求平均數(shù)，2、求計(jì)數(shù)，3、求計(jì)數(shù)值（自動(dòng)篩選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標(biāo)準(zhǔn)偏差，8、求標(biāo)準(zhǔn)偏差、9、求和，10、求方差，11、求總體方差。自動(dòng)排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2<>A1,1,N(C1)+1)按奇偶數(shù)排序我想請(qǐng)教怎樣按奇數(shù)順序然后再按偶數(shù)順序排序=IF(MOD(A1,2),0,1)=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(R

51、OW()>50)*99自動(dòng)生成序號(hào)比如在第二列中輸入內(nèi)容回車(chē)后第一列的下一行自動(dòng)生成序列號(hào)。=IF(B2<>"",A2+1,"")如何自動(dòng)標(biāo)示A欄中的數(shù)字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)如何設(shè)置自動(dòng)排序A列自動(dòng)變成從小到大排列B=SMALL(A$2:A$28,ROW(1:1)A列自動(dòng)變成從大到小排列B=LARGE(A$2:A$28,ROW(1:1)重復(fù)數(shù)據(jù)得到唯一的排位序列想得到數(shù)據(jù)的出現(xiàn)總數(shù)嗎（1,2,2,3,4,4,5 數(shù)據(jù)的出現(xiàn)總數(shù)為5）？ 解答：不需要插列,不需要很多的函數(shù)就行了. =R

52、ANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1按字符數(shù)量排序制作歌曲清單時(shí)，習(xí)慣按字符數(shù)量來(lái)排列分類(lèi)，但是EXCEL并不能直接按字?jǐn)?shù)排序。需要先計(jì)算出每首歌曲的字?jǐn)?shù)，然后再進(jìn)行排序。 如A、B列分別為“歌手”和“歌名”，在C1輸入“字?jǐn)?shù)”，在C2輸入公式：LEN(B2) 下拖，單擊C2，單擊工具欄上的“升序排列”即可，刪除C列。排序字母與數(shù)字的混合內(nèi)容日常使用中，表格經(jīng)常會(huì)有包含字母和數(shù)字混合的數(shù)據(jù)，對(duì)此類(lèi)數(shù)據(jù)排序時(shí)，通常是先比較字母的大小，再比較數(shù)字的大小，但EXCEL是按照對(duì)字符進(jìn)行逐位比較來(lái)排序的，如下表：A7排在第5位，而不是第1位。排序結(jié)果無(wú)法令人滿(mǎn)意。A

53、1A1222A293A3174A435A76B207B38C1449C510C33AB1A7A0072A29A0293A43A0434A122A1225A317A3176B3B0037B20B0208C5C0059C33C03310C144C144如果希望EXCEL改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。在B1中輸入公式：LEFT(A1,1)& RIGHT("000"& RIGHT(A1,LEN(A1)-1),3) 下拖單擊B2，單擊工具欄上的“升序排列”即可。隨機(jī)排序如A、B列分別為“歌手”和“歌名”，在C1輸入“次序”，在C2輸入公式：RAND（），下拖，

54、單擊C2，單擊工具欄上的“降序排列”即可對(duì)歌曲清單進(jìn)行隨機(jī)排序。排序的問(wèn)題我想要這樣的排序: 2001-2003 2004-2006 2007-2009 2010-2012;其實(shí)不是數(shù)據(jù)排序，應(yīng)該是數(shù)據(jù)填充。輸入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3 即可。怎樣才能讓數(shù)列自動(dòng)加數(shù)怎樣做才能讓數(shù)列自動(dòng)加數(shù)A        A0001B        B0001A        A0002C   

55、     C0001A        A0003B        B0002C        C0002公式為=A1&"000"&COUNTIF(A$1:A1,A1)向下拖=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"0000")否則數(shù)字超過(guò)9就錯(cuò)誤了。一個(gè)排序問(wèn)題一個(gè)電子表格，格式是101、102. 999,10101、10102. 99901

56、,1010101,1020201. 9990101,請(qǐng)問(wèn)如何將它排列成101,10101,1010101,102,10201,1020101,. 999,99901,9990101 的形式。我在數(shù)字前加了個(gè)字母,比如"d"&"數(shù)字",然后用排序就可以把它們按你的需求排列了.最后再把字母"d"去掉。數(shù)字的自動(dòng)排序,插入后不變?1 趙一 總經(jīng)理2 趙二 副經(jīng)理3 趙三 副經(jīng)理4 趙四 技術(shù)員5 趙五6 趙六 員工如上的一個(gè)表,如何實(shí)現(xiàn)當(dāng)我把趙六這一整行(第6行)插入到上面的表中時(shí),A列的序列號(hào)不變?最后的效果如下:1 趙一 總經(jīng)理

57、2 趙二 副經(jīng)理3 趙六 員工 4 趙三 副經(jīng)理5 趙四 技術(shù)員6 趙五A1單元格輸入公式 =row(),往下拉,然后再插入。=SUBTOTAL(3,$B$2:$B2)在A1中輸入公式：“=if(b1="","",counta($b$1:b1)”后下拉復(fù)制至A列各行即可（“”不必輸入）根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動(dòng)序號(hào)姓名序號(hào)張三1張三1李四2李四2趙五3趙五3趙五3王六4王六4=(A1<>A2)+N(B1)=IF(A3=A2,B2,B2+1)姓名已排序：B2=SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)姓名未排序：

58、B2=IF(COUNTIF(A$2:A2,A2)>1,VLOOKUP(A2,A:B,2,0),SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)排名的函數(shù)用排名函數(shù)來(lái)對(duì)成績(jī)進(jìn)行排名，用起來(lái)非常地方便。=IF(ISERR(RANK(M3,M:M),"",RANK(M3,M:M)A列是成績(jī),B列是排名=SUMPRODUCT(A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9)+1自動(dòng)排名公式=RANK(C3,$C$3:$C$12)=RANK(A2,$A$2:$A$11,0)=RANK(C2,$C$2:$C$65)+COUNTI

59、F($C$2:C2,C2)-1百分比排名的公式寫(xiě)法為：=PERCENTRANK($C$3:$C$12,C3)平均分及總分排名=AVERAGE(B2:E2)=RANK(F2,$F$2:$F$65536)求名次排名統(tǒng)計(jì)成績(jī)時(shí)遇到一個(gè)分別求班級(jí)和年級(jí)總分名次排名的問(wèn)題，不曉得應(yīng)該運(yùn)用什么公式來(lái)實(shí)現(xiàn)。班級(jí)名次：=SUMPRODUCT(BJ=A2)*(ZF>E2)+1年級(jí)名次：=RANK(E2,ZF) 公式下拖。排名次根據(jù)總分值大小，只將姓名排序后, 降序結(jié)果=INDEX(A$2:A$6,RANK(D2,D$2:D$6)根據(jù)總分值大小，只將姓名排序后, 升序=INDEX(A$2:A$6,RANK(

60、D2,D$2:D$6,1)根據(jù)分?jǐn)?shù)進(jìn)行普通排名=RANK(A2,$A$2:$A$12)=RANK(A2,A$2:A$12)+COUNTIF(A$2:A2,A2)-1=SUMPRODUCT(1*($E$3:$E$12>=E3)=RANK(K3,$K$3:$K$26)=RANK(A2,A$2:A$12)=SUM(A$2:A$12>=A2)/COUNTIF(A$2:A$12,A$2:A$12)=COUNTIF($K$3:$K$26,">"&K3)+1=INDEX($A$2:$A$7,MATCH(LARGE($C$2:$C$7,ROW(A1),$C$2:$

61、C$7,0),1)=SUMPRODUCT($A$2:$A$12>A2)/COUNTIF($A$2:$A$12,$A$2:$A$12&"")+1=RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)-1,3,COUNTIF($A:$A,$A2),1)對(duì)于普通排名分?jǐn)?shù)相同時(shí)，按順序進(jìn)行不重復(fù)排名=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1=COUNTIF($K$32:K32,K32)-1+COUNTIF($K$3:$K$26,">"&K32)+1=SUMP

62、RODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100)=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100)依分?jǐn)?shù)比高低名次成績(jī)排名=RANK($E3,$E$3:$E$22) 內(nèi)建方式排名=SUMPRODUCT(1*($E$3:$E$12>=E3) 一般方式排名=RANK(E3,$E$3:$E$22)+SUM(IF($E$3:$E$22>E3,1/COUNTIF($E$3

63、:$E$22,$E$3:$E$22),0)-COUNTIF($E$3:$E$22,">"&E3) 一般方式排名=RANK(E3,$E$3:$E$12)+COUNTIF($E$3:E3,E3)-1不重復(fù)排名=SUMPRODUCT(1*($E$3:$E$12+ROW($E$3:$E$12)/100>=($E3+ROW(E3)/100)=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100)>=(E3+B3/100) 不重復(fù)排名=SUMPRODUCT(1*($E$3:$E$12+$B$3:$B$12/100+$C$3:$C$12

64、/10000)>=(E3+B3/100+C3/10000) 不重復(fù)排名=RANK($E3,$E$3:$E$22,1) 倒排序美國(guó)式排名=RANK(K247,$K$247:$K$270) =RANK(B1,$B1:$H1)中國(guó)式排名=RANK(B2,$B$2:$B$21,0)=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1=SUM(IF($A$1:$E$1>=A1,1/COUNTIF($A$1:$E$1,$A$1:$E$1),"")=SUMPRODUCT($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21)=SUMPRODUCT(B$3:B$21>B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)+1 （升序）=SUMPRODUCT(B$3:B$21<B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)+1 （降序）=SUM(-(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)+1=SUM(IF($B$3: