Excel VBA循环筛选后的数据透视表项

Question

Excel VBA循环筛选后的数据透视表项

3

我是一名新手，如果我的帖子不完整，请见谅。

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

以下是主要循环（showstring显示在屏幕上）：

"最初的回答"

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本地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。这对我来说是不可接受的，因为我的数据透视表可能包含实际的0值。

我的问题是，是否有其他属性可以用来确定pivotitem是否被过滤掉（不可见和因此不相关）还是没有被过滤掉（可见和相关）。我尝试了VBA中的Object Browser，但没有更聪明。任何提示都将不胜感激。

谢谢

- PeterS

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Asger · Accepted Answer

正如您已经发现的那样，PivotItems的可见性：

如果您筛选了一个PivotTable，则某些RowFields或ColumnFields的PivotItems在视觉上是可见的或不可见的，
但是VBA仍然将每个PivotField.PivotItem返回为可见。

此外，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()可以区分它们。

...或者您可以循环遍历每个PivotField的DataRange，以捕获简单数据透视表中可见单元格中的项目：

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