陸錦鶴

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

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

中圖分類號(hào)： TP31 文獻(xiàn)標(biāo)識(shí)碼： A 文章編號(hào)： 1673-1069（2016）12-152-2

1 報(bào)表制作流程

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

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

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

2 報(bào)表制作方法

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

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

Sub 建立報(bào)表（）

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

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

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

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

flag = 2 ‘ 為標(biāo)識(shí)符賦值

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

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

sh.Cells（1， 5） = "費(fèi)用類型"

sh.Cells（1， 6） = "車間號(hào)"

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 ' 定義一個(gè)數(shù)據(jù)范圍，用來儲(chǔ)存生成數(shù)據(jù)透視表的數(shù)據(jù)

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

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

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

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

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

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

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

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

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

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

輸入上述內(nèi)容后，按F5鍵，執(zhí)行該程序，自動(dòng)生成新的報(bào)表?？蓪⑸鲜鰞?nèi)容保存為宏，以便今后再次使用。

3 綜述

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