excel排名公式大全

excel排名公式大全

排名的函数

用排名函数来对成绩进行排名，用起来非常地方便。

=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))

A列是成绩,B列是排名

=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(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)

求名次排名

统计成绩时遇到一个分别求班级和年级总分名次排名的问题，不晓得应该运用什么公式来实现。

班级名次：

=SUMPRODUCT((BJ=A2)*(ZF>E2))+1

年级名次：

=RANK(E2,ZF) 公式下拖。

排名次

根据总分值大小，只将姓名排序后, 降序结果

=INDEX(A$2:A$6,RANK(D2,D$2:D$6))

根据总分值大小，只将姓名排序后, 升序

=INDEX(A$2:A$6,RANK(D2,D$2:D$6,1))

根据分数进行普通排名

=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:$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))

对于普通排名分数相同时，按顺序进行不重复排名

=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)/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)))

依分数比高低名次成绩排名

=RANK($E3,$E$3:$E$22) 內建方式排名

=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)/1 00))))

=SUMPRODUCT(1*(($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>=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

｛=SUM(--(IF(FREQUENCY(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+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$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)*(MATCH($B$2:B$21,B$2:B$21,)=ROW($1:$2 0)))+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（如果数据列中数值有相同）

=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("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$76 8,E2)))

数组公式

｛=INDEX(A:A,MOD(LARGE(E$2:E$21*100+ROW($2:$21),ROW(1:1)),10 0))｝

｛=OFFSET($A$1,RIGHT(LARGE($E$2:$E$21*1000+ROW($E$2:$E$21),R OW()-25),3)-1,,)｝