使用PQ进行Excel多文件合并时 如何指定工作簿里的工作表或区域
- 相关推荐
说明:以下以 Excel 2016版本为例。
我们都知道,Power Query(以下简称PQ)作为Power BI组件的起始端,承担着数据的加载和清洗职能,功能非常强大。日常工作中进行数据的汇总、清洗、整理等,非常方便及快捷,省时省力。
如果我们每个工作簿里面有多个工作表,或者工作表有不同的表头,又或者我们只想要汇总特点的区域数据,有什么办法吗?
答案肯定是没问题的,这也是PQ之所以强大的地方。
例如我们有以下四个工作簿,每个工作簿里面各有2个不同的表,分别是水果和蔬菜。
如果我们想用PQ自动汇总水果的,或者蔬菜的,或者蔬菜+水果的,又或者是公司1水果,公司2蔬菜及公司4水果加蔬菜,又或者是某些水果及某些蔬菜的,等等,应该都能满足你的要求。
如果只想汇总工作表里面某个特点区域,得通过自定义区域名称来进行处理,只需要选择指定的区域,然后重新输入新的区域名称,例如选定水果的数据,再输入 shuiguo 来定义A1:B4这片区域的名称即可。
自定义名称不一定是英文,可以是中文。
如果自定义后需要修改,可以通过名称管理器来进行修改处理。
表格及数据处理好之后,就可以通过数据 - 查询 - 从文件 - 从文件夹,来加载工作簿及数据。
文件夹路径可以通过复制黏贴的方式(推荐),或者选择浏览的方式添加。
点击确定后就进入了编辑的后台,选定Binary,这个才有我们需要的数据资料,其它列的可以删除。
然后通过添加自定义列,将Binary 的数据进行解析出来,自定义列的公式:=Excel.Workbook([Content])
解析后就会出现一个 Tabel 列,我们再点击分叉箭头进行拓展,可以全部为默认。
点击确定后,距离成功又更近了,下面这个步骤也是很重要的,因为这一步就是我们很关键的一步。
从下图我们可以看到,拓展后在 Binary 和 Table 之间多出来了一列,而这一列,就是每个工作簿里面的工作表名称及自定义区域的名称,也就是我们需要选择的数据来源范围。
如下图所示,再通过选择工作表名称,例如水果或蔬菜,或者是自定义区域,例如 shuiguo 或 shucai ,就可以进行需要的工作表或指定区域数据合并。
例如我们选择工作表 -- 水果和蔬菜来进行合并,点击分叉的双箭头图标,其它可以为默认。
点击确定后,就出现了合并数据的预览,每个工作簿里面的数据就都呈现出来了。
而如果我们选择自定义的工作区域合并,例如选择 shuiguo 和 shucai,那么也会得到我们想要的结果。
最终再对数据进行清洗出来,最后上载,再加入切片器,或者使用数据透析,就可以很方便了解合并后的数据资料情况了。
怎么样?是不是很简单很方便呢?赶紧实操练习一下吧。
(期待您的关注、转发和点赞。图片来自网络,如有侵权,请联系删除。)
2020-6-9