枚举分组数据透视表VBA [英] Enumerate subgroups pivottable vba

问题描述

我的数据透视表结构看起来很像(即，数据透视表UI的"ROWS"框中有三个条目)

I have a pivot table structure that looks like (i.e. there are three entries in the "ROWS" box in the pivottable UI)

• 类别
  • 子类别
    • 子类别
    • Category
      • SubCategory
        • Sub Sub Category

        我知道我可以通过分别在VBA中执行PT.PivotFields(3).PivotItems()，PT.PivotFields(2).PivotItems()和PT.PivotFields(1).PivotItems()来获得所有类别，子类别和子子类别，其中PT是我的数据透视表.

        I know that I can get all the Categories, subcategories, and sub-sub categories by doing (in VBA) PT.PivotFields(3).PivotItems(), PT.PivotFields(2).PivotItems() and PT.PivotFields(1).PivotItems() respectively, where PT is my pivottable.

        如何找出每个类别中的哪些子类别，以及类别中子类别的子类别?

        How can I find out which subcategories are in each category, and same for sub sub categories in categories?

        我尝试使用PT.PivotFields(3).PivotItems()(1).ChildItems()，但遇到错误<Unable to get the ChildItems property of the PivotItem class>，尝试ParentItem时也是如此.

        I tried using PT.PivotFields(3).PivotItems()(1).ChildItems() but I get an error <Unable to get the ChildItems property of the PivotItem class> and same for trying ParentItem.

        知道我该怎么做吗?

        我要寻找的示例.拿下面的数据透视表，并枚举(以某种方式):

        An example of what I am looking for. Take the pivot table below, and enumerate (in some way) that:

        a具有子类别d，e； b具有子类别e，f； c具有子类别d，e，f；如果列位置上有多个级别，则将是相同的.

        a has subcategories d,e; b has subcategories e,f; c has sub categories d,e,f; and it would be the same if there were multiple levels on the columns position.

        推荐答案

        • 要求: 要构建一个表，以显示给定PivotTable的所有RowFields和ColumnsFields的所有Items组合.
          
          

          • Requirement: To build a table showing all the Items combinations for all RowFields and ColumnsFields of a given PivotTable.
            
            

            解决方案: 这可以通过如下设置数据透视表以及行，列和数据字段的某些属性和方法来实现:

            Solution: This can be achieved by setting some of the properties and methods of the PivotTable and the Row, Column and Data Fields as follows:

            1. 设置这些数据透视表属性:
               RowGrand，ColumnGrand，MergeLabels，RowAxisLayout

            1. Set these PivotTable properties:
               RowGrand, ColumnGrand, MergeLabels, RowAxisLayout

            设置ColumnFields的这些属性:
            方向

            Set these properties for the ColumnFields:
            Orientation

            为RowFields设置以下属性:
            重复标签，小计

            Set these properties for the RowFields:
            RepeatLabels, Subtotals

            设置数据字段的这些属性:
            方向
            
            

            Set these properties for the DataFields:
            Orientation
            
            

          • 过程:

            Sub PivotTable_Hierarchy_Rows_And_Columns(pt As PivotTable, _   
                aPtHierarchy As Variant, blClearFilters As Boolean, blIncludeCols As Boolean)
            

            此过程将调整所有上述属性，以便以类似于表" 格式显示数据透视表，并使用数据透视表的层次结构生成数组.它还提供了用于清除或不清除数据透视表筛选器以及是否在层次结构中包括ColumnFields的选项.

            This procedure adjusts all the above-mentioned properties in order to display the PivotTable in a "table like" format generating an array with the PivotTable’s Hierarchy. It also provides the options to clear or not the PivotTable filters and to include or not the ColumnFields in the hierarchy.

            参数:
            Pt :目标数据透视表
            aPtHierarchy :包含目标数据透视表层次结构的数组输出.
            blClearFilters :布尔值.确定是否清除所有数据透视表筛选器. blIncludeCols :布尔值.用于在输出层次结构中包含或不包含ColumnFields.

            Parameters:
            Pt: Target PivotTable
            aPtHierarchy: Array output containing the hierarchy of the target PivotTable.
            blClearFilters: Boolean. Determines whether to clear or not the all PivotTable filters. blIncludeCols: Boolean. Used to include or not the ColumnFields in the output hierarchy.

            语法:
            Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, blClearFilters, blIncludeCols)
            
            

            Syntax:
            Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, blClearFilters, blIncludeCols)
            
            

            VBA:

            Sub PivotTable_Hierarchy_Rows_And_Columns(pt As PivotTable, _
                aPtHierarchy As Variant, blClearFilters As Boolean, blIncludeCols As Boolean)
            Dim pf As PivotField
            
                Rem PivotTable Properties & Methods
                With pt
                    .RowGrand = False
                    .ColumnGrand = False
                    .MergeLabels = False
                    .RowAxisLayout xlTabularRow
                    If blClearFilters Then .ClearAllFilters
                End With
            
                Rem ColumnFields Properties
                For Each pf In pt.ColumnFields
                    If blIncludeCols Then
                        pf.Orientation = xlRowField
                    Else
                        pf.Orientation = xlHidden
                End If: Next
            
                Rem RowFields Properties
                For Each pf In pt.RowFields
                    With pf
                        On Error Resume Next
                        .RepeatLabels = True
                        .Subtotals = Array(False, False, False, False, _
                            False, False, False, False, False, False, False, False)
                        On Error GoTo 0
                End With: Next
            
                Rem DataFields Properties
                For Each pf In pt.DataFields
                    pf.Orientation = xlHidden
                Next
            
                Rem Set Hierarchy Array
                aPtHierarchy = pt.RowRange.Value2
            
                End Sub
            

            
            

            示例:

            假设我们需要获取图2中数据透视表的层次结构. 1. 请注意，数据透视表已应用了一些过滤器.

            Assuming we need to obtain the Hierarchy of the PivotTable in fig. 1. Note that the PivotTable has some filters applied.

            根据所需结果，可以按以下方式调用过程PivotTable_Hierarchy_Rows_And_Columns:

            The procedure PivotTable_Hierarchy_Rows_And_Columns can be called as follows depending on the required outcome:

            Sub PivotTable_Hierarchy_Rows_And_Columns_TEST()
            Dim pt As PivotTable, aPtHierarchy As Variant
            
                'Set PivotTable - Change worksheet and pivottable name as required
                Set pt = ThisWorkbook.Worksheets("Summary").PivotTables("PtTst")      
            
                '1. To obtain the Hierarchy for Rows and Columns, clearing all the filters applied to the PivotTable try this:  
                Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, True, True)    'See results in Fig. R1 (Table & Array)  
            
                ‘2. To obtain the Hierarchy for Rows only, clearing all the filters applied to the PivotTable try this:  
                Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, True, False)    'See results in Fig. R2   (Table & Array)  
            
                '3. To obtain the Hierarchy for Rows and Columns with the filters currently applied to the PivotTable try this:  
                Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, False, True)    'See results in Fig. R3   (Table & Array)  
            
                '4. To obtain the Hierarchy for Rows only with the filters currently applied to the PivotTable try this:  
                Call PivotTable_Hierarchy_Rows_And_Columns(pt, aPtHierarchy, False, False)    'See results in Fig. R4   (Table & Array)  
            
                End Sub
            

            
            图1
            
            
            图R1
            
            
            图R2
            
            
            图R3
            
            
            图R4
            
            

            
            Fig. 1
            
            
            Fig. R1
            
            
            Fig. R2
            
            
            Fig. R3
            
            
            Fig. R4
            
            

            有关所用资源的其他信息，请参见以下页面:

            For additional information on the resources used see the following pages:

            数据透视表对象(Excel)
            PivotTable.RowAxisLayout方法(Excel)
            PivotField对象(Excel)

            PivotTable Object (Excel)
            PivotTable.RowAxisLayout Method (Excel)
            PivotField Object (Excel)

            这篇关于枚举分组数据透视表VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！