生成彼此下方的数据透视表。VBA

Sub CreatePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim rng As Range
    
    ' 定义数据源范围
    Set rng = Worksheets("Sheet1").Range("A1:D10")
    
    ' 创建数据透视表
    Set ws = Worksheets.Add
    Set pt = ws.PivotTables.Add(rng, ws.Range("A1"), "PivotTable1")
    
    ' 添加行、列和值字段
    With pt
        .PivotFields("Category").Orientation = xlRowField
        .PivotFields("Product").Orientation = xlColumnField
        .PivotFields("Sales").Orientation = xlDataField
    End With
End Sub

Sub CustomizePivotTable()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    
    ' 获取数据透视表对象
    Set ws = Worksheets("Sheet2")
    Set pt = ws.PivotTables("PivotTable1")
    
    ' 添加筛选字段
    Set pf = pt.PivotFields("Region")
    pf.Orientation = xlPageField
    
    ' 设置值字段计算方式
    pt.PivotFields("Sales").Calculation = xlSum
    
    ' 设置数据透视表格式
    pt.TableStyle2 = "PivotStyleMedium4"
End Sub