[Excel VBA办公]VBA代码创建透视表

[Excel VBA办公]VBA代码创建透视表

原始数据如下有日期、单价、数量、金额 4个字段。

创建透视表的关键代码：

将对应字段放在对应的  行xlRowField、列、值AddDataField位置

完整代码：

这个子程序的目的在于创建一个数据透视表，它首先检查是否有一个名为"透视表"的工作表，如果没有，就创建一个。然后，它使用指定的数据源（"Sheet1"中的"A1:D100"范围）来创建数据透视表缓存，并在新创建或已存在的工作表上创建数据透视表。最后，它将"日期"字段设置为行字段，并添加了"数量"和"金额"字段作为数据字段，计算它们的总和。

Sub CreatePivotTable()
    ' 定义变量
    Dim ptCache As PivotCache
    Dim pt As PivotTable
    Dim wsSource As Worksheet
    Dim wsDest As Worksheet
    Dim rngSource As Range
    Dim sheetName As String
    Dim sheetExists As Boolean

    ' 设置数据源范围
    ' 将wsSource变量设置为ThisWorkbook（当前工作簿）中的"Sheet1"工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    ' 将rngSource变量设置为"Sheet1"工作表中的"A1:D100"范围
    Set rngSource = wsSource.Range("A1:D100")
    
    ' 设置透视表工作表的名称
    ' 将sheetName变量设置为"透视表"，这是我们将要创建或使用的透视表工作表的名称
    sheetName = "透视表"
    
    ' 检查透视表工作表是否已存在
    ' 初始化sheetExists变量为False，表示默认工作表不存在
    sheetExists = False
    ' 遍历当前工作簿中的所有工作表
    For Each ws In ThisWorkbook.Sheets
        ' 如果找到名称与sheetName相同的工作表，则设置sheetExists为True并退出循环
        If ws.Name = sheetName Then
            sheetExists = True
            Exit For
        End If
    Next ws
    
    ' 如果透视表工作表不存在，则创建它
    ' 如果sheetExists为False，表示需要创建新的工作表
    If Not sheetExists Then
        ' 在工作簿末尾添加一个新的工作表，并将其赋值给wsDest变量
        Set wsDest = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        ' 将新工作表的名称设置为sheetName
        wsDest.Name = sheetName
    Else
        ' 如果透视表工作表已存在，则使用它
        ' 将wsDest变量设置为已存在的名为sheetName的工作表
        Set wsDest = ThisWorkbook.Sheets(sheetName)
    End If
    
    ' 创建数据透视表缓存
    ' 使用rngSource范围内的数据创建一个数据透视表缓存，并将其赋值给ptCache变量
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngSource)
    
    ' 创建数据透视表
    ' 在wsDest工作表的"B2"单元格位置创建一个新的数据透视表，命名为"PivotTable1"，并将其赋值给pt变量
    Set pt = ptCache.CreatePivotTable(TableDestination:=wsDest.Cells(2, 2), TableName:="PivotTable1")
    
    ' 添加数据透视表字段
    ' 使用With语句来简化对pt对象（数据透视表）的多次引用
    With pt
        ' 将"日期"字段设置为行字段，这意味着数据透视表将按日期分组显示数据
        .PivotFields("日期").Orientation = xlRowField
        ' 添加一个新的数据字段"数量"，计算其总和，并命名为"总数量"
        .AddDataField .PivotFields("数量"), "总数量", xlSum
        ' 添加一个新的数据字段"金额"，计算其总和，并命名为"总金额"
        .AddDataField .PivotFields("金额"), "总金额", xlSum
    End With
End Sub