那个985研究生上班第一天就通宵加班!求平均值就这么难?
★
编按
★
Hello小伙伴们,大家对于AVERAGEIFS函数应该不陌生,它是根据指定的多个条件计算平均数的函数,也是我们今天的干货教程的主角。但是今天的教程不止介绍一个函数那么简单,我们的郅龙老师更想跟大家分享一下我们在面对各种Excel问题时的思维方式,一起去学习一下吧~
前段时间学校考试结束,正是老师们忙着分析成绩的时候。收到这样一个问题求助:一共9个班级,涉及到8门课程,要统计出每个班每门课的前五名平均分。数据源是这样的:
班主任本来打算手动处理的。首先筛选1班语文前5名同学的成绩,然后用鼠标选中,在表格左下角就能看到平均值,然后做好记录。
因为有8门课程,同样的动作一个班就要操作8次,9个班下来就是72次,费时又费力。
于是就想求得一个快捷准确的方法。
老师电脑中的Excel是2007版,没有办法使用PQ,实际上即便有PQ一般人也还真用不来。用数据透视表似乎也比较困难,思来想去还得用函数公式来解决。
↑扫码下载教程配套练习文件↑
如何用公式解决?下面和大家分享一个思路,涉及到两个比较有用的套路。
我们把上述问题分成两个步骤来实现。
1.求出每个班第五名的成绩是多少。
【方法】在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函数的结果是一组数据,在这一组数据中,取第五个最大值,就是上图中公式的结果。
得到每个班每门课第五名的成绩之后,下一步就容易了。
2.用多条件平均值函数来得到最终的结果。
【方法】在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。
注意,这里的排名是不排除相同成绩的。如果设置成大于第六名就没有满足条件的数据了,所以平均值会得到错误值。
一模一样的问题可能你遇不到,但是同类型的问题就不一定了。还是那句话,从每个问题中去思考解决问题的思路,发现自己欠缺的知识点,找到新的学习目标,这样才能不断提高自己的能力。以上就是我们本期分享的全部内容啦,下期再见。