Excel-解析SUMPRODUCT函數

在本教學網站裡,使用最多的函數就屬 SUMPRODUCT 函數,其函數意義是用來:計算乘績和。

語法:=SUMPRODUCT (array1, [array2], [array3], ...)

各陣列引數必須有相同的維度 (相同的列數、相同的欄數), 否則 SUMPRODUCT 函數會傳回 #VALUE! 錯誤值。

 

Excel-解析SUMPRODUCT函數

選取儲存格A3:B9,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A組、B組。

選取儲存格A11:H12,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:C組、D組。

(1) 直式資料

儲存格I4:=SUMPRODUCT(A組,B組)

儲存格I4:=SUMPRODUCT(A組*B組)

以上二個式子略有不同,但是結果相同。A組和B組必須相同維度、欄裡的列數要相同。

(2) 横式資料

儲存格I12:=SUMPRODUCT(C組,D組)

儲存格I12:=SUMPRODUCT(C組*D組)

以上二個式子略有不同,但是結果相同。C組和D組必須相同維度、列裡的欄數要相同。

 

Excel-解析SUMPRODUCT函數

(1) 計算A組大於5的總和

儲存格I4:=SUMPRODUCT(A組*(A組>5))

條件一:A組>5,會傳回 TRUE/FALSE 陣列。

公式中的「*」,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(2) 計算C組大於5的總和

儲存格I12:=SUMPRODUCT(C組*(C組>5))

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

儲存格I4:=SUMPRODUCT(A3:C7*D8:F12)

2個矩陣相乘,欄和列的數量相同。

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

儲存格I4:=SUMPRODUCT(A3:C3*D8:F12)

2個矩陣相乘,欄數相同和列數不相同。「;」區隔不同列,「,」區隔不同欄。

 

Excel-解析SUMPRODUCT函數

(1) 二個矩陣乘積和

儲存格I4:=SUMPRODUCT(A3:A7*D8:F12)

2個矩陣相乘,列數相同和欄數不相同。

 

Excel-解析SUMPRODUCT函數

選取儲存格A3:B16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、數值。

(1) 計算女生數值大於10的總和

儲存格H5:=SUMPRODUCT(數值*(性別="女")*(數值>10))

條件一:性別="女"

條件二:數值>10

公式中的「*」,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

 

Excel-解析SUMPRODUCT函數

選取儲存格D3:G100,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:加權。

(1) 計算加權平均

儲存格H5:=SUMPRODUCT(E5:G5*加權)/SUM(加權)

利用 SUMPRODUCT(E5:G5*加權) 計算加權分數,再除以加權的總和,即為加權平均。

 

Excel-解析SUMPRODUCT函數

選取儲存格A3:G100,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:班級、座號、性別、國中、國文、英文、數學。

(1) 計算各國中的人數

儲存格J4:=SUMPRODUCT((國中=I4)*1)

(2) 計算各國中裡女生的人數

儲存格J15:=SUMPRODUCT((國中=I15)*(性別="女"))

(3) 計算各國中裡國文大於或等於60的人數

儲存格J26:=SUMPRODUCT((國中=I26)*(國文>=60))

 

Excel-解析SUMPRODUCT函數

(1) 計算各國中裡女生英文及格人數

儲存格J4:=SUMPRODUCT((國中=I4)*(性別="女")*(英文>=60))

利用三個條件傳回的 TRUE/FALSE 陣列,再以「*」運算執行邏輯 AND 運算。並將 TRUE/FALSE 陣列轉換為 1/0 陣列再加總。

(2) 計算各國中裡女生、男生人數

儲存格J15:=SUMPRODUCT((國中=$I15)*(性別=J$14))

(3) 計算各班級中各國中人數

儲存格J26:=SUMPRODUCT((國中=$I26)*(班級=J$25))

 

Excel-解析SUMPRODUCT函數

(1) 計算各國中裡國文及格或英文及格的人數

儲存格J4:=SUMPRODUCT((國中=I4)*((國文>=60)+(英文>=60)))

在公式中裡「+」,相當於執行邏輯 OR 運算,並將傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列再加總。

(2) 計算各國中裡國文,英文,數學總分超過200的人數

儲存格J15:=SUMPRODUCT((國中=I15)*(國文+英文+數學>200))

在公式中裡「+」,相當於執行加法運算,並將傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列再加總。

【參考資料】

​ SUMPRODUCT 函數參考微軟提供的說明網頁:

https://support.microsoft.com/zh-tw/office/sumproduct-函數-16753e75-9f68-4874-94ac-4d2145a2fd2e

【延伸閱讀】

​ 本教學網站和 SUMPRODUCT 函數相關的網頁:

https://isvincent.pixnet.net/blog/search/SUMPRODUCT

學不完.教不停.用不盡文章列表

    文章標籤

    Excel SUMPRODUCT

    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()