数据透视表实例

标准数据库数据组织格式

_数据透视表

1．假如有一学生成绩表，数据是按标准数据库组织格式组织的 即第一行为字段名，下面的每一行为一个记录，如图所示

图1 数据透视表源数据

2．数据→数据透视表和数据透视图

如果只生成数据透视表，则只选择数据透视表，如果同时要生成数据透视图，则最好选择下一项，即数据透视图（及数据透视表），这里选择第一项 ，下一步

图2 数据透视表向导一

3

．指定数据区域

在这里可以指定数据区域，假如以后，还要添加新的记录，最好这里指定的数据区域可以大一些，如A1:F1000（你甚至可以设为A:F，这样做的缺点是，会产生空格项，但如果增加数据时，不必再重头制作），下一步

图3 数据透视表向导二 4．指定数据透视表产生的位置

可以指定新建工作表，还可以指定现有工作表，只需指定产生数据透视表的第一个单元格就可以了，如H3，完成。

图4 数据透视表向导三

产生的空数据透视表如下图所示

图5 数据透视表初表

看了这个图，你应该明白，我为什么要在4步，指定在H3显示数据透视表了吧。最上面产生的哪一行的妙处，将在下面介绍。

5．行字段的拖放

将学号拖至“将行字段拖至此处”框中，姓名和性别分别拖至学号相邻的右侧， 将科目拖至“将列字段拖至此处”框中。 将成绩拖至“请将数据项拖至此处”框中。 经过以上设置，可以产生如下图所示的效果。

图6

初步完成表一 6．字段的进一步设置

在左侧的行字段中，不需要对姓名和性别进行汇总，可以在任意的姓名汇总上（如刘备 汇总单元格）右击，隐藏（图12），或者字段设置，无，确定。 同样设置隐藏性别的汇总。

注：这里的汇总，实际就相当于数据分类汇总产生的结果，也就是说，数据透视表对于需要经常分类汇总的人来说，要方便了许多，不需要手工排序，排序工作自动完成。 本例只是选择了最简单一种，就不需要分类汇总了。

图7 字段设置对话框

（注意上面这个对话框，下面要反复用到） 此时，数据透视表变为如下形式

图8 初步完成表二

7．隐藏空白行列

在行(空白)或列(空白)字段名上右击，隐藏（图12）。则透视表变为

图9 初步完成表三

如果你不想显示总计行和总计列，同样可以在总计行标题和列标题上右击，隐藏。

8．调整数据汇总方式

此时的显示，并不理想，显示的仅为数据的个数，而我们需要显示各科成绩，与几门课的平均值 在图中显示为1和区域任一单元格上右击，字段设置，改为求和或平均值（图7）。 因为每人每项目的数据仅有一个，求和与平均值在数值上是相等的，可以任选一项。

其实，在图7字段设置对话框中，你还可以设置其他的计算方式，包括求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差，总计11种计算方式，你可以根据你的需要进行选择哟。

图10

初步完成表四 9．数值格式的设置

对于最右侧的总计项，由于显示的小数点位数过多，能不能通过设置小数点位数，使列宽变得窄一些呢？ 这个没有问题，右击，字段设置，数字（图7中与高级相同位置的按钮，在这里已经变为数字），数值，保留小数点1位。

令人可惜的是，这里设置的数值格式，会影响到整个表的单元格设置，在一处设置了保留小数点位数，在其他单元格，也自动继承这种数值格式，如果要单独设置，可以按常规的单元格格式设置方法，选中要设置的单元格区域，再设置单元格格式。

图11 完成表 10．数据刷新

数据透视表还有一个致使的弱点，就是不能自动刷新数据，需要通过右击→刷新数据，进行手工刷新。

图12

右击快捷菜单

数据刷新后，你可以看出，姓名中多了周瑜，科目中添加了政治。而刘备的政治缺考。 如果你在第3步，没有多指定的数据区域，新增添的数据，将不能显示出来。

图13 添加数据并刷新后的效果

11．数据顺序的调整

图13中的数据，怎么按性别排序呢？比如把貂蝉和小乔放到周瑜的后面。

这个操作对于Excel 2003及以前的版本，稍微差了一些，只能一个一个地调整，在貂蝉对应的学号上右击，顺序，移至底端。再在小乔的学号上右击，顺序，移至底端。请谅解，调整后的效果图，就不要再上传了吧，这个文章已经够长了。

在Excel 2007中，排序功能大大增强，你用了就知道了，这里也不再多叙。

图14

顺序调整子菜单

如果你并不在意表格各字段的排列，只是要统计男生与女生的平均值，只需将性别拖放到学号的左侧

图15 按性别汇总效果 12．数据的筛选

数据透视表还自动地设置成自动筛选状态，你可以方便地进行筛选，如只显示男同学的成绩，只需点击性别右侧的三角形筛选标志，仅勾选“男”。

图16 透视表的自动筛选功能

同样，对科目进行筛选，可以有选择地显示某一科目的成绩。

但这样筛选后，可能会导致数据格式的清除。既然不想显示所有的科目，就不如将科目拖放到最上面的“将

页字段拖至此处

”框中，并通过筛选，只显示某一科目，如数学。

图17 将科目拖至页字段的效果

如果将科目拖放到行字段区域，则会产生按学号（姓名）汇总的效果（这个就不上传效果图了吧）。数据透视表是不是很自由？

13．数据透视表的复制

辛辛苦苦制作的数据透视表，按上所述，只显示为数学成绩，要同时再制作一个只显示英语成绩的透视表，是不是需要重新制作呢？

答案是：如果你不怕麻烦，想再练习一遍，就重新制作好了。不过，我可不愿意一切从头来，选中透视表区域，复制，在N1单元格粘贴。其它的设置，就不用再多说了吧。

你不妨试试下面的效果，复制一份透视表，让原来的透视表显示平均成绩，新粘贴的透视表显示计数项。

14．数据透视表数据的获取

数据表是动态变化的，一旦对透视表结构进行调整，所引用的单元格的位置有可能会发生改变，如何才能获得一个动态跟随的效果呢？

方法很简单，比如要获得周瑜的各科目的平均成绩，可以将透视表调整回图13所示效果，在引用数据单元格输入“=”，再点击周瑜最右边的总计项。在编辑栏中，就会显示周瑜各科目平均值的引用公式。 =GETPIVOTDATA("成绩",$H$3,"学号",20070616,"姓名","周瑜","性别","男")

当然你完全可以将公式中的"学号"、20070616等项，改为某个单元格引用。但这种引用，如果脱离开透视表，就会出现错误。

15．数据透视图的生成

如果在第2步，选择“数据透视图（及数据透视表）”，则会同时生成数据透视表和数据透视图，不过现在生成数据透视图，也为时不晚。

只需将光标放到透视表区域，插入→图表。即可生成相应的数据透视图。

其他方面的应用

在许多数据中筛选出不重复的数据，以本例说明，源数据中的B、C、D列都可以看成是有重复的数据，通过数据透视表，就可以生成不重复的姓名列表，性别列表和科目列表。