Indirect函数：批量引用多个工作表的数据，就靠它

👍

近期推送的文章

·  正  ·  文  ·  来  ·  啦  ·

读者群常有朋友问，如何批量引用多个工作表的数据。

如果已有工作表的名称，那么，使用Indirect函数就可轻松批量引用。

如果没有工作表的名称，可以使用GET.WORKBOOK来自定义名称，自动提取工作表名称，再使用Indirect函数批量引用。具体参见《偷懒的技术2：财务Excel表格轻松做》第三章第二节“开发项目信息登记表：批量引用多表数据的经典案例”。

本文摘录《偷懒的技术2：财务Excel表格轻松做》第一章第六节中Indirect函数的内容，介绍Indirect函数如何点石成金，将文本变成引用。

1、批量引用不同工作表的同一单元格
前面我们介绍了使用正确的单元格引用让公式能批量应用到其他单元格、使用【表格】功能自动包含新增数据，做到了这两点，可以让公式具有良好的扩展性。但这只能处理一个工作表的数据，要处理多个工作表的数据时就无能为力了。比如图  1‑32各公司的收入统计表分工作表分别列示，表格格式和布局完全一样：
图  1‑32  各公司收入统计表
现要在“查询表”分别引用各公司一季度的合计数，用简单的单元格引用各表相应单元格，其公式如下：
图  1‑33  引用各公司一季度合计数
B5单元格要引用'逸凡本部’工作表的B9单元格，公式为：
=逸凡本部!B9
B6单元格要引用“北京公司”工作表的B9单元格，公式为：
=北京公司!B9
上面的公式变化部分就是工作表的名称，而A列相应单元格的内容恰好是工作表的名称，如果我们用A列单元格的内容替代公式中工作表的名称后还能否引用相应工作表的内容呢？将B5单元格的公式修改为：
=A5&"!B9"
公式解释：上面的公式是将A5单元格的内容与字符“!B9“用&符号连接起来，文本字符串要用英文双引号括起来。
输入完前面的公式后，公式结果如图1-34：
图  1‑34  类似单元格引用的字符串
它显示的是原单元格引用公式的文本字符串（工作表名和单元格，中间用!分隔），而不是这个文本字符串代表的单元格的值。
图  1‑35  单元格引用示意图
那如何将公式计算结果的文本字符串点石成金转化为真正的引用呢？我们只需在上面公式最外层套一个INDIRECT函数即可，B5单元格完整的公式为：
=INDIRECT(A5&"!B9")
然后将B5单元格的公式拖动填充到其他单元格，即可批量引用A列单元格中指定工作表的B9单元格的值：
图  1‑36  使用INDIRECT函数
需要强调的是，如果A列的公司名称与工作表的名称不一致，那么此公式就会出错，计算结果为“#REF!“，这是因为INDIRECT函数其作用就是将文本字符串的单元格地址变为真正的引用。如果A列的公司名称和工作表名称不一致，公式肯定出错，这和邮寄地址写得不正确，快递员拿着包裹没法投递是一个道理。
INDIRECT函数语法格式为：
=INDIRECT（文本字符串的单元格地址，引用类型）
此函数的第二参数是用于表示使用A1引用样式还是R1C1样式，如果第二参数为 TRUE 或省略，则表示使用A1样式的引用。
【提示】
R1C1样式：R表示行，C表示列，A5单元格用R1C1样式表示R5C1，C4用R1C1样式表示就是R4C3。
2、批量引用不同工作表的不同单元格
在INDIRECT中使用A1样式会有一定的局限性，比如要求在图 1‑37中的A2单元格输入指定的工作表名，就能自动查询各表格各季度的收入数据。为了让公式更灵活，希望在B5单元格输入公式，将此公式拖动填充应用到其他单元格。
图  1‑37  查询各公司各季度的收入
我们在'查询表2’工作表的B5单元格输入公式：
=INDIRECT($A$2&"!B5")
这个公式填充到其他单元格时，其引用相应工作表的行列不会变化，因而其结果都是一样的。要让行和列随着单元格变化，这时就需要使用R1C1样式：“逸凡本部!B5”单元格使用R1C1样式就是“逸凡本部!R5C2”，因而“查询表2”B5单元格的的公式使用R1C1样式就是：
=INDIRECT($A$2&"!R5C2",0)
上面公式的第二参数为0表示使用R1C1样式。为了让公式能拖动填充，将其完善为：
=INDIRECT($A$2&"!R"&ROW()&"C"&COLUMN(),0)
第一参数生成“逸凡本部!R5C2”，第二参数为0表示使用R1C1样式。
这样就可以将此公式拖动填充到其他单元格了。
图  1‑38  使用R1C1样式
赞 (0)

相关推荐