VBA自動化整理：分析原始資料，依規則標記報表

VBA自動化整理資料首先是分析規則，然後設計相應程式。本文以爬蟲所取得報表為例，介紹如何於工作表儲存格中寫入文字，依照規則批次標記大量資料。

一個完整網頁必然會有很多內容，包括網站介紹、產品分類、服務說明等，通常網站內容越多，越是需要有一個布局架構，瞭解這個布局後才能進一步活用。本節介紹如何分析所取得網頁資料的整體規則，進而設計對應的執行程式。

一、原始資料規則

以博客來排行榜資料為例，每個排行是以TOP1、TOP2接續下去，每個TOP規則一致，都是6行，這6行再以計算公式分析，以六行中的第一行作為基準，0是排行，1是書名，4是作者，5是價格。這樣子就完成了100筆排行資料的分析，接下來是如何設計程式了。

二、VBA對象語法

Worksheets(“20200808”).Cells(94, 5).Value = “保留行”

Worksheets(“20200808”)指的是名稱為「20200808」的工作表，Cell是儲存格對象，Cell(94,5)表示第94列、第5欄的儲存格，在Excel工作表的座標為94E，Value是儲存格值。因此綜合起來，「Worksheets(“20200808”).Cells(94, 5).Value = “保留行”」意思是在20200808這張工作表的94E儲存格輸入「保留行」這個文字。

三、程式寫入資料

VBA執行「儲存格輸入資料1」，果然在特定工作表特定儲存格出現「保留行」。由於之後會統一將整個工作表清理，這裡依照需求標記保留行，表示是想保留的資料，在後來執行程式清理工作表的過程中，預先標記好的會被保留下來，其餘刪除。

四、VBA宣告變數

先前是特定文字和列欄座標指定工作表和儲存格，在此用變數方式指定不確定的工作表和儲存各，這樣無形中了擴充程式的執行範圍，作為之後大規模執行作準備。

Dim Today1 As Date, TOP As Integer

Today1 = Date

Today2 = Format(Today1, “yyyymmdd”)

TOP = 94

以上程式先前章節有介紹過了，TOP=94是直接賦予變數值特定值。

Worksheets(Today2).Cells(TOP, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 1, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 4, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 5, 5).Value = “保留行”

在此變數指定工作表和儲存格。變數Today2已經以VBA函數設定當天的日期，也就是當天執行網路爬蟲取得資料的工作表名稱，Worksheets(Today2)便是那張工作表。

另外這裡的變數TOP的值為94，所以Cells(TOP, 5)便是工作表上第94列、第5欄的儲存格，Cells(TOP, 5).Value= “保留行”是在這個儲存格寫入「保留行」文字，後面的TOP+1、TOP+4、TOP+5是以(TOP,5)為起點往下第1、第4、第5個儲存格，這是配合第一步驟所分析的資料規則。

這裡的程式分成兩大階段，第一階段是設定變數，第二階段是輸入資料，兩個階段都用「’文字說明」的方式在第一行作說明。當設計的程式越來越複雜時，建議可以維持這樣的分段和說明的習慣，整個程式容易閱讀理解，更重要的是，有問題方便自行偵錯。

五、程式標記保留行

執竹程式後，果然在第一本書的資料區域，TOP1、書名、作者、優惠價四行在第E欄都標記了「保留行」，這就是之後整個工作表全部程式清零後要保留的資料行。

六、For Next迴圈事件

排行榜有100項，程式設計上不可能像第四步驟那樣寫100次，這樣的話又何必設計程式呢。在第四步驟已經將工作表和儲存格設定為不確定變數，在這裡可以輕鬆運用VBA迴圈事件，依照規則順序把第四步驟的輸入資料執行100次。

For i = 1 To 100

TOP = 94 + (i – 1) * 6

Worksheets(Today2).Cells(TOP, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 1, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 4, 5).Value = “保留行”

Worksheets(Today2).Cells(TOP + 5, 5).Value = “保留行”

Next i

建立一個I = 1 To 100的迴圈事件，TOP = 94+(i-1)*6是配合第一步驟的資料結構分析，i=1時，TOP是94，i=2時，TOP是100，i=3時，TOP是106，剛好是第一步驟標黃色的工作表列號，可以想見當i一直累加到100，便是100個排行榜項目的各個第一行。Cells(TOP, 5)、Cells(TOP+1, 5)、Cells(TOP+4, 5)、Cells(TOP+5, 5)則是各個排行的排行、書名、作者、價格。

這裡的迴圈事件是先設定i=1，執行For…Next中間的程式行，最後到了Next i，再跳回到For設定i=2，再執行一次，最後再到Next再跳回For。讀者稍微思考這裡相對簡單的程式和排行榜範例，應該可以瞭解VBA迴圈事件的作用和妙用。

七、批次資料標記

執行程式後，果然於工作表儲存格依照既定規則寫入資料100次。

大數據資料分析標記

每天學習，每天充電：VBA爬蟲專案文章合集。

本文內容取自《人人都學得會的網路大數據分析入門》，書本仍然是最好的學習方法，省下一張電影票的錢，今天就買本書吧！

發佈日期： 2022-01-23
文章分類： VBA爬蟲專案