《EXCEL VBA(EXCEL透视表的制作).doc》由会员分享，可在线阅读，更多相关《EXCEL VBA(EXCEL透视表的制作).doc（10页珍藏版）》请在金锄头文库上搜索。
1、深山老猫 和我一起学EXCEL VBA( EXCEL透视表的制作) 嗯，昨天家里来客人了，十来个人在围着吃火锅，好不热闹，所以把课程耽误了，不过不要紧，下面就让我来和大家一起讨论一下如何用VBA创建，操作EXCEL的数据透视表-半年前我求人家帮我也求不来，NND，以下的教程是我一手一脚试出来的，耗时两天，当你回复这个帖子的同时，你也会觉得回复得很值，如果你有更好的想法，一起说出来研究一下哈哈。在做这些之前，我得告诉你们，数据透视表的体积可是相当的大，可能超过你的文件本身的10倍以上，所以啊，要小心喽录制一个数据透视表宏1用VBA创建一个透视表3建立多字段的复杂报表4建立加入公式计算的透视表5给
2、数据区加上格式7取消行例合计7快速建立多行多例数据透视表的方法7透视表的行例位置转换/转换9数据区的行例转换10禁止透视表字段拖拉10录制一个数据透视表宏在讲这一切之前，我们再回到以前的习惯，看一下这个透视表都有些啥对像。然后呢，我们来录制一个宏，来一起研究一下如何去用VBA来写。为我们得到的代码都是一样的，所以请用以下的数据进行录制。谢谢。我们来录制这样一个宏我们把所有的数量(Order Quantity)按Inventory Code 分组求和这是生成的数据透视表：录制的宏很简单，如下面的代码。Sub Macro1() Macro1 Macro 宏由 XueweiL 录制，时间: 11/2
3、8/2008 ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ tc521!R1C1:R575C12).CreatePivotTable TableDestination:=, TableName:= _ 数据透视表1, DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.PivotTables(数据透视表1).Add
4、Fields RowFields:=Inventory_Code ActiveSheet.PivotTables(数据透视表1).PivotFields(Order Quantity).Orientation = _ xlDataFieldEnd Sub好了，在这里我们可以看到几个对象。我们一一来了解一下。PivotCache透视表缓存，如果改变数据源，可以修改这个属性。下面这个代码可以马上刷新当前的数据透视表ActiveSheet.PivotTables(1).PivotCache.RefreshPivotCell透视表的一个单元格PivotField表示数据透视表中的一个字段，PivotF
5、ield 对象是 PivotFields 集合的成员。PivotFields 集合包含数据透视表中所有的字段，也包括隐藏字段。PivotFormula代表在数据透视表中用于计算的公式。PivotItem代表数据透视表字段中的一个项。该项是字段类别中的一个独立的数据条目。PivotItem 对象是 PivotItems 集合的成员。PivotItems 集合包含 PivotField 对象中的所有项。PivotItemList指定的数据透视表中所有 PivotItem 对象的集合。每个 PivotItem 代表数据透视表字段中的一个项。PivotLayout代表数据透视图报表中字段的位置。Piv
6、otTable代表工作表上的一张数据透视表。PivotTable 对象是 PivotTables 集合的成员。PivotTables 集合包含单张工作表中的所有 PivotTable 对象。有人说，TNND这样我怎么看得懂啊？没关系，只是先让你知道有那么个东东先，后面你就会明白了。用VBA创建一个透视表由刚刚录制的宏和我们平时用数据透视表的习惯可以知道，建立数据透视表的步骤是：确定数据源-确定行与例-确定数据项及运算公式先来看看这个代码，这个代码与刚刚我们录制的代码的功能是一样的，都是把所有的数量(Order Quantity)按Inventory Code 分组求和：把代码COPY到VBE里
7、面去Sub CreatePivotTable()Dim ptcache As PivotCacheDim pt As PivotTable设置区域Set ptcache = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.Range(A1).CurrentRegion.Address)增加透视表到新的工作表如果你不想加到新的工作表去，比如你想加到另一个工作表的C1单元格去，那么你可以这样写：Set pt = ptcache.CreatePivotTable(Sheet2.Range(c1), PT1)Set pt = ptcache.Crea
8、tePivotTable(, PT1)With pt设定行字段.PivotFields(Inventory_Code).Orientation = xlRowField设定数据字段设定数据字段的公式默认为求和.PivotFields(Order Quantity).Orientation = xlDataFieldEnd With应用于 CubeField 和 PivotField 对象的 Orientation 属性。在指定数据透视表中的字段位置。XlPivotFieldOrientation 可为以下 XlPivotFieldOrientation 常量之一。xlColumnField 例
9、字段xlDataField 值字段xlHidden 隐藏字段xlPageField 页字段xlRowField 行字段End Sub看了这段代码，是否觉得很简单呢？做一个透视表也只不过是设定PivotCache,再用PivotCache添加透视表，再用透视表的PivotFields的字段去构造这个透视表的结构，就是那么简单。修改数据项的公式当然了，接下来可没有那么简单了。假如我要把所有的数量(Order Quantity)按Inventory Code 分组计数，就要用到修改计算公式的方法。上面介绍了，在EXCEL透视表中，数值字段默认是求和的，那我们要怎么样去修改呢？我们注意到PivotFi
10、eld的有一个Function 属性，在录制宏的时候，也可以看到是用FUNCTION去修改的。我试着去修改，得到下面这些代码：ActiveSheet.PivotTables(PT1).PivotFields(求和项:Order Quantity).Function xlCount这个求和项:Order Quantity可不怎么好得到,但是他是在数据区的字段，我们可以用pt.DataFields(1).Caption 或是pt.DataFields(1).Name 去得到。建立多字段的复杂报表 我们已经知道了如何建立一个透视表了，现在就来做一个复杂一点的报表. 我们来建立像下面这样的一个报表我们
11、的步骤和以前一样代码如下：Sub CreatePivotTable()Dim ptcache As PivotCacheDim pt As PivotTable设置区域Set ptcache = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.Range(A1).CurrentRegion.Address)增加透视表到新的工作表如果你不想加到新的工作表去，比如你想加到另一个工作表的C1单元格去，那么你可以这样写：Set pt = ptcache.CreatePivotTable(Sheet2.Range(c1), PT1)Set pt = p
12、tcache.CreatePivotTable(, PT1)With pt设定页字段.PivotFields(Account_Number).Orientation = xlPageField.PivotFields(Order_Status_Code).Orientation = xlPageField设定行字段.PivotFields(Inventory_Code).Orientation = xlRowField设定例字段.PivotFields(Shipment Due Date).Orientation = xlColumnField设定数据字段设定数据字段的公式默认为求和.Pivo
13、tFields(Order Quantity).Orientation = xlDataFieldEnd With应用于 CubeField 和 PivotField 对象的 Orientation 属性。在指定数据透视表中的字段位置。XlPivotFieldOrientation 可为以下 XlPivotFieldOrientation 常量之一。xlColumnField 例字段xlDataField 值字段xlHidden 隐藏字段xlPageField 页字段xlRowField 行字段End Sub呵呵，简单吧。事实就是一个模板修改一下就是了.建立加入公式计算的透视表其实透视表还是可
14、以自己计算的。比如下面这个表，做为一个领导，他只想看到这些出货日期中，过了货期的有多少，未来五天要出货的有多少，其它的有多少，这可要花一番工夫!这里我们要用到PIVOTTIEMS集合，这个集合会显示出所有的例标。我们来用一个程序来演示一下。For Each i In .PivotFields(Shipment Due Date).PivotItems Msgbox i.NameNext知道这个了，自然会写下面的程序了。Sub CreatePivotTable()Dim ptcache As PivotCacheDim pt As PivotTableDim Delay As StringDim In5Days As StringDim Others As StringSet ptcache = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.Range(A1).CurrentRegion.Address)Set pt = ptcache.CreatePivotTable(, PT1)With pt .PivotFields(Account_Number).Orientation = xlPageField .P
