计算机基础上机实验精华版十

1、计算机科学导论实验教程实验十、Excel数据管理一、实验目的：1、掌握数据表单的建立方法。2、掌握数据表单的基本编辑方法。3、掌握数据表单常用的查询和统计方法。二、实验设备和软件：1、硬件设备：Pentium 以上微型计算机。2、软件设备：Windows XP、Excel 2002或以上版本。三、实验预备知识：在Excel中，数据表单是包含相似数据组的带标题的一组工作表数据行。可以将“数据表单”看作“数据库”，其中行作为数据库中的记录，列对应数据库中的字段，列标题作为数据库中的字段名称。借助数据表单，Excel能将应用于数据库中的数据管理功能-筛选、排序以及一些分析操作，应用到数据表单中的数据

2、上。使用Excel的数据管理功能，首先必须将表格创建成数据表单。数据表单是一种特殊的表格，其特殊性在于此类表格至少由两个必备部分组成-表结构和纯数据。表结构为数据表单中的第一行标题，Excel将利用这些标题名对数据进行查找、排序以及筛选等操作。纯数据部分则是Excel实施管理功能的对象，该部分不允许有非法数据内容出现。正确创建数据表单应遵守的准则如下：1、在数据表单的第一行中创建列标题，且在同一个数据表单中列标题内容必须唯一。2、列标题与纯数据之间不能用空行隔开。3、同一列数据的数据类型必须相同。4、纯数据区域中不允许出现非法数据，如空记录等。5、如果在工作表中还有其它数据，要与数据表单之间留

3、出至少一个空行和空列。数据表单的创建操作和普通表格的创建方法完全相同。首先，根据数据表单内容创建表结构，然后移到表结构下的第一个空行，键入数据信息完成数据表单的创建工作。筛选功能实现在数据表单中提炼出满足筛选条件的数据，不满足条件的数据只是暂时被隐藏起来（并未真正被删除掉）；一旦筛选条件被撤走，这些数据又重新出现。Excel提供了两种筛选方式：1、自动筛选，通过隐藏不满足筛选条件的数据记录方式来显示满足筛选条件的数据记录。但这种筛选方式存在一定的局限性，它所构造的筛选条件并不完备，如针对同一字段可构造“与”和“或”条件，但在不同字段间，只能够造“与”条件。2、高级筛选，这种筛选方式可适应所有的

4、筛选情况，在构造筛选条件时要注意“与”和“或”条件的不同构造形式。字段名在同一行表示“与”关系，在不同行表示“或”关系。在Excel中，主要提供了两种针对数据表单的数据进行统计的方法，即分类汇总和数据表单统计函数。1、分类汇总，是在利用数据管理功能将数据表单中大量数据明确化和条理化的基础上，利用Excel提供的函数进行数据汇总。汇总时首先将数据分类（排序），然后再将数据按类进行汇总分析处理。使用Excel的“分类汇总”命令，并不需要创建公式，Excel将自动创建公式、插入分类汇总总和行，并自动分级显示数据，结果数据可以打印出来。在进行分类汇总前，要注意先对需要汇总的数据进行分类处理，可通过排序

5、操作实现，没有经过排序的汇总结果是没有意义的。2、数据表单统计函数，Excel除了提供分类汇总功能实现数据记录统计外，还提供了具有等同功能的数据表单统计函数，在使用数据表单统计函数的时候要注意区分统计对象（第二个参数指定）和统计条件（第三个参数指定）的不同。四、实验内容和步骤：1、模仿性实验实验任务1 数据表单的浏览与编辑（1）、打开“职工工资表.XLS”文件，插入一个新的工作表，并将其改名为“数据管理”，将“工资表”工作表区域A2:K22中的内容复制到“数据管理”工作表中，复制位置：以A1为左上角，复制内容：数值。（2）、在“数据管理”工作表中，对工资表中的记录以“年龄”为主关键字，“职称”

6、为次关键字进行排序。（3）、在“数据管理”工作表中，以记录单的方式浏览工资表的每一条记录。（4）、在“数据管理”工作表中，以记录单的方式查找工资表中姓名为“朱丽”的记录，并将其职称改为“副教授”。实验步骤图10-1 以值方式复制数据（1）、数据复制。插入新工作表，并重命名为“数据管理”，选择“工资表”工作表区域A2：K22，单击“编辑”“复制”，选择“数据管理”工作表A1单元格，单击“编辑” “选择性粘贴”，选择其中的“数值”单选钮，单击【确定】按钮。如图10-1所示。图10-2 数据表单排序（2）、数据排序。先单击“数据管理”工作表中工资表范围内的任意单元格，再单击“数据” “排序”，在排序

7、对话框中选择“年龄”为主关键字，选择“职称”为次关键字，单击【确定】按钮。如图10-2所示。（3）、数据浏览。先单击“数据管理”工作表中工资表范围内的任意单元格，再单击“数据” “记录单”，通过【上一条】和【下一条】按钮浏览记录。如图10-3所示。图10-3 以记录单方式浏览数据（4）、数据编辑。先单击“数据管理”工作表中工资表范围内的任意单元格，再单击“数据” “记录单”，通过【上一条】和【下一条】按钮浏览到姓名为“朱丽”的记录，将其职称改为“副教授”。如图10-4所示。图10-4 以记录单方式编辑数据实验结果图10-5 数据表单基本操作后的结果结果如图10-5所示。实验任务2 数据表单查询

8、-自动筛选和高级筛选的使用。（1）、在“数据管理”工作表中，使用自动筛选方法，检索出“职称”为“副教授”且应发工资>=2000的职工；再进一步找出其中女性职工。（2）、取消自动筛选，使工资表恢复到初始状态。（3）、在“数据管理”工作表中，检索出所有年龄>=35的讲师和工程师（讲师和工程师都有年龄要求），将他们输出到以A27为左上角的区域，条件建立在从A23开始的区域。（4）、在“数据管理”工作表中，筛选出所有姓张或姓李的职工。（5）、在“数据管理”工作表中，筛选出1965年以后出生的职工(含1965年出生者)。实验步骤图10-6 自定义自动筛选条件（1）、自动筛选。先单击“数据管理

9、”工作表中工资表范围内的任意单元格，再单击“数据”“筛选”“自动筛选”，在“职称”旁边的下拉列表中选择“副教授”，进一步在“应发工资”旁边的下拉列表中选择“自定义”，在如图10-6所示的对话框中输入筛选条件，最后在“性别”旁边的下拉列表中选择“女”。结果如图10-7所示。图10-7 自动筛选后的结果（2）、取消自动筛选。单击“数据”“筛选”“全部显示”，或在“自动筛选”状态下，再次单击“数据”“筛选”“自动筛选”。如图10-8所示。图10-8 取消自动筛选（3）、高级筛选。从A23区域开始建立如图所示的条件区域，单击“数据”“筛选”“高级筛选”，在“高级筛选”对话框中，“方式”选择为“将筛选结

10、果复制到其他位置”，“列表区域”选择为A1：K22，“条件区域”选择为你所设定的区域，如A23：B25，“复制到”选择为A27。单击【确定】按钮。如图10-9所示。图10-9 高级筛选结果1（4）、高级筛选。从A34区域开始建立如图所示的条件区域，单击“数据”“筛选”“高级筛选”，在“高级筛选”对话框中，“方式”选择为“将筛选结果复制到其他位置”，“列表区域”选择为A1：K22，“条件区域”选择为你所设定的区域，如A34：A36，“复制到”选择为A38。单击【确定】按钮。如图10-10所示。图10-10 高级筛选结果2（5）、高级筛选。从A47区域开始建立如图所示的条件区域，单击“数据”“筛选

11、”“高级筛选”，在“高级筛选”对话框中，“方式”选择为“将筛选结果复制到其他位置”，“列表区域”选择为A1：K22，“条件区域”选择为你所设定的区域，如A47：A48，“复制到”选择为A50。单击【确定】按钮。如图10-11所示。(注：如果“出生日期”为数值显示格式，可将其换为日期显示格式) 图10-11 高级筛选结果3实验结果结果如图10-7、图10-9、图10-10、图10-11所示。实验任务3 数据表单统计-分类汇总的使用。用分类统计的方式分别按职称求各职称的人数、实发工资总额及平均实发工资，要求三个结果都放在一个汇总表中。实验步骤图10-12 分类汇总计数（1）、求各职称人数。先对“数

12、据管理”工作表中的“工资表”数据按“职称”排序，然后单击“数据”“分类汇总”，在“分类汇总”对话框中，“分类字段”选择“职称”，“汇总方式”选择“计数”，“选定汇总项”选择任意字段，单击【确定】按钮。如图10-12所示。图10-13 分类汇总求和（2）、求实发工资总额。单击“数据”“分类汇总”，在“汇总方式”中选择“求和”，在“选定汇总项”中选择“实发工资”，取消“替换当前分类汇总”前的复选框，单击【确定】按钮。如图10-13所示。（3）、求平均实发工资。单击“数据”“分类汇总”，在“汇总方式”中选择“平均值”，在“选定汇总项”中选择“实发工资”，单击【确定】按钮。如图10-14所示。图10-

13、14 分类汇总平均值（4）、折叠汇总项。在汇总表左上角单击显示级别按钮“1 2 3 4 5”中的“4”，可以隐藏第5级（原始记录细节），而得到仅含汇总项（小计和总计）的表格。如图10-15所示。实验结果结果如图10-15、图10-16所示。图10-15 折叠汇总项图10-16 分类汇总结果实验任务4 数据表单统计-数据表单统计函数的使用。应用数据表单统计函数，在“工资表”工作表中依如图10-17所示格式统计出各职称人员的人数和相关工资数据，其中工资数据要求以两位小数显示。图10-17 各职称工资统计表实验步骤（1）、设置条件格式。在“工资表”工作表中的相应区域输入如图10-17所示的数据。（2

14、）、数据统计。单击B28单元格，在其中输入公式：=DCOUNT($A$2:$K$22,B$26:B$27)，拖动该单元格句柄到H28单元格；单击B29单元格，在其中输入公式：DSUM($A$2:$K$22,11,B$26:B$27)，拖动该单元格句柄到H29单元格；单击B30单元格，在其中输入公式：=DAVERAGE($A$2:$K$22,11,B$26:B$27)，拖动该单元格句柄到H30单元格；单击B31单元格，在其中输入公式：=DMAX($A$2:$K$22,11,B$26:B$27)，拖动该单元格句柄到H31单元格；单击B32单元格，在其中输入公式：=DMIN($A$2:$K$22,1

15、1,B$26:B$27)，拖动该单元格句柄到H32单元格。实验结果结果如图10-18所示。图10-18 各职称工资统计结果2、设计性实验实验任务1 奖学金等级查询在“奖学金”工作表中，检索出所有获奖学金以及“大学英语”课程绩点在3.0以上的未获奖学金的学生记录，将结果输出到以F19为左上角的区域，条件建立在从H2开始的区域。实验结果结果如图10-19所示。图10-19 奖学金等级查询结果实验任务2 足球队出线的确认 根据某次足球赛会的出线规则，通过对某一小组比赛成绩的判断来决定出线球队。判定时首先判断积分，如果积分相等，再判断净胜球的多少来确定比赛的名次。（1）、插入一个新的工作表“比赛出线”，在其中创建如图10-20所示的小组比赛原始记录表，并建立同“课程1”工作表中相同的格式。图10-20 原始比赛纪录（2）、在“