Excel函数Indirect精讲和应用

    Excel 中,Indirect函数用于用于返回由文本字符串指定的单元格引用。它的引用类型有两种,一种为如 A1 样式的引用,另一种为如R1C1 样式的引用,它们由逻辑值真和假设置。它引用的单元格可以是同表的,也可以是同文档或跨文档的,后两种情况为跨表引用。

    Indirect函数常与 Address、Row、Column、Match 函数组合使用,例如 Indirect + Row + Address + Column 函数组合实现将多个表格中指定行数据提取到一个表格,Indirect + Match + Column 函数组合实现按条件批量提取数据。

一、Excel Indirect函数的语法

1、表达式:INDIRECT(Ref_Text, [A1])
中文表达式:INDIRECT(引用文本, [引用类型])
2、说明:
A、Ref_Text 既可以是内部引用(在本工作簿中引用,即在同一 Sheet 中引用或在一个 Sheet 引用另一个 Sheet)又可以是外部引用(对另一工作簿的引用,即在一个文档引用另一个文档);值得注意的是,外部引用时,被引用的工作簿必须打开,否则Indirect函数将返回引用错误 #Ref!。另外,Excel Web App 不支持外部引用。
B、如果 Ref_Text 引用的单元格区域超出 Excel 允许的最大行数 1048576 或最大列数 16384(XFD),Indirect函数也返回引用错误#REF!;但 Excel 早期版本会忽略这一限制并且返回一个值。
C、A1 有两个可选值,分别为 True(或省略)和 False;若为 True,则解释为 A1 样式的引用;若为 False,则解释为 R1C1 样式的引用,R 表示“行”,C 表示“列”,R1C1 表示对第一行与第一列对应单元格的引用,即 A1。无论哪种引用方式,如果引用单元格不存在,Indirect函数都将返回错误。

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

(一)引用类型 A1 为 True(或省略)的实例
1、选中 A9 单元格,输入公式 =Indirect(B5),按回车,返回引用错误 #REF!;把公式改为 =INDIRECT(“B”&5) ,按回车,返回 B5 中的姓名“佛山”;在 5 后输入“,true”,=INDIRECT(“B”&5,TRUE),按回车,也返回“佛山”;操作过程步骤,如图1所示:
Excel函数Indirect精讲和应用

图1
2、公式说明:
A、公式 =Indirect(B5) 之所以会返回引用错误,是因为Indirect函数的第一个参数必须为文本,而 B5 是对单元格的引用。
B、公式 =INDIRECT(“B”&5) 中,”B”&5 为文本,意思是用 & 把字符 B 与 5 连接起来,即 “B5″;由于 “B5” 为文本是对 B5 单元格内容的引用,因此Indirect函数能返回正确的值。
C、公式 =INDIRECT(“B”&5) 与 =INDIRECT(“B”&5,true) 返回一样的值,说明Indirect函数省略第二个参数与把它设置为 True 一样;另外,True 也可以用 1 代替,即公式  =INDIRECT(“B”&5,true) 可改为 =INDIRECT(“B”&5,1)。
(二)引用类型 A1 为 False 的实例
1、双击 B9 单元格,把公式 =INDIRECT(“R”&5&”C”&2,FALSE) 复制到 B9,按回车,返回 B5 中的姓名“佛山”;双击 B9,把 FALSE 改为 0,按回车,也返回一样的结果;操作过程步骤,如图2所示:
Excel函数Indirect精讲和应用
图2
2、公式说明:
公式中 “R”&5 表示第五行,”C”&2 表示第二列,”R”&5&”C”&2 连接起来为 “R5C2″,表示第五行与第二列对应的单元格;FALSE 表示以R1C1 样式引用,0 可以替代 FALSE;公式的意思是:返回第 5 行与第 2 列对应的单元格引用,即返回 B5 中的姓名。
(三)内部引用(同表与跨表引用)的实例
1、同表引用;上图中公式=INDIRECT(“B”&5) 就是同表引用B5单元格的内容。跨表引用,假如在“首页”表中A1单元格,引用“河南”表中的A2单元格;双击 A1,把公式 =INDIRECT(“河南!A”&2) 复制到 A1,按回车,返回“河南”表中的 A2 中的内容 “郑州”;操作过程步骤,如图3所示:
Excel函数Indirect精讲和应用
图3
2、公式说明:
同表引用与跨表引用的区别为:跨表引用只需在要引用的单元格前加工作表名称和半角感叹号(即 !),如演示中的 “河南” 为工作表名称,它的后面为感叹号(!),A2 为引用单元格。
(四)外部引用(跨文档引用)的实例
1、假如要在文档“服装销量1.xlsx”中的“1月”工作表引用“服装销量2.xlsx”中的同一工作表中的“黑色T恤”销量。双击 E6 单元格,把公式 =INDIRECT(“[服装销量2.xlsx]1月!D6”) 复制到 E6,按回车,返回 750;操作过程步骤,如图4所示:
Excel函数Indirect精讲和应用
图4
2、公式说明:
公式 =INDIRECT(“[服装销量2.xlsx]1月!D6”) 中,[服装销量2.xlsx] 为文档名称,注意要加扩展名“.xlsx”;“1月”为工作表名,D6 为引用单元格,工作表名与引用单元格之间同样要加感叹号(!)。
(五)引用单元格为 Excel 允许的最大行数 1048576 或列数 16384(XFD)的实例
1、双击 C2 单元格,把公式 =INDIRECT(“A”&1048576) 复制到 C2,按回车,返回 Excel 允许的最大行数 1048576;双击 C2,把 后面的6 改为 7,按回车,返回引用错误 #REF!。双击 C3,把公式 =INDIRECT(“XFD”&1) 复制到 C3,按回车,返回 Excel 允许的最大列数 16384;双击 C2,把 XFD 改为 XFE,按回车,也返回引用错误 #REF!;按 Ctrl + 向右方向键,移到最后一列,XFD 列后已无列。操作过程步骤,如图5所示:
Excel函数Indirect精讲和应用
图5
2、公式说明:
从演示可知,当把最后一行 1048576 改为 1048577,公式返回引用错误,说明 1048576 已是最后一行;同理把最后一列 XFD 改为 XFE,公式也返回引用错误,说明 XFD 也是最后一列。

三、Excel Indirect函数的应用实例

(一)Indirect + Row + Address + Column 函数组合实现将多个表格中指定行的数据提取到一个表格
1、假如要把服装销量表“1月、2月和3月”中的第四行提取到总表。当前工作表为“总表”,双击 A2 单元格,把公式 =INDIRECT(ROW(A1)&”月!”&ADDRESS(ROW($A$4),COLUMN(B$1))) 复制到 A2,按回车,返回工作表“1月”的第四行第二列的服装名称“白色修身T恤”;选中A2,把鼠标移到 A2 右下角的单元格填充柄上,鼠标变为黑色加号后,按住左键并往右拖,一直拖到 C2(提取数据表格的最后一列),则提取了表格“1月”的第四行数据;再往下拖(要提取数据的表格还剩几个拖几行),则又提取工作表“2月和3月”的第四行数据;切换到被提取数据的工作表,可以看到提取的数据正确;操作过程步骤,如图6所示:
Excel函数Indirect精讲和应用
图6
2、公式 =INDIRECT(ROW(A1)&”月!”&ADDRESS(ROW($A$4),COLUMN(B$1))) 说明:
A、ROW(A1) 用于返回 A1 的行号 1,然后与“月”字用 & 连接合成工作表名称“1月”;当往下拖时,A1 会变为 A2,A3 等,Row(A2) 返回行号 2,再与“月”字合成工作表“2月”,其它的以此类推。
B、$A$4 表示绝对引用 A4,往下拖时,A4 不会变 A5、A6 等;往右拖时,A4 不会变 B4、C4 等;这样确保 ROW($A$4) 始终返回第 4 行。
C、COLUMN(B$1) 用于返回 B1 的列号 2,B$1 为相对引用列绝对引用行,以确保往右拖时,B1 变为 C1、D1 等,从而达到取不同列的数据的目的。
D、则 ADDRESS(ROW($A$4),COLUMN(B$1)) 变为 ADDRESS(4,2),接着以文本方式且以绝对引用返回第四行与第二列对应的单元格,即 “$B$4″,因为 Address 函数省略了第三个参数“引用类型”,默认以绝对引用返回。
E、则公式变为 =INDIRECT(1&”月!”&”$B$4″),进一步计算,把字符串连接起来,则公式变为 =INDIRECT(“1月!$B$4”),最后返回对工作表“1月”中 B4 单元的引用,即返回 B4 中的服装名称。
(二)Indirect + Match + Column 函数组合实现按条件批量提取数据
1、假如要从一个表格中提取任意指定几个月份的销量,每个月的销量占一行。双击 B13 单元格,把公式 =INDIRECT(“r”&MATCH($A13,$A$1:$A$10,)&”c”&COLUMN(),0) 复制到 B13,按回车,返回“T恤”“1月”的销量 567;选中 B13,用往右拖的方法返回“1月”剩余的数据,再用往下拖的方法提取“4月、5月和8月”的销量;操作过程步骤,如图7所示:
Excel函数Indirect精讲和应用
图7
2、公式 =INDIRECT(“r”&MATCH($A13,$A$1:$A$10,)&”c”&COLUMN(),0) 说明:
A、MATCH($A13,$A$1:$A$10,) 用于返回 A13(即“1月”)在 A1:A10 中位置,结果为 2;$A13 之所以用列绝对引用行相对引用,是因为往右拖时要求它不变为 B13、往下拖时要变为 A14,这样实现取到“A14 中的4月、A15 中的5月”等等;$A$1:$A$10 之所以列行都用绝对引用,是因为往下拖时始终要确保在 A1:A10 中查找,因为用绝对引用,往下拖,A1 不为会 A2、A10 也不会变 A11。另外,Match函数省略了最一个参数,默认取 0,表示精确匹配。
B、COLUMN() 用于返回公式所在列号,当公式在 B13 时,它返回 2;当公式有 C13 时,它返回 3,其它的以此类推。
C、则公式变为 =INDIRECT(“r”&2&”c”&2,0),进一步计算把各字符连接起来,公式变为 =INDIRECT(“r2c2”,0),公式中,r 表示行号,c 表示列号,0 表示以 r1c1 样式引用,最后公式返回第 2 行与第 2 列对应的单元格引用,即返回 B2 中的数值 567。
(三)利用INDIRECT制作二级下拉菜单
如下图所示
Excel函数Indirect精讲和应用
1、先选中数据区域,依次点击“公式”、“根据所选内容创建”,弹出对话框后勾选“首行”,然后按确定。
Excel函数Indirect精讲和应用
2、选中A2单元格,依次点击“数据”、“数据验证”、“数据验证”。
Excel函数Indirect精讲和应用
3、弹出对话框,下拉后选择“序列”,来源填入“=$E$1:$H$1”
Excel函数Indirect精讲和应用
4、选中B2单元格,按照2种的方法选择数据验证,弹出对话框后,选择“序列”,将公式”=INDIRECT($A$2)”粘贴进去,点击确定。
Excel函数Indirect精讲和应用

分享到:

评论抢沙发

评论前必须登录!