【
置頂公眾號
】或【
設為星標
】☝ 及時接收不迷路
哈嘍,小夥伴們,你們好呀!
之前的文章裏,給大家講過很多有關求和的技巧,包括 合並單元格求和、有錯誤值求和、帶單位的求和 等等。
今天就來給大家系統性地講解一下 SUMPRODUCT函式!
文能挑戰SUMIF函式在條件求和領域的權威,武能單挑COUNTIF函式進行條件計數,甚至還能將排名函式RANK也斬於馬下……
總之,是個狼人,不對,是個狼數!
廢話不多說,一起來看看吧!
文章較長,建議先收藏,慢慢看~
先來 分析 一下 這個函式:
SUM函式的功能是對所有參數中的數位求和;
PRODUCT函式的功能則是對參數中的所有數位求積;
SUMPRODUCT可以實作對參數之積求和的功能。
例如根據單價和數量可以直接計算出總金額,如圖所示:
但這只是SUMPRODUCT函式的基本功,今天要和大家分享它的 十八般武藝 ,招招精彩。
註:以下案例直接給出公式,有同學們適用的場景可以直接套用。
1:條件求和
如圖,對7月的銷售數量求和,公式為:=SUMPRODUCT((A2:A15="7月")*C2:C15)
2:秒了SUMIF的條件求和方法
如圖,資料來源裏沒有月份只有具體的銷售日期,試問你能用SUMIF求和嗎?
SUMPRODUCT是這樣幹的:
=SUMPRODUCT((MONTH(A2:A15)=10)*C2:C15)
3:針對多列的條件求和
如圖,要求和的數據位於多列。
公式為:=SUMPRODUCT(($A$2:$A$22=I2)*$C$2:$G$22)
看到這個問題,SUMIF也想試試,但好像一個SUMIF搞不定,得來五個……
4:多條件求和
如圖,按照銷售機構和商品名稱對銷售數量求和,這是SUMIFS的老本行,但SUMPRODUCT是這樣幹的:=SUMPRODUCT($C$2:$C$22*($A$2:$A$22=$E2)*($B$2:$B$22=F$1))
5:針對多列的多條件求和
如圖,要求滿足兩個條件(機構和商品名稱)後,並將求和的結果顯示到不同列中。SUMPRODUCT是這麽幹的:
=SUMPRODUCT($C$2:$G$22*($A$2:$A$22=$I2)*($B$2:$B$22=J$1))
6-13:條件計數
資料來源如圖,涉及四類條件計數問題,每個問題兩個公式,共八個公式。
統計女性人數:
公式1:=SUMPRODUCT(N(B2:B20=G2))
公式2:=SUMPRODUCT(N(B2:B20="女"))
統計男性本科人數:
公式1:=SUMPRODUCT((B2:B20=G4)*(C2:C20=G5))
公式2:=SUMPRODUCT((B2:B20="男")*(C2:C20="本科"))
統計30歲以上的人數:
公式1:=SUMPRODUCT(--(D2:D20>G6))
公式2:=SUMPRODUCT(--(D2:D20>30))
統計30歲到40歲之間的人數(含40歲):
公式1:=SUMPRODUCT((D2:D20>G8)*(D2:D20<=G9))
公式2:=SUMPRODUCT((D2:D20>30)*(D2:D20<=40))
RANK正在處理一個排名次的問題。
SUMPRODUCT好奇的看著,覺得有點新鮮,沈思了一會,秀出了一招。
14:排名次
公式為:=SUMPRODUCT(N($B$2:$B$9>B2))+1
15:中國式排名,我也行
公式為:
=SUMPRODUCT((B$2:B$9>=B6)/COUNTIF(B$2:B$9,B$2:B$9))
16:分組排名
公式為:=SUMPRODUCT((A$2:A$9=A2)*(C$2:C$9>=C2))
17:綜合排名
例如,下圖數據中,需要同時考慮業績增長率(最重要)、任務達成率(其次重要)和投訴解決率(第三重要)三項指標,可以使用公式:
=SUMPRODUCT(N(B$2:B$8*10000+C$2:C$8*10+D$2:D$8>=B2*10000+C2*10+D2))
18:統計不重復個數
有若幹條銷售數據,需要統計出有幾位銷售人員。
公式為=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))
十八招表演完畢,就問你SUMPRODUCT厲害不厲害?
其實只要是函式就有死穴,而SUMPRODUCT的弊病就在於:陣列參數必須具有相同的維數,否則將返回#VALUE!錯誤值。
還有3點需要註意:
1、函式SUMPRODUCT將非數值型的陣列元素作為0處理;
2、函式SUMPRODUCT不能使用通配符,但可以與函式FIND組合實作包含功能;
3、函式SUMPRODUCT是不用按<Ctrl+Shift+Enter>三鍵結束的陣列運算函式。
今天的內容怎麽樣?
這麽長的幹貨,得來不易呀,還沒有學會的同學趕緊收藏呀!
有疑問的同學可以 評論區留言 ,或者 進群交流呀 ~
Excel函式視訊課程 歡迎掃碼加入學習