使用VBA创建具有不同计数的数据透视表 [英] Creating Pivot Table with Distinct Count using VBA

问题描述

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

I am trying to create a pivot table using Excel 2013 VBA with DISTINCT COUNT as a value field.

我了解，如果您手动创建数据透视表，则必须选中将此数据添加到数据模型"复选框，以使值数据透视表具有不同的计数选项，但是我不知道如何转换转换成VBA代码.

I understand that if you create the pivot table manually, you have to check the "Add this data to the Data Model" checkbox in order to have the distinct count option for the value pivotfield, but I have no idea how to translate this into VBA code.

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

I've tried creating the pivot table with xlCount as the valuue pivotfield and it worked fine, but for xlDistinctCount it does not work

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却不执行任何操作

I expect the pivot table to update with the distinct count once the last line is computed like it does with xlCount but it just doesn't do anything with xlDistinctCount

推荐答案

我刚刚记录了创建一个数据透视表，该数据透视表使用了将此数据添加到数据模型"选项.并创建了一个非重复计数字段. 我必须先添加计数，然后将其更改为Distinct. 我为您的工作表和数据透视表名称进行了修改.添加appno的度量值计数，然后修改为Distinct. 这是不重复计数部分.

I just recorded creating a pivot table that uses the option "Add this data to the Data Model". And created a Distinct count field. I had to add the count first, then change it to Distinct. I modified it for your worksheet and pivot table names. Add the measure count of appno, then modify to Distinct. Here is the distinct count section.

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

这篇关于使用VBA创建具有不同计数的数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！