使用 VBA 过滤数据透视表中具有特定文本的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42929493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Filter items with certain text in a Pivot Table using VBA
提问by J. L. Muller
I've been trying to build a code to filter all items within a Pivot Table which contain a specific text fragment. I initially imagined I could use asterisks (*) to indicate any string before or after my text, but VBA reads that as a character instead. This is necessary to display the Pivot Table array in a Userform Listbox. Look what I tried:
我一直在尝试构建一个代码来过滤数据透视表中包含特定文本片段的所有项目。我最初想象我可以使用星号 (*) 来表示文本前后的任何字符串，但 VBA 将其读取为字符。这是在用户窗体列表框中显示数据透视表数组所必需的。看看我尝试了什么：
Sub FilterCstomers()
Dim f As String: f = InputBox("Type the text you want to filter:")
With Sheets("Customers Pivot").PivotTables("Customers_PivotTable")
.ClearAllFilters
.PivotFields("Concatenation for filtering").CurrentPage = "*f*"
End With
End Sub
回答by Shai Rado
Try the code below to filter all items in field "Concatenation for filtering" that are Likewild card *and String freceived from InputBox.
尝试使用下面的代码来过滤字段“过滤的串联”中的所有项目，这些项目是Like通配符*和f从InputBox.
Option Explicit
Sub FilterCstomers()
Dim PvtTbl As PivotTable
Dim PvtItm As PivotItem
Dim f As String
f = InputBox("Type the text you want to filter:")
' set the pivot table
Set PvtTbl = Sheets("Customers Pivot").PivotTables("Customers_PivotTable")
With PvtTbl.PivotFields("Concatenation for filtering")
.ClearAllFilters
For Each PvtItm In .PivotItems
If PvtItm.Name Like "*" & f & "*" Then
PvtItm.Visible = True
Else
PvtItm.Visible = False
End If
Next PvtItm
End With
End Sub
回答by quollio
Why not just:
为什么不只是：
.PivotFields("PivotFieldName").PivotFilters.Add2 Type:=xlCaptionContains, Value1:="X"
.PivotFields("PivotFieldName").PivotFilters.Add2 Type:=xlCaptionContains, Value1:="X"
回答by jeffreyweir
You can tweak Shai's answer to significantly speed things up, by:
您可以通过以下方式调整 Shai 的答案以显着加快速度：
- removing the TRUE branch of the IF as it is not needed
- setting ManualUpdate to TRUE while the code executes, to stop the PivotTable from recalculating each time you change the visible status of any PivotItems.
Turning off screen updating and calculation (in case there are volatile functions in the workbook) until you are done
You probably also want to put an Option CompareText in there if you want your comparisons to be case insensitive.
- 删除 IF 的 TRUE 分支，因为它不需要
- 在代码执行时将 ManualUpdate 设置为 TRUE，以在每次更改任何数据透视项的可见状态时停止重新计算数据透视表。
关闭屏幕更新和计算（以防工作簿中存在易失性函数），直到完成
如果您希望比较不区分大小写，您可能还想在其中放置一个 Option CompareText。
And you probably want some error handling in case the user types something that doesn't exist in the PivotTable.
并且您可能需要一些错误处理，以防用户键入数据透视表中不存在的内容。
You might want to give my blogpost on this stuffa read, because PivotTables are very slow to filter, and it discusses many ways to speed things up
您可能想阅读我关于这些内容的博文，因为数据透视表的过滤速度非常慢，并且它讨论了许多加快速度的方法
Here's a reworked example of Shai's code:
这是一个重新设计的 Shai 代码示例：
Option Explicit
Option Compare Text
Sub FilterCstomers()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim f As String
f = InputBox("Type the text you want to filter:")
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set pt = Sheets("Customers Pivot").PivotTables("Customers_PivotTable")
Set pf = pt.PivotFields("Concatenation for filtering")
pt.ManualUpdate = True
With pf
.ClearAllFilters
On Error GoTo ErrHandler
For Each pi In .PivotItems
If Not pi.Name Like "*" & f & "*" Then
pi.Visible = False
End If
Next pi
End With
ErrHandler:
If Err.Number <> 0 Then pf.ClearAllFilters
pt.ManualUpdate = False
On Error GoTo 0
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
