Excel | 多个表批量合并一起,告别手动复制粘贴

如果是跟数据打交道的小伙伴(财务、人事、数据分析类),一天负责十几张表格应该是正常事情,但是这十几张表格大多数结构类似,主要由于上传的部门不一样或者是分公司上传过来,当你一遍遍复制粘贴,老板在聊天软件上不停地消息催促你的汇总表。


就会有有趣的事情发生了。

嘤嘤嘤,老板真不是我的问题啊(( ▼-▼ ))


汇总神器:Power Query



解决这个问题的神器就是它:Power Query


首先这个Power Query可不是插件!他是Excel2016版本自带的一个功能,实际上呢也是微软PowerBI(可视化数据工具)的附带功能,微软的数据导入亲儿子。


Power Query可应用的场景可多了:关系型数据库、Excel、文本和XML文件,OData提要、web页面、Hadoop的HDFS等等。


【数据】菜单-右侧就是Power Query啦


而数据整合以后,就建立好数据模型,为用Excel、Power Pivot、Power View、Power Map进行进一步的数据分析做好准备。


并且这个设置都是自动化的!一次设置以后,后续右键刷新即可。


什么?你只装2007版本?老哥现在是2018年了为,不过你要使用Power Query也不是不可以,那就要去官网下载了,相关链接:


Power BI:

https://powerbi.microsoft.com/zh-cn/desktop/

Power Query:

http://www.microsoft.com/zh-cn/download/details.aspx?id=39379


老哥,为什么我下载了还是不行啊?

老铁,WPS可没有这个功能........


场景1:同文件,多sheet的合并



同文件,多sheet的合并,我们来设置下(文件名:表1)


表1~表4(表格格式一样,规范)


每个sheet分别是一个代销商信息,所有列是一一对应的,没有列混乱的。


那我们就新建一个新的sheet,取名“汇总表”

 

【菜单栏】→【数据】 →【新建查询】→【从文件】  →【从工作簿】



选择文件【表1】、“打开”后,进入【导航器】,可以看到文件中的4个sheet,勾选“选择多项”,然后勾选所需的sheet,点击右下角【加载】。


稍等片刻,右边弹出“工作簿查询”窗口后会显示刚刚导入的四个表格。


重点来了!点击【新建查询】→在下拉菜单中找到【合并查询】→选择【追加】→在追加窗中选择【三个或更多表】→选取数据→点击【确定】


 

这时【查询编辑器】会自动打开看到几个sheet数据已经追加在一起了。 之后点击【关闭并上载】就可以看到了最后的我们要的【汇总表】了!



表要修改数据?不要紧,在原sheet进行修改然后再汇总表进行右键刷新就可以。


要追加新的Sheet?没事,下面就教下如何快速增加心得Sheet数据,不必会去再去导入:


在汇总表中点击【数据】→【新建查询】→在下拉菜单中找到【合并查询→【编辑器】→点击【高级编辑器】


在源 = Table.Combine({表1, 表2, 表4, 表3})后面添加正确的表名就可以,比如:源 = Table.Combine({表1, 表2, 表4, 表3,表4})→完成点击左上角的【关闭并上载】


 

 

场景2:多个文件的合并



假设你的文件还是刚才那几个数据源,但是Sheet变成文件了,但是在同一个文件夹,那么就是另外一个操作方法。



注意文件夹不要有其他无关的文件,不然会影响计算。


首先新建一个表格→【数据】→【新建查询】→【从文件夹】→在弹窗中选择目标文件夹路径确定

 

 

然后点击右下角的【编辑】弹出【查询编辑器】

新建自定义列:键入公式 =Excel.Workbook([Content])


注意公式一定要一模一样,包括大小写、全半角、符号。



然后点击最后一列【确认】两次,如图所示(必须点两下确认哦!要扩展两次。)

 


然后我们在【转换】→【把第一行作为标题】→【地区】这一列进行筛选,取消地区的勾选,这样就不会有重复的标题出现了



最后删除无关数据列,选中地区列,按住shift键,单击代销商列,这样正确的数据源都被选中后,在列标题上右键单击【删除其他列】



完成后点击左上角的【关闭并上载】,数据就完成啦~



本次教程结束

谢谢各位观看~

如有问题可以在后台提问或者私信我



Excel | HR必备神器,员工生日自动化提醒


图表说 | 如何六步制作商业级的柱状图


图表说01-平时用的这些图表,可能会害了你


技巧 | 日常工作时必备的 Excel 技能有哪些?


推荐阅读更多精彩内容