vba 筛选包含值的字段的数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15559882/
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 Pivot Table for fields containing a value
提问by user2197263
I am trying to filter the pivottable field "name" to only display records that have "AA5" in the title.
我正在尝试过滤数据透视表字段“名称”以仅显示标题中包含“AA5”的记录。
When I record the macro, it seems to only de-select the newest items that do not contain AA5 (using the false property). Rather than using the AA5=true property.
当我录制宏时，它似乎只取消选择不包含 AA5 的最新项目（使用 false 属性）。而不是使用 AA5=true 属性。
However, since the pivot is constantly updated with new data, running the macro a second time does not remove the newly added items, since the code has identified which NOT to show, rather than which TO show.
但是，由于数据透视表会不断更新新数据，因此第二次运行宏不会删除新添加的项目，因为代码已确定不显示哪些，而不是显示哪些。
Sub Macro3()
Range("A8").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
.PivotItems("Test:777:1").Visible = False
.PivotItems("Test:777:2").Visible = False
.PivotItems("Test:777:3").Visible = False
End With
End Sub
回答by Kazimierz Jawor
It would go this way:
它会这样：
Sub Quick()
Dim PTfield As PivotField
Set PTfield = ActiveSheet.PivotTables(1).PivotFields("Name")
With PTfield
.ClearAllFilters
.PivotFilters.Add xlCaptionEquals, , "AA5" 'for exact matching
End With
End Sub
If AA5is a part of filtering fields, then change parameter into xlCaptionContains.
如果AA5是过滤字段的一部分，则将参数更改为xlCaptionContains.
回答by Jossy
Here is a simple macro that actually works. I have a pivot table that contains on Column H Order Fill Rate % of various finished goods. Order Fill Rate of 98.5% or lower is considered no good. This macro will go to Column H starting with Row 5 and go down row by row to check the fill rates. It will hide the rows that are good leaving visible those rows that require futher analysis or action. It will keep doing this until it reaches the "last row"
这是一个实际有效的简单宏。我有一个数据透视表，其中包含各种成品的 H 列订单填充率 %。98.5% 或更低的订单完成率被认为是不好的。此宏将从第 5 行开始转到 H 列，然后逐行向下查看填充率。它将隐藏好的行，让那些需要进一步分析或操作的行可见。它将继续这样做，直到到达“最后一行”
Sub zSeries09_FilterPivotTable_ByFillRate98pt5() ' ' zSeries09_FilterPivotTable_ByFillRate98pt5 Macro '
Sub zSeries09_FilterPivotTable_ByFillRate98pt5() ' ' zSeries09_FilterPivotTable_ByFillRate98pt5 宏'
' Application.Calculation = xlManual
' Application.Calculation = xlManual
Dim LR As String
Selection.SpecialCells(xlCellTypeLastCell).Select
LR = ActiveCell.Row
Application.Goto Reference:="R5C8"
Routine:
常规：
If ActiveCell.Row < LR Then GoTo TestError Else GoTo EndCom
TestError:
测试错误：
If IsError(ActiveCell) = True Then GoTo HideRow Else
TestBlank:
测试空白：
If IsEmpty(ActiveCell) = True Then GoTo HideRow Else
TestFillRate:
测试填充率：
If ActiveCell.Value >= 0.985 Then GoTo HideRow Else
NoHide:
不隐藏：
Application.Goto Reference:="R[1]C"
GoTo Routine
HideRow:
隐藏行：
Selection.EntireRow.Hidden = True
Application.Goto Reference:="R[1]C"
GoTo Routine
EndCom:
终端通讯：
Application.Calculation = xlAutomatic
End Sub
结束子
