Sub CreatePivotTable() Dim PTcache As PivotCache Dim pt As PivotTable Application.ScreenUpdating = False ' 如果存在指定工作表，则删除这个工作表 On Error Resume Next Application.DisplayAlerts = False Sheets("PivotSheet").Delete On Error GoTo 0 ' 数据放在PTcache缓存对象中。SourceData参数可以用单元格，也可以用地址 Set PTcache = ActiveWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Range("A1").CurrentRegion.Address) '新建一个工作表，命名为PivotSheet Worksheets.Add ActiveSheet.Name = "PivotSheet" '隐藏excel网格线 ActiveWindow.DisplayGridlines = False '创建透视表，PivotCache:数据缓存对象； 'TableDestination：透视表左上角位置； 'TableName：透视表名字 Set pt = ActiveSheet.PivotTables.Add( _ PivotCache:=PTcache, _ TableDestination:=Range("A1"), _ TableName:="透视表名称") With pt 'xlPageField：筛选，PivotFields使用数字索引，能避免用户修改字段名称报错。比如第一个字段：PivotFields(1) '下面示例：筛选字段‘地市’在数据源的第二个字段，也可以使用PivotFields(2) '.PivotFields("地市").Orientation = xlPageField 'xlRowField：行 .PivotFields("日期").Orientation = xlRowField '日期按照年月组合，单元格只要是透视表日期所在区域任意一个单元格。start和end=True表示日期组合区域包括数据源所有日期。 'periods日期组合形式：从左到右依次为：array(秒，分，小时，天，月，季度，年)，这里组合为年、月，把年月所在位置设置为True即可。 Range("a10").Group Start:=True, End:=True, Periods:=Array(False, False, False, False, True, False, True) 'xlColumnField：列 .PivotFields("地市").Orientation = xlColumnField 'xlDataField：值，默认.Function=xlsum求和 .PivotFields("采购").Orientation = xlDataField .PivotFields("销售").Orientation = xlDataField '多个值，逐行展开，相当于二级index，也可以设置xlcolumnfield逐列展开。 .DataPivotField.Orientation = xlRowField'新建一个计算字段，净增值=销售-采购 .CalculatedFields.Add "净增值", "=销售-采购" .PivotFields("净增值").Orientation = xlDataField;设置数值格式：千位符；DataBodyRange是针对透视表所有单元格 .DataBodyRange.NumberFormat = "0,000"'设置透视表类型 .TableStyle2 = "PivotStyleMedium2"'Hide Field Headers隐藏数据行列的名字，这样透视表看起来规整。 .DisplayFieldCaptions = False '修改字段透视表计算字段名称，不能跟字段同名，命名前面加上一个空格。 .PivotFields("求和项:采购").Caption = " 采购" .PivotFields("求和项:销售").Caption = " 销售" .PivotFields("求和项:净增值").Caption = " 净增值" End With End Sub
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071 其他补充： PivotFields除了Orientation属性外，还有Name（名称）,Function（透视表值汇总依据，比如xlcount计数），NumberFormat（值的数值格式），Calculation（值显示方式，比如xlPercentOfRow行汇总百分比）等，具体设置大家也可以通过录制宏查看相关参数设置。DataPivotField.Orientation = xlRowField，当有多个值计算字段时，我们就需要设置这些计算字段是一行还是以列扩展显示，这里是行扩展显示。如果我们只需要在固定sheet中呈现透视表内容，通常我们会通过公式-自定义名称实现透视表动态数据源选择。这样，当数据源更新时，我们只需要将新的数据写入到数据源sheet中，在原来的透视表基础上调用透视表的refresh刷新下就可以了。```'透视表的名称不一，具体大家可以通过录制宏查看。 ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh 12
测试数据源：
链接：https://pan.baidu.com/s/1kr-zTqvEQ5maWzqoWcfd2g
提取码：0nay
参考：《Excel2016高级VBA编程宝典》
