文档介绍：excel排名公式大全
excel排名公式大全
排名的函数
用排名函数来对成绩进行排名，用起来非常地方便。
=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))
A列是成绩,B列是排名
=SU,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),)｝
根据双列成绩进行共同排名
=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)
=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$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:$B$25-ROW($B$16:$B$25)/10000<B16-ROW(B16)/10000))+1
顺次排名
由大到小
=SU