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

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

 

作者:老菜鸟来源:部落窝教育发布时间:2022-11-08 17:19:18点击:506

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

编按:

单纯求和、求平均值都很简单,但是动态求和或求平均值就不简单了。这里的动态求和或求平均值,是指根据用户的不同选择结果自动实现求和或求平均值。我们提供至少4种方法,一起来看看吧。

 

学习,切莫囫囵吞枣。

这是读者小A今年夏天and秋天总是加班得出的心得体会。

虽然已经学了很久的Excel,但东一榔头西一棒槌,不成体系。看似很简单的问题,别人3分钟搞定,她却总是暗戳戳地搞很久,还不一定正确。

这不,只是在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

相关推荐:

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

10种职场人最常用的excel多条件查找方法!(建议收藏)

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

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

版权申明:

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