excel - 使用 VBA 创建具有不同计数的数据透视表

Question

我正在尝试使用 Excel 2013 VBA 创建一个数据透视表，并将 DISTINCT COUNT 作为值字段。

我了解，如果您手动创建数据透视表，则必须选中“将此数据添加到数据模型”复选框才能获得值数据透视字段的不同计数选项，但我不知道如何将其转换为 VBA代码。

我尝试使用 xlCount 作为值数据透视字段创建数据透视表，它工作正常，但对于 xlDistinctCount 它不起作用

Set wb = ActiveWorkbook
Set ws = wb.Sheets.Add(Type:=xlWorksheet, After:=Application.Worksheets(1))
Worksheets(1).Range("A1:I" & i).Copy
Worksheets(2).Range("A1").PasteSpecial xlPasteValues
Worksheets(2).Name = "PivotTable"

'Defining data range for pivottable
lastrow = Worksheets("PivotTable").Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Worksheets("PivotTable").Cells(1, Columns.Count).End(xlToLeft).column
Set pRange = Worksheets("PivotTable").Cells(1, 1).Resize(lastrow, lastCol)


On Error Resume Next
'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.create _
(SourceType:=xlDatabase, SourceData:=pRange, Version:=xlPivotTableVersion12). _
CreatePivotTable(TableDestination:=Worksheets("PivotTable").Cells(2, 10), _
TableName:="SalesPivotTable")


Set PTable = PCache.CreatePivotTable _
(TableDestination:=Worksheets("PivotTable").Cells(2, 10), TableName:="SalesPivotTable")

With Worksheets("PivotTable").PivotTables("SalesPivotTable").PivotFields("User")
    .Orientation = xlRowField
    .Position = 1
End With

With Worksheets("PivotTable").PivotTables("SalesPivotTable").PivotFields("BinType")
    .Orientation = xlColumnField
    .Position = 1
End With


'Doesn't work with xlDistinctCount but does with xlCount
With Worksheets("PivotTable").PivotTables("SalesPivotTable")
    .AddDataField Worksheets("PivotTable").PivotTables( _
        "SalesPivotTable").PivotFields("AppNo"), "Distinct Count of AppNo", 
         xlDistinctCount
End With

我希望数据透视表在计算最后一行后使用不同的计数进行更新，就像使用 xlCount 一样，但它对 xlDistinctCount 没有任何作用

Answer 1

我刚刚记录了创建一个使用“将此数据添加到数据模型”选项的数据透视表。并创建了一个 Distinct count 字段。
我必须先添加计数，然后将其更改为 Distinct。
我为您的工作表和数据透视表名称修改了它。添加appno的度量计数，然后修改为Distinct。
这是不同的计数部分。

With Worksheets("PivotTable").PivotTables("SalesPivotTable").PivotFields( _
    "[Measures].[Count of AppNo]")
    .Caption = "Distinct Count of AppNo"
    .Function = xlDistinctCount
End With