用 Excel 爬取網路資料的四個小案例

數據分析那些事

9 min readAug 15, 2022

文章來源：數位管理

平時我們使用 Excel，經常是處理本地的資料，你懂得 Excel 的排序，篩選，條件格式，透視表，圖表等…… 但你可能不懂 Excel 怎麼獲取網頁上的資料！

你可能以為一定要寫程式碼才能實現，其實除了程式碼外，Excel 還配有一些現成的功能，方便你不用程式碼也能獲取網頁的資料！

本文就給大家介紹一下，不寫程式碼也能用 Excel 獲取網頁資料的一些方法。

函式法

自 Excel 2013 版本開始，就推出了一些 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 中！

而且你還可以重新整理這個表格，同時支援手動重新整理和自動重新整理，非常的方便～

這個方法要注意的問題是，並不是所有的網頁都可以獲取到資料，因為你不一定能拿到真實的網頁地址，所以你要儘量找到最深入的那個地址。你有辦法爬，別人也有辦法反爬，所以持續地獲取網頁的資料，就需要密切關注網頁是否有變化~

Power Query 獲取網頁資料

自從 Excel 2016 開始，PQ 就整合在 Excel 中，並且計劃用它來替換舊版的資料查詢獲取功能，所以用 PQ 也可以幫我們獲取網頁的資料！

因為動圖太大，無法上傳，所以我就用截圖去說明步驟：

1、點選上面的“自網站”，輸入網址，按下“確定”

2、在出現的導航器中，點選 Table 開頭的圖示，右側就會有這些表格的預覽，選擇你要匯入的表格，按右下角的“載入”

3、然後就能獲取表格資料到 Excel 了，也是支援手動重新整理和自動重新整理的！

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 12 ‘ 按 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