vba Excel 2007 跨多个数据透视表过滤

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1423753/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:47:49  来源:igfitidea点击:

Excel 2007 Filtering across multiple Pivot Tables

excelpivot-tableexcel-vbavba

提问by John Sullivan

In Excel 2007, I have a single dataset in the form of an Excel Table from which I want to make multiple pivot tables and pivot charts. I would like to be able to synchronize the 'report filter' field across all of the pivot tables and charts simultaneously, so that by filtering, (for example) the US_Region field to 'Pacific Northwest' on one of the pivot tables would apply that filter to all of the pivot tables and charts in the workbook (which share a single dataset). Is there a way to do this without VBA and, if there is not, what would be the most flexible/least klugy way to do it in VBA?

在 Excel 2007 中,我有一个 Excel 表格形式的单个数据集,我想从中制作多个数据透视表和数据透视图。我希望能够同时在所有数据透视表和图表中同步“报告过滤器”字段,以便通过过滤(例如)在其中一个数据透视表上将 US_Region 字段应用到“太平洋西北地区”筛选到工作簿中的所有数据透视表和图表(共享单个数据集)。有没有办法在没有 VBA 的情况下做到这一点,如果没有,在 VBA 中最灵活/最不笨拙的方法是什么?

Thanks

谢谢

回答by dendarii

Create a procedure to change all the page fields. This one loops through all the worksheets in the workbook and if the worksheet has a pivot table, it changes the page field to match the page field on the pivot table passed to the sub.

创建一个过程来更改所有页面字段。这个循环遍历工作簿中的所有工作表,如果工作表有一个数据透视表,它会更改页面字段以匹配传递给子的数据透视表上的页面字段。

Sub ChangePage(pt As PivotTable)

    Dim strPageValue As String
    Dim wks As Worksheet
    Dim ptUpdate As PivotTable

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    strPageValue = pt.PivotFields("Area").CurrentPage

    For Each wks In ThisWorkbook.Worksheets

        On Error Resume Next
        Set ptUpdate = wks.PivotTables(1)

        If Err.Number = 0 Then
            On Error GoTo 0
            wks.PivotTables(1).PivotFields("Area").ClearAllFilters
            wks.PivotTables(1).PivotFields("Area").CurrentPage = strPageValue
        End If
        Err.Clear

    Next wks

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Then place a call to this procedure in the worksheet code itself and pass the pivot table you changed:

然后在工作表代码本身中调用此过程并传递您更改的数据透视表:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Call ChangePage(Target)

End Sub

Change the name of the pivot field from "Area" to the page field you need, for example "US_Region".

将数据透视字段的名称从“Area”更改为您需要的页面字段,例如“US_Region”。

This may not be suitable if people are frequently changing the structure of your pivot tables in other ways, as it will trigger each time the pivot table is changed, including when it is refreshed. It will also give an error if users remove the specified page field.

如果人们经常以其他方式更改数据透视表的结构,这可能不合适,因为每次更改数据透视表时都会触发,包括刷新时。如果用户删除指定的页面字段,它也会出错。

If the pivot tables are static though, this might do.

如果数据透视表是静态的,这可能会。