随着互联网技术的发展和大数据的形成、逐步开放,使我们有机会获取更多信息。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),左侧列出了之前选中的两个表。在这里进行以下工作:
图8
2.删除人口结构数及比例中不需要的内容,包括城镇、乡村、男性、女性等指标。点击工具栏上的“删除行”后,选择删除间隔行。
图9
在弹出的页面中输入相关内容,如图9,点击确定。
图10
表格中只保留了我们要提取的数据,如图10。
3.重复上面的步骤,删除居民衣食住行价格中不相关的内容,结果如图11。
图11
4.确认无误后,点击工具栏“关闭并上载”。
图12
数据被加载至当前的工作表中,具体数据如图12。图中右侧显示了当前查询的数量以及每个查询加载的行数。
选择表格中的数据,点击查询选项卡后,点击“属性”按钮。
图13
如图13,在刷新控件中设置更新数据的方式。根据情况可以对刷新频率、刷新时机选择等进行设置。通常情况下,可以设置打开文件时刷新数据,这样保存后再次打开时会自动刷新数据。
图14
再次打开文件时如果出现如图14的警告,点击启用内容即可。
点击查询选项卡后,点击“编辑”按钮,再次回到Power Query的主界面对设置进行调整。
图15
如图15,在右侧的“应用的步骤”中,可以直接恢复到最初的表格格式和内容。
除上述功能外,Power Query还可以对查询结果的表格列进行编辑,可以删除列和增加列,还可以实现数据的行列转换。对任意列、文本列、编号列、日期&时间列、结构化列等各种类型的表格列提供了丰富的功能。
以上就是为大家介绍的使用Power Query导入网站数据的过程和方法,希望对提升Excel技能有所帮助。小伙伴们行动起来吧,一起感受一下Power Query的魅力。