SUMPRODUCT 函數

Excel SUMPRODUCT 函數教學

SUMPRODUCT 函數 是 Excel 中一個強大的數據處理函數，能對數組進行運算並返回結果。這個函數會將多個數組的對應元素相乘，再對結果進行加總。SUMPRODUCT 函數不僅僅適用於乘法和加總，還能在許多情況下進行條件加總或數據分析。

SUMPRODUCT 函數語法

SUMPRODUCT(array1, [array2], [array3], ...)

• array1（必填）：第一個數組或範圍，這是你需要進行運算的第一個數據集。
• array2, array3, …（選填）：後續的數組或範圍，你可以提供多達 255 個數組進行運算。

每個數組都會按元素位置進行運算，並將對應的結果加總。

SUMPRODUCT 函數的運作原理

SUMPRODUCT 函數會將多個數組的對應元素逐個相乘，然後將這些結果進行加總。換句話說，它對每個位置的數據進行乘法運算，再將所有結果進行加總。

範例

範例 1：計算加權平均

假設你有以下學生的分數數據，範圍 A1:A5 是學生的分數，範圍 B1:B5 是分數的權重：

A1: 90    B1: 0.1
A2: 80    B2: 0.2
A3: 85    B3: 0.3
A4: 95    B4: 0.4
A5: 70    B5: 0.2

如果你想計算加權平均分數，公式如下：

=SUMPRODUCT(A1:A5, B1:B5) / SUM(B1:B5)

這個公式的意思是：

• 先計算分數和權重的乘積：(90*0.1) + (80*0.2) + (85*0.3) + (95*0.4) + (70*0.2)
• 再將這些結果加總並除以權重的總和 SUM(B1:B5)。

結果是 85.0。

範例 2：計算銷售額

假設你有以下銷售數據，範圍 A1:A5 是產品數量，範圍 B1:B5 是產品單價：

A1: 10   B1: 50
A2: 15   B2: 40
A3: 20   B3: 30
A4: 25   B4: 60
A5: 30   B5: 20

如果你想計算總銷售額，可以使用 SUMPRODUCT 函數：

=SUMPRODUCT(A1:A5, B1:B5)

這個公式會將數量和單價對應相乘，然後加總所有結果。計算如下：

(10*50) + (15*40) + (20*30) + (25*60) + (30*20) = 500 + 600 + 600 + 1500 + 600 = 3800

結果是 3800，即總銷售額。

範例 3：多條件加總

你可以使用 SUMPRODUCT 函數進行多條件加總。例如，假設你有以下銷售數據，範圍 A1:A5 是銷售數量，B1:B5 是產品類別，C1:C5 是產品單價：

A1: 10   B1: "Apple"   C1: 50
A2: 15   B2: "Banana"  C2: 40
A3: 20   B3: "Apple"   C3: 30
A4: 25   B4: "Orange"  C4: 60
A5: 30   B5: "Apple"   C5: 20

如果你想計算所有 Apple 類別的總銷售額，可以使用以下公式：

=SUMPRODUCT((B1:B5="Apple") * A1:A5 * C1:C5)

這個公式的運作是：

• (B1:B5="Apple") 會產生一個布林數組，對 Apple 產生 1，對其他產品產生 0。
• 然後將該數組與數量 (A1:A5) 和單價 (C1:C5) 相乘。

結果計算如下：

(1*10*50) + (0*15*40) + (1*20*30) + (0*25*60) + (1*30*20) = 500 + 0 + 600 + 0 + 600 = 1700

結果是 1700，即 Apple 類別的總銷售額。

範例 4：條件加總（不等式條件）

假設你有以下數據，範圍 A1:A5 是數字，範圍 B1:B5 是對應的銷售金額：

A1: 5   B1: 100
A2: 15  B2: 200
A3: 25  B3: 150
A4: 10  B4: 300
A5: 30  B5: 250

如果你想對數字大於 10 的銷售金額進行加總，可以使用以下公式：

=SUMPRODUCT((A1:A5>10) * B1:B5)

這個公式會將 A1:A5>10 轉換為布林值，並將其與銷售金額進行乘法運算：

(0*100) + (1*200) + (1*150) + (0*300) + (1*250) = 0 + 200 + 150 + 0 + 250 = 600

結果是 600，即大於 10 的銷售金額總和。

如何使用 SUMPRODUCT 函數

1. 在 Excel 中，選擇一個空白的單元格。
2. 輸入公式 =SUMPRODUCT(array1, [array2], ...)，其中：
   • array1 是你要處理的第一個數組或範圍。
   • 你可以繼續提供更多的數組。
3. 按 Enter 鍵，Excel 會返回符合條件的結果。

注意事項

• 數組大小：所有數組的大小必須一致。如果 array1 和 array2 的大小不同，SUMPRODUCT 函數會返回錯誤。
• 數組元素：SUMPRODUCT 函數對數組中的每個元素逐一進行處理。如果某個元素不是數字（例如文本或空白），會將其視為 0。
• 條件處理：你可以使用邏輯條件（如 >、<、=）進行數據篩選，並將其與數字數組進行運算。

常見錯誤

1. #VALUE! 錯誤
   如果數組的大小不一致，會返回 #VALUE! 錯誤。確保所有數組的大小相同。
2. #NUM! 錯誤
   如果數組中包含非數字數據（如文本），SUMPRODUCT 函數會將其視為 0，但在某些情況下可能會導致錯誤。

相關函數

• SUM 函數：用來對數字範圍進行加總。
• SUMIF 函數：根據條件對數字範圍進行加總，單一條件。
• SUMIFS 函數：根據多個條件對數字範圍進行加總。
• COUNTIF 函數：根據條件計算範圍內符合條件的單元格數量。
• AVERAGEIFS 函數：根據多個條件計算範圍內數字的平均值。

結論

• SUMPRODUCT 函數 是一個非常靈活和強大的函數，適用於多條件的加總、加權計算、數據篩選等各種場合。
• 它支持多數據範圍運算，能夠進行條件加總、加權平均等複雜操作。