一个文件夹里面有多个工作簿,如何快速将这些工作簿合并成一个工作簿,而且后期在文件夹插入新的工作簿时,汇总后的工作簿也会实时更新,用什么方法实现,这是很多小伙伴很想知道的一个技能!
下图的Excel文件夹里面,放置了5个工作簿,每个工作簿里面的数据结构一样,但标题的顺序可以不一样。
下面教你用Excel中的Power Query来解决这个问题,具体操作步骤如下:
1、新建一个空白的工作簿并打开 — 在“获取和转换”工具组中点击“新建查询”的下拉三角 — 在下拉菜单中选择“从文件”–“从文件夹”。
2、弹出“文件夹”对话框 — 可以直接将工作簿所在的文件夹路径复制到文本框中,或者点击“浏览”按钮。
3、点击“浏览”按钮之后 — 弹出“浏览文件夹”对话框 — 选择工作簿所在的文件夹 — 点击“确定”按钮。
4、点击“确定”按钮之后 — 返回到“文件夹”对话框 — 可以看到文本框中的路径就是工作簿所在文件夹的路径 — 点击“确定”按钮。
5、点击“确定”按钮之后 — 可以看到文件夹下的所有工作簿都显示在下面这个界面上 — 点击“转换数据”按钮。
6、弹出“Power Query 编辑器”对话框 — 选中“Content”列 — 点击“管理列”– 点击“删除列”–“删除其他列”。
7、删除其他列之后欧只剩下Content这一列 — 点击“添加列”选项卡 — 在“常规”工具组中点击“自定义列”。
8、弹出“自定义列”对话框 — 在“自定义列公式”中输入公式“=Excel.Workbook([Content],true)”– 其中Content值为“可用列”点击插入的 — 点击“确定”按钮。
9、点击确定按钮之后,可以看到在Content列右边多列一列自定义列 — 点击自定义列右边的“扩展”按钮 — 取消勾选“选择所有列”,勾选“Data”前面的复选框,取消勾选“使用原始列名作为前缀”前面的复选框 — 点击“确定”按钮。
10、可以看到这一列的列名就变成“Data”了,而不是“自定义”– 再次点击“Data”右边的“扩展”按钮 — 在“选择所有列”前面的复选框打钩,不勾选“使用原始列名作为前缀”前面的复选框 — 点击“确定”按钮。
11、现在我们可以看到我们工作簿的所有数据都显示出来了,但多了一个Content列,所以需要删除。选中Content列 — 点击“鼠标右键”– 在右键菜单中选择“删除”。
12、切换到“主页”选项卡 — 点击“关闭”工具组中的“关闭并上载”的下拉三角 — 选择“关闭并上载”。
13、以上步骤操作完成之后,可以看到5个工作簿的数据都汇总到一个工作表中了。点击“品牌”右边的筛选按钮 — 里面就可以看到这5个表的数据。
14、如果我们在文件夹内创建一个工作簿,回到汇总表里面,点击菜单栏的“数据”选项卡 — 在“连接”工具组中点击“全部刷新”– 然后点击“品牌”右边的“筛选”按钮 — 可以看到我们新创建的工作簿自动汇总到这个表中。
15、动图演示。
总结:使用Power Query 来汇总多个工作簿,只需要每个工作簿的标题一样,顺序可以不一样,就可以汇总在一张工作簿中,如果你的文件夹新增了工作簿,只需在汇总表内点击“全部刷新”,即可将新增加的工作簿自动汇总到汇总表中。这个方法我们也是很常见的,相信大家都能够学会!