excel表格如何拉长
5949
2022-06-08
在 Excel 中,Row函数用于返回引用单元格或单元格区域的行号,如果省略参数,它将返回公式所在行的行号;Rows函数用于返回数组或引用单元格区域的行数,它只有一个参数且不能省略。
Row函数常与 Index、Small、Match、Indirect、OffSet、If、SumProduct、CountA 等函数组合使用,例如 Row + Indirect + CountA 组合实现以数组形式返回所有不为空的单元格,SumProduct + Row + Mod 组合实现求偶数行或奇数行之和。另外,如果要返回一列的引用,可以用 Row(A:A);返回一行的引用,可以用 Row(1:1)。
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 为数组或对单元格区域的引用,且不能省略。
(一)返回公式所在行行号的实例
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(:),即在列号或行号前加绝对引用符 $,把相对引用变为绝对引用。
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(:);操作过程步骤,如图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" 的引用,即返回 :。
B、则公式变为 =ROW(:),最后以数组形式返回 1 到 7 的数组,也需要把 =ROW(:) 放到 Index 函数中才能返回数组。
(一)参数为数组的实例
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 行。
(一)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 为起始行。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。