2.3.1 数据透视表实现统计分析
数据透视表是Excel中强大的数据分析工具。用户只需要通过简单拖拽就可以完成相对复杂的数据统计分析，相比函数的统计分析功能，数据透视表较为灵活方便。
数据透视表可以从数据源（工作表或数据库等）中获取数据，然后将多行多列的数据转换成有意义的数据表现形式，方便用户从多角度进行数据分析工作。
数据透视表中可以插入公式创建新的计算字段从而实现指标计算，也可以在每个维度层面进行分类汇总，用户可以展开或折叠整个字段，从而可以看到不同维度展示的数据。
基于数据透视表去开发业务报表也是非常轻松的事情，但数据透视表有一个显著的缺点，当数据源更新之后，透视表不会自动更新，但用户可以单击“数据|连接|全部刷新”命令实现数据刷新。
1.数据透视表的创建
数据透视表要求数据的格式是矩形数据库表。数据源的第一行为标题，下面的每一行数据叫作记录（或日志），用来描述数据的信息。数据的每一列是一个字段（包含维度和度量）。
● 维度：用于描述分析的字段。出现在数据透视表的行、列、筛选选项。
● 度量：用于汇总聚合的字段。出现的数据透视表的值选项。
以店铺销售明细表为例，数据如表2-19所示。此数据包含7个字段（店铺名称、订单号、用户ID、金额、日期、省份、城市），记录数为12行。
表2-19 店铺销售明细表
（1）数据分析需求
基于此数据集的汇总分析需求如下：
● 汇总不同省份的订单数、订单金额。
● 汇总不同店铺的订单数、订单金额。
● 江苏省不同店铺的订单数、订单金额。
● 不同省份不同城市的订单数、订单金额。
（2）操作步骤
1）选中区域A1:G13或数据区域内的任意单元格，然后单击“插入|表格|数据透视表”命令，在“创建数据透视表”对话框中，“选择放置数据透视表的位置”选项可以选择“新工作表”和“现有工作表”。选择默认选项为“新工作表”，数据透视表会生成在一张新建的工作表内，选择“现有工作表”选项，则需要输入存储数据透视表位置。这里选择生成在当前工作表的单元格I2，如图2-103所示。
图2-103 “创建数据透视表”对话框
2）单击“确定”按钮，弹出“数据透视表字段”对话框，该对话框包含整个数据集的所有字段以及四个区域（筛选、行、列、值）。进行汇总分析时，把需要分析的维度拖到行、列、筛选区域，汇总聚合的度量值拖到值区域。
3）将省份字段拖放到行区域，店铺名称（默认计数）、付费金额（默认求和）字段拖到值区域，完成不同省份的订单数、订单金额的汇总分析，如图2-104所示。
图2-104 “数据透视表字段”对话框
注意：
● 数据透视表为什么会对店铺名称字段实现计数，而对付费金额字段实现求和呢？因为店铺名称字段类型是字符串，值汇总依据默认计数，而付费金额字段类型是数值，值汇总依据默认求和。原则上任何字段都可以拖到值区域进行数值计数，但需要注意的是该字段一定不要有缺失值。
● 值汇总依据的切换有如下两种方法。
方法一：通过选中数据透视表中对应的需要修改的字段那一列的任意单元格，然后右击选择“值汇总依据”选项，下拉菜单里可以选择需要的值汇总依据，如图2-105所示。
图2-105 选择值汇总方式（方法一）
方法二：在值区域找到对应的需要修改的字段，单击下拉按钮，选择“值字段设置“选项，然后在弹出的“值字段设置”对话框里面选择需要的值字段汇总方式，如图2-106所示。
图2-106 选择值汇总方式（方法二）
4）将店铺名称字段拖放到行区域，店铺名称（默认计数）、付费金额（默认求和）字段拖到值区域，完成不同店铺的订单数、订单金额的汇总分析，如图2-107所示。
图2-107 不同店铺的订单数、订单金额
5）将店铺名称字段拖放到行区域，店铺名称（默认计数）、付费金额（默认求和）字段拖到值区域，省份字段拖到筛选区域，然后下拉菜单筛选“江苏省”选项，完成江苏省不同店铺的订单数、订单金额的汇总分析，如图2-108所示。
图2-108 江苏省不同店铺的订单数、订单金额
6）将省份、城市字段分别拖放到行区域（省份的位置在城市上面），店铺名称（默认计数）、付费金额（默认求和）字段拖到值区域，完成不同省份不同城市的订单数、订单金额的汇总分析。然后选中数据透视表任意单元格，右击选择“数据透视表选项”，在“数据透视表选项”对话框中切换到“显示”选项卡，勾选“经典数据透视表布局（启用网格中的字段拖放）”选项，单击“确定”按钮，实现将省份、城市这两个维度拆到不同的两列中。再选中数据透视表中省份列里任意单元格，右击选择“分类汇总省份”选项，单击去掉“√”选项，省份维度的汇总则被删除，如图2-109所示。
图2-109 不同省份不同城市的订单数、订单金额
注意：
● 在创建数据透视表时，可以通过单击“插入|表格|推荐的数据透视表”命令，快速完成数据透视表的制作。“推荐的数据透视表”对话框会显示一些缩略图，推荐给用户可以选择的数据透视表。店铺销售明细表的“推荐的数据透视表”，如图2-110所示。
图2-110 “推荐的数据透视表”对话框
● 当数据源的行记录增加时，希望通过执行“数据|连接|全部刷新”命令实现数据透视表的刷新，有三种方法可以解决这个问题。以店铺销售明细表为例，第一种方法是将需要增加的记录数据放置第二行到最后一行之间。第二种方法是在“创建数据透视表”的对话框里面“选择一个表或区域”下的“表/区域”内范围修改为“$A:$G”，这样就可以保证以后新增的记录数据都在这个范围之内。第三种方法是选择整个数据源区域，然后单击“插入|表格|表格”，将数据源添加到表中。
2.手动分组和自动分组
数据透视表里面有一项功能是“组合”，它可以对“数据透视表字段”对话框里面的行和列字段进行分组。Excel提供了两种字段组合方式：
● 手动组合：创建数据透视表之后，按〈Ctrl〉键同时选中需要组合的项，然后右击选择“组合”选项。或者通过单击“分析|分组|分组选择”命令来实现。
● 自动组合：如果是日期或者数值字段，可以使用“组合”对话框指定项的组合方式。选中数据透视表中需要组合的字段对应列里任意单元格，然后右击选择“组合”选项。或者通过单击“分析|分组|分组选择”命令来实现。
接下来仍以表2-19店铺销售明细表为例进行说明。
手动组合操作步骤如下：
1）创建第一个组，需要按住〈Ctrl〉键，同时选中“LZ旗舰店”“MBL旗舰店”“NWY旗舰店”，右击选择“组合”选项。
2）创建第二个组。按住〈Ctrl〉键，同时选中“SFL旗舰店”“XYBC旗舰店”，然后右击选择“组合”选项。
3）将默认的“数据组1”和“数据组2”替换成“区域一”和“区域二”，结果如图2-111所示。
图2-111 手动分组创建组合
当字段包含数值、日期时，Excel可以自动创建组。下面以日期字段为例实现自动创建组。这里将日期字段拖放到行区域，店铺名称（默认计数）、付费金额（默认求和）字段拖到值区域，完成不同日期维度下的订单数、订单金额的汇总分析。数据透视表会自动将日期聚合到月份维度，如图2-112所示。
图2-112 自动分组创建月份组合
如需同时显示季度、月维度，选中数据透视表中需要组合的日期字段对应列里任意单元格，然后右击选择“组合”选项，“组合”对话框里同时选择“季度”“月”选项，然后单击“确定”按钮，如图2-113所示。
图2-113 自动分组创建季度、月组合
按季度、月维度汇总的订单数、订单金额结果如图2-114所示。
图2-114 季度、月维度的汇总
3.添加计算字段
当创建数据透视表的数据源不允许或不方便修改，但是又需要进行简单公式运算时，这时候就需要利用数据透视表的添加计算字段功能。以某企业的产品销售订单表为例，数据如表2-20所示。基于此数据创建数据透视表并汇总各大区的成本、收入之和，并添加计算字段“成本收入比”（成本收入比=收入/成本），小数部分四舍五入保留两位。
表2-20 产品销售订单表
添加计算字段的操作步骤如下：
1）选中表格所有数据，单击“插入|表格|数据透视表”命令，在现有工作表的单元格F2位置创建数据透视表，然后将大区字段拖放到行区域，成本（默认求和）、收入（默认求和）字段拖放到值区域，如图2-115所示。
图2-115 不同区域的销量汇总
2）选中数据透视表任意区域，然后单击“分析|计算|字段、项目和集”命令，在下拉菜单里面选择“计算字段”，在“插入计算字段”对话框中的“名称”栏输入“成本收入比”，“公式”栏输入“=ROUND(收入/成本,2)”，然后单击“确定”按钮，如图2-116所示。
图2-116 “插入计算字段”对话框
3）基于数据透视表添加的计算字段“成本收入比”的结果如图2-117所示。
图2-117 添加“成本收入比”计算字段结果
4.切片器筛选数据透视表
创建完数据透视表之后，可以通过插入“切片器”来实现可视化筛选功能。下面以某企业不同大区对应的数据为例，来实现添加切片器筛选功能。
此处仍以表2-20所示的产品销售订单表为例进行说明。
切片器筛选数据透视表的操作步骤如下：
1）选中数据透视表任意区域，单击“插入|筛选器|切片器”命令，在“插入切片器”对话框中勾选“大区”选项，然后单击“确认”按钮，如图2-118所示。
图2-118 “插入切片器”对话框
2）创建完成的“大区”切片器选项，右击选择“大小与属性”选项，修改“位置与布局”下的“列数”参数为4，右击选择“切片器设置”选项，去掉“显示页眉”的勾选项。最后选中切片器，通过按住鼠标左键拖拽方式调整切片器边框大小，结果如图2-119所示。
图2-119 切片器属性设置
3）按住〈Ctrl〉键可以同时选中切片器中多个选项，实现数据透视表的筛选功能。切片器筛选“区域1”和“区域2”之后的数据结果如图2-120所示。
图2-120 使用切片器筛选数据透视表中显示的数据
5.数据透视图的制作
数据透视图是根据数据透视表中的数据制作的可视化图表。数据透视图和数据透视表之间具有很强的关联性，此外，Excel里面的所有图形在数据透视图中都可以用来进行绘制。
Excel提供了多种方法来创建数据透视图，方法如下：
● 选中数据透视表任意区域，然后单击“插入|图表|数据透视图|数据透视图”命令来创建数据透视图。
● 选中数据透视表任意区域，然后单击“分析|工具|数据透视图”命令来创建数据透视图。
● 先单击“插入|图表|数据透视图|数据透视图”命令或单击“插入|图表|数据透视图|数据透视图和数据透视表”命令，然后需要在弹出的“创建数据透视图”对话框或“创建数据透视表”对话框中输入数据源范围，输入放置数据透视图或数据透视表的位置，最后单击“确定”按钮，会同时出现数据透视表和数据透视图的绘制界面。
（1）数据透视图创建示例
以2017年的某企业四大区域（华东、华西、华南、华北）销售业绩表的部分数据为例，创建数据透视表和数据透视图。字段包括日期、区域、业绩，数据如图2-121所示。
图2-121 某企业区域销售业绩表
（2）数据透视图创建的操作步骤
1）首先基于此数据创建数据透视表，将月份字段拖放到行区域（日期字段自动按月分组），区域拖放到列区域，业绩拖放到值区域，生成结果如图2-122所示。
图2-122 基于某企业区域销售业绩创建的数据透视表
2）选中数据透视表任意区域，然后单击“插入|图表|二维柱形图|簇状柱形图”命令，生成图2-123所示的数据透视图。
图2-123 基于某企业区域销售业绩创建的数据透视图
选中数据透视表中需要组合的日期字段对应列里任意单元格，然后右击选择“组合”选项，在“组合”对话框里，选择“季度”选项，然后单击“确定”按钮，此时数据透视表和数据透视图同时发生了变化，展示的都是2017年的四大区域在不同季度对应的销售业绩数据，如图2-124所示。
图2-124 不同季度销售业绩对应的数据透视表和数据透视图
（3）数据透视图需要注意的几点事项
● 数据透视图和数据透视表都可以进行字段筛选。
● 数据透视图和数据透视表互相关联，筛选数据透视图，数据透视表会发生变化，筛选数据透视表，数据透视图也会发生变化。
● 删除基础数据透视表，数据透视图仍然存在。
● 一个数据透视表可以创建多个数据透视图，可以分别操作这些数据透视图。
● 数据透视图可以使用切片器功能。
6.数据模型的建立
之前讲述的数据透视表都是基于单表创建的，对于多表关联之后创建的数据透视表，可以使用Excel里面的增强功能“数据模型”来实现。Excel 2016版本里集成了Power Pivot功能，它是数据透视表功能的增强版，可以实现多表之间的关联，然后在合并后的大表基础上进行数据分析。此外，还包含新建度量值、新建KPI指标等功能。
（1）Power Pivot功能建立数据模型
单击“开发工具|加载项|COM加载项”命令，在弹出的“COM加载项”对话框中勾选“Microsoft Power Pivot for Excel”选项，然后单击“确定”按钮，这样就可以调出“Power Pivot”选项卡，如图2-125所示。
图2-125 加载“Power Pivot”选项卡
在某个工作簿里的三张不同工作表中存放有不同的数据，这里三张表分别是student_info（学生信息表）、student_score（学生成绩表）、student_course（学生课程表）。学生信息表和学生成绩表可以通过字段stuNo（学号）来关联，学生成绩表和学生课程表可通过字段CourseID（课程号）来关联。目的是通过三张表的关联分析不同性别、不同科目的平均成绩，性别字段（sex）位于student_info（学生信息表）中，科目名称字段（CourseName）位于student_course（学生课程表）中，成绩字段（Score）位于student_score（学生成绩表）中，数据如图2-126所示。
图2-126 学生信息表、学生成绩表、学生课程表
（2）操作步骤
1）选中第一张工作表里的student_info（学生信息表），然后单击“Power Pivot|表格|添加到数据模型”，在“创建表”对话框中勾选“我的表具有标题”选项，单击“确定”按钮，这样会把学生信息表的数据加载到“Power Pivot for Excel”对话框。同理，将student_score（学生成绩表）、student_course（学生课程表）数据加载到“Power Pivot for Excel”对话框，结果如图2-127所示。
图2-127 “Power Pivot for Excel”对话框
2）在“Power Pivot for Excel”对话框里单击“主页|查看|关系图视图”命令，然后选中student_score（学生成绩表）里的stuNo字段，右击选择“创建关系”选项，在“创建关系”对话框中“表1”的列中选择stuNo字段，“表2”的列也选择stuNo字段，单击“确定”按钮，这样两个表之间的关系就建立好了，如图2-128所示。同理“表2”的列选择CourseID字段，“表3”的列选择CourseID字段，然后建立关系。
图2-128 建立“表1”和“表2”之间的关系
3）在“Power Pivot for Excel”对话框里单击“主页|数据透视表|数据透视表”命令，在“创建数据透视表”对话框中单击“确定”按钮，会在新工作表中生成一张数据透视表，如图2-129所示。
图2-129 “创建数据透视表”对话框
4）在“数据透视表字段”对话框中，将科目名称字段（CourseName）拖到行区域，性别字段（sex）拖到列区域，成绩字段（Score）拖到值区域，然后修改Score的值汇总依据为“平均值”，最后选中整个数据透视表，右击选择“设置单元格格式”选项，选择“数字|数值”选项，调整小数位数的数值为2。通过以上步骤实现了不同性别、不同科目平均成绩的统计分析，结果如图2-130所示。
图2-130 不同性别学生对应的不同科目的平均成绩
7.数据透视表案例—字段去重计数
字段去重计数指的是将某个字段中的相同数值去重后统计个数。日常报表（日报、周报、月报等）中都需要实现对客户人数的统计，涉及人数的汇总统计，往往需要在不同维度层面上去除字段中的重复数据再进行计数。例如，每日消费人数指标，需要统计每天不同的消费用户ID的数量。每周、每月消费人数指标，需要分别从周、月的维度去统计不同的消费用户ID的数量。下面以超市销售明细表的数据为例，进行详细说明。
（1）案例分析
以某超市的销售明细表为例，数据如表2-21所示。字段包括日期、用户、商品名称、单价、数量、金额。要求统计超市每日消费人数、消费次数、消费金额。
表2-21 超市销售明细表
对于消费次数和消费金额的统计，可以用之前介绍的COUNTIFS、SUMIFS函数进行统计，但消费人数的统计需要去重计数。下面提供三种方法来统计每日消费人数：第一种方法是使用“数据”选项卡下的“删除重复值”命令来实现；第二种方法是使用数据透视表中“值汇总方式”下的“非重复计数”方法来实现；第三种方法是使用“Power Pivot”选项卡下的“新建度量值”的命令来实现。
（2）方法一
复制日期、用户两个字段数据到空白列，然后同时选中这两个字段的数据，单击“数据|数据工具|删除重复值”命令，这样不同日期下的用户仅出现一次。去重后的日期作为COUNTIFS函数的条件范围进行日期统计，结果如图2-131所示。
图2-131 用户人数统计（方法一）
方法一的公式与步骤如下：
● 统计消费人数的公式。
1）将A列和B列数据复制到H列和I列，然后单击“数据|数据工具|删除重复值”命令进行数据去重。
2）单元格L2内输入公式“=COUNTIFS(H:H,K2)”。
● 统计消费次数的公式。
单元格M2内输入公式“=COUNTIFS(A:A,K2)”。
● 统计消费金额的公式。
单元格N2内输入公式“=SUMIFS(F:F,A:A,K2)”。
（3）方法二
数据透视表可以很好地解决不同维度层面的人数去重计数的问题，在插入数据透视表进行汇总分析时，一定要先把数据源加载到数据模型中然后再进行计算。
方法二的操作步骤如下：
1）选择所有数据，单击“插入|表格|数据透视表”命令，在“创建数据透视表”对话框中选择放置数据透视表的位置为“现有工作表”的单元格I2，并勾选“将此数据添加到数据模型”，然后单击“确定”按钮，如图2-132所示。
图2-132 创建数据透视表
2）将日期字段拖到行区域，用户、商品名称、金额字段分别拖到数据透视表的值区域进行度量值统计，如图2-133所示。
图2-133 数据透视表字段
3）单击值区域里用户计数的度量值对应的下拉箭头，在“值字段设置”对话框中的“值汇总方式”选项卡里选择“非重复计数”，如图2-134所示，统计结果如图2-135所示。
图2-134 值字段设置
图2-135 指标统计结果
从统计结果上看，每日消费人数已经实现了去重计数，消费次数是对商品名称字段出现的行数计数，消费金额是对字段金额的求和，总计的消费人数也同样实现了去重计数。因此，在创建数据透视表的时候勾选“将此数据添加到数据模型”，可以实现字段的去重计数。
（4）方法三
使用Excel 2016版本里集成的Power Pivot功能来实现用户去重分析。使用Power Pivot功能，也需要将数据加载到数据模型后进行计算，计算方法参照上面的方法二。下面介绍的是利用Power Pivot功能中“新建度量值”方法，使用其内置的DISTINCTCOUNT函数来计算消费人数。
方法三的操作步骤如下：
1）在使用这个功能之前，需要先加载出“Power Pivot”选项卡。单击“开发工具|加载项|COM加载项”，在“COM加载项”对话框中勾选“Microsoft Power Pivot for Excel”选项，单击“确定”按钮，如图2-136所示。这样选项卡里面就会出现Power Pivot功能。
图2-136 加载“Power Pivot”选项卡
2）选择所有数据，单击“Power Pivot|表格|添加到数据模型”命令，在“创建表”对话框中勾选“我的表具有标题”选项，单击“确定”按钮，如图2-137所示，然后弹出“Power Pivot for Excel”对话框。
图2-137 创建表
3）切换到Excel数据源工作表，单击“Power Pivot|计算|度量值|新建度量值”命令，在“度量值”对话框中修改“度量值名称”为消费人数，“公式”里输入公式“=DISTINCTCOUNT('表1'[用户])”，然后单击“确定”按钮。此时，在“Power Pivot for Excel”对话框里面会显示刚才编写的度量值以及计算公式，如图2-138所示。
图2-138 新建度量值
4）切换到“Power Pivot for Excel”对话框，单击“主页|数据透视表|数据透视表”命令，在“创建数据透视表”对话框中选择“现有工作表”里的单元格I2位置，然后单击“确定”按钮，如图2-139所示。
图2-139 创建数据透视表
5）在“数据透视表字段”对话框中展开“表1”的所有字段，会出现新建的消费人数字段，然后将日期字段拖到行区域里面，消费人数、商品名称、金额字段分别拖到透视表的值区域里面进行度量值统计。利用Power Pivot新建度量值字段的方法，也可以实现消费人数去重计数，如图2-140所示。
图2-140 数据透视表字段
由此可见，使用“Power Pivot”选项卡下的“新建度量值”方法可以很好地解决字段去重计数。其实，此种方法的流程步骤可以进行简化，省去使用DISTINCTCOUNT函数来“新建度量值”的步骤。在数据加载到数据模型之后，可以直接在“Power Pivot for Excel”对话框中单击“数据透视表”命令，然后在数据源工作表中插入数据透视表来实现对消费人数的去重计数。