【sumproduct用法】解鎖Excel多功能利器：SUMPRODUCT函數的全面解析與實戰應用
在Excel的數據處理世界中，有這樣一個函數，它以其獨特的數組處理能力和強大的靈活性，被譽為「瑞士軍刀」，它就是SUMPRODUCT。對於許多需要進行複雜數據分析的用戶來說，掌握SUMPRODUCT用法是提升效率、解決多條件難題的關鍵。本文將深入探討SUMPRODUCT函數的方方面面，從基本概念到高級應用，助你徹底掌握這個強大的工具。
SUMPRODUCT函數是什麼？
SUMPRODUCT函數，顧名思義，是「求和」與「乘積」的結合體。它的核心功能是將指定數組中對應位置的數值相乘，然後返回這些乘積的總和。但其真正的強大之處在於，它能夠處理數組運算，並巧妙地利用布爾邏輯（TRUE/FALSE轉換為1/0）來實現多條件求和、計數、加權平均等高級計算，而無需像傳統的數組公式那樣按下Ctrl+Shift+Enter。
簡而言之：SUMPRODUCT函數在一個運算中完成兩步：先乘后加。
SUMPRODUCT函數的基本語法
理解SUMPRODUCT用法首先要從其語法結構開始。
=SUMPRODUCT(array1, [array2], [array3], ...)
- array1 (必需)：要相乘的第一個數組或區域。
- [array2], [array3], ... (可選)：要相乘的其他數組或區域，最多可以有255個。
關鍵點：
- 每個數組或區域的行數和列數通常應該相同，否則SUMPRODUCT會返回
#VALUE!錯誤。 - SUMPRODUCT會自動將非數值項（如文本、邏輯值TRUE/FALSE）轉換為數值進行計算。其中，TRUE被視為1，FALSE被視為0。這是其實現條件判斷的關鍵。
- 如果數組中包含錯誤值（如
#DIV/0!），SUMPRODUCT通常也會返回錯誤值。
SUMPRODUCT的工作原理詳解
要深入理解SUMPRODUCT用法，我們需要了解它是如何一步步執行計算的。我們以一個簡單的例子來說明：
假設我們有兩列數據：
| 數量 (A列) | 單價 (B列) |
|---|---|
| 10 | 5 |
| 20 | 3 |
| 15 | 4 |
如果你使用公式 =SUMPRODUCT(A2:A4, B2:B4)，它的執行過程如下：
- 評估數組：
array1= {10; 20; 15}array2= {5; 3; 4}
- 對應元素相乘：
- 10 * 5 = 50
- 20 * 3 = 60
- 15 * 4 = 60
這一步產生了一個中間結果數組：{50; 60; 60}
- 求和：
- 50 + 60 + 60 = 170
最終結果為170。
更重要的是，當我們將條件表達式引入SUMPRODUCT時，其利用布爾邏輯進行轉換的機制變得尤為重要。
布爾邏輯與「--」雙負號的應用
在Excel中，像(A2:A10="蘋果")這樣的條件判斷會返回一個由TRUE和FALSE組成的數組。例如，如果A2是「蘋果」，則結果為TRUE；如果A3不是「蘋果」，則結果為FALSE。
- TRUE 在數值運算中會被視為 1。
- FALSE 在數值運算中會被視為 0。
因此，我們可以利用這一點來實現條件篩選。有幾種方法可以將TRUE/FALSE數組轉換為1/0數組：
- 乘以1：
(A2:A10="蘋果") * 1 - 加0：
(A2:A10="蘋果") + 0 - 雙負號（--）：
--(A2:A10="蘋果")雙負號是Excel中將邏輯值轉換為數值（1或0）的一種常見且高效的技巧。第一個負號將TRUE變為-1，FALSE變為0；第二個負號再將-1變為1，0保持不變。
- 與另一個數組相乘： 如果後面有一個數值數組需要相乘，那麼直接相乘即可，例如
(A2:A10="蘋果") * B2:B10。
理解了這些基礎，我們就可以開始探索SUMPRODUCT的強大應用了。
SUMPRODUCT的強大應用場景與實例
掌握了基礎的SUMPRODUCT用法，接下來我們通過具體的例子，展示它在各種複雜數據分析任務中的應用。
1. 單條件或多條件求和（替代SUMIFS）
當SUMIFS函數無法滿足你的需求（例如，條件區域不連續，或需要更複雜的「或」邏輯）時，SUMPRODUCT是你的救星。
示例數據：
| 區域 (A) | 產品 (B) | 銷量 (C) | 銷售額 (D) |
|---|---|---|---|
| 華東 | A | 100 | 1000 |
| 華南 | B | 150 | 1200 |
| 華東 | C | 200 | 1800 |
| 華北 | A | 80 | 800 |
| 華南 | C | 120 | 1500 |
| 華東 | B | 180 | 1600 |
a. 單條件求和：計算「華東」區域的總銷售額
公式：
=SUMPRODUCT((A2:A7="華東") * D2:D7)
解釋：
(A2:A7="華東")會生成一個邏輯數組：{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}。- 這個邏輯數組在與
D2:D7（即{1000; 1200; 1800; 800; 1500; 1600}）相乘時，TRUE會被視為1，FALSE被視為0。 - 因此，實際運算是：
{1; 0; 1; 0; 0; 1} * {1000; 1200; 1800; 800; 1500; 1600}，得到{1000; 0; 1800; 0; 0; 1600}。 - 最後，SUMPRODUCT將這個結果數組求和：
1000 + 0 + 1800 + 0 + 0 + 1600 = 4400。
b. 多條件求和（AND邏輯）：計算「華東」區域且產品為「B」的總銷售額
公式：
=SUMPRODUCT((A2:A7="華東") * (B2:B7="B") * D2:D7)
解釋：
(A2:A7="華東")得到{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}(B2:B7="B")得到{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}- 當這兩個邏輯數組相乘時，只有當兩個條件都為TRUE（即1*1=1）時，結果才為1。否則為0。
{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE} * {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}得到{0; 0; 0; 0; 0; 1}。
- 再乘以
D2:D7，最後求和：1600。
c. 多條件求和（OR邏輯）：計算產品為「A」或產品為「C」的總銷售額
公式：
=SUMPRODUCT(((B2:B7="A") + (B2:B7="C")) * D2:D7)
解釋：
(B2:B7="A")得到{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE}(B2:B7="C")得到{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE}- 這兩個邏輯數組相加時，只要其中一個為TRUE，結果就會大於0（即1+0=1, 0+1=1, 1+1=2）。SUMPRODUCT在與銷售額相乘后，會將這些結果作為有效值進行求和。
{1; 0; 0; 1; 0; 0} + {0; 0; 1; 0; 1; 0}得到{1; 0; 1; 1; 1; 0}。
- 再乘以
D2:D7並求和：{1; 0; 1; 1; 1; 0} * {1000; 1200; 1800; 800; 1500; 1600}- 得到
{1000; 0; 1800; 800; 1500; 0} - 求和：
1000 + 1800 + 800 + 1500 = 5100。
2. 單條件或多條件計數（替代COUNTIFS）
與求和類似，SUMPRODUCT也能輕鬆實現條件計數。
a. 單條件計數：計算「華東」區域的記錄數
公式：
=SUMPRODUCT(--(A2:A7="華東"))
解釋：
(A2:A7="華東")得到{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}。--將其轉換為{1; 0; 1; 0; 0; 1}。- SUMPRODUCT對這個1和0的數組求和，結果為
1 + 0 + 1 + 0 + 0 + 1 = 3。
b. 多條件計數（AND邏輯）：計算「華東」區域且產品為「B」的記錄數
公式：
=SUMPRODUCT((A2:A7="華東") * (B2:B7="B"))
解釋： 類似於多條件求和的解釋，只有當兩個條件都為TRUE時，乘積才為1，最終求和得到滿足所有條件的記錄數。
3. 計算加權平均值
加權平均值是SUMPRODUCT的經典用法之一。
示例數據：
| 學生 | 分數 | 權重 |
|---|---|---|
| 小明 | 85 | 0.3 |
| 小紅 | 90 | 0.4 |
| 小剛 | 75 | 0.3 |
計算加權平均分：
公式：
=SUMPRODUCT(B2:B4, C2:C4) / SUM(C2:C4)
解釋：
SUMPRODUCT(B2:B4, C2:C4)計算 (85*0.3) + (90*0.4) + (75*0.3) = 25.5 + 36 + 22.5 = 84。SUM(C2:C4)計算權重的總和：0.3 + 0.4 + 0.3 = 1。- 最終結果：84 / 1 = 84。
4. 處理日期範圍條件
SUMPRODUCT可以輕鬆地處理日期範圍條件，這在日常報表統計中非常實用。
示例數據：
| 日期 | 銷售額 |
|---|---|
| 2023/1/5 | 500 |
| 2023/1/15 | 700 |
| 2023/2/1 | 600 |
| 2023/2/10 | 800 |
| 2023/2/20 | 900 |
計算2月份（即從2023/2/1到2023/2/28）的總銷售額：
公式：
=SUMPRODUCT((A2:A6>=DATE(2023,2,1))*(A2:A6<=DATE(2023,2,28))*B2:B6)
解釋：
(A2:A6>=DATE(2023,2,1))檢查日期是否大於等於2023年2月1日。(A2:A6<=DATE(2023,2,28))檢查日期是否小於等於2023年2月28日。- 兩個條件相乘，只有同時滿足時才為TRUE（即1）。
- 再乘以銷售額，最後求和。
5. 處理部分文本匹配條件
如果你需要根據文本內容的一部分進行條件篩選，SUMPRODUCT結合其他文本函數也能實現。
示例數據：
| 描述 | 費用 |
|---|---|
| 購買辦公用品 | 200 |
| 差旅費 - 住宿 | 500 |
| 購買耗材 | 150 |
| 差旅費 - 交通 | 300 |
| 設備維修 | 400 |
計算包含「差旅費」的總費用：
公式：
=SUMPRODUCT((ISNUMBER(SEARCH("差旅費", A2:A6))) * B2:B6)
解釋：
SEARCH("差旅費", A2:A6)會在A2:A6單元格中查找「差旅費」的起始位置。如果找到，返回一個數字；如果未找到，則返回#VALUE!錯誤。ISNUMBER(...)會檢查SEARCH的結果是否為數字。如果是數字（即找到了），返回TRUE；如果是錯誤值（未找到），返回FALSE。這樣就得到了一個邏輯數組。- 這個邏輯數組與費用B2:B6相乘，最後求和。
使用SUMPRODUCT的技巧與注意事項
為了更好地掌握SUMPRODUCT用法，以下是一些進階技巧和需要注意的事項：
1. 數組長度必須匹配
這是最常見的問題。用於相乘的數組（或區域）必須具有相同的行數和列數。如果它們的維度不匹配，SUMPRODUCT將返回#VALUE!錯誤。
2. 性能考量
雖然SUMPRODUCT非常強大，但它在處理大量數據時可能會比較慢，因為它對數組中的每個元素都執行操作。對於非常大的數據集（數十萬行），請謹慎使用，或者考慮使用更高效的Power Query或DAX（Power Pivot）。
3. 處理錯誤值
如果你的數據區域中包含錯誤值（如#DIV/0!, #N/A），SUMPRODUCT通常也會返回錯誤。你可以通過結合IFERROR、ISNUMBER等函數來處理這些錯誤，例如：
=SUMPRODUCT(IFERROR((A2:A10="條件")*B2:B10, 0))注意：這個公式本身可能需要作為數組公式（Ctrl+Shift+Enter）輸入，或者根據Excel版本和具體情況調整。更通用的方法是，在原始數據源中清理錯誤值，或者使用輔助列。
4. 理解「--」雙負號
再次強調，-- 是將TRUE/FALSE轉換為1/0的簡潔高效方式。它比*1或+0更常見且推薦使用。
5. 與其他函數的配合
SUMPRODUCT可以與MID, LEFT, RIGHT, LEN, TEXT, DATE, YEAR, MONTH, DAY, ISNUMBER, IFERROR等多種函數結合使用，實現更加複雜的條件邏輯和數據處理。
常見問題（FAQ）
Q1：如何理解SUMPRODUCT中的布爾邏輯？它為何能實現條件判斷？
A1： SUMPRODUCT中的布爾邏輯是指，當你在公式中寫入一個條件表達式（如 A2:A10="蘋果"）時，Excel會返回一個由TRUE和FALSE組成的邏輯數組。在進行數值運算（如乘法）時，Excel會自動將TRUE視為數值1，FALSE視為數值0。這樣，當一個條件為TRUE時，它對應的數值就是1；當條件為FALSE時，數值就是0。通過將多個條件表達式相乘，只有當所有條件都為TRUE時，乘積才為1（1*1*1=1），否則為0，從而實現了「AND」邏輯的條件篩選。最後SUMPRODUCT再對這些0和1的乘積進行求和，就得到了滿足條件的計數或求和。
Q2：為何SUMPRODUCT在多條件求和時比SUMIFS更靈活？
A2： SUMPRODUCT相比SUMIFS更靈活主要體現在以下幾個方面：
- OR邏輯： SUMIFS只能處理「AND」邏輯的多條件，而SUMPRODUCT通過將條件表達式相加（
(條件1)+(條件2)）可以輕鬆實現「OR」邏輯。 - 不連續區域： SUMIFS要求條件區域與求和區域的長度一致，且條件區域之間是平行的。SUMPRODUCT可以處理不連續的、甚至不同維度（經過轉換后）的數組。
- 文本模糊匹配： SUMPRODUCT可以結合如
ISNUMBER(SEARCH())等函數實現文本的模糊匹配求和或計數，而SUMIFS的模糊匹配能力有限。 - 複雜數組運算： SUMPRODUCT可以直接處理和生成數組，實現加權平均等更複雜的數組運算，這是SUMIFS無法做到的。
Q3：如何用SUMPRODUCT處理數據區域中的錯誤值（如#DIV/0!）？
A3： 當SUMPRODUCT引用的數據區域中包含錯誤值時，通常會返回錯誤。最直接的解決方法是在原始數據中避免或清理錯誤值。如果無法避免，你可以在SUMPRODUCT內部結合IFERROR函數來處理：
例如，如果你的求和範圍B2:B10可能包含錯誤，你可以嘗試：
=SUMPRODUCT(--(A2:A10="條件"), IFERROR(B2:B10, 0))
這裡，IFERROR(B2:B10, 0)會將B列中的任何錯誤值替換為0，這樣SUMPRODUCT就能正常進行計算了。請注意，根據Excel版本和上下文，此方法可能需要作為數組公式（Ctrl+Shift+Enter）輸入，但在較新版本的Excel中，SUMPRODUCT本身就能很好地處理這種嵌套的數組。如果遇到問題，可以考慮使用輔助列先將錯誤值轉換為0，再進行SUMPRODUCT計算。
Q4：SUMPRODUCT函數和傳統的數組公式（Ctrl+Shift+Enter）有什麼區別？
A4： SUMPRODUCT本身就是一個特殊的數組函數，但它與需要按Ctrl+Shift+Enter（CSE）輸入的「傳統」數組公式有所不同：
- 輸入方式： SUMPRODUCT函數通常作為普通公式輸入，直接按Enter即可。而傳統的數組公式在編輯完后必須同時按下Ctrl+Shift+Enter，Excel會在公式兩端自動添加大括號
{}。 - 自動數組處理： SUMPRODUCT天生就是設計來處理數組的，它能夠在其參數內部自動進行數組運算和求和，無需顯式地以CSE方式輸入。
- 靈活性： SUMPRODUCT在處理多條件邏輯（尤其是「OR」條件）和將邏輯值轉換為數值方面表現更佳，且通常在性能上比許多自定義的CSE數組公式更優。
Q5：如何利用SUMPRODUCT實現「或」 (OR) 條件的篩選？
A5： 在SUMPRODUCT中實現「或」條件，通常是通過將多個條件表達式相加來實現的。當條件表達式相加時，只要任何一個條件為TRUE（即轉換為1），其和就會大於0。
例如，要計算區域為「華東」或「華南」的銷售額：
=SUMPRODUCT(((A2:A7="華東") + (A2:A7="華南")) * D2:D7)
在這裡，(A2:A7="華東") 和 (A2:A7="華南") 分別產生兩個邏輯數組。這兩個數組相加后，如果單元格既不是「華東」也不是「華南」，結果是0；如果滿足其中一個，結果是1；如果同時滿足（雖然在此示例中不可能，但在其他場景中可能），結果是2。SUMPRODUCT會將這個結果數組與銷售額D2:D7相乘，然後求和。這種方法有效地實現了「OR」邏輯，即只要滿足其中任一條件，其對應的銷售額就會被包含在總和中。
總結
通過本文的詳細解析和眾多實例，相信你對SUMPRODUCT用法已經有了全面的理解。它不僅僅是一個簡單的求和函數，更是一個能應對各種複雜條件篩選和數組運算的強大工具。從多條件求和計數，到加權平均，再到處理日期和文本，SUMPRODUCT都能提供簡潔高效的解決方案。熟練掌握它，將極大地提升你在Excel中的數據處理能力和工作效率。
現在，就開始嘗試將SUMPRODUCT應用到你的實際工作中吧！多加練習，你將發現這個「瑞士軍刀」的無限潛力。
