E_cel2000入门第七第八讲稿.doc

第七讲 数据的管理与分析 通过对工作表和图表的学习，我们了解了Excel2000有很强的表格处理和图表功能。在数据的管理分析方面，尽管Excel并不是一个真正的数据库管理系统，但在管理数据、操作数据、作数据分析透视等方面，Excel却能实现数据库的管理职能。实际上，Excel是将数据列表用作数据库的。一、 数据列表所谓数据列表是包含相关数据的一系列工作表数据行，例如一批学生成绩、一组商品发货单数据，或一组单位名称和联系电话等等。在数据列表中，第一行数据通常用来作为数据列表的列标题，对列表的内容进行说明，它相当于数据库中的字段名称；其余各行的内容都是由列标题所标识的具体数据组成，每一行就象是数据库中的一个记录。在执行数据列表中数据的排序、筛选、分类汇总时，Excel会自动将数据列表视作一个数据库。数据列表不同与一般的工作表，在数据列表中每列必须有列名，且每一列必须是同类型的数据。可以说数据列表是一种特殊的工作表。例如，上一节中介绍过的“学生公共课成绩表”就可以看作是一个数据列表，如图7-1所示。图7-1 数据列表示意图1. 数据列表数据的输入在工作表中建立一张数据列表后，就可以输入数据了。在建立数据列表时需要注意以下几点：l 一个工作表上最好只建一个数据列表；l 数据列表中最好不要有空白行和空白列；l 在数据列表中的第一行一定是列表头（字段名），名字必须唯一；l 数据列表中同一列上的数据类型要完全相同。l 如果工作表中还有其它的数据，那么数据列表要与这些数据间留出至少一个空白列和一个空白行；数据列表既可像一般工作表一样进行输入和编辑，又可通过“数据”菜单“记录单” 命令图7-2 “记录单”对话框查看、更改、添加及删除数据列表中的记录。在图7-1所示的数据列表中选定任一单元格，选择“数据”菜单“记录单”命令，屏幕显示图7-2所示记录单对话框，一个记录单一次只显示一个完整的记录。如果在记录单上输入或编辑数据，Excel将在数据列表中及时更改相应的单元格内容。2. 列表数据的编辑 数据列表的编辑可以和一般的工作表数据的编辑一样进行，由于数据列表的特殊性，对数据修改后，要注意保证数据列表中数据的完整性。所以数据列表通常是以记录（一行）或字段（一列）为单位进行编辑的。（1）添加新记录可以在数据列表的末尾依次输入数据，添加一条新记录，不过要保持每一列数据类型的一致性。（2）插入记录可以先插入空白的行，然后再在空白行中输入数据。（3）删除记录选中要删除的一个或多个记录（行），然后选择“编辑”菜单 “删除”命令项（注意！不是用“清除”）。（4）增加字段可以先插入空白的列，然后在空白的列中输入字段名及具体的数据。（5）删除字段选中要删除的一个或多个字段（列），然后选择“编辑”菜单 “删除”命令项，删除字段实际上是删除字段所在的列。需要注意的是：如果在一张工作表中有多个数据列表，那么，在插入和删除记录、字段时，就不能简单地插入或删除行和列，而只能是插入或删除局限在本数据列表范围的单元格区域中的数据。二、 记录（行）的排序Excel2000的排序功能允许对数据列表中的所有记录（行）按照某种约定重新安排次序，从而实现用户按自己的要求对数据列表进行组织和管理。1. 简单排序直接使用“常用”工具栏上的“升序”、“降序”按钮就可以实现简单的数据排序。例如要对图4-93中“计算机”成绩作重新排序，可以这样操作：单击“学生公共课成绩表”中“计算机”列上的任一单元格选择“常用”工具栏“降序”（“升序”）按钮（如图7-3所示），即可将“计算机”成绩从高（低）到低（高）重新排列。 “升序”按钮 “降序”按钮图7-3 “常用”工具栏上的“降序”/“升序”按钮 2. 复杂数据排序如果排序要求复杂一点，就需要使用“数据”菜单的“排序”命令了。例如在“学生公共课成绩表”中先按“总分”降序排序，若总分相等，再按“语文”降序排序，语文相等，再按“数学”降序排序。这个排序过程已不再局限于单列之中了，所以必须使用“数据”菜单的“排序”命令。操作步骤如下：第一步 单击数据列表区域内的任何一个单元格；第二步 选择“数据”菜单“排序”命令项弹出对话框，如图7-4所示；第三步 在“排序”对话框中设置排序条件；第四步 单击“确定”按钮，完成排序操作完成。图7-4 “排序”对话框第三步中排序条件的设置：Excel在排序时以“主要关键字”作为排序的依据，当主要关键字相同时按“次要关键字”排序，如果次要关键字又相同，再考虑“第三关键字”。每个关键字有“递增”和“递减”两种排列顺序。在Excel中，用字段名作为排序的关键字。在排序中必须指明主要关键字。其他的关键字可以没有。可以在对话框的“当前数据清单”区设置“有标题行”或“没有标题行”。Excel据此确定数据列表是否有标题行（字段名）。一般Excel会自动判别数据库中是否有标题行，所以通常也不需要设置。如果有标题行，而指定的是“无标题行”，就会将标题行作为数据记录排序到数据列表中。请记住：如果排序结果不对，要立刻用菜单命令“编辑”菜单“撤消排序”，把刚才的操作撤消，恢复数据列表原来的样子。 Excel默认状态为按字母顺序排序，如果需要按时间顺序（对月份或星期）数据排序，可以自定义排序次序：单击“排序”对话框“选项”按钮，然后在弹出的“排序选项”对话框（见图7-5）中进行设置。对话框中的“自定义排序次序”只能作用于“主要关键字”下拉列表框中指定的数据列。如果要使用自定义排序次序对多个数据列排序，请分别对每一列执行一次排序操作。如果希望按特殊顺序排序，例如百家姓，则要使用自定义序列。图7-5 “排序选项”，对话框如果希望根据行的数据对数据列排序，则可用下面的方法：第一步 在“排序”对话框中，单击“选项”按钮；第二步 在“排序选项”对话框的“方向”框中设置“按行排序”，单击“确定”；第三步 在“主要关键字”和“次要关键字”中，选定需要排序的数据行，单击“确定”三、 数据的筛选 要显示出数据列表中满足给定条件的记录行数据，把不满足条件的数据暂时隐藏起来，可用自动筛选来达到要求。Excel提供了自动筛选和高级筛选两种方法，其中自动筛选较简单，而高级筛选的功能较强，可以利用复杂的筛选条件进行筛选。1自动筛选在“学生公共课成绩表”中插入“性别”、“出生年月”两列数据：如图7-6所示。图7-6 插入“性别”、“出生年月”两列数据的“学生公共课成绩表”（l）自动筛选操作 自动筛选的操作步骤如下：第一步 选定数据列表中的任一单元格；第二步 选择“数据”菜单“筛选”命令项“自动筛选”命令。此时在字段名（列标题）的右下角显示一个下拉控制箭头，如图7-7所示。第三步 单击某一字段名的下拉控制箭头，出现下拉列表。在下拉列表中，通常包括该字段中每一独有的选项，另外还有五个选项：全部、前十个、自定义、空白、非空白。第四步 在下拉列表中单击某一个具体的值，这时符合条件的记录被显示，不符合筛选条件的记录均隐藏起来。 例如，单击“数学” 字段名的下拉箭头按钮，然后在列表中选择87，则所有数学成绩是87分的学生记录都筛选出来了。调选出的记录所在加列号变成了蓝色。 如果在下拉列表中选定“空白”，就仅显示该字段为空白的那些记录，选定“非空白”则仅显示该字段不为空白的那些记录。 恢复隐藏的记录恢复的方法有：图7-7 “自动筛选”窗口l 单击“数据”菜单“筛选”命令项“全部显示”命令，可恢复显示数据库所有的记录，这时菜单中“自动筛选”命令旁的“”仍存在，所以筛选的下拉按钮也在，仍可以继续筛选。l 选择“数据”菜单 “筛选”命令项“自动筛选”命令，此时“自动筛选”旁的“”消失，结束自动筛选，数据列表恢复成原样。l 最简单的方法是：只须在字段名的下拉菜单中选择“全部”即可。（2）自动筛选的“自定义” 在图4-99中，如果单击“数学”字段下拉表中的“自定义”项，就会弹出“自定义自动筛选方式”对话框，如图7-8所示。在对话框中可以自定义自动筛选的条件，这些条件当然不像是选择某一个值那么简单。 在对话框的左下拉列表中可以规定关系操作符（大于、等于、小于等等），在右下拉列表中则可以规定字段值，而且两个比较条件还能以“或者”或“并且”的关系组合起来形成复杂的条件。 例如，可以自定义筛选条件为数学成绩在85分和95分之间（大于等于85并且小于等于95），见图4-100。图7-8“自定义自动筛选方式”对话框通过对多个字段的依次自动筛选，就可以叠加为复杂一些的筛选操作。例如要筛选出英语和数学成绩都在85分以上的学生的记录，可以先筛选出“英语成绩在85分以上”的学生记录，然后在已经筛选出的记录中继续筛选“数学成绩在85分以上”的记录。2. 高级筛选对于复杂的筛选条件，可以多次使用“自动筛选”来叠加完成。但如果使用“高级筛选”功能，既直观又方便，使用效率上事半功倍。使用“高级筛选”的关键是学会如何设置由用户自定义的复杂组合条件，这些组合条件常常是放在一个称为条件区域的单元格区域中。（1）筛选的条件区域使用“高级筛选”时需要一个“条件区域”，条件区域包括两个部分：标题行（也称字段名）；一行或多行的条件行。创建一个条件区域的操作步骤如下：第一步 在数据列表记录的下面准备好一个空白区域；第二步 在此空白区域的第一行输入字段名作为条件名行，最好是把字段名行整个都复制过来，以免输入时因大小写或有多余的空格而造成不一致；第三步 在字段名的下行开始输入条件。（2）筛选的条件 简单比较条件简单条件是指只用一个简单的比较运算 ( =、=、=、 )表示条件。在条件区域字段名正下方的单元格内输入条件，如：姓名英语数学8085当是等于（=）关系时。等号“=”可以省略。当某个字段名下没有条件时，允许空白，但是不能加上空格，否则将得不到正确的筛选结果。对于字符型字段，在条件行中可以使用通配符“*”及“？”。字符的大小比较按照字母顺序进行。对于汉字，则以拼音为顺序。如果是汉字、字符串用于比较条件中，那么必须用双引号“”括起来（如“男”、“Beijing”等）。例题一：在“学生公共课成绩表”中筛选出满足条件“数学=85分且英语80分”的记录，条件区域的设置如图7-9所示。图7-9 条件区域的设置 组合条件对于需要使用多重条件在数据列表中筛选记录，就必须把条件组合起来。基本的形式有两种：在同一行内的条件表示AND（“与”）关系，也就是“所有条件为真（成立）结论才真”。例如：要筛选出“所有姓刘并且计算机成绩不低于86分”的记录，条件表示为：姓名计算机刘*=86 如果要建立一个条件为某字段的值的范围，必须在同一行的不同列中为每一个条件建立字段名。例如：要筛选出“所有姓刘并且英语成绩在70至79之间”的记录，条件表示为：姓名英语英语刘*=70=8060 如果组合条件为：“姓刘或英语分数低于60分”。在条件区域中应写成：姓名 英语 刘*=85分或语文85分或英语80分”的设置条件；规则二：当在数据列表的一个字段中使用多重条件时，必须在条件区域中重复使用同一字段名，这样可以在同一行的不同列中输入每一个条件。规则三：在一个条件区域中使用不同字段或同一字段的逻辑OR关系时，必须在不同行中输入条件。例题二：在“学生公共课成绩表”中筛选出满足条件“数学=85分或语文85分或英语80分”的记录,条件区域的设置如图7-10（见上页）所示。 计算条件上面介绍的筛选方法都是用数据列表字段的值与条件区域中的条件作比较。实际上，如果用数据列表的字段（一个或几个）根据条件计算出来的值进行比较，也可以筛选出所需的记录。操作方法如下：在条件区域第一行中输入一个不同于数据列表中任何字段名的条件名（空白也可以）。如果计算条件的条件名与某一字段名相同，Excel将认为是字段名。在条件名正下方的单元格中输入计算条件公式。在公式中通过引用字段的第一条记录的单元格地址（用相对地址）去引用数据库字段。公式计算后得到的结果必须是逻辑值TRUE或FALSE。 例题三：从“学生公共课成绩表”中筛选出语文和数学两门课分数之和大于170的学生记录。 分析：解决本例可以用计算条件。假设语文、数学分别在F、E列，第一条记录在第二行，计算条件就是F2E2170。在条件名行增加条件名“语文数学”，在其下输入计算条件。表示为：语文数学=F2+E2170图7-11 “计算条件”的设置处理过程如图7-11图7-13所示。（3）高级筛选的操作高级筛选的操作步骤为：第一步 按照前面所讲的方法建立条件区域；第二步 在数据列表区内选定任一个单元格；第三步 选择“数据”菜单“筛选”命令项“高级筛选”命令，弹出“高级筛选”图7-12 利用“计算条件”作“高级筛选”图7-13 满足“计算条件”的筛选结果对话框，如图7-14所示；第四步 在“高级筛选”对话框中选中“在原有区域显示筛选结果”选项；第五步 输入“条件区域”。“数据区域”是自动获取的，如果不正确，可以更改；第六步 单击“确定”按钮，屏幕显示筛选出符合条件的记录。图7-14 “高级筛选”对话框若希望把筛选出的结果复制到一个新的位置，则在“高级筛选”对话框中选定“将筛选结果复制到其地位置”选项，并且还要在“复制到”区域中输入要复制到的目的区域的首单元地址。注意，以首单元地址为左上角的区域必须足够多的空位存放筛选结果，否则将覆盖该区域的原有数据。若希望把筛选的结果复制到另外的工作表中，则必须首先激活目标工作表，然后再选“数据”菜单“筛选”命令项“高级筛选”项，在“高级筛选”对话框中，输入“数据区域”和“条件区域”时要注意加上工作表的名称，如数据区域为Sheet1！A1:G15，条件区域为Sheet1！A20:B22，而复制到的区域直接为A1。这个A1是当前的活动工作表（比如Sheet2）的A1，而不是源数据区域所在的工作表Sheet1。如果不想从一个数据列表中提取全部字段，就必须先定义一个提取区域。在提取区域的第一行中给出要提取的字段及字段的顺序。这个提取区域就作为高级筛选的结果“复制到”的目的区域的地址，Excel2000会自动在该区域中所要求的字段下面列出筛选结果。例如把 A15:C15作为提取区域，区域中输入的内容如下：单元格A15B15C15内容姓名总分平均分图 7-15 提取数据列表的部分字段的数据到新区域然后在“复制到”区域中输入A15:C15，由条件区域所指定的记录的姓名、总分、平均分三个字段的数据将被复制到提取区域A15:C15下面的新位置上。例如，把“学生公共课成绩表”中的姓名、总分、平均分三个字段的数据复制到提取区域A15:C15下面的新位置，执行结果如图7-15所示。如果要删除某些符合条件的记录，可以在筛选后（在原有区域显示筛选结果），选中这些筛选结果，选“编辑”菜单“删除”命令项即可。在“高级筛选”对话框中，选中“选择不重复的记录”复选框后再筛选，得到的结果中将剔除相同的记录（但必须同时选择“将筛选结果复制到其地位置”此操作才有效）。这个特性使得用户可以将两个相同结构的数据列表合并起来，生成一个不含有重复记录的新数据列表。此时筛选的条件为“无条件”，具体做法是：在条件区域只写一个条件名，条件名下面不要写任何的条件，这就是所谓的“无条件”。四、分类汇总在对数据列表中的数据进行分析处理时，有时需要多次重复输入公式或调用函数来计算这些数据的总和、平均值以及乘积等。如果利用Excel中的分类汇总命令在数据列表中插入分类汇总值，那么可以使工作效率再次提高。1. 为数据列表插入分类汇总分类汇总是指将数据列表中的记录先按某个字段进行排序分类，然后再对另一字段进行汇总统计。汇总的方式包括求和、求平均值、统计个数等等。为便于讨论问题，现在“学生公共课成绩表”中再插入“籍贯”列标题及数据。 例题一：在“学生公共课成绩表”中按“性别”分类统计出男、女学生四门课程各自的平均成绩，操作步骤如下：第一步 按“性别”字段将记录行排序；第二步 选择“数据”菜单“分类汇总”命令项弹出“分类汇总”对话框，如图7-16；第三步 在“分类字段”下拉列表框中选择“性别”字段；注意这里选择的字段就是在第1步排序时的主关键字。第四步 在“汇总方式”下拉列表框中选择“平均值”；第五步 “选定汇总项”选定数学、语文、英语、计算机复选框。此处可根据要求选择多项。第六步 单击“确定”按钮即可完成。 图7-16 “分类汇总”对话框分类汇总的结果如图7-17所示。如果要撤消分类汇总，可以选“数据”菜单“分图7-17 “分类汇总”后的结果类汇总”命令项进入“分类汇总”对话框单击“全部删除”按钮，即可恢复原来的数据列表清单。2. 分级显示工作表数据在对数据进行了分类汇总之后，如何来查看数据列表中的明细数据或单独查看汇总总计呢？在图4-106中分类汇总数据的左端出现了一个区域和一些带有“1”、“2”、“+”、“-”等符号的按钮，单击这些按钮符号，数据列表中显示的数据就会发生改变。带有“+”的符号按钮被称为显示明细数据按钮，单击它可以在数据列表中显示出数据列表的明细数据，事实上，图7-17所显示的就是明细数据。带有“-”的符号按钮被称为隐藏明细数据按钮，单击它可以在数据列表中隐藏数据列表的明细数据，如图7-18所显示的就是隐藏了“女”学生四门课程平均值的明细数据。如果要指定分级显示的级别，可单击左侧上方标有“1”、“2”、“3”字样的按钮，例如，如果要在分级显示中包括一个显示级别，单击标有“1”符号的按钮，则数据列表区只显示“总平均值”，如图7-19所示。图7-18 隐藏了“女”学生四门课程平均值的明细数据图7-19 包含一个显示级别的分级显示 五、数据透视表分类汇总适合于按一个字段进行分类，对一个或多个字段进行汇总的问题，在日常工作中，很多问题要求按多个字段进行分类并且汇总，此时如果用前面介绍的用分类汇总方法来解决就显得无能为力。为此，Excel为用户提供了用数据透视表功能来解决问题。数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格。它可以转换行和列以查看源数据的不同汇总结果，可以显示不同页面以筛选数据，还可以根据需要显示区域中的明细数据。我们先建立一个“学生身体素质表”，列标题有“编号”、“姓名”、“性别”、“出生年月”、“籍贯”、“专项”、“身高”、“体重”等，列标题下有15条记录（如图7-20所示），结合这一数据列表，利用数据透视表的向导功能，了解一下数据透视表的基础知识。图7-20 “学生身体素质表”1. 数据透视表的建立 比如要统计“学生身体素质表”中各专项中男、女生的人数，此时既要按所学专项分类，又要按性别分类，用数据透视表来解决问题，操作步骤是：第一步 打开“学生身体素质表”，用鼠标单击数据列表中任一单元格；第二步 选择“数据”菜单“数据透视表和图表报告”命令显示“数据透视表向导-3步骤之l”对话框，如图7-21所示；“步骤之1”对话框主要是用来指定数据源（默认为“Microsoft Excel数据清单或数据库”，一般选择该项）和创建的报表类型（默认为数据透视表，一般也选择该项）；图7-21 “数据透视表向导-3步骤之l”对话框单击“下一步”按钮，出现如图7-22所示“数据透视表向导-3步骤之2”对话框。第三步 “步骤之2”对话框主要用来选定数据区域，一般情况下选定整个列表， 图7-22 “数据透视表向导-3步骤之2”对话框用户可键入新值重新选定区域，单击“下一步”按钮，出现如7-23所示“数据透视表向导-3步骤之3”对话框。图7-23 “数据透视表向导-3步骤之3”对话框图7-24 数据透视表布局示意图第四步 “步骤之3”对话框决定数据透视表的放置位置，选择“新建工作表”单选钮，将数据透视表放置在工作薄新建的工作表中，并成为活动工作表。选择“现有工作表”则还须指定数据透视表在现有工作表放置的位置。选“新建工作表”，单击“完成”按钮，此时屏幕显示数据透视表布局示意图，如图7-24所示。第五步 在数据透视表的布局示意图的旁边，弹出的“数据透视表”工具栏中列出了所有字段，将要分类的字段可拖入示意图的行、列位置，就成为透视表的行、列标题。将需要汇总的字段拖入示意图的数据区，拖入页位置的字段将成为分页显示的依据。例题中将“性别”字段拖曳到行位置，“专项”字段拖曳到列位置，数据项区中拖入的是“姓名”字段，这样操作之后就出现如图7-25所示的结果。图7-25 生成“数据透视表”拖入数据区的汇总对象如果是非数字型字段，则默认为对其计数，如为数字型字段则默认为求和。2. 透视表的编辑修改数据透视表建好之后，根据实际情况，随时可作出修改、编辑，具体操作步骤是：选择“视图”菜单“工具栏”命令项“数据透视表”命令弹出“数据透视表”工具栏（更简捷的办法是：鼠标指向屏幕上工具栏的空白区，单击右键，弹出工具栏名称框，选中其中的“数据透视表”即可），使用该工具栏中的按钮可用于对数据透视表的修改。（1）增删数据透视表中的字段例如，将图7-25之中“数据透视表”的行字段“性别”删去，增加一个“籍贯”行字段，具体操作步骤如下：第一步 用上述方法将“数据透视表”工具栏显示在屏幕上；第二步 将“性别”行字段拖出数据透视表，将“籍贯”拖入数据透视表的行字段。如图7-26所示，将行、列、页、数据字段移出表示删除该字段，移入表示增加该字段。图7-26 拖出“性别”移入“籍贯”后的“数据透视表”（2）改变汇总方式不同类型的数据有不同的默认汇总方式。用户如果不想使用默认的汇总方式，可以改变为其它如平均值、最大(小)值的汇总方式。比如前面曾对“体重”求和。现在希望改为统计体重的平均值。其操作步骤如下：图7-27 从“数据透视表”下拉式菜单中选定“字段设置”第一步 选择数据透视表的数据区中任一单元格，如求和数据所在单元格；第二步 单击“数据透视表”工具栏“数据透视表”下拉式按钮弹出下拉式菜单“字段设置”命令项显示“数据透视表字段”对话框，如图7-27所示；第三步 对话框从汇总方式列表框中选择“平均值”，如图7-28所示；图7-28 “数据透视表字段”对话框第四步 单击“确定”按钮，改变汇总方式完成，结果如图