![](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/SUMPRODUCT%E5%87%BD%E6%95%B8%E4%BB%8B%E7%B4%B9_%E5%B0%81%E9%9D%A2.jpg?fit=1024%2C574&ssl=1)
SUMPRODUCT函數介紹—舊陣列函數時代的英雄
SUMPRODUCT函數可算是Excel進階函數之一,它在Excel動態陣列公式功能出現之前,可說是處理陣列公式最厲害的函數,有非常多的應用方式及使用技巧。本篇文章將先介紹SUMPRODUCT函數的幾個應用方式,接著再說明新的動態陣列公式功能釋出後,有哪些可替代SUMPRODUCT函數的方式。
SUMPRODUCT函數使用目的
於SUMPRODUCT這個名字中,sum是加總之意,product有相乘之意;另SUMPRODUCT函數的參數為陣列(可放入多個陣列):=SUMPRODUCT(array1,[array2],[array3],...),綜上所述,此函數的使用目的為:
將不同陣列先進行相乘,再進行加總。
![20240118_P1 20240118_P1](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P1.jpg?resize=360%2C95&ssl=1)
「將不同陣列先進行相乘,再進行加總」這句話是什麼意思呢?可能有些人對「陣列」的觀念不夠清楚,那可以先想像為「值」的相乘(1×1的陣列就是值),實際用SUMPRODUCT函數舉個例:
=SUMPRODUCT(2,3) = 2×3 = 6
![20240118_P2 20240118_P2](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P2.jpg?resize=335%2C161&ssl=1)
接著進一步用「陣列」來舉例:
=SUMPRODUCT({2,1},{3,5}) = 2×3+1×5 = 11
![20240118_P5 20240118_P5](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P5.jpg?resize=401%2C163&ssl=1)
下一節,將以實際常用的範例進行說明。
SUMPRODUCT函數應用舉例
1.以單價、數量算出總價
Excel用戶若要以商品的單價及銷售量算出總銷售額,一般會先計算每一個商品的銷售額,再進行加總。(如下圖)
![20240118_P4 20240118_P4](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P4.jpg?resize=476%2C256&ssl=1)
若利用SUMPRODUCT函數,總銷售額的公式可簡化為:
SUMPRODUCT(C2:C6,D2:D6) = 1×5+2×4+3×3+4×2+5×1 = 35
![20240118_P6 20240118_P6](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P6.jpg?resize=404%2C311&ssl=1)
2.條件式加總
下表為一旅遊人數統計表,假設要計算出台北的總旅遊人數,那計算的條件為:「城市」欄位="台北"。
![20240118_P9 20240118_P9](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P9.jpg?resize=619%2C655&ssl=1)
此條件式套用在SUMPRODUCT函數中,其輸出的值為一True/False的陣列。(如下圖公式)
![20240118_P7 20240118_P7](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P7.jpg?resize=606%2C120&ssl=1)
因為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。(如下圖)
![20240118_P8 20240118_P8](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P8.jpg?resize=990%2C1024&ssl=1)
其實原因是SUMPRODUCT函數中的參數不接受True/False的形式,故有一個小技巧,可以先將此True/False的陣列轉換為1或0的陣列,可直接在其括號外面×1。(如下圖)
![20240118_P10 20240118_P10](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P10.jpg?resize=630%2C131&ssl=1)
就可以得到正確的結果喔。(如下圖)
![20240118_P11 20240118_P11](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P11.jpg?resize=590%2C261&ssl=1)
也可以利用此方法去進行多個條件的計算,如城市="台北"及季節="春"。(如下圖)
![20240118_P12 20240118_P12](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P12.jpg?resize=627%2C315&ssl=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 (如下圖)
![20240118_P13 20240118_P13](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P13.jpg?resize=1010%2C925&ssl=1)
這是舊版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。
![20240118_P14 20240118_P14](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P14.jpg?resize=1024%2C415&ssl=1)
是不是很有趣呢!
另外若各位想了解更多的SUMPRODUCT函數應用,可以參考Excel專家Alan Murray先生近期於LinkedIn上發表的一篇文章。
![20240118_P15 20240118_P15](https://i0.wp.com/fanlovexcel.com/wp-content/uploads/2024/01/20240118_P15.jpg?resize=553%2C188&ssl=1)
總結及補充:
上述舉的兩個範例皆為加總,其實SUMPRODUCT函數也可以用來計數,用法為SUMPRODUCT((範圍="條件")*1),其原理是將True/False的陣列轉化為1或0的陣列,再進行加總計數,於上方Alan Murray先生的文章中有相關的範例可參考喔。
雖然在動態陣列公式功能出現以後,SUMPRODUCT函數的使用頻率可能會逐漸降低,甚至慢慢被人們遺忘,但SUMPRODUCT函數絕對是在舊Excel時代中無疑是扮演最重要的角色之一,仍是Excel用戶相當值得花時間了解、學習的一個函數