如何动态地更新所有的数据透视表的数据源在Excel

发表于： 2020年10月22日 2020年11月12日
标签： excel-pivot-tables

|在上一篇文章中，我们了解了如何通过收缩或扩展数据源动态更改和更新各个数据透视表。

在本文中，我们将学习如何使工作簿中的所有数据透视表自动更改数据源。换句话说，我们将尝试更改工作簿中所有数据透视表的数据源，而不是一次更改一个数据透视表，以动态地包括添加到源表中的新行和新列，并立即反映出数据透视表中的更改。

在源数据表中编写代码由于我们希望这是完全自动化的，因此我们将使用工作表模块而不是核心模块来编写代码。这将允许我们使用`link：/ events-in-vba-the-worksheet-events-in-excel-vba [worksheet events]`。

如果源数据和数据透视表位于不同的工作表中，我们将编写VBA代码以更改包含源数据（不包含数据透视表）的工作表对象中的数据透视表数据源。按CTRL + F11打开VB编辑器。现在转到项目资源管理器，找到包含源数据的工作表。双击它。

一个新的编码区将打开。您可能看不到任何更改，但是现在您可以访问工作表事件。

单击左侧的下拉菜单，然后选择工作表。在左侧的下拉菜单中，选择停用。您会看到在代码区域名称worksheet_deativate上写了一个空白子。我们的代码用于动态更改源数据和刷新数据透视表。每当您从数据表切换到任何其他表时，该代码就会运行。您可以阅读所有工作表事件`link：/ events-in-vba-the-worksheet-events-in-excel-vba [here]`。

现在我们准备实现代码。

用于使用新范围动态更新工作簿中所有数据透视表的源代码为了解释其工作原理，我有一个工作簿。该工作簿包含三页。 Sheet1包含可以更改的源数据。 Sheet2和Sheet3包含依赖于sheet2的源数据的数据透视表。

现在，我已经在sheet1的编码区域中编写了此代码。我正在使用Worksheet_Deactivate事件，以便每当我们从源数据表切换时，此代码就会运行以更新数据透视表。

Private Sub Worksheet_Deactivate()

Dim source_data As Range

'Determining last row and column number

lstrow = Cells(Rows.Count, 1).End(xlUp).Row

lstcol = Cells(1, Columns.Count).End(xlToLeft).Column

'Setting the new range

Set source_data = Range(Cells(1, 1), Cells(lstrow, lstcol))

'Code to loop through each sheet and pivot table

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables



pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:=source_data)



Next pt

Next ws

End Sub

如果您有类似的工作簿，则可以直接复制此数据。我已经解释了以下代码的工作原理，以便您可以根据需要进行修改。

您可以在下面的gif中查看此代码的效果。

此代码如何自动更改源数据和更新数据透视表？首先，我们使用了worksheet_deactivate事件。仅在切换或禁用包含代码的工作表时触发此事件。这就是代码自动运行的方式。

为了动态获取整个表作为数据范围，我们确定最后一行和最后一列。

lstrow = Cells（Rows.Count，1）.End（xlUp）.Row

lstcol = Cells（1，Columns.Count）.End（xlToLeft）.Column

使用这两个数字，我们定义了source_data。我们确定源数据范围将始终从A1开始。您可以定义自己的起始单元格引用。

设置source_data = Range（Cells（1，1），Cells（lstrow，lstcol））

现在我们有了动态的源数据。我们只需要在数据透视表中使用它即可。

由于我们不知道一个工作簿一次包含多少个数据透视表，因此我们将遍历每个工作表和每个工作表的数据透视表。这样就没有数据透视表了。为此，我们使用嵌套的for循环。

对于ThisWorkbook.Worksheets中的每个ws

对于ws.PivotTables中的每个pt

pt.ChangePivotCache _

ThisWorkbook.PivotCaches.Create（_

SourceType：= xlDatabase，_

SourceData：= source_data）

下一个pt

下一个ws

第一个循环遍历每张纸。第二个循环遍历工作表中的每个数据透视表。数据透视表已分配给变量pt。*我们使用pt对象的ChangePivotCache方法。我们使用ThisWorkbook.PivotCaches.Create方法动态创建数据透视缓存。此方法采用两个变量SourceType和SourceData。作为源类型，我们声明xlDatabase，作为SourceData，我们传递之前计算的source_data范围。

就是这样。我们将数据透视表自动化。这将自动更新工作簿中的所有数据透视表。

好的，这就是您可以在Excel中动态更改工作簿中所有数据透视表的数据源范围的方式。我希望我能解释得足够。如果您对本文有任何疑问，请在下面的评论部分中告诉我。

link：/ excel-pivot-tables-如何动态更新-excel-数据透视表数据源范围[如何在Excel中动态更新数据透视表数据源范围]：要动态更改数据透视表的源数据范围，我们使用数据透视表缓存。这几行代码可以通过更改源数据范围来动态更新任何数据透视表。

如何使用VBA自动刷新数据透视表：要自动刷新数据透视表，您可以使用VBA事件。使用此简单的代码行即可自动更新数据透视表。您可以使用3种自动刷新数据透视表的方法。

`link：/ events-in-vba-run-macro-if-any-changed-sheet-range [如果在指定范围内对表进行了任何更改，则运行宏]：*在您的VBA惯例中，当某个范围或单元格发生变化时，需要运行宏。在这种情况下，要在对目标范围进行更改时运行宏，请使用change事件。

在表上进行任何更改时运行宏因此，为了在工作表更新时运行宏，我们使用VBA的工作表事件。

使用突出显示当前行和列的最简单VBA代码使用此VBA小片段突出显示工作表的当前行和列。

Excel VBA中的工作表事件 |当您希望在工作表上发生指定事件时运行宏时，工作表事件非常有用。