教學過程中，常有人一直無法理解陣列公式的概念。本篇以 SUMPRODUCT 函數再來說明一下。以下圖為例，如果要計算所有品項的價錢總和，必須將每個品項的單價乘以數量再予以加總。
以下數種做法都在執行同一個工作，結果是相同的。
(1) =B4*C4+B5*C5+B6*C6+B7*C7+B8*C8
(2) =SUM(B4*C4,B5*C5,B6*C6,B7*C7,B8*C8)
(3) =SUM({100;200;150;250;100}*{3;4;2;3;4})
注意公式中是使用「;」。
(4) {=SUM(B4:B8*C4:C8)}
以前的 Excel 在陣列公式輸入後要按 Ctrl+Shift+Enter 鍵，讓 Excel 知道你要抧寫陣列公式，Excel 會自動加上「｛｝」。
在此，儲存格B4:B8為一組陣列，儲存格C4:C8為一組陣列，兩組陣列執行「*」運算。
(5) =SUM(B4:B8*C4:C8)
2021版之後直接按 Enter 鍵，且不會顯示「｛｝」。
(6) =SUMPRODUCT(B4:B8*C4:C8)
第(1)式至第(5)式就是在執行「乘積和」運算，也就是先乘後加。
在 SUMPRODUCT 函數裡即會以陣列公式來運算。在此，儲存格B4:B8為一組陣列，儲存格C4:C8為一組陣列，兩組陣列執行「*」運算。
利用 SUMPRODUCT 函數顯的公式簡單並且易於撰寫。
接著，來看 SUMPRODUCT 函數的進一步運用。要來計算小計超過500者的總計。
公式：=SUMPRODUCT((B4:B8*C4:C8>500)*(B4:B8*C4:C8))
B4:B8*C4:C8>500 是條件，會傳回 TRUE／FALSE 函數。
公式：=SUMPRODUCT({FALSE;TRUE;FALSE;TRUE;FALSE}*(B4:B8*C4:C8))
其中第一個「*」，相當於執行邏輯 AND 運算，會將 TRUE/FALSE 陣列會轉為 1/0 陣列。
公式：=SUMPRODUCT({0;1;0;1;0}*(B4:B8*C4:C8))
結果為 800+750 = 1550。
【參考資料】
SUMPRODUCT 函數參考微軟提供的說明網頁：SUMPRODUCT 函數
留言列表