二维码 购物车
部落窝在线教育欢迎您!

老师如何有效学习Excel?一文概括成绩单中的函数问题!

 

作者:E图表述来源:部落窝教育发布时间:2022-04-13 17:27:27点击:1297

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

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

 

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

数据源很常见。

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

 

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 按照JK列做一个图表



选中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,再用SUMOK了。

 

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)),原理同上。

 

 

本文配套的练习课件请加入QQ群:902294808下载。

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

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

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

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

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

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

版权申明:

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