论文部分内容阅读
职场实战中,经常会涉及多个工作簿数据的汇总或分析。如图1所示,这是某集团下属公司的销售数据,类似的销售数据还有许多,如果每次手工复制、粘贴之后再进行后续操作,工作量可想而知。而且如果源数据发生了变化,上述操作势必需要重新执行一次。
由于源数据的表结构相同,我们可以利用查询编辑器完成多工作簿的动态合并。最主要的准备工作是将分布不同工作簿、不同工作表的数据汇总到同一工作表内。这里以Excel 365版本为例进行说明,具体操作如下。
第1步:载入文件夹
新建一个空白工作簿,切换到“数据”选项卡,在“获取和转换数据”功能组下依次选择“获取数据→来自文件→从文件夹”,按照提示載入相应的文件夹。如图2所示,点击右下角的“转换数据”按钮,此时会自动打开Power Query编辑器。如果是Excel 2016/2019版本,请点击“编辑”按钮。
如果只需要合并各个工作簿中某几个工作表的数据,可以依次选择“组合→合并并转换数据”,如图3所示,在这里选择相应的工作表转换数据即可。
第2步:添加自定义列
进入Power Query编辑器之后,选择最左侧的两列,单击右键,从快捷菜单选择“删除其他列”并执行。切换到“添加列”选项卡,在“常规”功能组单击“自定义列”按钮,此时会打开“自定义列”对话框,在这里输入任意列名,例如“data”,在公式编辑框中输入如下公式:
=Excel.Workbook([Content],true)
上述公式中,Excel.Workbook函数的作用是从Excel工作簿返回各工作表的记录,第一个参数是需要解析的字段,第二个参数使用true,表示指定将表格中的第一行作为字段标题。要强调的是,这里的公式要注意字母的大小写,否则会报错。
第3步:
右击刚才添加的自定义列“data”,从快捷菜单选择“删除其他列”。单击“data”列标右侧的展开按钮,默认设置下直接点击“确定”按钮。右击“data”列选择“删除其他列”,此时可以看到如图4所示的合并效果。
接下来的操作就简单多了,返回“主页”选项卡,单击“关闭并上载”按钮,即可将该文件夹下所有工作簿中各个工作表的数据都汇总到一起,后续的统计、分析等相关操作就简单多了。如果在文件夹中添加了新的工作簿,我们只需要在汇总表格中单击右键刷新就可以了。
补充:如果是Excel2016以下的版本,可以从微软官方下载安装Power Query编辑器插件,也可以下载安装“易用宝”插件。
由于源数据的表结构相同,我们可以利用查询编辑器完成多工作簿的动态合并。最主要的准备工作是将分布不同工作簿、不同工作表的数据汇总到同一工作表内。这里以Excel 365版本为例进行说明,具体操作如下。
第1步:载入文件夹
新建一个空白工作簿,切换到“数据”选项卡,在“获取和转换数据”功能组下依次选择“获取数据→来自文件→从文件夹”,按照提示載入相应的文件夹。如图2所示,点击右下角的“转换数据”按钮,此时会自动打开Power Query编辑器。如果是Excel 2016/2019版本,请点击“编辑”按钮。
如果只需要合并各个工作簿中某几个工作表的数据,可以依次选择“组合→合并并转换数据”,如图3所示,在这里选择相应的工作表转换数据即可。
第2步:添加自定义列
进入Power Query编辑器之后,选择最左侧的两列,单击右键,从快捷菜单选择“删除其他列”并执行。切换到“添加列”选项卡,在“常规”功能组单击“自定义列”按钮,此时会打开“自定义列”对话框,在这里输入任意列名,例如“data”,在公式编辑框中输入如下公式:
=Excel.Workbook([Content],true)
上述公式中,Excel.Workbook函数的作用是从Excel工作簿返回各工作表的记录,第一个参数是需要解析的字段,第二个参数使用true,表示指定将表格中的第一行作为字段标题。要强调的是,这里的公式要注意字母的大小写,否则会报错。
第3步:
右击刚才添加的自定义列“data”,从快捷菜单选择“删除其他列”。单击“data”列标右侧的展开按钮,默认设置下直接点击“确定”按钮。右击“data”列选择“删除其他列”,此时可以看到如图4所示的合并效果。
接下来的操作就简单多了,返回“主页”选项卡,单击“关闭并上载”按钮,即可将该文件夹下所有工作簿中各个工作表的数据都汇总到一起,后续的统计、分析等相关操作就简单多了。如果在文件夹中添加了新的工作簿,我们只需要在汇总表格中单击右键刷新就可以了。
补充:如果是Excel2016以下的版本,可以从微软官方下载安装Power Query编辑器插件,也可以下载安装“易用宝”插件。