处理数量较大的数据时，一般分为数据获取、数据筛选，以及结果展示几个步骤。在 Excel 中，我们可以利用数据透视表(Pivot Table)方便快捷的实现这些工作。

本文首先手把手的教你如何在 Excel 中手动构建一个基本的数据透视表，最后用 VBA 展示如何自动化这一过程。

注：

  • 本文基于 Excel 2016 for Mac 完成，个别界面和 Windows 版略有差异
  • 如果要完成 VBA 的部分，Excel for Mac 需要升级到 15.38 版本以上
  • Excel 2007 及之后的顶部 Ribbon 菜单，文中简称为 Ribbon
  • 开启“开发工具”菜单的方法也请自行了解

I. 源数据

Excel 提供了丰富的数据来源，我们可以从 HTML、文本、数据库等处获取数据。

这个步骤本文不展开讨论，以下是我们作为分析来源的工作表数据：


II. 创建数据透视表

  • 此处将工作表重命名为sheet1
  • 首先确保表格第一行是表头
  • 点击表中任意位置
  • 选中 Ribbon 中的“插入”
  • 点击第一个图标“数据透视表”，出现“创建数据透视表”对话框

注意观察对话框中的各种选项，这里我们都采用默认值

点击“确定”后，一个空的数据透视表出现在了新工作表中：


III. 数据透视表中的字段

  • 在“数据透视表生成器”菜单中，选择“球队、平、进球、失球、积分、更新日期”几个字段


  • 将“平”拖放至“行”列表中的“球队”上方；表示在“平局”的维度上，嵌套(nesting)的归纳了“球队”的维度
  • 将“更新日期”拖放至“筛选器”列表中；表示可以根据更新日期来筛选显示表格数据


  • 分别对当前“值”列表中的几个字段，点击其右侧的i图标
  • 因为本例中无需计算其默认的“求和”，故将这几个字段的“汇总方式”都改为“平均值”


  • 暂时关闭“数据透视表生成器”
  • 该窗口随后可以用“字段列表”按钮重新打开


此时一个基本的数据透视表已经成型


IV. 增加自定义字段

有时基本的字段并不能满足分析的需要，此时就可以在数据透视表中插入基于公式计算的自定义字段。

下面用不同的方法加入两个自定义字段：

4.1 简单运算的公式

首先简单计算一下各队的场均进球数：

  • 点击数据透视表中的任意位置，以激活“数据透视表分析” Ribbon 标签
  • 点击“字段、项目和集”按钮，在弹出的下拉菜单中选择“计算字段”
  • “插入计算字段”对话框会出现
  • 在“名称”中填入“场均进球”
  • 在“字段”列表中分别双击“进球”和“场次”
  • 以上两个字段会出现在“公式”框中，在它们中间键入表示除法的斜杠/
  • 也就是说，此时“公式”部分为 =进球/场次


  • 点击“确定”关闭对话框，数据透视表中出现了新的“求和/场均进球”字段
  • 按照之前的方法，将字段的汇总方式改为“平均值”，确定关闭对话框


4.2 调用 Excel 公式

再简单的评估一下球队的防守质量，这里我们假设以如下 Excel 公式判断：

= IF(净胜球>=0,2,1)

防守还不错的取 2，不佳的则标记为 1。

  • 按照刚才的方法新建一个计算字段
  • 将上述公式填入“公式”框


  • 将字段的汇总方式改为“计数” -- 虽然在此处并无太多实际意义

V. 利用切片器过滤数据

除了可以在“数据透视表生成器”中指定若干个“过滤器”，切片器(Slicers)也可以用来过滤数据，使分析工作更清晰化。

切片器的创建非常简单：

  • 在 Ribbon 中点击“插入切片器”按钮
  • 在字段列表中选择“胜”、“负”
  • 两个切片器就出现在了界面中


  • 点击切片器中的项目就可以筛选
  • 结合 ctrl 键可以多选


VI. 成果

至此，我们得到了一个基于源数据的、可以自由组合统计维度、可以用多种方式筛选展示的数据透视表。

可以在 Ribbon 的“设计”菜单中选择预设的样式等，本文不展开论述。


以上就是创建数据透视表的基本过程。

VII. 自动化创建

基本的数据透视表的创建和调整并不复杂，但如果有很多类似的重复性工作的话，使用一些简单的 VBA 来自动化这一过程，将极大提升工作的效率。

本例中使用 VBA 脚本完成与上述例子一样的任务，对于 VBA 语言仅做简单注释，想更多了解可以自行查阅官方的文档等

7.1 一键生成

此处我们放置一个按钮在源数据所在的数据表，用于每次点击自动生成一个数据透视表。

  • 在 Ribbon 的“开发工具”中点击按钮
  • 在界面任意位置框选一个按钮的尺寸
  • 释放鼠标后弹出“指定宏”对话框
  • 此处我们将“宏名称”框填入 ThisWorkbook.onCreatePovit
  • “宏的位置”选择“此工作簿”
  • 点击"编辑"后关闭对话框


  • 将按钮名称改为“一键生成透视表”


7.2 脚本编写

  • 点击 Ribbon 中“开发工具”下面第一个按钮“Visual Basic”
  • 在出现的“Visual Basic”编辑器中，选择左侧的“ThisWorkbook”类目
  • 在右侧编辑区贴入下面的代码




Sub onCreatePovit()
    Application.DisplayAlerts = False

    ' 声明变量
    Dim sheet1 As Worksheet
    Dim pvtTable As PivotTable
    Dim pvtField As PivotField
    Dim pvtSlicerCaches As SlicerCaches
    Dim pvtSlicers As slicers
    Dim pvtSlicer As Slicer

    ' 删除可能已存在的透视表
    Dim existFlag As Boolean
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.Name = "pivot1" Then existFlag = True: Exit For
    Next
    If existFlag = True Then
        Sheets("pivot1").Select
        ActiveWindow.SelectedSheets.Delete
    End If

    ' 初始化
    Set sheet1 = ActiveWorkbook.Sheets("sheet1")
    Set pvtSlicerCaches = ActiveWorkbook.SlicerCaches

    ' 指定数据源
    sheet1.Select
    Range("A1").Select

    ' 创建透视表
    Set pvtTable = sheet1.PivotTableWizard
    ActiveSheet.Name = "pivot1"
    
    ' 指定行和列
    pvtTable.AddFields _
        RowFields:=Array("平", "球队"), _
        ColumnFields:="Data"

    ' 指定数据字段
    Set pvtField = pvtTable.PivotFields("失球")
    pvtField.Orientation = xlDataField
    pvtField.Function = xlAverage
    pvtField.Name = "平均值/失球"
    Set pvtField = pvtTable.PivotFields("进球")
    pvtField.Orientation = xlDataField
    pvtField.Function = xlAverage
    pvtField.Name = "平均值/进球"
    Set pvtField = pvtTable.PivotFields("积分")
    pvtField.Orientation = xlDataField
    pvtField.Function = xlAverage
    pvtField.Name = "平均值/积分"

    ' 指定计算字段
    pvtTable.CalculatedFields.Add Name:="场均进球", Formula:="=进球/场次"
    Set pvtField = pvtTable.PivotFields("场均进球")
    pvtField.Orientation = xlDataField
    pvtField.Function = xlAverage
    pvtField.Name = "平均值/场均进球"
    pvtTable.CalculatedFields.Add Name:="防守质量", Formula:="= IF(净胜球>=0,2,1)"
    Set pvtField = pvtTable.PivotFields("防守质量")
    pvtField.Orientation = xlDataField
    pvtField.Function = xlCount
    pvtField.Name = "计数/防守质量"

    ' 指定切片器
    Set pvtSlicers = pvtSlicerCaches.Add(pvtTable, "胜", "胜_" & ActiveSheet.Name).slicers
    Set pvtSlicer = pvtSlicers.Add(ActiveSheet, , , , 300, 400)
    Set pvtSlicers = pvtSlicerCaches.Add(pvtTable, "负", "负_" & ActiveSheet.Name).slicers
    Set pvtSlicer = pvtSlicers.Add(ActiveSheet, , , , 350, 450)
        
    ' 指定过滤器
    Set pvtField = pvtTable.PivotFields("更新日期")
    pvtField.Orientation = xlPageField

    Application.DisplayAlerts = True
End Sub复制代码



7.3 运行程序

回到界面中，每次点击按钮就会在新工作表中生成结构和之前例子一致的数据透视表


VIII. 总结

  • 本文简单的展示了在 Excel 中创建透视表的过程，以及其筛选、展示数据的方式
  • 通过 VBA 可以完成和手动创建一样甚至更多的功能，并大大提高工作效率

IX. 解决 windows 版本中无法打开 VBA 编辑器

如果遇到此类问题，可按以下步骤尝试解决：

  1. download vbe6ext.OLB from http://www.121down.com/soft/softview-63611.html
  2. copy vbe6ext.OLB to C:\Program Files\Common Files\microsoft shared\VBA\VBA6and C:\Program Files\Common Files\microsoft shared\VBA\VBA7and C:\Program Files\Common Files\microsoft shared\VBA\VBA7.1

X. 参考资料