Excel函数学习总结:成绩单处理的相关函数概括
编按:今天的文章是一篇概述性的内容,针对一张成绩表,讲解一系列的函数操作。虽然都不难,但是我们可以从中学到许多思路,希望大家能从中“温故知新”吧!
(模拟数据源,这班学生偏科的厉害)
数据源很常见。
需求也很“简单且朴实”——要做全套的数据统计。
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图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。
注意了!手机上这5类应用请尽快卸载,从此告别广告的困扰
相信很多朋友在使用手机的时候啊,经常会发现只要一打开手机,屏幕上就会自动弹出各种各样的广告,而且无论怎么关都关不掉,删也删不掉,非常烦人,不仅会拖慢手机速度,而且使用起来也非常不方便,体验非常不好,其实啊会出现这样的情况,很多时候都是由于我们下载安装了一些恶意软件造成的,那今天小俊就给大家分享一下具体的哪些软件应用引起的,不知道的朋友可以点赞收藏加关注,方便以后需要的时候,可以快速找到!站长网2023-07-29 17:30:560000令人心跳加速的4个网站!
hello大家好,这里是正常上班开始日更的老Y工作室。五一小长假就这样悄无声息的结束啦,大家也是时候收收心,开始为生计奔波搬砖啦,忘掉外面的人山人海,可以专注工作和摸鱼啦。宅家里几天的老Y依然给大家带来一些好玩的网站,朋友们也可以给老Y留言,聊聊旅行中的囧事01、在线免费白板访问地址:/站长网2023-07-27 09:59:070000真的是太齐全了!没有比这更专业更贴心的整理了!原来那些懂电脑的高
真的是太齐全了!没有比这更专业更贴心的整理了!原来那些懂电脑的高手,是把键盘上的功能全搞清楚了,让人一看就明白,尤其是经常使用电脑,学生,办公室白领的人,尽早把这些快捷键背得溜溜熟,你将成为电脑高手,领导会对你刮目相看。站长网2023-07-30 13:46:190000电脑密码忘了怎么解除?分享4个好方法!
我的电脑设置了一个密码,但刚刚想开电脑的时候怎么也想不到那个密码了!电脑密码忘了怎么解除?有什么好的方法吗?为电脑设置一个密码可以更好地保护我们的电脑数据。但有时候我们在输入密码时可能会忘记密码,这将导致我们无法成功开启电脑。电脑密码忘了怎么解除?今天小编分享几个实用的方法。方法一:使用密码提示使用密码开机时,忘了开机密码如何打开电脑?可以尝试使用密码提示。具体操作如下:站长网2023-07-30 08:50:120000【知识技术】常见的滤镜有哪些?
常见的滤镜包括:色彩滤镜:用于增强或改变照片的色彩。例如,红色滤镜可以增强红色色调,绿色滤镜可以增强绿色色调,蓝色滤镜可以增强蓝色色调。对比度滤镜:用于增强或减少照片的对比度。例如,高对比度滤镜可以增强照片中的黑白对比度,低对比度滤镜可以减少照片中的黑白对比度。模糊滤镜:用于使照片变得模糊或柔和。例如,高斯模糊滤镜可以使照片变得模糊,使焦点更加突出。0000