Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

炎炎夏日,突然灵机一动,我们是否可以使用excel获得上海历史若干年的天气数据呢? 想好了就动,先查查互联网吧! (注:今天的文章步骤和截图相对较多)

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

突然找到这么一家网站,看上去是包含我们想要的数据的

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

数据包含历史每一天的最高温,最低温,天气,风向和风力

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

观察URL构成 shanghaitianqi

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

如果选择了2017年1月的天气查询数据,URL就会变成这样子

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

那么可以简单推断,历史数据的获得可以通过以下通项:

Https://15tianqi.cn/[YEAR]shanghai[MONTH]yuetianqi/

 

接下来让我们开始“爬虫”吧!


 

步骤1,让我们打开一个Excel,并且选择从网络获取数据

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

这里在高级选项中把我们的URL中可变的部分(以后要作为参数)分割出来

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

点击链接来获得数据

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤2,选择table 0 并且点击编辑来编辑数据

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

可以看到数据包含一些说明行和分表的字段名。

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤3, 让我们做一些过滤,这里主要为了删除多余的行数

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤4, 用第一行作为表头,这样我们的数据表就有正确的表头了

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

再过滤掉其他重复的信息,因为日期已经作为表头了,那么其它行中的带有字符“日期”的行数就可以删除了!

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

阶段性成果好啦,看到我们已经获得了一个月(如最开始输入的 2017年1月)的数据

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤5, 我们根据气温的数据来制作两个新的字段,数据类型,方便以后运算

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

修改为数值型

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

最高温和最低温都修改出来 (这里PowerQuery会自动提取数值,很方便!!)

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

并且所有的调整步骤都会被记录下来(这也是为什么上篇文章说有了PowerQuery,可以告别VBA了)

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤6, 下来让我们来制作两个参数

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

分别命名为【年】和【月】,注意 !!类型 需要是【文本型】,因为要拼接URL用!!!

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤7, 双击查询的源(Source),来做修改

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

把刚才我们填写 2017 和 1 的地方分别修改成我们建立的参数【年】和【月】

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤8, 鼠标右键Table 这个查询,并创建函数

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

命名函数为 HDTQ (这里随意,我是取了【获得天气】的首字母)

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

创建完成可以看到多了一个函数列在查询侧边栏里面,并且有两个可以输入参数值的位置

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤9, 让我们用新的值, 2016年1月来测试以下有没有数据返回

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

可以看到数据正常获取到了

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

第二个阶段性成果好啦! 接下来我们要一次性获取更多月份数据了!

步骤10, 我们关闭Power Query 并返回到工作簿中-去创建我们需要使用的参数值列表!!

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤11, 创建两个表,包含你要获得的数据的年份和月份并且导入到Power Query中

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

导入以后的样子,会多两个表

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤12,我们需要把Year这张表和Month这张表强行并在一起

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤13,展开MONTH可以获得所有的列联结果

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

相当于Cross Join 的结果集

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤14,移除空值等不相干数据

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤15,对新生成的表中的数据使用我们之前创建好的函数HDTQ

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

按顺序选择好对应的字段名称来作为参数

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

有时候我们会遇到一些警告,可能是一些安全设置导致的

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

勾选忽略Privacy Level

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

这个时候我们就可以看到对应到每个月的数据表都顺利获得了

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤16,展开数据表我们就可以看到数据了

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

步骤17, 把数据导入到工作簿中,我们选择一个新的工作页

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

大功告成!! 可以看到我们想要的天气数据都顺利输出到Excel 工作簿中了

 

Excel技巧, 高效
Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

下来,你就可以利用这些数据来研究研究上海的历史天气,气温了。

 

 

本文:Excel技巧, 高效”爬虫”, 网页爬虫, Excel版网页爬虫, Excel抓取网络数据

 

 

 

Add a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.