首页 > Excel专区 > Excel教程 >

EXCEL2016数据查询功能实现多工作簿合并与及时刷新

Excel教程 2021-12-26 13:41:33

最近几天,不止一位朋友问同一个问题:有很多工作簿,是从下属分部门收集上来的,怎样实现数据汇总计算?

那今天就详细一多工作簿合并的步骤。

今天的步骤,是用EXCEL2016完成的。EXCEL2016提供了强大的“PowerQuery”,即数据查询功能,能帮助我们不用费时费人力粘贴、不用多数人接受不了的VBA、不用专业的SQL查询语句,只需要鼠标点下去,就能完成多工作簿的数据汇总。当然,中间只需要一个非常简单的小公式。

步骤详解

第一步:将需要合并的工作簿存入一个文件夹中。

用八个简单的工作簿来做示例:

第二步:新建“合并”工作簿

新建的工作簿不要和要合并的工作簿放在同一文件夹。

第三步:新建查询

1、【数据】——【新建查询】——【从文件】——【从文件夹】

2、浏览到要合并的工作簿所在的文件夹:

3、可以看到文件夹中要合并的工作簿,点击【编辑】:

第四步:查询设置

1、进过以上步骤,我们看到了EXCEL数据查询的真面目:

2、选中前两列,将其他记录工作簿信息的数据列删除:

第五步:添加列选项

1、【添加列】选项卡中,点击【自定义列】:

2、输入公式: =Excel.Workbook([Content]) ,其中,[Content] 在右侧选择插入。(特别注意:此处公式区分大小写)

3、添加列完成以后,多了内容为“Table”的自定义列,要合并的工作簿数据表就隐藏在此列:

4、点击下图红框按钮:

5、出现下图,只勾选“扩展”“data”,确定:

6、又出现隐藏的表格:

7、继续点击下图红框按钮:

8、直接确定:

9、文件夹里所有工作簿的数据都合并在一起了:

第六步:数据加工

1、有几个合并工作簿,就会又几个列标签:

2、删除 Content列:

3、设置将第一行用作标题

可以看到列标签变为工作簿1的列标签:

4、其他工作簿的列标签依然存在:

5、在其中一个列标签出点开“筛选”,比如此处打开“来源”,去掉“来源”前的勾,就会去掉每一个工作簿原有的列标签:

6、此时合并以后的每一列数据类型都是“任意”,可以根据实际情况设置各个字段数据类型:

第七步:数据加载到表格

1、【开始】——【关闭并上载】:

2、最终实现了工作簿的合并:

第八步:数据刷新

如果合并前的工作簿数据进行了更新,合并后的工作簿可以通过【设计】——【刷新】,或者【数据】——【全部刷新】来更新,如下两幅图:

特别注意

1、如果合并以后的数据,不再是表格,而是转为了“区域”,合并前的工作簿数据更新后,合并后的数据不能随之更新。如下两幅图:

2、带有查询功能的工作簿打开时,要选择启用外部数据连接,才能进行随原工作簿数据的更新而更新:


标签: excel公式技巧excel函数公式Excel常用函数excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.