贊贊小屋

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例

Excel VBA網路爬蟲有固定流程,首先變數宣告,分析網址結構,建立For Next迴圈取得網頁資料,UsedRange.Rows.Count彙總資料、最後再以Cells.Font.Name調整格式,本文以借閱排行榜為例介紹。

上一節取得單一年度的資料,如果僅止於此,未免大費周章,不過,電腦程式的優點在於非常有效率且不出錯的機器運作,在利用Excel VBA取得網站資料亦是如此,這一節便進一步介紹如何一次取得多個年度的排行榜。

一、變數宣告

首先編寫VBA程式碼如下。這裡將變數宣告單獨做在第一個段落,在程式將會一大長串的時候,最好把所有變數統一集中,容易閱讀及檢查。另外這裡因為預知接下來會有很多年度,特定把年度作為新增工作表和輸入內容的變數處理。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第1張

二、新增工作表

新增了一個工作表「2008」,內容正是2008年的中文圖書排行榜。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第2張

三、年度迴圈事件

套用第四章第三節關於期間迴圈的程式結構,設計VBA如下:

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第3張

四、多年度排行榜

一鍵取得2009年到2016年的排行榜資料,每個年度一個工作表。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第4張

五、彙總資料、刪除標題、調整格式

最後是彙總所有年度的工作表資料彙總,如圖所示,在彙總之後,刪除多餘重覆的標題列,再次整理格式。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第5張

六、多年度資料彙總

整合起來的資料量相當大,從2008年第一名到2016年最後一個200名,總共有1815筆資料,以Excel群組指令將各個年度中間的資料折壘,方便大略與各個工作表初步驗證。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第6張

七、資料核對

援引會計實務上總帳與明細帳金額核對的觀念,把各個工作表的總次數羅列加總,再加和彙總報表的總次數對照,通常兩個數字一致就沒有問題。

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第7張

VBA網路爬蟲自動化時的考量

從本書目前所積累的VBA程式技術來說,其實也可以一次把九個年度的資料全都下載到同一個工作表,這裡採取分開取得再彙總的方式,考量有兩點,其一是Excel本身就不是專門為取得網頁資料所設計的軟體,大批量執行有可能會當機,所以保留彈性;其二是就資料整理和驗證而言,像這樣將工作表分開比較適合Excel的特性。

本文內容取自《人人做得到的網路資料整理術》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

Excel VBA網路爬蟲SOP,以多年度書籍借閱排行榜為例 VBA網路爬蟲 第8張