下载掌阅APP，畅读海量书库
立即打开
数据透视表具有强大的数据分析能力，它集合了数据排序、筛选、分类汇总等数据分析的优点，建立数据表之后，通过字段的设置可以瞬间得出各种不同的分析结果，以实现快速创建多维度的统计分析报表。另外，通过不同的值显示方式又可以达到多种不同的分析目的。
数据透视表所具有的统计能力靠语言的描述永远无法体现。这一节中我们给出几个实例，通过对源数据与统计结果的查看，可以了解数据透视表能达到哪些统计目的。
如图3-100所示，表格按日期显示了各车间的加班时长记录，建立数据透视表可以对不同技工类别的总加班时长进行统计，从而分析哪种技工类别是车间比较紧缺的，如图3-101所示。
图3-100
图3-101
图3-102所示表格为企业某次培训后的员工测试成绩表，表格数据涉及4个部门，现在想对各个部门的最高分、最低分、平均分进行统计。通过建立如图3-103所示的数据透视表即可快速达到统计目的。
图3-102
图3-103
如图3-104所示的表格中统计了公司某次考核中员工的相关数据。通过建立数据透视表可以快速统计出此次考核中各个学历层次的人数，如图3-105所示。另外，通过更改 “学历” 字段值的显示方式，还可以直观地看到各个学历层次的人数占总人数的比例情况，如图3-106所示。
图3-104
图3-105
图3-106
如图3-107所示表格为某月的工资统计表，下面需要按部门统计人数，并统计出各个部门的平均工资。通过建立数据透视表可得到想要的统计结果，如图3-108所示。
图3-107
图3-108
使用数据透视表分析数据，首先要学会建立基本数据透视表，然后再添加字段，获取分析结果。设置不同的字段将决定获取什么统计结果。
数据透视表是基于已经建立好的数据表的，基本上可以通过如下三步完成透视表的创建。
1 准备好数据表，如图3-109所示为一份销售记录单。
图3-109
2 选中数据表中的任意单元格，在 “插入” 选项卡的 “表格” 组中单击 “数据透视表” 按钮，打开 “创建数据透视表” 对话框，如图3-110所示。
3 在 “选择一个表或区域” 框中显示了当前要建立为数据透视表的数据源（默认情况下将整张数据表作为建立数据透视表的数据源），如图3-111所示。单击 “确定” 按钮，则可创建数据透视表（默认的数据透视表是空白状态，需要添加字段才能得出分析结果），如图3-112所示。
图3-110
图3-111
图3-112
数据透视表的功能虽然非常强大，但使用之前需要先规范数据源，否则会给后期创建和使用数据透视表带来重重阻碍，甚至无法创建数据透视表。规范数据源一般需要注意以下几个问题。
· 不能包含多层表头，即表格应具有完备的列标识。
· 列标识不能缺失。如果有遗漏列标识，那么在创建数据透视表时会弹出错误提示。
· 数据格式要规范。比如前面章节中介绍的文本数据不应包含空格、不能出现文本型数字等。
· 数据应具有连续性。一个完善的数据源如果中间使用空行中断，那么在创建数据透视表时，也不能正确识别完整的数据源。
在创建了数据透视表之后，默认是一个空表，要想得出各种分析结果，需要进行不同字段的设置。
在字段列表中选择字段，按住鼠标左键不放将其拖到下面的字段设置框中。例如将 “店铺” 拖动到 “行标签” 区域，如图3-113所示；将 “销售金额” 拖动到 “值” 区域，如图3-114所示。通过得到的分析表可以直观看到各个部门中各个商品的总数量与总金额，如图3-115所示。
图3-113
图3-114
图3-115
数据透视表的强大功能体现在字段的设置上，不同的字段组合可以获取不同的统计效果，因此可以随时调整字段位置，多角度地分析数据。下面仍然以如图3-109所示的销售记录单为例，介绍字段的设置及调整方法。这其中涉及字段位置的设置、顺序的调整等知识点。
例1：各类别商品销售数量及金额统计报表
利用如图3-109所示的数据源创建统计各类别商品销售数量及金额的报表。
1 选中数据源表格中的任意单元格，在 “插入” 选项卡的 “表格” 组中单击 “数据透视表” 按钮，如图3-116所示。
2 打开 “创建数据透视表” 对话框，保持默认选项，如图3-117所示。
3 单击 “确定” 按钮，即可在新工作表中创建数据透视表，如图3-118所示。
4 在字段列表中将光标指向 “系列” 字段，按住鼠标左键将其拖动至 “行” 区域中，然后按相同方法将 “销售数量”“销售金额” 字段拖动到 “值” 区域中，即可得到分析各系列商品销售情况的数据透视表，如图3-119所示。
5 在Sheet2工作表上单击鼠标右键，在弹出的快捷菜单中选择 “重命名” 命令，如图3-120所示。将工作表重命名为 “各系列商品销售统计” ，如图3-121所示。
图3-116
图3-117
图3-118
图3-119
图3-120
图3-121
创建数据透视表是为了进行分类统计，如果数据表一个分类都找不到，那么对其创建数据透视表是无任何意义的，所以数据源表格应该至少包含一个分类。
例如，如图3-122所示表格没有任何分类，这种表无论怎么统计还是这个结果；而图3-123所示表格，则可以按部门进行分类统计。
图3-122
图3-123
例2：各店铺的销售统计报表
利用如图3-109所示的数据源创建各店铺的销售统计报表。
1 按例1中的方法创建数据透视表。
2 拖动 “店铺” 字段和 “系列” 字段到 “行” 区域中，拖动 “销售金额” 字段到 “值” 区域中，即可得到各店铺的销售统计表，如图3-124所示。
图3-124
3 将工作表重命名为 “各店铺的销售统计报表” ，如图3-125所示。
图3-125
数据透视表是一个交互式报表，当设置双行标签时，还可以通过调节字段获取多种不同的统计结果。
在要调整的字段 “系列” 上右击，在右键菜单中单击 “上移” 或 “下移” 按钮，如图3-126所示。调整后的数据透视表的统计结果也自动发生变化，当前的侧重点在对各个系列总销售额的统计，但下面也对各个店铺的明细进行了统计，如图3-127所示。
图3-126
图3-127
例3：各销售员业绩统计报表
利用如图3-109所示的数据源创建各销售员业绩统计报表。
1 按例1中的方法创建数据透视表。
2 拖动 “销售员” 字段到 “行” 区域中，拖动 “销售金额” 字段到 “值” 区域中，即可得到各销售员业绩统计报表，如图3-128所示。
图3-128
3 按例1中的方法将工作表重命名为 “各销售员业绩统计报表” ，如图3-129所示。
图3-129
例4：各类别商品各月份销售统计报表
假设当前数据表中涉及多月的数据，还可以建立分月统计的报表。
1 此处假设销售记录表中有8月数据与9月数据，如图3-130所示。按例1中的方法创建数据透视表。
图3-130
2 拖动 “系列” 字段和 “日期” 字段到 “行” 区域中，拖动 “销售金额” 字段到 “值” 区域中，即可让各个系列商品的销售额按月份统计，如图3-131所示。
图3-131
添加日期字段后，可以看到当前统计表中会多出一个 “月” 字段，这是自动生成的，因为当前数据涉及多月，所以会自动进行分组统计。如果数据涉及多年，则还会自动添加 “年” 字段，即能按年自动分组，方便查看数据的统计结果。
例5：筛选查看指定店铺的统计报表
在数据透视表中还可以将字段添加到 “筛选” 区域中，实现只筛选统计部分数据。例如，将 “店铺” 字段添加到 “筛选” 区域，可以筛选查看某一个店铺的统计数据。
1 按例4中的方法创建数据透视表，并且字段保持相同的设置。
2 将 “店铺” 字段拖入 “筛选” 区域中，得到的统计结果如图3-132所示。
图3-132
3 单击筛选字段右侧的下拉按钮，在下拉列表中选择 “港汇店” （如图3-133所示），单击 “确定” 按钮得到的是 “港汇店” 的统计结果，如图3-134所示。
图3-133
图3-134
4 单击筛选字段右侧的下拉按钮，在下拉列表中选择 “万达店” （如图3-135所示），单击 “确定” 按钮得到的是 “万达店” 的统计结果，如图3-136所示。
图3-135
图3-136
数据透视表对数值字段默认的汇总方式为求和，对文本字段的默认汇总方式为计数。当默认的汇总结果不是需要的统计结果时，可以重新更改汇总方式。例如，图3-137所示数据透视表的目的是要统计各个工龄段的人数，而当添字段后，默认的汇总方式是对工龄进行求和，因此达不到统计目的。
图3-137
1 在数据透视表中选中汇总项下的任意单元格，在 “数据透视表” → “分析” 选项卡的 “活动字段” 组中单击 “字段设置” 按钮，打开 “值字段设置” 对话框，如图3-138所示。
图3-138
2 在 “计算类型” 列表框中选择 “计数” 汇总方式，并在 “自定义名称” 框中输入更加直观的名称，如图3-139所示。更改计数方式后，可以看到统计出的各个年龄段的人数，如图3-140所示。
图3-139
图3-140
将数值字段添加到 “值” 字段框中时，默认的汇总方式为求和。求和只是其中的一种显示方式，除此之外，还有占总计的百分比、占行汇总的百分比、占父行汇总的百分比等显式方式。
例如，在如图3-141所示的数据透视表中统计了各个部门的总销售额，现在要求显示各个部门的销售额占总销售额的百分比。
图3-141
1 选中列字段下的任意单元格，单击鼠标右键，在弹出的快捷菜单中依次选择 “值显示方式” → “总计的百分比” 命令，如图3-142所示。
图3-142
2 按上述操作完成设置后，即可看到各部门的销售额占总销售额的百分比，效果如图3-143所示。
图3-143
在有列标签的数据透视表中，可以设置值的显示方式为占行汇总的百分比。在此显示方式下横向观察报表，可以看到各个项所占百分比情况。如图3-144所示的数据透视表为默认统计结果，现在需要查看各部门中男女的比例情况。
图3-144
1 选中列字段下的任意单元格，单击鼠标右键，在弹出的快捷菜单中依次选择 “值显示方式” → “行汇总的百分比” 命令，如图3-145所示。
图3-145
如果当前数据透视表设置了双行标签或双列标签，也可以设置 “父行汇总的百分比”“父列汇总的百分比” 显示方式。
2 按上述操作完成设置后，即可看到各个部门中男女所占比例。例如， “销售部” 中男性占37.5%，女性占62.5%，如图3-146所示。
图3-146
如果设置了双行标签，则可以设置值的显示方式为占父行汇总的百分比。在此显示方式下可以看到父级下的各个类别的百分比。如图3-147所示的数据透视表为默认统计结果，通过设置 “占父行汇总的百分比” 显示方式，可以直观地看到在每个月份中每一种支出项目所占的百分比情况，如图3-148所示。
图3-147
图3-148
1 选中列字段下的任意单元格，单击鼠标右键，在弹出的快捷菜单中依次选择 “值显示方式” → “父行汇总的百分比” 命令，如图3-149所示。
图3-149
2 按上述操作完成设置后，即可看到每个月份下各个不同的支出项目所占的百分比，同时也显示出一季度中各个月份支出额占总支出额的百分比。
数据透视表建立后，由于数据性质的不同，有时统计结果会比较分散，不便于分析查看，这时就需要对统计结果进行分组，从而获取某一类数据的统计结果。在统计学中经常需要对数据进行分组统计。
例如，在本例数据透视表中统计了58份有效调查问卷的打分情况（如图3-150所示），这样的统计结果很分散。如果对打分的分值按分数段显示，就可直观地查看各分数段的人数。
图3-150
1 选中 “打分” 字段下的任意项，在 “数据透视表工具” → “分析” 选项卡的 “组合” 组中单击 “分组选择” 按钮，如图3-151所示。
图3-151
2 打开 “组合” 对话框，这里想以5分为一组，所以设置步长为 “5” ， “起始于” 和 “终止于” 这两个值程序会根据当前的最大值与最小值自动生成，如图3-152所示。
3 单击 “确定” 按钮，此时即可看到在按各个分数段进行人数分组汇总统计，效果如图3-153所示。
图3-152
图3-153
对数值执行自动分组时，只能按统一的步长去分组。如果想很自由地按分析目的去分组，则可以进行手动分组。例如，如图3-154所示的数据表记录了各省市旅行社的统计数量（篇幅限制，只显示部分记录），最少的有65个，最多的有443个，变化幅度很大，现在想将数据分为100以下、100~200、200~300以及300以上4个区间。
图3-154
1 选中表格任意单元格区域，创建数据透视表，并设置 “三星以上商务酒店” 字段为行标签，设置 “城市名” 字段为值标签，如图3-155所示。
图3-155
2 在行标签下选中100以内的数字，在 “数据透视表工具” → “分析” 选项卡的 “组合” 组中单击 “分组选择” 命令（如图3-156所示），即可将选中的数据分为一个组，如图3-157所示。
图3-156
图3-157
3 接着选中100~200的数据，在 “数据透视表工具” → “分析” 选项卡的 “组合” 组中单击 “分组选择” 命令（如图3-158所示）进行分组。
图3-158
4 按相同的方法将200~300、300以上的数字进行分组，分组后的数据透视表如图3-159所示。
图3-159
5 选中 “数据组1” 单元格，重新输入名称为 “<100” ，如图3-160所示。
图3-160
6 按相同的方法将 “数据组2” 、 “数据组3” 等全部进行重命名，如图3-161所示。
图3-161
7 在字段列表中取消选中 “三星以上商务酒店” 复选框，统计结果如图3-162所示。
图3-162
8 接着在A3单元格更改列标识为 “三星以上商务酒店” ，将B3单元格的标识更改为 “数量” ，得到的统计报表如图3-163所示。
图3-163
分组的目的是让同一类或同一阶段的数据进行归类，从而得到归类后的结果。因此，即使是自定义分组，也建议找寻一定的规律，不能毫无规律地随意分类。随意分类会让统计结果失去意义。
数据透视表是一种统计报表，对于这种统计结果，很多时候都需要复制到其他的地方使用。因此在得到统计结果后可以将其转换为普通表格，方便使用。
方法是：选中整张数据透视表，按Ctrl+C组合键复制，在 “开始” 选项卡的 “剪贴板” 组中单击 “粘贴” 下拉按钮，在弹出的下拉菜单中单击 “值和源格式” 按钮（如图3-164所示），即可将数据透视表中当前数据转换为普通表格，如图3-165所示。
图3-164
图3-165