Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬

Excel正規化是整理成資料庫的報表格式,本文介紹如何分析原始資料結構,VBA程式小規模測試之後,For Next建立迴圈事件﹐依照規則重新整理資料並調整欄寬。

本章到目前為止已經把所取得的多餘資料删除,剩下來的資料雖然都是所需要的,但不是很適合直接作為統計分析的報表,頂多只能說是取得篩選後的原始資料。這一節進一步介紹如何將資料重新排列組合,整理成容易處理的正規化報表格式。

一、資料正規化概念

資料庫程式設計裡面有個基本的資料表正規化的概念(Database Normalization),大意是將資料以規範好的格式呈現,程式比較好處理,Excel也可以沿用這個概念。

Excel在進行資料處理分析的時候,報表最好格式是第1列是標題列,都是標題欄位,第2列開始是一筆一筆的資料。上一節最後得到的報表顯然需要正規化,如圖所示,希望VBA能自動整理成規範格式。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 1

二、VBA ActiveSheet

先前有學過如何用vba程式ActiveSheet.Cells(y, x).Value在Excel特定儲存格寫入資料,在Excel資料正規化整理的過程中,可以利用這個簡單的方式執行。固定的標題列直接以文字寫入,排行榜TOP1和TOP2其實只是儲存格內容的複製貼上,等於是將特定儲存格內容指定另一個特定儲存格,亦即Cells(y, x).Value= Cells(y, x).Value的程式語法。注意到VBA預設是目前工作表,所以「ActiveSheet .」也可以省略,像這一個步驟的程式範例一開始沒有省略、後來省略,效果會是一樣的。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 3

三、程式小範圍測試

小範圍程式執行測試,果然像第1步驟那樣得到TOP1和TOP2正規化報表了。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 5

四、For Next迴圈

Excel可以設計兩個以上的函數組合成巢狀公式,VBA程式事件也是如此。本節範例有100項排行榜項目,每個項目相對應的4個資料值,因此設計雙迴圈事件:「For i = 1 To 100」和「For j = 1 To 4」,並且利用簡單的數學等差計算公式,讓原始資料依照正規化要求重新組合複製。

設計的數學公式:「ActiveSheet.Cells(i + 1, j + 2).Value = ActiveSheet.Cells((i – 1) * 4 + j, 1).Value」,也許一開始無法掌握如此設計的用意,可以利用先前步驟的方式,先以TOP1和TOP2模擬兩個迴圈變數i及j的執行過程,比較容易理解。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 7

五、報表全範圍測試

執行程式之後,100個榜行榜項目正規化表達了。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 9

六、VBA設定欄寛

成功正規化之後,調整前的資料可以刪除了,調整後的表格要適當設置欄寛。Call指令可以呼叫引用其他程序,在此是引用第4步驟的程序,這樣子本書補充說明的參考圖片較為簡潔,讀者有需要可以在所提供的範例檔案中,用先前章節方式將「報表資料重整2」的程式碼複製貼上,效果和Call指令引用是一樣的。

另外這裡用到了兩個調整欄寛的指令,EntireColumn.AutoFit是快速依照儲存格內容適當調整欄寛,ColumnWidth = 30是精準設定欄寛為30,Columns(“B:C”)是針對B到C欄的意思,這些都是很基本普遍的Excel操作,只不過現在是以VBA程式碼書寫執行。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 11

七、Excel資料正規化

最後得到了正規化報表,Excel可以很方便的進行資料整理統計分析。

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 13

Excel與資料庫軟體差異

專業的資料庫軟體例如Access、MySQL等,它在輸入介面時已經設計好了格式,操作者一開始就只能依照既定格式輸入,因此不會有正規化的問題。Excel是開放的試算表軟體,在空白工作表上輸入沒有任何限制,開放的另外一面是可能不那麼正規,從網頁所取得的資料更是如此,所以這一節範例的處理原則在很多地方都適用。

加強學習:VBA自動化文章合輯

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

Excel資料正規化:VBA For Next迴圈整理報表並調整欄寬 17
歡迎加入Line社群,口袋裡的VBA小教室!