VBA 数据透视表的创建

创建一个数据透视表（PivotTable）

创建方法一

通常会先创建一个数据缓存（PivotCache），然后再创建透视表（PivotTable）

创建透视缓存的语法如下

Workbook.PivotCaches.Create(SourceType, [SourceData], [Version])

创建透视表的语法如下

PivotCache.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

PivotCaches.Create 中的参数说明

参数名称	必需/可选	数据类型	说明
SourceType	必需	XlPivotTableSourceType	数据的来源。_SourceType_可以是下列XlPivotTableSourceType常量之一： xlConsolidation 、 xlDatabase 、xlExternal
SourceData	可选	Variant	数据透视表缓存的数据地址，以文本表示。 当 SourceType 不为 xlExternal 时，是必须参数。
Version	可选	Variant	数据透视表的版本。 _Version_可以是 XlPivotTableVersionList 常量之一。

PivotCache.CreatePivotTable 中的参数说明

名称	必需/可选	数据类型	说明
TableDestination	必需	Variant	数据透视表目标区域 (工作表上将放置生成的数据透视表的区域) 左上角的单元格。 目标区域必须位于工作簿（此工作簿包含由 expression 指定的 PivotCache 对象）的某个工作表中。
TableName	可选	Variant	新的数据透视表的名称。
ReadData	可选	Variant	如果为 True, 则创建包含外部数据库中的所有记录的数据透视表缓存;此缓存可能非常大。 如果为 False，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。
DefaultVersion	可选	Variant	数据透视表的默认版本。

XlPivotTableSourceType 的值与说明

名称	值	说明
xlConsolidation	3	多重合并计算数据区域。
xlDatabase	1	Microsoft Excel 列表或数据库。
xlExternal	2	其他应用程序中的数据。
xlPivotTable	-4148	与另一数据透视表相同来源。
xlScenario	4	数据基于使用方案管理器创建的方案。

XlPivotTableVersionList 的值与说明

名称	值	说明
xlPivotTableVersion2000	0	Excel 2000
xlPivotTableVersion10	1	Excel 2002
xlPivotTableVersion11	2	Excel 2003
xlPivotTableVersion12	3	Excel 2007
xlPivotTableVersion14	4	Excel 2010
xlPivotTableVersion15	5	Excel 2013
xlPivotTableVersionCurrent	-1	仅为向后兼容性而提供

创建方法二

PivotTables.Add (PivotCache, TableDestination, TableName, ReadData, DefaultVersion)

名称	必需/可选	数据类型	说明
PivotCache	必需	PivotCache	表示一个数据透视表缓存，而新的数据透视表将基于此缓存。 缓存用于为报表提供数据。
TableDestination	必需	Variant	数据透视表目标区域（工作表中用于放置所生成的报表的区域）左上角的单元格。 必须在工作表中（此工作表包含由 expression 指定的 PivotTables 对象）指定一个目标区域。
TableName	可选	Variant	新的数据透视表的名称。
ReadData	可选	Variant	如果为 True，则创建数据透视表缓存以包含外部数据库中的所有记录；此时缓存可能会很大。 如果为 False，则允许在实际读取数据之前将某些字段设置为基于服务器的页字段。
DefaultVersion	可选	Variant	最初在其中创建数据透视表的 Microsoft Excel 的版本。

透视表样式设置中的部分重要属性

PivotTable 对象的属性

属性	读/写	值类别	默认值	说明
.Name	读/写	String		透视表名称
.ColumnGrand	读/写	Boolean	True	是否启用列汇总
.RowGrand	读/写	Boolean	True	是否启用行汇总
.HasAutoFormat	读/写	Boolean	True	数据透视表在刷新或移动域时自动设置格式
.DisplayErrorString	读/写	Boolean	False	有错误的单元格是否显示用户自定义的错误字符串
.ErrorString	读/写	String		DisplayErrorString 属性为 True 时，如果单元格中有错误而显示的字符串
.DisplayNullString	读/写	Boolean	True	包含空值的单元格中是否显示用户自定义的字符串
.NullString	读/写	String	空字符串 ("")	当 DisplayNullString 属性为 True 时, 在包含 null 值的单元格中显示的字符串
.EnableDrilldown	读/写	Boolean	True	是否启用“显示明细数据”
.MergeLabels	读/写	Boolean	False	数据透视表的外部行项、列项、分类汇总和总计标志使用合并单元格
.PageFieldOrder	读/写	Long	xlDownThenOver	将页字段添加到数据透视表的布局中的顺序 可以是下列的XlOrder 常量之一: xlDownThenOver或xlOverThenDown
.PageFieldWrapCount	读/写	Long	0	数据透视表中每行或每列的页字段数目
.PreserveFormatting		Boolean	True	透视、排序或更改页字段项等操作刷新或重新计算报表时保留透视表格式
.SaveData	读/写	Boolean	True	是否保留数据透视表缓存（即 PivotCache） 如果数据透视表的数据随工作簿一起保存，则为 True。 如果仅保存数据透视表的定义，则为 False。
.PrintTitles	读/写	Boolean	False	如果基于数据透视表设置工作表的打印标题，则该属性值为 True。 如果使用工作表的打印标题，则该属性值为 False。
.RepeatItemsOnEachPrintedPage	读/写	Boolean	True	当打印指定的数据透视表时，如果每页第一行上都显示行、列和项标志，则该值为 True。 如果仅在第一页上打印这些标志，则该值为 False。
.TotalsAnnotation	读/写	Boolean	False	是否在指定的数据透视表中的每个分类汇总和总计值的旁边显示() (如果报表基于 OLAP 数据源)。
.CompactRowIndent	读/写		1	当启用压缩行布局表单时，返回或设置透视项目的缩进增量。 读/写。默认值为 1。 此设置的有效值为0到 Microsoft Excel 中指定的最大缩进量。
.InGridDropZones	读/写	Boolean	False	此属性用于为 PivotTable 对象切换网格中的拖放区域。 在一些情况下，它还会影响数据透视表的布局。 当 InGridDropZones 属性设置为 True 时，存在网格中的拖放区域。 当此属性设置为 False 时，不存在网格中的拖放区域。数据透视表的布局也会随此属性一起改变。
.DisplayFieldCaptions	读/写	Boolean	True	控制是否在网格中显示行和列的筛选按钮和透视字段标题。
.DisplayMemberPropertyTooltips	读/写	Boolean	False	控制是否在工具提示中显示成员属性。
.DisplayContextTooltips	读/写	Boolean	True	控制是否为数据透视表单元格显示工具提示。
.ShowDrillIndicators	读/写	Boolean	True	用于切换数据透视表中深化指示符的显示。
.PrintDrillIndicators	读/写	Boolean	False	指定是否使用数据透视表打印钻取标识符。
.AllowMultipleFilters	读/写	Boolean	False	如果此属性设置为 True，可将多个筛选应用于一个透视字段。 如果此属性设置为 False，在对已应用了筛选的透视字段应用筛选时，将删除现有筛选，然后应用新筛选。 如果数据透视表中有多个筛选已应用的字段, 则将此属性设置为False将无提示删除数据透视表中的所有筛选器, 而不显示任何警告。 但是通过用户界面删除筛选时，将显示警告。
.SortUsingCustomLists	读/写	Boolean	True	控制是否使用 自定义列表对字段的项进行排序, 包括在 初始化数据透视域和数据透视项时，以及之后当用户应用排序时也是如此。 将此属性设置为 False 可优化包含许多项目的字段的性能, 并且它还允许不需要基于自定义列表的排序功能.
.FieldListSortAscending	读/写	Boolean	False	控制数据透视表字段列表中字段的排序顺序。 当此属性设置为 True 时，字段按升序顺序排序。 当它设置为 False 时，字段按数据源顺序排序。
.ShowValuesRow	读/写	Boolean	False	是否显示值行。
.CalculatedMembersInFilters	读/写	Boolean	False	是否在筛选器中对 OLAP 服务器的计算成员进行求值。
.RowAxisLayout				此方法用于同时为所有现有的透视字段设置版式选项。指定版式行的类型。 xlCompactRow 0 压缩行；xlOutlineRow 2 大纲行；xlTabularRow 1 表格行

.PageFieldOrder 属性的可选值及其说明

名称	值	说明
xlDownThenOver	1	向下处理行，然后向右逐个处理页或页面字段。
xlOverThenDown	2	向右逐个处理页或页面字段，然后向下处理行。

.RowAxisLayout （报表的显示布局） 属性的可选值及其说明

名称	值	说明
xlCompactRow	0	压缩形式显示
xlTabularRow	1	表格形式显示
xlOutlineRow	2	大纲形式显示

添加透视字段（PivotField）及相应设置

PivotField 对象是 PivotFields 集合的成员
向透视表中添加字段时，直接如下方式引用字段名称即可

WorkSheet.PivotTables("透视表").PivotFields("字段")

以下为整理自 Microsoft 帮助文件 中的 PivotField 常用的一些 “读/写” 属性及其说明

方法	读/写	值类别	默认值	说明
.Orientation	读/写	XlPivotFieldOrientation		返回或设置一个**.XlPivotFieldOrientation** 值, 它代表指定数据透视表中的字段的位置。
.Position	读/写	Variant		"返回或设置一个Variant值, 它代表其方向 (行、列、页、数据) 中的所有字段中的字段位置 (第一个、第二个、第三个, 等等)。
.Function	读/写	XlConsolidationFunction		返回或设置对数据透视表字段汇总时所使用的函数（仅用于数据字段）。
.LayoutForm	读/写	XlLayoutFormType		返回或设置指定的数据透视表项出现的方式，即以表格格式还是以分级显示格式显示。
.Calculation	读/写	XlPivotFieldCalculation		返回或设置一个**.XlPivotFieldCalculation** 值, 该值代表指定的字段执行的计算类型。 此属性仅对数据字段有效。
.Value	读/写	String		返回或设置一个 String 值，它代表数据透视表中指定的字段的名称。
.VisibleItemsList	读/写	Variant		返回或设置一个 Variant 类型的值，该值指定一个字符串数组，字符串代表应用于透视字段的手动筛选中的包含项。
.DatabaseSort	读/写	Boolean		如果为 True，则允许手动更改数据透视表字段中项目的位置。 如果字段没有手动定位的项，则返回 True 。
.DataType		XlPivotFieldDataType		返回一个XlPivotFieldDataType 值, 它代表数据透视表字段中的数据类型。
.DragToColumn	读/写	Boolean	True	如果指定字段能被拖动到列位置上，则为 True。
.DragToData	读/写	Boolean	True	如果指定字段可被拖动到数据位置上，则为 True。
.DragToHide	读/写	Boolean	True	如果通过将字段拖离数据透视表可隐藏该字段，则为 True。
.DragToPage	读/写	Boolean	True	如果字段可被拖动到页位置上，则为 True。
.DragToRow	读/写	Boolean	True	如果字段可被拖动到行位置上，则为 True。
.DrilledDown	读/写	Boolean	True	如果指定数据透视表字段或数据透视表项的标志设置为“drilled”（展开或可见），则为 True。
.EnableItemSelection	读/写	Boolean	True	如果为 False，则在用户界面中禁止使用下拉字段的功能。
.LayoutPageBreak	读/写	Boolean	False	如果每个字段后都插入了分页符，则该值为 True。
.ShowDetail	读/写	Boolean		获取或设置指定的透视字段对象是否显示详细信息
.LayoutCompactRow	读/写	Boolean		指定在选择行时是否压缩透视字段（在一列中显示多个透视字段的项目）
.LayoutSubtotalLocation	读/写	XlSubtotalLocationType		返回或设置与指定字段相关（在其上面或下面）的数据透视表字段分类汇总的位置。
.Name	读/写	String		返回或设置一个 String 值，它代表对象的名称。
.NumberFormat	读/写	String		返回或设置一个 String 值，它代表对象的格式代码。
.PropertyOrder	读/写			只对属于成员属性字段的数据透视表字段有效。 返回一个 Long 类型的数值，该数值表示成员属性在其所属的多维数据集字段内的显示位置
.Subtotals	读/写	Variant		返回或设置与指定字段同时显示的分类汇总。 仅对非数据字段有效。
.RepeatLabels	读/写			返回或设置在数据透视表中是否对指定的透视字段重复项目标签。
.SubtotalName	读/写	String。		返回或设置显示在指定数据透视表的分类汇总列或行标题中的文本字符串标志。
.UseMemberPropertyAsCaption	读/写	Boolean		此属性用于控制是否将成员属性标题用于透视字段的 PivotItem 标题
.XlPivotFieldDataType	读/写	Boolean	True	此属性用于指定是否在工具提示中显示透视字段的特定成员属性
.DisplayInReport	读/写	Boolean	True	此属性用于指定是否在数据透视表中显示指定的成员属性透视字段
.HiddenItemsList	读/写	Variant		返回或设置一个Variant类型的值, 该值指定作为数据透视表字段的隐藏项的字符串数组
.BaseField	读/写	Variant		返回或设置自定义计算的基准字段。 本属性仅对数据字段有效 此属性对于 OLAP 数据源无效
.BaseItem	读/写	Variant		返回或设置用于自定义计算的基本字段中的项。 仅对数据字段有效。 此属性对于 OLAP 数据源无效。
.Caption				返回一个 String 值，它代表数据透视字段的标签文本。
.CurrentPage	读/写	PivotItem		返回或设置页字段的当前页显示（仅对页字段有效）。
.EnableMultiplePageItems	读/写	Boolean		用于指定是否在页面区域中的字段的筛选器下拉列表中显示复选框。
.CurrentPageList	读/写	Variant		返回或设置对应于项目列表的字符串数组，该项目列表包含于数据透视表的多项目页字段中。若要避免运行时错误, 数据源必须是 OLAP 源, 选择的字段当前必须位于页面位置中, 并且EnableMultiplePageItems 属性必须设置为True。
.CurrentPageName	读/写	String		返回或设置指定数据透视表上的当前显示页。 该页名称将出现在页字段中。 注意，只有当已存在当前显示页时，本属性才有效。本属性应用于与 OLAP 数据源相连的数据透视表。 如果用未与 OLAP 数据源相连的数据透视表返回或设置本属性，则将导致运行时错误。
.Formula	读/写	String		返回或设置一个 String 值，它代表 A1 样式表示法和宏语言中的对象的公式
.Hidden	读/写	Boolean		此属性用于隐藏 OLAP 层次结构的各个级别。
.IncludeNewItemsInFilter	读/写	Boolean		在将手动筛选应用于透视字段时，此属性允许开发人员指定是应跟踪排除的项目还是应跟踪包含的项目。
.LayoutBlankLine	读/写	Boolean	False___	如果在数据透视表的指定行字段后插入了一个空行，则该值为 True。
.MemberPropertyCaption	读/写	Boolean		设置MemberPropertyCaption属性可控制将哪个成员属性用作给定级别的标题。
.ServerBased	读/写	Boolean		如果指定数据透视表的数据源为外部数据源，并且只检索与选定页字段相匹配的数据项，则该属性值为 True。
.ShowAllItems	读/写	Boolean	False	如果显示数据透视表中的所有项, 即使它们不包含摘要数据也是如此。
.StandardFormula	读/写			返回或设置一个 String 值，该值指定使用标准英语（美国）格式的公式，主要影响具有日期或数字格式的项目名称。

.Orientation 属性中 XlPivotFieldOrientation 参数其值及说明如下

名称	值	说明
xlHidden	0	Hidden，隐藏
xlRowField	1	Row，行
xlColumnField	2	Column，列
xlPageField	3	Page，筛选
xlDataField	4	Data，数据