上一篇:07-2-批量汇总工作簿
本篇为Excel工作簿高级操作实例之一:
本篇所介绍的“合并工作簿”分为两种情况:
- 情况1:将多个工作簿合并在一个工作表内。
- 情况2:将多个工作簿合并在一个工作簿中。
下面将分别介绍。
1.将多个工作簿合并在一个工作表内
例如,用于记录每个月销售记录的文件夹中保存着3个月的销售记录。
每个工作簿中都只有一个工作表,工作表的结构相同,且3个工作表的名称都相同。
此时,我们创建一个新的工作簿,使用PowerQuery可以合并三个工作簿中的数据。
具体方法如下。
<1>创建一个新工作簿。选择【数据】选项卡,单击【获取和转换】命令组中的【新建查询】下拉按钮,从弹出的列表中选择【文件】
<2>打开【选择数据源】对话框,选中Excel Files选项,单击【确定】按钮。
<3>打开【选择工作簿】对话框,在【驱动器】下拉列表中选择保存要合并工作簿文件夹的驱动器,在【目录】列表中选中保存要合并工作簿的文件夹,然后在【数据库名】列表框中双击任意一个要合并的工作簿。
<4>打开【添加表】对话框后,单击【选项】按钮。
<5>打开【表选项】对话框后,选中【系统表】复选框,单击【确定】按钮。
<6>返回【添加表】对话框,双击数据源所在的工作表名,然后单击【关闭】按钮关闭该对话框。
<7>在PowerQuery查询窗口中双击数据表中的“*”号。
<5>单击SQL按钮,打开SQL对话框。
<6>在SQL对话框中选中SELECT和FROM之间的代码。
<7>按下Delete键将选中的代码删除,在其位置上输入一个“*”号。
<8>在代码的最后加一个空格,然后输入“union all”,完整代码如下:
SELECT * FROM `D:合并工作簿一月销售数据.xlsx`.`数据$` `数据$` union all
<9>复制SQL对话框中全部语句,然后按下Enter键执行【粘贴】命令,我们需要合并几个工作簿,就粘贴几段相同 代码。
<10>将SQL对话框中的工作簿名称该为需要合并的实际工作簿名称,例如本例中改为,一月销售数据.xlsx、二月销售数据.xlsx和三月销售数据.xlsx。
<11>删除SQL对话框中最后一个“union all”,然后单击【确定】按钮。
<12>在弹出的提示对话框中单击【确定】按钮。
<13>在PowerQuery对话框中单击【将数据返回到Excel】按钮。
<14>打开【导入数据】对话框,单击【现有工作表】输入框后的按钮。
<15>选择数据存放的位置(例如A1单元格),按下Enter键。
<16>返回【数据导入】对话框,单击【属性】按钮。
<17>打开【连接属性】对话框,选中【打开文件时刷新数据】复选框,单击【确定】按钮。
<18>返回【导入数据】对话框,单击【确定】按钮,即可在步骤1新建的工作簿中得到合并工作簿后的数据。当我们在保存数据源的工作簿中添加或删除了数据之后,在合并数据的表中右击鼠标,从弹出的菜单中选择【刷新】命令,可以同步更新数据的变化。
2.将多个工作簿合并在一个工作簿内
例如,用于记录每个城市售记录的文件夹中保存3个工作簿,每个工作簿中工作表的名称和数量不等。
此时,我们可以使用VBA来将这3个工作簿合并为一个工作簿。
<1>新建一个工作薄,将其命名为工作簿合并后的名字,例如“合并工作簿”。
<2>右击新建的工作簿中的任意一个工作表标签,从弹出的菜单中选择【查看代码】命令。
<3>打开VBA窗口,在其中输入以下代码:
Sub 工作薄合并()Dim FileOpenDim X As IntegerApplication.ScreenUpdating = FalseFileOpen = Application.GetOpenFilename(FileFilter:="Microsoft Excel文件(*.xlsx),*.xlsx