这是来自读者的求助:全国的客户销售表按每个省份分别单独建立了一个工作簿,任何一个省份的数据有更新都需要同步到总表中。

可是数据量很大,复制粘贴的工作量太大,如果用 vlookup 多条件查找,公式复杂容易出错不说,过多公式还会导致 Excel 运行极慢。

有什么更简便的方法帮助他合并多个工作簿,且每日实时更新?

案例:

文件夹下有多个工作簿,样式分别如下,需要将这些工作簿合并到总表,并按购买产品排列成二维表。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

解决方案:

1. 打开空的总表文件 --> 选择菜单栏的“数据”-->“新建查询”-->“从文件”-->“从工作簿”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

2. 在弹出的对话框中选择需要导入到文件后确定

3. 在弹出的导航器页面中可以看到,需要导入的工作簿只有一个工作表,所以双击这个工作表即可。如果有多个工作表的话,可以勾选“选择多项”来选择。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

第一个工作簿已上传至 Power Query。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

4. 选择菜单栏的“主页”-->“新建源”-->“文件”--> Excel

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

5. 重复上述步骤 2、3 将第二个文件上传至 Power Query。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

6. 重复步骤 4、5 依次上传所需的工作簿。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

7. 在左边的“查询”导航中选中第一个文件 --> 选择菜单栏的“主页”-->“追加查询”-->“追加查询”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

8. 在弹出的对话框中选择“三个或更多表”--> 将“可用表”区域中除了当前表之外的所有表依次添加到“要追加的表”区域 --> 点击“确定”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

现在所有工作簿都已经合并好了,如果只需要合并,不用做进一步操作,那么只要把这个表上载到 Excel 就可以了。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

9. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

合并后的表格就传回了 Excel,今后任何工作簿的数据若有更新,只要刷新这个表就能同步。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

如果需要将上述表格转置成二维表,可以在 Power Query 中再进行设置。

10. 双击右边的“工作簿查询”区域中的 Sheet1 打开 Power Query。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

11. 选中“购买产品”列 --> 选择菜单栏的“转换”-->“透视列”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

12. 在弹出的对话框中按以下方式设置 --> 点击“确定”:

  • 值列:价格
  • 聚合值函数:不要聚合

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

13. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载”

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网

Excel 中的表格就排列成了二维表。今后同样只要刷新就能实时导入任何工作簿的更新。

excel中如何实现快速合并多个工作簿,且关联数据实时更新?-趣帮office教程网