时间戳

首页 > 解决方案 > 筛选数据透视表数据字段

vba - 筛选数据透视表数据字段

问题描述

我试图弄清楚如何根据计算的总和列（数据字段）正确过滤数据透视表，以便我能够收集过滤后留下的标签和值。示例胜于雄辩，这是我的数据透视表（使用以下代码中的代码创建）：

Row Labels Sum of things XXXXXXXXX 0 YYYYYYYYY 0 ZZZZZZZZZ 2045 AAAAAAAAA 0 BBBBBBBBB 0

我想要的是根据值字段（事物的总和）过滤数据透视表，并且只保留大于 0 的行。

所以那会让我留下

Row Labels Sum of things ZZZZZZZZZ 2045

留下所有元素后，我想获取所有这些值（行标签+大于 0 的总和）并将它们复制到另一张表中。

这是我到目前为止所做的：

创建数据透视表

  workBk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            workBk.Sheets(workSht.Name).Range("A1").CurrentRegion).CreatePivotTable _
            TableDestination:=workBk.Sheets(workShtPivot.Name).Cells(1, 1), _
            TableName:="PivotTableTest"

        With workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot)
            .Orientation = xlRowField
            .Position = 1
        End With

        workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").AddDataField workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields("Net " & whatToPivot & " Amount"), "Sum of things", xlSum

执行我想要的过滤器：只保留“事物总和”大于 0 的行 

workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot).PivotFilters.Add2 Type:=xlValueIsGreaterThan, _
                                                            DataField:=workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields("Sum of things"), Value1:=0

最后，当尝试遍历剩下的项目时（过滤后），即使它们已被过滤掉，它们似乎仍然可见（我正在遍历整个 PivotItems 集，即使是等于 0 的那些，其中我想避免）

Dim cellRange As Range
For Each pivotItm In workBk.Sheets(workShtPivot.Name).PivotTables("PivotTableTest").PivotFields(whatToPivot).PivotItems
    If pivotItm.Visible = True Then
        MsgBox (pivotItm.Value)
    End If
Next pivotItm

我想要的只是循环

ZZZZZZZZZ         2045

我真的不明白为什么这些过滤后的元素仍然被认为是可见的

谢谢你的帮助

标签： vbaexcelpivotpivot-table

解决方案

我能够复制您的问题：尽管应用了值过滤器，PivotItm.Visible但仍然是True. 该Visible属性似乎不适用于通过标签或值过滤器过滤的项目。

如果要获取可见的行标签及其对应的值，可以使用sDataRange的 PivotField例如，像这样复制可见行标签及其对应值的内容：

Sub FilterPTable()
    Dim pivotTbl As PivotTable
    Dim labelField As PivotField, sumField As PivotField
    Dim copyRng As Range

    Set pivotTbl = ThisWorkbook.Sheets("Sheet1").PivotTables("PivotTable2")
    Set labelField = pivotTbl.PivotFields("Labels")
    Set sumField = pivotTbl.PivotFields("Sum of Things")

    With labelField
        .ClearAllFilters
        .PivotFilters.Add2 Type:=xlValueIsGreaterThan, _
            DataField:=sumField, Value1:=0

        Set copyRng = Union(.DataRange, sumField.DataRange)
        copyRng.Copy ThisWorkbook.Sheets("Sheet2").Range("A1")
    End With
End Sub

或者，如果您的数据透视表没有显示总计，那么您可以使用DataBodyRange来引用数据透视表中的整个值范围：

Set copyRng = Union(.DataRange, pivotTbl.DataBodyRange)

Jon Peltier 的参考数据透视表范围是一个有用的指南。

注意如果过滤掉所有元素，这种方法将复制标题，例如“行标签”和“事物总和”。在这种情况下，您可以使用Intersect来测试是否copyRng重叠以避免复制。LabelRangePivotField

推荐阅读