如何从VBA代码或公式中访问Excel数据透视表中的选定报表过滤器值？

I have a pivot table with report filter field. I need to do some operations based on the value user selects in report filter field.

我有一个带有报告过滤器字段的数据透视表。我需要根据用户在报表过滤器字段中选择的值进行一些操作。

If user selects single value (or selects All) - no problem to get that value. In sample video it's cell B1, but in case of multiple values are selected, this field shows just Multiple selected.

如果用户选择单个值（或选择全部） - 获取该值没有问题。在示例视频中，它是单元格B1，但是如果选择了多个值，则此字段仅显示多个选定的。

Where can I get the specific reporting filter values that are selected? Can I access those values from Excel formulas? If not - can I access those values from VBA code?

在哪里可以获得所选的特定报告过滤器值？我可以从Excel公式中访问这些值吗？如果不是 - 我可以从VBA代码访问这些值吗？

Sample video

视频示例

1 个解决方案

#1

Since there was no activity in *, I posted question to few other forums. The only place where I got response (that lead to solution) was msdn forum. In case if someone else that searches for this in * - I post the essence of the solution here, additional info available at the provided links.

由于*中没有活动，我向其他几个论坛发布了问题。我得到回应（导致解决方案）的唯一地方是msdn论坛。万一其他人在*中搜索这个 - 我在这里发布了解决方案的精髓，在提供的链接上提供了更多信息。

I did not get info how to access report filter's values directly from formulas - but that could be done with creating user defined function -> see how to do that

我没有得到如何直接从公式访问报表过滤器值的信息 - 但这可以通过创建用户定义的函数来完成 - >看看如何做到这一点

1) This is how to access report filter field's from VBA code (by P.Thornton):

1）这是如何从VBA代码访问报告过滤器字段（由P.Thornton）：

Sub Button960_Click()
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
        Set pt = ActiveSheet.PivotTables(1)

        pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
        pt.PivotCache.Refresh

        Set pf = pt.PivotFields("your report field name")
        For Each pi In pf.PivotItems
            Debug.Print pi.Name, pi.Visible

            If pi.Visible Then
                '... user has selected this value!
            End If
        Next   
End Sub

Please note, that You should set PivotCache.MissingItemsLimit = xlMissingNone otherwise You may experience unexpected results. In my case it turned out that cache had a bunch of values that were NOT present at the data source anymore. This was a legacy file and those non-existing values most likely were in the file at some moment in past, and later erased from the file. So, clearing PivotCache clears some old, non-existing data.

请注意，您应该设置PivotCache.MissingItemsLimit = xlMissingNone否则您可能会遇到意外的结果。在我的例子中，事实证明缓存有一堆不再存在于数据源的值。这是一个遗留文件，那些不存在的值很可能在过去的某个时刻出现在文件中，后来从文件中删除。因此，清除PivotCache会清除一些旧的，不存在的数据。

2) A nice solution how to display list of selected values by the side of the report filter fields available at the msdn forum(by D.Tamburino)

2）一个很好的解决方案如何在msdn论坛上提供的报告过滤器字段旁边显示所选值的列表（由D.Tamburino提供）

P.S. Thanks to P.Thornton and D.Tamburino from MSDN forum!

附：感谢来自MSDN论坛的P.Thornton和D.Tamburino！

#1