贊助廠商

Excel-ROW和ROWS函數的運用

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

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

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

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

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

Excel-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, …。

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

文章標籤

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

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

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

文章標籤

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

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼