利用VBA编程提高报表制作效率

陆锦鹤

摘 要：Excel电子表格软件是应用最广泛的办公软件之一，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经等众多领域。Excel的强大优势还在于它支持VBA编程。VBA是Visual Basic For Application的简称，使用它可以达成执行特定功能或是重复性高的操作，从而大大提高使用者的工作效率。本文以笔者本部门实际需求为例，描述如何使用Excel中的VBA编程功能提高报表的制作效率，实现办公自动化。

关键词：VBA编程；办公自动化

中图分类号： TP31 文献标识码： A 文章编号： 1673-1069（2016）12-152-2

1 报表制作流程

①制作报表，需要从公司SAP系统中导出原始数据表；

②按照部门的需求，向原始数据表中加入四列新的字段，并调整原始数据表的数据格式，形成新的数据表；

③使用新数据表中的数据形成数据透视表，供经理使用。

2 报表制作方法

①打开原始数据表的Excel文件，按Alt+F11，打开Excel VBA编辑器；

②在Excel VBA编辑器中双击左上窗口中“Microsoft Excel对象”下的“ThisWorkbook”，在右侧的空白编辑区内输入如下代码：

Sub 建立报表（）

Dim sh As Worksheet ‘ 定义工作表名称

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

Dim MyRow1， MyRow2， flag As Integer ‘ 定义三个标识符，供转换格式时使用

Dim X As String ‘ 定义字符型变量，用于存储转换格式时的字符串

flag = 2 ‘ 为标识符赋值

sh.Cells（1， 3） = "费用类型" ‘ 向原始数据表中添加四列新内容

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

sh.Cells（1， 5） = "费用类型"

sh.Cells（1， 6） = "车间号"

For MyRow1 = 2 To ActiveSheet.UsedRange.Rows.Count ‘ 依照条件转换格式

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 ‘ 取表中总记录数

For MyRow1 = MyRow2 To 2 Step -1 ‘ 按部门订制的需求根据条件添加数据类型

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

依照条件向新表中添加数据

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"

依照条件向新表中添加数据

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 ' 定义一个数据范围，用来储存生成数据透视表的数据

Dim MyPivot As Worksheet ' 定义一个工作表，存放数据透视表"

Dim MyPivotTable As PivotTable ' 定义一个数据透视表，用来储存数据透视表对象

Dim MyTable As Worksheet ' 定义一个工作表，做为汇总表

Dim sh1 As Worksheet ' 定义工作表变量，删除数据透视表时使用

Set DataRng = Range（"Sheet1！A1：F" & ActiveSheet.UsedRange.Rows.Count） ' 确定生成数据透视表的数据

Set MyPivot = Sheets.Add ' 新建一个工作表，用来存放数据透视表

Set MyPivotTable = MyPivot.PivotTableWizard（SourceType：=xlDatabase， SourceData：=DataRng） ‘ 利用PivotTableWizard方法生成一个空的数据透视表

MyPivotTable.AddFields RowFields：="费用类型"， ColumnFields：="车间号" ' 添加数据透视表行字段和列字段

MyPivotTable.AddDataField MyPivotTable.PivotFields（"Act. Costs"）， Function：=xlSum ' 添加数据透视表数据字段和汇总方法

MyPivotTable.PivotFields（"车间号"）.Subtotals（1） = False' 取消透视表的分类汇总功能

输入上述内容后，按F5键，执行该程序，自动生成新的报表。可将上述内容保存为宏，以便今后再次使用。

3 综述

如果由人工来逐条数据进行处理，由于大量的时间用来执行重复的操作，因此制作新的报表至少需要一个小时的时间，而且容易出现错误。在使用该VBA程序自动生成报表后，生成新的报表可以在不到一分钟的时间内完成。不仅节约了大量的时间，大大提升了生成报表的效率，而且减少了在制作报表中出现错误的概率。可见如果有大量数据需要执行重复操作的时候，VBA编程是提升工作效率的很好方法。