EXCEL VBA(EXCEL透视表的制作).doc

深山老猫 和我一起学EXCEL VBA( EXCEL透视表的制作) 嗯，昨天家里来客人了，十来个人在围着吃火锅，好不热闹，所以把课程耽误了，不过不要紧，下面就让我来和大家一起讨论一下如何用VBA创建，操作EXCEL的数据透视表-半年前我求人家帮我也求不来，NND，以下的教程是我一手一脚试出来的，耗时两天，当你回复这个帖子的同时，你也会觉得回复得很值，如果你有更好的想法，一起说出来研究一下哈哈。在做这些之前，我得告诉你们，数据透视表的体积可是相当的大，可能超过你的文件本身的10倍以上，所以啊，要小心喽录制一个数据透视表宏1用VBA创建一个透视表3建立多字段的复杂报表4建立加入公式计算的透视表5给数据区加上格式7取消行例合计7快速建立多行多例数据透视表的方法7透视表的行例位置转换/转换9数据区的行例转换10禁止透视表字段拖拉10录制一个数据透视表宏在讲这一切之前，我们再回到以前的习惯，看一下这个透视表都有些啥对像。然后呢，我们来录制一个宏，来一起研究一下如何去用VBA来写。为我们得到的代码都是一样的，所以请用以下的数据进行录制。谢谢。我们来录制这样一个宏我们把所有的数量(Order Quantity)按Inventory Code 分组求和这是生成的数据透视表：录制的宏很简单，如下面的代码。Sub Macro1() Macro1 Macro 宏由 XueweiL 录制，时间: 11/28/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).AddFields RowFields:=Inventory_Code ActiveSheet.PivotTables(数据透视表1).PivotFields(Order Quantity).Orientation = _ xlDataFieldEnd Sub好了，在这里我们可以看到几个对象。我们一一来了解一下。PivotCache透视表缓存，如果改变数据源，可以修改这个属性。下面这个代码可以马上刷新当前的数据透视表ActiveSheet.PivotTables(1).PivotCache.RefreshPivotCell透视表的一个单元格PivotField表示数据透视表中的一个字段，PivotField 对象是 PivotFields 集合的成员。PivotFields 集合包含数据透视表中所有的字段，也包括隐藏字段。PivotFormula代表在数据透视表中用于计算的公式。PivotItem代表数据透视表字段中的一个项。该项是字段类别中的一个独立的数据条目。PivotItem 对象是 PivotItems 集合的成员。PivotItems 集合包含 PivotField 对象中的所有项。PivotItemList指定的数据透视表中所有 PivotItem 对象的集合。每个 PivotItem 代表数据透视表字段中的一个项。PivotLayout代表数据透视图报表中字段的位置。PivotTable代表工作表上的一张数据透视表。PivotTable 对象是 PivotTables 集合的成员。PivotTables 集合包含单张工作表中的所有 PivotTable 对象。有人说，TNND这样我怎么看得懂啊？没关系，只是先让你知道有那么个东东先，后面你就会明白了。用VBA创建一个透视表由刚刚录制的宏和我们平时用数据透视表的习惯可以知道，建立数据透视表的步骤是：确定数据源-确定行与例-确定数据项及运算公式先来看看这个代码，这个代码与刚刚我们录制的代码的功能是一样的，都是把所有的数量(Order Quantity)按Inventory Code 分组求和：把代码COPY到VBE里面去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.CreatePivotTable(, PT1)With pt设定行字段.PivotFields(Inventory_Code).Orientation = xlRowField设定数据字段设定数据字段的公式默认为求和.PivotFields(Order Quantity).Orientation = xlDataFieldEnd With应用于 CubeField 和 PivotField 对象的 Orientation 属性。在指定数据透视表中的字段位置。XlPivotFieldOrientation 可为以下 XlPivotFieldOrientation 常量之一。xlColumnField 例字段xlDataField 值字段xlHidden 隐藏字段xlPageField 页字段xlRowField 行字段End Sub看了这段代码，是否觉得很简单呢？做一个透视表也只不过是设定PivotCache,再用PivotCache添加透视表，再用透视表的PivotFields的字段去构造这个透视表的结构，就是那么简单。修改数据项的公式当然了，接下来可没有那么简单了。假如我要把所有的数量(Order Quantity)按Inventory Code 分组计数，就要用到修改计算公式的方法。上面介绍了，在EXCEL透视表中，数值字段默认是求和的，那我们要怎么样去修改呢？我们注意到PivotField的有一个Function 属性，在录制宏的时候，也可以看到是用FUNCTION去修改的。我试着去修改，得到下面这些代码：ActiveSheet.PivotTables(PT1).PivotFields(求和项:Order Quantity).Function xlCount这个求和项:Order Quantity可不怎么好得到,但是他是在数据区的字段，我们可以用pt.DataFields(1).Caption 或是pt.DataFields(1).Name 去得到。建立多字段的复杂报表 我们已经知道了如何建立一个透视表了，现在就来做一个复杂一点的报表. 我们来建立像下面这样的一个报表我们的步骤和以前一样代码如下：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.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设定数据字段设定数据字段的公式默认为求和.PivotFields(Order Quantity).Orientation = xlDataFieldEnd With应用于 CubeField 和 PivotField 对象的 Orientation 属性。在指定数据透视表中的字段位置。XlPivotFieldOrientation 可为以下 XlPivotFieldOrientation 常量之一。xlColumnField 例字段xlDataField 值字段xlHidden 隐藏字段xlPageField 页字段xlRowField 行字段End Sub呵呵，简单吧。事实就是一个模板修改一下就是了.建立加入公式计算的透视表其实透视表还是可以自己计算的。比如下面这个表，做为一个领导，他只想看到这些出货日期中，过了货期的有多少，未来五天要出货的有多少，其它的有多少，这可要花一番工夫!这里我们要用到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 .PivotFields(Shipment Due Date).Orientation = xlColumnField .PivotFields(Inventory_Code).Orientation = xlRowField.PivotFields(Order Quantity).Orientation = xlDataField 判断日期以生成公式,注意，因为例标题是日期，如果直接参于计算的话，会当成数值直接汇总。所在这里采用替换法，把日期换成文本，什么事都好做了。 PivotItems，数据透视表字段中所有 PivotItem 对象的集合。这些对象为字段分类中的独立数据项，由于是由数据自动生成，所以要用FOR EACH取值 For Each i In .PivotFields(Shipment Due Date).PivotItems Select Case CDate(i.Name) - Date Case Is = 5 未来5天的 i.Caption = in5days & Format(i.Name, YYMMDD) In5Days = In5Days & i.Caption & + Case Else 其它的 i.Caption = others & Format(i.Name, YYMMDD) Others = Others & i.Caption & + End Select Next 添加公式例 If Delay Then .PivotFields(Shipment Due Date).CalculatedItems.Add Delay, = & Left(Delay, Len(Delay) - 1) If In5Days Then .PivotFields(Shipment Due Date).CalculatedItems.Add In5Days, = & Left(In5Days, Len(In5Days) - 1) If Others Then .PivotFields(Shipment Due Date).CalculatedItems.Add Others, = & Left(Others, Len(Others) - 1) 隐藏不相干的例，注意，一个透视表最少一定要有一例显示，不然会出错的。 For Each i In .PivotFields(Shipment Due Date).PivotItems If i.Name Delay And i.Name In5days And i.Name Others And i.Name 合计 Then i.Visible = False NextEnd WithEnd Sub这里要注意的一点就是，我们用的公式项CalculatedItems.Add方法，后面的标题不能重复，公式是已有的例计算所得，如果其中引用不存在的例，就会引出错误。效果如下。当然，你也可以用其它的公式。一切都要看你的需要了哈给数据区加上格式有人要问了，数据区那么大，我怎么选啊？我们这里试试DataBodyRang属性。这个属性返回的是一个RANGE对像，查以直接对这一片数据区进行设置格式。试试这个代码ActiveCell.PivotTable.DataBodyRange.NumberFormatLocal = #,#0.00_ ;红色-#,#0.00 当然，如果你想给你的数据透视表的行标例标也给设置上颜色，那也是可以的。如：ActiveCell.PivotTable.RowRange.Interior.ColorIndex = 3 把一整行标都涂成了猴子屁股ActiveCell.PivotTable.ColumnRange.Interior.ColorIndex = 3 猴子屁股双多了一张ActiveCell.PivotTable.PageRange.Interior.ColorIndex = 4 页标也变了ActiveCell.PivotTable.DataLabelRange.Interior.ColorIndex = 5 你的数据标签也加上了.取消行例合计你还想说什么？不想要那个合计？没问题!ActiveCell.PivotTable.ColumnGrand =False 取消例合计ActiveCell.PivotTable.RowGrand =False 取消行合计快速建立多行多例数据透视表的方法如果我们要建立像这样的一个透视表，有两个行标题，一个例标题或是更多，那么就可以用数组的方法来建立.Sub CreatePivotTable()Dim ptcache As PivotCacheDim pt As PivotTableSet ptcache = ActiveWorkbook.PivotCaches.Add(xlDatabase, Sheet1.Range(A1).CurrentRegion.Address)Set pt = ptcache.CreatePivotTable(, PT1)添加行例字段pt.AddFields Array(Inventory_Code, Account_Number), Status_Date添加数据字段pt.AddDataField pt.PivotFields(Order Quantity), Sum as Qty, xlSumEnd Sub在这里要注意AddFields的用法：expression.AddFields(RowFields, ColumnFields, PageFields, AddToTable, AppendField)expression 必需。该表达式返回一个 PivotTable 对象。RowFieldsVariant 类型，可选。指定要作为行添加或要添加到分类坐标轴中的字段名（或者字段名数组）。ColumnFieldsVariant 类型，可选。指定要作为列添加或要添加到系列坐标轴中的字段名（或者字段名数组）。PageFieldsVariant 类型，可选。指定要作为页添加或要添加到页区域中的字段名（或者字段名数组）。AddToTable Variant 类型，可选。仅应用于数据透视表。如果为 True，则将指定的字段加入到报表中（不替换现有字段）。如果为 False，则用新的字段替换已有的字段。默认值为 False。AppendField Boolean 类型，可选。仅用于数据透视表。如果为 True，则可往报表中添加字段（不替换任何现有字段），如果为 False，则以新字段替换现有字段。默认值为 False。添加的时候的Array里面，是按顺序来进行加载的。pt.AddFields Array(Inventory_Code, Account_Number), Status_Date，就是先加载Inventory_Code后加载Account_NumberAddDataField的用法：expression.AddDataField(Field, Caption, Function)expression 必需。该表达式返回“应用于”列表中的对象之一。Field Object 类型，必需。服务器上的唯一字段。如果源数据是联机分析处理 (OLAP)，则唯一字段是多维数据集字段。如果源数据不是 OLAP（非 OLAP 源数据），则唯一字段是数据透视表字段。Caption Variant 类型，可选。数据透视表中使用的标志，用于识别该数据字段。Function Variant 类型，可选。在已添加字段中执行的函数。FUNCTION呢，是一个常量。返回或设置对数据透视表字段汇总时所使用的函数（仅用于数据字段）。XlConsolidationFunction 类型，可读写。XlConsolidationFunction 可为以下 these XlConsolidationFunction 常量之一。 有什么用我想就不用我来说了哈。xlAverage xlCountNums xlMin xlStDev xlSum xlVar xlCount xlMax xlProduct xlStDevP xlUnknown xlVarP这个表我们也可以隐藏子合计项：pt.PivotFields(Inventory_Code).Subtotals(1) = False这个合计项，不仅仅是可以求和，也可以计数，也可以都算上!在刚刚的那个代码的后面加上这个，就可以显示你要合计的项了：pt.PivotFields(Inventory_Code).Subtotals = Array(False, False, True, False, False, False, False, False, False, False, False, False)Array(False, False, True, False, False, False, False, False, False, False, False, False)里面的数值的意思啊，就按顺序从左到右，分别是以下的值：索引含义1自动2Sum3Count4Average5Max6Min7Product8Count Nums9StdDev10StdDevp11Var12Varp也就是说，如果你要使你为这个字段求最大值，就数一下，把最大值的那一项修改成TRUE就行了pt.PivotFields(Invent