這裡示範如何使用 Excel 的 WEBSERVICE 自動下載網路上的資料,並藉由 FILTERXML 函數萃取出有用的資訊,即時顯示於 Excel 表格內。

許多人應該都聽過網路爬蟲程式,它可以自動下載網路上的資料,並萃取出有用的資訊,幫使用者省下手動抓資料的時間,而在 Excel 中其實也有內建類似的功能,只不過使用上需要一些技巧,以下是在 Excel 中開發爬蟲程式的簡單教學。

WEBSERVICE 下載網頁函數

在 Excel 中如果想要把網路上及時的資料下載下來,放進 Excel 的報表中,可以使用 WEBSERVICE 這個網路服務函數,它可以接受任何的 URL 網址,自動將網頁內容擷取下來:

=WEBSERVICE(網址)

例如在行政院環境保護署環境資源資料開放平臺上面,可以查到即時的紫外線觀測資料,XML 格式的資料會類似這樣:

紫外線觀測資料 XML 檔

若要在 Excel 中把這個即時的紫外線觀測資料抓下來,就把 XML 檔的網址放進 WEBSERVICE 函數中即可:

=WEBSERVICE("http://opendata2.epa.gov.tw/UV/UV.xml")

以 WEBSERVICE 函數抓取資料

不過由於大部分的網路資料 API 所提供的資料都是 XML、CSV 或 JSON 這類的編碼格式,所以若使用 WEBSERVICE 抓下來之後,直接放在 Excel 表格中,就會像這樣不太好閱讀,若想要產生比較乾淨的報表,就需要再加上一些處理。

FILTERXML 解析 XML 函數

Excel 的 FILTERXML 函數可用來解析複雜的 XML 結構資料,快速從大量的 XML 資料中取出有用的資訊,有了這個函數我們就可以在不需要自己寫 VBA 程式的情況下,解析各種 XML 資料,其用法如下:

=FILTERXML(XML資料,XPath路徑)

通常 XML 資料的部分就直接放置 WEBSERVICE 抓回來的結果,然後再使用 XPath 路徑指定要萃取的資訊。

以上面的紫外線觀測資料範例來說,我們可以使用 /UV/Data[1]/County 這個 XPath 把 XML 中第一筆資料的縣市名稱抓出來:

=FILTERXML(WEBSERVICE("http://opendata2.epa.gov.tw/UV/UV.xml"),"/UV/Data[1]/County")

接著再用 /UV/Data[1]/UVI 這個 XPath 抓出紫外線指數:

=FILTERXML(WEBSERVICE("http://opendata2.epa.gov.tw/UV/UV.xml"),"/UV/Data[1]/UVI")

這樣就可以非常精準的把有興趣的資訊抓出來,放在 Excel 表格中了:

解析 XML 資料結果

由於在 Excel 中現成可用的解析工具只有適用於 XML 檔的 FILTERXML 函數,若遇到 JSON 或 CSV 檔的話,就真的要寫 VBA 程式了,所以如果自己要下載的資料有 XML 格式可用的話,就盡量選擇 XML 格式,會方便許多。

參考資料:analystcave.com