EXCEL培训之七.ppt

1、数 据 清 单 的 使用,3 数据透视表,数据透视表是Excel中最具技术性的复杂组件之一，本质上是从数据库中产生的一个动态汇总报告。数据透视表可以快速合并和比较大量数据，对分类变量进行汇总并显示明细数据。 数据透视表由字段(页字段、行字段、列字段和数据字段)、项(页字段项和数据项)以及数据区域组成。,数 据 清 单 的 使用,3.1 数据透视表的创建,步骤一： 在数据清单中，任选一单元格，然后单击“数据数据透视表和数据透视图”，弹出“数据透视表和数据透视图向导3步骤 之1”对话框；在对话框中选择数据源的类型和报表类型，一般采用默认选项，数据源类型为Microsoft Excel数据清单，报表

2、类型为数据透视表；,数 据 清 单 的 使用,步骤二：单击“下一步”按钮，弹出“数据透视表和数据透视图向导3步骤之2”对话框，Excel一般会显示选定区域所在的单元格区域； 步骤三：单击“下一步”，弹出“数据透视表和数据透视图向导3步骤之3”对话框；在该对话框中选择数据透视表显示的位置，默认方式为新建工作表；,数 据 清 单 的 使用,步骤四： 点击“3步骤之3”对话框中“布局”按钮，弹出“数据透视表和数据透视图向导布局”对话框，在对话框右上角有提示：“将右边的字段按钮拖到左边的图上即可构造数据透视表”。选择分类变量移至“行字段” 和“列字段”，如有必要还可以移到“页字段”，将数值变量或品质变

3、量移至“数据字段”，点击“确定”按钮，回到“数据透视表和数据透视图向导3步骤之3”对话框；点击“完成”按钮，就可以建立数据透视表。,数 据 清 单 的 使用,在“数据透视表和数据透视图向导3步骤之3”中没有按“完成”按钮之前，如果要增添或修改项目，可以点击“选项”，将弹出“数据透视表选项”对话框，可以在该对话框中更改数据透视表的名称和其他格式选项和数据选项。,数 据 清 单 的 使用,在“数据透视表和数据透视图向导3步骤之1”中直接单击“完成“按钮，屏幕就弹出数据透视表草图，同时出现数据透视表工具栏和数据清单中的各字段名，可以仿照上面的步骤建立数据透视表。,创建数据透视表的简便方法,数 据 清

4、 单 的 使用,3.2 数据透视表的组成,对于建立的数据透视表，我们应该进一步了解它的组成元素。 行字段：数据透视表中指定为行方向的源数据清单或表单中的字段，通常为分类变量；有多个行字段的数据透视表具有一个内部行字段和多个外部行字段，其中内部行字段距离数据区最近；最外部的行字段中的项仅显示一次，而其他行字段中的项可以重复显示； 列字段：数据透视表中指定为列方向的源数据清单或表单中的字段，和行字段类似，通常将分类变量放到列字段中；,数 据 清 单 的 使用,页字段：数据透视表中指定为页方向的源数据清单或表单中的字段，通常也将分类变量作为页字段； 数据字段：含有数据的源数据清单或表单中的字段，通常

5、将数值变量移入数据字段中，使用Sun等汇总函数对数值变量进行计算，但是品质变量也可以移入数据字段，使用Count计数函数来计算频数； 项：数据透视表中字段的子类型或子成员，它表示源数据中字段的唯一条目。,数 据 清 单 的 使用,3.3 数据透视表的基本操作,对数据透视表的操作，主要依靠“数据透视表”工具来实现，前面已经看到，在建立数据透视表的同时，就出现了一个数据透视表工具栏。点击“数据透视表(P)”,就显示一个下列式菜单，包含“设置报告格式、数据透视图、向导、更改数据、脱机、隐藏、选定、组及显示明细数据、公式、顺序、字段设置”等选项。右边还有并列的9个功能按钮，包括“设置报告格式、数据透视

6、图、隐藏明细数据、显示明细数据、更新数据、在汇总中包含隐藏项、始终显示项目、字段设置和隐藏字段列表”。,数 据 清 单 的 使用,单击数据透视表中任一单元格，再单击“数据透视表”工具栏中的“数据透视表(P)向导”，返回到“数据透视表和数据透视图向导3步骤之3”； 点击“布局”按钮，打开“数据透视表和数据透视图向导布局”对话框，将一个新的字段拖到“数据字段”下面；点击“确定”按钮，回到“数据透视表和数据透视图向导3步骤之3”对话框，在“数据透视表显示位置”下的复选框中选择“新建工作表”，再点击“完成”按钮，会在原来求和项的字段下新增了一个新的字段,1 添加字段,数 据 清 单 的 使用,添加字段

7、的另外一种方法,在创建好的数据透视表中，从“数据透视表字段列表”对话框中，选择需要添加的字段，如“订购日期”，将它拖动到原数据透视表行字段所在的列上，可以增加一个新的行字段；,数 据 清 单 的 使用,2 移动字段,点击数据透视表工具栏中的“布局”按钮，打开“数据透视表和数据透视图向导布局”对话框，可以将行字段、列字段和页字段进行相互转换，达到移动字段的目的。,数 据 清 单 的 使用,3 改变字段的汇总方式,单击数据透视表中任一单元格，再单击“数据透视表”工具栏中的“字段设置”，弹出“数据透视表字段”对话框，在“汇总方式”列表框中列出了“求和、计数、平均值、最大值、最小值、乘积、计数值、标准

8、偏差、总体标准偏差、方差、总体方差”共11个函数； 在“汇总方式”中选择“平均值”，点击“确定”，就改变了字段的汇总方式。,数 据 清 单 的 使用,4 改变字段的显示方式,如果要改变数据的显示方式，只要在“数据透视表字段”对话框中点击右下角的“选项”按钮，对话框中会延伸出“数据显示方式”下拉式列表框，其中有9种显示方式可以选择，假定选择“占同列数据总和的百分比”，点击“确定”按钮，字段的显示方式也就发生了变化。,数 据 清 单 的 使用,5 改变字段标题的名称,如果想修改字段标题名称，可以选择字段，点击工具栏中的“字段”按钮，或者双击该字段名称，弹出“数据透视表字段”对话框，在名称文本框中修

9、改字段的名称； 最快捷的方式是单击字段名称，在Excel编辑栏中为字段改名。,数 据 清 单 的 使用,6 显示明细数据：,点击需要显示明细数据的字段单元格，再单击“数据透视表”工具栏中的“显示明细数据”按钮，弹出“显示明细数据对话框”；或者点击需要显示明细数据的字段单元格，从右键快捷菜单中选择“组及显示明细数据显示明细数据”，弹出“显示明细数据”对话框，从中选择明细数据中包含的字段； 如果在数据区域所在的列点击“显示明细数据”按钮，明细数据清单中会包含所有的字段。,数 据 清 单 的 使用,在第一个数据透视表的基础上，单击“数据透视表”工具栏种的“向导”按钮，返回“数据透视表和数据透视图3步

10、骤3”对话框，将“品种”字段拖回原位置，将“销售地区”字段移至“行字段”，“产品大类”字段不动，建立一个新的数据透视表； 选定“本市”的“塑料薄膜”单元格，再单击“数据透视表”工具栏种的“显示明细数据”，按钮，就列出4种薄膜各个季度的销售量：,数 据 清 单 的 使用,7 设置报告格式,点击“数据透视表”工具栏中“设置报告格式”，按钮，弹出“自动套用格式”对话框，共显示了22种自动套用格式，从中可以选择合适的格式修饰数据透视表。假设选择“表2”，即产生如下报表：,数 据 清 单 的 使用,3.4 数据透视表的高级功能,1 数据组合 组合项目是数据透视表的一个有用的功能。它可以对行字段或列字段进

11、行组合。比如下面建立的一个数据透视表，如果想计算出1019、2029和3139岁的人数。点击行字段标题“年龄”，单击右键，选择“组及显示明细数据组合”，显示“组合”对话框；,数 据 清 单 的 使用,在“起始于”后文本框中键入10，“步长”后文本框中键入10，点击“确定”按钮，数据透视表中的年龄字段就按照这三个年龄段重新进行组合计算。,数 据 清 单 的 使用,2 插入计算字段,从数据透视表的字段中创建新字段，计算字段必须位于数据透视表的数据区域，不能在行、列或页面区域内使用计算字段； 数据透视表是一种特殊的数据区域，不能再数据透视表中插入新行或者新列，用户不能使用插入公式计算数据透视表中的数

12、据，但是为数据透视表创建计算字段可以解决这个问题； 计算字段的公式不是标准的Excel函数，不能再单元格内输入公式，而是在对话框中输入计算公式，它们和数据透视表中的数据一起存储起来。,数 据 清 单 的 使用,在前面建立的数据透视表中，如果要根据订单金额给销售人员给与一定的提成或奖金，就可以利用计算字段功能来生产这个新的奖金或提成字段。,数 据 清 单 的 使用,点击数据字段所在区域，从数据透视表工具栏中选择“公式计算字段”，弹出“插入计算字段对话框； 在名称后的文本框中键入插入字段的名字，比如奖金提成；从字段下的变量列表框中选择订购金额，点击插入字段按钮，将它移入公式文本框中，在后面键入公式

13、，比如乘以0.10，销售人员按照销售额的10%来提成，点击确定按钮，就生成一个新的计算字段。,数 据 清 单 的 使用,3 插入计算项目,计算项目使用数据透视表的一个字段中其它项目的内容，计算项目必须位于数据透视表的行、列或页面字段内，不能在数据区域中使用计算字段。 计算字段是在数据源中增加新字段的一种可选的方法。,数 据 清 单 的 使用,下面的数据透视表中显示了按照月份和销售人员分类的销售额，在这个透视表中使用“月份”作为行字段，可以为它创建一个计算项目，比如计算每一季度的销售额，当然可以使用组合功能来进行组合，但是组合会隐藏单独的月份，只显示某组的合计，因此为每个季度的总和创建一个项目更

14、加灵活。可以显示单独月份的销售额和每季度的总销售额。,数 据 清 单 的 使用,将鼠标移动到要进行计算项目的数据透视表的行列或页字段区域，本例中将光标移到月份字段； 从数据透视表中选择“公式计算项”，弹出“在月份中插入计算字段对话框”，在名称后的文本框中键入“一季度”，从“项”列表框中选择“一月”，点击“插入项”，将它移入公式文本框中，采用同样方式，输入一季度合计公式，点击“添加”按钮；采用同样方式，为其它三个季度输入公式； 单击“确定”按钮，完成计算项目的设定；,数 据 清 单 的 使用,创建好“季度”项目后，这些新项目显示在数据透视表的下方，可以通过选择和拖动来重新排列这些项目。,数 据

15、清 单 的 使用,3.5 数据透视图的操作,1 创建数据透视图的两种方式： 方法一：在数据透视表工具栏中单击“图表向导”；弹出“图表向导”，选择“簇状柱型图”，再按照绘制柱状图的步骤来创建数据透视图；,数 据 清 单 的 使用,方法二： 在存放原始数据的数据清单中，选取任一单元格，单击“数据数据透视表和数据透视图”，打开“数据透视表和数据透视图向导3步骤之1”对话框中，将“所需创建的报表类型”选项改为“数据透视图(及数据透视表)”； 单击“下一步”按钮，再单击“数据透视表和数据透视图向导3步骤之2”的“下一步”按钮，单击“完成”按钮，显示数据透视图雏形；,数 据 清 单 的 使用,在“数据透视图”工具栏中，将“产品大类”字段拖到底部框中，将“销售地区”字段拖到右侧框中，将“全年”字段拖到中间放置数据的部位，然后单击工具栏中的“图表向导”按钮，在“图表向导4步骤1图表类型”中，选择“簇状柱形图”；,数 据 清 单 的 使用,点击“下一步”，进入“图表向导4步骤3图表类型”，在“图表标题”框中输入“本市和外地三大类产品销售比较图”，在“分类轴”框中输入“产品大类”，在数值轴中输入“销售量”，点击“下一步”； 在“图表向导4步骤4图表位置”，选择默认方式，点击完成，就生成数据透视图：,数 据 清 单 的 使用,2 将数据透视图更改为静态图,在创建