excel - 使一张纸上的所有数据透视表在行扩展和折叠方面相互模仿

好吧，我是 VBA 新手，但我知道这必须是可能的。我花了一段时间编写 android 应用程序，但我不会称自己几乎是专家，老实说，甚至可能不是中级。但是，唉，excel 不使用 java。这是我的问题:

我所需要的只是在同一张纸上制作 6 个其他透视表，模仿我将称之为主透视表的内容。它需要模仿的唯一功能(现在我想)是当主要扩展/折叠时，其他功能应该效仿。

我猜测该代码将类似于 Java 中的 onclicklistener，当代码“听到”主数据透视表中折叠或展开的点击时，它只会将相同的折叠或展开应用于其他六个。其他 6 个枢轴将始终具有相同的行标签，因此将点击的“位置”从主要点转移到其他点时出现错误应该永远不会成为问题。

我尝试在数据透视表中记录行标签之一的扩展并取回此代码。

ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").PivotItems("2005"). _
    ShowDetail = True

我知道，这是执行该扩展的编码(并且 ShowDetail = False 会使其崩溃)。我认为我需要的就像我说的那样，一个听众可以“听到”主数据透视表的任何展开/折叠的点击，一种存储/携带有关点击的行标签(“位置”)的信息的方法如果您愿意，请单击单击)，然后使用上述代码的通用版本在其他 6 个代码上执行，我猜是某种 for 循环。

我在正确的轨道上削减任何帮助吗？一如既往地感谢。

最佳答案

这应该适合你:

把它放在一个标准模块中(这是效率较低的方法 - 因为它检查所有字段):

Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(1)

Dim PivotTableIndex As Integer
Dim PivotFieldIndex As Integer
Dim PivotItemIndex As Integer

Dim PivotFieldIndexName As String
Dim PivotItemIndexName As String

Dim BoolValue As Boolean

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

    For PivotFieldIndex = 1 To pt.PivotFields.Count

    PivotFieldIndexName = pt.PivotFields(PivotFieldIndex).Name

        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        PivotItemIndexName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name
        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndexName).PivotItems(PivotItemIndexName).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndexName).PivotItems(PivotItemIndexName).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

    Next PivotFieldIndex


Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

然后要在任何数据透视表编辑上自动运行这个宏，你需要把它放在你的 Sheet1 代码中(如果你需要帮助，请告诉我)。

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

End Sub

如果所有数据透视表都在第一张纸上，它将起作用。您可能必须先复制/粘贴到写字板或其他文本编辑器中，因为我不担心行长限制(注意自动换行)。

编辑/添加:

这是将代码放在特定工作表对象上的方式:

EDIT2/ADDITION2 - 效率方法:

此方法将显着提高效率，但您必须具体告诉它要同步哪个字段(它不会同步所有字段):

Sub LinkPivotTables_ByFieldItemName_ToShowDetail(pt As PivotTable)  'takes as argument - pt As PivotTable

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim wks As Worksheet
Set wks = wkb.Sheets(1)

Dim PivotTableIndex As Integer
Dim PivotItemIndex As Integer
Dim PivotFieldIndex As String
Dim BoolValue As Boolean
Dim ItemName As String

Application.ScreenUpdating = False
Application.EnableEvents = False

PivotFieldIndex = "Year"

On Error Resume Next


        For PivotItemsIndex = 1 To pt.PivotFields(PivotFieldIndex).PivotItems.Count

        BoolValue = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail
        ItemName = pt.PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).Name

            For PivotTableIndex = 1 To wks.PivotTables.Count

                ' This If statement will dramatically increase efficiency - because it takes a long long time to set the value but it doesn't take long to check it.
                If wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail <> BoolValue Then
                    wks.PivotTables(PivotTableIndex).PivotFields(PivotFieldIndex).PivotItems(PivotItemsIndex).ShowDetail = BoolValue
                End If

            Next PivotTableIndex

        Next PivotItemsIndex

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

您必须在此行中手动告诉它要同步的字段:

PivotFieldIndex = "Year"

我在网上找到了几个其他解决方案，它们使用相同的循环方法来同步数据透视表 - 问题是当您获得合适大小的数据透视表时，它们都会遇到相同的效率问题。这些通过包含一个 IF 语句来解决这个问题，该语句在设置它之前检查 Item.ShowDetail 值(因为设置值比检查它花费的时间要长得多)。祝你好运。

关于excel - 使一张纸上的所有数据透视表在行扩展和折叠方面相互模仿，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12352905/