excel - 如何在 VBA 中将过滤器应用于我的数据透视图

Question

我正在尝试编写一个宏来创建数据透视图。我需要将数据透视图设置为条形图，其中的列被过滤为仅显示其中 3 列。这是我到目前为止的代码。

'Define Data Range
LastRow = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Dsheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = Dsheet.Cells(1, 1).Resize(LastRow, LastCol)

'Define Pivot Cache
Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange)

'Insert Blank Pivot Table
Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="Studies Impacted")

'Insert Column Fields
 With ActiveSheet.PivotTables("Studies Impacted").PivotFields("Study")
    .Orientation = xlColumnField
    .Position = 1
End With

'Insert Row Fields
With ActiveSheet.PivotTables("Studies Impacted").PivotFields("Workstream")
    .Orientation = xlRowField
    .Position = 1
End With

'Insert Data Field
With ActiveSheet.PivotTables("Studies Impacted").PivotFields("Study")
    .Orientation = xlDataField
    .Position = 1
    .Function = xlCount
    .NumberFormat = "#,##0"
    .Name = "Status Count"
End With


'Format Pivot
ActiveSheet.PivotTables("Studies Impacted").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("Studies Impacted").TableStyle2 = "PivotStyleMedium9"

我尝试过使用 >add FilterType 但它似乎不起作用。我也尝试将其定义为 PivotField 但同样没有结果。

我想要的结果是一个条形图，其中每个工作流仅显示 3 个研究。相反，我得到了所有的研究。

Answer 1

我假设您的数据透视图和表格是耦合的。

您可以使用以下方法过滤最多两个项目

pvtField.ActiveFilters.Add2

(Excel 2013 或更高版本)或

pvtField.ActiveFilters.Add

(早于 Excel 2013)

你会运行这样的代码

Sub main()
Dim pvtTable As PivotTable
Dim pvtFields As PivotFields
Dim pvtField As PivotField
Dim ws As Worksheet

Set ws = ActiveSheet
Set pvtTable = ws.PivotTables(1)
pvtTable.AllowMultipleFilters = True

Set pvtFields = pvtTable.PivotFields
Set pvtField = pvtFields.Item(1)
pvtField.PivotFilters.Add2 xlCaptionEquals, Value1:="A", Value2:="B"
End Sub

否则，您必须循环遍历要过滤的行的数据透视项

代码:

Option Explicit

Sub main()
Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtItems As PivotItems
Dim pvtItem As PivotItem
Dim Index As Long

Set ws = ActiveSheet
Set pvtTable = ws.PivotTables(1)
Set pvtItems = pvtTable.PivotFields(1).PivotItems

For Index = 1 To pvtItems.Count
    Set pvtItem = pvtItems.Item(Index)
    If pvtItem.Value = "A" Or pvtItem.Value = "B" Then
        pvtItem.Visible = False
    End If
Next Index
End Sub