如何自动更新数据透视表范围？

更新数据透视表很痛苦，不是吗？如果您在工作中经常使用数据透视表，我相信您也能体会到。

问题是，每次向源工作表添加新数据时，您都需要在刷新数据透视表之前更新数据透视表源范围。

现在想象一下，如果您每天向源工作表添加数据，则需要每天更新源范围。

每次更改数据透视表范围都是一团糟。是的，没错，添加数据越频繁，就越需要更新源范围。

所以重点是你需要一种方法来在添加新数据时自动更新源范围。

注意：数据透视表是中级 EXCEL 技能的一部分。

应用表以自动更新数据透视表范围

几天前，我向John Michaloudis询问了他的价值百万美元的数据透视表技巧。它说：将源数据放入表中。相信我，这是一百万美元的小费。

通过在源数据中应用表，您无需一次又一次更改数据透视表的源范围。

每次添加新数据时，它都会自动更新数据透视表范围。

创建数据透视表之前将数据转换为表

每次 创建数据透视表之前，请确保使用以下步骤将该表应用到源数据。

  1. 选择数据中的一个单元格。
  2. 使用快捷键 Ctrl + T 或导航至 → 插入选项卡 → 表格 → 表格。
  3. 您将看到一个弹出窗口，其中显示当前的数据范围。
    add table to update pivot table range
  4. 单击“确定”。
  5. 现在要创建数据透视表，请选择数据中的任何单元格。转到 → 设计选项卡 → 工具 → 使用数据透视表进行汇总。
    new pivot table to update pivot table range
  6. 单击“确定”。

现在，每当您向数据表添加新数据时，数据透视表范围都会自动更新，您只需刷新数据透视表即可。

创建数据透视表后将数据转换为表

如果您的工作表中已有数据透视表，则可以使用以下步骤将数据源转换为表。

  1. 选择数据源中的单元格之一。
  2. 使用快捷键 Ctrl + T 或导航至 → 插入选项卡 → 表格 → 表格。
  3. 您将看到一个弹出窗口，其中显示当前的数据范围。
  4. 单击“确定”。
  5. 现在，选择数据透视表中的一个单元格，然后转到→分析→数据→编辑数据源→编辑数据源（下拉菜单）。
  6. 您将看到一个弹出窗口以重新选择数据源，或者您也可以在范围条目中输入表名称。
    change source data to update pivot table range
  7. 单击“确定”。

从现在开始，每次您在源工作表中添加新数据时，数据透视表范围都会增加以自动更新。

使用 OFFSET 函数创建动态数据透视表范围

自动更新数据透视表范围的另一种最佳方法是使用动态范围。

每次您将新数据添加到源工作表时，动态范围都会自动扩展。以下是创建动态范围的步骤。

  1. 转至 → 公式选项卡 → 定义名称 → 名称管理器。
  2. 单击名称管理器后，您将看到一个弹出窗口。
    use name manager to update pivot table range
  3. 在“名称管理器”窗口中，单击“新建”以创建命名范围。
  4. 在新名称窗口中，输入
    1. 您的新系列的名称。我使用名称“SourceData”。
    2. 指定范围的范围。您可以在当前工作表或工作簿之间指定。
    3. 添加注释来描述您的命名范围。在“参考”输入栏中输入以下公式。
       =OFFSET(PivotTableData!$A$1,0,0,COUNTA(PivotTableData!$A:$A),COUNTA(PivotTableData!$1:$1))
  5. 最后，单击“确定”。
创建新名称以更新数据透视表范围

现在您拥有了创建数据透视表的动态范围。

您所需要做的只是使用源数据创建一个数据透视表，然后使用我在第一个表方法中使用的相同方法更改指定范围的源。

将新数据添加到源表后，只需刷新数据透视表即可。

这个公式是如何运作的？

在上面的公式中，我使用偏移函数来创建动态范围。

我提到单元格 A1 作为起点，然后在没有提及行和列的情况下，我使用 COUNTA 指定了范围的高度和宽度。

COUNTA 将计算包含 A 列和 1 行的值的单元格，并告诉 offset 相应地扩展其高度和宽度。

唯一需要注意的是 A 列和第 1 行之间没有空单元格。

使用 VBA 代码更新数据透视表

大多数人喜欢使用 VBA 代码。下面是使用 VBA 更新数据透视表范围的代码。

 Sub UpdatePivotTableRange() Dim Data_Sheet As Worksheet Dim Pivot_Sheet As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String Dim LastCol As Long Dim lastRow As Long 'Set Pivot Table & Source Worksheet Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3") 'Enter in Pivot Table Name PivotName = "PivotTable2" 'Defining Staring Point & Dynamic Range Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol)) NewRange = Data_Sheet.Name & "!" & DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_Sheet.PivotTables(PivotName). _ ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated." End Sub

在工作簿中使用它之前需要更改的内容。

  1. 第 13 行：更改源工作表的名称。
  2. 第 14 行：更改数据透视表工作表的名称。
  3. 第 17 行：更改数据透视表名称。

如果您在使用此代码时仍然遇到任何问题，请在评论框中写信给我。现在让我向您展示这段代码是如何工作的，以便您可以根据您的需要轻松修改它。

 Set Data_Sheet = ThisWorkbook.Worksheets("PivotTableData3") Set Pivot_Sheet = ThisWorkbook.Worksheets("Pivot3")

在上面的代码部分中，我们指定了源数据的数据透视表和工作表变量。您可以从此处更改电子表格名称。

 PivotName = "PivotTable2"

在代码的上述部分中，输入要使用此代码的数据透视表的名称。

 Data_Sheet.Activate Set StartPoint = Data_Sheet.Range("A1") LastCol = StartPoint.End(xlToRight).Column DownCell = StartPoint.End(xlDown).Row Set DataRange = Data_Sheet.Range(StartPoint, Cells(DownCell, LastCol))

上述代码部分将使用数据源工作表的单元格 A1 创建动态范围。

它将检查最后一列和最后一行的数据以创建动态范围。每次运行此宏时，它都会创建一个新的动态范围。

 'Ensure Pivot Table is Refreshed Pivot_Sheet.PivotTables(PivotName).RefreshTable 'Complete Message Pivot_Sheet.Activate MsgBox "Your Pivot Table is now updated."

上面的代码部分将刷新数据透视表并向用户显示数据透视表现已更新的消息。

获取 Excel 文件

下载

更多数据透视表教程

添加评论

您的邮箱地址不会被公开。 必填项已用 * 标注