使用Rank和SUMPRODUCT、COUNTIF函数排名成绩

使用 Rank 和 sumproduct、countif 函数

对考试成绩排名的方法示例

 基本语法：RANK(number,ref,order)

使用的函数句型：

 =IF(ISNUMBER(B2),RANK(B2,$B$2:$J$2,0),"缺考")                                                                

  =IF(ISNUMBER(B2),RANK(B2,$B$2:$J$2),"")                                    =RANK(B2,$B$2:$J$2)

     =IF(ISNUMBER(B2),RANK(B2,$B$2:$J$2,0),"")                                 =RANK(B2,$B$2:$J$2,0)

     =IF(ISNUMBER(B2),RANK(B2,$B$2:$J$2,1),"")                                 =RANK(B2,$B$2:$J$2,1)

 选择不连续的数据，按给定条件，进行排名次：

=IF(N3="语",RANK(O3,($O$3,$O$5,$O$7,$O$9,$O$11),0),"")

=IF(N3="数",RANK(O3,($O$4,$O$6,$O$8,$O$10,$O$12),0),"")    

=IF(N3="语",RANK(O3,($O$3,$O$5,$O$7,$O$9,$O$11),0),IF(N3="数",RANK(O3,($O$4,$O$6,$O$8,$O$10,$O$12),0),""))

=IF(B14="语",RANK(B15,($B$15,$D$15,$F$15,$H$15,$J$15),0),IF(B14="数",RANK(B15,($C$15,$E$15,$G$15,$I$15,$K$15),0),""))

=IF(B14="语",RANK(B15,($B$15,$D$15,$F$15,$H$15,$J$15),0),"")    

=IF(B14="数",RANK(B15,($C$15,$E$15,$G$15,$I$15,$K$15),0),"") 

 按指定的数据范围和分类，进行排名次：

 =SUMPRODUCT((年级=$B3)*($E3<语文))+1 （或  =COUNTIF($E$3:$E$22,">"&E3)+1）

 =SUMPRODUCT((年级=$B3)*($F3<数学))+1 （或  =COUNTIF($F$3:$E$22,">"&F3)+1）

  =SUMPRODUCT((年级=$B3)*($G3<英语))+1 （或  =COUNTIF($G$3:$G$22,">"&G3)+1）

  =SUMPRODUCT((年级=$B3)*($H3<总分))+1 （或  =COUNTIF($H$3:$H$22,">"&H3)+1）

=SUMPRODUCT((班级=$C3)*($E3<语文))+1

=SUMPRODUCT((班级=$C3)*($F3<数学))+1

=SUMPRODUCT((班级=$C3)*($G3<英语))+1

=SUMPRODUCT((班级=$C3)*($H3<总分))+1