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

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

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

一、原始資料規則

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

VBA自動化整理:分析原始資料,依規則標記報表 1

二、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自動化整理:分析原始資料,依規則標記報表 3

三、程式寫入資料

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

VBA自動化整理:分析原始資料,依規則標記報表 5

四、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個儲存格,這是配合第一步驟所分析的資料規則。

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

VBA自動化整理:分析原始資料,依規則標記報表 7

五、程式標記保留行

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

VBA自動化整理:分析原始資料,依規則標記報表 9

六、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迴圈事件的作用和妙用。

VBA自動化整理:分析原始資料,依規則標記報表 11

七、批次資料標記

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

VBA自動化整理:分析原始資料,依規則標記報表 13

大數據資料分析標記

這一節是以書籍排行榜為範例,介紹大量資料結構分析,運用VBA迴圈事件依照規則標記目標對象。熟悉了這個過程之後,不管實際工作上遇到的案例為何,相信讀者都能夠著手進行。當然狀況有可能更為複雜,標記之後當然是要進一步處理,這些在之後會再繼續介紹。zanzan.tw © 2022

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