数据透视表进阶应用-Word、Excel、PPT高级应用

仅仅按上一节对数据透视表进行的简单设置所获得的分析统计表，就已经可以充分展现出数据透视表的强大与灵活性了。而我们在这里，将继续介绍有关数据透视表深度使用的相关内容。

1．多层次的分组

将各个分组字段拖入“数据透视表字段”浮动窗格中的“行”或者“列”列表框中，可以获得各种不同维度的动态统计表，展示业务的开展情况。

数据透视表中的“行标签”“列标签”标题可以提供对行或者列的筛选功能。

字段在“行”列表框或者“列”列表框中的次序决定了分组的先后，处于高一级的字段为高一级的分组，处于低一级的字段自动为低一级的分组。例如，我们先将“门店” 拖放到“行”列表框中，再将“商品”拖放进去，即可获得以“门店”分组的商品销售统计表格，这一统计包含了两个层次。数据透视表字段设置如图17-40左图所示，而统计结果如图17-40右图所示。

图17-40　多层次分类显示的数据透视表

2．数据透视表的“分析”和“设计”子选项卡

数据透视表是如此强大，以至于Excel专门为其提供了“分析”和“设计”子选项卡。如图17-41所示。

图17-41　数据透视表的“分析”和“设计”选项卡

在“数据透视表工具—分析”选项卡中的“数据透视表”组中可以修改数据透视表的名称、选项，但一般不必修改。

“数据透视表工具—分析”选项卡的“活动字段” 组中的“活动字段”输入框指示当前选中单元格所属的字段。单击下方的“字段设置”按钮，弹出“字段设置”对话框，如果选中的是值字段，则会弹出“值字段设置”对话框，如图17-42所示，在其中可以对字段自定义名称，也可以修改计算类型。

“活动字段”组中“向下钻取”和“向上钻取”按钮是在建立了数据模型的情况下分析不同级别数据的详细信息的命令。所以，在通常的数据透视表中不能使用。

图17-42　“值字段设置”对话框

当在数据透视表中选中已经被分组的“行”字段或者“列”字段时，可以通过单击“活动字段”组中的“展开字段”或“折叠字段”按钮来进行展开或者折叠，这是展开或者折叠所有选中层次的命令。单击分类数据（例如各个门店或各个月度）前的展开按钮“ ”或折叠按钮“ ”，则会展开或折叠分组。

图17-43　数据透视表的再次组合(www.xing528.com)

使用“数据透视表工具—分析”选项卡的“分组”组中的“分组选择”功能，可以对数据透视表中被选中的数据或被选中的列进行进一步的分组。例如，对图17-40右图所示的数据透视表，选中前三个门店的所在行。单击“分组选择”按钮，则对其再次进行了组合。如果按列选中三个月度（包括其下属的数据），单击“分组选择” 按钮，弹出如图17-43左图所示的“组合”对话框，这是一个设置专门对时间自动进行组合的功能参数的对话框，默认选中了“日”“月”选项，再单击“季度”选项。然后，单击“确定”按钮，则对时间再次进行组合，生成按季度组合的新分组，结果如图17-43右图所示。

● Excel对时间字段提供的优化组合方式是按任意设定的天数进行组合，如果选中日期字段，即可单击“组字段”按钮，打开类似图17-43左图所示的“组合”对话框。然后，在只选择“天”选项时，设定天数，例如7天。单击“确定”按钮后，即对日期按设定天数进行组合统计。鉴于篇幅关系，在此不再图示说明。

还可以点击“数据透视表工具—分析”选项卡的“筛选” 组的“插入切片器”“插入日程表”等按钮，获得对数据透视表更多的筛选效果。如图17-44所示。

图17-44　利用切片器和日程表对数据透视表的筛选

如果利用外部数据生成透视表，则可通过“数据透视表工具—分析”选项卡的“数据” 组中的功能来刷新数据或者更改数据源。

可以通过“数据透视表工具—分析”选项卡的“操作”组中的功能来清除或者移动数据透视表。

数据透视表一个重要的功能即增加字段。单击“数据透视表工具—分析”选项卡—“计算”组—“字段、项目和集”下拉按钮，在下拉列表中选择“计算字段”选项，弹出“插入计算字段”对话框，在其中输入新的字段名称、公式即可。在其中输入公式时，公式中的变量可以通过双击“字段”列表中的某个字段获得。这样，即建立了二次计算某个新字段的设置。例如，创建“销售成本”字段，公式为“=销售金额*0.005”，如图17-45左图所示。单击“确定”按钮后，“数据透视表字段”浮动窗格的字段列表中即添加了新创建的字段，将此字段拖放到数据透视表的“值”字段列表中，数据透视表中即添加了新创建的字段，并按照数据透视表的分组进行计算，分类合计出了相关数据。如图17-45右图所示。

图17-45　创建新的计算字段和数据透视表效果

其他OLAP工具需要创建联机数据源，鉴于篇幅关系，在此不作介绍。

在“数据透视表工具—分析”选项卡的“显示”组中，“字段列表”按钮控制了“数据透视表字段”浮动窗格的显示与隐藏，“+/- 按钮”控制了数据透视表中是否显示“展开”与“折叠”按钮，而“字段标题”按钮控制了是否显示可以提供行/列筛选的“行标签”或“列标签”。

3．数据透视表的设计

在数据透视表的“数据透视表工具—设计”选项卡中，可以通过“布局”组中的各种设置改变数据透视表的显示方式。图17-46左图所示的数据透视表即在“布局”组的“分类汇总”按钮下拉列表中选择“在组的底部显示所有分类汇总”，并在“布局”组的“空行”按钮下拉列表中选择“在每个项目后插入空行”的效果。另外，可以为数据透视表选择某种表格样式，以获得不同的显示效果。

最后，如果需要删除或者移动数据透视表中的字段，可以单击“数据透视表字段”浮动窗格中各子列表中的字段右侧的下拉按钮进行设置，也可直接在字段上单击鼠标右键，在下拉菜单中进行选择，如图17-46右图所示。

图17-46　改变布局模式后的数据透视表

本节所示的分析表参见本书提供的样例文档“连锁销售—数据透视表.xlsx”。