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

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

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

案例:

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(1)

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(2)

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(3)

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(4)

解决方案:

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(5)

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

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(6)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(7)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(8)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(9)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(10)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(11)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(12)

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(13)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(14)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(15)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(16)

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

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(17)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(18)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(19)

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(20)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(21)

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

excel把多个工作簿内容合并到一起(多个工作簿合并且转置成一个二维总表)(22)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

,