怎样在excel中用函数给成绩排名

对于中国人，给学生成绩排名与西方的排名方式略有不同，主要体现在并列名次上，中国式排名如果有并列名次，比如有五个人成绩分别为94，90，90，89，83。那么中国式排名是1，2，2，3，4。而excel中的rank函数排的名次是1，2，2，4，5。此处就给大家介绍两种中国式排名的方法，供各位参考。

一、利用sumproduct函数排名。

如下图所示，以销量为例进行排名。rank函数是常规的排名方法。而sumproduct函数稍显复杂。sumproduct函数公式为SUMPRODUCT(($C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))+1。

对于这个公式，以E2单元格计算结果为例：

COUNTIF($C$2:$C$21,$C$2:$C$21)表示条件计数，运用这个函数计算的结果是1;1;2;1;1;2;1;1;1;1;1;1;1;1;2;1;2;1;1;1，可以看到如果有重复值，那么结果就是2，没有重复值结果为1。

1/COUNTIF($C$2:$C$21,$C$2:$C$21）返回结果为1;1;0.5;1;1;0.5;1;1;1;1;1;1;1;1;0.5;1;0.5;1;1;1，相当于把countif计算的结果加权平均了，这个作用就是每个重复出现的数只统计依次，比如第7名出现了两次，用这个公式返回结果是2个0.5，加起来相当于只统计了1次。

$C$2:$C$21>C2单独拿出来是一个数组函数，每一个单元格引用的内容不同，根据判断返回的逻辑值也不同，在E2单元格返回的逻辑值是{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE}，大于188的返回结果为true，小于或者等于188的返回结果为false。

（$C$2:$C$21>C2)/COUNTIF($C$2:$C$21,$C$2:$C$21)这个公式计算出来的结果是0;1;0.5;1;1;0.5;1;1;1;1;0;0;1;0;0.5;0;0.5;0;0;1，用sumproduct函数对上面返回结果求和，E2单元格就会计算出来11，最后+1对结果修正。

二、利用vlookup函数和辅助列排名。

这个方法操作虽然多一些步骤，但是很容易理解。

1.复制C列到空白区域，比如I列。选中I列的数据后打开数据选项卡—删除重复值。

2.对于I列删除重复值后的数据运用rank函数进行排名，函数公式为=RANK.EQ(I2,$I$2:$I$19)，向下拖动填充。

3.在D列数据运用vlookup函数进行查找，公式为=VLOOKUP(C2,$I$2:$J$19,2,0)，然后向下拖动即可在D列中完成中国式排名。和上面的sumproduct对比结果相同。

下面是动图演示：