VBA 数据透视表的创建

VBA 学习笔记 专栏收录该内容
15 篇文章 4 订阅

创建一个数据透视表(PivotTable)

创建方法一

通常会先创建一个数据缓存(PivotCache),然后再创建透视表(PivotTable

创建透视缓存的语法如下

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

创建透视表的语法如下

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

PivotCaches.Create 中的参数说明

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

PivotCache.CreatePivotTable 中的参数说明

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

XlPivotTableSourceType 的值与说明

名称说明
xlConsolidation3多重合并计算数据区域。
xlDatabase1Microsoft Excel 列表或数据库。
xlExternal2其他应用程序中的数据。
xlPivotTable-4148与另一数据透视表相同来源。
xlScenario4数据基于使用方案管理器创建的方案。

XlPivotTableVersionList 的值与说明

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

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

名称说明
xlDownThenOver1向下处理行,然后向右逐个处理页或页面字段。
xlOverThenDown2向右逐个处理页或页面字段,然后向下处理行。

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

名称说明
xlCompactRow0压缩形式显示
xlTabularRow1表格形式显示
xlOutlineRow2大纲形式显示

添加透视字段(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 。
.DataTypeXlPivotFieldDataType返回一个XlPivotFieldDataType 值, 它代表数据透视表字段中的数据类型。
.DragToColumn读/写BooleanTrue如果指定字段能被拖动到列位置上,则为 True。
.DragToData读/写BooleanTrue如果指定字段可被拖动到数据位置上,则为 True。
.DragToHide读/写BooleanTrue如果通过将字段拖离数据透视表可隐藏该字段,则为 True。
.DragToPage读/写BooleanTrue如果字段可被拖动到页位置上,则为 True。
.DragToRow读/写BooleanTrue如果字段可被拖动到行位置上,则为 True。
.DrilledDown读/写BooleanTrue如果指定数据透视表字段或数据透视表项的标志设置为“drilled”(展开或可见),则为 True。
.EnableItemSelection读/写BooleanTrue如果为 False,则在用户界面中禁止使用下拉字段的功能。
.LayoutPageBreak读/写BooleanFalse如果每个字段后都插入了分页符,则该值为 True。
.ShowDetail读/写Boolean获取或设置指定的透视字段对象是否显示详细信息
.LayoutCompactRow读/写Boolean指定在选择行时是否压缩透视字段(在一列中显示多个透视字段的项目)
.LayoutSubtotalLocation读/写XlSubtotalLocationType返回或设置与指定字段相关(在其上面或下面)的数据透视表字段分类汇总的位置。
.Name读/写String返回或设置一个 String 值,它代表对象的名称。
.NumberFormat读/写String返回或设置一个 String 值,它代表对象的格式代码。
.PropertyOrder读/写只对属于成员属性字段的数据透视表字段有效。 返回一个 Long 类型的数值,该数值表示成员属性在其所属的多维数据集字段内的显示位置
.Subtotals读/写Variant返回或设置与指定字段同时显示的分类汇总。 仅对非数据字段有效。
.RepeatLabels读/写返回或设置在数据透视表中是否对指定的透视字段重复项目标签。
.SubtotalName读/写String。返回或设置显示在指定数据透视表的分类汇总列或行标题中的文本字符串标志。
.UseMemberPropertyAsCaption读/写Boolean此属性用于控制是否将成员属性标题用于透视字段的 PivotItem 标题
.XlPivotFieldDataType读/写BooleanTrue此属性用于指定是否在工具提示中显示透视字段的特定成员属性
.DisplayInReport读/写BooleanTrue此属性用于指定是否在数据透视表中显示指定的成员属性透视字段
.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读/写BooleanFalse___如果在数据透视表的指定行字段后插入了一个空行,则该值为 True。
.MemberPropertyCaption读/写Boolean设置MemberPropertyCaption属性可控制将哪个成员属性用作给定级别的标题。
.ServerBased读/写Boolean如果指定数据透视表的数据源为外部数据源,并且只检索与选定页字段相匹配的数据项,则该属性值为 True。
.ShowAllItems读/写BooleanFalse如果显示数据透视表中的所有项, 即使它们不包含摘要数据也是如此。
.StandardFormula读/写返回或设置一个 String 值,该值指定使用标准英语(美国)格式的公式,主要影响具有日期或数字格式的项目名称。

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

名称说明
xlHidden0Hidden,隐藏
xlRowField1Row,行
xlColumnField2Column,列
xlPageField3Page,筛选
xlDataField4Data,数据
  • 1
    点赞
  • 1
    评论
  • 20
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

SQL+数据透视表+VBA 使数据透视表走向更灵活,更智能,更适用。 这个是我和师傅一撇首度合作,他提供了文件并提出了要求,我帮他实现其效果 下面从几个方面解释一下: 1、功能 一个源文件和一个通过用SQL查询生成的数据透视表 将源文件拖到电脑的任意位置,甚至将文件名也改掉,用VBA配上代码和窗体找到文件,数据透视表仍然能够正常工作 2、套用 现在来讲讲怎么使做出来的东东适应大家的需要 2、1 用OLE DB窗口引用工作或写SQL语句,因为用这个方法同VBA相通,copy下来代码区的的语句 2、2 打开透视文件,将透视中的字段全部拖出来,也就是变成一个空数据透视表。 右击下面工作图标 或者 工具》宏》visual basic 编辑器,点击模块看到代码区 2、3 将2、1步骤copy的语句commandtext的数据Array中的引号中 .CommandText = Array(" ") 可能不同版本会有一些差别,同时SQL语句中如果添加了文本生成新字段,双引号要成对翻倍 如:"出库" AS 单选项 要改成 ""出库"" AS 单选项 2、4 语句太长的处理:在代码区如果你想好看一些,你可以插入“ _”来换行,当然不能插在一个单词或自动名等中间。 2、5 将文件存盘,重新打开就会有了数据,你可以将字段拖入数据透视表中,创建你自己的数据透视表, 2、6 这样文件就可以使用,相信VBA的引导不用教就可以交给别人使用了 下面附上代码,包含3个区: 1、 工作簿去,打开文件时工作 Private Sub Workbook_Open() Dim OP If Dir(Sheets("path").Range("A1")) = "" Then OP = MsgBox("源文件已被移走,请选择下列选项" + Chr(10) + "1、选择是,重新输入文件全名" + Chr(10) + "2、选择否,打开原有的数据透视表" + Chr(10) + "3、选择取消,关闭文件", vbYesNoCancel, "Scarlett温馨提示") If OP = vbYes Then UserForm1.Show End If If OP = vbNo Then ActiveWorkbook.Close True End If If OP = vbCancel Then Exit Sub End If Else Call refreshpv End If End Sub 2、窗体区,实现文件的查找 Private Sub CommandButton1_Click() Dim fopen As FileDialog Set fopen = Application.FileDialog(msoFileDialogFilePicker) fopen.Show TextBox1.Value = fopen.SelectedItems(1) Set fopen = Nothing End Sub Private Sub CommandButton2_Click() If InStr(TextBox1.Value, ".") > 0 Then Sheets("path").Range("A1") = TextBox1.Value Call refreshpv unload me Else MsgBox "文件名要带路径含后缀的文件名", "Scarlett_88温馨提示" TextBox1.SetFocus End If End Sub Private Sub CommandButton3_Click() Unload Me End Sub Private Sub TextBox1_Change() End Sub Private Sub UserForm_Activate() End Sub Private Sub UserForm_Click() TextBox1.Value = Sheets("path").Range("A1") End Sub 3、模块区,实现SQL语句的地址更新和刷新数据透视表数据源 Sub refreshpv() With ActiveSheet.PivotTables("数据透视表1").PivotCache .Connection = Array( _ "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Sourc
©️2022 CSDN 皮肤主题:黑客帝国 设计师:我叫白小胖 返回首页

打赏作者

风转砂

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值