Excel函數王者Sumproduct之大顯身手

06-10

上期我們已經講了Sumproduct函數的多條件查找與求和，單條件求和與查找，其實Sumproduct函數功能十分強大。今天讓我們繼續學習Sumproduct函數的進階應用。

首先讓我們回顧下Sumproduct函數的語法和注意事項：

Sumproduct函數功能是在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和。

語法：Sumproduct(array1,array2,array3, ...)Array1, array2, array3, ... 為 2 到 255 個數組，其相應元素需要進行相乘並求和。

數組參數必須具有相同的維數，否則，函數 Sumproduct 將返回錯誤值 #VALUE!。

特別提醒：Sumproduct函數的計算區域不採用一整列計算，一般是採用單元格區域，比如A1:A100,而不採用A:A。

函數 Sumproduct 將非數值型的數組元素作為 0 處理。

一、數組求和

Sumproduct函數支持數組求和。前幾期我曾說過sum函數的應用，當sum函數對數組求和時，必須按ctrl enter shift組合鍵，數組公式才能生效。Sumproduct函數不要按三鍵求和，寫完公式後直接按enter就能對數組公式進行求和。

上圖為某水果店報表，請問總價是多少？

思路：如果按常規方法做，先求出每種水果的總價，最後再累加得出水果總價。如果水果很多的話，這種方法既耗時費力，又容易出錯。經觀察，我們發現，各種水果單價和數量相乘，最後再累加即可得到總價。我們可以採用Sumproduct函數數組公式來進行求和。

公式

=Sumproduct(B2:B8*C2:C8)

公式解讀：B2*C2 B3*C3 …B8*C8。

該公式為數組公式，Sumproduct函數支持數組運算，因此不用按ctrl enter shift組合鍵，就能得到結果。

二、 Sumproduct函數二維區域求和

上表為某公司一季度業績表，請問各部門一季度各月累計業績多少？

思路:經觀察，我們發現匯總表為二維區域表。一個單元格對應兩個欄位，就是二維表。在右表中，F2單元格對應兩個欄位，一個欄位是部門，一個是時間。我們可以用Sumproduct函數的多條件求和。

公式

=Sumproduct(($A$2:$A$11=F$1)*($B$2:$B$11=$E2),$C$2:$C$11)

公式解讀：Sumproduct函數的參數必須維度一致，A2:A11, B2:B11, C2:C11的維度一致。我們構造公式要滿足兩個條件，一個是部門名字，一個是時間。F$1是混合引用，當我們把公式進行右拉和下拉，其列號會發生變化，而行號被固定住。$E2道理也一樣。如果不理解，可以參看本訂閱號歷史文章：引用的切換和智能匹配。該公式進行左右上下拖動後會自動進行匹配，不用調整參數。

二維區域的引用快捷思路：

經觀察，右表中單元格兩個欄位條件的規律如下：

F2=F1*E1,F3=F1*E2,

G2=G1*E2,G3= G1*E2。

對於部門來說，行號1沒有發生變化，而列號發生變化，因此我們可以用混合引用F$1來表示。同理對於月度來說，列號不變，行號發生變化，我們可以用$E1來表示。綜合起來就是F$1*$E1。

三、 Sumproduct函數模糊求和

上圖為某公司一季度業務表，請問青春部一月累計業績多少？

思路:在A列中有青春1部，青春2部.也就是說部門中只要含有青春二字，求其一月累計業績。

公式

=Sumproduct(ISNUMBER(FIND("青春",A2:A11))*(B2:B11=F2),C2:C11)

公式解讀：

Find函數用來對中某個字元串進行定位，以確定其位置。

Find函數進行定位時，總是從指定位置開始，返回找到的第一個匹配字元串的位置，而不管其後是否還有相匹配的字元串。

語法為：find（要查找的字元串，查找的單元格，從第幾個字元開始查找）如果省略最後一個參數，則默認從第一個字元開始查找。

FIND("青春",A2:A11)是找出A列中如果含有青春二字就返回數字，否則返回錯誤值。我們可以用F9來試運算該函數得到：

在find函數外圍還有一個ISNUMBER函數，構成嵌套函數。這是判斷數字的函數。

ISNUMBER函數只有一個參數value，表示進行檢驗的內容，如果檢驗的內容為數字，將返回TRUE，否則將返回FALSE。其函數語法為：ISNUMBER（value）是判斷函數，最終返回邏輯值真和假。

我們可以用F9來試運算該嵌套函數（isnumber和find嵌套函數）得到：，最後再用Sumproduct函數進行求和。在sumproduct函數中，TRUE當做1來處理，FALSE當做0來處理。因此我們就能用sumproduct、ISNUMBER、FIND函數嵌套來進行模糊查找。

GIF操作如下：

本教程的源數據表格百度網盤網址為：http://pan.baidu.com/s/1gfAKWZD

Sumproduct函數運用很廣泛，在業內號稱萬能函數。掌握Sumproduct函數，必將讓你的函數水平百尺竿頭更進一步。如果能將所學的知識，互相融會貫通，那麼你離成功就不遠了。