Excel如何进行动态筛选求和、求平均值？

编按：单纯求和、求平均值都很简单，但是动态求和或求平均值就不简单了。这里的动态求和或求平均值，是指根据用户的不同选择结果自动实现求和或求平均值。我们提供至少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使用的参数也作出了对应的调整，但是原理基本不变，相信通过这种对比讲解，大家也能领悟到一些奥妙的。

纸上得来终觉浅，才知此事要躬行。

解决这个问题的其他思路该如何调整就留给小伙伴们自己思考吧。

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

学习交流请加微信hclhclsc进群。

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

10种职场人最常用的excel多条件查找方法！（建议收藏）

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

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

版权申明：

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