Excel2003数据分析.doc

第十五周Excel2003数据分析教学过程：一、数据的排序1、关键字概念排序依据称为“关键字”。（举例说明关键字的概念，依据“语文”来重新排列“成绩表”中的数据，则“语文”为关键字）2、对数据进行简单排序方法：确定关键字（如“语文”）；单击工具栏中的排序按钮（“升序” 、“降序” ）3、使用关键字进行排序“主要关键字”、“次要关键字”、“第三关键字”(举例说明三种关键字在使用中的作用及区别)方法：确定关键字单击“数据”菜单排序确定二、数据的筛选数据管理时经常需要从众多的数据中挑选出一部分满足条件的记录进行处理，即进行条件查询。如学生评比“三好生”，要从成绩清单中筛选出符合条件的记录。对于筛选数据，Excel2003提供了自动筛选和高级筛选两种方法：自动筛选是一种快速的筛选方法，它可以方便地将那些满足条件的记录显示在工作表上；高级筛选可进行复杂的筛选，挑选出满足多重条件的记录。范例下载1、自动筛选“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。例如：下表是某单位营业情况表。步骤：（1）单击数据区域中任一单元格单击“数据”菜单“筛选”标签“自动筛选”（2）此时，每个字段标题旁边都增加了一个下拉箭头，在相应的字段旁边单击下拉箭头，选择“自定义”命令，弹出“自定义自动筛选方式”对话框，在对话框中输入筛选条件，并选择逻辑运算符，确定即可。筛选中涉及两个条件的情况（筛选出一月销售额在10000元以上，且在50000元以下的商品）可以使用筛选对话框中的第二个条件框。如要还原，只需在“数据”菜单的“筛选”命令下，把“自动筛选”命令前的“”去掉，则可恢复数据表原样。另外，使用“自动筛选”还可同时对多个字段进行筛选操作，此时各字段间限制的条件只能是“与”的关系。2、高级筛选“高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。例如：要从学生成绩单库中筛选出性别为男，语文70且语文80的记录，对于这类查询，自动筛选显然无法一次完成，需要用高级筛选。步骤如下：（1）将要筛选的数据库的字段标题（性别、语文、语文、数学）复制到一个空白区域A10：D10。下一行输入相关条件，如下图所示：（注：条件区域的格式为：第一行为字段名行，以下各行为相应的条件值，其中同一行条件的关系为“与”，不同行条件的关系为“或”。）（2）单击“数据”菜单下的“筛选”“高级筛选”命令，屏幕出现高级筛选对话框；（3）在数据区域文本框输入筛选区域（包括字段标题）A1：G8，也可用鼠标直接在区域上点取；在条件区域文本框输入A10：D11或用鼠标直接点取；这里在“方式框”选择将筛选结果复制到其他位置，在“复制到”文本框输入A14：G20，或由鼠标直接点取；若“方式框”选择原地显示，则不必定义复制区域。 （4）按确定按钮。3、两种筛选操作的比较“自动筛选”一般用于条件简单的筛选操作，符合条件的记录显示在原来的数据表格中，操作起来比较简单，初学者对“自动筛选”也比较熟悉。若要筛选的多个条件间是“或”的关系，或需要将筛选的结果在新的位置显示出来那只有用“高级筛选”来实现了。 一般情况下，“自动筛选”能完成的操作用“高级筛选”完全可以实现，但有的操作则不宜用“高级筛选”，这样反而会使问题更加复杂化了，如筛选最大或最小的前几项记录。4、高级筛选的应用利用Excel的“高级筛选”功能，我们可以把符合某种条件的数据挑选并保存到其他区域中。 （1）同时满足多个条件数据的筛选要求：在“员工基本情况登记表”中，把“职称”为“高级工程师”，并且“学历”为“大学”的人员挑选出来。 步骤：1）先将条件列标题（学历、职称）和条件（大学、高级工程师）输入到某个单元格区域中。 2）选中数据表中任意一个单元格，执行“数据筛选高级筛选”命令，打开“高级筛选”对话框。 注意：此时，系统自动将数据区域填入到“列表区域”右侧的方框中，如果区域不正确，请重新填写。 3）按“条件区域”右侧的红色按钮，对话框变成浮动状态条状，选中上述保存条件的单元格区域，再按浮动条右侧的红色按钮返回到“高级筛选”对话框中。 4）选中“将筛选结果复制到其他位置”选项，然后在下面的“复制到”右边的方框中输入保存区域的第一个单元格地址（如$O$3）5）按下“确定”按钮，符合条件的数据即该被复制到相应的区域中。 （2）并列满足多个条件数据的筛选要求：在“员工基本情况登记表”中，把“职称”为“高级工程师”，“学历”为“大学”的人员都挑选出来。步骤：1）先将条件列标题（学历、职称）和条件（大学、高级工程师）输入到某个单元格区域中（两个条件位于不同行中）。 2）然后仿照上面第1步的“高级筛选”操作即可。 具体操作的动画显示过程如下：三、分类汇总对于上例中的数据库清单中，班主任想知道全班学生的最高分/最低分、男生的最高分/最低分、女生的最高分/最低分，Excel提供的分类汇总功能可以在同一份数据清单上满足不同的汇总要求。分类汇总可以对数据清单的各个字段按分类逐级进行如求和、均值、最大值、最小值等汇总计算，并将计算结果分级显示出来。在进行分类汇总之前，必须先对数据清单进行排序，使属于同一类的记录集中在一起。如把下图中的数据清单按性别进行排序，使相同性别的记录放在一起。排序之后，就可以按性别这个类对数据清单进行分类汇总。具体步骤如下：（1）单击排序后的数据清单中任一单元格；（2）激活数据菜单；单击分类汇总命令，屏幕显示分类汇总对话框；（3）在分类字段下拉列表框中指定按什么字段进行分类。按什么字段进行排序，就按该字段进行分类汇总；（4）在汇总方式下拉列表框中选择用何种方式进行汇总，此处选择最大值；（5）在选定汇总项列表框中指定对哪些字段进行分类汇总，此处选择总分字段；（6）单击确定按钮，可得到如下图所示汇总结果。在图中，“+”、“”称作分级显示符号，这些符号显示在标志的左边，各符号的含义如下：单击“+”，可显示汇总行或列展开的各级明细数据。单击“”可隐藏各级明细数据，只显示汇总行或列。用户若对分类汇总结果不满意，想回到汇总前的数据清单，可选择数据菜单中的分类汇总命令，在出现的分类汇总对话框中选择全部删除按钮，即可恢复到汇总前的情况。 四、冻结拆分窗口与撤消窗口冻结1、冻结拆分窗口使用工作表查看记录时，如果数据库中的记录较多，往往需要使用滚动条来查看各个表格。但在滚动时表格的标题与字段名会随着数据一起移出屏幕外，造成只看到内容，而看不到标题的结果，给用户造成不必要的麻烦。Excel2003提供的冻结拆分窗口的功能，可以将标题和字段拆分在另一个窗口，并冻结这个窗口，以便于用户的操作。如把前例中标题“学生成绩单”和字段拆分成另一窗口，并加以冻结的操作方法如下：(1) 单击单元格A3，选择其作为窗口的分割点；(2) 单击窗口菜单中的冻结拆分窗口命令。这时当向下滚动工作表窗口的滚动条时，上面的标题条和字段就不会移出窗口，如下图所示。2、撤消窗口冻结当不再需要已建立的冻结窗口时，可以将其撤消。撤消的方法是：单击窗口菜单，选择下拉菜单中的撤消冻结窗口命令即可。五、数据透视表 “数据透视表”功能能够将筛选、排序和分类汇总等操作依次完成，并生成汇总表格，是Excel强大数据处理能力的具体体现。例：利用Excel数据透视表分类求学生成绩平均值。期末统考结束后，教育局要求全市学生的成绩都汇总在如图1的同一个Excel工作表中，并要求求出各学校、各专业、各学科的平均分。全市三个学校、每个学校都有六个专业、每个专业都有五门课、各专业人数不等，总计有1000余人，工作量巨大。但如果采用Excel数据透视表来完成的话就简单多了。步骤：1、创建数据透视表点击菜单命令“数据数据透视表和数据透视图”，打开“数据透视表和数据透视图向导”对话框。第一步，如图2所示，选择“Microsoft Excel数据列表或数据库”及下面的“数据透视表”单选项。第二步，如图3所示，在“选定区域”输入全部数据所在的单元格区域，或者点击输入框右侧的“压缩对话”按钮，在工作表中用鼠标选定数据区域。第三步，在对话框中选定“新建工作表”单选项，以便将创建的数据透视表放到一个新的工作表中，再点击“完成”按钮，如图4所示。这样，就可以建立一个空的数据透视表，并同时显示“数据透视表”工具栏和“数据透视表字段列表”对话框，如图5所示。2、利用数据透视表得到需要的结果根据要求，我们应该得到各学校、各专业、各学科的平均分数。因此，我们应该把“学校”、“专业”作为行字段，而把各学科数据作为数据项。 从“数据透视表字段列表”中，把“学校”拖到数据透视表左侧“将行字段拖至此处”位置，再将“专业”字段拖至“学校”字段的右侧，待鼠标变成“I”字形时松开鼠标，如图6所示。如此就会得到如图7所示的效果。这正是我们想要计算平均分的各个项目。现在，我们只要从“数据透视表字段列表”中依次将学科字段拖至数据透视表中“请将数据项拖至此处”位置，可以得到各学校、各专业、各学科的成绩总和。如图8所示。现在我们将各个求和项改为求平均值就可以了。选中透视表中第一个“求和项：语文”，如图中C4单元格，然后点击“数据透视表工具栏”中“字段设置”按钮，打开“数据透视表字段”对话框。在此对话框“汇总方式”列表中选择“平均值”。如图9所示。确定后，所有各学校、各专业的语文科就由求和改成求平均值了。其它学科照此办理。五门课只要五次这样的操作。与此同时，我们还会得到各学校的各学