快来学习 Power Query 吧，屌爆了的Excel加载项！

张文洲

今天开始介绍一点新的知识，关于微软新发布的加载项——Microsoft Power Query for Excel（前身是Data Explorer 数据浏览器）

这是一个免费插件，你可以从以下链接下载到：

中文预览版（for excel 2010&2013）     

英文版（for for excel 2010&2013）  

系统要求：

支持的操作系统Windows 7, Windows 8, Windows 8.1, WindowsServer 2008, Windows VistaWindows Vista（需要 .NET 3.5SP1）Windows Server 2008（需要 .NET 3.5 SP1）Windows 7Windows 8Windows 8.1不支持XP

支持以下 Office 版本：Office 2010 professional SP1 Office 2013
PowerQuery 在功能区有单独的选项卡，利用它我们可以导入、转置和合并来自多种不同数据源的数据。除了使用标准数据源如excel数据列表、Access&Sql数据库、Text文件，还可以是来自Active Directory活动目录、 Azure云平台、Odata开源数据和Hadoop分布式系统等多种来源的数据。
以下介绍使用PQ的5个具体示例
示例1：基本应用

安装PQ加载项以后，在功能区你能看见新的选项卡，获取外部数据分项里面有很多数据来源可供选择，这个基础示例中我选择从Web获取数据，有没有注意到我输入的网址是http://club.excelhome.net/forum-3-1.htmlExcelHome论坛函数与公式板块的链接地址。

输入网址，点击Ok稍等片刻后会出现以下视图，左边会列出所有查询到的表格，点击表格，右边会显示表格的预览数据。这里table0是公式与函数板块最上面4列顶置内容，table1是会员们的发帖数据，这里我选择第二个表。

当我选择列表某一列右键时，有很多菜单项，可以看见有很多我们熟悉的功能选项，具体在后面的帖子再细说，这里我选择使用第一行作为标题，当然也可以自定义行标题。

然后删除不想要的列，如第1列和第4列

经过以上步骤，我们可以发现视图右边显示了我们刚才的操作步骤（以命令语句形式列示，有点类似于Photoshop的历史记录），同时我们也发现上方有个公式编辑栏，里面自动生成公式语句，具体我们不需要了解，先跳过。

经过以上操作，点击Done，我们会在excel中得到列表数据，并且发现功能区多了一个活动选项卡-表格工具-查询。点击里面的刷新，我们可以随时更新表格数据，与网上保持同步。补充：这些数据仅保留数值，相应的链接没有保存；有了这些数据，你可以设置条件格式进行进一步的分析哦！

张文洲

示例2：

在查询的过程中进行统计然后返回结果

例如我想查询公式与函数板块第一页每个会员发帖数量

我先按示例1方法查询数据，仅保留发帖作者一列

因为作者中还包含了日期选项，我想删除日期导致的差异，如excelhome 我想看成一个作者，怎么办？看看右键有什么功能可选-分列！按照 – 分隔符分列，得到三列数据，虽然第一了包含了年份，不过已经不影响我统计次数了。

删除第2、3列以后，接着进行分组统计，到这里是不是觉得很眼熟，跟sql查询有点像

看看，还可以对不同的依据进行分组

点击ok看看结果，完成。记得可以筛选掉作者名为空的记录。

张文洲

示例3：

还在为获取文件夹（含子文件夹）下所有文件名列表头疼么？

这次选择从文件夹获取数据

看看，惊喜出现了

有文件属性一列attributes与其他列不同，点击看看，哇，原来另有玄机啊，这里我选择了文件分类kind和文件大小size

看看，多么的便捷，你可以通过筛选，选择你想要的文件类型、创建日期范围、文件大小、文件路径等等。。。。在这里，VBA什么的都弱爆了！

张文洲

示例4：

将类似于数据透视表的二维数据转换为一维列表

我们知道数据透视表有行区域和列区域，要想把这种结构的二维数据转换为一维数据列表，只需要双击透视表总计单元格，就可以得到明细数据了。但是，如果不是数据透视表，只是普通数据区域，要转换就复杂了。

一般会用到三种方法：

1.直接剪切后面的列至记录下方，并补充左边空白区域，重复操作。对于后面要转换的列数不多的情况，快捷简单。问题是不能自动更新，而且破坏数据源。

2.如果要转换的区域只有第一列是文本型数据，其他列是数字、货币、日期等类型的数据，可以采用多重合并区域数据透视表进行转换。优势是可以自动更新，缺陷是只能在只有一列是文本型数据的情况下使用。

3.VBA操作，可以实现动态更新，且不破坏数据源。不足是要使用者有VBA编程基础。

以下使用PQ功能，操作简单有效选择要转换的数据列表，使用PQ从列表获取数据，选择要反转的所有列，这里我想把语文、数学、英语转换为单独一列，把成绩单独放置在一列，右键-逆透视，看吧，碉堡了！

修改最后两列为自定义名称。看吧，果断秒杀前面三种方法了。

张文洲

示例5：

合并和连接工作表

两张同样结构的表合二为一：我有三张表，两张成绩表，一张学生学籍表，结下来先把两张成绩表合成一张表，再把这张合并的成绩表与学籍表连接到一起，姓名作为连接字段。

首先新建一个工作簿，依次以Excel文件为数据源，创建三个单独的查询


然后通过“ 追加”功能将查询1和查询2合并为一个查询，这个新的查询被命名为Append1

接下来将Append1和学籍查询3连接在一起，连接字段设置选择姓名


点击Ok后发现学籍、序号、籍贯等列没有出现，不要紧，点击最后一列右上角小标记，看看

选择要添加的字段，这里我选择序号、年龄、籍贯，姓名左边已经有了，不用重复选择

发现新添加的三列字段名都有前缀，这里可以进行修改，并左右移动各列的顺序，最后完成。

张文洲

Microsoft Power Query for Excel是一项功能强大的数据查询加载项
这里只是简单介绍了的其基本功能还有很多具体设置和使用技巧，有待大家自己去摸索，相信它会让你爱不释手。


需要说明的是，这个不支持Xp系统，只能在office2010sp1（及以后） office2013中使用，使用这个做的查询表，在没有安装此加载项的电脑上，无法实现数据刷新。
此外，如果一个电脑上同时安装不同版本的excel，使用此插件时，有可能会在关闭excel时生成某些副本文件（具体还不清楚）。

使用中如果有什么疑问，大家可以一起探讨，分享经验。
我也是刚接触这个，边学边卖，有问题的地方，大家多担待。



更多视频操作教程.rar (170 Bytes, 下载次数: 1160)

2016-9-1 11:24 上传

点击文件名下载附件

liucqa

嗯，果断秒杀目前的劣质插件

张文洲

使用Power Query 以xlsx文件为数据源，导入数据的时候，即便某个工作表被隐藏（深度隐藏），并且工作簿被保护，依然能发现隐藏的数据，包括宏表。
但是导入xls文件，貌似不能发现隐藏数据。

zylthu2007

楼主能不能提供插件下载，您提供的微软的插件下载网页不能使用了，多谢啦~

Zero_GtA

诶 很强大的功能 新人学习下