函數(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)=15,19&mid(a2,7,2)&-&mid(a2,9,2)&-&mid(a2,11,2),mid(a2,7,

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

3、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,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)從身份證號碼中提取出性別=if(mod(mid(a1,15,3),2),男,女) （最簡單公式）=if(

4、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*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,

5、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)按身份證號號碼計算至今天年齡 =datedif(text(len(a1)=15)*19&mid(a1,7,6+(len(a1)=18)*2),#-00-00),today(),y)以2006年10月31日為基準日,按按身份證計算年齡(周歲)的公式=datedif(te

6、xt(mid(a1,7,6+(len(a1)=18)*2),#-00-00)*1,2006-10-31,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

7、,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)&/&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)&周歲datedif(a1,now(),y)根據(jù)出生年月推算生肖中國人有12生肖，屬什么可以推算出來。即用誕生年份除以12，再用除不盡的余數(shù)對照如下：0猴

8、，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)計算距離退休年齡的公式=if(e2=,if(e2=v2,已經(jīng)退休,距離退休還有&datedif(today(),date(year(u2)+(v2),month(u2),day(u2),y)&年&datedif(today(),date

9、(year(u2)+(v2),month(u2),day(u2),ym)&個月&datedif(today(),date(year(u2)+(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(),md) =datedif(b2,today

10、(),y)&年&datedif(b2,today(),ym)&月&datedif(b2,today(),md)&日 計算工齡=datedif(c6,c8,y)求兩日期間的年數(shù)=datedif(c6,c8,ym)求兩日期間除去整年數(shù)剩余的月數(shù)=datedif(c6,c8,m)求兩日期間的總月數(shù)如果只需要算出周年的話，可以用=datedif(1978-8,2006-5,y)年齡及工齡計算有出生年月如何求年齡？有工作時間如何求工齡？（求出的結(jié)果為多少年另幾個月，如：0303的形式，即3年零3個月）。a1是出生年月或工作時間：=datedif(a1,today(),y)=text(datedif(a1

11、,today(),y),00)&text(datedif(a1,today(),m),00)如 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月)顯示 40年09月如果你找不到 datedif 函數(shù)，也可以不用 datedif 函數(shù),如 b2=1964-9-1 則:=text(right(year(now()-b2),2

12、),00)&text(mod(month(now()-b2)-1,12),00) 顯示 4009=text(right(year(now()-b2),2)&年&mod(month(now()-b2)-1,12)&個月,) 顯示 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)！你不妨把“.”替換成“-”，不就行

13、了嗎，再說后者是日期的一種標準格式，=text(right(year(now()-substitute(a1,.,-),2)&年&mod(month(now()-substitute(a1,.,-)-1,12)&個月,)【時間和日期應(yīng)用】自動顯示當前日期公式=year(now() 當前年=month(now() 當前月=day(now() 當前日如何在單元格中自動填入當前日期ctrl+;如何判斷某日是否星期天=weekday(a2,2)=text(a1,aaaa)=mod(a1,7)1)顯示昨天的日期每天需要單元格內(nèi)顯示昨天的日期，但雙休日除外。例如，今天是7月3號的話，就顯示7月2號，如果是

14、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在年月日向后推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

15、,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),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)換為“20050

16、806”格式用公式：=text(a1,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)&-&right(a1,2) 顯示：2005-8-6將“20060501”轉(zhuǎn)換為“2

17、006-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),e-m)把198405轉(zhuǎn)換成1984.05 一、查找1984，替換1984.二、如果全部是年月的話，我個人建議， 1、采取輔助mid(xxxxxx,1,4) & . & right(xxxxxx,2) 2、選中這列，用數(shù)據(jù)中的分列

18、。然后三、單元格格式數(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,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“月”改為：yyyy“年”mm“月”

19、，即可。但這方法打印出來顯示為：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-6-12=text(a1,yyyy-mm-dd) 顯示：1968-06-12將1993-12-28的日期格式轉(zhuǎn)換成1993年12月=concatenate(year(a1),年,month(a1),月)

20、 =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ù)菜單中選分列，分列過程中“格式”選“日期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,yyy

21、y/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,元,一),text(row($14611:$55153),dbnum1yyyy年m月d日),0)該公式速度較慢。2、改進后的公式，速度要快的多：=date(1899+match(left(a7,4),text(row($1900:$2100),dbnum10000),0),month(match(substitute(mid(a7,6,7)

22、,元,一),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&日反之，要將c列的“8月18日” 直接分別到d、e列，顯示月份和日期，月數(shù)份=left(c5,find(月,c5)-1)日期數(shù)=mid(c5,find(月,c5)+1,find(日,c5)-find(月,c5)-1)也可分別用公式：=month(-c5)

23、=day(-c5)日期格式轉(zhuǎn)換問題輸入的日期是:04-07-26. 與另一格的001合并,合并出來是:040726001.=text(a1,yymmdd)&001要想自動取得“編制日期：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、求總體

24、方差。自動排序=subtotal(3,$b$2:b2)*1=if(a2a1,1,n(c1)+1)按奇偶數(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列自動

25、變成從大到小排列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ù)就行了. =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)容日

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

27、,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 a0001b b0001a a0002c c0001a a0003b b0002c c000

28、2公式為=a1&000&countif(a$1:a1,a1)向下拖=text(countif(a$1:a1,a1),!&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 趙一 總經(jīng)理2 趙二 副經(jīng)理

29、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)”后下拉復(fù)制至a列各行即可（“”不必輸入）根據(jù)規(guī)律的重復(fù)的姓名列產(chǎn)生自動序號姓名序號張三1張三1李四2李四2趙五3趙五3趙五3王六4王六4=(a1a2)+n(b1)=if(a3=a2,b2,b

30、2+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$9a1)/countif(a$1:a$9,a$1:a$9)+1自動排名公式=rank(c3,$c$3:$c$12)=rank(a2,$a$2:$a

31、$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)*(zfe2)+1年級名次：=rank(e2,zf) 公式下拖。排名次根據(jù)總分值大小，只將姓名排序后, 降序結(jié)果=index(a$2:a$6,rank(d2,d$2:d$6)根據(jù)總分值大小，只將

32、姓名排序后, 升序=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)=countif($k$3:$k$26,&k3)+1=index($a$2:$a$7,match(large($c$2:$c$7,row(a1),$c$2:

33、$c$7,0),1)=sumproduct($a$2:$a$12a2)/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ù)相同時，按順序進行不重復(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=sumproduct(1*($e$3:$e$12+row($e$3:$e$12)/10

34、0=($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)建方式排名=sumproduct(1*($e$3:$e$12=e3) 一般方式排名=rank(e3,$e$3:$e$22)+sum(if($e$3:$e$22e3,1/countif($e$3:$e$22,$e$3:$e$22),0)-countif($e$3:$e$22,&e3) 一般方式排名=ra

35、nk(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/10000)=(e3+b3/100+c3/10000) 不重復(fù)排名=rank($e3,$e$3:$e$22,1) 倒排序美國式排名=rank(k247,$k$247

36、:$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=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$21b3)*(1/countif($b$3:$b$21,$b$3:$b$21)+1 （升序）=sumproduct(b$3:b$21b2)+1=sum(if($b$3

37、:$b$21=b3,1/(countif($b$3:b$21,b$3:b$21)+1（升序）=sum(if($b$3:$b$21b2,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$21b2)*(match($b$2:b$21,b$2:b$21,)=row($1:$20)+1=sum(if($b$1:$h$1=e2)/countif($e$2:$e$21,$e$2:$e$21),第dbnum1g/通用格式名)排序后排名=sum(if($b$2:$b$

38、15=b2,1/countif($b$2:$b$15,$b$2:$b$15)=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$25b16)+1由

39、小到大排名=rank(b3,$b$3:$b$12,1)=sumproduct($a$16:$a$25=a16)*($b$16:$b$25b16-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$25)/10000b16+row(b16)/10000)+1由小到大=count($b$3:$b$12)-rank(b3,$b$3:$b$12)-countif($b$3:b3,b3)+2=sumproduct($a

40、$16:$a$25=a16)*($b$16:$b$25-row($b$16:$b$25)/10000b3)*(1/countif(b$3:b$12,b$3:b$12)+1=sumproduct($a$16:$a$25=a16)*($b$16:$b$25b16)/countif($k$16:$k$25,$k$16:$k$25)+1由小到大=sumproduct(b$3:b$12b3)*(1/countif(b$3:b$12,b$3:b$12)+1=sumproduct($a$16:$a$25=a16)*($b$16:$b$25b3)+1=countif($b$3:$b$21,&b3)+1=sum

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

42、=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$21c3)/countif($n$3:$n$21,$n$3:$n$21)+1（需輔助列）無并列分段排名=sumproduct($a$3:$a$

43、21=a3)*($c$3:$c$21-row($c$3:$c$21)/10000c3-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ù)學(xué)英語1楊增海1351361462郭愛玲1381371413華志鋒1341381414袁文飛134143135能否用一個公式直接找出所用考生中語文成績中第100名的成績是多少?=large(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%