Sumproduct已經過時了嗎?

  在一次會議上,眾目睽睽之下,我給大家演示了使用Sumproduct來解決多條件求和的問題,從此「一舉成名」。說起來有點誇張,但是當時大家的Excel應用水平普遍不高,Sumproduct、數組公式等知識對大多數人來講還是比較難以理解,能會個Sumproduct竟然也能令人「崇拜」……

自Excel 2007之後,新加入的Sumifs、Countifs等函數可以方便地進行多條件求和、計數,這些函數簡單易用、效率高,學習起來也不難,可以說是造福表弟表妹們。這些「Ifs」類型的函數在很多應用中都可以取代Sumproduct函數,難道Sumproduct已經過時了嗎?當然不是的,我們就通過幾個例子再來看一下Sumproduct函數的一些實用的用法。

先介紹一下Sumproduct的基礎知識。

語法:

SUMPRODUCT(array1, [array2], [array3], …)

作用:

將參數中的數組中的對應位置的元素相乘,得到結果後再將乘積相加。

要點:

至少一個數組作為參數;

參數必須具有相同的維數;

非數值型數據都作為0處理,所以如果參數中有邏輯值數組,應該乘以1來轉換成數值數組。

1、兩列乘積求和

如下圖所示,一列是利潤率,一列是銷售額,求總利潤。

Sumproduct已經過時了嗎?

公式1:

求總利潤。

=SUMPRODUCT(B2:B10,C2:C10)

說明:這個公式使用兩個參數,每個數組相同位置的數相乘,最後所有乘積相加即得結果。

公式2:

求總利潤。

=SUMPRODUCT(B2:B10*C2:C10)

說明:上面這個公式只有一個參數,是兩列相乘的結果作為參數,所以數組內元素直接相加即得結果。

公式3:

求包含「空調」的產品的總利潤。

=SUMPRODUCT(ISNUMBER(FIND(”空調”,A2:A10))*1,B2:B10,C2:C10)

說明:Find查找「空調」兩個字在每個單元格中的位置,找到就返回數組,否則返回錯誤值,用IsNumber判斷是否找到「空調」。

2、「或」關係的多條件求和

還記得我們在介紹Sumif函數的應用時講到的多條件求和嗎?

其中用到了如下數組公式。

=SUM(SUMIF(B2:B10,{”Lily”,”Mary”},D2:D10))

其實,我們把SUM替換為Sumproduct就不需要使用數組公式形式了。

=SUMPRODUCT(SUMIF(B2:B10,A13:A14,D2:D10))

說明:Sumproduct支持數組運算,不需要再按Ctrl Shift Enter來作為數組公式來計算。

Sumproduct已經過時了嗎?

3、兩列比較後求和

下圖是一天內的標準產出與實際產出,需要計算低於標準產出的那些記錄的和。

公式如下:

=SUMPRODUCT((C3:C10<B3:B10)*1,C3:C10)

說明:比較C列和B列的數據,得到邏輯值數組,再乘以1轉換成數值數組{1;1;0;1;0;0;0;1},Sumproduct函數再完成計算:也就是跟第二個參數中的數字對應地分別相乘後再求和。

Sumproduct已經過時了嗎?

4、在求和區域加入運算

下面是某一天生產統計的標準周期與實際周期,需要計算超出標準周期的數據中,超出的那部分時間的和。也就是下圖紅色單元格減去前面的標準周期,再加和。使用如下公式。

=SUMPRODUCT((C3:C10>B3:B10)*1,C3:C10-B3:B10)

說明:第一個參數的結果是邏輯值數組,需要乘以1轉換成數值;後面是兩列數據直接相減,得到的結果是兩列數據中相同位置的數字分別相減生成的數組。

Sumproduct已經過時了嗎?

對於能夠使用Sumifs、Countifs來完成的應用,我們就不再介紹。

學習Excel就是為了節省時間,簡單為王,效率為王!所以說,我們能夠從多種解決問題的方法中找到合適的、易於使用的就可以了。

–End–