VBA透视表

最新推荐文章于 2024-05-22 11:05:46 发布

一个散步者的梦

最新推荐文章于 2024-05-22 11:05:46 发布

阅读量7.2k

点赞数 10

分类专栏： VBA 文章标签： VBA 透视表日期组合

本文链接：https://blog.csdn.net/me_to_007/article/details/90116732

版权

VBA 专栏收录该内容

19 篇文章 39 订阅

订阅专栏

目标：按照年月，统计不同日期的销售、采购以及净增（销售-采购）金额。
数据源样式如下：
透视表效果如下：
代码如下：

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

其他补充：
1. PivotFields除了Orientation属性外，还有Name（名称）,Function（透视表值汇总依据，比如xlcount计数），NumberFormat（值的数值格式），Calculation（值显示方式，比如xlPercentOfRow行汇总百分比）等，具体设置大家也可以通过录制宏查看相关参数设置。
2. DataPivotField.Orientation = xlRowField，当有多个值计算字段时，我们就需要设置这些计算字段是一行还是以列扩展显示，这里是行扩展显示。
3. 如果我们只需要在固定sheet中呈现透视表内容，通常我们会通过公式-自定义名称实现透视表动态数据源选择。这样，当数据源更新时，我们只需要将新的数据写入到数据源sheet中，在原来的透视表基础上调用透视表的refresh刷新下就可以了。```
```
'透视表的名称不一，具体大家可以通过录制宏查看。
ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh
```

测试数据源：
链接：https://pan.baidu.com/s/1kr-zTqvEQ5maWzqoWcfd2g
提取码：0nay

参考：《Excel2016高级VBA编程宝典》