排名excel排名公式大全

排名的函数

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

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

A列是成绩,B列是排名

=SUMPRODUCT((A$1:A$9>A 1)/COUNTIF(A$1:A$9,A$1:A$9))+1自动排名公式

=RA NK(C3,$C$3:$C$12)

=RA NK(A2,$A$2:$A$11,0)

=RA NK(C2,$C$2:$C$65)+C O UN TIF($C$2:C2,C2)-1

百分比排名的公式写法为

=PERCEN TRA NK($C$3:$C$12,C3)

平均分及总分排名

=A VERA GE(B2:E2)

=RA NK(F2,$F$2:$F$65536)

求名次排名

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

班级名次

=SUMPRODUC T((BJ=A2)*(ZF>E2))+1

年级名次

=RA NK(E2,ZF) 公式下拖。

排名次

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

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

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

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

根据分数进行普通排名

=RA NK(A2,$A$2:$A$12)

=RA NK(A2,A$2:A$12)+C O UN TIF(A$2:A2,A2)-1

=SUMPRODUCT(1*($E$3:$E$12>=E3)) 

=RA NK(K3,$K$3:$K$26)

=RA NK(A 2,A$2:A$12)

=S UM((A$2:A$12>=A2)/CO UN TIF(A$2:A$12,A$2:A$12))

=C O UN TIF($K$3:$K$26,>&K3)+1

=INDEX($A$2:$A$7,MA TCH(LARGE($C$2:$C$7,ROW(A 1)),$C$2:$C$7,0), 1)

=SUMPRODUC T(($A$2:$A$12>A2)/COUNTIF($A$2:$A$12,$A$2:$A$12&\))+1

=RA NK(D2,OFFSE T($A$1,MA TCH($A2,$A:$A,0)-

1,3,CO UN TIF($A:$A,$A2), 1))

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

=RA NK(K32,$K$32:$K$55)+C O UN TIF($K$32:$K32,K32)-1=C O UN TIF($K$32:K32,K32)-1+C O UN TIF($K$3:$K$26,>&K32)+1=SUMPRODUCT(1*(($E$3:$E$12+RO W($E$3:$E$12)/100>=($E3+RO W(E3)/100))))

=RANK(E3,$E$3:$E$12)+COUN TIF($E$3:E3,E3)-1

=SUMPRODUCT(1*(($E$3:$E$12+

$B$3:$B$12/100)>=(E3+B3/100)))

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

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

=SUMPRODUCT(1*($E$3:$E$12>=E3)) 一般方式排名

=RA NK(E3,$E$3:$E$22)+S UM(IF($E$3:$E$22>E3, 1/COUN TIF($E$3:$E$22,$E$3:$E$22),0))-COUN TIF($E$3:$E$22,>&E3) 一般方式排名

=RA NK(E3,$E$3:$E$12)+CO UN TIF($E$3:E3,E3)-1不重复排名=SUMPRODUCT(1*(($E$3:$E$12+RO W($E$3:$E$12)/100>=($E3+ROW(E3)/100))))

=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)))不重复排名=RA NK($E3,$E$3:$E$22, 1)倒排序

美国式排名

=RA NK(K247,$K$247:$K$270)

=RA NK(B 1,$B 1:$H 1)

中国式排名

=RA NK(B2,$B$2:$B$21,0)

=RANK(B1,$B 1:$H1)+COUNTIF($B$1:B 1,B 1)-1

=SUM(IF($A$1:$E$1>=A 1, 1/COUN TIF($A$1:$E$1,$A$1:$E$1), \))

=SUMPRODUC T(($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21)) 

=SUMPRODUC T((B$3:B$21>B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1 升序

=SUMPRODUC T((B$3:B$21<B3)*(1/COUNTIF($B$3:$B$21,$B$3:$B$21)))+1 降序

=SUM(--

(IF(FREQ UENC Y(B$2:B$21,B$2:B$21),B$2:B$21>B2)))+1 =SUM(IF($B$3:$B$21<=B3, \, 1/

(C O UN TIF($B$3:B$21,B$3:B$21))))+1 升序

=SUM(IF($B$3:$B$21<=B3, 1/

(CO UN TIF($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>=A 1, 1/COUN TIF($A$1:$E$1,$A$1:$E$1), \))

=SUM(($B$2:$B$21>B2)*(MA TCH($B$2:B$21,B$2:B$21,)=ROW($1:$20)))+1

=SUM(IF($B$1:$H$1<=B 1, \, 1/

(CO UN TIF($B$1:$H$1,$B$1:$H$1))))+1

求最精简的自动排名公式

=RA NK(E2,$E$2:$E$21)

=RA NK(A2,$A$2:$A$9,0)

=RA NK(A2,$A$2:$A$10)+CO UN TIF($A$2:$A2,$A2)-1 如果数据列中数值有相同

=RANK(F10,$F10:$Q 10)+COUNTIF($F10:F10,F10)-1

=INDEX(A:A, 1/MOD(LARGE(E$2:E$21+1/ROW($2:$21),ROW(1:1)), 1))

=L OOKUP(1,0/

(($F$2:$F$21=A27)*(CO UN TIF(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))

=RA NK(C2,OFFSE T($C$1,MA TCH(E2,$E$2:$E$768,), ,CO UN TIF($E$2:$E$768,E2)))

数组公式

=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),)

吽塅?啓偍佒啄呃??㈤?????尩?問呎?????㈤??㈤????尬第

[DBNUM1]G/通用格式名)

排序后排名

=SUM(IF($B$2:$B$15>=B2, 1/COUNTIF($B$2:$B$15,$B$2:$B$1

5)))

=SUMPRODUCT((B$2:B$15>=B2)/COUNTIF(B$2:B$15,B$2:B$15))

位次排名

=IF($B2:$O2>=0,RA NK($B2:$O2,$B2:$O2,0),)

根据双列成绩进行共同排名

=RA NK(C345,($C$345:$C$356,$H$345:$H$356))

在双列间排名

=RA NK(B2,($B$2:$B$26,$E$2:$E$16))

等次排名

由大到小排名

=RA NK(B 3,$B$3:$B$12)

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25>B16))+1由小到大排名

=RA NK(B 3,$B$3:$B$12, 1)

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25<B16))+1不等次排名行小排先 (由大到小

=RA NK(B3,$B$3:$B$12)+COUN TIF($B$3:B3,B3)-1

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25-

ROW($B$16:$B$25)/10000>B16-ROW(B16)/10000))+1

由小到大

=RA NK(B3,$B$3:$B$12, 1)+C O UN TIF($B$3:B3,B 3)-1

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$1

6:$B$25)/10000<B16+ROW(B16)/10000))+1

不等次排名行大排先 (由大到小

=CO UN T($B$3:$B$12)-RA NK(B3,$B$3:$B$12, 1)-

CO UN TIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A16)*($B$16:$B$25+ROW($B$1

6:$B$25)/10000>B16+ROW(B16)/10000))+1

由小到大

=C O UN T($B$3:$B$12)-RA NK(B 3,$B$3:$B$12)-

CO UN TIF($B$3:B3,B3)+2

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25-

ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1

顺次排名

由大到小

=SUMPRODUC T((B$3:B$12>B3)*(1/COUNTIF(B$3:B$12,B$3:B$1

2)))+1

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25>B16)/COUNTI F($K$16:$K$25,$K$16:$K$25))+1

由小到大

=SUMPRODUC T((B$3:B$12<B3)*(1/COUNTIF(B$3:B$12,B$3:B$1

2)))+1

=SUMPRODUCT(($A$16:$A$25=A 16)*($B$16:$B$25<B16)/COUNTI F($K$16:$K$25,$K$16:$K$25))+1