搜索
2第七讲数据的管理与分析通过对工作表和图表的学习，我们了解了Excel2000有很强的表格处理和图表功能。在数据的管理分析方面，尽管Excel并不是一个真正的数据库管理系统，但在管理数据、操作数据、作数据分析透视等方面，Excel却能实现数据库的管理职能。实际上，Excel是将数据列表用作数据库的。一、数据列表所谓数据列表是包含相关数据的一系列工作表数据行，例如一批学生成绩、一组商品发货单数据，或一组单位名称和联系电话等等。在数据列表中，第一行数据通常用来作为数据列表的列标题，对列表的内容进行说明，它相当于数据库中的字段名称；其余各行的内容都是由列标题所标识的具体数据组成，每一行就象是数据库中的一个记录。在执行数据列表中数据的排序、筛选、分类汇总时，Excel会自动将数据列表视作一个数据库。数据列表不同与一般的工作表，在数据列表中每列必须有列名，且每一列必须是同类型的数据。可以说数据列表是一种特殊的工作表。例如，上一节中介绍过的“学生公共课成绩表”就可以看作是一个数据列表，如图7-1所示。图7-1数据列表示意图1.数据列表数据的输入在工作表中建立一张数据列表后，就可以输入数据了。在建立数据列表时需要注意以下几点：图7-2“记录单”对话框查看、更改、添加及删除数据列表中的记录。在图7-1所示的数据列表中选定任一单元格，选择“数据”菜单→“记录单”命令，屏幕显示图7-2所示记录单对话框，一个记录单一次只显示一个完整的一个工作表上最好只建一个数据列表；数据列表中最好不要有空白行和空白列；在数据列表中的第一行一定是列表头（字段名），名字必须唯一；数据列表中同一列上的数据类型要完全相同。如果工作表中还有其它的数据，那么数据列表要与这些数据间留出至少一个空白列和一个空白行；数据列表既可像一般工作表一样进行输入和编辑，又可通过“数据”菜单→“记录单”命令3记录。如果在记录单上输入或编辑数据，Excel将在数据列表中及时更改相应的单元格内容。2.列表数据的编辑数据列表的编辑可以和一般的工作表数据的编辑一样进行，由于数据列表的特殊性，对数据修改后，要注意保证数据列表中数据的完整性。所以数据列表通常是以记录（一行）或字段（一列）为单位进行编辑的。（1）添加新记录可以在数据列表的末尾依次输入数据，添加一条新记录，不过要保持每一列数据类型的一致性。（2）插入记录可以先插入空白的行，然后再在空白行中输入数据。（3）删除记录选中要删除的一个或多个记录（行），然后选择→“编辑”菜单→“删除”命令项（注意！不是用“清除”）。（4）增加字段可以先插入空白的列，然后在空白的列中输入字段名及具体的数据。（5）删除字段选中要删除的一个或多个字段（列），然后选择→“编辑”菜单→“删除”命令项，删除字段实际上是删除字段所在的列。需要注意的是：如果在一张工作表中有多个数据列表，那么，在插入和删除记录、字段时，就不能简单地插入或删除行和列，而只能是插入或删除局限在本数据列表范围的单元格区域中的数据。二、记录（行）的排序Excel2000的排序功能允许对数据列表中的所有记录（行）按照某种约定重新安排次序，从而实现用户按自己的要求对数据列表进行组织和管理。1.简单排序直接使用“常用”工具栏上的“升序”、“降序”按钮就可以实现简单的数据排序。例如要对图4-93中“计算机”成绩作重新排序，可以这样操作：单击“学生公共课成绩表”中“计算机”列上的任一单元格→选择“常用”工具栏→“降序”（“升序”）按钮（如图7-3所示），即可将“计算机”成绩从高（低）到低（高）重新排列。“升序”按钮“降序”按钮图7-3“常用”工具栏上的“降序”/“升序”按钮2.复杂数据排序如果排序要求复杂一点，就需要使用“数据”菜单的“排序”命令了。例如在“学生公共课成绩表”中先按“总分”降序排序，若总分相等，再按“语文”降序排序，语文相等，再按“数学”降序排序。这个排序过程已不再局限于单列之中了，所以必须使用“数据”菜单的“排序”命令。操作步骤如下：第一步单击数据列表区域内的任何一个单元格；第二步选择“数据”菜单→“排序”命令项→弹出对话框，如图7-4所示；第三步在“排序”对话框中设置排序条件；第四步单击“确定”按钮，完成排序操作完成。4图7-4“排序”对话框第三步中排序条件的设置：Excel在排序时以“主要关键字”作为排序的依据，当主要关键字相同时按“次要关键字”排序，如果次要关键字又相同，再考虑“第三关键字”。每个关键字有“递增”和“递减”两种排列顺序。在Excel中，用字段名作为排序的关键字。在排序中必须指明主要关键字。其他的关键字可以没有。可以在对话框的“当前数据清单”区设置“有标题行”或“没有标题行”。Excel据此确定数据列表是否有标题行（字段名）。一般Excel会自动判别数据库中是否有标题行，所以通常也不需要设置。如果有标题行，而指定的是“无标题行”，就会将标题行作为数据记录排序到数据列表中。请记住：如果排序结果不对，要立刻用菜单命令“编辑”菜单→“撤消排序”，把刚才的操作撤消，恢复数据列表原来的样子。Excel默认状态为按字母顺序排序，如果需要按时间顺序（对月份或星期）数据排序，图7-5“排序选项”，对话框如果希望根据行的数据对数据列排序，则可用下面的方法：第一步在“排序”对话框中，单击“选项”按钮；第二步在“排序选项”对话框的“方向”框中设置“按行排序”，单击“确定”；第三步在“主要关键字”和“次要关键字”中，选定需要排序的数据行，单击“确定”三、数据的筛选要显示出数据列表中满足给定条件的记录行数据，把不满足条件的数据暂时隐藏起来，可用自动筛选来达到要求。Excel提供了自动筛选和高级筛选两种方法，其中自动筛选较简单，而高级筛选的功能较强，可以利用复杂的筛选条件进行筛选。1．自动筛选在“学生公共课成绩表”中插入“性别”、“出生年月”两列数据：如图7-6所示。可以自定义排序次序：单击“排序”对话框→“选项”按钮，然后在弹出的“排序选项”对话框（见图7-5）中进行设置。对话框中的“自定义排序次序”只能作用于“主要关键字”下拉列表框中指定的数据列。如果要使用自定义排序次序对多个数据列排序，请分别对每一列执行一次排序操作。如果希望按特殊顺序排序，例如百家姓，则要使用自定义序列。5图7-6插入“性别”、“出生年月”两列数据的“学生公共课成绩表”（l）自动筛选操作①自动筛选的操作步骤如下：第一步选定数据列表中的任一单元格；第二步选择“数据”菜单→“筛选”命令项→“自动筛选”命令。此时在字段名（列标题）的右下角显示一个下拉控制箭头，如图7-7所示。第三步单击某一字段名的下拉控制箭头，出现下拉列表。在下拉列表中，通常包括该字段中每一独有的选项，另外还有五个选项：全部、前十个、自定义、空白、非空白。第四步在下拉列表中单击某一个具体的值，这时符合条件的记录被显示，不符合筛选条件的记录均隐藏起来。例如，单击“数学”字段名的下拉箭头按钮，然后在列表中选择87，则所有数学成绩是87分的学生记录都筛选出来了。调选出的记录所在加列号变成了蓝色。如果在下拉列表中选定“空白”，就仅显示该字段为空白的那些记录，选定“非空白”则仅显示该字段不为空白的那些记录。②恢复隐藏的记录恢复的方法有：图7-7“自动筛选”窗口单击“数据”菜单→“筛选”命令项→“全部显示”命令，可恢复显示数据库所有的记录，这时菜单中“自动筛选”命令旁的“√”仍存在，所以筛选的下拉按钮也在，仍可以继续筛选。选择“数据”菜单→“筛选”命令项→“自动筛选”命令，此时“自动筛选”旁的“√”消失，结束自动筛选，数据列表恢复成原样。6最简单的方法是：只须在字段名的下拉菜单中选择“全部”即可。（2）自动筛选的“自定义”在图4-99中，如果单击“数学”字段下拉表中的“自定义”项，就会弹出“自定义自动筛选方式”对话框，如图7-8所示。在对话框中可以自定义自动筛选的条件，这些条件当然不像是选择某一个值那么简单。在对话框的左下拉列表中可以规定关系操作符（大于、等于、小于等等），在右下拉列表中则可以规定字段值，而且两个比较条件还能以“或者”或“并且”的关系组合起来形成复杂的条件。例如，可以自定义筛选条件为数学成绩在85分和95分之间（大于等于85并且小于等于95），见图4-100。图7-8“自定义自动筛选方式”对话框通过对多个字段的依次自动筛选，就可以叠加为复杂一些的筛选操作。例如要筛选出英语和数学成绩都在85分以上的学生的记录，可以先筛选出“英语成绩在85分以上”的学生记录，然后在已经筛选出的记录中继续筛选“数学成绩在85分以上”的记录。2.高级筛选对于复杂的筛选条件，可以多次使用“自动筛选”来叠加完成。但如果使用“高级筛选”功能，既直观又方便，使用效率上事半功倍。使用“高级筛选”的关键是学会如何设置由用户自定义的复杂组合条件，这些组合条件常常是放在一个称为条件区域的单元格区域中。（1）筛选的条件区域使用“高级筛选”时需要一个“条件区域”，条件区域包括两个部分：标题行（也称字段名）；一行或多行的条件行。创建一个条件区域的操作步骤如下：第一步在数据列表记录的下面准备好一个空白区域；第二步在此空白区域的第一行输入字段名作为条件名行，最好是把字段名行整个都复制过来，以免输入时因大小写或有多余的空格而造成不一致；第三步在字段名的下行开始输入条件。（2）筛选的条件①简单比较条件简单条件是指只用一个简单的比较运算(=、、=、、=、)表示条件。在条件区域字段名正下方的单元格内输入条件，如：姓名英语数学＞80＞＝85当是等于（=）关系时。等号“=”可以省略。当某个字段名下没有条件时，允许空白，但是不能加上空格，否则将得不到正确的筛选结果。7对于字符型字段，在条件行中可以使用通配符“*”及“？”。字符的大小比较按照字母顺序进行。对于汉字，则以拼音为顺序。如果是汉字、字符串用于比较条件中，那么必须用双引号“”括起来（如“男”、“Beijing”等）。例题一：在“学生公共课成绩表”中筛选出满足条件“数学=85分且英语80分”的记录，条件区域的设置如图7-9所示。图7-9条件区域的设置②组合条件对于需要使用多重条件在数据列表中筛选记录，就必须把条件组合起来。基本的形式有两种：在同一行内的条件表示AND（“与”）关系，也就是“所有条件为真（成立）结论才真”。例如：要筛选出“所有姓刘并且计算机成绩不低于86分”的记录，条件表示为：如果要建立一个条件为某字段的值的范围，必须在同一行的不同列中为每一个条件建立字段名。例如：要筛选出“所有姓刘并且英语成绩在70至79之间”的记录，条件表示为：在不同行内的条件表示OR（“或”）的关系,也就是“所有条件中只要有一个条件为真（成立）结论就真（成立）”。例如：要筛选出满足条件“姓刘并且英语分数大于等于80或英语分数低于60分”的记录。这个组合条件在条件区域中应表示为：如果组合条件为：“姓刘或英语分数低于60分”。在条件区域中应写成：由以上的例子可以总结出组合条件的表示规则如下：姓名计算机刘*=86姓名英语英语刘*=7080姓名英语刘*=8060姓名英语刘*608规则一：当在数据列表的不同字段中使用多重条件时，必须在同一行的不同列中输入；图7-10条件“数学=85分或语文85分或英语80分”的设置条件；规则二：当在数据列表的一个字段中使用多重条件时，必须在条件区域中重复使用同一字段名，这样可以在同一行的不同列中输入每一个条件。规则三：在一个条件区域中使用不同字段或同一字段的逻辑OR关系时，必须在不同行中输入条件。例题二：在“学生公共课成绩表”中筛选出满足条件“数学=85分或语文85分或英语80分”的记录,条件区域的设置如图7-10（见上页）所示。③计算条件上面介绍的筛选方法都是用数据列表字段的值与条件区域中的条件作比较。实际上，如果用数据列表的字段（一个或几个）根据条件计算出来的值进行比较，也可以筛选出所需的记录。操作方法如下：在条件区域第一行中输入一个不同于数据列表中任何字段名的条件名（空白也可以）。如果计