【文章內(nèi)容簡介】 (A$2:A$12,A$2:A$12))=COUNTIF($K$3:$K$26,amp。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$12A2)/COUNTIF($A$2:$A$12,$A$2:$A$12amp。))+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,amp。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)建方式排名=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,amp。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/10000)=(E3+B3/100+C3/10000))) 不重復(fù)排名=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=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$21B3)*(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$21B2)))+1｝｛=SUM(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$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=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($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(Aamp。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)),100))｝｛=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)),第[DBNUM1]G/通用格式名)排序后排名｛=SUM(IF($B$2:$B$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由小到大排名=RANK(B3,$B$3:$B$12,1)=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25B16))+1不等次排名(行小排先）由大到小=RANK(B3,$B$3:$B$12)+COUNTIF($B$3:B3,B3)1=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25ROW($B$16:$B$25)/10000B16ROW(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,1)COUNTIF($B$3:B3,B3)+2=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$16:$A$25=A16)*($B$16:$B$25ROW($B$16:$B$25)/10000B16ROW(B16)/10000))+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$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$25B16)/COUNTIF($K$16:$K$25,$K$16:$K$25))+1有并列排名=RANK(B2,$B$2:$B$20)=SUMPRODUCT(1*($B$3:$B$21B3))+1=COUNTIF($B$3:$B$21,amp。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。COUNTIF($A$3:$A$21,A3)))),0)｝｛=MATCH(C3,LARGE(IF($A$3:$A$21=A3,$C$3:$C$21),ROW(INDIRECT(1:amp。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$21=A3)*($C$3:$C$21ROW($C$3:$C$21)/10000C3ROW(C3)/10000))+1｝=19FREQUENCY(($A$3:$A$21=A3)*($C$3:$C$21ROW($C$3:$C$21)/1000),C3ROW()/1000)+1成績排名序號姓名語文數(shù)學(xué)英語1楊增海1351361462郭愛玲1381371413華志鋒1341381414袁文飛134143135能否用一個公式直接找出所用考生中語文成績中第100名的成績是多少?=LARGE(C2:C417,100)=PERCENTILE(C2:C417,(416100)/416)=PERCENTILE($C$2:$C$417,(COUNTA($C$2:$C$417)100)/COUNTA($C$2:$C$417))能否用一個公式直接找出所用考生中語文成績中按與考人數(shù)的35%切線中位于第35%的成績是多少?升冪=SMALL(C2:C417,416*)=PERCENTILE($C$2:$C$417,) 降冪=LARGE(C2:C417,416*)=PERCENTILE($C$2:$C$417,)如何排名對英語進行排名，缺考不計算在內(nèi)。對英語進行排名，缺考計算在內(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=,RANK(A2,$A$2:$A$11,0))如果隔幾行排名，如下表，第五行、第九行和第十二行不參與排名。單位數(shù)據(jù)排名A18A57A66小計12B84B93B75小計24C181C112小計29=IF(A2