如何动态更新数据透视表数据源范围在Excel

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

|目前，我们可以使用“ link：/ table-excel-2007-17-amazing-features-of-excel-tables [Excel Tables]”或“ link：/ excel-range-name-dynamic- named-ranges-in-excel [动态命名范围]。但是这些技术并非万无一失。因为您仍然必须手动刷新数据透视表。如果您的大型数据包含成千上万的行和列，那么excel表将无济于事。相反，它将使您的文件变重。因此，唯一的方法就是VBA。

在本文中，我们将学习如何使数据透视表自动更改数据源。换句话说，我们将自动执行手动更改数据源的过程，以动态包括添加到源表中的新行和新列，并立即在数据透视表中反映更改。

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

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

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

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

现在我们准备实现代码。

用于使用新范围动态更新数据透视表的源代码为了解释其工作原理，我有一本工作簿。该工作簿包含两页。 Sheet1包含可以更改的源数据。 Sheet2包含数据透视表，该数据透视表取决于sheet2的源数据。

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

Private Sub Worksheet_Deactivate()

Dim pt As PivotTable

Dim pc As PivotCache

Dim source_data As Range

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

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

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

Set pc = ThisWorkbook.PivotCaches.Create(xlDatabase, SourceData:=source_data)

Set pt = Sheet2.PivotTables("PivotTable1")

pt.ChangePivotCache pc

End Sub

如果您有类似的工作簿，则可以直接复制此数据。我已经解释了下面的代码。

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

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

现在，要更改数据透视表的源数据，我们将更改数据透视表缓存中的数据。使用数据透视表缓存创建数据透视表。数据透视表缓存包含旧的源数据，直到不手动刷新数据透视表或手动更改源数据范围为止。

我们创建了名为pt的数据透视表，名为pc的数据透视表和名为source_data的范围的引用。源数据将包含整个数据。为了动态获取整个表作为数据范围，我们确定最后一行和最后一列。

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））

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

我们将此数据存储在数据透视缓存中，因为我们知道数据透视缓存存储所有数据。

设置pc = ThisWorkbook.PivotCaches.Create（xlDatabase，SourceData：= source_data）接下来，我们定义要更新的数据透视表。由于我们要更新PivotTable1（数据透视表的名称。您可以在sheet1上在“分析”选项卡中检查数据透视表的名称。）在表1上进行设置，如下所示。

设置pt = Sheet2.PivotTables（“ PivotTable1”）

现在，我们仅使用此数据透视表缓存来更新数据透视表。我们使用pt对象的changePivotCache方法。

pt.ChangePivotCache pc

而且我们的数据透视表是自动化的。这将自动更新您的数据透视表。如果您有多个具有相同数据源的表，则只需在每个数据透视表对象中使用相同的缓存。

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

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

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

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

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

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