数据透视表教程

1、教程:数据透视表我县有三个职业学校，每个学校有四个专业，每个专业都有五门课，学生人数千人左右。这次期末统考结束后，教育局要求将所有学生的考试成绩汇总到一个工作表中，并要求对三所学校的各科成绩、各专业成绩进行分析比较。可见工作量之大啊！不仅要运用到分类、还要实现汇总计算。永中Office的数据透视表就具有很强大的数据处理能力，不必写入复杂的公式，使用向导创建一个交互式表格就可以自动提取、组织和汇总数据。下面就来和大家分享：统计出的全县三所学校的学生成绩：一、创建数据透视表在创建数据透视表前，首先对源数据进行几项重要检查，否则无法创建数据透视表。*l 必须包含列标题。如“准考证号”、“语文”、“数

2、学”等。*l 用于创建数据透视表的数据区域内不能有空行或空列，否则仅为空行前或空列左的数据进行创建。* 每列只能包含一种类型的数据l1.选择“数据”“数据透视表”，弹出“数据透视表向导步骤1”对话框。2.单击“下一步”，到步骤2，输入要建立数据透视表的数据源区域。（注意：“全县三所职中统考成绩”行不能包含在内）3.按照步骤，“完成”后，会同时显示“数据透视表”工具栏和“数据透视表字段列表”框。二、构建数据透视表字段1.双击“数据透视表字段列表”中的“学校”和“专业”，项目会自动被填充到“将行字段拖至此处”区域。2.选中“语文”，将其拖拽到“请将数据字段拖至此处”区域。“语文”拖至后效果见下图（

3、这时可清晰得观察出各个学校及各个专业的“语文”成绩总分）3.参照上面方法，分别将“数学”、“英语”等科目拖至上图中的“汇总”列区域。所有科目拖至后效果：三、改变数据透视表的汇总方式由于各个学校、各个专业的学生人数不等，所以通过总分还无法判断哪所学校此次考试的成绩更胜一筹。最有力的方法就是通过求平均值比较。1.选中数据透视表中的“求和项：语文”，单击“数据透视表”工具栏上“字段设置”图标，弹出“数据透视表字段”对话框。2.在“汇总方式”框中选择“平均值”。三所学校的各专业的语文科目就由“求和”改成“求平均值”了。与此同时，各学校的语文平均分也可以轻松得到。3.参照上面方法，得出其他学科的平均分值

4、。四、隐藏项目的明细数据1.双击“财会学校”、“第一职专”、“三山职中”所在的单元格，即可隐藏各专业各学科的明细数据。效果见下图所示。2.这时显示“每个学校每门科目的平均分”及“县平均分”。通过观察可一目了然得分析出哪个学校在此次统考中的成绩更优异及较“县平均分”的差距。贴心提示：如果检查发现数据透视表中的数据有误时，不能直接在其上进行修改，需先修改数据源，然后单击“数据透视表”工具栏的“数据透视表”按钮，从下拉菜单中选择“刷新数据”。标准数据库数据组织格式_数据透视表1假如有一学生成绩表，数据是按标准数据库组织格式组织的即第一行为字段名，下面的每一行为一个记录，如图所示图1数据透视表源数据2

5、数据数据透视表和数据透视图如果只生成数据透视表，则只选择数据透视表，如果同时要生成数据透视图，则最好选择下一项，即数据透视图（及数据透视表），这里选择第一项 ，下一步图2数据透视表向导一3指定数据区域在这里可以指定数据区域，假如以后，还要添加新的记录，最好这里指定的数据区域可以大一些，如A1:F1000（你甚至可以设为A:F，这样做的缺点是，会产生空格项，但如果增加数据时，不必再重头制作），下一步图3数据透视表向导二4指定数据透视表产生的位置可以指定新建工作表，还可以指定现有工作表，只需指定产生数据透视表的第一个单元格就可以了，如H3，完成。图4数据透视表向导三产生的空数据透视表如下图所示图5

6、数据透视表初表看了这个图，你应该明白，我为什么要在4步，指定在H3显示数据透视表了吧。最上面产生的哪一行的妙处，将在下面介绍。5行字段的拖放将学号拖至“将行字段拖至此处”框中，姓名和性别分别拖至学号相邻的右侧，将科目拖至“将列字段拖至此处”框中。将成绩拖至“请将数据项拖至此处”框中。经过以上设置，可以产生如下图所示的效果。图6初步完成表一6字段的进一步设置在左侧的行字段中，不需要对姓名和性别进行汇总，可以在任意的姓名汇总上（如刘备 汇总单元格）右击，隐藏（图12），或者字段设置，无，确定。同样设置隐藏性别的汇总。注：这里的汇总，实际就相当于数据分类汇总产生的结果，也就是说，数据透视表对于需要经

7、常分类汇总的人来说，要方便了许多，不需要手工排序，排序工作自动完成。本例只是选择了最简单一种，就不需要分类汇总了。图7字段设置对话框（注意上面这个对话框，下面要反复用到）此时，数据透视表变为如下形式图8初步完成表二7隐藏空白行列在行(空白)或列(空白)字段名上右击，隐藏（图12）。则透视表变为图9初步完成表三如果你不想显示总计行和总计列，同样可以在总计行标题和列标题上右击，隐藏。8调整数据汇总方式此时的显示，并不理想，显示的仅为数据的个数，而我们需要显示各科成绩，与几门课的平均值在图中显示为1和区域任一单元格上右击，字段设置，改为求和或平均值（图7）。因为每人每项目的数据仅有一个，求和与平均值

8、在数值上是相等的，可以任选一项。其实，在图7字段设置对话框中，你还可以设置其他的计算方式，包括求和、计数、平均值、最大值、最小值、乘积、数值计数、标准偏差、总体标准偏差、方差、总体方差，总计11种计算方式，你可以根据你的需要进行选择哟。图10初步完成表四9数值格式的设置对于最右侧的总计项，由于显示的小数点位数过多，能不能通过设置小数点位数，使列宽变得窄一些呢？这个没有问题，右击，字段设置，数字（图7中与高级相同位置的按钮，在这里已经变为数字），数值，保留小数点1位。令人可惜的是，这里设置的数值格式，会影响到整个表的单元格设置，在一处设置了保留小数点位数，在其他单元格，也自动继承这种数值格式，如

9、果要单独设置，可以按常规的单元格格式设置方法，选中要设置的单元格区域，再设置单元格格式。图11完成表10数据刷新数据透视表还有一个致使的弱点，就是不能自动刷新数据，需要通过右击刷新数据，进行手工刷新。图12右击快捷菜单数据刷新后，你可以看出，姓名中多了周瑜，科目中添加了政治。而刘备的政治缺考。如果你在第3步，没有多指定的数据区域，新增添的数据，将不能显示出来。图13添加数据并刷新后的效果11数据顺序的调整图13中的数据，怎么按性别排序呢？比如把貂蝉和小乔放到周瑜的后面。这个操作对于Excel 2003及以前的版本，稍微差了一些，只能一个一个地调整，在貂蝉对应的学号上右击，顺序，移至底端。再在小

10、乔的学号上右击，顺序，移至底端。请谅解，调整后的效果图，就不要再上传了吧，这个文章已经够长了。在Excel 2007中，排序功能大大增强，你用了就知道了，这里也不再多叙。图14顺序调整子菜单如果你并不在意表格各字段的排列，只是要统计男生与女生的平均值，只需将性别拖放到学号的左侧图15按性别汇总效果12数据的筛选数据透视表还自动地设置成自动筛选状态，你可以方便地进行筛选，如只显示男同学的成绩，只需点击性别右侧的三角形筛选标志，仅勾选“男”。图16透视表的自动筛选功能同样，对科目进行筛选，可以有选择地显示某一科目的成绩。但这样筛选后，可能会导致数据格式的清除。既然不想显示所有的科目，就不如将科目拖

11、放到最上面的“将页字段拖至此处”框中，并通过筛选，只显示某一科目，如数学。图17将科目拖至页字段的效果如果将科目拖放到行字段区域，则会产生按学号（姓名）汇总的效果（这个就不上传效果图了吧）。数据透视表是不是很自由？13数据透视表的复制辛辛苦苦制作的数据透视表，按上所述，只显示为数学成绩，要同时再制作一个只显示英语成绩的透视表，是不是需要重新制作呢？答案是：如果你不怕麻烦，想再练习一遍，就重新制作好了。不过，我可不愿意一切从头来，选中透视表区域，复制，在N1单元格粘贴。其它的设置，就不用再多说了吧。你不妨试试下面的效果，复制一份透视表，让原来的透视表显示平均成绩，新粘贴的透视表显示计数项。14数

12、据透视表数据的获取数据表是动态变化的，一旦对透视表结构进行调整，所引用的单元格的位置有可能会发生改变，如何才能获得一个动态跟随的效果呢？方法很简单，比如要获得周瑜的各科目的平均成绩，可以将透视表调整回图13所示效果，在引用数据单元格输入“=”，再点击周瑜最右边的总计项。在编辑栏中，就会显示周瑜各科目平均值的引用公式。=GETPIVOTDATA(成绩,$H$3,学号,姓名,周瑜,性别,男)当然你完全可以将公式中的学号、等项，改为某个单元格引用。但这种引用，如果脱离开透视表，就会出现错误。15数据透视图的生成如果在第2步，选择“数据透视图（及数据透视表）”，则会同时生成数据透视表和数据透视图，不过

13、现在生成数据透视图，也为时不晚。只需将光标放到透视表区域，插入图表。即可生成相应的数据透视图。其他方面的应用在许多数据中筛选出不重复的数据，以本例说明，源数据中的B、C、D列都可以看成是有重复的数据，通过数据透视表，就可以生成不重复的姓名列表，性别列表和科目列表。2.2.5 动态图表的建立在企业的经营活动中，往往需要为每个部门建立大量相似的图表，如果在一张工作表上建立太多的图表，既费时也使得图表显得凌乱不堪。我们可以建立动态图表来解决这个问题，当需要了解某个部门的销售情况时，只需将鼠标移到工作表中该部门的单元格上，即可立即显示出该部门的销售图表。【例2-10】某企业的8个销售部门一年内各月的销售量数据如图2-34所示，建立各部门的动态图表的步骤如下：图2-34 动态销售图表（1）设计动态图表数据区域，如图2-34所示。（2）在单元格A13中输入公式“=INDIRECT(ADDRESS(CELL(row),COLUMN (A3)”，并把该公式向右填充复制到M13中，这里COLUMN的意思是返回参数所在的列标，CELL(row)的意思是返回当前光标所在的行号，ADDRES