2010数据透视表完全剖析

“透视”作为一个动词，意思是旋转。

如果将数据看成是一个物体，数据透视表允许旋转数据汇总，从不同角度或观点来看它。

数据透视表能够轻松地移动字段，交换字段位置，设置创建项目的特定组。

如果给出一个陌生的物体让你鉴定，你可能会从不同的角度观察它来得出答案。

处理数据透视表与研究一个陌生的物体类似。

此时，物体就是你自己的数据。

数据透视表需要多次试验，所以要旋转并控制数据透视表直到你满意为止。

最后得到的结果会让你感到惊讶。

数据透视表是一种让用户可以根据不同的分类、不同的汇总方式、快速查看各种形式的数据汇总报表。

简单来说，就是快速分类汇总数据，在处理数据方面很强大！鉴于目前有关Excel2010数据透视表的资料很少，现班门弄斧，将自己所知道的透视表知识总结出来，供大家学习。

有不当之处，烦请多指正。

内容分为三部分：一、常用功能介绍二、各种小技巧介绍三、在这两部分中间穿插一些小练习，作为巩固和补充说明：前两部分只是为了说明某个功能而已，不一定有实际意义。

小练习全部以行业运用的实例，以互动的形式来进行，我出题大家共同讨论，适当时候再进行点评。

第一部分：常用功能介绍1、数据透视表介绍1-1、什么是数据透视表数据透视表是一种可以快速汇总大量数据的交互式方法。

使用数据透视表可以深入分析数值数据，并且可以回答一些预料不到的数据问题。

数据透视表是专门针对以下用途设计：1) 以多种用户友好方式查询大量数据。

2) 对数值数据进行分类汇总和聚合，按分类和子分类对数据进行汇总，创建自定义计算和公式。

3) 展开或折叠要关注结果的数据级别，查看感兴趣区域汇总数据的明细。

4) 将行移动到列或将列移动到行（或“透视”），以查看源数据的不同汇总。

5) 对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，使您能够关注所需的信息。

6) 提供简明、有吸引力并且带有批注的联机报表或打印报表。

1-2、制作数据透视表的注意事项：以工作表数据制作数据透视表，这些工作表数据必须是一个数据清单。

所谓数据清单，就是在工作表数据区域的顶端行为字段名称(标题)，以后各行为数据(记录)，并且各列只包含一种类型数据的数据区域。

这种结构的数据区域就相当于一个保存在工作表的数据库。

第一，数据区域的顶端行为字段名称(标题)。

第二，避免在数据清单中存在有空行和空列。

这里需指明以下，所谓空行，是指在某行的各列中没有任何数据，如果某行的某些列没有数据，但其他列有数据，那么该行就不是空行。

同样，空列也是如此。

第三，各列只包含一种类型数据。

第四，避免在数据清单中出现合并单元格。

第五，避免在单元格的开始和末尾输入空格。

第六，尽量避免在一张工作表中建立多个数据清单，每张工作表最好仅使用一个数据清单。

第七，工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。

在制作数据透视表之前，应该按照以上7点来检查数据区域，如果不满足上面的要求，需要先进行整理工作表数据从而使之规范。

（不规范的实例就不提供表格了，至于怎么处理，想必大家应该比我更熟悉，呵呵）在EXCEL的使用中养成良好的数据处理习惯2、创建透视表单击数据源中的任意一个单元格（好处：在默认情况下，这样可以确保数据透视表能够捕获数据源的范围），插入――表格――数据透视表，选择透视表的位置，一般默认放在新工作表就可以。

下面是创建一个基础数据透视表的动画演示过程。

3、移动和删除数据透视表3-1、移动数据透视表在需要移动的数据透视表的任意位置单击，菜单栏将显示“数据透视表工具”，自动添加“选项”和“设计”选项卡。

在“选项”选项卡上的“操作”组中，“选择移动数据透视表”。

3-2、删除数据透视表在需要删除的数据透视表的任意位置单击，在“选项”选项卡上的“操作”组中，单击“选择”下方的箭头，然后单击“整个数据透视表”，按Delete键删除。

注：如果要把透视表的所有边框和内容都删除，直接选中包含数据透视表的单元格，右键删除就可以。

4、添加字段到报表中数据透视表字段列表中的四个区域分别是：1) 报表筛选――添加字段到报表筛选区可以使该字段包含在数据透视表的筛选区域中，以便对其独特的数据项进行筛选。

2) 列标签――添加一个字段到列标签区域可以在数据透视表顶部显示来自该字段的独特的值。

3) 行标签――添加一个字段到行标签区域可以沿数据透视表左边的整个区域显示来自该字段的独特的值。

4) 数值―――添加一个字段“数值”区域，可以使该字段包含在数据透视表的值区域中，并使用该字段中的值进行指定的计算。

5、更改字段名称最终的数据透视表中的每个字段都有一个名称，列和筛选区域中的字段从源数据的标题继承其名称，数据部分中的字段会被赋予“求和项：××”这样的名称。

比如，实际操作中，我们可能会用“总计”来代替默认名称，直接点击字段输入一个新名称即可，如果输入的是数据透视表中已有的名称，那么命名会失败，可以在命名字段的开头处增加一个空格。

增加一个空格，这种变化只是一种修饰，用户是不会注意到该名称前面的空格，只是Excel认为没有空格的和有空格是不同的。

6、从数据透视表中删除字段若要删除字段，在数据透视表字段列表中，执行下列操作之一：1. 在“选择要添加到报表的字段”框中，清除要删除的字段的复选框。

注意清除复选框将从报表中删除该字段的所有实例。

2. 在布局区域中，单击要删除的字段，然后单击“删除字段”。

3.在布局区域中，单击要删除的字段，并按住鼠标不放，然后将其拖到数据透视表字段列表之外。

7、修改数据透视表的样式在“设计”选项卡的“数据透视表样式”样式库中，选中你喜欢的样式，选中就可以。

8、更改数据的汇总方式汇总方式有：求和、计数、最大值、最小值、平均值等等。

9、更改数据的排列顺序排序可以分为：自动排序，手动排序，按字母、笔画排序，自定义排序。

1) 自动排序的几种形式：右键、透视表下拉按钮、菜单栏。

2) 手动排序：靠鼠标拖动排序。

3) 按字母和笔画排序：透视表下拉按钮——其他排序选项——升序（降序）——其他选项——去掉自动排序的勾——字母（笔画）排序——确定。

4) 自定义排序：第一步：文件——选项——高级——常规——编辑自定义列表——导入排序的依据——确定。

第二步：透视表下拉按钮——其他排序选项——升序（降序）——其他选项——去掉自动排序的勾——主关键字排序（选择刚定义的排序）——确定。

10、设置数字格式右键点击需要设置的字段的任意单元格，点击值字段设置——数字格式——选择设置单元格格式中任意一种格式。

11、刷新数据当数据源中的某一个数值更改，只要点击透视表中的任意单元格，刷新，就可以看到数据的变化。

12、打开工作簿时自动刷新数据每次点刷新数据也麻烦，其实可以直接设置自动刷新，这样可以省去不少麻烦。

点击透视表任意单元格，数据透视表选项——数据——勾选“打开文件时刷新数据”，确定。

13、不显示分类汇总点击透视表任意单元格，字段设置——分类汇总——无，确定。

14、筛选前10个最大值在字段下面的下拉箭头中，选择值筛选——10个最大的值，其实这里说10个，实际上可以根据自己需要而更改数量，比如换成3，5等，也可以是最小值。

15、组合数据透视表中组合有三种方式：数字组合，时间组合，文本组合。

15-1、数字组合数字组合有两种形式：按等步长自动组合，按不等步长组合。

等步长自动组合：1、选中行标签中任意单元格，右键，然后点击创建组；2、自己修改步长值。

按不等步长组合：选中多个数据右键，然后点击创建组，手动更改名称。

15-2、时间组合时间组合：可以按年月日时分秒等组合，比较灵活。

按月组合，如果跨年度必须选中年月。

按季度组合，如果跨季度必须选中年。

按日组合，最灵活，可以任意选择天数，以实现按周组合，按旬组合（不是很精确，添加辅助列用函数实现才会精确）。

小时、分、秒的组合也是一样的。

15-3、按文本组合按文本组合跟按数字不等距组合差不多，可以用手动组合还可以用函数做辅助列来实现（这里就不再做动画说明）。

取消组合：选择需要取消的字段的任意单元格，点击取消组合即可。

16、避免组合失败组合失败的原因主要有三种：组合字段数据类型不一致、日期格式不正确、引用区域失效。

1、组合字段数据类型不一致A、空白单元格数据源中存在空白单元格造成数据类型不一致，解决方法是先将数据源中的空白单元格删除。

注意：删除数据源的空白单元格之后，由于数据透视表的数据缓存的原因，刷新数据透视表时可能仍然不能正确地显示数据和分组。

解决办法是重新创建一张数据透视表或是用彻底更新数据透视表的方法来更新数据源。

B、数据类型不同将数据类型转换成相同类型的数据再创建数据透视表。

C、数据源引用为整列或整行创建数据透视表不能将数据源引用为整列或整行，因为引用整行和整列就会出现空白的项，所以在分组时也会出现错误提示。

2、日期格式不正确判断日期格式是否是数值型数据可用type函数，如果判断不是数值型数据，则可选复制一个空白单元格，然后选中要更改的区域》右键选择性粘贴》数值》加》确定》设定为日期格式》再重新创建数据透视表。

3、引用区域失效如果对数据透视表进行组合时出现“数据透视表无效”的警告提示框，可采用数据透视表向导查看数据源是否丢失。

如果是数据源丢失则是则可重新指定数据源。

检查数据源的操作方法：数据透视表工具栏上的数据透视表向导》上一步》查看“选定区域”目录下文件是否存在，如果这个选定区域不存在则会出现上述错误。

17、添加数据透视表向导在快速访问工具栏步骤：单击文件——选项——快速访问工具栏——所有命令——数据透视表向导——添加——确认。

也可以使用快捷键：Alt+D+P，快速调出数据透视表和数据透视图向导。

18、创建动态透视表第一、表格法单击菜单栏插入——表格——创建表格，然后再创建数据透视表即可达到动态的引用区域。

（推荐使用这种）第二、定义名称法将=OFFSET($A$4,,,COUNTA($A:$A)-1,COUNTA($4:$4))定义名称为DATA定义名称为DATA，再用DAT A作为数据源即可创建动态的数据透视表。

19、更改值的显示方式值的显示方式：无计算、升序排列、降序排列、总计百分比等等。

20、插入计算项及计算字段用数据透视表分析数据时，会经常发现需要将分析扩展到要包括基于不在原数据集内的计算结果的数据。

Excel提供了在数据透视表内通过计算字段和计算项计算的方法。

计算字段是通过对数据透视表内的现有字段进行计算创建的一个字段。

可以将字段看作是添加一个虚拟列到数据集中，该列不占用源数据中的空间，存放的是用公式定义的数据，并而作为一个字段与数据透视表相互作用――――就像数据透视表中的其他字段一样。

计算项是通过对数据字段内现有的数据项进行计算所创建的一个数据项，可以将计算字项看作是添加一个虚拟行到数据集中，该虚拟行不占用源数据中的空间，存放的是对同一字段中其他行进行计算的汇总值，计算项可以作为一个数据项与数据透视表相互作用―――就像数据透视表中其他数据项一样。