VBA重新建構分析報表:文字函數與程式流程控制

VBA取得資料的最終目的是進行分析,本文介紹如何利用程式內建文字函數以及迴圈判斷控制,將爬蟲資料自動化整理新增欄位,成為可以分析的報表狀態。

上一節整理好了報表格式,即使如此,報表內容本身可能不適合進行統計分析。例如每一天的排行榜報表沒有日期欄位,無法依照日期彙總,「TOP1」的數值意義為1,前面有「TOP」卻會將資料類型變成文字,又例如「作者:詹姆斯.克利爾」,前面的「作者:」雖然適合網頁呈現,分析報表時通常不需要。諸如此類的情形,有必要依照分析需求進一步處理報表欄位,本節具體介紹這個過程。

一、Excel函數公式

沒有VBA程式的話,通常是設計Excel函數公式,在此簡單說明範例如何設計公式:

日期:「=TODAY()」,注意它是取得目前電腦系統的日期,所以到了明天就會變了。

排行:「=RIGHT(B2,LEN(B2)-3)」,先LEN函數計算長度,再以RIGHT函數取得右邊三位文字。

作者:「=RIGHT(E2,LEN(E2)-3)」,和排行的函數公式一樣。

價格:「=IFERROR(MID(G37,FIND(“折”,G37)+1,LEN(G37)-FIND(“折”,G37)-1),MID(G37,FIND(“:”,G37)+1,LEN(G37)-FIND(“:”,G37)-1))」。價格的函數公式較為複雜,原始資料大部份是「優惠價:79折261元」,分析時只要其中的「261」,所以要先以FIND函數確定「折」的位置,而後面都是「元」,因此以MID函數取得中間的價格數字。

遇到少部份沒有打折的「優惠價:780元」情況,先以IFERROR判斷是否有此情形,有的話稍微修改公式,以「:」定位取得價格數字。

VBA重新建構分析報表:文字函數與程式流程控制 1

二、Private模組變數

隨著本書程式碼逐漸變多,所定義的變數增加,而且為了方便說明起見,目前章節是依次擴充程式,有很多共用變數,不想要每次都在Sub設定一次相同變數,這裡介紹可以在一個模組的最前面以Private宣告變數,如此宣告的變數是同一個模組所有Sub程序共用的。

Columns(1).Insert:在第一欄插入一欄。

其他的VBA程式先前章節都有介紹過,不再特地說明,這個Sub程序會在最左邊新增一欄,報表範圍內將該欄每一個項目都輸入目前日期。

VBA重新建構分析報表:文字函數與程式流程控制 3

三、程式新增欄位

執行程式後,報表最前面增加了一個日期欄位,資料內容都是今天。

VBA重新建構分析報表:文字函數與程式流程控制 5

四、VBA文字函數

程式中使用到VBA的Len和Right函數。Excel函數是全大寫英文,VBA是首字大寫,雖然寫法有點不同,這兩個函數剛好在Excel和VBA是同名同義。本節第一個步驟是Excel函數公式,這裡是VBA程式,兩者所使用函數相同,思惟架構也是一樣的,很適合作為VBA函數用法的學習範例。

迴圈1是把文字轉換的過程分行進行,迴圈2是把整個過程結合在一起,這個和設計Excel函數公式時是一樣的,可以分欄位切割計算,也可以使用組合公式把所有函數連在一起。如果對於VBA函數熟悉了,為精簡程式碼可以直接合併即可,這樣的函數思惟在Excel和VBA也是共通的。

VBA重新建構分析報表:文字函數與程式流程控制 7

五、VBA文字處理

VBA程式執行後,雖然看起來和第一步驟的Excel函數公式相同,兩者還是有所差別,這個在本節結語時補充說明。

VBA重新建構分析報表:文字函數與程式流程控制 9

六、流程控制設計

針對第一次出現的VBA指令說明如下:

Call 分析欄位設置1: Call 分析欄位設置2:這裡其實應該是兩行程式碼:Call 分析欄位設置1」和「Call 分析欄位設置2」,分別是呼叫執行「分析欄位設置1」和「分析欄位設置2」程序。因為這兩行程式碼相對較簡短,用「:」合併為一行,後續如果有已經介紹的程式,為節省篇幅也會採用「:」合併。

If Then…Else…End If:先前章節介紹過VBA If Then…End If語句,這裡多了一個Else,作用和第一步驟的Excel IFERROR相同,當條件不成立時,執行Else和End If之間的程式。

Range(“B:B,E:E,G:G”).Delete:先前章節有介紹過Columns(i).Delete單獨刪除一整欄,這裡因為要把已經被轉換成功的欄位刪掉,原來的「排行」、「作者」、「價格」分別在B、E、G欄,因此使用Range選取分散範圍再執行刪除,也許第一次看到這樣的指令有點陌生,但就英文語句而言應該不難理解其文法。

VBA重新建構分析報表:文字函數與程式流程控制 11

七、分析報表完成

執行後會把重點放在第37列,因為其他排行榜的書都有打折,就這本書沒有打折,所以在程式裡特別設定對應的規則,這裡可以確認VBA程式已經進行判斷並且完美處理好了。

VBA重新建構分析報表:文字函數與程式流程控制 13

Excel函數與VBA函數

VBA程式會直接將執行結果寫入Excel儲存格,Excel函數則是會把公式一直留在儲存格中,因此如果是Excel函數的話,每次工作表有任何操作,所有儲存格會再重算一次,而且報表有結構可能公式會亂掉,從這個角度而言,VBA函數是相對較有效率而且穩定。

完整學習:VBA自動化文章合輯

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

VBA重新建構分析報表:文字函數與程式流程控制 17
歡迎加入Line社群,口袋裡的VBA小教室!