Excel教程:動態(tài)求平均值/求和,你會嗎?
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2022-11-08 17:19:18點擊:2102
編按:
單純求和、求平均值都很簡單,但是動態(tài)求和或求平均值就不簡單了。這里的動態(tài)求和或求平均值,是指根據(jù)用戶的不同選擇結(jié)果自動實現(xiàn)求和或求平均值。我們提供至少4種方法,一起來看看吧。
學(xué)習(xí),切莫囫圇吞棗。
這是讀者小A今年夏天and秋天總是加班得出的心得體會。
雖然已經(jīng)學(xué)了很久的Excel,但東一榔頭西一棒槌,不成體系??此坪芎唵蔚膯栴},別人3分鐘搞定,她卻總是暗戳戳地搞很久,還不一定正確。
這不,只是在Excel里最基本的求和與求平均值上增加了按選擇計算,就成了困擾她的大難題。
數(shù)據(jù)源如下:這是一份成績表,要計算英語的平均分。
簡單的平均值計算,可以用公式=AVERAGE(D2:D17)。
假如學(xué)科是可以選擇的,要根據(jù)選擇的學(xué)科來統(tǒng)計平均分呢?
這是一個根據(jù)指定的條件確定出范圍,再去求平均值的問題。
針對這個問題提供三個解決思路。
已在群中的同學(xué),可以@助教老師
思路一:使用OFFSET函數(shù)確定范圍后求平均值
公式為=AVERAGE(OFFSET(A1,,MATCH(G1,B1:D1,),COUNTA(A:A)))
OFFSET函數(shù)有五個參數(shù),這里用到了三個參數(shù)。
第一參數(shù)基點:A1
第三參數(shù)列偏移量:MATCH(G1,B1:D1,),這里的G1就是要統(tǒng)計平均分的科目,利用MATCH函數(shù)計算出該科目在B1:D1這個區(qū)域的列數(shù)。
第四參數(shù)高度:COUNTA(A:A),這個函數(shù)可以計算出A列有數(shù)據(jù)的單元格個數(shù),也就是要計算平均分的數(shù)據(jù)行數(shù)。
明白了OFFSET函數(shù)在這個公式的作用后,要實現(xiàn)求和的效果只需要將AVERAGE函數(shù)換成SUM函數(shù)即可。
思路二:使用SUMPRODUCT函數(shù)統(tǒng)計
公式為=SUMPRODUCT((G1=B1:D1)*B2:D17)/COUNT(B:B)
這個公式?jīng)]有使用動態(tài)區(qū)域的思路,而是利用SUMPRODUCT((G1=B1:D1)*B2:D17)實現(xiàn)了條件求和,再用COUNT(B:B)計算出數(shù)字的個數(shù),用合計/個數(shù)就計算出了平均分。
這個思路要比用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ù)計算平均值。
不得不說還是365的函數(shù)香啊……
以上是按科目統(tǒng)計的思路解析,特點是數(shù)據(jù)都在同一列。
接下來看看按姓名進行統(tǒng)計的問題,也就是針對同一行的數(shù)據(jù)進行求和。
別看只是行和列的變化,公式要改的地方還不少呢,以思路一為例,需要將公式改成
=SUM(OFFSET(A1,MATCH(F6,A2:A17,),,,COUNTA(A:A)))
對比前一個問題的思路一來說,這里的OFFSET還是用了三個參數(shù)。
第一參數(shù)基點:A1,沒有變化。
第二參數(shù)行偏移量:MATCH(F6,A2:A17,),這里的F6是要統(tǒng)計總分的姓名,利用MATCH函數(shù)計算出該學(xué)員在A2:A17這個區(qū)域的行數(shù)。
第五參數(shù)高度:COUNTA(A:A),這個函數(shù)可以計算出A列有數(shù)據(jù)的單元格個數(shù),也就是要計算平均分的數(shù)據(jù)行數(shù)。
由于統(tǒng)計區(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)推薦:
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
10種職場人最常用的excel多條件查找方法?。ńㄗh收藏)
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!