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

Excel教程:动态求平均值/求和,你会吗?

 

作者:老菜鸟来源:部落窝教育发布时间:2022-09-21 16:18:53点击:519

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

编按:

今天要和大家讨论的可以说是Excel里最基础的统计问题:求和与求平均值。不要小看这些基础的问题,很多人还真不一定能解决得了。

 

例如下图是一份成绩表,要计算英语的平均分,该怎么解决?

 

 

我想大家都知道可以用公式=AVERAGE(D2:D17)来计算,但假如学科是可以选择的,要根据选择的学科来统计平均分呢,就是动画演示的效果。

 

 

所以这并不是单纯的求平均值,而是根据指定的条件确定出范围,再去求平均值。

针对这个问题提供三个解决思路。

 

思路一:使用OFFSET函数确定范围后求平均值

公式为=AVERAGE(OFFSET(A1,,MATCH(G1,B1:D1,),COUNTA(A:A)))

 

 

OFFSET函数有五个参数,这里用到了三个参数。

第一参数基点:A1

第三参数列偏移量:MATCH(G1,B1:D1,),这里的G1就是要统计平均分的科目,利用MATCH函数计算出该科目在B1:D1这个区域的列数。

第四参数高度:COUNTA(A:A),这个函数可以计算出A列有数据的单元格个数,也就是要计算平均分的数据行数。

明白了OFFSET函数在这个公式的作用后,要实现求和的效果只需要将AVERAGE函数换成SUM函数即可。

 

思路二:使用SUMPRODUCT函数统计

公式为=SUMPRODUCT((G1=B1:D1)*B2:D17)/COUNT(B:B)

 

 

这个公式没有使用动态区域的思路,而是利用SUMPRODUCT((G1=B1:D1)*B2:D17)实现了条件求和,再用COUNT(B:B)计算出数字的个数,用合计/个数就计算出了平均分。

这个思路要比用OFFSET的简单很多,关于SUMPRODUCT的用法,可以参考之前的教程,有非常多的案例可供参考。

 

思路三:使用FILTER函数确定数据源

公式为=AVERAGE(FILTER(B2:D17,B1:D1=G1))


 

FILTER函数是Excel365新增的一个函数,是一个类似于筛选的函数,但是要比筛选更加灵活。

FILTER(B2:D17,B1:D1=G1)的第一参数B2:D17是筛选的数据区域,第二参数B1:D1=G1是筛选条件,也就是直接定位出指定科目的成绩,再用AVERAGE函数计算平均值。

不得不说还是365的函数香啊……

 

以上是按科目统计的思路解析,特点是数据都在同一列。

接下来看看按姓名进行统计的问题,也就是针对同一行的数据进行求和。

 

 

别看只是行和列的变化,公式要改的地方还不少呢,以思路一为例,需要将公式改成

=SUM(OFFSET(A1,MATCH(F6,A2:A17,),,,COUNTA(A:A)))

 

 

对比前一个问题的思路一来说,这里的OFFSET还是用了三个参数。

第一参数基点:A1,没有变化。

第二参数行偏移量:MATCH(F6,A2:A17,),这里的F6是要统计总分的姓名,利用MATCH函数计算出该学员在A2:A17这个区域的行数。

第五参数高度:COUNTA(A:A),这个函数可以计算出A列有数据的单元格个数,也就是要计算平均分的数据行数。

由于统计区域的行列属性发生了变化,OFFSET使用的参数也作出了对应的调整,但是原理基本不变,相信通过这种对比讲解,大家也能领悟到一些奥妙的。

解决这个问题的其他思路该如何调整就留给小伙伴们自己思考吧,做出来的可以留言分享。


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

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

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

IMG_256

相关推荐:

去掉最低分和最高分算平均分:SUBTOTAL等四个函数法

如何在单元格顶部按分组求和?这2种方法最简单!

Excel教程:如何制作带有层次和透视感的图表?

八大查找函数公式,轻松搞定数据中的多条件查找

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。