云发教育

标题: 快来学习 Power Query 吧，屌爆了的Excel加载项！ [打印本页]
作者: 孤独的兔子    时间: 2021-2-19 03:43
标题: 快来学习 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获取数据，ExcelHome论坛函数与公式板块的链接地址。输入网址，点击Ok稍等片刻后会出现以下视图，左边会列出所有查询到的表格，点击表格，右边会显示表格的预览数据。这里table0是公式与函数板块最上面4列顶置内容，table1是会员们的发帖数据，这里我选择第二个表。当我选择列表某一列右键时，有很多菜单项，可以看见有很多我们熟悉的功能选项，具体在后面的帖子再细说，这里我选择使用第一行作为标题，当然也可以自定义行标题。然后删除不想要的列，如第1列和第4列经过以上步骤，我们可以发现视图右边显示了我们刚才的操作步骤（以命令语句形式列示，有点类似于Photoshop的历史记录），同时我们也发现上方有个公式编辑栏，里面自动生成公式语句，具体我们不需要了解，先跳过。经过以上操作，点击Done，我们会在excel中得到列表数据，并且发现功能区多了一个活动选项卡-表格工具-查询。点击里面的刷新，我们可以随时更新表格数据，与网上保持同步。补充：这些数据仅保留数值，相应的链接没有保存；有了这些数据，你可以设置条件格式进行进一步的分析哦！
作者: c4526    时间: 2021-2-19 03:52
示例2：在查询的过程中进行统计然后返回结果例如我想查询公式与函数板块第一页每个会员发帖数量我先按示例1方法查询数据，仅保留发帖作者一列因为作者中还包含了日期选项，我想删除日期导致的差异，如excelhome 我想看成一个作者，怎么办？看看右键有什么功能可选-分列！按照 – 分隔符分列，得到三列数据，虽然第一了包含了年份，不过已经不影响我统计次数了。删除第2、3列以后，接着进行分组统计，到这里是不是觉得很眼熟，跟sql查询有点像

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


点击ok看看结果，完成。记得可以筛选掉作者名为空的记录。
作者: jimmy    时间: 2021-2-19 03:54
示例3：还在为获取文件夹（含子文件夹）下所有文件名列表头疼么？这次选择从文件夹获取数据

看看，惊喜出现了

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

看看，多么的便捷，你可以通过筛选，选择你想要的文件类型、创建日期范围、文件大小、文件路径等等。。。。在这里，VBA什么的都弱爆了！
作者: 皮蛋妹妹    时间: 2021-2-19 03:58
示例4：将类似于数据透视表的二维数据转换为一维列表


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

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


 (, 下载次数: 7)