如何用vba创建数据透视表？

在我向您提供本指南并开始使用 VBA 创建数据透视表之前，让我告诉您一些事情。

我六年前才学会使用 VBA。我第一次编写宏代码来创建数据透视表时，失败了。

从那时起，我从糟糕的编码中学到的东西比实际工作的代码更多。

今天，我将向您展示一种使用宏代码自动化数据透视表的简单方法。

通常，当您将数据透视表插入电子表格时，它会通过一个简单的过程发生，但是整个过程是如此之快，以至于您永远不会注意到发生了什么。

在 VBA 中，整个过程是相同的，只是使用代码运行。在本指南中，我将向您展示每个步骤并解释如何为其编写代码。

只需看下面的示例，您可以在其中使用按钮运行此宏代码，它会立即在新工作表中返回一个新的数据透视表。

话不多说，让我们开始编写宏代码来创建数据透视表。

在 VBA 中编写宏代码以在 Excel 中创建数据透视表的 8 个简单步骤

为了您的方便，我将整个过程分为 8 个简单的步骤。执行这些步骤后，您将能够自动化所有数据透视表。

请务必从此处下载此文件以进行后续操作。

1.声明变量

第一步是声明我们需要在代码中使用的变量来定义不同的事物。

 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long

在上面的代码中我们声明：

PSheet：为新的数据透视表创建工作表。
DSheet：用作技术表。
PChache：用作数据透视表缓存的名称。
PTable：用作数据透视表的名称。
PRange：设置源数据范围。
LastRow 和 LastCol：获取数据范围的最后一行和最后一列。

2. 插入新的电子表格

在创建数据透视表之前，Excel 会插入一张空白工作表，然后在其上创建一个新的数据透视表。

并且，下面的代码将为您做同样的事情。

它将在活动工作表之前插入一个名为“数据透视表”的新工作表，如果已经存在同名工作表，它将首先删除它。

插入新工作表后，此代码将设置数据透视表工作表上的 PSheet 变量的值和源数据工作表上的 DSheet 变量的值。

 'Declare Variables On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data")

确保将代码中的工作表名称替换为数据中的名称。

3. 设置数据范围

现在，下一步是设置源工作表中的数据范围。这里你需要注意一件事，你不能指定固定的源范围。

您需要可以识别源表中所有数据的代码。下面是代码：

 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol)

此代码将从数据表的第一个单元格开始，选择直到最后一行，然后直到最后一列。

最后，将选定的范围设置为源。最好的部分是您不需要在创建数据透视表时每次都更改数据源。

4. 创建数据透视缓存

在Excel 2000及更高版本中，在创建数据透视表之前，必须创建数据透视缓存来设置数据源。

通常，当您创建数据透视表时，Excel 会自动创建数据透视表缓存而不询问您，但是当您需要使用 VBA 时，您必须为其编写一些代码。

 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable")

该代码有两种工作方式，第一，使用数据源设置数据透视缓存，第二，设置新插入的工作表中的单元格地址以插入数据透视表。

您可以通过修改此代码来更改数据透视表的位置。

5.插入空白数据透视表

在数据透视表缓存之后，下一步是插入一个空的数据透视表。请记住，当您创建数据透视表时，您总是首先获得一个空数据透视表，然后设置所有值、列和行。

这段代码会做同样的事情：

 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable")

此代码创建一个空数据透视表并将其命名为“SalesPivotTable”。您可以从代码本身更改此名称。

6.插入行和列字段

创建空数据透视表后，下一步是像平常一样插入行和列字段。

对于每个行和列字段，您必须编写一个代码。这里我们要在行字段中添加年份和月份，在列字段中添加面积。

这是代码：

 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With

在此代码中，您提到了年和月作为两个字段。现在，如果您查看代码，您会发现位置编号也在那里。该位置号定义了字段的顺序。

每次需要添加多个字段（行或列）时，请指定它们的位置。您可以通过更改代码中的名称来修改字段。

7. 插入数据字段

最主要的是定义数据透视表中的值字段。

定义值的代码与定义行和列不同，因为这里我们需要定义数字、位置和函数的格式。

 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With

您可以使用上面的代码将金额添加为值字段。此代码会将值格式化为带有分隔符 (,) 的数字。

我们使用 xlsum 来添加值，但您也可以使用 xlcount 和其他函数。

8. 设置数据透视表的格式

最终，您需要使用代码来格式化数据透视表。数据透视表中通常有默认格式，但您可以更改此格式。

使用VBA，您可以在代码中设置格式样式。

代码是：

 'Format Pivot TableActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

上面的代码将应用线条和“Pivot Style Medium 9”样式，但您也可以使用此链接中的其他样式。

最后，您的代码就可以使用了。

[完整代码] 使用 VBA 在 Excel 中创建数据透视表 – 复制和粘贴宏

 Sub InsertPivotTable() 'Macro By Exceladvisor.org 'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets("PivotTable").Delete Sheets.Add Before:=ActiveSheet ActiveSheet.Name = "PivotTable" Application.DisplayAlerts = True Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets("Data") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column Set PRange = DSheet.Cells(1, 1).Resize(LastRow, LastCol) 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create _ (SourceType:=xlDatabase, SourceData:=PRange). _ CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), _ TableName:="SalesPivotTable") 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Cells(1, 1), TableName:="SalesPivotTable") 'Insert Row Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Year") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Month") .Orientation = xlRowField .Position = 2 End With 'Insert Column Fields With ActiveSheet.PivotTables("SalesPivotTable").PivotFields("Zone") .Orientation = xlColumnField .Position = 1 End With 'Insert Data Field With ActiveSheet.PivotTables("SalesPivotTable").PivotFields ("Amount") .Orientation = xlDataField .Function = xlSum .NumberFormat = "#,##0" .Name = "Revenue " End With 'Format Pivot Table ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9" End Sub

下载示例文件

准备好

现有工作表上的数据透视表

我们上面使用的代码在新工作表上创建数据透视表，但有时您需要将数据透视表插入工作簿中已有的工作表中。

在上面的代码（新工作表中的数据透视表）中，在编写插入新工作表的代码的部分中，然后为其命名。请对代码进行一些更改。

不用担心;我给你看。

首先，您需要指定要在其中插入数据透视表的工作表（已在工作簿中）。

为此，您必须使用以下代码：

您必须在 PSheet 变量中指定工作表的名称，而不是插入新工作表。

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”)

还有一些事情要做。您使用的第一个代码会在插入数据透视表之前删除同名的工作表（如果存在）。

当您将数据透视表插入现有工作表时，您可能已经有一个同名的数据透视表。

我的意思是你需要先删除那个枢轴。

为此，您需要添加代码，以便在插入新数据透视表之前从工作表中删除同名的数据透视表（如果存在）。

这是您需要添加的代码：

 Set PSheet = Worksheets("PivotTable") Set DSheet = Worksheets(“Data”) Worksheets("PivotTable").Activate On Error Resume Next ActiveSheet.PivotTables("SalesPivotTable").TableRange2.Clear

让我告诉你这段代码的作用。

首先，它只是将 PSheet 设置为要插入工作簿中已有数据透视表的工作表，并将 Data Worksheets 设置为 DSheet。

之后，它激活工作表并从中删除“销售数据透视表”。

重要提示：如果工作簿中的工作表名称不同，您可以通过代码更改它们。我已经突出显示了您需要执行此操作的代码。

在最后，

使用此代码，我们可以自动化您的数据透视表。最好的部分是它是一个独特的设置；之后我们只需单击一下即可创建数据透视表，您可以节省大量时间。现在告诉我一件事。

您是否曾经使用 VBA 代码创建数据透视表？

欢迎在评论区与我分享你的意见；我想与您分享它们，并与您的朋友分享这个技巧。

类似文章：