如何检查数据透视表中是否选择了多个项目?

问题描述

在允许 MultipleItems 的情况下，是否可以知道在数据透视表过滤器中检查了哪些项目?

Is it possible to know which items are checked in a PivotTable filter in case MultipleItems are allowed?

我只看到 .Visible 属性来检查它，但它只有在不允许多个项目时才有效.如果允许多个项目并选中 .Visible 属性，您只会看到ALL"而不是所有选中的项目.

I only see .Visible property to check it but it only works if multiple items are not allowed.If multiple items are allowed and checked .Visible property, you only see "ALL" instead of all items selected.

有什么办法吗?

Dim pvt As PivotTable
Dim fld As PivotField
Dim itm As PivotItem
Dim flt As PivotFilter

Dim i As Integer


Set xbFuente = ThisWorkbook
Set xlDatos = xbFuente.Worksheets("TABLAS")
Set pvt = xlDatos.PivotTables("MAIN")

pvt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False

If pvt.ShowPageMultipleItemLabel = True Then
   Debug.Print "The words 'Multiple Items' can be displayed."
End If

For Each fld In pvt.PageFields
    Debug.Print fld.Name & " -- " & fld.Orientation & " -- " & fld.EnableItemSelection & " -- " & fld.EnableMultiplePageItems & " -- "


    If fld.AllItemsVisible = True Then
    ' If all items are visible  "ALL"
        For Each itm In fld.VisibleItems
            Debug.Print "---- ALLITEMSVISIBLE TRUE --" & "VISIBLE" & " -- " & itm.Name & " -- " & itm.Visible
        Next
    Else
        For Each itm In fld.VisibleItems
            Debug.Print "---- ALLITEMSVISIBLE FALSE --" & "VISIBLE" & itm.Name & " -- " & itm.Visible
        Next
        For Each itm In fld.HiddenItems
            Debug.Print "--------ALLITEMSVISIBLE FALSE --" & "HIDDEN -- " & itm.Name & " -- " & itm.Visible
        Next
        For Each itm In fld.PivotItems
            Debug.Print "--------ALLITEMSVISIBLE FALSE --" & "HIDDEN -- " & itm.Name & " -- " & itm.Value
        Next
    End If

    Next

结果:保修标志 -- 3 -- Verdadero -- Verdadero ------ ALLITEMSVISIBLE FALSE --VISIBLE(All) -- Verdadero--------ALLITEMSVISIBLE FALSE --HIDDEN -- A -- Falso--------ALLITEMSVISIBLE FALSE --HIDDEN -- I -- Falso--------ALLITEMSVISIBLE FALSE --HIDDEN -- O -- Falso--------ALLITEMSVISIBLE FALSE --HIDDEN -- P -- Falso

推荐答案

试试下面的示例代码:

Sub Check_PivotFilter_Selection()

Dim pvt As PivotTable
Dim fld As PivotField
Dim itm As PivotItem
Dim flt As PivotFilter

Dim i As Integer

Set xbFuente = ThisWorkbook
Set xlDatos = xbFuente.Worksheets("TABLAS")
Set pvt = xlDatos.PivotTables("MAIN")

pvt.ManualUpdate = True
Application.EnableEvents = False
Application.ScreenUpdating = False

If pvt.ShowPageMultipleItemLabel = True Then
   Debug.Print "The words 'Multiple Items' can be displayed."
End If


For Each fld In pvt.PageFields
    Debug.Print fld.Name & " -- " & fld.Orientation & " -- " & fld.EnableItemSelection & " -- " & fld.EnableMultiplePageItems & " -- "

    ' loop through all items in Field, and check which ones are Selected (and which ones are not)
    For Each itm In fld.PivotItems

        If itm.Visible = True Then
            Debug.Print " Item " & itm.Name & " in Field " & fld.Name & " is Visible (Selected) "
        Else
            Debug.Print " Item " & itm.Name & " in Field " & fld.Name & " is Hidden (Not Selected) "
        End If

    Next itm
Next fld

End Sub

这篇关于如何检查数据透视表中是否选择了多个项目?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！