Excel Row函數和Rows函數使用方法的8個實例,含Row、Row與求奇偶行之和

在 Excel 中,Row函數用于返回引用單元格或單元格區域的行號,如果省略參數,它將返回公式所在行的行號;Rows函數用于返回數組或引用單元格區域的行數,它只有一個參數且不能省略。

Row函數常與 Index、Small、Match、Indirect、OffSet、If、SumProduct、CountA 等函數組合使用,例如 Row + Indirect + CountA 組合實現以數組形式返回所有不為空的單元格,SumProduct + Row + Mod 組合實現求偶數行或奇數行之和。另外,如果要返回一列的引用,可以用 Row(A:A);返回一行的引用,可以用 Row(1:1)。

一、Excel Row函數Rows函數的語法

1、表達式:ROW([reference])

中文表達式:ROW([對單元格或單元格區域的引用])

2、表達式:ROWS(Array)

中文表達式:ROWS(數組或對單元格區域的引用)

3、說明:

A、Row函數用于返回引用單元格或單元格區域的行號。Reference 為可選項,如果省略,則返回 Row 所在行的行號;如果 Reference 為對一個單元格區域的垂直引用(如 A1:A9),Row函數將以數組形式返回所有引用單元格的行號,按 F9 或把 Row(A1:A9)作為 If 的條件都可以看到,具體見下文的實例;Reference 不能一次引用多個區域。

B、Rows函數用于返回引用的行數。Array 為數組或對單元格區域的引用,且不能省略。

二、Excel Row函數的使用方法及實例

(一)返回公式所在行行號的實例

1、雙擊 A1 單元格,輸入公式 =ROW(),按回車,返回 1;選中 A1,把鼠標移到 A1 右下角的單元格填充柄上,鼠標變為加號后,按住左鍵并往下拖,則所經過單元格都返回相應行的行號;操作過程步驟,如圖1所示:

圖1

2、說明:公式 =ROW() 省略了參數 Reference,則返回公式所在行的行號。

(二)返回指定行的行號實例

雙擊 B1 單元格,輸入公式 =ROW(A5),按回車,返回 A5 的行號 5;操作過程步驟,如圖2所示:

圖2

(三)對單元格區域的垂直引用返回數組的實例

1、假如要返回 A1:A5 的所有行號。雙擊 B1 單元格,把公式 =IF(ROW(A1:A5)>=3,A1:A5,0) 復制到 B1,按 Ctrl + Shift + 回車,返回 0;按住 Alt 鍵,分別按一次 M 和 V,打開“公式求值”窗口,按回車求值,則 ROW(A1:A5) 返回 1 到 5 的數組;操作過程步驟,如圖3所示:

圖2

2、公式說明:

A、公式 =IF(ROW(A1:A5)>=3,A1:A5,0) 為數組公式,因此要按 Ctrl + Shift + 回車。

B、ROW(A1:A5)>=3 為 If 的條件,ROW(A1:A5) 以數組形式返回 A1:A5 的行號,即 {1;2;3;4;5},執行時會取出數組中的所有元素與 3 比較,如果大于等于 3,返回 True,否則返回 False;最后返回 {FALSE;FALSE;TRUE;TRUE;TRUE};由于要求數組中的所有元素都為真才返回A1:A5 中的數值,因此返回 0。

(四)引用一列 Row(A:A) 與一行 Row(1:1) 的實例

1、引用一列;選中 B1 單元格,輸入公式 =ROW(,單擊第一列列號 A,則 A:A 自動輸入,接著輸入右括號 (,按回車,返回 1。引用一行;當前選中 B2,輸入 =ROW(,單擊第一行行號 1,則 1:1 自動輸入,再輸入右括號 ),按回車,返回 1。操作過程步驟,如圖4所示:

圖4

2、公式說明:

A、輸入 =ROW( 后,要引用那一列或那一行,單擊相應的列號或行號即可,另外,也可以直接輸入列號或行號;如引用第二列,公式為 =ROW(B:B);引用第二行,公式為 =ROW(2:2)。

B、另外,公式 =ROW(A:A) 也可以改為 =ROW($A:$A),公式 =ROW(1:1) 同樣可以改為 =ROW($1:$1),即在列號或行號前加絕對引用符 $,把相對引用變為絕對引用。

3、如果要返回數組,也需要把 =ROW(A:A) 或 =ROW(1:1) 放到一個參數為引用的函數中。例如把 =ROW(A:A) 放到 Index函數中,則公式變為 =INDEX(ROW(A:A),2),把該公式復制到 B1 單元格,按 Ctrl + Shift + 回車,返回 2;按住 Alt 鍵,分別按 M 和 V,打開“公式求值”窗口,按回車求值,則返回 A 列的所有行號;操作過程步驟,如圖5所示:

圖5

公式 =INDEX(ROW(A:A),2) 用于返回引用單元格區域或數組中由行號和列號指定單元格的引用,ROW(A:A) 為引用單元格區域,2 為行號,公式省略了列號,公式的意思是:返回 A 列第二行的行號,因此返回 2。

(五)在參數中使用函數的實例(Row + Indirect + CountA)

1、雙擊 E2 單元格,把公式 =ROW(INDIRECT("1:"&COUNTA(D:D))) 復制到 E2,按回車,返回 1;選中 E2,按住 Alt 鍵,依次按 M 和 V,打開“公式求值”窗口,按三次,返回 ROW($1:$7);操作過程步驟,如圖6所示:

圖6

2、公式=ROW(INDIRECT("1:"&COUNTA(D:D)))說明:

A、COUNTA(D:D) 用于統計 D 列非空單元格數,它返回 7;則 INDIRECT("1:"&COUNTA(D:D)) 變為 INDIRECT("1:"&7),進一步計算變為INDIRECT("1:7"),接著用 Indirect 函數返回對文本 "1:7" 的引用,即返回 $1:$7。

B、則公式變為 =ROW($1:$7),最后以數組形式返回 1 到 7 的數組,也需要把 =ROW($1:$7) 放到 Index 函數中才能返回數組。

三、Excel Rows函數的使用方法及實例

(一)參數為數組的實例

1、雙擊 A1 單元格,把公式 =ROWS({5,21,8,13,4,59}) 復制到 A1,按回車,返回 1;再次雙擊 A1,把公式中第二和第四個逗號(,)改為分號(;),按回車,返回 3;操作過程步驟,如圖7所示:

圖7

2、公式說明:

公式 =ROWS({5,21,8,13,4,59}) 中的數組只有一行,而公式 =ROWS({5,21;8,13;4,59}) 中的數組有三行,數組行與行之間用分號分隔、元素與元素之間用逗號分隔;第一個公式只有逗號沒有分號,因此只有一行;第二個公式有兩個分號,因此有三行。

(二)參數為引用單元格區域的實例

1、雙擊 B1,把公式 =ROWS(A1:A5) 復制到 B1,按回車,返回 5;在 B2 輸入 =ROWS(,單擊第一列列號 A,則自動輸入 A:A,接著輸入右括號 ),按回車,返回 1048576;操作過程步驟,如圖8所示:

圖8

2、公式說明:

公式 =ROWS(A1:A5) 返回 A1:A5 的行數,A1 至 A5 共 5 行,因此返回 5;公式 =ROWS(A:A) 返回 A 列的行數,A 列共 1048576 行。

四、Excel ROW函數的擴展應用實例

(一)SumProduct + Row + Mod 組合實現求偶數行或奇數行之和

1、假如要分別求銷量的偶數行和奇數行之和。雙擊 D8 單元格,把公式 =SUMPRODUCT((MOD(ROW($2:$7),2)=ROW(A1))*D$2:D$7) 復制到D8,按回車,返回 7750;再雙擊 D9,把公式 =SUMPRODUCT((MOD(ROW($2:$7),2)=0)*D$2:D$7) 復制到 D9,按回車,返回 4993;操作過程步驟,如圖9所示:

圖9

2、求偶數行公式=SUMPRODUCT((MOD(ROW($2:$7),2)=ROW(A1))*D$2:D$7)說明:

A、ROW($2:$7) 以數組形式返回 2 到 7 行的行號,則 MOD(ROW($2:$7),2) 變為 MOD({2;3;4;5;6;7},2),接著從數組中取出第一個元素2,然后與 2 取模,結果為 0;再從數組中取出第二個元素 3,然后與 3 取模,結果為 1;其它的以此類推,最后返回 {0;1;0;1;0;1}。

B、ROW(A1) 返回 A1 的行號 1;則 MOD(ROW($2:$7),2)=ROW(A1) 變為 {0;1;0;1;0;1}={1},進一步計算從數組中取出每一個元素與 1 比較,如果相等,返回 True,否則返回 False,最后返回 {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}。

C、D$2:D$7 以數組形式返回 D2 至 D7 中的數值,即返回 {0;3500;0;2050;0;2200}。

D、則公式變為 =SUMPRODUCT({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}*{0;3500;0;2050;0;2200}),進一步計算,把兩個數組對應元素相乘,然后把所有乘積相加,相乘時,True 轉為 1、False 轉為 0,最后返回求和結果 7750。

提示:以上公式是以求和區域 D2:D7 的起始行 D2 算第一行,如果以表格的第一行算起始行,則為求奇數行之和。

3、求奇數行公式=SUMPRODUCT((MOD(ROW($2:$7),2)=0)*D$2:D$7)說明:

求奇數行公式與求偶數行公式一樣,只是把 MOD(ROW($2:$7),2)=ROW(A1) 改為 MOD(ROW($2:$7),2)=0,因為模(即余數)為 0 的是奇數行,這里的求和區域 D2:D7 也是以 D2 為起始行。