当前位置: 首页 办公/科技 正文

Excel Address函数用法的7个实例,含返回四种引用类型的实例

admin |
132

在 Excel 中,Address函数用于返回由行号和列号指定的单元格地址,它可以指定单元格的引用类型、引用样式和要引用的工作表。引用类型共有四种,分别为:对列行都绝对引用,对列绝对引用、对行相对引用,对列相对引用、对行绝对引用,对列行都相对引用。

由于Address函数是以文本方式返回单元格的地址,因此它的返回结果还需要用Indirect函数把文本转为对单元格的引用;然后再与 OffSet、Match、Sum、Row 和 Column 等函数组合实现具体的功能。例如 Indirect + Address + Row + Column 实现提取由指定行开始的数据、OffSet + Indirect + Address + Match 实现查找数据、Sum + OffSet + Indirect + Address 实现多表格求和。

一、Excel Address函数的语法

1、表达式:ADDRESS(Row_Num, Column_Num, [Abs_Num], [A1], [Sheet_Text])

中文表达式:ADDRESS(行号, 列号, [引用类型], [引用样式], [工作表名称])

2、说明:

A、Abs_Num 为可选项,如果省略或为 1,则返回“绝对值”;如果为 2,则返回“绝对行号、相对列标”;如果为 3,则返回“相对行号、绝对列标”;如果为 4,则返回“相对值”。

B、A1 为可选项,如果省略或为 True,则返回如 A1 样式引用;如果为 False,则返回如 R1C1 样式引用,R 表示行号,C 表示列号。

C、Sheet_Text 为可选项,如果省略,表示引用当前工作表;如果要引用外部工作表,只需写工作表名称,例如 Sheet2。

二、Excel Address函数的使用方法及实例

(一)省略可选参数的实例

1、双击 E2 单元格,把公式 =ADDRESS(2,3) 复制到 E2,按回车,返回对单元格的引用结果为 $C$2;操作过程步骤,如图1所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图1

2、公式说明:

公式 =ADDRESS(2,3) 中,2 为行号,表示第 2 行;3 为列号,表示第 3 列;公式省略了后三个参数,其中“引用类型”为公式的第三个参数,省略则返回绝对值,即对单元格的绝对引用,如返回结果 $C$2;“引用样式”为公式的第四个参数,省略表示以 A1 样式引用;“工作表名称”为公式的第五个参数,省略表示引用当前工作表的单元格;公式的意思是:以文本方式返回对当前工作表第 2 行第 3 列对应的单元格的绝对引用,且以 A1 样式引用返回。

(二)返回四种引用类型的实例

1、双击 A11 单元格,把公式 =ADDRESS(7,4,1) 复制到 A11,按回车,返回 $D$7;双击 B11,把同一公式粘贴到 B11,把 1 改为 2,按回车,返回 D$7;双击 C11,把同一公式复制到 C11,把 1 改为 3,按回车,返回 $D7;再双击 D11,把同一公式复制到 D11,把 1 改为 4,按回车,返回D7;操作过程步骤,如图2所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图1
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图2

2、公式说明:

A、公式 =ADDRESS(7,4,1) 的意思是:以文本返回第 7 行与第 4 列交叉的单元格引用,且以“绝对引用”返回,即返回 $D$7,$ 表示绝对引用,列前和行前都有 $,表示对列和行都绝对引用,即往下或往右拖时,D7 不会变为 D8、D9、…………,也不会变为 E7、F7、……。

B、公式 =ADDRESS(7,4,2) 的第三个参数“引用类型”为 2,表示以“绝对行号、相对列标(即对行绝对引用、对列相对引用)”且以文本返回对单元格的引用,即返回 D$7,往下拖时,D7 不会变为 D8、D9、……,但往右拖时,D7 会变为 E7、F7、……。

C、公式 =ADDRESS(7,4,3) 的第三个参数“引用类型”为 3,表示以“相对行号、绝对列标(即对行相对引用、对列绝对引用)”且以文本返回对单元格的引用,即返回 $D7,往下拖时,D7 会变为 D8、D9、……,但往右拖时,D7 不会变为 E7、F7、……。

D、公式 =ADDRESS(7,4,4) 的第三个参数“引用类型”为 4,表示以“相对值(即对行列都相对引用)”且以文本返回对单元格的引用,即返回 D7,往下拖时,D7 会变为 D8、D9、……,往右拖时,D7 也会变为 E7、F7、……。

(三)引用样式为 A1 和 R1C1 的实例

1、双击 E2 单元格,把公式 =ADDRESS(6,1,,1) 复制到 E2,按回车,返回 $A$6;双击 E3,把同一公式复制到 E3,把后面的 1 改为 0,按回车,返回 R6C1;操作过程步骤,如图3所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图2
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图3

2、公式说明:

A、公式 =ADDRESS(6,1,,1) 是以文本返回第 6 行与第 1 列对应单元格的引用,第三个参数省略,默认以绝对引用返回,第四个参数为 1(即 True),则以 A1 样式返回,因此返回 $A$6。

B、公式 =ADDRESS(6,1,,0) 同样以绝对引用方式且以文本返回第 6 行与第 1 列对应单元格的引用,第四个参数为 0(即 False),表示以 R1C1 样式返回,因此返回 R6C1。

(四)引用外部工作表单元格的实例

1、引用同一文档的另一个工作表;当前工作表为“11月”,假如要引用另一个工作表“12月”的 D5;双击 E2,把公式 =ADDRESS(5,4,,,”12月”) 复制到 E2,按回车,返回 ’12月’!$D$5。

2、引用不同文档的其中一个工作表;假如要引用“excel教程”文档中的“学生表”中的 D5;双击 E3 单元格,把公式 =ADDRESS(5,4,,,”[excel教程.xlsx]学生表”) 复制到 E3,按回车,返回 [excel教程.xlsx]学生表!$D$5;操作过程步骤,如图4所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图3
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图4

3、公式说明:

A、公式 =ADDRESS(5,4,,,”12月”) 是在工作表“11月”以文本返回另一个工作表“12月”的 D5 的绝对引用,由于两工作表在同一文档,因此不需要在工作表前加文档名称。

B、公式 =ADDRESS(5,4,,,”[excel教程.xlsx]学生表”) 是以文本返回另一个文档(即“excel教程.xlsx”)中“学生表”的 D5 的绝对引用,由于公式所在文档与引用单元格的文档不是同一文档,因此“引用名称”要有文档名和工作表名且文档名要有扩展名,否则找不到文档,如公式中的 “[excel教程.xlsx]学生表”;如果要查看引用单元格中的值还需要加Indirect函数,则公式变为 =INDIRECT(ADDRESS(5,4,,,”[excel教程.xlsx]学生表”));另外,引用的文档要打开,公式才能返回正确的值,否则返回引用错误 #REF!。

三、Excel Address函数的应用实例

(一)Indirect + Address + Row + Column 组合实现提取由指定行开始的数据

1、假如要从第三行开始提取任意行数据。双击 A9 单元格,把公式 =INDIRECT(ADDRESS(ROW($A3),COLUMN())) 复制到 A9,按回车,返回“青皮西瓜”;选中 A9,把鼠标移到 A9 右下角的单元格填充柄上,鼠标变为黑色加号后,按住左键,往右拖,一直拖到 D9,则提取了表格第三行数据;再往下拖,经过多少行则提取多少行数据,如往下拖四行,则又提取表格四行数据;操作过程步骤,如图5所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图4
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图5

2、公式 =INDIRECT(ADDRESS(ROW($A3),COLUMN())) 说明:

A、$A3 表示对列绝对引用对行相对引用;往下拖时,A3 会变 A4、A5 等,往右拖时,A3 不会变 B3、C3 等;因为这里只要求往下拖时,行号增长、列号不变。

B、ROW($A3) 返回 A3 的行号 3,COLUMN() 返回公式所在列的列号 1,则 ADDRESS(ROW($A3),COLUMN()) 变为 ADDRESS(3,1),它以文本形式返回第三行与第一列对应的单元格的绝对引用,即 “$A$3″。

C、则公式变为 =INDIRECT(“$A$3”),最后用 Indirect 函数返回对文本 “$A$3” 的引用,即返回 A3 中的内容。

D、当公式在 B10 时,$A3 变 $A4,ROW($A4) 返回 A4 的行号 4;COLUMN() 返回 B10 的列号 2;则公式变为 =INDIRECT(ADDRESS(4,2)),进一步计算变为 =INDIRECT(“$B$4”),最后返回 B4 的内容“浆果”。

(二)OffSet + Indirect + Address + Match 组合实现查找数据

1、假如要根据姓名查找职务。双击 B12 单元格,把公式 =OFFSET(INDIRECT(ADDRESS(MATCH(A12,A1:A9,),1)),,2) 复制到 B12,按回车,返回王青瑗的职务“员工”;双击 A12,把王青瑗改为张秀玲,单击 B12,则返回张秀玲的职务为“经理”;操作过程步骤,如图6所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图5
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图6

2、公式 =OFFSET(INDIRECT(ADDRESS(MATCH(A12,A1:A9,),1)),,2) 说明:

A、MATCH(A12,A1:A9,) 用于在 A1:A9 中查找 A12,它省略了最后一个参数,默认取 0,即精确匹配,最后返回的结果为 6。

B、则 ADDRESS(MATCH(A12,A1:A9,),1) 变为 ADDRESS(6,1),由于省略了后面三个参数,它们全取默认值,因此以绝对引用返回第六行与第一列对应的单元格地址,即返回 “$A$6″。

C、由于Address函数返回是文本,所以还要用Indirect函数把文本转为引用,即 INDIRECT(“$A$6”) 返回 $A$6。

D、则公式变为 =OFFSET($A$6,,2),接着,以 A6 为基准,返回 A6 右边与它相距两列的单元格引用,即返回对 C6 的引用,也就是返回C6 的内容“员工”。OffSet函数省略了第二个参数行号,默认取 0;还省略了第四五个参数高度和宽度,默认取与 A6 一样的高度和宽度。

(三)Sum + OffSet + Indirect + Address 组合实现多表格求和

1、假如要汇总 10 到 12 月的服装销量,每个月的销量在一个表格中,表格名称以月份命名。当前工作表为“10月”,选择“销量汇总”工作表,双击 B2 单元格,把公式 =SUM(OFFSET(INDIRECT(ADDRESS(1,4,,,A2&”月”)),1,,6)) 复制到 B2,按回车,返回求和结果 4013;选中B2,再用双击 B2 单元格填充柄的方法求11月和12月销量之和;操作过程步骤,如图7所示:

Excel Address函数用法的7个实例,含返回四种引用类型的实例插图6
Excel Address函数用法的7个实例,含返回四种引用类型的实例

图7

2、公式 =SUM(OFFSET(INDIRECT(ADDRESS(1,4,,,A2&”月”)),1,,6)) 说明:

A、A2&”月” 用于组合工作表名称,A2 中的 10 与“月”用 & 连接组合成工作表“10月”的名称。

B、则 ADDRESS(1,4,,,A2&”月”) 变为 ADDRESS(1,4,,,”10月”),接着以绝对引用方式返回工作表“10月”中第一行与第四列对应的单元格地址,即返回文本 “’10月’!$D$1″。

声明:原创文章请勿转载,如需转载请注明出处!