1. 首页
  2. 自学中心
  3. 软件
  4. Excel

Excel:使用powerquery进行多表合并

工作中常遇到需汇总多张表进行分析的情况,本文以某公司销售数据(数据为虚构数据)为例介绍使用powerquery合并excel表的方法。
本文中所使用数据格式相同,且工作表中第一行为标题行,数据不规范可能使合并汇总数据存在问题或合并不成功,注意事项请移至文末进行查看。
多表合并存在多种情况

Excel:使用powerquery进行多表合并

一. 单工作簿多工作表合并

原始数据中存在多sheet页,进行单工作簿的多工作表合并,先看原始数据及处理之后的数据:
原始数据
Excel:使用powerquery进行多表合并
最终数据▼

Excel:使用powerquery进行多表合并

第一步:新建查询(进入powerquery编辑器)

点击【数据】>【获取数据】>【来自文件】>【从工作簿】,选择原始数据,导入
Excel:使用powerquery进行多表合并

第二步:追加查询
点击【主页】>【追加查询】>【追加查询】,选择“三个或更多表”,依次添加工作表,得到合并后的数据
Excel:使用powerquery进行多表合并

第三步:关闭并上载
点击【关闭并上载】>【关闭并上载】,可在excel中查看汇总后的数据
Excel:使用powerquery进行多表合并

第四步:excel中对数据进行最后处理
在excel中对数据进行简单处理,得到最终汇总数据
Excel:使用powerquery进行多表合并

二. 多工作簿单工作表合并
原始数据含有多个工作簿,但每个工作簿中只有一个工作表,进行多工作簿的单工作表合并,先看原始数据及处理之后的数据:
原始数据
Excel:使用powerquery进行多表合并

Excel:使用powerquery进行多表合并

最终数据
Excel:使用powerquery进行多表合并

第一步:新建查询(进入powerquery编辑器),合并

点击【数据】>【获取数据】>【来自文件】>【从文件夹】,选择原始数据,导入,点击【组合】>【合并和编辑】,选择工作表,点击【确定】,在powerquery中可查看到已合并的数据
Excel:使用powerquery进行多表合并

第二步:关闭并上载

查看合并后的数据,点击【关闭并上载】
注:由于第三个工作表中存在编辑过的空行,合并时空行也进入到合并的数据中,可在合并之前对工作表数据进行处理
Excel:使用powerquery进行多表合并

第三步:excel中对数据进行最后处理

在excel中对数据进行简单处理,得到最终汇总数据
Excel:使用powerquery进行多表合并

三. 多工作簿多工作表合并
实际工作中常常存在需要合并文件夹中的excel数据,且每个工作簿中含有多张工作表,进行多工作簿的多工作表合并,先看原始数据及处理之后的数据:
原始数据
Excel:使用powerquery进行多表合并
最终数据
Excel:使用powerquery进行多表合并

第一步:新建查询(进入powerquery编辑器)

点击【数据】>【获取数据】>【来自文件】>【从文件夹】,选择原始数据,导入,点击【转换数据】
Excel:使用powerquery进行多表合并

第二步:删除其他信息,保留content及name列
保留content及name列,点击【主页】>【删除列】>【删除其他列】
Excel:使用powerquery进行多表合并

第三步:自定义列,返回工作表记录

点击【添加列】>【自定义列】,输入新列名及公式:Excel.Workbook([Content],true)
Excel:使用powerquery进行多表合并
注:
公式Excel.Workbook([Content],true)需区分大小写

公式解析
功能:从Excel工作簿返回工作表的记录
参数:Excel.Workbook( workbook as binary, optional useHeaders as nullable logical, optional delayTypes as nullable logical) as table
第一个参数是要解析的字段,返回一个table,第二个是可选参数逻辑值,参数使用true,就是指定数据使用第一行做为标题。

第四步:展开数据并删除多余数据

1. 选中第三步中返回的table列及name列,点击【主页】>【删除列】>【删除其他列】
注:保留name列为保留数据来源,若不需要可以删除
2. 点击table列,进行展开
3. 展开的数据中,选中table列及数据源列,点击【主页】>【删除列】>【删除其他列】
4. 展开table列

注:若需保留数据来源(工作簿名、工作表名),在数据处理中对数据来源列进行保留即可
Excel:使用powerquery进行多表合并

第五步:关闭并上载

点击【关闭并上载】>【关闭并上载】,可在excel中查看到已合并的数据
Excel:使用powerquery进行多表合并

第六步:excel中对数据进行最后处理

在excel中对数据进行简单处理,得到最终汇总数据
Excel:使用powerquery进行多表合并

注:当文件夹中的数据改变中,可右键进行【刷新】

注意事项
1.数据格式:此方法仅适用于数据格式相同的数据表合并
2.合并单元格:工作表中应避免出现合并单元格
3.空行:若工作表中存在编辑过或有格式的空行,合并时空行也会保留,需在合并之前对工作表数据进行处理:Ctrl+shift+↓选中所有空行,Ctrl+-删除所有空行
4. 其他注意事项后续补充

首发于个人微信公众号【八九子】,有任何问题可随时联系我,邮箱 fanyu1601@163.com

原文链接 https://www.cnblogs.com/fanyu2019/p/11175827.html

原创文章,作者:xsmile,如若转载,请注明出处:http://www.17bigdata.com/excel%ef%bc%9a%e4%bd%bf%e7%94%a8powerquery%e8%bf%9b%e8%a1%8c%e5%a4%9a%e8%a1%a8%e5%90%88%e5%b9%b6/

联系我们

在线咨询:点击这里给我发消息

邮件:23683716@qq.com

跳至工具栏