當前位置: 妍妍網 > 辦公

比Sumif好用10倍!Excel求和函式之王SUMPRODUCT的十八般武藝!

2024-06-30辦公

置頂公眾號 】或【 設為星標 】☝ 及時接收不迷路

哈嘍,小夥伴們,你們好呀!

之前的文章裏,給大家講過很多有關求和的技巧,包括 合並單元格求和、有錯誤值求和、帶單位的求和 等等。

今天就來給大家系統性地講解一下 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函式視訊課程 歡迎掃碼加入學習