答案公布 | 沒有什麼匯總問題，不是一個SUMPRODUCT解決不了的

SUMPRODUCT函數應用

——SUMPRODUCT函數在數據匯總中的使用

如果你問我，工作中最喜歡的一個函數是什麼

不是VLOOKUP，也不是OFFSET

我選擇SUMPRODUCT

能計數，能求和，還能匹配

沒有什麼匯總問題，不是一個SUMPRODUCT解決不了的

1、什麼是SUMPRODUCT？

SUMPRODUCT函數，從字面上看包括SUM（求和）、PRODUCT（乘積），顧名思義，即先乘積後求後，它可以返回兩個或兩個以上數組或區域的對應位置乘積的和。

比如，上圖中，用SUMPRODUCT函數對A1:A4和B1:B4兩個區域進行先乘積後求和，相當於34*3 456*5 12*5 567*7，結果為6411。

但如果只是用SUMPRODUCT來做這樣的簡單運算，那實在有點「暴殄天物」了。SUMPRODUCT能做的，遠遠不止這些，不信來看。

▼

2、SUMPRODUCT用於條件統計

由於SUMPRODUCT函數的參數是數組，因此當參數中包含邏輯數組（即由TRUE和FALSE組成的數組）時，通過運算（TRUE可以轉化為1，FALSE可以轉化為0）可以實現數據的條件計數和求和。

比如，下面這個案例，表格中記錄的是一個學校全部老師的性別、職稱和每周課時情況。

現在，要求根據性別和職稱統計老師人數和課時。

以B26和B31單元格為例，請注意SUMPRODUCT函數的寫法：

公式中，$B$12:$B$22=$A26和$C$12:$C$22=$B$25為數組判斷，自上而下判斷數據行是否為「男」性以及是否是「高級」職稱，返回的結果為邏輯數組，用*做乘法運算後轉化為數值型數組（即由0、1組成），如下圖所示。

所以，總結SUMPRODUCT函數用於條件計數和求和，通用公式如下▼

條件計數

=SUMPRODUCT（（條件一）*（條件二）*（條件三）*……）

條件求和

=SUMPRODUCT（（條件一）*（條件二）*（條件三）*……，求和列）

使用SUMPRODUCT函數進行條件統計，優勢明顯：

1、完成可以替代條件計數（COUNTIF/COUNTIFS）、條件求和（SUMIF/SUMIFS）函數，同時使用更方便、易記；

2、更容易寫判斷條件，結合其它函數可以用於複雜場景下的數據統計與匯總。

下面，我來舉幾個最近函數課程和實際工作中遇到的案例。

▼

3、案例 | SUMPRODUCT中「或」的條件寫法

這個案例來自於卓越之道視頻課程《Excel函數從入門到精通，助你成為職場Excel達人》。

如下圖報表為卓越之道在運營初期（2016/4/9到2016/9/7）的粉絲增長數據，共計154條記錄。

現在我們要求根據這張表計算2016年5月份和8月份凈增關注人數（D列）的總和，請問如何計算？

這裡涉及到兩個條件，一是A列日期為5月份的數據，二是A列數據為8月份的數據，是「或」的關係。可能你想到的是SUMIFS，但是使用SUMIFS很難定義條件（你可以試試），而用SUMPRODUCT函數則可以快速進行計算，公式如下：

=SUMPRODUCT(((MONTH($A$5:$A$158)=5) (MONTH($A$5:$A$158)=8)),$D$5:$D$158)

其中，兩個條件之間用加號 進行連接，這樣兩個邏輯數組對應位置只要有一個為TRUE，相加後結果就為1，最後再與數值數組$D$5:$D$158相應位置進行先乘積後求和即得到最後的計算結果。

所以，對於邏輯為「或」的多條件之間可以用加號 ；對於邏輯為「並」的多條件之間可以用乘號*，目的都是通過運算將邏輯值轉化為1和0。

4、案例 | SUMPRODUCT用於數值匹配

最近，我在負責公司戰略任務評估，根據戰略管理辦法，一項部門任務根據承辦性質（主辦/協辦）和任務類型（一類/二類/三類）對應不同的評分標準。比如，由部門主辦的一類任務標準得分為10分，而協辦的一類任務得分為5分，具體得分表如下：

現在，我需要根據任務的承辦性質（B列）和任務類型（E列）快速將上述基準分值匹配到各部門的任務表（L列）中，如下圖所示：

按照常規匹配辦法，我們必須先對基準得分表進行處理，將A、B兩列進行連接得到一個新的表，然後用VLOOKUP函數進行匹配。

但是，如果熟練掌握SUMPRODUCT函數，就不必這麼麻煩了，直接編寫公式如下：

公式「=SUMPRODUCT(($A$40:$A$45=$B4)*($B$40:$B$45=$E4)*($C$40:$C$45))」實際上就是前述的第2個通用公式=SUMPRODUCT（（條件一）*（條件二）*（條件三）*……，求和列）。

表面上看好像是做了一個數據匹配，本質上還是條件求和。

5、案例 | 判斷條件中的輔助函數應用

這個案例即昨天我們推文中的案例，再看一遍題目。

為了跟進2017年公司項目的推進情況，進行量化評估，我設計了下面這張表，讓需求部門按項目對承辦部門（包括主辦、協辦）進行打分。

最終收到需求部門打分見L列「綜合評分」。

現在的問題是，我需要進一步按規則將評分分配到每個承辦部門，並按部門進行匯總。分配規則是：主辦部門拿滿分，協辦部門拿總分的30%。比如針對項目1，產品部作為主辦部門得分為9分，而銷售部、市場部、中收部作為協辦部門各拿30%，即2.7分。

每個部門既有可能是主辦，也有可能是協辦，所以需要按部門對主辦分和協辦分各自按部門進行匯總。最終要完成的匯總格式如下表：

這裡有兩點要注意：

1、表格中的協辦（F列），一個單元格中包含多個部門；

2、報表中部門名字寫法可能沒有統一，比如同一個部門，有些地方寫的是風險部，有的地方寫的可能是風險管理部。

昨天的文章發出後，很多朋友都下載文件進行了嘗試，比如這樣：

或者這樣：

雖然可能得出了結果，但想必是廢了九牛二虎之力，使用了中間輔助過程。

好吧，下面又到SUMPRODUCT函數隆重登場了，我們來看如何用SUMPRODUCT一步到位解決這一問題。

▼

1）主辦分匯總

2）協辦分匯總

顯然，不管是主辦還是協辦，我們的匯總思路都相同，即從項目打分表的E、F兩列判斷每一個項目是否包含要匯總的部門。轉化成公式條件，就用到了ISNUMBER加FIND函數，如果FIND返回的是一個數值，證明滿足包含關係，則通過SUMPRODUCT實現得分求和。

類似的數據場景在我們平時的工作中屢見不鮮，只要熟練掌握SUMPRODUCT函數，能夠靈活用輔助函數實現條件編寫，就可以輕鬆應對各種數據匯總問題。可以說，沒有什麼匯總的問題，不是一個SUMPRODUCT解決不了的。

當然，這個案例也有其它解法，我們也可以通過通配符技巧快速進行匯總（關於通配符技巧，可以參見《這兩個字元，你真的會用嗎》），公式如下：

B16單元格：=SUMIF($E$2:$E$11,""*""&A16&""*"",$L$2:$L$11)

C16單元格：=SUMIF($E$2:$E$11,""*""&A16&""*"",$L$2:$L$11)*0.3

以上就是今天的內容，如果你覺得有用，歡迎點贊、收藏、轉發~

案例文件獲取

卓越之道會員請在會員群獲取

非會員請在後台回復「315」獲取下載地址

案例文檔 ▼

推薦閱讀：