打开电脑
上的Ex cel表 格,点击 选择要排 序的列, 点击排序 功能,即 可把成绩 排序。点 击选择列 。打开E xcel 表格,选 择要排序 的列。打 开Exc el表格 ,选择要 排序的列 。点击排 序。点击 右上角的 排序功能 。点击右 上角的排 序功能。 点击查看 。排序完 成,这样 成绩排序 就好了。 排序完成 ,这样成 绩排序就 好了。小编还为您整理了以下内容,可能对您也有帮助:
如何在excel中对学生成绩进行排名?
我用中国式排名为例来说明吧,我们先来了解一下什么叫中国式排名?我就举个最简单的例子。
比如,你们班级一共 50 个人,期末考试有 49 个人考了 100 分,你考了 90 分,那么按国际惯用的排名法则:49 个人并列第一,你第 50 名。如果按中国式排名:49 个人并列第一,你第 2 名。是不是瞬间和谐了许多?
既然中国式排名是中国特色,那么,Excel 现有的 rank 函数显然是按老美的规则来排名的,如果要统计中国式排名,就得用到些技巧。
本文教大家 4 种方法,由浅入深、一网打尽,总有一款适合你。
案例:
下表的高考总分,分别有两个分数出现重复,请用中国式排名给这些学生排名次。
解决方案:
用 if 排名
用 vlookup 排名
用 sumproct(sum 家族的函数都可以,比如 sum+if,或者 sumif,本文以神级函数 sumproct 举例)+ countif 排名
用数据透视表排名
方案1:用 if 排名
1. 开始中国式排名前,我们首先来看一下普通排名,可以用 rank 函数,也可以用如下 sumproct 公式:
=SUMPRODUCT((C2$C$2:$C$15)*1)+1
公式释义:
sumproct 作为神级函数,我在各种案例中多次、反复讲解过,比较完整的可参见 Excel函数(四) – sumproct函数计数、排名、求和等等
本公式中,C2$C$2:$C$15:
用 C2,即当前行的总分与一整列的分数依次比较,判断 C2 是否比别人小
根据判断结果会得到一个由 true 和 false 组成的数组,true 相当于 1,false 相当于 0
用 F9 看一下计算结果(如下图),就非常直观容易理解
SUMPRODUCT((C2$C$2:$C$15)*1):sumproct 是积求和函数,因此会用数组中的 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:用 sumproct + 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 会在数组中出现三次,sumproct 对它积求和即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 列的标题改成“排名”,就完成了
如何在excel中对学生成绩进行排名?
我用中国式排名为例来说明吧,我们先来了解一下什么叫中国式排名?我就举个最简单的例子。
比如,你们班级一共 50 个人,期末考试有 49 个人考了 100 分,你考了 90 分,那么按国际惯用的排名法则:49 个人并列第一,你第 50 名。如果按中国式排名:49 个人并列第一,你第 2 名。是不是瞬间和谐了许多?
既然中国式排名是中国特色,那么,Excel 现有的 rank 函数显然是按老美的规则来排名的,如果要统计中国式排名,就得用到些技巧。
本文教大家 4 种方法,由浅入深、一网打尽,总有一款适合你。
案例:
下表的高考总分,分别有两个分数出现重复,请用中国式排名给这些学生排名次。
解决方案:
用 if 排名
用 vlookup 排名
用 sumproct(sum 家族的函数都可以,比如 sum+if,或者 sumif,本文以神级函数 sumproct 举例)+ countif 排名
用数据透视表排名
方案1:用 if 排名
1. 开始中国式排名前,我们首先来看一下普通排名,可以用 rank 函数,也可以用如下 sumproct 公式:
=SUMPRODUCT((C2$C$2:$C$15)*1)+1
公式释义:
sumproct 作为神级函数,我在各种案例中多次、反复讲解过,比较完整的可参见 Excel函数(四) – sumproct函数计数、排名、求和等等
本公式中,C2$C$2:$C$15:
用 C2,即当前行的总分与一整列的分数依次比较,判断 C2 是否比别人小
根据判断结果会得到一个由 true 和 false 组成的数组,true 相当于 1,false 相当于 0
用 F9 看一下计算结果(如下图),就非常直观容易理解
SUMPRODUCT((C2$C$2:$C$15)*1):sumproct 是积求和函数,因此会用数组中的 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:用 sumproct + 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 会在数组中出现三次,sumproct 对它积求和即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 列的标题改成“排名”,就完成了
声明:本网页内容旨在传播知识,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。TEL:177 7030 7066 E-MAIL:11247931@qq.com