情报技巧│掌握数据分析基本功：用EXCEL做数据透视表

来源：掘金

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

本文手把手的教你如何在 Excel 中手动构建一个基本的数据透视表。

注： 

• 本文基于 Excel 2016 for Mac 完成，个别界面和 Windows 版略有差异 

• Excel 2007 及之后的顶部 Ribbon 菜单，文中简称为 Ribbon

• 开启“开发工具”菜单的方法也请自行了解
  

1

 源数据

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

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

2

创建数据透视表

• 此处将工作表重命名为sheet1

• 首先确保表格第一行是表头

• 点击表中任意位置

• 选中 Ribbon 中的“插入”

• 点击第一个图标“数据透视表”，出现“创建数据透视表”对话框

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

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

3

数据透视表中的字段

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

• 将“平”拖放至“行”列表中的“球队”上方；表示在“平局”的维度上，嵌套(nesting)的归纳了“球队”的维度

• 将“更新日期”拖放至“筛选器”列表中；表示可以根据更新日期来筛选显示表格数据

• 分别对当前“值”列表中的几个字段，点击其右侧的i图标

• 因为本例中无需计算其默认的“求和”，故将这几个字段的“汇总方式”都改为“平均值”

• 暂时关闭“数据透视表生成器”

• 该窗口随后可以用“字段列表”按钮重新打开

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

4

增加自定义字段

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

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

1.简单运算的公式

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

• 点击数据透视表中的任意位置，以激活“数据透视表分析” Ribbon 标签

• 点击“字段、项目和集”按钮，在弹出的下拉菜单中选择“计算字段”

• “插入计算字段”对话框会出现

• 在“名称”中填入“场均进球”

• 在“字段”列表中分别双击“进球”和“场次”

• 以上两个字段会出现在“公式”框中，在它们中间键入表示除法的斜杠/

• 也就是说，此时“公式”部分为 =进球/场次

• 点击“确定”关闭对话框，数据透视表中出现了新的“求和/场均进球”字段

• 按照之前的方法，将字段的汇总方式改为“平均值”，确定关闭对话框

2.调用 Excel 公式

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

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

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

• 按照刚才的方法新建一个计算字段

• 将上述公式填入“公式”框

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

5

利用切片器过滤数据

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

切片器的创建非常简单：

• 在 Ribbon 中点击“插入切片器”按钮

• 在字段列表中选择“胜”、“负”

• 两个切片器就出现在了界面中

• 点击切片器中的项目就可以筛选

• 结合 ctrl 键可以多选

6

成果

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

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

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