教學過程中,常有人一直無法理解陣列公式的概念。本篇以 SUMPRODUCT 函數再來說明一下。以下圖為例,如果要計算所有品項的價錢總和,必須將每個品項的單價乘以數量再予以加總。

Excel-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者的總計。

Excel-SUMPRODUCT函數乘積和概念的應用

公式:=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 函數參考微軟提供的說明網頁:

學不完.教不停.用不盡文章列表

    文章標籤

    Excel SUMPRODUCT

    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()