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

在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 M