如何利用Excel中的VBA在数据透视表中添加百分比列

Sub AddPercentageColumnToPivotTable()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim dataRange As Range
    Dim cell As Range
    
    ' 修改"Sheet1"为你的透视表所在的工作表名字
    Set pt = Worksheets("Sheet1").PivotTables("PivotTable1")
    
    ' 修改"ColumnLabel"为你想要添加百分比列的列名
    Set pf = pt.PivotFields("ColumnLabel")
    
    ' 修改"A1"为你的透视表数据区域的起始单元格
    Set dataRange = Range("A1").CurrentRegion
    
    ' 添加百分比列
    With pt
        .AddDataField .PivotFields("Value"), "Percentage", xlSum
        .DataFields("Percentage").NumberFormat = "0.00%"
        
        For Each cell In dataRange.Columns(1).Cells
            ' 计算百分比
            cell.Offset(0, pf.Position + 1).FormulaR1C1 = "=RC[-1]/SUMIF(" & pf.DataRange.Address & ", RC" & "," & dataRange.Columns(pf.Position + 1).Address & ")"
        Next cell
    End With
    
End Sub