编按:今天的文章是一篇概述性的内容,针对一张成绩表,讲解一系列的函数操作。虽然都不难,但是我们可以从中学到许多思路,希望大家能从中“温故知新”吧! (模拟数据源,这班学生偏科的厉害) 数据源很常见。 需求也很“简单且朴实”——要做全套的数据统计。 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进微信学习群。 相关推荐: Power Query的数据替换技巧比Excel函数更万能! 版权申明: 本文作者E图表述;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。 |
|
来自: 部落窝教育BLW > 《部落窝excel/VBA》