ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 77359|回复: 86

[分享] 快来学习 Power Query 吧,屌爆了的Excel加载项!

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2013-11-6 22:57 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:其他官方插件
本帖最后由 张文洲 于 2013-11-9 02:16 编辑

今天开始介绍一点新的知识,关于微软新发布的加载项——Microsoft Power Query for Excel(前身是Data Explorer 数据浏览器)
这是一个免费插件,你可以从以下链接下载到:
系统要求:
支持的操作系统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中得到列表数据,并且发现功能区多了一个活动选项卡-表格工具-查询。点击里面的刷新,我们可以随时更新表格数据,与网上保持同步。补充:这些数据仅保留数值,相应的链接没有保存;有了这些数据,你可以设置条件格式进行进一步的分析哦!

评分

参与人数 14鲜花 +23 技术 +2 收起 理由
15810910132 + 2 太强大了
诗意地蛰伏 + 1 值得肯定
cleverzhzhf + 2 优秀作品
hugo_huang + 2 太强大了
ivy_zying + 1 太强大了

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-6 22:57 | 显示全部楼层
本帖最后由 张文洲 于 2013-11-6 23:00 编辑

示例2:
在查询的过程中进行统计然后返回结果
例如我想查询公式与函数板块第一页每个会员发帖数量
我先按示例1方法查询数据,仅保留发帖作者一列
因为作者中还包含了日期选项,我想删除日期导致的差异,如excelhome 我想看成一个作者,怎么办?看看右键有什么功能可选-分列!按照 – 分隔符分列,得到三列数据,虽然第一了包含了年份,不过已经不影响我统计次数了。
删除第2、3列以后,接着进行分组统计,到这里是不是觉得很眼熟,跟sql查询有点像

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


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

评分

参与人数 1鲜花 +1 收起 理由
ivy_zying + 1 太强大了

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-6 23:03 | 显示全部楼层
示例3:
还在为获取文件夹(含子文件夹)下所有文件名列表头疼么?
这次选择从文件夹获取数据


看看,惊喜出现了

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

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

评分

参与人数 1鲜花 +1 收起 理由
ivy_zying + 1 太强大了

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-6 23:08 | 显示全部楼层
本帖最后由 张文洲 于 2013-11-6 23:34 编辑

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



我们知道数据透视表有行区域和列区域,要想把这种结构的二维数据转换为一维数据列表,只需要双击透视表总计单元格,就可以得到明细数据了。但是,如果不是数据透视表,只是普通数据区域,要转换就复杂了。
一般会用到三种方法:
1.直接剪切后面的列至记录下方,并补充左边空白区域,重复操作。对于后面要转换的列数不多的情况,快捷简单。问题是不能自动更新,而且破坏数据源。
2.如果要转换的区域只有第一列是文本型数据,其他列是数字、货币、日期等类型的数据,可以采用多重合并区域数据透视表进行转换。优势是可以自动更新,缺陷是只能在只有一列是文本型数据的情况下使用。
3.VBA操作,可以实现动态更新,且不破坏数据源。不足是要使用者有VBA编程基础。
以下使用PQ功能,操作简单有效选择要转换的数据列表,使用PQ从列表获取数据,选择要反转的所有列,这里我想把语文、数学、英语转换为单独一列,把成绩单独放置在一列,右键-逆透视,看吧,碉堡了!

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


45.jpg


TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-6 23:16 | 显示全部楼层
本帖最后由 张文洲 于 2013-11-6 23:17 编辑

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




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


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




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


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

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

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





评分

参与人数 1鲜花 +2 收起 理由
bbm000000 + 2 太强大了

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-6 23:24 | 显示全部楼层
本帖最后由 张文洲 于 2016-9-1 11:25 编辑

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


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

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



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

TA的精华主题

TA的得分主题

发表于 2013-11-6 23:49 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-11-7 11:47 | 显示全部楼层
使用Power Query 以xlsx文件为数据源,导入数据的时候,即便某个工作表被隐藏(深度隐藏),并且工作簿被保护,依然能发现隐藏的数据,包括宏表。
但是导入xls文件,貌似不能发现隐藏数据。

TA的精华主题

TA的得分主题

发表于 2014-4-16 13:42 | 显示全部楼层
楼主能不能提供插件下载,您提供的微软的插件下载网页不能使用了,多谢啦~

TA的精华主题

TA的得分主题

发表于 2014-4-16 14:43 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-8-24 15:23 , Processed in 0.109237 second(s), 15 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表