如何遍历数据透视表中的每个活动(未过滤)元素(不包括列标签和总计)? [英] How to loop through each active (non filtered) element in a Pivot Table (excluding the column labels and grand totals)?

问题描述

我希望这些字段为"Reg Hrs.之和"，"Reg OT Hrs.的总和"，...，"SNWH NSD Hrs.的总和".

I was expecting the fields to be "Sum of Reg Hrs.", "Sum of Reg OT Hrs.", ... , "Sum of SNWH NSD Hrs."

但是我得到的是MsgBox pf显示值"

But what I got was MsgBox pf shows "Values"

Msgbox pi显示"Reg Hrs.之和"，"Reg OT Hrs.之和"，...，"SNWH NSD Hrs的总和".

Msgbox pi shows "Sum of Reg Hrs.", "Sum of Reg OT Hrs.", ... , "Sum of SNWH NSD Hrs."

但是理想情况下，我希望pi作为每列的项目.还有另一种方法吗?

But ideally, I want pi to be the items of each column. Is there another method to do so?

Sub test()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

Set pt = ThisWorkbook.Worksheets("N-MR Hours Summary").PivotTables("N-MR Hours Summary")

For Each pf In pt.ColumnFields
    MsgBox pf
    For Each pi In pf.PivotItems
        MsgBox pi
    Next pi
Next pf

End Sub()

此外，它应该仅包括活动单元(未过滤的单元)，但我意识到即使过滤的单元也要进行迭代.

Also, it should only include, the active cells (non filtered cells), but I realized even the filtered cells are iterated about.

推荐答案

数据透视表具有PivotRowAxis(垂直)和PivotColumnAxis(水平).
它们每个都包含一个或多个PivotLines.
以下屏幕截图用PivotLineCells解释了轴及其PivotLines.

A pivot table has a PivotRowAxis (vertical) and a PivotColumnAxis (horizontal).
They each consist of one or more PivotLines.
Following screenshot explains both axis and their PivotLines with PivotLineCells.

在您的示例中，每个PivotColumnAxis.PivotLine仅具有1个PivotLineCell.
PivotLine.PivotLineType可用于标识具有常规值，总计，总计或完全空白行的行或列:

In your example, each PivotColumnAxis.PivotLine only has 1 PivotLineCell.
The PivotLine.PivotLineType can be used to identify rows or columns with regular values, sub totals, grand total or completely blank lines:

由于您的列仅由DataFields组成(没有专用的ColumnFields)，因此可以遍历PivotColumnAxis.PivotLine(i).PivotLineCells(1).PivotField以获取各列的垂直DataRange:

As your columns only consist of DataFields (no dedicated ColumnFields), you can loop over PivotColumnAxis.PivotLine(i).PivotLineCells(1).PivotField to get the vertical DataRange of each column:

这是它的代码:

Private Sub ColumnByColumn()
    Dim pt As PivotTable
    Dim pl As PivotLine
    Dim pf As PivotField
    Dim c As Range

    Set pt = ActiveSheet.PivotTables(1)
    For Each pl In pt.PivotColumnAxis.PivotLines
        If pl.LineType = xlPivotLineRegular Then
            Set pf = pl.PivotLineCells(1).PivotField
            If pf.Orientation = xlDataField Then
                Debug.Print pf.Caption,
                For Each c In pf.DataRange.Cells
                    Debug.Print c.Value,
                Next c
                Debug.Print
            End If
        End If
    Next pl
End Sub

---

更多提示:

---

Further hints:

您的数据透视表仅包含DataFields(没有明确的ColumnFields)，因此，数据透视表的名称将作为值"返回.

Your pivottable only contains DataFields (no explicit ColumnFields), therefore the pivotfield's name is returned as "Values".

不幸的是，遍历所有 visible 枢轴项绝非易事，因为遍历所有 visible 枢轴项也将返回不可见(已过滤)的项.甚至PivotField.VisibleItems.Count总是返回与PivotField.PivotItems.Count相同的值.

Unfortunately it's never simple to loop over all visible pivotitems, as a simple loop over all of them also returns the invisible (filtered) items. Even PivotField.VisibleItems.Count always returns the same value as PivotField.PivotItems.Count.

PivotLine.LineType始终为xlPivotLineRegular(如果您有空白列，小计列或总计列，也可以是xlPivotLineBlank，xlPivotLineSubtotal或xlPivotLineGrandTotal).

PivotLine.LineType is in your case always xlPivotLineRegular (It could also be xlPivotLineBlank, xlPivotLineSubtotal or xlPivotLineGrandTotal if you would have blank columns, subtotal columns or grand total columns).

这篇关于如何遍历数据透视表中的每个活动(未过滤)元素(不包括列标签和总计)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！