在Excel中更新多个数据透视表的数据源

Question

在单个Excel工作表上同时更新多个数据透视表的数据源有没有简单的方法？

所有的数据透视表都引用相同的命名区域，但我需要创建第二个工作表，其具有相同的数据透视表，但访问不同的命名区域。

理想情况下，我希望能够进行某种搜索和替换操作（就像您可以对公式执行的操作一样），而不是手动更新每个数据透视表。

有什么建议吗？

- phrenetic

Answer 1

以下 VBA 代码将更改单个工作表上所有数据透视表的数据源。

您需要将 Sheet2 参数更新为具有新数据透视表的工作表名称，并将 Data2 参数更新为您的新命名区域。

Sub Change_Pivot_Source()

    Dim pt As PivotTable

    For Each pt In ActiveWorkbook.Worksheets("Sheet2").PivotTables
             pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
                (SourceType:=xlDatabase, SourceData:="Data2")
    Next pt

End Sub

- Robert Mearns

---

工作得很好，谢谢。 - Delta_zulu

Answer 2

假设您愿意使用VBA，this可能相关。

如果您在每个工作表上迭代PivotTable集合，您应该能够使用该帖子中显示的方法来修改数据源。语法应该非常类似于使用命名范围而不是单元格范围。

- Ed Harper

Answer 3

我将上述代码结合起来，现在你可以使用以下代码，无论是表格还是范围引用作为您的源数据。您只需要通过替换*符号来输入您的源数据，然后就可以开始了。

Sub Change_Pivot_Source()

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

For Each pt In ws.PivotTables
         pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create _
            (SourceType:=xlDatabase, SourceData:="*****")
Next pt
Next ws

End Sub

- ram singh

Answer 4

改编自使用此VBA宏代码动态更改数据透视表的数据源范围:

1. 可以设置 PivotTable.SourceData 属性，该属性通过 ChangePivotCache 方法进行设置。
2. 要创建一个新的 PivotCache，请调用 ActiveWorkbook.PivotCaches.Create
3. 您需要传递一个 SourceType 和一个 Range 作为 SourceData。
4. 最后，在更新后，请确保调用 RefreshTable 应用更改。

这是一个例子，它会自动查找工作簿中的每个数据透视表并更新它。

Sub AdjustPivotDataRange()
    Dim pt As PivotTable, pc As PivotCache
    Dim dataSheet As Worksheet, ws As Worksheet
    Dim startPoint As Range, dataSource As Range, newRange As String

    ' get worksheet with data
    Set dataSheet = ThisWorkbook.Worksheets("Sheet1")

    ' Dynamically Retrieve Range Address of Data
    Set startPoint = dataSheet.Range("A1")
    Set dataSource = dataSheet.Range(startPoint, startPoint.SpecialCells(xlLastCell))
    newRange = dataSheet.Name & "!" & dataSource.Address(ReferenceStyle:=xlR1C1)

    ' create new PivotCache
    Set pc = ActiveWorkbook.PivotCaches.Create( _
               SourceType:=xlDatabase, _
               SourceData:=newRange)

    ' loop through all tables in all sheets
    For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables

            ' update pivot source and refresh
            pt.ChangePivotCache pc
            pt.RefreshTable

        Next pt
    Next ws

End Sub

只需将"Sheet1"替换为您的数据源所在的位置。

- KyleMit

---

@kilemit：我刚刚让代码保持一致。要么使用两次activeworkbook，要么使用两次thisworkbook，但是建议混合使用肯定会带来麻烦。只有当activeworkbook是thisworkbook时，它才能正常工作。 - iDevlop

Answer 5

只需将原始数据转换成表格，一切问题都将得到解决。

- Vishal

---

您的回答可以通过添加额外的支持信息来改进。请[编辑]以添加更多细节，例如引用或文档，以便其他人可以确认您的答案是否正确。您可以在帮助中心找到有关如何编写良好答案的更多信息。 - Community