在 Excel 2010 中使用 VBA 创建数据透视表和图表

  • 项目

**摘要：**了解如何在 Microsoft Excel 2010 中使用 Visual Basic for Applications (VBA) 创建和处理数据透视表及图表。使用代码创建数据透视表和图表可帮助您更加高效地重复执行这些任务。

上次修改时间： 2011年6月5日

适用范围： Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

本文内容
将 VBA 与数据透视表和图表结合使用的概述
使用 VBA 创建数据透视表
使用 VBA 创建图表
对内嵌图表进行更改
创建基于数据透视表的图表
结论
其他资源

**发布时间：**2011 年 6 月

供稿人：社区成员图标 Gerard M. Verschuuren 博士，Mr. Excel（该链接可能指向英文页面） | Mark Roberts，Microsoft Corporation

目录

  • 将 VBA 与数据透视表和图表结合使用的概述

  • 使用 VBA 创建数据透视表

  • 使用 VBA 创建图表

  • 对内嵌图表进行更改

  • 创建基于数据透视表的图表

  • 结论

  • 其他资源

单击以获取代码下载示例工作簿：使用 VBA 创建数据透视表和图表 (PivotTablesAndCharts.xlsm)（该链接可能指向英文页面）

将 VBA 与数据透视表和图表结合使用的概述

通过阅读本文并下载示例工作簿，您可以了解如何使用 Visual Basic for Applications (VBA) 代码创建数据透视表和图表。要使用示例数据运行本文所述的代码，请下载 PivotTablesAndCharts（该链接可能指向英文页面） 示例工作簿。

使用 VBA 创建数据透视表

图 1 显示了 PivotTablesAndCharts 示例工作簿中由 CreatePivot 宏创建的数据透视表。

图 1. 数据透视表

数据透视表

此数据透视表以示例工作簿的 Employees 工作表中的数据表为基础。

如下面的代码行所示，CreatePivot 宏激活工作表，然后使用 PivotTableWizard 方法开始创建数据透视表的过程。

ActiveWorkbook.Sheets("Employees").Select
Range("A1").Select
Set objTable = Sheet1.PivotTableWizard

备注

如果您在代码编辑器中按 F8 逐行浏览 CreatePivot 宏代码，您可以看到调用 PivotTableWizard 方法的代码行向工作簿中添加一个新工作表，然后在该工作表上创建新的数据透视表。

PivotTableWizard 方法返回 PivotTable 对象，该对象具有关联的 PivotFields 集合。CreatePivot 宏继续向 PivotFields 集合中添加 PivotField 对象以指定下面的数据透视表字段。

  • 名为 DEPT 的行字段

  • 名为 LOCATION 的列字段

  • 基于使用 SUM 函数的 SALARY 字段的数据字段

Set objField = objTable.PivotFields("DEPT")
objField.Orientation = xlRowField

Set objField = objTable.PivotFields("LOCATION")
objField.Orientation = xlColumnField

Set objField = objTable.PivotFields("SALARY")
objField.Orientation = xlDataField
objField.Function = xlSum
objField.NumberFormat = "$ #,##0"

备注

尽管本示例只使用一个行字段和一个列字段，但是您可以向 PivotFields 集合中添加更多字段，以创建在各个维度上具有多个级别的数据透视表。

CreatePivot 宏还添加一个页字段，以便启用按 GENDER 字段筛选报告，如下面的代码行所示。

Set objField = objTable.PivotFields("GENDER")
objField.Orientation = xlPageField

CreatePivot 宏然后在打印预览中显示新的数据透视表。

ActiveSheet.PrintPreview

如下面的代码行所示，在用户关闭打印预览之后，CreatePivot 宏询问用户是否删除新的数据透视表。将 DisplayAlerts 属性设置为 False 的代码行禁止显示 Microsoft Excel 中的内置警告，即"要删除的工作表中可能存在数据"。

Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
    ActiveSheet.Delete
End If
Application.DisplayAlerts = True

下面的列表显示 CreatePivot 宏的完整代码。

Sub CreatePivot()
    ' Creates a PivotTable report from the table on Sheet1
    ' by using the PivotTableWizard method with the PivotFields
    ' method to specify the fields in the PivotTable.
    Dim objTable As PivotTable, objField As PivotField
    
    ' Select the sheet and first cell of the table that contains the data.
    ActiveWorkbook.Sheets("Employees").Select
    Range("A1").Select
    
    ' Create the PivotTable object based on the Employee data on Sheet1.
    Set objTable = Sheet1.PivotTableWizard
    
    ' Specify row and column fields.
    Set objField = objTable.PivotFields("DEPT")
    objField.Orientation = xlRowField
    Set objField = objTable.PivotFields("LOCATION")
    objField.Orientation = xlColumnField
    
    ' Specify a data field with its summary
    ' function and format.
    Set objField = objTable.PivotFields("SALARY")
    objField.Orientation = xlDataField
    objField.Function = xlSum
    objField.NumberFormat = "$ #,##0"
    
    ' Specify a page field.
    Set objField = objTable.PivotFields("GENDER")
    objField.Orientation = xlPageField
    
    ' Preview the new PivotTable report.
    ActiveSheet.PrintPreview
    
    ' Prompt the user whether to delete the PivotTable.
    Application.DisplayAlerts = False
    If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
 End Sub

使用 VBA 创建图表

图 2 显示了示例工作簿中由 CreateChart 宏创建的图表。

图 2. 三维柱形图

三维柱形图

此图表以示例工作簿的 Table 工作表中的数据为基础。

CreateChart 宏开头的代码行选择 Table 工作表，然后提示用户选择制图的数据范围。将 InputBox 方法的 Default 参数设置为 Selection.Address 并将 Type 参数设置为 8 将返回 Range 对象，并且其 Address 属性设置为用户选择范围的范围引用。

ActiveWorkbook.Sheets("Table").Select

Set objSelection = _
    Application.InputBox(Prompt:="Select the columns and rows to chart", _
    Default:=Selection.Address, _
    Type:=8)

接下来的代码行验证是否选择了多个单元格，如果没有，则提示用户做出正确选择。

If objSelection.Cells.Count = 1 Then
    MsgBox "You must select at least one row or column for the chart range."
    Exit Sub
End If

下面的代码行执行创建图表的工作。Charts 集合的 Add 方法创建一个新的图表工作表，然后返回被赋予 objChart 变量的 Chart 对象。随后 With 语句使用该变量指定图表的外观，从使用 SetSourceData 方法指定用户选择的数据范围开始。请注意，.Legend.Delete 行用于删除图例，因为它重复显示三维柱形图第三个轴上的信息。

Set objChart = Charts.Add
With objChart
    .SetSourceData objSelection
    .ChartType = xl3DColumn
    .Location xlLocationAsNewSheet
    .Legend.Delete
    .PlotBy = xlColumns
End With

备注

本示例创建了一个非常简单的图表。Chart 对象的属性和方法有很多。请参阅图表成员主题以了解有关可用于指定图表外观的不同属性和方法。

CreateChart 宏中剩下的代码行提供一些示例，提示用户指定其他影响图表外观的属性，并且还询问用户是否删除图表。下面的列表显示了 CreateChart 宏的完整代码。

Sub CreateChart()
    ' Create a new chart sheet from the table on Sheet2
    ' by using the Add method of the Charts collection.
    Dim objSelection As Range, objChart As Chart
    
    ' Select the sheet that contains the data.
    ActiveWorkbook.Sheets("Table").Select
    
    ' Prompt the user to select the range to chart
    ' and set the Range object to the specified range.
    Set objSelection = _
        Application.InputBox(Prompt:="Select the columns and rows to chart", _
        Default:=Selection.Address, _
        Type:=8)
    
    ' Verify whether a selection was made.
    If objSelection.Cells.Count = 1 Then
        MsgBox "You must select at least one row or column for the chart range."
        Exit Sub
    End If
    
    ' Create a new chart sheet and specify its source data
    ' and appearance.
    Set objChart = Charts.Add
    With objChart
        .ChartType = xl3DColumn
        .SetSourceData objSelect
        .Location xlLocationAsNewSheet
        .Legend.Delete
        .PlotBy = xlColumns
    End With
    
    ' Ask the user whether to plot by rows instead.
    If MsgBox("Or plot by rows?", vbYesNo) = vbYes Then
        objChart.PlotBy = xlRows
    End If
    
    ' Prompt the user for a title.
    objChart.HasTitle = True
    objChart.ChartTitle.Text = InputBox("Title?")
    
    ' Ask the user whether to delete the chart.
    Application.DisplayAlerts = False
    If MsgBox("Delete chart?", vbYesNo) = vbYes Then
        ActiveSheet.Delete
    End If
    Application.DisplayAlerts = True
End Sub

对内嵌图表进行更改

示例工作簿中的 DynamicChart 宏显示了如何根据用户选择的所有行和列更改内嵌图表的外观。图 3 显示了运行宏并选择"北部"和"东部"列的数据后的图表外观。

图 3. 动态图表

动态条形图

若要访问并处理内嵌图表，您必须使用 Worksheet 对象的 ChartObjects 集合，而不是本文前面一节中的示例使用的 Workbook 对象的 Charts 集合。DynamicChart 宏中开头的代码行显示如何通过激活包含内嵌图表的工作表，然后将 Chart 对象变量设置为该工作表的 ChartObjects 集合中的第一个项目来执行此操作。

ActiveWorkbook.Sheets("Table+Chart").Activate
Set objChart = ActiveSheet.ChartObjects(1).Chart

接下来的代码行提示用户选择制图的行或列，通过使用本文使用 VBA 创建图表一节所示的相同方法将该选择赋予 Range 对象变量。

代码将根据用户的选择确定是将第一行还是第一列用作图表的类别。为此，代码会将所选行的数量与所选列的数量进行比较。如果行的数量比较多，则将第一列的值选为类别，如果列的数量比较多，则将第一行的值选为类别。

r = objSelection.Rows.Count
c = objSelection.Columns.Count
If r > c Then
    Set objCategories = Range(Cells(1, 1), Cells(r, 1))
Else
    Set objCategories = Range(Cells(1, 1), Cells(1, c))
End If

DynamicChart 宏中最后的代码行使用 Union 方法根据用户的选择以及代码确定用作类别的行或列创建一个范围。最后，代码将该范围传递给 SetSourceData 方法以更新图表的显示。

Set objSrcData = Union(objCategories, objSelection)
objChart.SetSourceData objSrcData

下面的代码示例显示 DynamicChart 宏的完整列表。

Sub DynamicChart()
    ' Adjusts the embedded chart on Sheet3.
    Dim objChart As Chart, objChObject As ChartObject
    Dim objSelection As Range, objSrcData As Range, objCategories As Range
    Dim r As Long, c As Long
    
    ' Activate the sheet that contains the chart.
    ActiveWorkbook.Sheets("Table+Chart").Activate
    
    ' Access the chart from the ChartObject collection
    ' of the active sheet.
    Set objChart = ActiveSheet.ChartObjects(1).Chart
    
    ' Prompt user to select the rows or columns to chart
    ' and set the Range object to the specified range.
    Set objSelection = _
        Application.InputBox(Prompt:="Select entire rows or columns to chart", _
        Default:=Selection.Address, _
        Type:=8)
    
    ' Determine whether the user selected rows or columns,
    ' and then use either the first row or first column
    ' as the range for categories.
    r = objSelection.Rows.Count
    c = objSelection.Columns.Count
    If r > c Then
        Set objCategories = Range(Cells(1, 1), Cells(r, 1))
    Else
        Set objCategories = Range(Cells(1, 1), Cells(1, c))
    End If
    
    ' Create a single range from the union of
    ' categories and selected data, and then
    ' update the chart.
    Set objSrcData = Union(objCategories, objSelection)
    objChart.SetSourceData objSrcData
End Sub

创建基于数据透视表的图表

您还可以根据示例工作簿中 CreateChartForPivot 宏演示的数据透视表创建图表。该宏通过调用本文使用 VBA 创建数据透视表一节中所述的 CreatePivot 宏开始此过程。

其中一个值得关注的地方是从新工作表的 PivotTables 集合中访问新建的数据透视表的代码行。

Set objPivot = ActiveSheet.PivotTables(1)

另一个值得关注的地方是使用 PivotTable 属性创建包括整个数据透视表但不包括页字段的范围的代码行。

Set objPivRange = objPivot.TableRange1

CreateChartForPivot 宏中最后的代码行使用此范围指定源数据，然后指定图表的外观。请注意，图例再次遭到删除，因为它会重复显示三维柱形图第三个轴上的信息。

With objChart
    .SetSourceData objPivRange
    .ChartType = xl3DColumn
    .Legend.Delete
End With

下面的代码示例显示了 CreateChartForPivot 宏的完整列表。

Sub CreateChartForPivot()
    ' Creates a chart based on a PivotTable report.
    Dim objPivot As PivotTable, objPivotRange As Range, objChart As Chart
    
    ' Call the CreatePivot macro to create a new PivotTable report.
    CreatePivot
   
    ' Determine whether the user deleted the PivotTable report,
    ' and if so, exit the macro.
    If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
    
    ' Access the new PivotTable from the sheet's PivotTables collection.
    Set objPivot = ActiveSheet.PivotTables(1)
    
    ' Add a new chart sheet.
    Set objChart = Charts.Add
    
    ' Create a Range object that contains
    ' all of the PivotTable data, except the page fields.
    Set objPivotRange = objPivot.TableRange1
    
    ' Specify the PivotTable data as the chart's source data.
    With objChart
        .SetSourceData objPivotRange
        .ChartType = xl3DColumn
        .Legend.Delete
    End With
End Sub

结论

本文和 PivotTablesAndCharts 示例工作簿提供如何在 Excel 中使用 VBA 代码创建数据透视表和图表的示例。

其他资源