使用 VBA 更新和过滤数据透视表 - 通配符过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27449142/
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
Update and filter pivot with VBA - Wildcard filter
提问by 12345
I want to clear the prevoius filter on pivotfield Invoicenr, update a pivot table, and not show certain items.
I want to show Everything but the items that have a Invoicenrthat begins with PO*(seems that * can't be used in VBA?).
Besides this I want to see everything else and the Invoicenrthat starts with POand contains OH.
我想清除透视字段的prevoius过滤Invoicenr，更新数据透视表，并没有显示出某些项目。
我想显示除Invoicenr以PO*开头的项目以外的所有内容（似乎 * 不能在 VBA 中使用？）。
除此之外，我想查看其他所有内容以及以PO开头并包含OH的Invoicenr。
See my attempt below:
请参阅下面的我的尝试：
Sub Macro2()
'
' Macro2 Macro
'
ThisWorkbook.RefreshAll
'Worksheets("Pivot").Select
'ActiveSheet.PivotTables("PIVOT1").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("PIVOT1").PivotFields("Invoicenr"). _
ClearLabelFilters
With ActiveSheet.PivotTables("PIVOT1").PivotFields("invoicenr")
.PivotItems("PO").Visible = False
End With
End Sub
回答by mb2011
If I'm understanding the conditions correctly, this should get you the results you want for the first case...
如果我正确理解了条件，这应该会为您提供第一种情况所需的结果......
Show All Items except ones that begin with "PO" :
显示除以“PO”开头的所有项目：
Sub ShowAllButPO()
Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PIVOT1")
Set pvtField = pvtTable.PivotFields("Invoicenr")
pvtTable.RefreshTable
pvtTable.ClearAllFilters
For Each pvtItem In pvtField.PivotItems
If Left(UCase(pvtItem), 2) = "PO" Then
pvtItem.Visible = False
End If
Next
End Sub
And this should cover the second condition...
这应该涵盖第二个条件......
Show All Items in "invoicenr" that start with "PO" andalso contain "OH" :
显示 "invoicenr" 中以 "PO" 开头且还包含 "OH" 的所有项目：
Sub ShowOnlyPO()
Dim ws As Worksheet
Dim pvtTable As PivotTable
Dim pvtField As PivotField
Dim pvtItem As PivotItem
Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PIVOT1")
Set pvtField = pvtTable.PivotFields("Invoicenr")
pvtTable.RefreshTable
pvtTable.ClearAllFilters
For Each pvtItem In pvtField.PivotItems
If Left(UCase(pvtItem), 2) = "PO" And InStr(UCase(pvtItem), "OH") > 0 Then
pvtItem.Visible = True
Else
pvtItem.Visible = False
End If
Next
End Sub
I'm less sure about what you wanted for the second condition. Your wording "i want to see Everything else and the invoicenr that starts with PO and contains "OH"" wasn't completely clear to me.
我不太确定你想要什么第二个条件。您的措辞“我想查看其他所有内容以及以 PO 开头并包含“OH”的发票”对我来说并不完全清楚。
If you could clarify what you mean by "Everything elseand invoicenr that starts with PO.. etc etc" then I can update my code if needed.
如果您可以澄清“以 PO 开头的所有其他内容和发票等”的意思，那么我可以根据需要更新我的代码。
Also, if those two code blocks end up getting you what you want, then you could just assign each macro to its own button in your worksheet. That way, you could just toggle the two scenarios without having to open the VBEditor to run the code. If you are unsure how to do this, check out this link
此外，如果这两个代码块最终满足您的需求，那么您可以将每个宏分配给工作表中的自己的按钮。这样，您就可以切换这两个场景，而无需打开 VBEditor 来运行代码。如果您不确定如何执行此操作，请查看此链接
回答by MAF
Use this code:
使用此代码：
Sub Except_PO()
Dim var As Variant
var = "PO*"
ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").ClearAllFilters
ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").PivotFilters. _
Add Type:=xlCaptionDoesNotEqual, Value1:=var
End Sub
Sub POwithOH()
Dim var As Variant
var = "PO*OH*"
ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").ClearAllFilters
ActiveSheet.PivotTables("Pivot1").PivotFields("Invoicenr").PivotFilters. _
Add Type:=xlCaptionEquals, Value1:=var
End Sub
Then make 2 command buttons with this code
然后使用此代码制作 2 个命令按钮
filtering All EXCEPT PO
过滤除 PO 之外的所有内容
Private Sub CommandButton1_Click()
Call Except_PO
End Sub
Filtering data starting with PO and contains OH
过滤以 PO 开头并包含 OH 的数据
Private Sub CommandButton2_Click()
Call POwithOH
End Sub
So if you click CommandButton1, your pivot will filter those data that don't start with PO. And when you click CommandButton2, your pivot will filter all data that starts with PO AND contains OH.
因此，如果您单击 CommandButton1，您的数据透视表将过滤那些不以 PO 开头的数据。当您单击 CommandButton2 时，您的数据透视表将过滤所有以 PO 开头并包含 OH 的数据。
