登陆注册
6593

Excel函数学习总结:成绩单处理的相关函数概括

站长网2023-07-29 16:40:200

编按:今天的文章是一篇概述性的内容,针对一张成绩表,讲解一系列的函数操作。虽然都不难,但是我们可以从中学到许多思路,希望大家能从中“温故知新”吧!


(模拟数据源,这班学生偏科的厉害)

数据源很常见。

需求也很“简单且朴实”——要做全套的数据统计。

STEP 1 排名

在G2单元格输入函数=RANK(F2,$F$2:$F$13),下拉填充至G13单元格。

实际上RANK函数是有三个参数,第三个参数是“升序排序、降序排序”

如果我们不填写,则默认为0降序排序,需要的话,也可以在函数中输入1进行升序排序。

STEP 2 中国式排名

上面的排名明显有一个问题,有两个第一名,却没有第二名,所以我们要调整同分数不占名次的问题。

在G2单元格输入函数:

{=SUM(IF($F$2:$F$13>F2,1/COUNTIF($F$2:$F$13,$F$2:$F$13))) 1},下拉填充至G13单元格。

这是全篇最不好理解的一个函数,使用数组函数,判断数据列表中有多少是大于F2单元格值的,如果大于则用1/COUNTIF的技巧,标记序号,如果有重复的,则如下图标记为0.5,最后再用SUM将这些数值相加既得F2数值的排序。

STEP 3 不重复排序

中国式排名的内涵,就是如果有同分情况,则比例第N名,下一个分数则为N 1名次。但是这样的话,还是会有重复排名,那我们再来一个“不重复排序”的函数吧。

在单元格H2输入函数=RANK(F2,$F$2:$F$13) COUNTIF($F$2:F2,F2)-1,下拉填充至H13单元格。

STEP 4 动态按总分排列成绩单

STEP3的作用就是这样了,可以让我们动态的按照H列的排序引出排序明细。这里用了经典的VLOOKUP函数的反向查询。

在J2单元格输入函数=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$B$2:$B$13),2,0)

在K2单元格输入函数=VLOOKUP(ROW(J1),IF({1,0},$H$2:$H$13,$F$2:$F$13),2,0)

STEP 5 按照J、K列做一个图表

选中J1:K13单元格区域,按ALT F1快捷键,就可以快速的创建一个总分排名的图表,再加上数据标签,齐活儿!

STEP 6 各科平均分

很直白的函数,在单元格C15输入函数=AVERAGE(C2:C13),右拉填充至F15单元格即可。

STEP 7 及格率和优秀率

假定及格线是60及以上,优秀率是85及以上。

在单元格C16输入函数{=SUM((C2:C13>=60)*1)/COUNT(C2:C13)},右拉填充至E16单元格

在单元格C17输入函数{=SUM((C2:C13>=85)*1)/COUNT(C2:C13)},右拉填充至E17单元格

这里我们利用逻辑值求和技巧,再除以总体样本数。纯粹的TRUE或者FALSE是不能被SUM求和的,但是可以进行四则运算,所以用了“*1”之后就可以转化成1或者0,再用SUM就OK了。

STEP 8 前N名、后N名

在单元格C19输入函数=LARGE(C$2:C$13,ROW(C1)),利用ROW函数得到第N大的序号,配合相对饮用固定行号,下拉再右拉填充至E21单元格,就得到了相对于各科的前三甲。

与LARGE相对应的函数是SMALL函数,引用数据列表中的第N小的值,在C22单元格输入函数=SMALL(C$2:C$13,ROW(C1)),原理同上。


做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

学习交流请加微信:hclhclsc进微信学习群。

相关推荐:

如何计算两个日期间的工作日天数?超实用的5类日期函数来了!

超级经典的8个函数组合,解决70%工作中的函数难题

4种删除excel重复值的小妙招,速收藏

Power Query的数据替换技巧比Excel函数更万能!

版权申明:

本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。

0000
评论列表
共(0)条