EXCEL高级应用——学生成绩表
一、创建学生成绩表
(1) 打开工作簿“学生成绩表-原始数据.xls”，另存为“序号-姓名.xls”。
(2) 将Sheet1复制到Sheet2，并将Sheet2更名为“成绩表”。
二、数据的初步处理
在Sheet1中进行如下操作：
(1)在Sheet1中用公式和函数计算学生的总分和平均分，分别存放于I列和J列；
(2)使用自动筛选功能调整Sheet1中的“体育”成绩，凡85分以上的减掉5分；
(3)按“总分”列进行降序排列，并在K列填入名次；
(4)格式化数据：
•设置Sheet1中的“平均分”为两位小数；
•设置表中字体为“宋体”，字号为“12”，并居中；
•插入一行标题行，添加标题“学生成绩表”（楷体，20号）；
•数据区域设置“最适合的列宽”；
•给表头添加灰底纹；
•给表格区域A2:K24，添加“红双线外边框”，其余各单元格添加“细实边框线”。三、函数和图表的使用
在Sheet1中进行如下操作：
(1)IF()条件函数：若各门功课全部通过，显示为“是”，否则显示为“否”；将“是否通过”的结果存放在L列。
(2)IF()嵌套函数：如果平均分≥85，显示为“优秀”；如果60≤平均分＜85，则为“合格”，否则显示“不合格”，并将“等级”的判断结果存放在M列。
说明：以下任务(3)-(7)输入到Sheet1中“学生成绩表”的下方，并设置其底纹、边框和对齐方式（设置
合适的列宽，其中B列设置为“自动换行”），如图所示：
(3) COUNTIF()条件统计：统计男生人数和女生人数。
(4) COUNTIF()条件统计：统计总分在400~450的人数。
(5) SUMIF()条件求和：统计男生的总分。
(6) ROUND()设定小数位数：求男生总分的平均分，并利用
公式四舍五入保留2位小数。
(7)MAX()计算最高分：计算“体育”成绩中的最高分。
(8)插入图表：用三维立体饼图展示男女生比例，图表的标
题为“男女生比例”，字体为楷体，加粗，字号12。“图
例”选项卡显示在“底部”。
四、EXCEL高级应用
在Sheet2“成绩表”中进行如下操作：
(1)替换函数REPLACE()：在第1列和第2列之间插入一个新列“新学号”，将学号“001”升级为“2011001”存放到第2列“新学号”列。
sumif函数的使用方法绝对引用(2)条件格式的设置：将性别列为“男”的单元可的字体颜设置为红、加粗。
(3)数据有效性的设置：设置A24单元格中只能录入5位数字或文本，当录入位数错误时，提示错误原因，
样式为“警告”，错误信息为“只能录入5位数字或文本”。
(4)数组函数：使用数组公式，统计学生的“总分”，存放在J列。
(5)函数排名：根据总分列对学生成绩排名，存放在K列。
（注意：用鼠标拖选总分列成绩区域后，直接按下“F4”功能键，实现数据的绝对引用）
(6)数据库函数：将统计情况放在数据区域下方（D26:J30区域），对应的条件区域放在右侧（N2:O14区域），如下图：
•DCOUNT()函数：“语文”和“数学”成绩都大于或等于85的学生人数。
•DGET()函数：“体育”成绩大于或等于90的“女生”姓名。
•DAVERAGE()函数：“体育”成绩中男生总分的平均分。
•DMAX()函数：“体育”成绩中男生的最高分。
(7)查函数：使用查函数VLOOKUP()奖励不同等级的同学，奖励结果存放在M列。
在工作表“成绩表”的K列后插入2个新列，复制Sheet1中的“等级”列到插入的L列，并在N19:O23区域插入一个奖励表：
在M1
(8)高级筛选：将Sheet1中的“学生成绩表”复制到Sheet3，并对Sheet3进行高级筛选，要求如下：
•在Sheet3工作表空白区域创建筛选条件的表格：“性别”为“男”，“英语”为“＞90”，“信息技术”为“＞95”；
•在原有区域显示筛选结果。
(9)数据透视表：根据Sheet1中“学生成绩表”，在Sheet4（新建的工作表）中新建一张数据透视表。要求：
•显示不同性别、不同成绩等级（优秀、合格、不合格）的学生人数情况；
•行区域设置为“性别”；
•列区域设置为“等级”；
•数据区域设置为“等级”；
•计数项为“等级”。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。