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

Question

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

我所需要的只是在同一张纸上制作其他 6 个数据透视表，模仿我所说的主数据透视表。它需要模仿的唯一功能(目前我认为)是当主扩展/折叠时，其他功能应该效仿。

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

我尝试记录数据透视表中行标签之一的扩展，并得到了此代码。

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

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

我走在正确的道路上吗？有什么帮助吗？一如既往地非常感谢。

Answer 1

这应该适合你:

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

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

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

编辑/添加:

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

编辑2/添加2 - 效率方法:

这种方法会大大提高效率，但你必须具体告诉它你想要同步哪个字段(它不会全部同步):

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 值之前检查该值(因为设置该值比仅检查该值花费的时间要长得多)。祝你好运。