Excel新晉萬能函數，吊打SUMIFS，讓LOOKUP黯然失色！_b-2轟炸機

文章圖片

文章圖片

文章圖片

文章圖片

文章圖片

我是【桃大喵學習記】，歡迎大家關注喲~ ，每天為你分享職場辦公軟件使用技巧干貨！
——首發于微信號：桃大喵學習記

今天跟大家分享的Excel新晉萬能函數，就是那個大家熟悉但又未必能完全掌握的——SUMPRODUCT函數！這個函數最初只是用于簡單的分組乘積后求和，但確被一些Excel大牛發掘出了無數擴展用法。
SUMPRODUCT函數介紹
功能：SUMPRODUCT函數主要功能是返回相應的數據或區域乘積的和。
語法：=SUMPRODUCT（數組1數組2數組3 ...）。
解讀：

①數組1數組2數組3 ... 為 2 到 30 個數組，其相應元素需要進行相乘并求和。
②數組參數必須具有相同的維數，否則，函數 SUMPRODUCT 將返回錯誤值 #VALUE! 。
③函數 SUMPRODUCT 將非數值型的數組元素作為 0 處理。

一、基本乘積求和
萬能公式：=SUMPRODUCT(數組1數組2數組3 ...)
實例：

如下圖所示，這是一個商品單價和數量信息表，需要計算總價格。

在目標單元格中輸入公式：
=SUMPRODUCT(B2:B7C2:C7)
然后點擊回車即可

解讀：

SUMPRODUCT函數的功能是返回相應的數據或區域乘積的和，公式=SUMPRODUCT(B2:B7C2:C7)中，數據區域有B2:B7和C2:C7兩個，這兩個數據區域對應數據元素先乘積，后求和，得到最終的總價格。

二、單條件計數（搶COUNTIF飯碗）
萬能公式：=SUMPRODUCT(--(條件數據區域=條件))
實例：

如下圖所示，統計女性員工人數。

在目標單元格中輸入公式：
=SUMPRODUCT(--(C2:C6=\"女\"))
然后點擊回車即可

解讀：

①公式中的(C2:C6=\"女\")就是把表格中的“性別”這列數據的每個元素跟“女”做判斷，返回的結果是一個數組由邏輯值TRUE或者FALSE（備注：邏輯值TRUE可以看做1 ，邏輯值FALSE可以看做0）組成。
②前面添加雙減號“--” ，其實就是一個減負運算，可以將文本數字串或邏輯值轉換為數值，就是把邏輯值TRUE轉成數值1 ，把邏輯值FALSE轉成數值0 。因為SUMPRODUCT函數會將非數值型的數組元素作為0 處理，所以我們要先轉換，轉換后再求和。

三、多條件計數（搶COUNTIFS飯碗）
萬能公式：=SUMPRODUCT((條件數據區域1=條件1)*(條件數據區域2=條件2)*(條件數據區域N=條件N))
實例：

還是以下圖為例，統計考核成績大于90分的男性人數。

在目標單元格中輸入公式：
=SUMPRODUCT((C2:C6=\"男\")*(D2:D6>90))
然后點擊回車即可

解讀：

多條計數其實跟單條件求和十分相似，只添加了一個條件。

四、單條件求和（搶SUMIF/SUMIFS飯碗）
萬能公式：=SUMPRODUCT((條件數據區域=條件)*求和數據區域)
實例：

如下圖所示，對性別為“女”的員工銷售業績求和

在目標單元格中輸入公式：
=SUMPRODUCT((D2:D6=\"女\")*F2:F6)
然后點擊回車即可

解讀：

公式中有二個數據區域分別是D2:D6=\"女\"、F2:F6 ，當D2:D6性別區域內的值等于\"女\"時返回邏輯值TRUE（1），否則返回FALSE（0）。然后二個數據對應元素先乘積，再求和，從而計算得到女性員工銷售業績求和。

五、多條件求和（搶SUMIFS飯碗）
萬能公式：=SUMPRODUCT((條件數據區域1=條件1)*(條件數據區域2=條件2)*(條件數據區域N=條件N)*求和區域)
實例：

如下圖所示，對“業務部”考核成績大于85的員工銷售業績求和

在目標單元格中輸入公式：
=SUMPRODUCT((C2:C6=\"業務部\")*(E2:E6>80)*F2:F6)
然后點擊回車即可

解讀：

多條件求和同“單條件求和”類似，只是增加了一個判斷條件而已。

當然單條件/多條件求和中，有一個比較經典的用法，就是按月單條件/多條件求和。
萬能公式：=SUMPRODUCT((MONTH(日期列)=數字月份)*(條件數據區域1=條件1)*(條件數據區域N=條件N)*求和數據列)

如下圖所示，對“業務部”按月匯總銷售業績

在目標單元格中輸入公式：
=SUMPRODUCT((MONTH(D2:D10)=G2)*(B2:B10=F2)*C2:C10)
然后回車即可

解讀：

①首先使用MONTH(D2:D10)把左側日期轉換成數字月份，然后跟G2單元格要查詢匯總的月份比較，如果成立返回邏輯值TRUE ，否則返回FALSE 。
②B2:B10=F2：檢查B2:B10區域內的值是否與F2單元格中的值相同，同樣返回一個TRUE和FALSE的數組。
③最后在跟C2:C10數據數組相乘，并返回乘積的總和。

六、多條件查找（搶LOOKUP飯碗）

如下圖所示，我們根據姓名、部門查詢對應的報銷費用。

在目標單元格中輸入公式：
=SUMPRODUCT((A2:A10=F2)*(B2:B10=G2)*C2:C10)
然后回車即可

解讀：

其實上面公式原理上還是多條件求和公式，只是求和區域只有一個符合條件，也就類似于多條件查詢指定值。

七、橫豎交叉查詢
Excel橫豎交叉查詢，所謂橫豎交叉查詢，其實就是判斷條件一個是縱橫的，一個是橫向的。

如下圖所示，我們根據左側表格中的姓名和商品名稱來查找最終的銷售額，一個是一個是縱橫的，一個是橫向的，也就是交叉查詢。

【Excel新晉萬能函數，吊打SUMIFS，讓LOOKUP黯然失色！】
在目標單元格中輸入公式：
=SUMPRODUCT((A3:A11=F2)*(B2:D2=G2)*B3:D11)
然后點擊回車即可

解讀：

這個公式本質上還是實用SUMPRODUCT函數進行多條件求和。
①A3:A11=F2 ，【姓名】等于需要統計的姓名，其實它是對左側信息表【姓名】進行判斷，判斷是否等于右側查詢表格的【姓名】，結果是一行邏輯值True（1）， False（0），本質是1維的列數組。
②B2:D2=G2 ，【商品名稱】等于需要統計的產品，其實它是對左側信息表【商品名稱】進行判斷，判斷是否等于右側查詢表格的【產品姓名】，結果是一行邏輯值True（1）， False（0），本質也是1維的列數組。
③B3:D11 ，需要統計的產品銷量區域，是一個多行多列的二維數組。

最后將這三個式子相乘，就得到了我們需要查詢的結果了。當然如果是進行交叉多條件求和也是一樣的道理，直接套用上面的公式就可以。