SUMPRODUCT 函數是什麼？
SUMPRODUCT 是 Excel 中一個功能強大的資料分析函數，能將多組數據對應相乘後加總，常用於計算加權總和、條件統計、複雜資料彙整等情境。與 SUM、SUMIFS 等函數相比，SUMPRODUCT 能靈活處理多條件與進階邏輯，特別適合需要同時考慮多個因素的分析需求。
適用情境舉例：
– 銷售總額計算（單價 × 數量）
– 多條件篩選統計（如同時滿足部門與月份條件的業績加總）
– 權重平均分數計算
– KPI 或預算分攤等複雜加總
SUMPRODUCT 函數語法與參數說明
SUMPRODUCT 的基本語法如下：
=SUMPRODUCT(array1, [array2], [array3], ...)
- array1, array2, …：要相乘並加總的數據範圍。每個範圍的大小必須相同（行數與列數一致）。
- 支援數值、邏輯運算（如條件判斷），但遇到文字或空白時需特別注意。
語法範例：
=SUMPRODUCT(A2:A10, B2:B10)
此公式會將 A2:A10 與 B2:B10 對應相乘後加總。
參數注意事項：
– 所有 array 範圍需等長，否則會出現錯誤。
– 可直接在 array 位置寫入條件式（如 (A2:A10=”A”)*B2:B10）。
SUMPRODUCT 基本用法與實例
SUMPRODUCT 最常見的應用是計算兩組數據的乘積總和，例如計算產品銷售總額。
案例：計算總銷售額
假設有下列表格：
| 產品 | 單價 | 數量 |
|---|---|---|
| A | 100 | 5 |
| B | 200 | 3 |
| C | 150 | 4 |
公式：
=SUMPRODUCT(B2:B4, C2:C4)
這會計算：100×5 + 200×3 + 150×4 = 500 + 600 + 600 = 1700
實務應用情境：
– 銷售報表自動彙總
– 成本計算（單價 × 數量）
– 物流運費總計（重量 × 單價）
SUMPRODUCT 進階應用技巧
多條件篩選與邏輯運算
SUMPRODUCT 支援多條件篩選，常用於同時滿足多個條件的統計。
範例：計算同時符合「部門=行銷」且「月份=3月」的銷售額
假設資料如下：
| 部門 | 月份 | 銷售額 |
|---|---|---|
| 行銷 | 3 | 1000 |
| 業務 | 3 | 800 |
| 行銷 | 4 | 1200 |
公式：
=SUMPRODUCT((A2:A4="行銷")*(B2:B4=3), C2:C4)
此公式會將符合條件的銷售額加總。
技巧說明：
– (A2:A4=”行銷”) 會產生 TRUE/FALSE 陣列，乘上 (B2:B4=3) 後，只有同時為 TRUE 的資料才會被計算。
– 可用於多條件篩選、交叉分析。
與 SUMIFS 比較：
– SUMIFS 適合單純條件加總，SUMPRODUCT 可進行更複雜的邏輯運算與動態條件。
加權平均、權重計算
SUMPRODUCT 可輕鬆計算加權平均，常用於績效、評分等場景。
範例：計算加權平均分數
| 項目 | 分數 | 權重 |
|---|---|---|
| A | 80 | 0.3 |
| B | 90 | 0.5 |
| C | 70 | 0.2 |
公式：
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
這會計算：(80×0.3 + 90×0.5 + 70×0.2) ÷ (0.3+0.5+0.2)
實務應用情境：
– 員工績效加權計算
– 投資組合報酬率計算
– 專案進度加權統計
處理空值、非數值資料
SUMPRODUCT 在遇到空白或非數值資料時，可能導致錯誤或計算異常。
常見情境：
– 空白儲存格會被視為 0，不影響計算。
– 若有文字或錯誤值（如 #N/A），會導致 #VALUE! 錯誤。
避免錯誤技巧：
– 可搭配 ISNUMBER、IFERROR 等函數處理非數值資料。
– 範例：=SUMPRODUCT((ISNUMBER(A2:A10))*A2:A10, B2:B10)
SUMPRODUCT 常見錯誤與排解
常見錯誤訊息與原因：
| 錯誤訊息 | 可能原因 | 排解方法 |
|---|---|---|
| #VALUE! | 範圍長度不一致、含文字或錯誤值 | 檢查範圍大小、排除非數值資料 |
| #REF! | 參照範圍已刪除或無效 | 修正範圍引用 |
排查步驟：
1. 確認所有 array 範圍大小一致。
2. 檢查是否有非數值、錯誤值混入。
3. 減少全欄引用，避免效能與錯誤問題。
FAQ：
– Q：SUMPRODUCT 為何出現 #VALUE!？
– A：通常是範圍大小不一致或資料中有文字、錯誤值。
– Q：如何避免 SUMPRODUCT 計算錯誤？
– A：預先用 ISNUMBER、IFERROR 處理資料，並確保範圍一致。
SUMPRODUCT 實務應用案例
專案管理：預算分攤
假設有多個專案，需依據人力投入比例分攤總預算：
| 專案 | 人力投入 | 分攤比例 | 預算 |
|---|---|---|---|
| A | 5 | 0.4 | 40000 |
| B | 3 | 0.3 | 30000 |
| C | 2 | 0.3 | 30000 |
公式：
=SUMPRODUCT(C2:C4, D2:D4)
可快速計算總分攤預算。
KPI 計算
將不同指標依權重計算總績效分數，SUMPRODUCT 可一次完成。
延伸應用：
– 結合 IF 條件，僅計算達標項目。
– 與 SUMIFS、COUNTIFS 搭配，提升統計彈性。
SUMPRODUCT 效能優化建議
SUMPRODUCT 在處理大量資料時，計算量大可能影響效能。
優化建議：
– 避免全欄（如 A:A）引用，改用精確範圍（如 A2:A1000）。
– 若資料量極大，可考慮分批計算或使用樞紐分析表。
– 清理不必要的錯誤值與文字資料，減少運算負擔。
實務經驗分享：
– 在專案管理報表中，建議將資料整理為結構化表格，並用精確範圍搭配 SUMPRODUCT，能大幅提升運算速度與準確性。
SUMPRODUCT 在 Google Sheets 的應用
Google Sheets 同樣支援 SUMPRODUCT 函數，語法幾乎一致。
差異與注意事項：
– Google Sheets 對陣列運算的支援度更高，可直接用陣列公式進行動態計算。
– 若遇到資料型態不一致，Google Sheets 可能自動轉型，但仍需注意範圍一致。
操作建議：
– 在 Google Sheets 中，SUMPRODUCT 亦可搭配 FILTER、ARRAYFORMULA 等進行更彈性的資料分析。
– 若需多條件篩選，語法與 Excel 相同，但可利用 QUERY 函數進行更進階的資料處理。
推薦專案管理工具提升效率
雖然 Excel SUMPRODUCT 能處理多種資料分析需求，但在專案管理、團隊協作與跨部門資料整合時，專業工具能進一步提升效率。例如，Monday.com、ClickUp 等平台，支援自動化報表、進度追蹤、權限管理，並可與 Excel、Google Sheets 整合，讓團隊能即時掌握專案進度與數據分析結果。對於需要跨部門協作、動態資料彙整的情境，這類工具能大幅簡化流程、減少人為錯誤，提升整體生產力。
