电子表格软件操作实验三 数据管理

实验三数据管理

一、实验目的

1.掌握数据表的排序、筛选。

2.掌握数据的分类汇总。

3.掌握数据透视表的操作。

二、实习内容

1．打开WE2.xls工作簿，将其换名为WE3.xls保存。

2．将“学生成绩表”工作表中的数据复制到Sheet2表中，再将Sheet2重命名为“学生情况表”。

3．在“学生情况表”中插入一列，列标题为“性别”，并将“陈莉”、“张小燕”的性别输入为“女”，其余的性别全部为“男”。

4．对“学生情况表”按主要关键字为“总分”降序排列，次要关键为“学号”升序排列。排序前数据表如图3.16所示与排序后数据表如图3.17所示。

5．筛选出英语成绩在80分以上的所有记录。

学号姓名性别高数英语总分

071020101 周丽江男65 70 135

071020102 刘海男86 88 174

071020103 陈莉女67 78 145

071020105 江涛男82 90 172

071020106 郑宏男77 68 145

071020107 张小燕女53 65 118

071020108 吴红女91 92 183

图3.16数据排序前

学号姓名性别高数英语总分

071020108 吴红女91 92 183

071020102 刘海男86 88 174

071020105 江涛男82 90 172

071020103 陈莉女67 78 145

071020106 郑宏男77 68 145

071020101 周丽江男65 70 135

071020107 张小燕女53 65 118

图3.17数据排序后

6．筛选出高数成绩在85分以上或60分以下的女生记录。

【知识点提示】

①本操作分两步：先选出性别为女的记录，再筛选高数85分以上或60分以下的；或先筛选高数85分以上或60分以下的，再选性别为女的记录。

②选高数85分以上或60分以下的操作步骤是：对高数筛选时选择自定义，打开“自定义自动筛选方式”对话框，在其中输入两个条件用“或”逻辑，如图3.18所示。

图3.18筛选高数85分以上或60分以下的两个条件示意

7．筛选出高数成绩在80分以上或英语成绩在80分以上的所有记录，并将筛选结果放到指定的位置显示。

【知识点提示】

以上筛选都仅对一门课程成绩进行筛选，同时筛选结果放在原来位置上，因此用自动筛选就能完成，本题要对两门课程成绩进行筛选，同时将筛选结果要放到指定的位置，这时就应该用高级筛选较。操作步骤是：

①在数据表下面两个外或两列外，设置筛选条件区域，先分别在两个单元格中输入“高数”和“英语”。

②在“高数”单元格对应的下一行的单元格中输入条件“>80”。

③在“英语”单元格对应的下两行的单元格中输入条件“>80”。注意，因为是“或”的关系，条件不能在一行进行输入。

④将光标放在数据表中，执行“表格|筛选|高级筛选”命令，在打开的对话框中进行设置。

原数据表

筛选结果

图3.19高级筛选示意

8．将“学生情况表”中的数据复制到Sheet3中，然后对Sheet3中的数据进行分类汇总操作。

1）按性别分别求出男、女生的各门课程的平均成绩（不包括总分），平均成绩保留1位小数。

【知识点提示】

①由于分类字段是性别，因此在进行分类汇总之前，必须先对性别字段进行排序。

②有两门课程成绩进行汇总。

③汇总的方式是求平均值。在“分类汇总”对话框中设置示意图如3.20所示。

图3.20按性别求各课程平均值

汇总结果如图3.21所示。

图3.21汇总结果示意

2）在原有分类汇总的基础上，再统计出男生、女生各有多少人。

【知识点提示】

该题目是一个分类汇总嵌套使用。就是再次使用“分类汇总”命令进行统计。在其对话框中的设置如图3.22所示。

图3.22“分类汇总”嵌套设置

分类汇总嵌套使用后的结果显示如图3.23所示。

图3.23分类汇总嵌套结果示意

9．表3.3是教师上课情况表，在该表中要统计各学院中每种职称的人数各是多少。

【知识点提示】

题目中既要按“学院”又要按“职称”分类，这就要用数据透视表来解决。建立透视表必须分清分类字段是什么，是按行还是按列分类，汇总字段是什么，以及汇总方式等。具体操作是：

①将光标放在数据表中。

②通过执行“数据|数据透视表和数据透视表图”命令，在弹出的数据透视表向导对话框中点“下一步”，直到出现如图3.24所示的建立数据透视表操作界面，在其中进行设置。

图3.24建立数据透视表操作界面

本题行字段用“学院名称”，即将“学院名称”拖到“行”；列字段用“职称”，即将“职称”拖到“列”；“姓名”拖到“数据项”位置。透视表设置效果如图3.25所示。

图3.25透视表效果