學不完．教不停．用不盡

在教學經驗裡，很多人對於 Excel 裡的函數：ROW 和 ROWS 是理解其意義的，但是不知道要用在那裡，以為實用性不高。

(1) ROW函數：傳回儲存格的列號

(2) ROWS函數：傳回儲存格範圍的列數

通常會用來依其傳回值(一個數值)做數列、判斷第幾個或是共有幾個。

參考下圖，以 ROW 函數和 ROWS 函數運用來計算各個公里的速度。

1. 使用 ROW 函數

儲存格D5：=ROW(2:2)*B5-SUM($D$4:D4)

複製儲存格D5，貼至儲存格D5:D8。

ROW(2:2) 會傳回2(第2列)，當公式往下複製時會產生 ROW(2:2)=2→ROW(3:3)=3→ROW(4:4)=4→ …。在此為產生數列 2, 3, 4, …。

2. 使用 ROWS 函數

儲存格D14：=ROWS($B$13:B14)*B14-ROWS($B$13:B13)*B13

複製儲存格D14，貼至儲存格D14:D17。

ROWS($B$13:B14) 會傳回 2(2列)，當公式向下複製時會產生 ROWS($B$13:B14)=2→ROWS($B$13:B15)=3→ROWS($B$13:B16)=4→…。在此為產生數列 2, 3, 4, …。

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