VBA数据透视表 - 如何在过滤器中选择多个条件?

Question

我无法让我的数据透视表为 B3 过滤器选择多个值。当我以数组形式输入条件时，它会将其识别为 null - "无法在数据透视表中输入 null 值作为项或字段名称。有没有一种简单的方法可以做到这一点？我无法从谷歌找到解决方案。

Sub Button5_Click()

Dim docworksheet As Worksheet
Dim docworkbook As Workbook
Set docworkbook = ThisWorkbook
Set docworksheet = docworkbook.Sheets("Analysis")

docworksheet.Activate
ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False

ActiveSheet.Range("B4").Value = "(blank)"
ActiveSheet.Range("B5").Value = "(All)"
ActiveSheet.Range("B2").Value = "(All)"
ActiveSheet.Range("B3").Value = Array("A", "B", "C")
ActiveSheet.Range("B7").Value = "L"
End Sub

Answer 1

点击"录制宏"，然后执行您的操作，然后点击"停止录制"。

你应该有这样的东西：

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Field Name Here")
.PivotItems("First Item Name").Visible = True
.PivotItems("Second Item Name").Visible = False
.PivotItems("Third Item Name").Visible = True
End With

这将逐个检查并设置每个项目。

因此，循环遍历PivotField中的所有PivotItems，并与Array中的值进行比较(例如，使用Filter函数( - 如下所示：

Public Sub FilterPivotByArray(Target As PivotField, Values() As String)
Dim piTMP As PivotItem, bManualUpdate As Boolean
With Target.Parent
bManualUpdate = .ManualUpdate 'Store setting for later
.ManualUpdate = True 'Turn on Manual Update to speed things up
End With
Target.ClearAllFilters
For Each piTMP In Target.PivotItems
'Visible if Value is in Array
'Not Visible if Value is Not in Array
piTMP.Visible = (UBound(Filter(Values, piTMP.Value)) >= 0)
Next piTMP
With Target.Parent
.Update
.ManualUpdate = bManualUpdate 'restore setting
End With
End Sub