SUMPRODUCT 函數完整解析
SUMPRODUCT 是 Excel 中功能強大的陣列運算函數，能將多個數組（陣列）對應元素相乘後再加總，適合處理加權統計、條件計算、複雜資料彙整等情境。無論是專案管理的成本分析、業務績效統計，還是日常數據整理，SUMPRODUCT 都能大幅提升效率。
SUMPRODUCT 適用場合摘要：
| 應用情境 | 說明 | 優點 | 限制 |
|---|---|---|---|
| 加權平均 | 計算不同權重下的平均值 | 一步完成，不需輔助欄 | 權重需正確對應 |
| 條件加總 | 依多重條件篩選資料後加總 | 支援複雜條件，彈性高 | 條件寫法需留意 |
| 條件計數 | 計算符合條件的資料筆數 | 可多條件組合 | 需轉換布林值為數字 |
| 複雜資料彙整 | 多數組相乘後加總，如多層加權統計 | 一步完成多重運算 | 數組大小必須一致 |
| 取代 SUMIFS | SUMIFS 不支援 OR、部分複雜條件時可取代 | 條件彈性高 | 公式較長，易出錯 |
SUMPRODUCT 基本語法與參數說明
SUMPRODUCT 的語法如下：
=SUMPRODUCT(array1, [array2], [array3], ...)
- array1, array2, …：要參與運算的數組（可為範圍、常數陣列、運算結果），數組大小必須一致。
- 支援型態：數值、邏輯值（TRUE/FALSE 會自動轉為 1/0），不支援純文字。
- 常見錯誤：
- 數組大小不一致：會回傳 #VALUE!。
- 數組含非數值且無法自動轉換：回傳 #VALUE!。
- 空白儲存格：視情況自動當作 0。
參數限制與注意事項：
– 數組大小必須完全一致（列數、欄數相同）。
– 若有邏輯運算（如條件判斷），需以括號包住並轉為數值（*1 或 –）。
– 不支援多重區域（如 A1:A3, C1:C3），需為連續範圍。
SUMPRODUCT 實用範例解析
單一數組與雙數組計算
單一數組加總：
=SUMPRODUCT(A1:A3)
等同於 =SUM(A1:A3)，通常直接用 SUM 更直觀。
雙數組乘積加總（常見於加權統計）：
假設 A1:A3 為單價，B1:B3 為數量：
| A | B |
|---|---|
| 100 | 2 |
| 150 | 3 |
| 200 | 1 |
公式：
=SUMPRODUCT(A1:A3, B1:B3)
計算：(100×2) + (150×3) + (200×1) = 200 + 450 + 200 = 850
應用情境：
– 銷售總額、成本計算、工時加權等。
多數組計算與應用場合
SUMPRODUCT 支援多個數組同時相乘後加總，常見於多層加權計算。
範例：三數組加權統計
| A | B | C |
|---|---|---|
| 2 | 3 | 4 |
| 1 | 5 | 2 |
| 3 | 2 | 6 |
公式：
=SUMPRODUCT(A1:A3, B1:B3, C1:C3)
計算：(2×3×4) + (1×5×2) + (3×2×6) = 24 + 10 + 36 = 70
應用情境：
– 多維度評分、績效加權、複雜成本分攤。
條件計算與多重條件應用
SUMPRODUCT 可彈性組合多個條件，進行條件加總或條件乘積。
單一條件加總：
| 價格 | 數量 | 類別 |
|---|---|---|
| 100 | 1 | A |
| 200 | 2 | B |
| 300 | 3 | A |
只計算類別為 A 的總金額：
=SUMPRODUCT((C1:C3="A")*(A1:A3)*(B1:B3))
運算過程：{1,0,1} × {100,200,300} × {1,2,3} = {100,0,900}，總和 1000
多重條件（AND）：
=SUMPRODUCT((C1:C3="A")*(A1:A3>150)*(A1:A3)*(B1:B3))
說明：類別為 A 且價格大於 150
多重條件（OR）：
=SUMPRODUCT(((C1:C3="A")+(C1:C3="B"))*(A1:A3)*(B1:B3))
說明：類別為 A 或 B
處理空值與文字：
– 空白儲存格會被視為 0。
– 若條件範圍有文字，需確保運算結果為數值。
產業應用情境：
– 專案預算僅統計特定類型費用
– 業務績效僅計算特定區域或產品
SUMPRODUCT 計數技巧
除了加總，SUMPRODUCT 也能用來計數，尤其適合多重條件下的計數需求。
範例：計算類別為 A 的筆數
=SUMPRODUCT((C1:C3="A")*1)
結果為 2（有兩筆類別為 A）
多重條件計數：
=SUMPRODUCT((C1:C3="A")*(A1:A3>150)*1)
說明：類別為 A 且價格大於 150 的筆數
應用場合：
– 統計合格產品數、達標專案數
– 多條件篩選後的資料筆數
SUMPRODUCT 進階應用與實務案例
加權平均計算
SUMPRODUCT 可直接計算加權平均，省去輔助欄。
範例：計算加權平均分數
| 學生 | 分數 | 權重 |
|---|---|---|
| 甲 | 80 | 0.3 |
| 乙 | 90 | 0.5 |
| 丙 | 70 | 0.2 |
公式：
=SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4)
計算：(80×0.3 + 90×0.5 + 70×0.2) ÷ 1 = 24 + 45 + 14 = 83
實務應用：
– 專案績效評分、員工獎金分配、產品加權平均成本
與 SUMIFS、COUNTIFS 比較
SUMPRODUCT 與 SUMIFS、COUNTIFS 都能做條件加總/計數，但適用情境略有不同。
| 函數 | 優點 | 限制 | 適用場合 |
|---|---|---|---|
| SUMPRODUCT | 支援複雜條件、OR、運算式 | 公式較長，易出錯 | 多重條件、複雜運算 |
| SUMIFS | 直觀、易讀，支援多條件 AND | 不支援 OR、運算式條件 | 單純多條件加總 |
| COUNTIFS | 條件計數簡單 | 不支援 OR、複雜運算式 | 單純多條件計數 |
何時選用 SUMPRODUCT？
– 條件包含 OR、運算式（如大於平均值）
– 需同時進行乘積、加權等運算
– SUMIFS/COUNTIFS 無法處理的複雜情境
常見錯誤與排錯技巧
常見錯誤類型與解決方式：
| 錯誤訊息 | 可能原因 | 解決方式 |
|---|---|---|
| #VALUE! | 數組大小不一致 | 檢查所有參數範圍是否等長 |
| #VALUE! | 數組含純文字、無法轉換 | 確認所有數組內容為數值或可轉換 |
| 結果為 0 | 條件寫法錯誤、無符合資料 | 檢查條件邏輯、括號、運算式 |
| 結果異常 | 空白儲存格、公式未正確轉型 | 使用 *1 或 — 將布林轉為數字 |
排錯建議：
– 逐步拆解公式，確認每個條件產生的陣列內容
– 利用「公式評估」功能檢查中間結果
– 確認所有參數範圍一致，避免多餘空白
常見問題 FAQ
Q1：SUMPRODUCT 為什麼出現 #VALUE!?
A：最常見原因是參數數組大小不一致，或數組中有無法自動轉換為數值的文字。請檢查所有參數範圍是否等長，並確保內容為數值或邏輯值。
Q2：如何用 SUMPRODUCT 處理多重條件？
A：可將多個條件以乘法（*）連接，代表 AND 條件。如需 OR 條件，則以加法（+）連接條件陣列。
Q3：SUMPRODUCT 可以取代 SUMIFS 嗎？
A：在條件較複雜（如 OR、運算式）時，SUMPRODUCT 更具彈性。但若僅需多條件 AND 加總，SUMIFS 更直觀。
Q4：SUMPRODUCT 可以處理空白儲存格嗎？
A：空白儲存格會被視為 0，不會影響加總結果。但若需排除空白，條件式需加上非空判斷。
Q5：SUMPRODUCT 可以用於 Google Sheets 嗎？
A：可以，Google Sheets 的 SUMPRODUCT 用法與 Excel 幾乎完全相同。
總結與工具推薦
SUMPRODUCT 是 Excel 中極具彈性與威力的數據處理工具，無論是多重條件加總、加權平均、複雜資料彙整，都能大幅提升工作效率。對於專案管理、團隊協作、業績統計等需求，善用 SUMPRODUCT 能讓你在資料分析上更得心應手。
若需進一步提升團隊數據協作與專案管理效率，建議可結合 Monday.com、ClickUp 等專業工具，將 Excel 數據與專案流程整合，打造更高效的工作環境。