【原】Excel案例（十一）——学生成绩表

案例讲解

知识点

1.在Sheet5中，使用函数，将B1中的时间四舍五入到最接近的15分钟的倍数，结果存放在C1单元格中。

2.在Sheet1中，使用条件格式将“铅球成绩（米）”列中单元格数据按下列要求显示。

* 数据大于9的单元格中字体颜色设置为红色、加粗显示；

* 数据介于7和9之间的单元格中字体颜色设置为蓝色、加粗显示；

* 数据小于7的单元格中字体颜色设置为绿色、加粗显示。

3.在Sheet1“学生成绩表”中，使用REPLACE函数和数组公式，将原学号转变成新学号，同时将所得的新学号填入“新学号”列中。

* 转变方法：将原学号的第四位后面加上“5”，

* 例如：“2007032001”—>“20075032001”

4.使用IF函数和逻辑函数，对Sheet1“学生成绩表”中的“结果1”和“结果2”列进行填充。填充的内容根据以下条件确定。（要求：将男生、女生分开写入IF函数中）

* 结果1

如果是男生

″      成绩<14.00，填充为“合格”；

″      成绩>=14.00，填充为“不合格”；

如果是女生

″      成绩<16.00，填充为“合格”；

″      成绩>=16.00，填充为“不合格”；

* 结果2

如果是男生

″      成绩>7.50，填充为“合格”；

″      成绩<=7.50，填充为“不合格”；

如果是女生

″      成绩>5.50，填充为“合格”；

″      成绩<=5.50,填充为“不合格”。

5.对Sheet1“学生成绩表”中的数据，根据以下条件，使用统计函数进行统计。要求：

* 获取“100米跑的最快的学生成绩”，并将结果填入到Sheet1的K4单元格中；

* 统计“所有学生结果1为合格的总人数”，并将结果填入Sheet1的K5单元格中。

6.根据Sheet2中的贷款情况，使用财务函数对贷款偿还金额进行计算。要求：

* 计算“按年偿还贷款金额（年末）”，并将结果填入到Sheet2中的E2单元格中；

* 计算“第9个月贷款利息金额”，并将结果填入到Sheet2中的E3单元格中。

7.将Sheet1中的“学生成绩表”复制到Sheet3，对Sheet3进行高级筛选。

要求

（1）筛选条件为：“性别”－“男”，“100米成绩（秒）”－“<=12.00”，“铅球成绩（米）”－“>9.00”；

（2）将筛选结果保存在Sheet3的J4单元格中。

注意

（1）无需考虑是否删除或移动筛选条件；

（2）复制过程中，将标题项“学生成绩表”连同数据一同复制；

（3）数据表必须顶格放置。

8.根据Sheet1中的“学生成绩表”，在Sheet4中创建一张数据透视表。要求：

* 显示每种性别学生的合格与不合格总人数；

* 行区域设置为“性别”；

* 列区域设置为“结果1”；

* 数据区域设置为“结果1”；

* 计数项为“结果1”。

(声明：此案例来源于浙江省计算机等级考试二级办公软件高级应用真题，由韩老师讲解)