Martin

V1

2022/08/04阅读:12主题:自定义主题1

用 Excel 爬取网络数据的四个小案例

平时我们使用 Excel,经常是处理本地的数据,你懂得 Excel 的排序,筛选,条件格式,透视表,图表等…… 但你可能不懂 Excel 怎么获取网页上的数据!

你可能以为一定要写代码才能实现,其实除了代码外,Excel 还配有一些现成的功能,方便你不用代码也能获取网页的数据!

本文就给大家介绍一下,不写代码也能用 Excel 获取网页数据的一些方法。

函数法

自 Excel2013 版本开始,就推出了一些 Web 函数,其中有 2 个函数配合使用,就可以帮助我们获取网页的数据到 Excel

FILTERXML(xml, xpath) 从 XML 数据中返回指定的数据,网页上的数据很多都是以 XML 的形式存在,这个函数就是用于提取 XML 中的指定信息。

WEBSERVICE(url) 返回 Web 服务中的数据,你可以理解为可以获得一堆的 XML 字符串。

例如下面的这个公式,就是使用有道翻译的网站,可以获取翻译结果:

=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&B2&"&doctype=xml&version"), "//translation")

如果你复制以上公式到 Excel 后,你只需要在 B2 单元格输入你要翻译的内容,公式的运算结果就会返回翻译的结果给你。

用函数获取网络数据
用函数获取网络数据

是不是觉得超级方便!但使用这个公式也需要注意,必须保证你的电脑能正常联网,如果有道翻译调整了网页代码,或者网址的入口改变了,那么上面的这个公式也会失效,需要重新去研究逻辑再写公式~

Query Table 获取网页数据

在 Excel 2016 以前,一直有一个Query Table获取网页数据的功能,但在 Excel 2016 推出 PQ 后就隐藏起来了,但是仍然可以通过手工调出来!

手工调出来的方法可以从「选项」中的「数据」里去设置,也可以直接通过快捷键 Alt → D → D → W(注意:不需要同时按,只需按顺序按下去即可!),然后就会弹出以下的界面:

打开目标网页
打开目标网页

然后我们只需要输入网页地址,图中演示的是 http://quotes.money.163.com/trade/lsjysj_600519.html#06f01 这个地址。

这个工具就能够帮我们识别网页中的表格,然后我们只需要选中表格(点黄色箭头去选),点右下角的导入,然后就可以把数据导入到 Excel 中!

而且你还可以刷新这个表格,同时支持手动刷新和自动刷新,非常的方便~

可刷新的 Query Table
可刷新的 Query Table

这个方法要注意的问题是,并不是所有的网页都可以获取到数据,因为你不一定能拿到真实的网页地址,所以你要尽量找到最深入的那个地址。你有办法爬,别人也有办法反爬,所以持续地获取网页的数据,就需要密切关注网页是否有变化~

Power Query 获取网页数据

自从 Excel 2016 开始,PQ 就集成在 Excel 中,并且计划用它来替换旧版的数据查询获取功能,所以用 PQ 也可以帮我们获取网页的数据!

自网站获取数据
自网站获取数据

因为动图太大,无法上传,所以我就用截图去说明步骤:

1、点击上面的“自网站”,输入网址,按下“确定”

填入网址
填入网址

2、在出现的导航器中,点击 Table 开头的图标,右侧就会有这些表格的预览,选择你要导入的表格,按右下角的“加载”

选择要加载的表格
选择要加载的表格

3、然后就能获取表格数据到 Excel 了,也是支持手动刷新和自动刷新的!

pq获取网络数据
pq获取网络数据

VBA 代码法

VBA 的方法当然也可以,而且是最后压轴出场!使用 VBA 获取网页数据,首先当然是可以和上面的三种方法去结合使用,因为写代码意味着你可以判断,可以循环。也就是说像上面的股票收盘价信息,你就可以通过循环获取多个股票的数据了,会更方便!

对于零售行业来说,天气是很重要的因素,会影响客流,会影响应季货品的销售,所以结合历史天气数据进行分析是很有必要的!那怎么批量地获取天气数据呢?

这就需要用到 VBA 代码了,以上的办法都不适合大批量地获取数据,不会代码?没关系,我已经写好了,而且也有解释,大家只要稍作修改即可使用,用 VBA 调用 Query Table 来实现:

Sub Tianqi()
    Dim str As String
    On Error Resume Next
    Cells.Delete
    t1 = Time: n = 1
    For i = 2022 To 2022 '设置要获取的年份区间
        For j = 1 To 1 ' 按1 -12 月进行循环
            If j < 10 Then '给小于10的月份前补数字0(网址需要)
                t = 0
            Else
                t = ""
            End If
            str = i & t & j
            If i = Year(Date) And j > Month(Date) Then Exit For ' 如果时间大于本月则推出循环,主要是为了不循环今年的后几个月
            
            With ActiveSheet.QueryTables.Add("url;http://www.tianqihoubao.com/lishi/beijing/month/" & str & ".html", Range("a" & n)) '天气后报的网址,如果要爬某个城市的天气请把“beijing”改成对应的城市拼音即可
                
                .WebFormatting = xlWebFormattingNone ' 不包含格式
                .WebSelectionType = xlSpecifiedTables '指定table模式
                .WebTables = "1" '第1张table
                .Refresh False
            End With
            n = Cells(Rows.Count, 1).End(3).Row + 1
        Next
    Next
    Columns("A:D").Select
    ActiveSheet.Range("$A:$D").RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo '删除重复项
    Range("C:C,D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove '插入空行
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分列
    Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分类
    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '分类
    ColumnCells.Replace " ", "", 2 '去掉空格
    Cells.Replace "℃", "", 2 '去掉℃
    Range("B1:G1") = Array("白天天气", "夜晚天气", "最高气温", "最低气温", "白天风", "夜晚风")
    Columns.AutoFit
    Range("A1").Select
    str1 = Time - t1
    MsgBox Format(CDate(str1), "hh:mm:ss")
End Sub

运行效果图如下:

vba获取天气数据
vba获取天气数据

以上就是用 Excel 获取网络数据的四种方法,希望文章对你有帮助。

分类:

后端

标签:

大数据

作者介绍

Martin
V1