在Excel中，一个函数不能解决的问题就用两个函数。老师在统计学生成绩时通常是用平均函数或者最值函数统计一个班一个年级的平均分或者最高分最低分。但是当我们在求平均值的同时又要满足多个条件，又应该用怎样的函数去达到我们的目的？

前段时间学校考试结束，正是老师们忙着分析成绩的时候。收到这样一个问题求助：一共9个班级，涉及到8门课程，要统计出每个班每门课的前五名平均分。数据源是这样的：

班主任本来打算手动处理的。首先筛选1班语文前5名同学的成绩，然后用鼠标选中，在表格左下角就能看到平均值，然后做好记录。

因为有8门课程，同样的动作一个班就要操作8次，9个班下来就是72次，费时又费力。

于是就想求得一个快捷准确的方法。

老师电脑中的Excel是2007版，没有办法使用PQ，实际上即便有PQ一般人也还真用不来。用数据透视表似乎也比较困难，思来想去还得用函数公式来解决。

如何用公式解决？下面和大家分享一个思路，涉及到两个比较有用的套路。

我们把上述问题分成两个步骤来实现。

第一步，求出每个班第五名的成绩是多少。

【方法】在L3单元格输入公式：=LARGE(IF($A$2:$A$375=$K3,B$2:B$375,0),5)

注意，这个公式需要按快捷键“Ctrl+shift+Enter”完成输入。

【解析】用到的两个函数分别是LARGE和IF。

①LARGE函数的功能是得到指定的第几名的最大值，格式为：LARGE（数据，第几名），这个函数的用法相对比较容易；

②问题的关键是数据的确定，必须是指定的班级和科目所对应的成绩。比如L3单元格中，要在数据源中的1班语文成绩中找第五名出来，这就用到了IF函数；

③在IF($A$2:$A$375=$K3,B$2:B$375,0)中，第一参数（也就是条件判断这里）用到了一组数“$A$2:$A$375”和一个值“$K3”做比较。 如果两者相等，即A列中的数据是1班的时候，得到B列中相对应的成绩。当A列的数据不是1班的时，结果等于0。

这个IF函数的结果是一组数据，在这一组数据中，取第五个最大值，就是上图中公式的结果。

得到每个班每门课第五名的成绩之后，下一步就容易了。

第二步，用多条件平均值函数来得到最终的结果。

【方法】在L15单元格输入公式：=AVERAGEIFS(B$2:B$375,$A$2:$A$375,$K15,B$2:B$375,">="&L3)

为了使结果美观一点，加一个ROUND函数强制显示两位小数：=ROUND(AVERAGEIFS(B$2:B$375,$A$2:$A$375,$K15,B$2:B$375,">"&L3),2)

最终结果如图所示：

【解析】如果你对SUMIFS函数熟悉的话，你就会明白它和AVERAGEIFS函数的用法几乎没有区别，两者一个求和，一个算平均值，但是格式都一样。

AVERAGEIFS（要计算平均值的数据区域，第一组条件所在的区域，第一组条件，第二组条件所在的区域，第二组条件）。在这个例子中，第一组条件是班级，第二组条件稍微有点特殊，是大于等于班级和课程所对应的第五名成绩，结果就是对前五名的成绩计算平均值。

到这里，问题已经解决了，值得思考的有两个问题：

第一个就是$的用法：上述解法中没有直接使用课程作为条件，这样公式得以简化。但是有个前提，数据源中的课程顺序必须和统计区域的课程顺序一致，才可以利用混合引用在公式右拉的时候，对应的数据区域跟着变化，实现了课程成绩的同步，这个需要新手多练习多思考。

第二个问题是：为什么不直接提取第六名的成绩呢？这样算平均分的时候可以直接用大于第六名而不是大于等于第五名。

针对这个问题，我们可以验证一下：

将第一步的公式改成=LARGE(IF($A$2:$A$375=$K3,B$2:B$375,0),6)，

第二步的公式改成=ROUND(AVERAGEIFS(B$2:B$375,$A$2:$A$375,$K15,B$2:B$375,">"&L3),2)。

结果会出现错误值：

为什么会这样呢？

这就得通过数据源来看了，我们手动筛选一下3班地理的成绩。

可以看到地理最高分就是70，一共有9个人都是70分，第六名的成绩也是70。

注意，这里的排名是不排除相同成绩的。如果设置成大于第六名就没有满足条件的数据了，所以平均值会得到错误值。

一模一样的问题可能你遇不到，但是同类型的问题就不一定了。还是那句话，从每个问题中去思考解决问题的思路，发现自己欠缺的知识点，找到新的学习目标，这样才能不断提高自己的能力。以上就是我们本期分享的全部内容啦，下期再见。

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

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

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

相关推荐：

Excel实用案例：SUBSTITUTE嵌套函数对文本单元格的判断和计算

Excel数字提取技巧:从包含文字的单元格中提取所有数字的万能公式

Excel数字提取技巧:从无规律文本中提取手机号的5种方法

Excel数字提取技巧:用简单公式从混合文本中提取数字的3种情景

版权申明：

本文作者郅龙；同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。