3 电子表格软件操作实验 ·1·
实验三 数据管理
一、实验目的
1. 掌握数据表的排序、筛选。 2. 掌握数据的分类汇总。 3. 掌握数据透视表的操作。
二、实习内容
1．打开WE2.xls工作簿，将其换名为WE3.xls保存。
2．将“学生成绩表”工作表中的数据复制到Sheet2表中，再将Sheet2重命名为“学生情况表”。
3．在“学生情况表”中插入一列，列标题为“性别”，并将“陈莉”、“张小燕”的性别输入为“女”，其余的性别全部为“男”。
4．对“学生情况表”按主要关键字为“总分”降序排列，次要关键为“学号”升序排列。排序前数据表如图3.16所示与排序后数据表如图3.17所示。
5．筛选出英语成绩在80分以上的所有记录。
学号
071020101 071020102 071020103 071020105 071020106 071020107 071020108
姓名
周丽江 刘海 陈莉 江涛 郑宏 张小燕 吴红
性别
男 男 女 男 男 女 女
高数
65 86 67 82 77 53 91
英语
70 88 78 90 68 65 92 总分 135 174 145 172 145 118 183
图3.16数据排序前
学号
071020108 071020102 071020105 071020103 071020106 071020101 071020107
姓名
吴红 刘海 江涛 陈莉 郑宏 周丽江 张小燕
性别
女 男 男 女 男 男 女
高数 英语
92 88 90 78 68 70 65
91 86 82 67 77 65 53
图3.17数据排序后
总分 183 174 172 145 145 135 118
6．筛选出高数成绩在85分以上或60分以下的女生记录。
3 电子表格软件操作实验 ·2·
【知识点提示】
① 本操作分两步：先选出性别为女的记录，再筛选高数85分以上或60分以下的；或先筛选高数85分以上或60分以下的，再选性别为女的记录。
② 选高数85分以上或60分以下的操作步骤是：对高数筛选时选择自定义，打开“自定义自动筛选方式”对话框，在其中输入两个条件用“或”逻辑，如图3.18所示。
图3.18筛选高数85分以上或60分以下的两个条件示意
7．筛选出高数成绩在80分以上或英语成绩在80分以上的所有记录，并将筛选结果放到指定的位置显示。
【知识点提示】
以上筛选都仅对一门课程成绩进行筛选，同时筛选结果放在原来位置上，因此用自动筛选就能完成，本题要对两门课程成绩进行筛选，同时将筛选结果要放到指定的位置，这时就应该用高级筛选较。操作步骤是：
① 在数据表下面两个外或两列外，设置筛选条件区域，先分别在两个单元格中输入“高数”和“英语”。
② 在“高数”单元格对应的下一行的单元格中输入条件“>80”。 ③ 在“英语”单元格对应的下两行的单元格中输入条件“>80”。注意，因为是“或”的关系，条件不能在一行进行输入。
④ 将光标放在数据表中，执行“表格|筛选|高级筛选”命令，在打开的对话框中进行设置。
学号 071020108 071020102 071020105 071020101 071020106 071020103 071020107 姓名 吴红 刘海 江涛 周丽江 郑宏 陈莉 张小燕 性别 女 男 男 男 男 女 女 高数 98 86 78 65 59 67 53 英语 91 88 90 85 68 78 65 总分 189 174 168 150 145 127 118 原数据表
筛选结果
高数 >80 英语 >80
条件区域
3 电子表格软件操作实验 ·3·
学号 071020108 071020102 071020105 071020101
姓名 吴红 刘海 江涛 周丽江 性别 女 男 男 男 高数 98 86 78 65 英语 91 88 90 85 总分 189 174 168 150 图3.19高级筛选示意
8．将“学生情况表”中的数据复制到Sheet3中，然后对Sheet3中的数据进行分类汇总操作。
1）按性别分别求出男、女生的各门课程的平均成绩（不包括总分），平均成绩保留1位小数。
【知识点提示】
① 由于分类字段是性别，因此在进行分类汇总之前，必须先对性别字段进行排序。 ② 有两门课程成绩进行汇总。
③ 汇总的方式是求平均值。在“分类汇总”对话框中设置示意图如3.20所示。
图3.20按性别求各课程平均值
汇总结果如图3.21所示。
学号 姓名 性别 高数 071020102 刘海 男 071020105 江涛 男 071020101 周丽江 男 071020106 郑宏 男 男 平均值 071020108 吴红 女 071020103 陈莉 女 071020107 张小燕 女 女 平均值 总计平均值 英语 总分 86 88 174 78 90 168 65 85 150 59 68 127 72 82.8 98 91 189 67 78 145 53 65 118 72.7 78 72.3 80.7 图3.21汇总结果示意
3 电子表格软件操作实验 ·4·
2）在原有分类汇总的基础上，再统计出男生、女生各有多少人。 【知识点提示】
该题目是一个分类汇总嵌套使用。就是再次使用“分类汇总”命令进行统计。在其对话框中的设置如图3.22所示。
图3.22“分类汇总”嵌套设置
分类汇总嵌套使用后的结果显示如图3.23所示。
学号 071020102 071020105 071020101 071020106 071020108 071020103 071020107
姓名 性别 高数 刘海 男 江涛 男 周丽江 男 郑宏 男 男 计数 4 男 平均值 吴红 女 陈莉 女 张小燕 女 女 计数 3 女 平均值 8 总计数 总计平均值 英语 总分 86 88 174 78 90 168 65 85 150 59 68 127 72 82.8 98 91 189 67 78 145 53 65 118 72.7 78 72.3 80.7 图3.23分类汇总嵌套结果示意
9．表3.3是教师上课情况表，在该表中要统计各学院中每种职称的人数各是多少。
【知识点提示】
题目中既要按“学院”又要按“职称”分类，这就要用数据透视表来解决。建立透视表必须分清分类字段是什么，是按行还是按列分类，汇总字段是什么，以及汇总方式等。具体操作是：
① 将光标放在数据表中。
② 通过执行“数据|数据透视表和数据透视表图”命令，在弹出的数据透视表向导对话框中点“下一步”，直到出现如图3.24所示的建立数据透视表操作界面，在其中进行设置。
3 电子表格软件操作实验 ·5·
图3.24建立数据透视表操作界面
本题行字段用“学院名称”，即将 “学院名称”拖到“行”；列字段用“职称”，即将“职称”拖到“列”；“姓名”拖到“数据项”位置。透视表设置效果如图3.25所示。
图3.25透视表效果