用Power Query实现多表合并

工作中经常会遇到多表合并的问题,如果手动复制粘贴费时费力,还难免出错,小编之前分享了多表合并的4种方法,多表合并(Power Query、SQL、函数与公式、VBA四种方法)。

本文介绍Power Query批量合并,要求Excel 2016版本或Office 365版本。如果是Excle 2010或者2013版本,则需要安装Power Query插件(加载项),官网下载地址为:
https://www.microsoft.com/zh-CN/download/details.aspx?id=39379

按照数据源结构和要求效果,多表合并可以分为以下几种情况:

  1. 单工作簿内多张工作表多表合并
  2. 多工作簿单张工作表多表合并
  3. 多工作簿多张工作表多表合并

单工作簿内多张工作表多表合并

有N多个以月份命名的excel工作表(为演示方便以6个为例),每张表字段名相同,现需要把表格全部合并到一个表中去。

工作表名:

图01

每张表字段名:

图02

下面我们看看怎么利用这个工具实现多表合并。

操作步骤如下:

Step1:点击菜单数据→新建查询→从文件→从工作簿,找到当前文件的位置并导入。

图03
图04

Step2:在打开的导航器,选择要合并的多个工作表,再点击“编辑”。

图05

Step3:在打开的“查询编辑器”中点击“追加查询”,选择要合并的工作表,点击“确定”。

图06

Step4:点击“关闭并上载”,瞬间生成了6张工作表,sheet1就是把6个月的报表合并后的汇总表,sheet2到sheet6是多余无用的表,点击sheet2,按住shift键选中sheet2到sheet6工作表,右键“删除”。

多工作簿多张工作表多表合并

先来看下数据源,不同省份的数据分别放在不同的工作簿文件中,每个工作簿中又包含多张工作表。截图如图16:

图16

现在要求将文件夹中所有工作簿文件中的所有工作表的数据合并到一起。操作步骤如下:

Step1:点击菜单数据→新建查询→从文件夹,在打开的文件夹向导对话框中,点击“浏览”,从电脑中找到存放数据多个文件的文件夹,单击“确定”。

图17

Step2:Excel会弹出一个界面,展示所选文件夹内包含的Excel工作簿及文件属性, 单击右下方“编辑”按钮,如图19。

图19

Step3:在打开的Power Query编辑器中,展示内容如图20。

图20

在这个界面中,我们可以删掉不需要的数据,方法如下:
按住Ctrl选中需要保留的两列数据,然后单击删除列→删除其他列,如图21。

图21

Step4:将多个工作簿文件中的多个工作表数据添加到编辑器界面,方法如下:
单击添加列自定义列,如图22。

图22

Step5:在弹出自定义列的对话框中,输入自定义列公式如下:

Excel.Workbook([Content],true)

注意:这个公式严格区分大小写,否则会导致错误。

图23

输入自定义列公式之后,单击右下角的“确定”按钮。可见编辑器界面中已经添加自定义列,如图24。

图24

Step6:下面我们把自定义列中的数据按照工作簿文件和工作表展开,方法如下:

点击自定义列右边的按钮,如图25,展示如图26页面,点击“确定”,得到如图27结果。

图25
图26
图27

Step7:点击自定义.Data右边的按钮,得到如图28,点击“确定”,得到如图29结果,这时候可以看到多个工作簿多张工作的全部字段内容。

图28
图29

单击“关闭并上载”后,多表合并好的数据已经返回到工作簿。

作者: Hugh

Welcome to Wan's world~