在我们实际工作中,经常会碰到这样的问题,比如有相同结构的数据表,有多人来完成,最后把每个人完成的数据表汇总到一张表。或者,我们每年做一个数据文件,最后把各年的数据文件汇总到一张表。此类问题,原来都用VBA或者SQL语句来解决,复杂的编程语句总是让大家看着吃力又难记。现在好啦,我们只需要点击鼠标即可完成,这就是Power BI系列中的Power Query,因为简单高效,更加得到大家的青睐。OK,今天就给大家分享使用Power Query来解决这个问题。
在使用之前,首先要看看你的Excel版本,在Excel2016版本中,微软已经把Power Query嵌入到Excel中,所以安装了Excel2016版的小伙伴们可以直接使用Power Query功能。使用Excel2010或Excel2013版的小伙伴们也不用担心,可以在官网下载Power Query插件,安装后就可以使用了。
https://www.microsoft.com/zh-CN/download/details.aspx?id=39379根据自己电脑上Office 是32位还是64位来选择相应的Power Query下载安装。插件安装完成后就可以看到Excel中多出一个Power Query选项卡:
而Excel2016版不需要安装插件,在“数据”选项卡下可以看到功能区上有“获取和转换/新建查询”功能,和Power Query插件显示界面稍有不同,但是功能一样。
把以上工作准备就绪,我们就可以使用Power Query来汇总文件了。
假设我们有一个订单文件夹,里面有三年的订单,如图所示:
新建一个工作薄,分别单击“数据/新建查询/从文件/从文件夹”。
从字段"Name"下我们可以看到三年的订单文件已经调入:
不过,"Content"字段下显示的是"Binary", 即二进制数据。二进制数据不能直接提取,所以我们还需要添加一个自定义列,写一条公式。在【添加自定义列】对话框中,【自定义列公式】处输入:
=Excel.Workbook([Content],true)函数 Excel.Workbook,首字母要大写;
第一个参数Content 是需转换的二进制字段名称,不用手动输入,可双击右侧“可用列”中的Content添加;
第二个参数True, 逻辑值,表示原数据有标题行。
确定后,可看到多出一列"Custom", 数据类型是"Table":单击"Custom"右侧的展开按钮,选择“扩展”,取消“使用原始列名作为前缀”对勾,按“确定”。"Data"字段下显示类型还是"Table",我们需要把"Table"再扩展。单击"Data"右侧的展开按钮,选择“扩展”,依然取消“使用原始列名作为前缀”,按“确定”。
整理数据
再把数据类型转换一下,特别是“订单日期”数据类型要选择“日期”。
单击“开始/关闭并上载”。
这个合并的数据表,是一个“查询”表,原文件夹下的数据更改了,只要“刷新”一下这个数据表,所有数据将随之更新。是不是简单又高效?在这里使用Power Query真的比用VBA和SQL语句要简单很多呢!
好啦,将Excel多个文件汇总到一张表,今天就给大家分享到这儿了,希望小伙伴们都能Get到。
推荐您阅读更多有关于“ Excel file win10 win8 win7 ”的文章
评论列表: