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 = _
xlDataField
End Sub

好了，在这里我们可以看到几个对象。我们一一来了解一下。
PivotCache
透视表缓存，如果改变数据源，可以修改这个属性。下面这个代码可以马上刷新当前的数据透视表
ActiveSheet.PivotTables(1).PivotCache.Refresh
PivotCell
透视表的一个单元格
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 PivotCache
Dim 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 = xlDataField
End 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 PivotCache
Dim 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 = xlDataField
End 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.Name
Next

知道这个了，自然会写下面的程序了。

Sub CreatePivotTable()
Dim ptcache As PivotCache
Dim pt As PivotTable
Dim Delay As String
Dim In5Days As String
Dim Others As String

Set 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 < 0 '小于今天的是DELAY
i.Caption = "delay" & Format(i.Name, "YYMMDD")
Delay = Delay & i.Caption & "+"
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
Next
End With
End 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 PivotCache
Dim pt As PivotTable

Set 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", xlSum
End Sub

在这里要注意AddFields的用法：
expression.AddFields(RowFields, ColumnFields, PageFields, AddToTable, AppendField)
expression      必需。该表达式返回一个 PivotTable 对象。
RowFields     Variant 类型，可选。指定要作为行添加或要添加到分类坐标轴中的字段名（或者字段名数组）。
ColumnFields     Variant 类型，可选。指定要作为列添加或要添加到系列坐标轴中的字段名（或者字段名数组）。
PageFields     Variant 类型，可选。指定要作为页添加或要添加到页区域中的字段名（或者字段名数组）。
AddToTable      Variant 类型，可选。仅应用于数据透视表。如果为 True，则将指定的字段加入到报表中（不替换现有字段）。如果为 False，则用新的字段替换已有的字段。默认值为 False。
AppendField      Boolean 类型，可选。仅用于数据透视表。如果为 True，则可往报表中添加字段（不替换任何现有字段），如果为 False，则以新字段替换现有字段。默认值为 False。

添加的时候的Array里面，是按顺序来进行加载的。pt.AddFields Array("Inventory_Code", "Account_Number"), "Status_Date"，就是先加载"Inventory_Code"后加载"Account_Number"…

AddDataField的用法：
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
自动
2
Sum
3
Count
4
Average
5
Max
6
Min
7
Product
8
Count Nums
9
StdDev
10
StdDevp
11
Var
12
Varp
也就是说，如果你要使你为这个字段求最大值，就数一下，把最大值的那一项修改成TRUE就行了
pt.PivotFields("Inventory_Code").Subtotals = Array(False, False, False, False, True, False, False, False, False, False, False, False)
明白了吧@!HOHO!

透视表的行例位置转换/转换
上面这个透视表，我们要把Account_Number做为例字段运算---怎么办？
先修改喽。。。
ActiveSheet.PivotTables(1).PivotFields("Order_Status_Code")..Orientation = xlColumn
放上面还是放下面呢？？？如果是放在上面，效果就像这样：

放下面呢？？就成了这样了：

这个就要用Position属性了。你修改Position=1，那么那个字段就会在上面~~~~
ActiveSheet.PivotTables(1).PivotFields("Order_Status_Code").Position = 2
这样不是很难懂吧？@#$@%@

数据区的行例转换
如果像这样一个透视表要把数据从行显示转成例显示，可以直接修改数据区的属性就行了。。。

ActiveSheet.PivotTables(1).DataPivotField.Orientation = xlColumnField
当然，转回来只要小小修改一下就行了
ActiveSheet.PivotTables(1).DataPivotField.Orientation = xlRowField

禁止透视表字段拖拉
人家一拖，格式就完全变样了。。。。如何防止？我们可以把字段的拖拉限制一下~~下面这个代码就使这个字段不能拖到例字段上去。。。
ActiveSheet.PivotTables(1).PivotFields("Order_Status_Code").DragToColumn = False
当然，还有很多相关的限制，如下：
DragToHide ‘防止拖出报表
DragToPage ‘防止拖到页面区
DragToRow ‘防止拖到行例表

嗳，数据库，多页字段的汇总，这些最好是用ADO来进行，或是用MS QUERY来进行，这里就不再啰嗦了哈~~~各位慢用~~用完上论坛吼吼~~

如果有人帮助了你,请记往他,并以他帮助你的精神去帮助别人/深山老猫/欢迎您的到来