如何在excel中对学生成绩进行排名
- 2023-05-24 23:05:52
如何在excel中对学生成绩进行排名?
我用中国式排名为例来说明吧,我们先来了解一下什么叫中国式排名?我就举个最简单的例子。
比如,你们班级一共 50 个人,期末考试有 49 个人考了 100 分,你考了 90 分,那么按国际惯用的排名法则:49 个人并列第一,你第 50 名。如果按中国式排名:49 个人并列第一,你第 2 名。是不是瞬间和谐了许多?
既然中国式排名是中国特色,那么,Excel 现有的 rank 函数显然是按老美的规则来排名的,如果要统计中国式排名,就得用到些技巧。
本文教大家 4 种方法,由浅入深、一网打尽,总有一款适合你。
案例:
下表的高考总分,分别有两个分数出现重复,请用中国式排名给这些学生排名次。
解决方案:
用 if 排名
用 vlookup 排名
用 sumproduct(sum 家族的函数都可以,比如 sum+if,或者 sumif,本文以神级函数 sumproduct 举例)+ countif 排名
用数据透视表排名
方案1:用 if 排名
1. 开始中国式排名前,我们首先来看一下普通排名,可以用 rank 函数,也可以用如下 sumproduct 公式:
=SUMPRODUCT((C2<$C$2:$C$15)*1)+1
公式释义:
sumproduct 作为神级函数,我在各种案例中多次、反复讲解过,比较完整的可参见 Excel函数(四) – sumproduct函数计数、排名、求和等等
本公式中,C2<$C$2:$C$15:
用 C2,即当前行的总分与一整列的分数依次比较,判断 C2 是否比别人小
根据判断结果会得到一个由 true 和 false 组成的数组,true 相当于 1,false 相当于 0
用 F9 看一下计算结果(如下图),就非常直观容易理解
SUMPRODUCT((C2<$C$2:$C$15)*1):sumproduct 是积求和函数,因此会用数组中的 1 和 0 依次与 1 相乘,最后求和,也就是说,有几个总分比自己高的,就得出几
+1:如果有 5 个比自己高,那么自己排名第 6,所以要 +1
2. 现在开始中国式排名步骤,先按 C 列的高考分数由高到低排序
3. 在 E2 列输入“1”,在 E3 列输入以下公式,下拉即可:
=IF(C3=C2,E2,E2+1)
公式释义:
如果上下两行分数相等,则排名相同
如果分数不等,那么不管上一行的分数有没有重复值,排名 +1
方案2:用 vlookup 排名
1. 按 C 列的高考分数由高到低排序
2. 将 C 列复制粘贴到旁边的辅助列,比如 J -- 选中 J 列 -- 选择菜单栏的 Data -- Remove Duplicates -- 在弹出的对话框中勾选“高考分数”-- OK
3. 现在 J 列是去除重复项的分数
4. 在 K 列用 rank 函数对 J 列排名,公式如下:
=RANK(J2,$J$2:$J$12)
5. 在 F2 输入以下公式,下拉即可,目的是用 vlookup 函数去查找 C 列的分数所对应的 K 列去重后的排名,即中国式排名:
=vlookup(C2,J:K,2,0)
方案3:用 sumproduct + countif 排名
1. 在 E2 单元格输入以下公式,下拉即可:
=SUMPRODUCT((C2<$C$2:$C$15)*(1/COUNTIF($C$2:$C$15,$C$2:$C$15)))+1
公式释义:
* 前面部分之前解释过了,不赘述
重点来看这一段 COUNTIF($C$2:$C$15,$C$2:$C$15):Countif 统计数组中每个数的重复次数,即先用 C2 历遍整个数组,得到重复次数;再用 C3 历遍整个数组,得到重复次数……依次类推
1/COUNTIF($C$2:$C$15,$C$2:$C$15):
用 1 除以数组中每个数的重复次数,等到一组最大值为 1 的数组
当总分重复 n 次时,通过 1/n,把重复次数拆分成了 n 等分
下图是用 F9 查看这段公式后显示的值,便于大家理解
SUMPRODUCT((C2<$C$2:$C$15)*(1/COUNTIF($C$2:$C$15,$C$2:$C$15))):
就是用一组 1 和 0 组成的数组,与一组 1 和小数组成的数组积求和
0 * 任何数为 0,忽略不计;1 * 1 也好理解
当第二个值为分数时,比如1/3(上图中显示为0.33333333……),表示重复了 3 次,而且 1/3 会在数组中出现三次,sumproduct 对它积求和即1*1/3+1*1/3+1*1/3=1,也就是不管重复几次,最后结果都只统计 1 次
因此实现了中国式排名的逻辑
+1:比自己大的个数 +1,即自己的排名
方案4:用数据透视表排名
1. 制作数据透视表:点击表格中的任意单元格 -- 选择菜单栏的 Insert -- PivotTable
2. 默认将数据透视表放入一个新 sheet -- 在右边的对话框中将“姓名”拖动到 Rows 区域 -- 将“高考总分”拖动到 Values 区域,拖动两次:一列用来显示总分,另一列用来显示排名
3. 回到数据透视表,随意选中第 2 个“高考总分”列的任意单元格 -- 右键单击 -- 选择 Show Values As -- Rank Largest to Smallest
4. 在弹出的对话框中点击 OK
5. 现在 C 列已经变成了中国式排名了
6. 我们再对 C 列排下序:选中 C 列的任意单元格 -- 右键单击 -- 选择 Sort -- Sort Largest to Smallest
7. 然后把 C 列的标题改成“排名”,就完成了
声明:本文内容及图片来源于读者投稿,本网站无法甄别是否为投稿用户创作以及文章的准确性,本站尊重并保护知识产权,根据《信息网络传播权保护条例》,如果我们转载的作品侵犯了您的权利,请在一个月内通知我们,我们会及时删除。请将本侵权页面网址发送邮件到583666585@qq.com,我们会及时做删除处理。