
SUMPRODUCT函數介紹—舊陣列函數時代的英雄
SUMPRODUCT函數可算是Excel進階函數之一,它在Excel動態陣列公式功能出現之前,可說是處理陣列公式最厲害的函數,有非常多的應用方式及使用技巧。本篇文章將先介紹SUMPRODUCT函數的幾個應用方式,接著再說明新的動態陣列公式功能釋出後,有哪些可替代SUMPRODUCT函數的方式。
SUMPRODUCT函數使用目的
於SUMPRODUCT這個名字中,sum是加總之意,product有相乘之意;另SUMPRODUCT函數的參數為陣列(可放入多個陣列):=SUMPRODUCT(array1,[array2],[array3],...),綜上所述,此函數的使用目的為:
將不同陣列先進行相乘,再進行加總。

「將不同陣列先進行相乘,再進行加總」這句話是什麼意思呢?可能有些人對「陣列」的觀念不夠清楚,那可以先想像為「值」的相乘(1×1的陣列就是值),實際用SUMPRODUCT函數舉個例:
=SUMPRODUCT(2,3) = 2×3 = 6

接著進一步用「陣列」來舉例:
=SUMPRODUCT({2,1},{3,5}) = 2×3+1×5 = 11

下一節,將以實際常用的範例進行說明。
SUMPRODUCT函數應用舉例
1.以單價、數量算出總價
Excel用戶若要以商品的單價及銷售量算出總銷售額,一般會先計算每一個商品的銷售額,再進行加總。(如下圖)

若利用SUMPRODUCT函數,總銷售額的公式可簡化為:
SUMPRODUCT(C2:C6,D2:D6) = 1×5+2×4+3×3+4×2+5×1 = 35

2.條件式加總
下表為一旅遊人數統計表,假設要計算出台北的總旅遊人數,那計算的條件為:「城市」欄位="台北"。

此條件式套用在SUMPRODUCT函數中,其輸出的值為一True/False的陣列。(如下圖公式)

因為True=1、False=0,所以我們期望得到的結果是:
SUMPRODUCT({109893;161175;147539;184384;342297...},{1;1;1;1;0;...}) = 109893×1+161175×1+147539×1+184384×1+342297×0.... = 602991。
但此公式的結果卻為:0。(如下圖)

其實原因是SUMPRODUCT函數中的參數不接受True/False的形式,故有一個小技巧,可以先將此True/False的陣列轉換為1或0的陣列,可直接在其括號外面×1。(如下圖)

就可以得到正確的結果喔。(如下圖)

也可以利用此方法去進行多個條件的計算,如城市="台北"及季節="春"。(如下圖)

如何以SUM的動態陣列公式替代SUMPRODUCT函數
在過去動態陣列公式功能還沒出現以前,SUM這個函數僅有最簡單的相加功能:將一串數值(一陣列)進行相加。
但在動態陣列公式功能出現後,可藉由此功能去改變SUM函數公式裡的陣列,再進行相加。以第二節第一個例子來講,公式可以寫為:
=SUM(C2:C6*D2:D6) = SUM(1×5+2×4+3×3+4×2+5×1) = SUM(5,8,9,8,5) = 35 (如下圖)

這是舊版Excel無法辦到的方式呢!
接著以第二節第二個例子來講,也可以藉由條件式True/False的陣列去改變SUM函數中既有的陣列,如公式可寫成:
SUM(旅遊人數表[旅遊人數]*(旅遊人數表[城市]="台北")) = {109893;161175;147539;184384;342297...},{TRUE;TRUE;TRUE;TRUE;FALSE;...}) = 109893×1+161175×1+147539×1+184384×1+342297×0.... = 602991。

是不是很有趣呢!
另外若各位想了解更多的SUMPRODUCT函數應用,可以參考Excel專家Alan Murray先生近期於LinkedIn上發表的一篇文章。

總結及補充:
上述舉的兩個範例皆為加總,其實SUMPRODUCT函數也可以用來計數,用法為SUMPRODUCT((範圍="條件")*1),其原理是將True/False的陣列轉化為1或0的陣列,再進行加總計數,於上方Alan Murray先生的文章中有相關的範例可參考喔。
雖然在動態陣列公式功能出現以後,SUMPRODUCT函數的使用頻率可能會逐漸降低,甚至慢慢被人們遺忘,但SUMPRODUCT函數絕對是在舊Excel時代中無疑是扮演最重要的角色之一,仍是Excel用戶相當值得花時間了解、學習的一個函數