头部广告

合并多个Excel表格数据到一个表,一个函数公式轻松搞定!

我是【桃大喵学习记】,欢迎大家关注我的公众号哟~,每天为你分享职场办公软件使用技巧干货!

今天跟大家分享一个超实用的Excel表格数据合并方法,借助函数公式轻松实现多表格数据合并需求。如下图所示,分别把1月,2月,3月每个员工的销售数据合并汇总到“合并汇总表”中,并且总表数据会根据分表数据更新而自动更新。

1.gif

如果想实现上面的功能,我们需要借助INDIRECT函数的动态引用功能,所以需要先介绍一下这个函数的动态引用使用技巧。

一、INDIRECT函数介绍

功能:返回由文本字符串指定的引用

语法:=INDIRECT(单元格引用,[引用样式])

第一参数:为对单元格的引用,此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text不是合法的单元格的引用,函数 INDIRECT 返回错误值 #REF!。

第二参数:引用的字符串样式,可省略。省略或TRUE或1时,为A1样式的引用;FALSE或0时,为R1C1样式的引用。

应该实例:

1、直接引用单元格

如下图所示,我们直接引用B2单元格中的销售额

使用公式=INDIRECT("B2",1)

2.jpg

2、跨表格引用单元格

如下图所示,我们跨表引用“2月”表中B2单元格中的销售额

使用公式=INDIRECT("2月!B2")

3.gif

我们可以看到如果是跨表引用的话,INDIRECT函数第一参数只需按"表名!引用单元格"这种形式即可。知道了这种用法后,我们就可以进行本文开头提到的合并多个Excel表格数据到一个表了。

二、合并多个Excel表格数据到一个表

还是以本文开头说的场景,将员工1-3月份的销售额,合并汇总到一个表格中。1-3月份销售表都是A列为员工名称,B列为销售额,当然每个月的销售表销售员顺序人数不一定相同。

在汇总表B3单元格中输入公式:

=IFERROR(VLOOKUP($A3,INDIRECT(B$2&"!A:B"),2,0),"")

然后点击回车,把公式先向右填充,然后再向下填充即可,如下图所示

4.gif

公式解读:

①INDIRECT(B$2&"!A:B")就是利用INDIRECT引用汇总表格第二行月份表头来生成动态引用,因为公式在B列引用1月表格,在C列则自动变换为引用2月表格....所以需要锁行不锁列B$2,最终获取对应月份表格A列和B列数据。

②利用VLOOKUP函数第一参数为A列姓名$A3,因为向左填充姓名不变,向下填充姓名改变,所以要锁列不锁行;第二参数使用INDIRECT函数生成动态表格引用;第三参数为2就是获取第二列销售额数据;第四参数0表示精准匹配。

③因为每个月的销售表销售员顺序人数不一定相同,使用VLOOKUP函数查询可能出现错误值,所以最后使用IFERROR函数,遇到错误值返回空值。

总结:

1、合并汇总上面形式的多个表格数据,汇总表格表头名称必须跟分表名称一致,比如说汇总表格中表头包含1月、2月、3月,分表名称也是1月、2月、3月。只有这样才能借助INDIRECT函数的动态引用功能。

5.jpg

2、大家如果对公式不太理解也可以直接套用公式,把下面的参数改成自己的就可以

语法=IFERROR(VLOOKUP(A列名称第一个姓名,INDIRECT(表头分表名称第一个单元格&"!返回分表哪几列"),返回列序号,0),"")

①A列名称第一个姓名:需要锁列不锁行;

②表头分表名称第一个单元格:需要锁行不锁列;

③返回分表哪几列和返回列序号:根据实际情况获取分别哪几列数据以及返回的序列号。

以上是【桃大喵学习记】今天的干货分享~觉得内容对你有帮助,记得顺手点个赞哟~。我会经常分享职场办公软件使用技巧干货!大家有什么问题欢迎留言关注!


底部广告