Excel VBA 循环通过过滤的数据透视项

Question

我是论坛的新手，所以如果我的帖子不完整，请原谅我。

我有一个非常简单的数据透视表，其中包含一个行字段、一个列字段、一个数据字段和一个过滤器字段。行字段根据过滤器设置显示。使用 VBA，我的意图是遍历所有行数据透视表和列数据透视表，并获取相应的字段名称和数据值并将它们显示为完整性检查。这是一个更大项目的开始。

这是主循环(显示字符串显示在屏幕上):

showstring = ""
For rowFldNo = 1 To pvt.RowFields.Count
  For colFldNo = 1 To pvt.ColumnFields.Count
    For rowItemNo = 1 To pvt.RowFields(rowFldNo).PivotItems.Count
      For colItemNo = 1 To pvt.ColumnFields(colFldNo).PivotItems.Count
        If pvt.RowFields(rowFldNo).PivotItems(rowItemNo).Visible And _
           pvt.ColumnFields(colFldNo).PivotItems(colItemNo).Visible Then
           showstring = showstring & _
           pvt.RowFields(rowFldNo).PivotItems(rowItemNo).Name & ": " & _ 
           pvt.ColumnFields(colFldNo).PivotItems(colItemNo).Name & _
           "= " & MyGetPivotData(pvt, rowFldNo, rowItemNo, colFldNo, _ 
           colItemNo) & vbCrLf
        End If
      Next colItemNo
    Next rowItemNo
  Next colFldNo
Next rowFldNo
MsgBox showstring

MyGetPivotData 是一个简单的子程序，它隐藏了使用 VBA native GetPivotData 函数的复杂性。这是代码:

Function MyGetPivotData(ByRef thisPvt As PivotTable, _
                        ByVal rowFld As Integer, _
                        ByVal rowItem As Integer, _
                        ByVal colFld As Integer, _
                        ByVal colItem As Integer) As Integer

On Error Resume Next
MyGetPivotData = 
  thisPvt.GetPivotData(thisPvt.DataFields(thisPvt.DataFields.Count), _
                thisPvt.RowFields(rowFld).Name, _ 
                thisPvt.RowFields(rowFld).PivotItems(rowItem).Name, _
                thisPvt.ColumnFields(colFld).Name, _ 
                thisPvt.ColumnFields(colFld).PivotItems(colItem).Name).Value

End Function

代码运行良好，但没有实现我的意图。我的问题出在第一个代码段中。在调用 MyGetPivotData 之前，我正在使用 Visible 属性。问题是 Visible 不会随着过滤器设置而改变 - 我通过在数据透视表字段中手动检查来验证它。过滤器设置会影响屏幕上可见的内容，但 pivotitem Visible 属性不会更改并且始终为 True。因此，我遍历所有可用字段，GetPivotData 为不可见字段返回值 0。这对我来说是 Not Acceptable ，因为我的数据透视数据包含实际的 0 值。

我的问题是是否有其他属性可以用来确定是否过滤掉了数据透视项(不可见，因此不相关)或不(可见且相关)。我在 VBA 中尝试了对象浏览器，但没有更明智。任何提示将不胜感激。

谢谢

Answer 1

正如您已经了解了 PivotItems 的可见性:

如果您过滤 PivotTable ，然后是一些 PivotItems您的RowFields或 ColumnFields是否可见或不可见，
但 VBA 仍然返回每个 PivotField.PivotItem如Visible .

还有PivotField.VisibleItems.Count始终保持最大值。

剩下的“真的”可见PivotItems可以通过 PivotLine.PivotLineCell.PivotItem 解决每个枢轴。
LineType区分常规行、空白行、小计行和总计行。

showstring = ""
For rowItemNo = 1 To pvt.PivotRowAxis.PivotLines.Count
    If pvt.PivotRowAxis.PivotLines(rowItemNo).LineType = xlPivotLineRegular Then
        For colItemNo = 1 To pvt.PivotColumnAxis.PivotLines.Count
            If pvt.PivotColumnAxis.PivotLines(colItemNo).LineType = xlPivotLineRegular Then
                showstring = showstring & _
                pvt.PivotRowAxis.PivotLines(rowItemNo).PivotLineCells(1).PivotItem.Name & ":" & _
                pvt.PivotColumnAxis.PivotLines(colItemNo).PivotLineCells(1).PivotItem.Name & _
                " = " & pvt.DataBodyRange.Cells(rowItemNo, colItemNo).Value & vbCrLf
            End If
        Next colItemNo
    End If
Next rowItemNo
MsgBox showstring

如果您有多个列或行字段，则 PivotLineCells()可以区分它们。

... 或者你遍历 DataRange每个 PivotField捕捉简单数据透视表可见单元格中的项目:

For rowFldNo = 1 To pvt.RowFields.Count
    For colFldNo = 1 To pvt.ColumnFields.Count
        For rowItemNo = 1 To pvt.RowFields(rowFldNo).DataRange.Rows.Count
            For colItemNo = 1 To pvt.ColumnFields(colFldNo).DataRange.Columns.Count
                showstring = showstring & ...
            Next colItemNo
        Next rowItemNo
    Next colFldNo
Next rowFldNo