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图表述；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。