EXCEL函數(shù)公式集(公式匯編)

1、excel常用函數(shù)公式及技巧搜集【身份證信息？提取】從身份證號碼中提取出生年月日=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)*19&mid(a2,7,6+(len(a2)=18)*2),"#-00-00")+0,)顯示格式均為yyyy-m-d。（最簡單的公式，把單元格設(shè)置為日期格式）=if(len(a2)=1

2、5,"19"&mid(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("19&quo

3、t;&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日”改成“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),

4、"#-00-00")+0=if(len(a2)=18,mid(a2,7,4)&-mid(a2,11,2),19&mid(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)*19&mid(a1,7,6+(len(a1)=18)*2),"#-00-00")

5、從身份證號碼中提取出性別=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

6、)=15*19&mid(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,"19"&mid(a1,7,2),mid(a1,7,4)=year(today()-value(mid(b1,7,4)&"歲"=year(today()-if(mid(b1,18,

7、1)="",concatenate("19",mid(b1,7,2),mid(b1,7,4)按身份證號號碼計算至今天年齡 =datedif(text(len(a1)=15)*19&mid(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&qu

8、ot;,"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,5

9、0,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)【年齡和工齡計算】根據(jù)出生年月計算年齡datedif(a1,today(),"y")datedif(a1,today(),"y")&"周歲"da

10、tedif(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對應(yīng)生肖是雞，xxx就屬雞。=mid("猴雞狗豬鼠?；⑼谬埳唏R羊",mod(year(a2),12)+1,1) （2007）如何求出一個人到某指定日期的周歲？=datedif(起始日期,結(jié)束日期,"y")計算距離退休年齡的公

11、式=if(e2="","",if(e2>=v2,"已經(jīng)退休","距離退休還有"&datedif(today(),date(year(u2)+(v2),month(u2),day(u2),"y")&"年"&datedif(today(),date(year(u2)+(v2),month(u2),day(u2),"ym")&"個月"&datedif(today(),date(year(u2)+(

12、v2),month(u2),day(u2),"md")&"天")其中e2為年齡（可用身份證號碼的公式生成）；v2為法定退休年齡（男60，女50）公式為：=if(d2="","",if(d2="男",60,50)d2為男或女（可用身份證號碼的公式生成）；u2為出生年月日（可用身份證號碼的公式生成）。求工齡=datedif(b2,today(),"y") =datedif(b2,today(),"ym") =datedif(b2,today(),&qu

13、ot;md") =datedif(b2,today(),"y")&"年"&datedif(b2,today(),"ym")&"月"&datedif(b2,today(),"md")&"日" 計算工齡=datedif(c6,c8,"y")求兩日期間的年數(shù)=datedif(c6,c8,"ym")求兩日期間除去整年數(shù)剩余的月數(shù)=datedif(c6,c8,"m")求兩日期間

14、的總月數(shù)如果只需要算出周年的話，可以用=datedif("1978-8","2006-5","y")年齡及工齡計算有出生年月如何求年齡？有工作時間如何求工齡？（求出的結(jié)果為多少年另幾個月，如：0303的形式，即3年零3個月）。a1是出生年月或工作時間：=datedif(a1,today(),"y")=text(datedif(a1,today(),"y"),"00")&text(datedif(a1,today(),"m"),"00&qu

15、ot;)如 b2=1964-9-1 則:=text(datedif(b2,today(),"y"),"00")&text(mod(datedif(b2,today(),"m"),12),"00")      '顯示 4009=text(datedif(b2,today(),"y"),"00年")&text(mod(datedif(b2,today(),"m"),12),"00月&qu

16、ot;)  '顯示 40年09月如果你找不到 datedif 函數(shù)，也可以不用 datedif 函數(shù),如 b2=1964-9-1 則:=text(right(year(now()-b2),2),"00")&text(mod(month(now()-b2)-1,12),"00")      '顯示 4009=text(right(year(now()-b2),2)&"年"&mod(month(now()-b2)-1,12)&&qu

17、ot;個月","")       '顯示 40年09個月自動算出工齡日期格式為(yyyy.mm.dd)能否用：（yyyy.mm.dd）這種格式來計算出工齡有多長呢？ 以前用這樣一段（ =text(right(year(now()-a1),2)&"年"&mod(month(now()-a1)-1,12)&"個月","") ）。但這種方法只能用：（yyyy-mm-dd）這樣的日期格式才能實現(xiàn)！你不妨把“.”替換成“-”，不就行了嗎，再說后

18、者是日期的一種標準格式，=text(right(year(now()-substitute(a1,".","-"),2)&"年"&mod(month(now()-substitute(a1,".","-")-1,12)&"個月","")【時間和日期應(yīng)用】自動顯示當前日期公式=year(now() 當前年=month(now() 當前月=day(now() 當前日如何在單元格中自動填入當前日期ctrl+;如何判斷某日是否星期天=wee

19、kday(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(),"aaaa  hh:mm:ss")求本月天數(shù)設(shè)a1為2006-8

20、-4  求本月天數(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)&":"&date(year(now(),month(

21、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)關(guān)于取日期怎么設(shè)個公式使a1在年月日向后推

22、5年,變成2011-7-15=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(b1-1)",b1為"2001-03-01日期格式轉(zhuǎn)換公式將 “01/12/2005” 轉(zhuǎn)換成“20050112”格式right(a1

23、,4)&mid(a1,4,2)&left(a1,2)year($a2)&text(month($a2),"00")&text(day($a2),"00") 該公式不用設(shè)置數(shù)據(jù)有效性,但要設(shè)置儲存格格式。也可以用下列兩方法：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,"

24、yyyymmdd")反之，將20050806轉(zhuǎn)為日期2005-8-6格式，可用公式：=date(left(a1,4),mid(a1,5,2),right(a1,2)另四種公式：text(a1,"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

25、)&"-"&right(a1,2) 顯示：2005-8-6將“20060501”轉(zhuǎn)換為“2006-05-01”格式=date(left(a2,4),mid(a2,5,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

26、"),"e-m")把198405轉(zhuǎn)換成1984.05 一、查找1984，替換1984.二、如果全部是年月的話，我個人建議， 1、采取輔助mid(xxxxxx,1,4) & "." & right(xxxxxx,2) 2、選中這列，用數(shù)據(jù)中的分列。然后三、單元格格式數(shù)字自定義，類型下面輸入：#"."#將文本“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,&q

27、uot;yyyy""年""m""月""d""日""")象22怎樣轉(zhuǎn)換成22日？轉(zhuǎn)成當年當月的日子公式為：=date(year(now(),month(now(),22)將“2006年5月”轉(zhuǎn)換成“2006年05月” 公式為：=text(a8,"yyyy""年""mm""月""")也可以這樣處理：選中單元格，設(shè)置單元格公式數(shù)字自定義，將yyyy“年”m“月”改為：yyy

28、y“年”mm“月”，即可。但這方法打印出來顯示為：2006/5/將“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將“1968年6月12日”轉(zhuǎn)換為“1968-6-12”格式=year(a1)&"-"&month(a1)&"-"&day(a1) 顯示：1968-

29、6-12=text(a1,"yyyy-mm-dd") 顯示：1968-06-12將1993-12-28的日期格式轉(zhuǎn)換成1993年12月=concatenate(year(a1),"年",month(a1),"月") =year(a1)&"年"&month(a1)&"月"也可以自定義格式 $-404e"年"m"月" 將“1978-5-2”包含年月日的日期轉(zhuǎn)換成“197805”只有年月的格式y(tǒng)ear(a1)&text(

30、month(a1),"00")要將“99.08.15” 格式轉(zhuǎn)換成“1999.08.15”如何做選中列，數(shù)據(jù)菜單中選分列，分列過程中“格式”選“日期ymd”，結(jié)束。要保持2005/8/6格式當輸入2005/8/6后系統(tǒng)自動變成2005-8-6，要保持2005/8/6格式，可以使用強制文本（前面加'號）或使用公式=text(a1,"yyyy/mm/dd")。也可以用另一種公式：=if(iserror(text(a1,"yyyy/mm/dd"),text(a1,"0000!/00!/00"),text(a1,&

31、quot;yyyy/mm/dd")將“二三年十二月二十五日”轉(zhuǎn)為“2003-12-25”格式，1、可以用數(shù)組公式將中文日期轉(zhuǎn)化為日期系列數(shù)=14610+match(substitute(a3,"元","一"),text(row($14611:$55153),"dbnum1yyyy年m月d日"),0)該公式速度較慢。2、改進后的公式，速度要快的多：=date(1899+match(left(a7,4),text(row($1900:$2100),"dbnum10000"),0),month(match(su

32、bstitute(mid(a7,6,7),"元","一"),text(row($1:$366),"dbnum1m月d日"),0),day(match(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&"日&quo

33、t;反之，要將c列的“8月18日” 直接分別到d、e列，顯示月份和日期，月數(shù)份=left(c5,find("月",c5)-1)日期數(shù)=mid(c5,find("月",c5)+1,find("日",c5)-find("月",c5)-1)也可分別用公式：=month(-c5)=day(-c5)日期格式轉(zhuǎn)換問題輸入的日期是:04-07-26. 與另一格的"001"合并,合并出來是:040726001.=text(a1,"yymmdd")&"001"要想自

34、動取得“編制日期：xxxx年x月x日”可在該單元格輸入 ="編制日期："&text(today(),"yyyy年m月d日")【排名及排序篩選】一個具有11項匯總方式的函數(shù)subtotal=subtotal(9,$b$2:b2)在數(shù)據(jù)篩選求和上有意想不到的功能，11項功能為：1、求平均數(shù)，2、求計數(shù)，3、求計數(shù)值（自動篩選序列）4、求最大值，5、求最小值，6、求乘積，7、求總體標準偏差，8、求標準偏差、9、求和，10、求方差，11、求總體方差。自動排序=subtotal(3,$b$2:b2)*1=if(a2<>a1,1,n(c1)+1)

35、按奇偶數(shù)排序我想請教怎樣按奇數(shù)順序然后再按偶數(shù)順序排序=if(mod(a1,2),0,1)=if(row()>50,(row()*2)-100,(row()*2)-1)=row()*2-1-(row()>50)*99自動生成序號比如在第二列中輸入內(nèi)容回車后第一列的下一行自動生成序列號。=if(b2<>"",a2+1,"")如何自動標示a欄中的數(shù)字大小排序？=rank(a1,$a$1:$a$5)=rank(a1,a:a)如何設(shè)置自動排序a列自動變成從小到大排列b=small(a$2:a$28,row(1:1)a列自動變成從大到小排列

36、b=large(a$2:a$28,row(1:1)重復數(shù)據(jù)得到唯一的排位序列想得到數(shù)據(jù)的出現(xiàn)總數(shù)嗎（1,2,2,3,4,4,5 數(shù)據(jù)的出現(xiàn)總數(shù)為5）？ 解答：不需要插列,不需要很多的函數(shù)就行了. =rank(b3,b$3:b$12)+countif(b$3:b3,b3)-1按字符數(shù)量排序制作歌曲清單時，習慣按字符數(shù)量來排列分類，但是excel并不能直接按字數(shù)排序。需要先計算出每首歌曲的字數(shù)，然后再進行排序。 如a、b列分別為“歌手”和“歌名”，在c1輸入“字數(shù)”，在c2輸入公式：len(b2) 下拖，單擊c2，單擊工具欄上的“升序排列”即可，刪除c列。排序字母與數(shù)字的混合內(nèi)容日常使用中，表格經(jīng)

37、常會有包含字母和數(shù)字混合的數(shù)據(jù)，對此類數(shù)據(jù)排序時，通常是先比較字母的大小，再比較數(shù)字的大小，但excel是按照對字符進行逐位比較來排序的，如下表：a7排在第5位，而不是第1位。排序結(jié)果無法令人滿意。a1a1222a293a3174a435a76b207b38c1449c510c33ab1a7a0072a29a0293a43a0434a122a1225a317a3176b3b0037b20b0208c5c0059c33c03310c144c144如果希望excel改變排序的規(guī)則，需要將數(shù)據(jù)做一些改變。在b1中輸入公式：left(a1,1)& right("000"&a

38、mp; right(a1,len(a1)-1),3) 下拖單擊b2，單擊工具欄上的“升序排列”即可。隨機排序如a、b列分別為“歌手”和“歌名”，在c1輸入“次序”，在c2輸入公式：rand（），下拖，單擊c2，單擊工具欄上的“降序排列”即可對歌曲清單進行隨機排序。排序的問題我想要這樣的排序: 2001-2003 2004-2006 2007-2009 2010-2012;其實不是數(shù)據(jù)排序，應(yīng)該是數(shù)據(jù)填充。輸入公式=left(e3,4)+3&"-"&right(e3,4)+3 即可。怎樣才能讓數(shù)列自動加數(shù)怎樣做才能讓數(shù)列自動加數(shù)a    &

39、#160;   a0001b        b0001a        a0002c        c0001a        a0003b        b0002c        c0002公式為=a1&"000"&countif(a$1:a1,a1)向下拖=text(countif(a$1:a1,a1),&

40、quot;!"&a1&"0000")否則數(shù)字超過9就錯誤了。一個排序問題一個電子表格，格式是101、102. 999,10101、10102. 99901,1010101,1020201. 9990101,請問如何將它排列成101,10101,1010101,102,10201,1020101,. 999,99901,9990101 的形式。我在數(shù)字前加了個字母,比如"d"&"數(shù)字",然后用排序就可以把它們按你的需求排列了.最后再把字母"d"去掉。數(shù)字的自動排序,插入后不變?1 趙

41、一 總經(jīng)理2 趙二 副經(jīng)理3 趙三 副經(jīng)理4 趙四 技術(shù)員5 趙五6 趙六 員工如上的一個表,如何實現(xiàn)當我把趙六這一整行(第6行)插入到上面的表中時,a列的序列號不變?最后的效果如下:1 趙一 總經(jīng)理2 趙二 副經(jīng)理3 趙六 員工 4 趙三 副經(jīng)理5 趙四 技術(shù)員6 趙五a1單元格輸入公式 =row(),往下拉,然后再插入。=subtotal(3,$b$2:$b2)在a1中輸入公式：“=if(b1="","",counta($b$1:b1)”后下拉復制至a列各行即可（“”不必輸入）根據(jù)規(guī)律的重復的姓名列產(chǎn)生自動序號姓名序號張三1張三1李四2李四2趙五3趙

42、五3趙五3王六4王六4=(a1<>a2)+n(b1)=if(a3=a2,b2,b2+1)姓名已排序：b2=sumproduct(1/countif(a$2:a2,a$2:a2)姓名未排序：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ù)來對成績進行排名，用起來非常地方便。=if(iserr(rank(m3,m:m),"",rank(m3,m:m)a列是成績,b列是排名=sumproduct(a$1:a$9>a1)/c

43、ountif(a$1:a$9,a$1:a$9)+1自動排名公式=rank(c3,$c$3:$c$12)=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)計成績時遇到一個分別求班級和年級總分名次排名的問題，不曉得應(yīng)該運用什么公式來實現(xiàn)。班級名次：=sumproduct(bj=a2)*(zf>e2)+1年級名次：=rank(e2,zf)

44、 公式下拖。排名次根據(jù)總分值大小，只將姓名排序后, 降序結(jié)果=index(a$2:a$6,rank(d2,d$2:d$6)根據(jù)總分值大小，只將姓名排序后, 升序=index(a$2:a$6,rank(d2,d$2:d$6,1)根據(jù)分數(shù)進行普通排名=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

45、)=countif($k$3:$k$26,">"&k3)+1=index($a$2:$a$7,match(large($c$2:$c$7,row(a1),$c$2:$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)對于普通排名分數(shù)相同時，按順序進行不重復排名=rank(k32,$k$32:$k$55)+co

46、untif($k$32:$k32,k32)-1=countif($k$32:k32,k32)-1+countif($k$3:$k$26,">"&k32)+1=sumproduct(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)依分數(shù)比高低名次成績排名=rank($e3,$e$3:$e$22) 內(nèi)建方式排

47、名=sumproduct(1*($e$3:$e$12>=e3) 一般方式排名=rank(e3,$e$3:$e$22)+sum(if($e$3:$e$22>e3,1/countif($e$3:$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不重復排名=sumproduct(1*($e$3:$e$12+row($e$3:$e$12)/100>=($e3+row(e3)/100)=sumproduct(1*

48、($e$3:$e$12+$b$3:$b$12/100)>=(e3+b3/100) 不重復排名=sumproduct(1*($e$3:$e$12+$b$3:$b$12/100+$c$3:$c$12/10000)>=(e3+b3/100+c3/10000) 不重復排名=rank($e3,$e$3:$e$22,1) 倒排序美國式排名=rank(k247,$k$247:$k$270) =rank(b1,$b1:$h1)中國式排名=rank(b2,$b$2:$b$21,0)=rank(b1,$b1:$h1)+countif($b$1:b1,b1)-1=sum(if($a$1:$e$1>

49、=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=s

50、um(if($b$3:$b$21<=b3,"",1/(countif($b$3:b$21,b$3:b$21)+1（升序）=sum(if($b$3:$b$21<=b3,1/(countif($b$3:b$21,b$3:b$21),"") （降序）=sum(if($b$2:$b$21>b2,1/countif($b$2:b$21,b$2:b$21)+1=sum(if($a$1:$e$1>=a1,1/countif($a$1:$e$1,$a$1:$e$1),"")=sum($b$2:$b$21>b2)*(mat

51、ch($b$2:b$21,b$2:b$21,)=row($1:$20)+1=sum(if($b$1:$h$1<=b1,"",1/(countif($b$1:$h$1,$b$1:$h$1)+1求最精簡的自動排名公式=rank(e2,$e$2:$e$21)=rank(a2,$a$2:$a$9,0)=rank(a2,$a$2:$a$10)+countif($a$2:$a2,$a2)-1（如果數(shù)據(jù)列中數(shù)值有相同）=rank(f10,$f10:$q10)+countif($f10:f10,f10)-1=index(a:a,1/mod(large(e$2:e$21+1/row($

52、2:$21),row(1:1),1)=lookup(1,0/($f$2:$f$21=a27)*(countif(d$26:d26,$a$2:$a$21)=0),$a$2:$a$21)=indirect("a"&right(large($e$2:$e$21*100+row($a$2:$a$21),row(a1),2)=rank(c2,offset($c$1,match(e2,$e$2:$e$768,),countif($e$2:$e$768,e2)數(shù)組公式=index(a:a,mod(large(e$2:e$21*100+row($2:$21),row(1:1),10

53、0)=offset($a$1,right(large($e$2:$e$21*1000+row($e$2:$e$21),row()-25),3)-1,)=offset($a$1,right(large($e$2:$e$21*100+row($a$1:$a$20),row(a3),2),)=text(sumproduct($e$2:$e$21>=e2)/countif($e$2:$e$21,$e$2:$e$21),"第dbnum1g/通用格式名")排序后排名=sum(if($b$2:$b$15>=b2,1/countif($b$2:$b$15,$b$2:$b$15)

54、=sumproduct(b$2:b$15>=b2)/countif(b$2:b$15,b$2:b$15)位次排名=if($b2:$o2>=0,rank($b2:$o2,$b2:$o2,0),)根據(jù)雙列成績進行共同排名=rank(c345,($c$345:$c$356,$h$345:$h$356)在雙列間排名=rank(b2,($b$2:$b$26,$e$2:$e$16)等次排名由大到小排名=rank(b3,$b$3:$b$12)=sumproduct($a$16:$a$25=a16)*($b$16:$b$25>b16)+1由小到大排名=rank(b3,$b$3:$b$12,1

55、)=sumproduct($a$16:$a$25=a16)*($b$16:$b$25<b16)+1不等次排名(行小排先）由大到小=rank(b3,$b$3:$b$12)+countif($b$3:b3,b3)-1=sumproduct($a$16:$a$25=a16)*($b$16:$b$25-row($b$16:$b$25)/10000>b16-row(b16)/10000)+1由小到大=rank(b3,$b$3:$b$12,1)+countif($b$3:b3,b3)-1=sumproduct($a$16:$a$25=a16)*($b$16:$b$25+row($b$16:$b

56、$25)/10000<b16+row(b16)/10000)+1不等次排名(行大排先）由大到小=count($b$3:$b$12)-rank(b3,$b$3:$b$12,1)-countif($b$3:b3,b3)+2=sumproduct($a$16:$a$25=a16)*($b$16:$b$25+row($b$16:$b$25)/10000>b16+row(b16)/10000)+1由小到大=count($b$3:$b$12)-rank(b3,$b$3:$b$12)-countif($b$3:b3,b3)+2=sumproduct($a$16:$a$25=a16)*($b$16

57、:$b$25-row($b$16:$b$25)/10000<b16-row(b16)/10000)+1順次排名由大到小=sumproduct(b$3:b$12>b3)*(1/countif(b$3:b$12,b$3:b$12)+1=sumproduct($a$16:$a$25=a16)*($b$16:$b$25>b16)/countif($k$16:$k$25,$k$16:$k$25)+1由小到大=sumproduct(b$3:b$12<b3)*(1/countif(b$3:b$12,b$3:b$12)+1=sumproduct($a$16:$a$25=a16)*($b

58、$16:$b$25<b16)/countif($k$16:$k$25,$k$16:$k$25)+1有并列排名=rank(b2,$b$2:$b$20)=sumproduct(1*($b$3:$b$21>b3)+1=countif($b$3:$b$21,">"&b3)+1=sum(if($b$3:$b$21>b3,1,0)+1=19-frequency($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

59、$21)+countif($b$3:$b3,b3)-1=sumproduct(b3-row()/1000<$b$3:$b$21-row($b$3:$b$21)/1000)*1)+1=19-frequency($b$3:$b$21-row($b$3:$b$21)/1000,b3-row()/1000)+1=sum(if($b$3:$b$21-row($b$3:$b$21)/1000>b3-row()/1000,1,0)+1有并列分段排名=sumproduct($a$3:$a$21=a3)*($c$3:$c$21>c3)+1=19-frequency($a$3:$a$21=a3)

60、*($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:"&countif($a$3:$a$21,a3),0)=match(c3,large(if($a$3:$a$21=a3,$c$3:$c$21),row(indirect("1:"&countif($a$3:$a$21,a3),0)=sumproduct($a$3:$a$21=a3)*($c$3:$c$21>c3)/countif($n

61、$3:$n$21,$n$3:$n$21)+1（需輔助列）無并列分段排名=sumproduct($a$3:$a$21=a3)*($c$3:$c$21-row($c$3:$c$21)/10000>c3-row(c3)/10000)+1=19-frequency($a$3:$a$21=a3)*($c$3:$c$21-row($c$3:$c$21)/1000),c3-row()/1000)+1成績排名序號姓名語文數(shù)學英語1楊增海1351361462郭愛玲1381371413華志鋒1341381414袁文飛134143135能否用一個公式直接找出所用考生中語文成績中第100名的成績是多少?=lar

62、ge(c2:c417,100)=percentile(c2:c417,(416-100)/416)=percentile($c$2:$c$417,(counta($c$2:$c$417)-100)/counta($c$2:$c$417)能否用一個公式直接找出所用考生中語文成績中按與考人數(shù)的35%切線中位于第35%的成績是多少?升冪=small(c2:c417,416*0.35)=percentile($c$2:$c$417,0.35) 降冪=large(c2:c417,416*0.35)=percentile($c$2:$c$417,1-0.35)如何排名1、對英語進行排名，缺考不計算在內(nèi)。2

63、、對英語進行排名，缺考計算在內(nèi)。英語英語排名429623721485485721544429缺考缺考458467缺考不計算在內(nèi)b2=if(a2="缺考","",rank(a2,$a$2:$a$13) 然后按照b列排序缺考計算在內(nèi)=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ù)據(jù)排名（隔幾行排名）=if(a2=&qu