SUMPRODUCT 函數全解析
SUMPRODUCT 是什麼？適用場景
SUMPRODUCT 是 Excel 中一個功能強大的數據分析函數，能將多個數組（或範圍）對應位置的數值相乘後再加總。這種「加權總和」的能力，讓SUMPRODUCT在處理複雜條件統計、加權平均、專案成本計算、出勤統計等情境中大放異彩。
常見應用場景包括：
– 計算不同專案成員的加權績效分數
– 根據多條件篩選後統計銷售額
– 分析多維度資料（如地區、產品、時間）下的合計
– 取代SUMIFS/COUNTIFS無法處理的複雜邏輯
SUMPRODUCT 語法與參數詳解
SUMPRODUCT 的基本語法如下：
SUMPRODUCT(array1, [array2], [array3], ...)
- array1：必填。第一個要相乘並加總的數組或範圍。
- array2、array3…：選填。其他要相乘並加總的數組或範圍。
注意事項：
– 所有數組必須維度一致（行數、列數相同），否則會出現 #VALUE! 錯誤。
– 支援數值、邏輯值（TRUE/FALSE 會自動轉為 1/0），但遇到文字時需特別處理。
– 可用於單一範圍（如 SUMPRODUCT(A1:A10)），此時等同 SUM。
SUMPRODUCT 的基本用法與範例
1. 基本乘積加總
假設有以下產品銷售數據：
| 產品 | 單價 | 銷量 |
|---|---|---|
| A | 100 | 5 |
| B | 80 | 8 |
| C | 120 | 3 |
計算總銷售額：
=SUMPRODUCT(B2:B4, C2:C4)
解釋：
100×5 + 80×8 + 120×3 = 500 + 640 + 360 = 1500
2. 加權平均計算
若需計算加權平均分數（如績效考核），假設權重在 D2:D4，分數在 E2:E4：
=SUMPRODUCT(D2:D4, E2:E4) / SUM(D2:D4)
應用情境：
專案管理中，根據不同任務權重計算團隊成員的總績效分數。
SUMPRODUCT 進階應用技巧
1. 數值條件篩選
計算銷量大於5的產品總銷售額：
=SUMPRODUCT((C2:C4>5)*B2:B4, C2:C4)
說明：
(C2:C4>5) 會產生 {FALSE, TRUE, FALSE}，轉為 {0,1,0}，只計算B3×C3。
2. 多條件篩選
計算單價大於80且銷量大於5的產品總銷售額：
=SUMPRODUCT((B2:B4>80)*(C2:C4>5)*B2:B4, C2:C4)
3. 文字條件與模糊比對
計算產品名稱以「A」開頭的總銷售額：
=SUMPRODUCT((LEFT(A2:A4,1)="A")*B2:B4, C2:C4)
模糊比對：
若需比對包含某字串，可結合ISNUMBER與SEARCH：
=SUMPRODUCT((ISNUMBER(SEARCH("A",A2:A4)))*B2:B4, C2:C4)
4. 與其他函數結合
- LEN：計算產品名稱長度大於1的總銷售額
- ISNUMBER：判斷儲存格是否為數字
- IF：搭配陣列公式進行更複雜邏輯
5. SUMPRODUCT 與 SUMIFS/COUNTIFS 比較
| 功能 | SUMPRODUCT | SUMIFS/COUNTIFS |
|---|---|---|
| 多條件支援 | 支援複雜邏輯 | 支援，較直觀 |
| 文字/模糊比對 | 可自訂（需公式） | 內建支援萬用字元 |
| 跨表運算 | 較不便 | 較方便 |
| 效能 | 大量資料較慢 | 較快 |
| 陣列運算 | 支援 | 不支援 |
建議：
– 條件簡單時用SUMIFS/COUNTIFS
– 條件複雜、需自訂邏輯時用SUMPRODUCT
SUMPRODUCT 常見錯誤與排查
1. #VALUE! 錯誤
- 原因：數組維度不一致（如一組有3列，另一組有4列）。
- 解法：檢查所有參數範圍是否完全對齊。
2. #REF! 錯誤
- 原因：參照到已刪除的儲存格或範圍。
- 解法：確認所有參照範圍有效。
3. 空白或非數字資料
- 空白視為0，文字資料會導致錯誤或被忽略。
- 建議：可用IFERROR或ISNUMBER輔助處理。
4. 效能問題
- 大量資料時SUMPRODUCT運算較慢，建議減少不必要的計算範圍，或考慮分批處理。
SUMPRODUCT 實戰案例
1. 專案成本統計
假設每個專案有不同成本項目與數量，需統計總成本：
| 專案 | 項目 | 單價 | 數量 |
|---|---|---|---|
| A | 設計 | 500 | 2 |
| A | 工程 | 800 | 1 |
| B | 設計 | 500 | 1 |
| B | 工程 | 800 | 2 |
計算A專案總成本：
=SUMPRODUCT((A2:A5="A")*C2:C5, D2:D5)
2. 出勤天數統計
統計員工出勤（Y/N）天數：
| 員工 | 出勤 |
|---|---|
| 小王 | Y |
| 小李 | N |
| 小張 | Y |
計算小王出勤天數：
=SUMPRODUCT((A2:A4="小王")*(B2:B4="Y"))
SUMPRODUCT 在 Google Sheets 的應用差異
- 語法與Excel相同，但Google Sheets在處理陣列時更為彈性。
- 支援動態陣列，部分複雜條件可直接運算。
- 注意：Google Sheets在處理大量資料時效能可能略低，建議範圍適度。
FAQ：SUMPRODUCT 你可能會遇到的問題
Q1：SUMPRODUCT 可以跨工作表運算嗎？
A：可以，但需完整指定工作表名稱與範圍，如 =SUMPRODUCT(Sheet1!A1:A10, Sheet2!B1:B10)。建議兩表範圍維度完全一致。
Q2：可以與其他函數巢狀使用嗎？
A：可以，常見如與IF、ISNUMBER、LEN等結合，進行複雜條件判斷。
Q3：如何處理文字條件？
A：可用如 (A2:A10=”指定文字”) 或結合SEARCH、LEFT等函數進行模糊比對。
Q4：SUMPRODUCT 可以處理多維陣列嗎？
A：僅支援同維度的多組一維或二維陣列，三維以上需拆解。
Q5：遇到效能瓶頸怎麼辦？
A：建議縮小計算範圍，或將部分條件預先計算，必要時考慮使用樞紐分析表。
與專案管理工具的結合建議
在專案管理平台（如 Monday.com）中，常見需求為將專案資料匯出Excel進行進階分析。此時可利用SUMPRODUCT快速統計多條件下的專案進度、成本、資源分配等。例如，將專案任務清單匯出後，根據任務狀態與負責人計算加權完成率，或根據多條件統計各部門的工時分布。
若需更自動化的數據整合與分析，建議搭配Monday.com等工具的API或匯出功能，結合Excel的SUMPRODUCT進行高效決策支持。
總結與進一步提升建議
SUMPRODUCT 是Excel與Google Sheets中不可或缺的數據分析利器，無論是加權計算、條件統計，還是複雜邏輯運算，都能大幅提升工作效率。熟練掌握SUMPRODUCT，並靈活結合其他函數與專案管理工具，將讓你在數據處理與決策分析上事半功倍。建議讀者可將SUMPRODUCT應用於日常專案管理、業績追蹤、成本分析等場景，並善用如Monday.com等現代化平台，進一步提升團隊協作與數據洞察力。
