使一张表上的所有数据透视表在行扩展和折叠方面相互模仿 [英] Making all pivot tables on one sheet mimic each other in terms of rows expanding and collapsing

问题描述

好的，我是VBA的新手，但我知道这是必须的.我花了一些时间编写android应用程序，但我不会称自己为专家，甚至说不上是中级专家.但是，可惜，Excel不使用Java.这是我的问题:

Alright, I'm new to VBA but I know this has to be possible. I spent a while coding android applications, but I wouldn't call myself nearly an expert, probably not even intermediate to be honest. However, alas, excel doesn't use java. Here's my problem:

我需要做的是使同一工作表上的其他6个数据透视表模仿我称为主数据透视表的内容.不过，它需要模仿的唯一功能(现在我想是)是在主要对象展开/折叠时，其他对象也应该照做.

All I need is to make 6 other pivot tables on the same sheet mimic what I will call the primary pivot table. The only feature it needs to mimic though (for now I suppose) is when the primary expands/collapses, the others should follow suit.

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

I'm guessing that the code will be similar to an onclicklistener in java and when the code "hears" a click for a collapse or expansion in the primary pivot table, it just applies that same collapse or expansion to the other six. The other 6 pivots will always have the same row labels, so an error in carrying over the "location" of the click from the primary to the others should never be a problem.

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

I tried recording the expansion of one of the row labels in my pivot table and got this code back.

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

不过，我知道这是执行该扩展的代码(而ShowDetail = False会使它崩溃).我认为我所需要的就像我说的那样，是一个侦听器，用于听到"主数据透视表的任何扩展/折叠的点击，这是一种存储/传递有关单击了哪个行标签的信息的方式(位置"点击次数)，然后使用我猜的for循环在其他6个代码上执行上述代码的通用版本.

I know though, that this is the coding for executing that expansion (and ShowDetail = False would make it collapse). I think what I'm needing is like I said, a listener to "hear" the click of any expansion/collapse of the primary pivot table, a way to store/carry over information on what row label was clicked (the "location" of the click if you will), and then a generic version of the above code to execute on the other 6 using I'm guessing a for loop of sorts.

我在正确的轨道上削减了任何帮助人员吗?一如既往的感谢.

Am I on the right track slash any help guys? Thanks a ton as always.

推荐答案

这应该对您有用:

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

Put this in a standard module(this is the less efficient method - because it checks all fields):

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代码中(如果需要帮助，请告诉我).

Then to automatically run this macro on any PivotTable edit you need to put this in your Sheet1 code(let me know if you need help doing that).

Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

    Call LinkPivotTables_ByFieldItemName_ToShowDetail(Target)

End Sub

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

It will work if all your pivot tables are on sheet one. You might have to copy/paste into wordpad or another text editor first because I didn't worry about line length limits(watch out for word wrap).

编辑/添加:

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

This is how you put code on a specific sheet object:

EDIT2/ADDITION2-效率方法:

此方法将大大提高效率，但您必须明确告诉您要同步哪个字段(它不会全部同步):

This method will dramatically increase the efficiency but you will have to tell it specifically which Field you want to be synced up(it won't sync them all):

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

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

You will have to manually tell it in this line what field you want synced up:

PivotFieldIndex = "Year"

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

I've found several other solutions online that use the same looping method to sync up pivot tables - the problem is that they all run into the same efficency problems when you get decent sized pivot tables. These somewhat get around that issue by including an IF statement that checks the Item.ShowDetail value before it sets it(because it takes alot longer to set the value than it does to just check it). Good Luck.

这篇关于使一张表上的所有数据透视表在行扩展和折叠方面相互模仿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！