五、数据透视表的组合功能：快速编制月报、季报、年报
我们使用数据透视表进行统计分析时，它默认以字段下的每个唯一值作为统计依据。比如：示例文件“表3-13 数据透视表”中的产品类别、片区类别，如果将订单日期作为行标签，它会默认以合同台账中的每一个日期作为分类依据。这种设置大部分情况下能满足我们的需求，但有时也存在例外。比如，我们很少需要统计每天的订单数据，而是按月、季、年进行统计。这种情况下，如果手工统计，工作量非常大，好在强大的Excel提供了组合的功能，完全可以解决我们的难题。下面通过实例来介绍组合功能。
制作产品按年、月统计的“合同统计月报”（见图3-65），具体操作如下。
图3-65 合同统计月报
Step1：打开示例文件“表3-13 数据透视表”，以“数据”表格为数据源，按图3-66的布局创建数据透视表。
图3-66 创建数据透视表1
Step2：选中数据透视表A列中的任一单元格，点击右键，选择“创建组”。在弹出的“分组”对话框中选择需要的步长。此步长设置可多选，比如可选择月、年，也可选择季度、年。我们按月和年进行统计。如图3-67所示。
图3-67 将日期按月和年进行组合
Step3：创建后的数据透视表自动添加了一个“年”的行
图3-68 将日期按年月组合后的数据透视表
至此，就做出了按年、月统计的报表。当然，我们还可按周来统计，假设按周统计是从周一到周日。其他步骤相同，只是在Step2中“步长”中选择“日”，将天数设为7，起始日期改为“2012/12/31”（因为2012年12月31日是周一，见图3-69）。
图3-69 将日报按周组合
除了Excel提供的自动组合功能，还可以手动组合：选定要组合的记录，点击右键，选择“创建组”。如图3-70所示。
图3-70 手动组合
手动组合时，不是连续区域的字段值也可组合在一起。以图3-70中的透视表为例，可以手工选定A产品、C产品、E产品，然后点击右键将其组合在一起。手动组合的优点是比较灵活，但如果项目较多时效率较低，并且新增项目时还需要重新组合。解决方案是：可考虑在数据源添加分组信息的辅助列，将源数据进行分组，然后再对包含辅助列的源数据进行数据透视。
六、数据透视表的显示方式：进行累计、环比、同比分析
数据透视表默认以“无计算”的方式显示。实际上，透视表提供了丰富的计算功能，我们可以通过设置值的显示方式来解决工作中统计报表的大部分计算需求，这些显示方式有：占某对象的百分比计算、与某对象的差异计算、按某字段进行汇总、按某字段汇总的百分比。下面通过如何进行累计、同比、环比分析来介绍透视表的显示方式功能。
1.利用透视表进行累计求和
累计求和要将各月的数据逐月累加。如果用函数公式来汇总报表可能是先计算出各月数据，然后逐月累加。在数据透视表中可以使用“值显示方式”的“按某一字段汇总”功能来实现累计求和。
Step1：打开示例文件“表3-14 数据透视表2”，以“数据”表格为数据源，按图3-71的布局创建数据透视表，注意：销售金额被拖了四次到数值区域。
图3-71 创建数据透视表2
Step2：将日期按月、年组合，并按经典数据透视表布局，具体操作参见本节第3点和第5点，如图3-72所示。
图3-72 将日期按年月组合
Step3：将数据透视表的数值变为行标签，即将C3单元格拖入B列和C列之间，改变透视表的结构，如图3-73所示。
图3-73 改变数据透视表布局
Step4：将行标签“年”拖入列标签（也可将图3-72中的A4单元格“年”拖入到D3单元格“汇总”的位置），改变后的布局如图3-74所示。
图3-74 将年拖为列标签
Step5：修改字段名称，分别将“求和项：销售金额”“求和项：销售金额2”“求和项：销售金额3”“求和项：销售金额4”改为“本月销售”“本年累计”“同比增长”“环比增长”。改变后的数据透视表如图3-75所示。
图3-75 修改数值字段名称
需要特别强调的是：在数据透视表中，数值字段的名称都是类似“求和项：销售金额”这种格式，无法删除“求和项：”而只保留“销售金额”。因为不允许修改后的字段名与已经存在的字段名重名，我们可以改为其他的名字，或在原字段名前插入一空格。
Step6：选中本年累计的行，点击右键→选择“值显示方式”→“其他选项”→在弹出的“值字段设置”对话框中，值显示方式设为“按某一字段汇总”，基本字段设为“订单日期”，然后点击“确定”。如图3-76所示。
Step7：按以上操作后，透视表如图3-77所示。
图3-76 设置本年累计字段的汇总方式
图3-77 完成本年累计汇总方式设置后的数据透视表
2.利用透视表进行同比分析
同比分析就是将本月数据与上年同月相比，看其增减值及变动率。假设本案例是进行同比增长率分析，那么按如下步骤操作。
图3-78 进行同比增长设置
Step1：按“利用透视表进行累计求和”的Step1～Step5操作。
Step2：选择同比增长的行，点击右键→选择“值显示方式”→“其他选项”→在弹出的“值字段设置”对话框中，值显示方式设为“差异百分比”，基本字段设为“年”，基本项设为“上一个”，然后点击“确定”，如图3-78所示。
如果不是求增长率，而是求增长额，那么值显示方式改为“差异”，其他操作和设置不变。
如果要进行定基分析，则选择“基本项”时选择某一基期即可，比如选择“2013年”。
3.利用透视表进行环比分析
环比分析是将本月数据与上月相比，看其增减值及变动率，具体操作如下。
Step1：按“利用透视表进行累计求和”的Step1～Step5操作。
Step2：选择环比增长的行，点击右键→选择“值显示方式”→“其他选项”→在弹出的“值字段设置”对话框，值显示方式设为“差异百分比”，基本字段设为“销售日期”，基本项设为“上一个”，然后点击“确定”，如图3-79所示。
图3-79 环比增长分析设置
七、数据透视表的切片器
切片器实际上就是筛选器，它包含一组按钮，使你能够快速地筛选数据透视表中的数据，而无须打开下拉列表以查找要筛选的项目。它的主要功能就是筛选，只是比筛选更方便、更直观。在早期Excel的版本中，可以使用报表筛选器来筛选数据透视表中的数据，但在对多个项目进行筛选时，很难看到当前的筛选状态。在Excel2010中，可以选择使用切片器来筛选数据。单击切片器提供的按钮可以筛选数据透视表数据。除了快速筛选之外，切片器还会指示当前筛选状态，从而便于我们轻松、准确地了解已筛选的数据透视表中所显示的内容。创建方法如下。
Step1：单击数据透视表中的任意位置。将显示“数据透视表工具”，同时添加【选项】和【设计】选项卡。
Step2：在【选项】选项卡上的“排序和筛选”组中，单击“插入切片器”。
Step3：在“插入切片器”对话框中，选中你要为其创建切片器的数据透视表字段的复选框。单击“确定”后将为选中的每一个字段显示一个切片器，如图3-80所示。
图3-80 建立切片器
Step4：在每个切片器中，单击要筛选的项目。若要选择多个项目，请按住【Ctrl】键，然后单击要筛选的项目。
八、利用透视表汇总多个工作表的数据
在第一章中我们介绍过，为了便于统计分析，清单型表格应该在同一张表格中登记，而不能分拆成多张工作表。比如合同登记台账，应该在一张工作表中登记，而不是按月、按年、按部门分别在不同的工作表登记。但是一些报表类的表格就需要分表填列，比如各公司的管理费用明细表、各月的销售统计报表等。如果要汇总这些数据，可以使用合并计算和数据透视表，相比而言使用透视表更有优势。因为透视表有一个分页字段功能，可以直接在汇总表筛选查看各公司的数据组成。另外，如果分表填列的是清单型表格，使用合并计算就不能满足需求。因而，要汇总多个工作表时数据透视表更适用。下面以案例形式介绍具体操作。
示例文件“表3-15 合并同一工作簿多张工作表”中，A公司、B公司、C公司三张工作表中分别登记各公司的销售数据，如图3-81所示。
图3-81 三个公司的销售统计表
Step1：打开示例文件“表3-15 合并同一工作簿多张工作表”，点击快速访问栏中的数据透视表向导按钮（添加此按钮的方法参见本节第二点“如何创建数据透视表”），或按【Alt】键，然后分别按D键、P键，打开数据透视表和数据透视图向导，选择“多重合并计算数据区域”，如图3-82所示，然后点击“下一步”。
Step2：在步骤2a对话框中，选择指定所需的页字段数目栏下的“创建单页字段”，如图3-83所示，然后点击下一步。
图3-82 创建多重合并计算数据区域的透视表
图3-83 创建单页字段
Step3：在步骤2b对话框中，点击压缩对话框按钮，选中需要合并的表格“A公司”A3:C13单元格区域（注意不要包含“合计行”），然后点击“添加”，如图3-84所示。
图3-84 添加A公司数据区域
Step4：重复Step3的操作，分别添加表格“B公司”的A3:E15单元格区域、表格“C公司”的A3:D12单元格区域，如图3-85所示。然后点击下一步，选择在新工作表创建数据透视表，最后点击“完成”。
Step5：创建后的数据透视表如图3-86所示。
Step6：将A4单元格“行标签”改为“月份”，“列标签”改为“水果类别”。此时B1单元格页字段的值分别为“项1”“项2”“项3”。分别筛选各页字段的值，筛选后在B1单元格分别将其修改为“A公司”“B公司”“C公司”。
图3-85 分别添加B公司、C公司数据区域
图3-86 合并三表格后的数据透视表
Step7：我们发现月份的先后顺序不正确，10～12月排在最前面，选定第5～7行，手工将其拖动至9月之后。
至此，汇总同一工作簿下多工作表数据的单页多重合并计算数据透视表创建完成。
另外，数据透视表可创建多页多重合并计算数据透视表，还可合并多个工作簿多个工作表的数据，由于本书篇幅所限，不进行介绍。
网友评论