Excel VBA循环筛选的枢轴项目 [英] Excel VBA loop through pivotitems that are filtered

问题描述

我是这个论坛的新手，所以如果我的帖子不完整，请纵容我.

I am new to the forum, so please indulge me if my post is incomplete.

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

I have a very simple pivot table with one row field, one column field, one data field and one filter field. Row fields are displayed based on the filter setting. Using VBA, my intent is to loop through all row pivotitems and column pivotitems and get the corresponding field names and data values and display them as a sanity check. This is a start of a bigger project.

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

here is the main loop (showstring is displayed on the screen):

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函数的复杂性.这是代码:

MyGetPivotData is a simple sub that hides the complexity of using the VBA native GetPivotData function. Here is the code:

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不会随着过滤器设置而改变-我通过在数据透视表字段中手动检查来验证了它.筛选器设置会影响屏幕上可见的内容，但枢轴项目的可见性"属性不会更改，并且始终为True.因此，我遍历了所有可用字段，并且对于不可见字段，GetPivotData返回值0.如果我的数据透视图数据包含实际的0值，这对我来说是不可接受的.

The code runs fine but does not accomplish my intention. My problem is in the first code segment. I am using the Visible property before I call MyGetPivotData. The problem is that Visible does not change with filter setting - I verified it by checking manually in pivot table fields. Filter setting impacts what is visible on screen, but the pivotitem Visible property does not change and is always True. Consequently, I am looping through all available fields and GetPivotData return value 0 for non-visible fields. This is not acceptable to me becase my pivot data my contain actual 0 values.

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

My question is if there are other properties I could use to determine if pivotitem is filtered out (invisible and hence irrelevant) or not (visible and relevant). I tried the Object Browser in VBA but did not get any wiser. Any tips will be greatly appreciated.

谢谢

推荐答案

您已经发现有关PivotItems可见性的信息:

As you already found out about the visibility of PivotItems:

如果您过滤PivotTable，则RowFields或ColumnFields中的某些PivotItems在光学上是可见的，
，但VBA仍将每个 PivotField.PivotItem作为Visible返回.

If you filter a PivotTable, then some of the PivotItems of your RowFields or ColumnFields are optically visible or not,
but VBA still returns each PivotField.PivotItem as Visible.

PivotField.VisibleItems.Count始终保持最大值.

每个枢轴的PivotLine.PivotLineCell.PivotItem可以解决剩余的真正"可见的PivotItems.

The remaining "really" visible PivotItems can be addressed by PivotLine.PivotLineCell.PivotItem of each pivot-axis.

LineType在规则行，空白行，小计行和宏计行之间进行区分.

LineType distingushes between regular, blank, subtotal and grandtotal lines.

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()可以区分它们.

If you have more than one column or row field, then PivotLineCells() can distinguish them.

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

... or you loop over the DataRange of each PivotField to catch the items in visible cells of a simple pivot table:

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

这篇关于Excel VBA循环筛选的枢轴项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！