Excel数据透视表完全剖析.docx
《Excel数据透视表完全剖析.docx》由会员分享，可在线阅读，更多相关《Excel数据透视表完全剖析.docx（37页珍藏版）》请在冰豆网上搜索。
Excel数据透视表完全剖析
第2章创建基本数据透视表
1创建基本的数据透视表
方法1：
（1）单击数据源中的任意一个单元格（好处：
在默认情况下，这样可以确保数据透视表能够捕获数据源的范围。
（2）插入――――表格―――数据透视表
方法2：
ALT＋N＋V＋T
1.1添加字段到报表中
数据透视表字段列表中的四个区域分别是：
（1）报表筛选――添加字段到报表筛选区可以使该字段包含在数据透视表的筛选区域中，以便对其独特的数据项进行筛选
（2）列标签――添加一个字段到列标签区域可以在数据透视表顶部显示来自该字段的独特的值。
（3）行标签――添加一个字段到行标签区域可以沿数据透视表左边的整个区域显示来自该字段的独特的值
（4）数值―――添加一个字段“数值”区域，可以使该字段包含在数据透视表的值区域中，并使用该字段中的值进行指定的计算
在开始将各个字段拖放到各个区域中之前，首先询问自己两个问题：
对什么进行度量，如何查看它？
第一个问题的答案告诉我们需要对数据源中的哪些字段进行处理，第二个问题的答案告诉我们将这些字段放在什么位置？
Excel如何知道字段应该去什么地放？
正如我们刚刚经历的，使用新的“数据透视表字段表”界面，简单地选中某一字段名即可将字段添加到数据透视表中。
Excel自动添加选中的字段到数据透视表中。
问题是Excel如何知道将选中的字段放在哪个区域。
答案是Excel并不是真正地知道使用哪个区域，它根据数据类型。
如果数据类型是数字，Excel就把该字段放到“∑数值”区域；否则，Excel把这个字段放到行标签区域中，这样放置明显可以强调各字段指定正确的数据类型的重要性。
※注意字段中的值。
如果是在值字段中有一个空单元格，Excel将把该字段读作文本字段。
1.2增加数据透视表的层次
1.3重新排列数据透视表
1.4创建报表筛选视
通常，可能会要求我们生成某一具体地区，不是为每种可能的分析案分别单独的数据透视表，而是可以使用筛选字段建立报表筛选哭。
1.5与数据源保持一致
随着时间的推移，数据也可能发生变化，并且增加新的行和列，然而，数据透视表报表所依赖的数据透视表缓存与数据源没有连接，因而不能反应对数据源所进行的修改，直到再一次更新数据源。
通过再次更新数据源更新透视表缓存的行为称为刷新数据。
有两个原因让我们必须更新数据透视表报表：
1.5.1对现有数据已经进行修改
更新方法：
简单地在数据透视表报表中单右击，并选择“刷新”。
1.5.2数据源的范围已经随着行或列的增加而扩大
更新方法：
在数据透视表内任何地方单击鼠标右键，然后，选择“数据透视表工具”选项卡上的“选项”。
由此选择“更新数据源”。
这一选择会引发如下图所示对话框。
2.6共享数据透视表缓存（共享数据透视表的副作用）
很多时候，我们可能必须以多种不同的方法分析相同数据集，这就要求我们使用相同的数据源创建单独的数据透视表，但是，当我们每次创建数据透视表时，都要在数据透视表缓存中存储整个数据副本。
所创建的每个数据透视表缓存都会增加内存用量和文件大小，由于文件大小增加了，所以我们应该考虑共享数据透视表缓存，只有这样才能提高内存和文件空间的利用率。
通过复制和粘贴的方法很容易解决这些潜在的问题，确实如此，通过简单的复制数据透视表并粘贴到任何地方，可以创建另一个数据透视表，而不用复制数据透视表缓存，这样可以链接许多相同数据透视表缓存，而忽略内存和文件大小增加。
共享数据透视表的副作用
假设有两个数据透视表使用相同的数据透视表缓存某些操作可能会影响两个数据透视表包括如下几种：
（1）刷新数据―――不能只刷新一个数据透视表而不刷新另一个，刷新影响两个数据透视表。
（2）增加计算字段――如果在一个数据透视表中创建一个计算字段，新创建的计算字段也会出现在另一个数据透视表的字段列表中。
（3）增加计算项――如果三个数据透视表创建一个计算项，该项也会在另一个数据透视表中出现
（4）组合字段或者取消字段组合――所进行的所有组合或者取消
虽然这些副作用从共享数据透视表缓存的概念上看都不是致命的缺点，但在决定是否使用数据透视表作为数据源是目前情况的最佳选项时，记住它们很重要
2.7利用新的数据透视表工具节约时间
2.7.1推迟布局更新
根据大型数据源建立数据透视表最困难的部分是每次添加字段到数据透视表区域中，Excel处理数据时，我们都必须一直等待。
在Excel2007缓解了这样的问题，可以将布局变化一直推迟到准备好才应用，我们可以激活这个选项，单击“数据透视表”对话框中相对不太惹人注意的“推迟布局更新”复选框，可以激活这一选项，如下图
请注意:
我们在结束数据透视表时，记住要取消选中“推迟布局”复选框。
在数据透视表中一直选中它，可能导致数据透视表一直停留在人工更新状态，阻止数据透视表的其他特性（即，排序，筛选，分级等）
2.7.2利用一次单击从头开始
通常，在处理数据透视表布局时，可能从头开始，那么在不删除数据透视表缓存的情况下，本质上从头再来，选择“数据透视表工具”选项卡上的“选项”并选择“清除”下拉列表
2.7.3重新定位数据视表
把数据透视表移到其他地放的方法：
选择“数据透视表工具”选项卡上的“选项”并选择“移动数据透视表”按钮。
这个图标将激活“移动数据透视表”对话框，如下图
3.1.3用0代替空值
良好的电子表格设计要素之一是在工作表的数值部分永远不要遗留空单元格。
对于数据透视表中的空格一般用“0”来填充，方法：
（1）在数据透视表内选择一个单元格
（2）在“选项”选项卡上，选择“数据透视表选项”组中“选项”图标，显示“数据透视表选项”对话框。
（3）在“布局和格式”选项卡上，在“格式”部分，在标记为“对于空单元格，显示”字段后输入0
（4）单击“确定”接受所做的修改
3.1.4修改字段名称
最终的数据秀透视表中的每个字段都有一个名称，列和筛选区域中的字段从源数据的标题继承其名称，数据部分中的字段会被赋予“求和项：
××”这样的名称。
我们可能会用“总计”来代替默认名称，方法：
右击数据透视表的数值区中的某个数值并选择“值字段设置”，在“自定义名称”输入一个新名称，如果输入的是数据透视表中已有的名称，那么命名会失败，我们可以在命名字段的结尾处增加一个空格。
Excel认为没有空格的和有空格的不同，因为这种变化是修饰，所以你的电子表格的读者不会请注意到该名称后面的空格
3.2改变布局
Excel2007提供了3种布局样式，新样式“压缩布局”可能是用户的数据透视表的默认样式
在“设计”选项卡的“布局”组中可以控制布局改变。
该组有4个菜单:
分类汇总―――将分类汇总移到组的顶部或底部，或关闭分类汇总
总计―――――打开或关闭行和列的总计
报表布局―――使用压缩、大纲或者表格形式
空行―――――在每上组后面插入空行，或者删除每一组后的空行
3.2.1使用新的压缩形式布局
默认情况下，所有新的数据透视表都使用压缩布局。
在这种布局中，行区域中的多个字段堆叠在列A中。
压缩形式适合使用“展开”和“折叠”图标，
3.2.2使用大纲形式的布局
以大纲形式显示时，Excel用最外层的行字段填充列A，其他的字段占据B、C等。
如果计划将数据透视表的数值复制到新的位置，这种布局比较合适，虽然压缩的形式的布局能够提供将多个字段挤压到一列中的巧妙方法，但对于以全重新使用数据并不理想。
默认情况下，压缩和大纲布局会将汇总显示在每组的顶部，可以使用“设计”选项卡上的“分类汇总”下拉选项将分类汇总移到每底部，
3.2.3使用传统的表格形式布局
使用分类汇总从不会出现在组的项部。
如果希望在后继分析中使用得到的汇总数据，表格形式的布局也许是最好的布局。
3.3案例：
3.4利用样式和主题自定义数据透视表的外观
3.4.1自定义样式
方法:
（1）在“数据透视表样式”样式库中，找到现有的一种样式，右击选择“复制”，Excel将显示“修改数据透视表快速样式”对话框
（2）为该样式选择一个新名称。
（3）在“元素”列表中，单击“第一行条纹”。
一个新的、称为“条纹尺寸”的部分会出现在对话框中。
（4）重复第二步，完成后，单击确定
3.4.2修改未来数据透视表的默认样式
我们可以控制计算机未来的数据透视表使用哪种样式作为默认样式，默认的样式可以是内置的样式之一或是修改的表的自定义样式。
。
在“设计”选项卡的“数据透视表样式”样式库中，右键单击该样式并选择“设为默认样式”
3.4.3用文档主题修改样式
打开“页面布局”选项卡中的“主题”下拉列表。
当我们在主题下拉列表上来回移动鼠标光标时，“即时预览”将向我们展示数据表的颜色和字体。
要选择某一主题，单击它。
某些主题有临时字体，使用“页面布局”选项卡上“主题”组中的“颜色”下拉菜单可以对新主题颜色而不改变其字体
3.5修改汇总计算
3.5.1了解空单元格会导致计数的原因
如果某列中的所有单元格都含有数值数据，会选择求和，但如果只有1个单元格为空格单元格或者含有文本，Excel就会选择计数
在Excel2007中，在单击“数据透视表字段列表”的“字段”部分数值字段（含有空格或文本）中时，会出现的情况是：
如果Excel将数值字段（含有空格或文本）移动到“行标签”区域，，就要知道Excel将数值字段（含有空格或文本）看成文本而不是数值；在拖钢产量字段到“数值”区域时要特别警惕，如果某一计算值看起来地小时，就要检查一直该字段的名称是不是用“计数”代替“求和”，解决的方法是：
双击“计算”，激活“值字段设置”对话框，然后将“值字段汇总方式”由“计数”改为“求和”，就OK了。
3.5.2使用除计数和求和之外的其他函数
在Excel2007中，在单击“数据透视表字段列表”的“汇总方式”部分提供了9种函数：
（1）求和
（2）计数:
对所有单元格进行计数，包括数值、文本和错误的单元格。
等价于counta函数
（3）最大值
（4）最小值
（5）平均值
（6）剩积
（7）数值计数：
只计算数值单元格的个数等价于count函数
（8）标准偏差和总体标准偏差――计算标准偏差
（9）方差和总体标准方差――――计算统计方差
3.6添加和删除分类汇总
报表汇总是数据透视表的基本特性，有时，我们可能要禁止分类汇总显示，而有时又需要显示各字段的多种分类汇总结果
3.6.1有许多行字段时，禁止分类汇总
方法：
为了删除某一字段的分类汇总，可以单击“数据透视表字段列表”的区域节中的那个字段，选择“字段设置”对话框中选择“分类汇总”下面的“无”如下图：
3.6.2为一个字段添加多种分类汇总
方法：
为一个字段添加多种分类汇总，可以单击“数据透视表字段列表”的区域节中的那个字段，选择“字段设置”对话框中选择“分类汇总”下面的“无”如下图：
3.7使用累积总计选项
如果要创建累积总计或者将一项与另一项对比，就需要使用“值字段设置”对话框的第2个选项卡，方法是：
右击数据透视表的数值区中的某个数值并选择“值字段设置”
单击“值显示方式”下拉列表时，除“普通”选项卡外，还有8个选项可供选项
占同行数据总和的百分比――将数据透视表一行的总计的百分比显示为100％
占列行数据总和的百分比――将数据透视表一列的总计的百分比显示为100％
点总和的百分比―――所有明细数据的总计的百分比显示为100％
差异―――显示一项与另一项对比或者前一项对比，二者之间的差异
百分比――将某一项的数值表示为另一项的数值的百分比
差异百分比――表示某一项相对于另一项的变化的百分比
按某一字段汇总―――计算连续项的重要性
指数――计算各项的相对重要性
3.7.1利用差异选项显示年与年之间的不同
各公司总是想知道与上月相比，本月的业绩如何，或者，如果他们的业务是季节性的，可能想知道本月与去年相同的月份相比业绩如何。
为了创建这样的报表，要双击“求和项”并单击“值显示方式”。
在“值显示方式”下拉列表中，选择“差异”。
因为要将一年与另一年的情况进行对比，所以要在“基本字段”中选择“Years”。
在“基本项”字段中有几个可实施的选项。
如果总是要将当年和上一年的情况进行对比，可以选择“上一个”选项，如下图。
如果有若干年的有价值的数据，并且都要和某一基准年，如2007年进行对比，则可以选择2007.
下图同时给出了“值字段设置”对话框的设置和根据该设置所得到的报表。
该报表显示2008年1月的收入比2007年1月多5万5千美元。
3.7.2利用差异百分比选项将当年与上一年进行对比
“差异百分比”选项与“差异”选项类似
按“某一字段汇总”跟踪YTD总和
如果需要按月比较年初至今总收入，可以使用“按某一字段汇总”选项来完成
3.7.4确定每一行业务对总计的贡献大小
公司老板一般对公司各部门对公司总收入的贡献的百分比感兴趣，我们可能利用“占用行数据总和的百分比”
3.7.5创建季节性报表
季节性报表特别适合查看业务的季节性，用“占同列数据的百分比”
3.7.8利用指数选项跟踪相对重要性
最后一个选项，“指数”，可以进行相当模糊的计算，Microsoft声称这种计算可以描述一列某个单元格的相对重要性。
3－8案例：
根据业务业务报表的行计算收入
要求人们创建一份报表，能够提供全面查看产品系列的收入。
这种分析需要包括各个市场的产品系列的收入、各产品系列在市场在所占的百分比，以及各个市场所表示的产品系列占公司总收入的百分比。
第四章查看视透数据的方法
本章主要讨论分级、排序、筛选数据可视化以及数据透视表选项
4.1.1组合日期字段
方法：
将光标定位到数据透视表中任意一个单元格，在“选项”选项卡上，单击“分级”组中的“将字段分组”
字段中含有日期信息，会出现“分组”对话框。
目前这种情况下，要选择“月”、“季度”和“年”如下图
注意1、“季度”和“年”已经添加到数据透视表字段列表中。
但我们不要被这种现象所欺骗，我们的数据源并没有发生变化而包括这些新的字段，这些字段现在仅仅在内存中数据透视表缓存的一部分，2、默认情况下“年”和“季度”会自动添加到与数据透视表布局中的原日期字段相同的区域，如下图所示：
4.1.2按月组合时包含年
如果我们按月组合字段，总是应该在“分组”对话框中包含“年”，如果不包含“年”，就会将原始数据中每年的相同月算在一起，这不是我们要的结果。
4.1.3按周组合日期字段
在“分组”对话框中没有步长“周”，如果要按组合，方法是：
在数据透视表中任选一个标题，然后从“选项”选项卡选择“将字段分组”，在“分组”对和话框中，取消选中所有“步长”选项，而只选选择“日”字段，这样可以使“天数”旋转按钮可用，为了生成周报表，将天数的值由1增加到7，最后必须设置“起始于”日期，通过在开始之前先查看日历知道第1组要从2007年1月1日开始
4.1.3在一个报表中组合两个日期字段
如果需要生成有两个日期字段的报表，并试图对两个日期字段按月和年分组，Excel将随机地把第1个组合字段命名为“年”，第2个组合字段命名为“年2”。
这样的命名约定不可避免地会带来混淆。
在这种情况下重要的是用有意义的名称重新命名这些字段。
4.2组合值字段
通过值字段的“组合”对话框可以将数据透视表中的项组合成相同的范围
在下图中，“行标签”字段含有年龄数字。
目前，数据显示的是20～75之间的年龄，在A列中选择任一年龄并从“选项”对话框中选项“将字段分组”。
Excel将显示“组合”对话框。
在“组合”对话框中，选择组合参数，如图，此建议从20到75按照10年的增量进行组合。
4.4查看数据透视字段列表
4.4.1停靠和浮动数据透视表字段列表
通过拖动的方法可以实现数据透视表字段列表的移动
4.4.2重新排列数据透视表字段列表
在靠近“数据透视表字段列表”的右上角有一个小的下拉菜单，选择此下拉查看数据透视表字段列表的5种可能的排列方式
4.4.3使用区域节下拉菜单
4.4.4使用字段下拉菜单
4.5数据透视表中的数据进行排序
4.5.1使用“选项”选项卡上的排序图标排序
在“选项”选项卡的“排序”组合出现3个图标。
4.5.2使用字段列表隐藏的下拉菜单排序
4.5.3使用自定义序列排序
方法：
“Office”图标菜单――“Excel选项”――－“常用”分类选项卡上，单击“编辑自定义列表”
选择数据透视表中任一个要排序字段的单元格之一――――“选项”选项卡上，单击“排序”――在“排序对话框”中的“其他选项”，取消选中的“自动排序”，在“其他排序选项”对话框中，打开“主关键字段”，然后选择刚刚自定义的顺序。
4.6对数据透视表中的数据进行筛选
4.6.1添加字段到报表筛选区域
4.6.2从报表筛选区中选择一项
4.6.3从报表筛选区中选择多项
4.6.4快速选择或清除所有筛选选项
方法：
选择下图中的全部，就OK了
4.6.5使用字段列表筛选
这一功能是Excel新增的强大功能
4.6.6.使用标签筛选
“标签筛选”弹出式菜单中可用的选项包括以下：
4.6.7使用日期筛选
第5章在数据透视表内进行计算
5.1计算字段与计算项简介
用数据透视表分析数据时，会经常发现需要将分析扩展到要包括基于不在原数据集内的计算结果的数据。
Excel提供了在数据透视表内通过计算字段和计算项计算的方法。
计算字段是通过对数据透视表内的现有字段进行计算创建的一个字段。
可以将字段看作是添加一个虚拟列到数据集中，该列不占用源数据中的空间，存放的是用公式定义的数据，并而作为一个字段与数据透视表相互作用――――就像数据透视表中的其他字段一样
计算项是通过对数据字段内现有的数据项进行计算所创建的一个数据项，可以将计算字项看作是添加一个虚拟行到数据集中，该虚拟行不占用源数据中的空间，存放的是对同一字段中其他行进行计算的汇总值，计算项可以作为一个数据项与数据透视表相互作用―――就像数据透视表中其他数据项一样
有了计算字段与计算项，就可以向数据透视表中插入公式，创建自己的自定义字段或者数据项
5.1.1方法1：
手工添加计算字段到数据源中
可以手工添加计算字段到数据源中，让数据透视表作为普通的数据字段获取该字段，表面上看，这一选项看起来简单，但这种预计算度量并将其与数据源合并的方法在若干层次上不切实际。
如果计算字段的定义发生变化，就不得不返回数据源，重新计算每一个度量刷新数据透视表。
如果必须增加一种度量，也得不得不返回没数据源，添加新的计算字段，然后修改数据视表的范围以捕捉新字段。
5.1.2在数据透视表外使用公式创建计算字段
还可以通过使用公式中外部单元格内进行计算可以添加计算字段。
虽然这种方法可以给出随数据透视表刷新而更新的计算字段，但是数据透视表的任何结构变化都可能使该公式无效
5.1.3直接在数据透视表内插入计算字段
直接在数据透视表内插入计算字段是最佳选项，可以消除要管理公式的需要，提供数据源增加或变化时的可量测性，度量定义变化时的灵活性。
这种方法的另一个优势是可以改变数据透视的结构，甚至依据计算字段度量不同的数据字段，而不用担心公式出错或者丢失单元格引用
关键问题是集成自定义计算到数据透视表有许多好处：
（1）可能的公式和单元格引用错误的消除
（2）添加或者删除数据透视表中的数据而不影响计算的能力
（3）数据透视表改变或者刷新时，自动重新计算的能力
（4）度量定义变化时，很容易个性计算的能力
（5）有效地管理和维护计算的能力
5.2创建计算第1个计算字段
方法：
将光标定位在数据透视表中任一单元格中，单击“选项”―――“工具”――“公式”――“计算字段”，如下图
选择“计算字段”之后，Excel会激活插入对话框，如下图
注意此对话框顶部的两个输入框“名称”和“公式”。
此时的目的是赋予计算字段一个名称，然后通过选择数据字段和数学运算符的组合建立公式，提供需要的度量，根据实际定公式。
这意味着刚刚在数据源中添加了一列了吗？
没有，计算字段类似于数据透视表默认的分类汇总和总计计算，因为它们都是当数据透视表变化或者更新时可以重新计算的数学函数。
计算字段只是模仿数据源中实际的字段，这样就可以施动它们，修改字段设置，以及将它们和其他的计算字段一起使用
5.4创建第1个计算项
方法：
将光标定位在数据透视表中任一单元格中，单击“选项”―――“工具”――“公式”――“计算字项”，如下图
选择“计算项”之后，Excel会激活插入对话框，如下图
5.5了解数据透视表计算的规则和缺陷
5.5.1记住运算的优先级次序
Excel的运算次序如下
求括号内各项的值
求区域（：
）的值
求交叉（空格）的值
求联合（，）的值
求负（－）。
转换百分比（％）
计算乘方（^）
计算乘（*），除（/），乘和除的优先级相同
计算加（＋），减（－），加和减的优先级相同
对文本运算求值（&）
进行比较（＝,>,<,<>）
注意：
优先相同运算将从左向右计算）
5.5.2使用单元格引用和命名区域
在数据透视表内创建计算时同，实际上是在“真空”中工作，惟一可用的数据是数据透视表缓存中存在的数据，因此，在公式中，不能越过数据透视表缓存的边界引用单元格或者命名区域
5.5.3使用工作表函数
可以使用不需要单元格引用或者定义名称作为变量的任何工用表函数，例如：
Count,Average,If,not,Or等。
5.5.4使用常量
在数据透视表计算中可以使用任意常量。
5.5.5引用总计
计算公式不能引用数据透视表的分类汇总或者总计，这意味着不能使用分类汇总或者总计的结果作为计算字段中的变量。
5.6管理和维护数据透视表计算
5.6.1编辑和删除数据透视表计算
5.6.2改变计算项的求解次序
如果数据透视表中某个单元格的值取决于两个或者两个计算项的结果，就会改变求解计算项次序的选项。
也就是说，可以指定单个计算的次序
方法：
将光标定位在数据透视表中任一单元格中，单击“选项”―――“工具”――“公式”――“求解次序”，如下图
选择“求解次序”之后，Excel会激活插入对话框，如下图
5.6.3提供公式的说明文档
方法：
将光标定位在数据透视表中任一单元格中，单击“选项”―――“工具”――“公式”――“公式”，如下图
选择“公式”之后，Excel会激活插入对话框，如下图
第6章使用数据透视表视图和其他可视化工具
6.1什么是真正的数据透视图
和其他人分享自己的分析结果时，可能很快就会发现效果不好的原因是人们需要图，数据透视表很好，但是它留下了许多需要时间才能消化的并且让人讨厌的数字。
另一方面，利用图可以快速确定自己的数据要揭示什么。
图可以直接让用户满意，让用户可以立即看出其相互关系，指出不同之处并观察趋势。
最关键的是。
现在经理想要尽可能快地吸收消化数据，而其他传递信息的手段都没有图快。
这正是数据透视图发挥作用的地方，数据透视表提供分析，而数据透视图可以提供视觉效果
6.2创建一个透视图
方法：
将光标放在数据透视表内的任何地放，并且应用程序的功能区上单击“插入”选项卡，“插入”选项卡上，可以看到“图表”组。
它展示了可以创建的各种不同类型的图表，可能选择自己喜欢会用的数据透视图表类型。
正如下图，所看到的，选择图表类型会在电子数据表上生成两个对象：
一个图形和一个“数据透视图筛选窗格”
6.3牢记数据透视规则
6.3.1修改基础数据透视表
升级会员 