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