说到在Excel中跨表提取数据,很多小伙伴首先想到的函数一定是VLOOKUP或HLOOKUP吧。的确,这两个函数拥有超强的查找引用技能,能够在指定范围内快速查找到与指定数据匹配的信息。但是,当在多个工作表中批量跨表提取数据时,如果再运用这两个函数就会有些力不从心了。
例如,将下面工作薄中 9~12 月“库存汇总表”每个字段的合计数提取到“月度库存总额汇总表”中,并分月列示。提取 9 月数据,B3 单元格公式设置为“=HLOOKUP(B$2,'2019 年9 月 '!$B$2:$J$23,22,0)”,再向右填充公式。但是,提取 10 月数据时,就必须将第 2 个参数中的月份批量替换为“2019 年 10 月”,以此类推,如下图所示。
对此,Excel函数高手都会在VLOOKUP或HLOOKUP函数中嵌套另一个查找引用类函数INDIRECT,无需再手工修改工作表名称,即可一秒批量跨表提取数据了。学会后,您也是同事眼中的函数高手。
下面首先看看INDIRECT函数的语法和参数说明。
标准语法:INDIRECT(ref_text,[a1])
语法释义:INDIRECT( 文本 ,[ 引用样式 ])
其中,参数“引用样式”包括 A1 和 R1C1 两种样式。A1 样式是指单元格的列号用字母表示,如右上图所示。而 R1C1 样式是指单元格的列号用数字表示,如右下图所示。如果 INDIRECT 函数缺省这一参数, 公式默认为 Excel 系统设置的样式。一般情况下,Excel 初始系统设置为A1 样式,如果要修改为 R1C1 样式,可在【Excel 选项】→【公式】→【使用公式】选项中选中【R1C1 引用样式】复选框。
INDIRECT 函数的参数形式包括直接引用和间接引用两种。两种形式的具体表现方式,以及作用和效果均有所不同,具体区别如下图所示。
下面是采用 INDIRECT 函数的两种引用形式设置公式所返回不同的结果。公式效果及公式表达式如下图所示。
跨表提取数据应该是我们的日常工作之一。例如,将某一核算项目 1~12 月报表中的数据分别提取至汇总表中。
在 B3 单元格中设置公式“=HLOOKUP(B$2,INDIRECT($A3&"!$B$2:$J$23"),22,0)”→将公式批量填充至 B3:I6 单元格区域即可,效果如下图所示。
这里提醒大家运用 INDIRECT函数时的一个细节:注意所引用单元格中的文本必须和与被引用的工作表名称完全相同,否则就无法正确查找到想要的数据。