Excel VBA - 自動生成分析報告
案例分析
上一篇文章介紹了VBA跟Macro，也寫了一個簡單的應用，想必你們會覺得沒有挑戰性，那麼現在一起開發一個更實用和複雜的應月！
假設你已經懂得使用Excel的內置工作表函數（公式），突然你親愛的老闆要求你今天之內提交這個月的顧客分析的報告，儘管你忙於其他事情，但你也不能拒絕，又要保住飯碗，那怎麼辦了？如果你學會了VBA的話，這個問題便迎刃而解了！因為你只要按幾個鍵，便可以自動生成一份報告，你應該會覺得這是瘋了，當然在自動化之前，我們先要把報告的格式先寫好，然後再Macro把生成的動作記錄起來，並作出少許的修改，每次激活這個Macro便完成了！而現在，你手上有一連串的交易記錄(csv格式)和客戶的資料(xlsx格式)，目標是用最快的速度去完成這個悶人的報表！
先給大家介紹這個應用的運作，根據下圖，我會先把客戶的資料貼在一個excel活頁簿裡面，之後把這一個月的銷售記錄再貼進去，已經寫好的Excel Formula會把資料會變成一個總結起來，而一些圖表，例如Pie Chart亦會顯示出來。整個過程只需要幾秒，可是製作過程可是大工程了，不過為了未來可以省點時間，這是很值得的！
流程:
準備
首先，先打開新的活頁簿 (Workbook) 和儲存檔案，格式為"啟用巨集的活頁簿"。因xlsx的格式不會儲存任何的巨集(Macro)，會把你寫好的巨集，在你關掉這本子的時候自動洗掉，所以千萬要記得另存為xlsm的格式啊！
先從Dropbox下載原始檔案：
1. Members.xlsx:會員的資料
2. Transactions.csv: 六月份的交易記錄
上一篇文章介紹了VBA跟Macro，也寫了一個簡單的應用，想必你們會覺得沒有挑戰性，那麼現在一起開發一個更實用和複雜的應月！
假設你已經懂得使用Excel的內置工作表函數（公式），突然你親愛的老闆要求你今天之內提交這個月的顧客分析的報告，儘管你忙於其他事情，但你也不能拒絕，又要保住飯碗，那怎麼辦了？如果你學會了VBA的話，這個問題便迎刃而解了！因為你只要按幾個鍵，便可以自動生成一份報告，你應該會覺得這是瘋了，當然在自動化之前，我們先要把報告的格式先寫好，然後再Macro把生成的動作記錄起來，並作出少許的修改，每次激活這個Macro便完成了！而現在，你手上有一連串的交易記錄(csv格式)和客戶的資料(xlsx格式)，目標是用最快的速度去完成這個悶人的報表！
先給大家介紹這個應用的運作，根據下圖，我會先把客戶的資料貼在一個excel活頁簿裡面，之後把這一個月的銷售記錄再貼進去，已經寫好的Excel Formula會把資料會變成一個總結起來，而一些圖表，例如Pie Chart亦會顯示出來。整個過程只需要幾秒，可是製作過程可是大工程了，不過為了未來可以省點時間，這是很值得的！
流程:
準備
首先，先打開新的活頁簿 (Workbook) 和儲存檔案，格式為"啟用巨集的活頁簿"。因xlsx的格式不會儲存任何的巨集(Macro)，會把你寫好的巨集，在你關掉這本子的時候自動洗掉，所以千萬要記得另存為xlsm的格式啊！
 |
| Remember to save as Excel Macro-Enabled Workbook! |
1. Members.xlsx:會員的資料
2. Transactions.csv: 六月份的交易記錄
製作範本
在寫巨集之前，要先設計好報告的樣式，先把三張工作表的名字改成："Raw_data", "Member_profile" and "Report"。打開"Raw_data"的工作表，接著根據下圖，把第一列的儲存格改成，G至L欄是透過公式計算出來的，所以我先把它塗成了綠色，在G2至L2的儲存格寫下公式。
先解釋這個公式的設計，為了從會員資料取得其名字，所以才使用vlookup的公式，VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE)，我們要透過B2的值，在Member_profile的工作表，來尋找它的名字，找到的話會回傳第二欄的值，也就是名字。但如果找不到的話，會回傳#VALUE，也就是ERROR。為了遮蓋#VALUE，我們用IF的公式把找不到的都換作""，儲存格看起來是空白的。
其實Report這工作表主要是給你的老闆看的，因此，一個清楚整齊的報告是不能缺少，不過如果你很不爽你的老闆，你是可以無視我剛才說的話 ：3 綠色的儲存格已寫下COUNTIF和SUMIF的公式，如果我們把銷售記錄貼上去Raw_data的工作表裡面，這些綠色的儲存格會自動計算回值正確的值。當然，如果你想用換成其他樣式也可以，只要公式寫對了，格式的變化根本不是問題。我也鼓勵大家設計自己的範本，自己決定要什麼樣的總結和並在圖表中可視化。
錄製Macro
正如前篇的文章，Macro是可以以錄製的方式取得，不用寫Visual Basic的程式，把一些hardcode的代碼改成變數，會讓你的程式適用於不同的情況。剛才，已經解釋了範本的設計和原理，現在，我們會依照以下的步驟把原始資料放進範本裡。
1. 按下 "錄製巨集"
2. 把Member.xlsx裡會員的資料（不包括欄名）複製，並貼在Member_profile的工作表裡。
3. 在Transaction.csv裡，複製交易記錄和貼上"Raw_data"工作表裡面。
4. 按下L2的右下角，並拉下去，自動填滿第2至31列的值。
5. 關閉活頁簿 (Transaction.csv & Member.xlsx)
6. 開啟"Report"工作表
7. 按下"停止錄製"
8. 打開VBE和module1
利用VBA改良巨集
為了讓程式能用在每一個月的報告裡，我會把它改良成放在Sub update()裡，當然我會解釋有什麼的東西要改。至於改後的版本(update.bas)，有需要可以在repository下載。
好！先把所有工作表，活頁簿的名字換成變數，如果有一天我們想改工作表或活頁簿的名字，只需要改一小段代碼便可以了，即是Worksheets("新的名字")。
正因為這個應用每個月也需要使用，不必要用人腦處理的步驟，盡力把它最自動化。因此我新增一個工作表，名字叫"Console"，加入一個按鈕並指定巨集"update"，用家只需要按下這個按鈕，選擇兩個檔案(Transactions.csv & Members.xlsx)，最後，一份完整的報告自動地做出來了！ 而且你再看不見更新和提示的畫面，因為我都把它關掉了。
與你分享
可能對於Excel新手是很難掌握，所以我把所有的檔案(包括Reporting Template.xlsm)都放在Github的repository上，希望能幫助在學習VBA的你！
如果有什麼疑問或者意見，可以隨時留言，交流一下，一起進步啊！
在寫巨集之前，要先設計好報告的樣式，先把三張工作表的名字改成："Raw_data", "Member_profile" and "Report"。打開"Raw_data"的工作表，接著根據下圖，把第一列的儲存格改成，G至L欄是透過公式計算出來的，所以我先把它塗成了綠色，在G2至L2的儲存格寫下公式。
小提示：下面的公式是用作尋找和取得會員的資料（會員名字，會員等級，性別，年齡）。
e.g. Member_name
e.g. Member_name
=IF(ISERROR(VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE))=FALSE,VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE),"")
先解釋這個公式的設計，為了從會員資料取得其名字，所以才使用vlookup的公式，VLOOKUP(B2,Member_profile!$A$2:$G$11,2,FALSE)，我們要透過B2的值，在Member_profile的工作表，來尋找它的名字，找到的話會回傳第二欄的值，也就是名字。但如果找不到的話，會回傳#VALUE，也就是ERROR。為了遮蓋#VALUE，我們用IF的公式把找不到的都換作""，儲存格看起來是空白的。
其實Report這工作表主要是給你的老闆看的，因此，一個清楚整齊的報告是不能缺少，
 |
| Empty report |
錄製Macro
正如前篇的文章，Macro是可以以錄製的方式取得，不用寫Visual Basic的程式，把一些hardcode的代碼改成變數，會讓你的程式適用於不同的情況。剛才，已經解釋了範本的設計和原理，現在，我們會依照以下的步驟把原始資料放進範本裡。
1. 按下 "錄製巨集"
2. 把Member.xlsx裡會員的資料（不包括欄名）複製，並貼在Member_profile的工作表裡。
3. 在Transaction.csv裡，複製交易記錄和貼上"Raw_data"工作表裡面。
4. 按下L2的右下角，並拉下去，自動填滿第2至31列的值。
5. 關閉活頁簿 (Transaction.csv & Member.xlsx)
6. 開啟"Report"工作表
7. 按下"停止錄製"
8. 打開VBE和module1
Sub recorded_update()
Windows("Members.xlsx").Activate
Range("A2:F11").Select
Selection.Copy
Windows("Reporting Template.xlsm").Activate
Sheets("Member_profile").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Transactions.csv").Activate
Range("A2:F31").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Reporting Template.xlsm").Activate
Sheets("Raw_data").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2:L2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("G2:L31"), Type:=xlFillDefault
Range("G2:L31").Select
Windows("Members.xlsx").Activate
ActiveWindow.Close
Windows("Transactions.csv").Activate
ActiveWindow.Close
Sheets("Report").Select
ActiveWorkbook.Save
End Sub
小提示：尋找錄好的巨集(record_update.bas)，也可以在GitHub的repository下載。利用VBA改良巨集
為了讓程式能用在每一個月的報告裡，我會把它改良成放在Sub update()裡，當然我會解釋有什麼的東西要改。至於改後的版本(update.bas)，有需要可以在repository下載。
好！先把所有工作表，活頁簿的名字換成變數，如果有一天我們想改工作表或活頁簿的名字，只需要改一小段代碼便可以了，即是Worksheets("新的名字")。
Sub update()
'Remarks: The code included in ' ' means outdated and replaced by others
'Command after ' is my own comment
Set report_book = ActiveWorkbook 'Set is needed as ActiveWorkbook is a Object
Set report_console_sheet = ActiveSheet
Set report_member_sheet = Worksheets("Member_Profile") 'Need update here once changed the worksheet name
Set report_trans_sheet = Worksheets("Raw_data")
Set report_report_sheet = Worksheets("Report")
'Windows("Members.xlsx").Activate'
插入一段code (Application.GetOpenFilename)去取得被選擇的檔案的名字（包括路徑），這樣excel會彈一個視窗出來給你選擇一個活頁簿，如果沒有選擇任何活頁簿的話，這個函數會回傳False。 ChDir ActiveWorkbook.Path '設定默認路徑 'Get the Filename of Member.xlsx member_file = Application.GetOpenFilename(fileFilter:="xlsx files (*.xlsx),*.xlsx", Title:="Import the Members.xlsx") If member_file = False Then Exit Sub End If Workbooks.Open (member_file) Set member_book = ActiveWorkbook '避免hardcode Set member_sheet = ActiveSheet因為每一個月的會員和交易數目是會變動的，因此我們要定義變數：會員數目和交易數目(members_num, trans_num)，來代替10跟30。VB會把交易記錄跟會員資料貼上去。
members_num = member_sheet.Range("A1").End(xlDown).Row - 1 'member_sheet.Range("A1").End(xlDown)的位置是$A$11
'Range("A2:F11").Select'
Range("A2:F" & members_num).Select '因為會員數目會變動的
Selection.Copy
'Windows("Reporting Template.xlsm").Activate'
report_book.Activate 'Avoid hardcode
'Sheets("Member_profile").Select'
report_member_sheet.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Get the Filename of Transactions.csv
transaction_file = Application.GetOpenFilename(fileFilter:="csv files (*.csv),*.csv", Title:="Import the Transactions.csv")
If transaction_file = False Then
Exit Sub
End If
Workbooks.Open (transaction_file)
Set transaction_book = ActiveWorkbook 'Avoid hardcode again
Set transaction_sheet = ActiveSheet
'Windows("Transactions.csv").Activate'
trans_num = transaction_sheet.Range("A1").End(xlDown).Row - 1
'Range("A2:F31").Select'
Range("A2:F" & trans_num).Select '因為交易數目會變動
Selection.Copy
'Windows("Reporting Template.xlsm").Activate'
report_book.Activate
report_trans_sheet.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2:L2").Select
Application.CutCopyMode = False
'Selection.AutoFill destination:=Range("G2:L31"), Type:=xlFillDefault'
Selection.AutoFill destination:=Range("G2:L" & trans_num), Type:=xlFillDefault
'Range("G2:L31").Select'
'Windows("Members.xlsx").Activate'
'ActiveWindow.Close'
member_book.Close (False) 'False代表不要儲存這個活頁簿
'Windows("Transactions.csv").Activate'
'ActiveWindow.Close'
transaction_book.Close (False)
'Sheets("Report").Select'
report_report_sheet.Select
report_member_sheet.Visible = False '隱藏工作表Raw_data和Members_profile
report_trans_sheet.Visible = False
report_console_sheet.Visible = False
'ActiveWorkbook.Save'
report_book.Save
MsgBox "Success! The report is generated!"
End Sub
優化UX正因為這個應用每個月也需要使用，不必要用人腦處理的步驟，盡力把它最自動化。因此我新增一個工作表，名字叫"Console"，加入一個按鈕並指定巨集"update"，用家只需要按下這個按鈕，選擇兩個檔案(Transactions.csv & Members.xlsx)，最後，一份完整的報告自動地做出來了！ 而且你再看不見更新和提示的畫面，因為我都把它關掉了。
Sub update()
'Remarks: The code included in ' ' means outdated and replaced by others
' Command after ' is my own comment
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set report_book = ActiveWorkbook 'Set is needed as ActiveWorkbook is a Object
Set report_console_sheet = ActiveSheet
Set report_member_sheet = Worksheets("Member_Profile") 'Need update here once changed the worksheet name
Set report_trans_sheet = Worksheets("Raw_data")
Set report_report_sheet = Worksheets("Report")
'Windows("Members.xlsx").Activate'
ChDir ActiveWorkbook.Path '設定默認路徑
'Get the Filename of Member.xlsx
member_file = Application.GetOpenFilename(fileFilter:="xlsx files (*.xlsx),*.xlsx", Title:="Import the Members.xlsx")
If member_file = False Then
Exit Sub
End If
Workbooks.Open (member_file)
Set member_book = ActiveWorkbook '避免hardcode
Set member_sheet = ActiveSheet
members_num = member_sheet.Range("A1").End(xlDown).Row - 1 'member_sheet.Range("A1").End(xlDown)的位置是$A$11
'Range("A2:F11").Select'
Range("A2:F" & members_num).Select '因為會員數目會變動的
Selection.Copy
'Windows("Reporting Template.xlsm").Activate'
report_book.Activate 'Avoid hardcode
'Sheets("Member_profile").Select'
report_member_sheet.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Get the Filename of Transactions.csv
transaction_file = Application.GetOpenFilename(fileFilter:="csv files (*.csv),*.csv", Title:="Import the Transactions.csv")
If transaction_file = False Then
Exit Sub
End If
Workbooks.Open (transaction_file)
Set transaction_book = ActiveWorkbook '避免hardcode
Set transaction_sheet = ActiveSheet
'Windows("Transactions.csv").Activate'
trans_num = transaction_sheet.Range("A1").End(xlDown).Row - 1
'Range("A2:F31").Select'
Range("A2:F" & trans_num).Select '因為交易數目會變動
Selection.Copy
'Windows("Reporting Template.xlsm").Activate'
report_book.Activate
report_trans_sheet.Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2:L2").Select
Application.CutCopyMode = False
'Selection.AutoFill destination:=Range("G2:L31"), Type:=xlFillDefault'
Selection.AutoFill destination:=Range("G2:L" & trans_num), Type:=xlFillDefault
'Range("G2:L31").Select'
'Windows("Members.xlsx").Activate'
'ActiveWindow.Close'
member_book.Close (False) 'False代表不要儲存這個活頁簿
'Windows("Transactions.csv").Activate'
'ActiveWindow.Close'
transaction_book.Close (False)
'Sheets("Report").Select'
report_report_sheet.Select
report_member_sheet.Visible = False '隱藏工作表Raw_data和Members_profile
report_trans_sheet.Visible = False
report_console_sheet.Visible = False
'ActiveWorkbook.Save'
report_book.Save
MsgBox "Success! The report is generated!"
Application.DisplayAlerts = True
Application.ScreenUpdating = True '允許出現更新和提示畫面
End Sub
Congratulation! The summary report is shown on the screen!
與你分享
可能對於Excel新手是很難掌握，所以我把所有的檔案(包括Reporting Template.xlsm)都放在Github的repository上，希望能幫助在學習VBA的你！
如果有什麼疑問或者意見，可以隨時留言，交流一下，一起進步啊！
Comments
Post a Comment