vba 格式化数据透视表 (.NumberFormat)

Formatting Pivot Table (.NumberFormat)

提问by jonplaca

OBJECTIVE

客观的

Pull in a normalized dataset. Place dataset into a pivot table.

拉入标准化数据集。将数据集放入数据透视表中。

APPROACH

方法

1. Normalize Data
2. Select normalized data and create pivot table
3. Configure pivot table headings and columns
4. Format headings and columns

1. 规范化数据
2. 选择规范化数据并创建数据透视表
3. 配置数据透视表标题和列
4. 格式化标题和列

CODE

代码

Sub createPivot()

Dim ws As Worksheet
Dim pvtCache As pivotCache
Dim pvt As pivotTable
Dim srcData As String
Dim lastRow As Long
Dim startPvt As String
Dim target As Worksheet

'Delete previous pivottable
Worksheets("PIVOT").PivotTables("PivotTable1").TableRange2.Clear

'Select pivot table data
Worksheets("CONSOLIDATED").Activate
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
srcData = ActiveSheet.Name & "!" & Range("A1:H" & lastRow).Address(ReferenceStyle:=xlR1C1)

'Set pivot table location
Set target = ThisWorkbook.Worksheets("PIVOT")
startPvt = target.Name & "!" & target.Range("A1").Address(ReferenceStyle:=xlR1C1)

'Create pivot cache
Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=srcData)

'Deploy pivot table
Set pvt = pvtCache.CreatePivotTable( _
    TableDestination:=startPvt, _
    TableName:="PivotTable1")

    'Add Pivot Fields
    pvt.PivotFields("Fiscal Year").Orientation = xlColumnField
    pvt.PivotFields("Fiscal Year").Position = 1

    pvt.PivotFields("Fiscal Month").Orientation = xlColumnField
    pvt.PivotFields("Fiscal Month").Position = 2

    pvt.PivotFields("Unit").Orientation = xlRowField
    pvt.PivotFields("Unit").Position = 1

    pvt.PivotFields("Project").Orientation = xlRowField
    pvt.PivotFields("Project").Position = 2

    pvt.PivotFields("Base Expense").Orientation = xlDataField

    'Format cells
    pvt.PivotFields("Base Expense").NumberFormat = "$ #,##0"

End Sub

ISSUE

问题

No errors, however pvt.PivotFields("Base Expense").NumberFormat = "$ #,##0"does not take effect.

没有错误，但是pvt.PivotFields("Base Expense").NumberFormat = "$ #,##0"不会生效。

回答by J_Lard

I wrote this macro a while ago to format my PivotTables. It should be pretty easy to modify the code to suit your needs. It loops through each of the value fields and changes the function to sum and formats the value.

不久前我写了这个宏来格式化我的数据透视表。修改代码以满足您的需求应该很容易。它循环遍历每个值字段并将函数更改为求和并格式化该值。

Sub formatPivot()
    Dim pvtTbl As PivotTable
    Dim pvtName As String
    Dim pvtType As String
    Dim pvtFld As PivotField

    pvtName = ActiveCell.PivotTable.Name                ' Get the name of the active pivottable
    Set pvtTbl = ActiveSheet.PivotTables(pvtName)       ' set the pivot table to the active table

    ' Change to sum and update the number format
    For Each pvtFld In pvtTbl.DataFields
        pvtType = pvtFld                                    
        With pvtTbl.PivotFields(pvtType)
            .Function = xlSum
            .NumberFormat = "#,##0"
        End With
    Next
End Sub

回答by Ethan Reilly

I just discover today that you cannot NumberFormat a label field, only a data field, if that helps.

我今天才发现你不能 NumberFormat 标签字段，只能是数据字段，如果有帮助的话。

https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.numberformat

https://docs.microsoft.com/en-us/office/vba/api/excel.pivotfield.numberformat

"Remarks You can set the NumberFormat property only for a data field.

"备注 只能为数据字段设置 NumberFormat 属性。

The format code is the same string as the Format Codes option in the Format Cells dialog box. The Format function uses different format code strings than do the NumberFormat and NumberFormatLocal properties."

格式代码与“单元格格式”对话框中的“格式代码”选项相同。Format 函数使用与 NumberFormat 和 NumberFormatLocal 属性不同的格式代码字符串。”