您当前的位置:首页 > 文本与office

提升技能,学习Power Query导入互联网数据的过程和方法,制作更专业的Excel电表格

时间:05-21来源:作者:点击数:

随着互联网技术的发展和大数据的形成、逐步开放,使我们有机会获取更多信息。Excel早在2007版本中就提供获取网站数据的功能,今天主要介绍使用Power Query如何获取网站的信息,以及是如何实现自动获取最新数据的。

本文以国家统计局公布的《中华人民共和国2019年国民经济和社会发展统计公报》(http://www.stats.gov.cn/tjsj/zxfb/202002/t20200228_1728913.html)的信息为外部数据来源,通过获取其中的表格数据来介绍和学习Power Query的工作过程和方法。报告中对外公布了包括人口数及其构成、居民消费价格比上年涨跌幅度等共16张表格数据。本文主要涉及表格1(2019年年末人口数及其构成)和表格2(2019年居民消费价格比上年涨跌幅度)中的部分内容,获取截至2019年国内人口结构比例和2019年度衣食住行的消费价格变化。

连接外部网站

在数据选显卡中,点击“自网站”按钮,在弹出的页面中有两个选项:基本和高级。基本选项中只有一个参数(URL,即网站的地址或者网页的地址);高级选项中的内容比较多,而且专业化程度有些高。除了可以提供多个网址外,还需要提供必要的Http请求标头参数。通俗一点讲,基本选项是网站不需要知道你是谁,能提供的信息都会提供给你;高级选项是网站需要知道你是谁,以此来判断是否能够提供信息和能够提供多少信息给你。

通常来说,政府对外公开网站(网址中带有“gov”字样)、新闻门户类网站(新浪、搜狐、网易等)都属于前者;一些提供咨询的商业组织网站,以及协会、商会、行会、联盟等会员制网站都属于后者。如果使用高级选项,需要掌握一些计算机网络知识或者由专业人士协助。感兴趣的小伙伴可以了解一下Http请求报文和Http请求头参数。在本文实例中,使用基本选项即可。

图3

在弹出的窗口中,输入网址后点击确定。如图3。

解析网站内容

通过对网页的解析,我们看到网页中包含有16张表格(Table)(Document和Table0不涉及实质内容,不用考虑)。在左侧点击表格名称后,右侧都会显示对应的信息,包括表视图和Web视图。

图4

表视图就是将对应表格数据提取后显示的视图。如图4。

图5

Web视图就是被提取数据所在网页中原始视图。如图5。通过比较我们很容易发现,表格数据被完整提取出来了。

定制数据内容

在这16张表格中,我们提取表格1和表格2的内容。为此,选中左侧选择多项复选框。

图6

选中表格1和表格2,如图6。

此时可以直接将数据加载至工作表中,也可以点击“转换数据”对提取的数据作进一步的处理。如前文所述,本实例只获取表格1和表格2的部分内容,所以我们需要进行“转换数据”。

定制数据格式

点击“转换数据”后,进入到Power Query的主界面。

图7

在Power Query主界面中(图7),左侧列出了之前选中的两个表。在这里进行以下工作:

  1. 修改表格名称。右键点击“Table1”,在弹出的菜单中选择重命名,修改为“人口结构数及比例”。将“Tabel2”修改为“居民衣食住行价格”。如图8。

图8

2.删除人口结构数及比例中不需要的内容,包括城镇、乡村、男性、女性等指标。点击工具栏上的“删除行”后,选择删除间隔行。

图9

在弹出的页面中输入相关内容,如图9,点击确定。

图10

表格中只保留了我们要提取的数据,如图10。

3.重复上面的步骤,删除居民衣食住行价格中不相关的内容,结果如图11。

图11

4.确认无误后,点击工具栏“关闭并上载”。

加载数据到工作表

图12

数据被加载至当前的工作表中,具体数据如图12。图中右侧显示了当前查询的数量以及每个查询加载的行数。

自动更新设置

选择表格中的数据,点击查询选项卡后,点击“属性”按钮。

图13

如图13,在刷新控件中设置更新数据的方式。根据情况可以对刷新频率、刷新时机选择等进行设置。通常情况下,可以设置打开文件时刷新数据,这样保存后再次打开时会自动刷新数据。

图14

再次打开文件时如果出现如图14的警告,点击启用内容即可。

编辑数据格式

点击查询选项卡后,点击“编辑”按钮,再次回到Power Query的主界面对设置进行调整。

图15

如图15,在右侧的“应用的步骤”中,可以直接恢复到最初的表格格式和内容。

功能强大的Power Query

除上述功能外,Power Query还可以对查询结果的表格列进行编辑,可以删除列和增加列,还可以实现数据的行列转换。对任意列、文本列、编号列、日期&时间列、结构化列等各种类型的表格列提供了丰富的功能。

以上就是为大家介绍的使用Power Query导入网站数据的过程和方法,希望对提升Excel技能有所帮助。小伙伴们行动起来吧,一起感受一下Power Query的魅力。

方便获取更多学习、工作、生活信息请关注本站微信公众号城东书院 微信服务号城东书院 微信订阅号
推荐内容
相关内容
栏目更新
栏目热门