計數與求和于一身的SUMPRODUCT函數(一)

1 ?為什么要使用SUMPRODUCT函數？

不論是COUNTIF函數、COUNTIFS函數，還是SUMIF函數、SUMIFS函數，在進行判斷時，工作表必須存在這樣的判斷區域，否則這幾個函數是不能使用的。但是，在實際工作中，原始數據往往是各種各樣的，條件隱含在數據中的情況比比皆是。

下圖就是這樣的一種情況。工作表左邊的4列是原始數據，右側是需要做的匯總報表，按照產品類別和月份進行匯總。

在這個表單數據中，并沒有單獨的一列來保存產品類別，也沒有單獨的一列來保存月份（盡管名義上A列是日期，但不是真正的日期），因此，如果要使用SUMIFS函數來求和的話，就必須把C列產品編碼的左兩位所代表的產品類別分離出來單獨保存在一列里，把A列數據中間兩位數字取出來轉換為月份單獨保存另外一列里，這樣的操作顯然違背了高效數據分析的基本原則。那么，我們能不能在不使用輔助列的情況下，使用一個綜合公式來解決呢？答案是肯定的，就是使用SUMPRODUCT函數。

2、SUMPRODUCT函數基本原理

SUMPRODUCT的功能是對多個數組的各個對應的元素進行相乘，然后再把這些乘積相加，語法如下：

=SUMPRODUCT(數組1，數組2，數組3，……)

顧名思義，從函數名字上理解，SUMPRODUCR= SUM + PRODUCT。

在使用這個函數時，要牢記以下重要的兩點：

各個數組必須具有相同的維數。

非數值型的數組元素是作為0處理的。例如，邏輯值TRUE和FALSE都被處理成0了，為了把TRUE還原成數字1，把FALSE還原為數字0，可以把它們都乘以1：TRUE*1，FALSE*1）。

下面我們結合2個示例，來解釋SUMPRODUCT函數的基本原理和用法。

示例1

下圖是一個各個產品銷售單價、銷售量和折扣率的數據，現在要求計算所有產品的銷售總額、折扣額、銷售凈額。

對于這樣的問題，很多人會采用這樣的做法：在數據區域的右側插入2個輔助列，分別計算出每個產品的銷售額和折扣額，再使用SUM函數求和。

這里，每個產品的銷售額就是每個產品單價和銷售量相乘的結果，也就是B列的單價與C列銷售量相乘；每個產品的折扣額就是每個產品單價、銷售量和折扣率相乘的結果，也就是B列的單價與C列銷售量以及D列折扣率相乘的結果。

這種先把幾列（或者幾行）數據分別相乘，然后再把這些乘積相加的計算問題，Excel給我們提供了一個非常有用的函數：SUMPRODUCT函數。

在這個例子中，利用SUMPRODUCT函數計算所有產品的銷售總額、折扣額、銷售凈額的公式分別如下：

銷售總額：

=SUMPRODUCT(B2:B9,C2:C9)

折扣額：

=SUMPRODUCT(B2:B9,C2:C9,D2:D9)

銷售凈額：

=SUMPRODUCT(B2:B9,C2:C9,1-D2:D9)

示例2

下圖是一個評分表，有5個評價指標，每個指標的權重是不同的，現在要計算每個人的評分，而這些評分就是每個指標分數與指標權重相乘相加的合計數，也就是數學中的，那么，計算公式如下。

=SUMPRODUCT($B$2:$F$2,B5:F5)

SUMPRODUCT可以替代COUNT、COUNTA、COUNTBLANK、COUNTIF、COUNTIFS、SUMIF、SUMIFS等函數，其原理就是使用條件表達式構建只有數字0和1組成的數組，然后將這個數組中的所有數字1和0相加，就是滿足條件的單元格個數；將這些只有數字0和1的數組與實際求和區域的每個單元格數據相乘相加，就得到滿足條件的合計數。

但是，條件表達式的結果是兩個邏輯值TRUE和FALSE，而SUMPRODUCT會把這兩個邏輯值都當做0處理，因此，需要將條件表達式乘以數字1，或者除以數字1，或者輸入兩個負號，以使其轉換為數字1和0。