我们使用数据透视表进行统计分析时，它默认以字段下的每个唯一值作为统计依据。比如：示例文件“表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月之后。
至此，汇总同一工作簿下多工作表数据的单页多重合并计算数据透视表创建完成。
另外，数据透视表可创建多页多重合并计算数据透视表，还可合并多个工作簿多个工作表的数据，由于本书篇幅所限，不进行介绍。
(一)数据透视表是什么？
数据透视表是可快速合并和比较大量数据的交互式的统计分析神器，是所有人最想学的Excel技巧，没有之一！
（二）数据透视表的特点
简单易 *** 作
交互性强
扩展性强
需要系统性学习
（三）创建数据透视表四步走
点击任意单元格
插入—数据透视表
选择数据区域和放置数据透视表位置
拖动显示字段
（四）数据透视表的结构
（五）职场新手创建数据透视表经常遇到的几个问题点及解决措施
Q1:标题行有空字段
R1:空字段填充完整
Q2:标题行有相同标题字段
R2:相同字段增加编号
Q3:区域中存在合并单元格
R3:取消合并单元格——取消合并单元格—定位空值—=上一单元格—Ctrl+Enter
Q4:区域中存在空行
R4:删除空行——定位—右键—删除整行
Q5:区域中存在文本型数字，无法计算
R5:将文本型数字转换成数值——分列
（六）数据透视表做得好是种怎样的体验？
大神们的数据透视表是这样的（大神制作1），这样的（大神制作2），还有这样的（大神制作3）
怎么样？你的数据透视表是什么样的？看到大神们的数据透视表，有没有一种尖叫的冲动呢？不要羡慕，我们都可以的!
二、数据透视表布局及美化
（一）综合应用
A 创建数据透视表：选中区域—插入数据透视表
B 右键—选项—显示—勾选经典数据透视表
C 拖动字段到透视表
D 美化
（1）合并
Way1:选中数据透视表—设计—报表布局—重复所有项目标签
Way2:右键—数据透视表选项—布局和格式—合并且居中合并
（2）降序排列
Way1:点击金额列—右键—排序—降序排列（只能对应销售员降序排序）
Way2:选中地区—其它排序—降序—金额（可以对地区、销售员分别降序排列）
（3）数据透视表样式
选中数据透视表—设计—选择已有或新建数据透视表样式
（4）修改数字格式
数据—右键—数字格式—保留0位小数—勾选千分位
（5）批量删除求和项
Ctrl+H—将 "求和项:" 替换为 空格 —批量替换
（6）汇总行标颜色：
点击小计行上方—向下黑色箭头—默认选中所有小计行—填充颜色
（7）增加边框：
添加边框选项—所有框线
（8）选项取消+、-按钮：
选中数据透视表—分析—+/-按钮选项
（9）取消数据刷新时自动调整列宽
选项—布局—取消勾选自动调整列宽
（二）日期组合
点击销售日期任意单元格—右键—创建组—选择分组依据—步长为年、季度、月
（三）价格组合
（1）利用VLOOKUP进行模糊查询，手动计算
（2）利用数据透视表—点击单价任意单元格—创建—起始值、终止值、步长
注意：日期数据必须全部是日期格式，不能有其它的文本和空白的单元格
（四） 插入公式 —均价分析
选中透视表—分析—字段、项目和集—计算字段—平均单价—设置公式=金额/数量—当数据源发生变化时，刷新后平均单价可以自动计算
（三）数据透视高级应用
A. 强大的“值显示方式”——自动计算占比和排名
*** 作步骤：
（1）占比
选中数据透视表—将需要计算占比字段再次拖至数值区域—选中该列—右键—“值显示方式”—“总计的百分比”—更改标题字段名称为“···占比”
（2）排名
选中数据透视表—将需要计算占比字段再次拖至数值区域—选中该列—右键—“值显示方式”—降序排列—“销售员”—更改标题字段名称为“···排名”
实例效果图：
B. 条件格式——扮靓报表
*** 作步骤：
选中数据透视表需设置条件格式的某列—开始—条件格式—按需设置（与常规条件格式设置无异）
C. 切片器——无所不能
*** 作步骤：
分别为多个数据透视表设置不同的名称—插入—“切片器”—选中切片器—选项—列—按切片器筛选字段设置x列—选项—“报表连接”—勾选需要相互联动的数据透视表的名称
D. 动态数据透视图——让你的图表动起来
E. 高级应用——多表合并计算
难度较大，不会，不做讲解······
持续更新中......
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）