【正文】 $B$2:B2) 說明：匯總時，不要在“全選”狀態(tài)下進行，先 “篩選”出某一單位，自動求和 ∑ 。 英語 英語排名 42 9 62 3 72 1 48 5 48 5 72 1 54 4 42 9 缺考 缺考 45 8 46 7 缺考不計算在內 b2=IF(A2=缺考 ,RANK(A2,$A$2:$A$13)) 然后按照 B 列排序 缺考計算在內 =IF(A2=缺考 ,COUNTIF($A$2:$A$13,=0)+1,RANK(A2,$A$2:$A$13)) =IF(A2=缺考 ,COUNT($A$2:$A$13)+1,RANK(A2,$A$2:$A$13,0)) 數(shù)據排名 （隔幾行排名） =IF(A2=,RANK(A2,$A$2:$A$11,0)) 如果隔幾行排名，如 下表，第五行、第九行和第十二行不參與排名 。B3)+1 ｛ =SUM(IF($B$3:$B$21B3,1,0))+1｝ =19FREQUENCY($B$3:$B$21,B3)+1 =SUMPRODUCT(($B$2:$B$20=B2)/COUNTIF($B$2:$B$20,$B$2:$B$20)) 無 并列排名 =RANK(B3,$B$3:$B$21)+COUNTIF($B$3:$B3,B3)1 =SUMPRODUCT((B3ROW()/1000$B$3:$B$21ROW($B$3:$B$21)/1000)*1)+1 =19FREQUENCY($B$3:$B$21ROW($B$3:$B$21)/1000,B3ROW()/1000)+1 ｛ =SUM(IF($B$3:$B$21ROW($B$3:$B$21)/1000B3ROW()/1000,1,0))+1｝ 有 并列 分段 排名 =SUMPRODUCT(($A$3:$A$21=A3)*($C$3:$C$21C3))+1 =19FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21),C3)+1 ｛ =MATCH(C3,LARGE(OFFSET($C$2,IF($A$3:$A$21=A3,ROW($A$3:$A$21)2),),ROW(INDIRECT(1:amp。))+1 =RANK(D2,OFFSET($A$1,MATCH($A2,$A:$A,0)1,3,COUNTIF($A:$A,$A2),1)) 對于普通排名分數(shù)相同時，按順序進行不重復排名 =RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)1 =COUNTIF($K$32:K32,K32)1+COUNTIF($K$3:$K$26,amp。 =IF(ISERR(RANK(M3,M:M)),RANK(M3,M:M)) A 列是成績 ,B 列是排名 =SUMPRODUCT((A$1:A$9A1)/COUNTIF(A$1:A$9,A$1:A$9))+1 自動排名公式 =RANK(C3,$C$3:$C$12) 11 =RANK(A2,$A$2:$A$11,0) =RANK(C2,$C$2:$C$65)+COUNTIF($C$2:C2,C2)1 百分比排名的公式寫法為： =PERCENTRANK($C$3:$C$12,C3) 平均分 及總分 排名 =AVERAGE(B2:E2) =RANK(F2,$F$2:$F$65536) 求名次排名 統(tǒng)計成績時遇到一個分別求班級和年級總分名次排名的問題，不曉得應該運用什么公式來實現(xiàn) 。 我在數(shù)字前加了個字母 ,比如 damp。COUNTIF(A$1:A1,A1)向下拖 =TEXT(COUNTIF(A$1:A1,A1),!amp。amp。 隨機排序 如 A、 B 列分別為“歌手”和“歌名”，在 C1 輸入“次序”，在 C2 輸入公式： ＝ RAND（） ， 下拖 ，單擊 C2，單擊工具欄上的“降序排列”即可對歌曲清單進行隨機排序。 A 1 A122 2 A29 3 A317 4 A43 5 A7 6 B20 7 B3 8 C144 9 C5 9 10 C33 如果希望 EXCEL改變排序 的規(guī)則，需要將數(shù)據做一些改變。需要先 計算出每首歌曲的字數(shù)，然后再進行排序。001 要想自動取得“編制日期： XXXX 年 X 月 X 日” 可在該單元格輸入 =編制日期： amp。 日期格式轉換 如 A 列是月份數(shù)為 8， B 列是日期數(shù)為 18，如何在 C 列顯示“ 8 月 18 日” =A1amp。 要保持 2020/8/6 格式 當 輸入 2020/8/6 后系統(tǒng)自動變成 202086，要保持 2020/8/6 格式，可以 使用強制文本（前面加 39。年 amp。amp。/amp。然后????? 三、 單元格格式／數(shù)字／自定義，類型下面輸入： . 將文本 “” 轉換為日期格式： 202012 =DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)) 將 202086 轉換為 2020 年 8 月 6 日 格式 =TEXT(A1,yyyy年 m月 d日 。RIGHT(A3,2)*1 公式 2： =TEXT(A3*100+1,0000) 公式 2 需要設置單元格格式，自定義： em 公式 3： =TEXT(TEXT(A3amp。MID(A1,5,2)amp。 也可以用下列兩方法： 先轉換成文本 , 然后再用字符處理函數(shù)。MID(A1,4,2)amp。:amp。個月 ,) ㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜ 4 【時間和日期應用】 自動顯示當前日期公式 =YEAR(NOW()) 當前年 =MONTH(NOW()) 當前月 =DAY((NOW())) 當前日 如何在單元格中自動填入當前日期 Ctrl+。個月 ,) ）。個月,) 39。TEXT(MOD(MONTH(NOW()B2)1,12),00) 39。TEXT(MOD(DATEDIF(B2,TODAY(),m),12),00) 39。DATEDIF(B2,TODAY(),md)amp。 3 求工齡 =DATEDIF(B2,TODAY(),y) =DATEDIF(B2,TODAY(),ym) =DATEDIF(B2,TODAY(),md) =DATEDIF(B2,TODAY(),y)amp。DATEDIF(TODAY(),DATE(YEAR(U2)+(V2),MONTH(U2),DAY(U2)),YM)amp。即用誕生年份除以 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(K1,11,2)amp。MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())VALUE(MID(B1,7,4))amp。MID(A1,13,2)amp。MID(A2,9,2)) =MID(A1,7,4)amp。 =TEXT((LEN(A1)=15)*19amp。（ 如果要求為“ 1995/03/29”格式的話，將 ”” 換成 ”/”即可） =IF(D4=,IF(LEN(D4)=15,TEXT((19amp。amp。amp。 1 excel常用函數(shù)公式及技巧搜集 【身份證信息 相關數(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,2)amp。MID(A2,11,2),MID(A2,7,4)amp。MID(A2,13,2)) 顯示格式為 yyyymmdd。MID(A1:A2,7,6)) 顯示格式為 yyyymmdd。MID(A2,7,2)amp。月 amp。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。/amp。周歲 ＝ DATEDIF(A1,NOW(),y) 根據出生年月 推 算 生肖 中國人有 12 生肖，屬什么可以推算出來。年amp。天 )) 其中 E2 為 年齡 （可用身份證號碼的公式生成）； V2 為法定退休年齡 （男 60，女 50）公式為： =IF(D2=,IF(D2=男 ,60,50)) D2 為男或女 （可用身份證號碼的公式生成） ； U2 為出生年月日 （可用身份證號碼的公式生成）。月amp。text(datedif(a1,today(),m),00) 如 [B2]=196491 則 : =TEXT(DATEDIF(B2,TODAY(),y),00)amp。顯示 40 年 09 月 如果你找不到 DATEDIF 函數(shù)，也可以不用 DATEDIF 函數(shù) , 如 [B2]=196491 則 : =TEXT(RIGHT(YEAR(NOW()B2),2),00)amp。MOD(MONTH(NOW()B2)1,12)amp。MOD(MONTH(NOW()A1)1,12)amp。MOD(MONTH(NOW()SUBSTITUTE(A1,.,))1,12)amp。 當前月天數(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。 =IF(TEXT(TODAY(),AAA)=一 ,TODAY()3,IF(TEXT(TODAY(),AAA)=日,TODAY()2,TODAY()1)) =IF(TEXT(TODAY(),AAA)=一 ,TODAY()3,TODAY()1) 5 關于取 日期 怎么設個公式使 A1 在年月日向后推 5 年 ,變成 2020715 =DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)) =EDATE(A1,12*5) 如何對日期進行上、中、下旬區(qū)分 =LOOKUP(DAY(A1),{0,11,21,31},{上旬 ,中旬 ,下旬 ,下旬 }) 如何獲取一個月的最大天數(shù) =DAY(DATE(2020,3,1)1)或 =DAY(B11),B1 為 20200301 日期 格式 轉換公式 將 “01/12/2020” 轉換成 “20200112”格式 ＝ RIGHT(A1,4)amp。TEXT(DAY($A2),00) 該公式 不用設置數(shù)據有效性 ,但要設置儲存格格式 。amp。amp。 right(xxxxxx,2) 選中這列，用數(shù)據中的分列。但這方法打印出來顯示為： 2020/5/ 將“ 1968 年 6 月 12 日”轉換為“ 1968/6/12”格式 =YEAR(A1)amp。DAY(A1) 顯示： 1968/6/12 =TEXT(A1,yyyy/mm/dd) 顯示： 1968/06/12 將“ 1968 年 6 月 12 日”轉換為“ 1968612”格式 =YEAR(A1)amp。DAY(A1) 顯示： 1968612 =TEXT(A1,yyyymmdd) 顯示： 19680612 將 19931228 的日期格式轉換成 1993 年 12 月 =CONCATENATE(YEAR(A1),年 ,MONTH(A1),月 ) =YEAR(A1)amp。text(month(A1),00) 要將“ ” 格式轉換成 “ ”如何做 選中列，數(shù)據菜單中選分列，分列過程中 ―格式 ‖選 ―日期 YMD‖，結束。 改進后的公式，速度要快的多： ｛ =DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),[DBNum1]0000),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),元 ,一 ),TEXT(ROW($1:$366),[DBNum1]m月 d 日 ),0)),DAY(MATCH(SUBSTITUTE(MID(A