indirect函数多表汇总-用indirect合并多表数据 收藏 0

INDIRECT，一个Excel高手爱不释手的函数，可以一次引用多个表格

今天有粉丝问到这样一个问题：有没有哪个函数，让人一看就知道是Excel高手？这样的函数其实有很多，我觉得最具代表性的就是INDIRECT函数，会这个函数的Excel水平一定不会差，但是不会这个函数的水平应该不会太好，今天我们就来了解下这个函数的使用方法，让你也能成为同事眼中的Excel大神。

以下内容在我的专栏中都有讲到，想要从零学些Excel，这里↑↑↑↑↑

INDIRECT:返回由文本字符串构成的数据引用区域，它是一个间接引用函数

语法：=INDIRECT(ref_text, [a1])

第一参数：定义的名称或者文本字符构成的引用的数据区域

第二参数：单元格引用类型，一般直接将其省略掉即可

INDIRECT函数它是一个间接引用函数，与之对应的就是直接引用，以下图为例来了解下它们的区别，现在我们想要获取A1单元格中张飞这个姓名。

直接引用:它是直接引用单元格的地址来获取姓名，所以公式为=A1

间接引用:它不会直接获取需要的结果，而是需要一个跳板，间接地获取引用结果。如上图INDIRECT引用的是C1这个地址，C1单元格的结果是A1，所以INDIRECT就会再返回A1单元格的结果

INDIRECT函数的第二参数一般是将其省略掉，所以关键是如何构建它的第一参数，第一参数包含两类数据

1.定义名称

这个比较简单，我直接将定义的名称作为参数，输入到第一参数中即可，最经典的案例就是用于制作多级联动下拉菜单

2. 文本字符构成的引用区域

这种我们需要记得它的编写规则，编写规则如下图所示，我们需要注意以下4点

1）工作薄名称与工作表名称必须用单引号括起来

2）工作薄名称需要包含扩展名（.xlsx）

3）在同一个工作薄中进行数据引用，工作薄名称可以省略

4）叹号是名称与引用区域的分割符号

以上就是INDIRECT函数第一参数的编写规则，随后我们来看2个案例，来具体的演示下

1.定义名称

首先需要将数据整理下，将数据的首行设置为数据的上一级，比如【河南】作为首行，下面的是【河南】对应的城市，【郑州】作为首行，下面的是【郑州】对应的区县，以此类推，有几层关系就整理几个表格

随后选中数据区域，按下快捷键F5调出定位，然后点击【定位条件】选择【常量】然后点击确定，紧接着点击【公式】找到【定义名称】选择【根据所选内容创建】，在跳出的界面中仅仅勾选【首行】然后点击确定，这样的话就会就根据首行来定义名称

2.制作多级下拉菜单

第一级下拉菜单比较简单，直接使用【数据验证】设置即可，在这里就不再过多演示，我直接来设置第二级下拉菜单

只需要点击【数据验证】然后将允许设置为【序列】将公式设置为=INDIRECT(D9)，D9的结果是河南，而刚才我们定义了名称，现在【河南】就代表它下面的所有城市，结果就是郑州、信阳、洛阳这三个城市，这个就是制作原理，三级下拉也是这个制作方法，大家可以试着做一下，就不再演示了

INDIRECT函数最常见的作用就是用于构建动态的数据区域，比如在这我们想要将1到5月的数据都汇总在一个表格中，就可以利用它来实现

首先我们需要将sheet名称就是1月到5月放在表格的首行，随后只需要将公式设置为

=VLOOKUP($A2,INDIRECT(\”\’\”&B$1&\”\’!$A:$B\”),2,FALSE),然后向右拖动，向下填充即可。

关键是vlookup函数的第二参数INDIRECT(\”\’\”&B$1&\”\’!$A:$B\”)，当向右拖动的时候，B1变为1月到5月，也就分别引用1月到5月的数据，这样的话就能达到一个动态引用的效果

以上就是INDIRECT函数的所有内容，这个函数理解起来没有那么直观，需要绕一圈，很多人感觉比较难，如果实在没懂的话，建议多看几次，关键是文本字符构成的引用区域的编写规则。

以上就是今天分享的全部内容，怎么样？你学会了吗？

我是Excel从零到一，关注我，持续分享更多Excel技巧

Excel多表汇总不用愁，五种多表合并方法，高效完成日常工作

工作中我们不免会碰到一种情况就是，多分数据表格的合并汇总操作。可能很多朋友碰到这样的情况的时候，就是简单的复制粘贴的操作，这样不仅耗时耗力，而且效果还很差。下面我们就来学习一下，Excel中全部5种多工作表数据合并操作。

函数=sum(\’*\’!B2)

讲解：通过用sum用通配符*求和的方式来求出每张工作表对应位置的数据之和进行汇总。这个案例需要注意的重点有2个：

1.1 通配符*代表的是任意工作表中的B2单元格；

1.2 A产品、B产品、C产品等不同产品工作表和汇总表中数据汇总区域，姓名和月份的数据位置都需要保持一致，位置不能错乱。

函数=sum(产品A：产品C！B2）

讲解：这个函数跟方法1中的通配符搭配的方法原理一致，同样是需要每张工作表数据对应的位置需要相同。

函数=INDIRECT(B$1&\”!B\”&ROW())

解析：在这里我们通过取不同工作表名称作为汇总数据的行标题，利用INDIRECT函数调用B1单元格提取对应工作表的数据。其中ROW()代表返回当前的行的值。如函数中B&ROW()=B2单元格。

操作方法：

4.1 依次按alt、D、P等按键，进入数透视表透视向导图界面，选择多表合并计算区域；

4.2 依次分别选择每张工作表对应区域，添加区域后点击下一步，点击创建新工作表。

讲解：这样的操作是利用了数据透视表汇总的方法，来添加在每张页面产品关键词对应的位置都不一样的时候的操作方法。

代码如下：

Sub 多表合并()

Dim i%, rs%, rss%, st As Worksheet, ast As Worksheet

Set zst = Sheet4 \’将汇总工作表第一季度定义为变量zst

For i = 1 To 3

Set st = Sheets(i & \”月\”) \’将1-3月的工作表定义为变量st

rs = st.UsedRange.Rows.Count \’计算1-3月每个表的最后一行

rss = zst.UsedRange.Rows.Count + 1 \’计算第一季度工作表的最后一行的下一行

st.Range(\”A2:B\” & rs).Copy Cells(rss, 1) \’复制1月、2月、3月每个工作表的数据到第一季度的汇总表中

Cells(rss, 3).Resize(rs – 1) = i & \”月\” \’将1-3月工作表的工作吗写入到汇总表对应的月份当中

Next

End Sub

现在你学会如何进行多工作表数据汇总了吗？

Excel函数案例应用分享 第二期 indirect函数跨表汇总数据

Hello大家好，这里是Joker，我们今天继续给大家做一个Excel函数案例应用的分享，今天的主题是indirect函数跨表数据汇总，这个问题也是我们在工作中经常会遇到的一个问题，下面我们来看一下具体案例。

问题分析：

首先我们这边总共有四张表，前面是三张以月份为工作表名的员工销售额数据表，第四张是一张汇总表：

1月

2月

3月

汇总表

并且这里面前面三张分表的销售人员数量是不固定的，我们现在所要做的事情，就是把每个月的合计销售额进行汇总，我们用常规的复制粘贴来操作的话，少量表可以在短时间内完成，但是如果分表数量达到几十上百张的话，再用这种方法的话，就不太现实了。这种的跨表汇总的问题，我们可以用indirect函数来解决，今天我们给小伙伴们分享两种方法。

解决方法1：

我们首先观察一下上面三张分表的规律，我们可以看出其实是把三张分表的合计总额进行跨表引用，可以使用indirect引用每张分表合计总额对应单元格地址，就可以引用到对应单元格的值，但是三张分表合计总额的位置都是不同的，也就是我们无法直接引用固定的地址了。但是我们的indirect函数的参数一大特点就是灵活，其中的单元格地址是可以随意组合的。

单元格的地址是由什么构成？是由行列坐标来构成。其有两种表示方法，一种是默认的A1样式，另种是R1C1样式，用的较少，R是ROW的缩写，代表行号，C是COLUMN的缩写，代表列号，而indirect函数的第二参数为FALSE时，代表采用R1C1样式，这样两者就完美契合了。

现在问题转变成确定分表中合计总额单元格地址的行列坐标，分表中合计总额单元格地址的列坐标其实是确定的，都是在b列，也就是第2列，那么列坐标就可以写为文本“C2”。再确认它是在第几行就好了，这个可以用match函数进行定位， 因为每个分表销售总额和“合计：”这个字段都是在一行，用match函数定位“合计：”字段行号，就等于确认合计总额单元格行号，我们就拿1月份数据举例，其它分表都是一样的操作：

=MATCH(\”合计：\”,INDIRECT(\”\’\”&A2&\”\’!A:A\”),0)

公式为=MATCH(\”合计：\”,INDIRECT(\”\’\”&A2&\”\’!A:A\”),0)，结果为11，也就是在“1月”这张分表中“合计：”字段在第11行。给大家解释下公式里面的各个参数的含义，第一参数查询值我们就直接拿“合计：”在A列中进行查询，第二参数查询列，我们用INDIRECT(\”\’\”&A2&\”\’!A:A\”)来构建，含义是引用1月工作表的A列，这里的A2代表的是总表A列对应的月份也就是每个分表的工作表名，在工作表名前后加单引号是避免工作表名存在不合法字符，导致的引用错误，这是一个非常重要的技巧，凡是做工作表名的引用时都最好在工作表名前后加单引号。第三参数为0，精确匹配。

现在行列坐标都有了，用文本连接符“&”，将行列坐标连接起来，就可以用indirect函数进行汇总了：

=INDIRECT(\”\’\”&A2&\”\’!R\”&MATCH(\”合计：\”,INDIRECT(\”\’\”&A2&\”\’!A:A\”),0)&\”C2\”,0)

公式：=INDIRECT(\”\’\”&A2&\”\’!R\”&MATCH(\”合计：\”,INDIRECT(\”\’\”&A2&\”\’!A:A\”),0)&\”C2\”,0)，这就是第一种方法。

解决方法2：

针对这个案例还有个更简单的方法，用max函数和indirect函数嵌套，通过观察各个分表的规律。可以看出分表的合计总额和员工销售数据是在同一列中的，并且肯定大于每一位员工的销售额，我们借用indirect函数跨表引用每张分表的B列数据，直接用max函数在每张分表B列求出最大值，就可以了。

=MAX(INDIRECT(\”\’\”&A2&\”\’!B:B\”))

公式为=MAX(INDIRECT(\”\’\”&A2&\”\’!B:B\”))，其中INDIRECT(\”\’\”&A2&\”\’!B:B\”)跨表引用各分表的B列数据，同时利用max函数求最大值时忽略文本的性质，就可以汇总各分表的合计总额了。

以上就是我们今天的案例分享啦。

手打不易，点个赞，留个言，收藏一下喽。有任何疑问，也非常欢迎小伙伴们给JOKER留言，JOKER尽量给大家做一个解答，小伙伴们也可以去JOKER主页观看对应的视频讲解。

学习的乐趣之一在于大方的分享已知，然后努力的探究未知。

想看更多函数案例? 点进JOKER主页查看合集列表。

对EXCEL函数感兴趣？点进JOKER主页找到最显眼的红色按钮，然后盘它。

需要练习课件？点进JOKER的主页链接自取。

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com