編按：

今天要和大家討論的可以說是Excel里最基礎(chǔ)的統(tǒng)計(jì)問題：求和與求平均值。不要小看這些基礎(chǔ)的問題，很多人還真不一定能解決得了。

例如下圖是一份成績表，要計(jì)算英語的平均分，該怎么解決？

我想大家都知道可以用公式=AVERAGE(D2:D17)來計(jì)算，但假如學(xué)科是可以選擇的，要根據(jù)選擇的學(xué)科來統(tǒng)計(jì)平均分呢，就是動畫演示的效果。

所以這并不是單純的求平均值，而是根據(jù)指定的條件確定出范圍，再去求平均值。

針對這個問題提供三個解決思路。

思路一：使用OFFSET函數(shù)確定范圍后求平均值

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

OFFSET函數(shù)有五個參數(shù)，這里用到了三個參數(shù)。

第一參數(shù)基點(diǎn)：A1

第三參數(shù)列偏移量：MATCH(G1,B1:D1,)，這里的G1就是要統(tǒng)計(jì)平均分的科目，利用MATCH函數(shù)計(jì)算出該科目在B1:D1這個區(qū)域的列數(shù)。

第四參數(shù)高度：COUNTA(A:A)，這個函數(shù)可以計(jì)算出A列有數(shù)據(jù)的單元格個數(shù)，也就是要計(jì)算平均分的數(shù)據(jù)行數(shù)。

明白了OFFSET函數(shù)在這個公式的作用后，要實(shí)現(xiàn)求和的效果只需要將AVERAGE函數(shù)換成SUM函數(shù)即可。

思路二：使用SUMPRODUCT函數(shù)統(tǒng)計(jì)

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

這個公式?jīng)]有使用動態(tài)區(qū)域的思路，而是利用SUMPRODUCT((G1=B1:D1)*B2:D17)實(shí)現(xiàn)了條件求和，再用COUNT(B:B)計(jì)算出數(shù)字的個數(shù)，用合計(jì)/個數(shù)就計(jì)算出了平均分。

這個思路要比用OFFSET的簡單很多，關(guān)于SUMPRODUCT的用法，可以參考之前的教程，有非常多的案例可供參考。

思路三：使用FILTER函數(shù)確定數(shù)據(jù)源

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

FILTER函數(shù)是Excel365新增的一個函數(shù)，是一個類似于篩選的函數(shù)，但是要比篩選更加靈活。

FILTER(B2:D17,B1:D1=G1)的第一參數(shù)B2:D17是篩選的數(shù)據(jù)區(qū)域，第二參數(shù)B1:D1=G1是篩選條件，也就是直接定位出指定科目的成績，再用AVERAGE函數(shù)計(jì)算平均值。

不得不說還是365的函數(shù)香啊……

以上是按科目統(tǒng)計(jì)的思路解析，特點(diǎn)是數(shù)據(jù)都在同一列。

接下來看看按姓名進(jìn)行統(tǒng)計(jì)的問題，也就是針對同一行的數(shù)據(jù)進(jìn)行求和。

別看只是行和列的變化，公式要改的地方還不少呢，以思路一為例，需要將公式改成

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

對比前一個問題的思路一來說，這里的OFFSET還是用了三個參數(shù)。

第一參數(shù)基點(diǎn)：A1，沒有變化。

第二參數(shù)行偏移量：MATCH(F6,A2:A17,)，這里的F6是要統(tǒng)計(jì)總分的姓名，利用MATCH函數(shù)計(jì)算出該學(xué)員在A2:A17這個區(qū)域的行數(shù)。

第五參數(shù)高度：COUNTA(A:A)，這個函數(shù)可以計(jì)算出A列有數(shù)據(jù)的單元格個數(shù)，也就是要計(jì)算平均分的數(shù)據(jù)行數(shù)。

由于統(tǒng)計(jì)區(qū)域的行列屬性發(fā)生了變化，OFFSET使用的參數(shù)也作出了對應(yīng)的調(diào)整，但是原理基本不變，相信通過這種對比講解，大家也能領(lǐng)悟到一些奧妙的。

解決這個問題的其他思路該如何調(diào)整就留給小伙伴們自己思考吧，做出來的可以留言分享。

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

去掉最低分和最高分算平均分：SUBTOTAL等四個函數(shù)法

如何在單元格頂部按分組求和？這2種方法最簡單！

Excel教程：如何制作帶有層次和透視感的圖表？

八大查找函數(shù)公式，輕松搞定數(shù)據(jù)中的多條件查找

版權(quán)申明：

本文作者老菜鳥；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。