excel学习库-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对比结果相同。下面是动图演示：对于这两种方法，小编还是倾向于推荐第二种，第一种虽然只有一个公式，但是如果理解不透彻，运用的时候不好编，而第二种方法理解了以后用的时候很容易就操作了。

一	二	三	四	五	六	日
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

excel学习库

excel表格_excel函数公式大全_execl从入门到精通

excel中中国式排名的两种方法2024-01-31 21:50:30