學不完．教不停．用不盡

教學過程中，常有人一直無法理解陣列公式的概念。本篇以 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 函數