利用VBA編程提高報表制作效率

陸錦鶴

摘 要：Excel電子表格軟件是應(yīng)用最廣泛的辦公軟件之一，它可以進行各種數(shù)據(jù)的處理、統(tǒng)計分析和輔助決策操作，廣泛地應(yīng)用于管理、統(tǒng)計財經(jīng)等眾多領(lǐng)域。Excel的強大優(yōu)勢還在于它支持VBA編程。VBA是Visual Basic For Application的簡稱，使用它可以達成執(zhí)行特定功能或是重復性高的操作，從而大大提高使用者的工作效率。本文以筆者本部門實際需求為例，描述如何使用Excel中的VBA編程功能提高報表的制作效率，實現(xiàn)辦公自動化。

關(guān)鍵詞：VBA編程；辦公自動化

中圖分類號： TP31 文獻標識碼： A 文章編號： 1673-1069（2016）12-152-2

1 報表制作流程

①制作報表，需要從公司SAP系統(tǒng)中導出原始數(shù)據(jù)表；

②按照部門的需求，向原始數(shù)據(jù)表中加入四列新的字段，并調(diào)整原始數(shù)據(jù)表的數(shù)據(jù)格式，形成新的數(shù)據(jù)表；

③使用新數(shù)據(jù)表中的數(shù)據(jù)形成數(shù)據(jù)透視表，供經(jīng)理使用。

2 報表制作方法

①打開原始數(shù)據(jù)表的Excel文件，按Alt+F11，打開Excel VBA編輯器；

②在Excel VBA編輯器中雙擊左上窗口中“Microsoft Excel對象”下的“ThisWorkbook”，在右側(cè)的空白編輯區(qū)內(nèi)輸入如下代碼：

Sub 建立報表（）

Dim sh As Worksheet ‘ 定義工作表名稱

Set sh = Worksheets（ActiveWorkbook.Sheets（1）.Name）

Dim MyRow1， MyRow2， flag As Integer ‘ 定義三個標識符，供轉(zhuǎn)換格式時使用

Dim X As String ‘ 定義字符型變量，用于存儲轉(zhuǎn)換格式時的字符串

flag = 2 ‘ 為標識符賦值

sh.Cells（1， 3） = "費用類型" ‘ 向原始數(shù)據(jù)表中添加四列新內(nèi)容

sh.Cells（1， 4） = "成本中心"

sh.Cells（1， 5） = "費用類型"

sh.Cells（1， 6） = "車間號"

For MyRow1 = 2 To ActiveSheet.UsedRange.Rows.Count ‘ 依照條件轉(zhuǎn)換格式

X = sh.Cells（MyRow1， 1）

If Left（X， 1） = "*" Then

For MyRow2 = MyRow1 To flag Step -1

sh.Cells（MyRow2， 3） = sh.Cells（MyRow1， 1）

Next MyRow2

flag = MyRow1 + 1

End If

sh.Cells（MyRow1， 4） = Mid（sh.Cells（MyRow1， 1）， 3， 7）

Next MyRow1

MyRow2 = ActiveSheet.UsedRange.Rows.Count ‘ 取表中總記錄數(shù)

For MyRow1 = MyRow2 To 2 Step -1 ‘ 按部門訂制的需求根據(jù)條件添加數(shù)據(jù)類型

If Left（sh.Cells（MyRow1， 1）， 5） = "72" And Right（Left（sh.Cells（MyRow1， 1）， 9）， 2） = "00" Then Rows（MyRow1）.Delete ‘ 按條件刪除表中的記錄

If Left（sh.Cells（MyRow1， 1）， 1） = "*" Then Rows（MyRow1）.Delete

依照條件向新表中添加數(shù)據(jù)

If sh.Cells（MyRow1， 3） = "*4107101 Shift & Over. Allo" Then sh.Cells（MyRow1， 5） = "personnel expense"

If sh.Cells（MyRow1， 3） = "*4207661 Stamps" Then sh.Cells（MyRow1， 5） = "other expense"

If sh.Cells（MyRow1， 3） = "*4207775 Low Value Goods" Then sh.Cells（MyRow1， 5） = "other expense"

If sh.Cells（MyRow1， 3） = "*5147420 Entertainment" Then sh.Cells（MyRow1， 5） = "OC internal allocation"

If sh.Cells（MyRow1， 3） = "*4207780 Regular Depreciati" Then sh.Cells（MyRow1， 5） = "depreciation"

If sh.Cells（MyRow1， 3） = "*8802391 SE alloc. to OC" Then sh.Cells（MyRow1， 5） = "OE"

依照條件向新表中添加數(shù)據(jù)

If sh.Cells（MyRow1， 4） = "722201" Then sh.Cells（MyRow1， 6） = "OC-N2201"

If sh.Cells（MyRow1， 4） = "722202" Then sh.Cells（MyRow1， 6） = "OC-N2202"

If sh.Cells（MyRow1， 4） = "722203" Then sh.Cells（MyRow1， 6） = "OC-N2203"

If sh.Cells（MyRow1， 4） = "722204" Then sh.Cells（MyRow1， 6） = "OC-N2204"

Next MyRow1

Dim DataRng As Range ' 定義一個數(shù)據(jù)范圍，用來儲存生成數(shù)據(jù)透視表的數(shù)據(jù)

Dim MyPivot As Worksheet ' 定義一個工作表，存放數(shù)據(jù)透視表"

Dim MyPivotTable As PivotTable ' 定義一個數(shù)據(jù)透視表，用來儲存數(shù)據(jù)透視表對象

Dim MyTable As Worksheet ' 定義一個工作表，做為匯總表

Dim sh1 As Worksheet ' 定義工作表變量，刪除數(shù)據(jù)透視表時使用

Set DataRng = Range（"Sheet1！A1：F" & ActiveSheet.UsedRange.Rows.Count） ' 確定生成數(shù)據(jù)透視表的數(shù)據(jù)

Set MyPivot = Sheets.Add ' 新建一個工作表，用來存放數(shù)據(jù)透視表

Set MyPivotTable = MyPivot.PivotTableWizard（SourceType：=xlDatabase， SourceData：=DataRng） ‘ 利用PivotTableWizard方法生成一個空的數(shù)據(jù)透視表

MyPivotTable.AddFields RowFields：="費用類型"， ColumnFields：="車間號" ' 添加數(shù)據(jù)透視表行字段和列字段

MyPivotTable.AddDataField MyPivotTable.PivotFields（"Act. Costs"）， Function：=xlSum ' 添加數(shù)據(jù)透視表數(shù)據(jù)字段和匯總方法

MyPivotTable.PivotFields（"車間號"）.Subtotals（1） = False' 取消透視表的分類匯總功能

輸入上述內(nèi)容后，按F5鍵，執(zhí)行該程序，自動生成新的報表。可將上述內(nèi)容保存為宏，以便今后再次使用。

3 綜述

如果由人工來逐條數(shù)據(jù)進行處理，由于大量的時間用來執(zhí)行重復的操作，因此制作新的報表至少需要一個小時的時間，而且容易出現(xiàn)錯誤。在使用該VBA程序自動生成報表后，生成新的報表可以在不到一分鐘的時間內(nèi)完成。不僅節(jié)約了大量的時間，大大提升了生成報表的效率，而且減少了在制作報表中出現(xiàn)錯誤的概率。可見如果有大量數(shù)據(jù)需要執(zhí)行重復操作的時候，VBA編程是提升工作效率的很好方法。