增加透视表字段查询

Question

我正在尝试自动创建报告的过程，我被困在其中的一部分。代码应该创建一个数据透视表，并在行中显示用户名，在值中同时显示用户名的计数。我创建了数据透视表，并通过以下代码在行中设置用户名。

ActiveWorkbook.PivotCaches.Create(xlDatabase, `Sheets("Days").Range("A1:B10000")).CreatePivotTable `Sheets("Days").Range("E1"), "DayData"
ActiveWorkbook.ShowPivotTableFieldList = True

With PivotFields
   With ActiveSheet.PivotTables(1).PivotFields("User Name")
        .Orientation = xlRowField
        .Position = 1
    End With

我需要知道如何才能让它显示用户名的计数除了用户名。可以通过将username拖动到行下，然后再次拖动values下的username来获得username的计数，从而手动表示。

另外，我还可以询问如何对数据透视表应用过滤器吗？

Answer 1

下面是一些示例：

Private Sub DayDataPivotTable()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pCache As PivotCache
    Dim pTable As PivotTable

    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    Set pCache = wb.PivotCaches.Create(xlDatabase, ws.Range("A1:B10000"))
    Set pTable = pCache.CreatePivotTable(ws.Range("E1"), "DayData")
    wb.ShowPivotTableFieldList = True

    With pTable
        With .PivotFields("User Name")
            ' use it as row field:
            .Orientation = xlRowField
            .Position = 1

            ' use it additionally as data field:
            .Orientation = xlDataField
            .Position = 1
            .Caption = "Count of Usernames"
            .Function = xlCount
        End With

        With .RowFields(1)
            ' filter the row field:
            .ClearAllFilters
            .EnableMultiplePageItems = True
            .PivotItems("John Doe").Visible = False
            .PivotItems("(blank)").Visible = False
        End With

        With .PivotFields(2)
            ' use the second column as separate filter:
            .Orientation = xlPageField
            .Position = 1
        End With

    End With
End Sub