在EXCEL中使用VBA处理数据透视表的探索

摘 要：Excel中的数据透视表能对原始数据进行重新组织和动态查询，但需要进行手工生成，会产生一定的人为失误。文章通过一个商品销售工作表为例，编程实现了通过VBA自动生成数据透视表及对其的修改，使Excel的数据处理满足个性化需要，大大提高了工作效率和质量。

关键词：Excel；VBA；数据处理

1 概述

Excel是Microsoft Office家族成员中一个功能强大、技术先进、使用方便的表格式数据综合管理和分析系统[1]。Visual Basic for Application（简称VBA）是新一代标准宏语言，基于Visual Basic for Windows发展起来，支持面向对象的程序设计语言。由于它具有直接应用Office套装软件的各项强大功能，其宏记录器可以很容易将日常工作转换为VBA。

Excel数据透视表实质上是一种基于数据源的能对原始数据进行重新组织和动态查询的表格[2]。创建得到的是一种数据表格，该表格的数据来源于已经创建好的数据源，不仅只是Excel工作表中的数据，也可以是其他数据库甚至是Internet上的数据。

使用VBA来自动生成数据透视表，既避免了手工生成数据透视表的麻烦，又可以使生成的数据透视表安全稳定，不会出现很多人工生成造成的失误。

2 实例分析

文章以商品销售工作表为例，该表列出了在某日期中哪些销售人员销售了哪些商品，并附带有销售的交易号以备查询，在将来方便对销售进行总计，从而对销售员进行表彰。

商品销售表如图1所示：

步骤一、在工作表上新建一个“按钮”控件，命名为“构造透视表”。并在“设计模式”下双击该按钮控件进入“Visual Basic编辑器”，编辑该“按钮”控件的单击响应代码如下所示：

Option Explicit

Private Sub CommandButton1_Click（）

ActiveWorkbook.PivotCaches.Add（SourceType：=x1Database，Source

Data：=“Sheet1！R2C1：R14C5”）

.CreatePivotTable TableDestination

：=Range（“F1”），TableName

：=“华夏数码城销售透视表”

ActiveSheet.PivotTables（“华夏数码城销售透视表”） .SmallGrid=False

ActiveSheet.PivotTables（“华夏数码城销售透视表”）

.AddFields RowFields：=Array（“销售日期”，“销售商品”）， ColumnFields：=“销售人员”

ActiveSheet.PivotTables（“华夏数码城销售透视表”）

.PivotFields（“销售金额”）.orientation=x1DataField

Range（“F1”）.select

End sub

步骤二、编辑代码完毕后保存并退出“Visual Basic编辑器”，单击“构造透视表”按钮即可自动生成如图2所示的透视表。

步骤三、利用VBA修改数据透视表。可以得到所选择日期当天的销售金额最多的销售员工。设计代码如下：

Function GetValue（ByVal TempDate As String） As String

Set PivotFieldVable = ActiveSheet. PivotTables（“华夏数码城销

售透视表”）. PivotFields（“销售日期”）

Set PivotVable= PivotFieldVable.PivotItems（CStr（CDate（Temp

Date）））

GetRow = pivotValue.DataRange.row

For TempInt =7 to 12 Step 1

If （Cells（GetRow， TempInt）.Value=Cells（GetRow， 13）.Value） Then

GetValue=Cells（2，TempInt）.Value

Exit Function

End If

Next TempInt

ENDCHK：

GetValue=“”

End Function

Private Sub CommandButton1_Click（）

Dim Name As String

Name=GetValue（ComboBox1.Text）

If （Name <>“”） Then

MsgBox“当天的销售状元是：” Name， vbOKOnly， “销售状元”

Else

MsgBox“当天没有销售状元”， vbOKOnly，“销售状元”

End If

End sub

如果选择日期有误或者当天没有销售记录，会弹出如图3对话框。

3 结束语

原则是只要数据源有3列或3列以上有数据，都可以使用它来创建数据透视表[4]。但是因为Excel中的数据透视表只是一种分析工具，而创建它的目的是从现有的数据中快速有效地整理出有价值的信息。数据透视表适合于把某列中含有多种数据而每种数据又重复出现多次的列以行的形式列出，使得每种数据在数据透视表中都以独立的列的形式出现并可以同时对每种数据进行加工处理。通过使用Excel VBA，可以减少人为生成数据透视表产生的失误问题，提高了办公软件的工作效率和质量。

参考文献

[1]苏进.探究如何在EXCEL中使用VBA编程处理数据[J].数字技术与应用，2016（1）：250.

[2]陆娇娇.基于ExcelVBA的多考场电子成绩自动统计[J].电脑知识与技术，2016（12）：218.

[3]孙敬杰.Excel入门与实例演练[M].北京：中国青年电子出版社，2008.

[4]周志军，张帆，窦志强.基于ExcelVBA实现考试自动编排[J].中国管理信息化，2016，19（3）：181-182.

作者简介：吕岚（1982-），女，研究生，研究方向为计算机科学与技术，陕西铁路工程职业技术学院讲师。