第 1 章
Office Excel 2010 高级应用技术
1.1 学生成绩管理
一、考查知识点
数据清单、格式设置、公式与函数、排序、筛选、分类汇总和图表等级知识，利用多 种公式和函数对单元格中的数据进行运算；对各种数据可以进行格式设置；分析汇总各单 元格中的数据信息，把相关数据用统计图表的形式表示，运用 Excel 2010 电子表格强大的 数据管理功能，进行相应的数据处理和分析。
二、知识点解析
1、创建学生信息表，如图 7-1 所示。
2、操作内容：
图 1-1 原始工作表
（1） 将首行合并居中，并将标题“学生信息表”设置为“黑体”，“20 号” ，将其他数据清单数据设置为“宋体”，“12 号”，“水平居中”。
（2） 将数据表中的行高设为“20 磅”，列宽设为“最合适的列宽”，数据 表中的单元格垂直和水平对齐方式均设定为“居中”。
（3） 设置各科成绩的数据输入有效性，要求成绩输入的范围为 0~100， 当鼠标停留在成绩的单元格上时，提示信息为“成绩范围：0~100”。
（4） 将每门课程中不及格的成绩用红色、加粗字体显示。
（5） 用 AVERAGE 函数求出每位学生的平均分，并用 IF 函数根据平均分对 学生做出评价，60 分及以上为“合格”，60 分以下为“不合格”。
（6） 根据“身份证号码”字段计算每位学生的年龄。
（7） 用统计函数完成对“平均分”中各分数段人数统计，把结果分别填 入相应的单元格，并根据各等级的比例生成三维饼图，放在统计表下。
（8） 插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优 秀学生信息表”；在“学生信息表”中筛选出平均成绩大于等于 85 分的学生记 录，将其复制到“优秀学生信息表”中，“学生信息表”中的记录还原。
（9） 建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制 到该表中，按“组别”字段升序排序。
（10） 按 A、B 两个组进行分类汇总，分别计算出 A、B 组的“数学”、“英 语”、“物理”、“化学”平均分。
3、操作分析与过程
（1）输入数据表中的原始数据。
（2）选择 D3 至 G23 区域，单击“数据”选项卡“数据工具”组中的“数据有效性” 命令，弹出如图 7-2 所示的对话框，选择“设置”选项卡进行“有效性条件”设置，在 “输入信息”选项卡中进行相关输入信息的设置。
图 1-2 设置“数据有效性”对话框
（3）选择 D3 至 G23 区域，单击“开始”选项卡“样式”组中的“条件格式”按钮， 选择“突出显示单元格规则”→“其他规则”命令，弹出“新建格式规则”对话框，设置 单元格数值为“小于 60”，再单击“格式”对话框，设置字体为“红色”、“加粗”，如图 7-3 所示，单击“确定”按钮。
图 1-3 条件格式设置
（4）在单元格 H3 中输入公式“=AVERAGE(D3:G3)”，计算该学生的平均分，利用 Excel 2010 的序列填充功能，求出其他学生的平均分，如图 7-4 所示。
图 1-4 计算“平均分”
（5）在单元格 I3 中输入公式“=YEAR(NOW())-MID(C3,7,4)”，计算该学生的年龄， 利用 Excel 2010 的序列填充功能，求出其他学生年龄，如图 7-5 所示。
图 1-5 计算“年龄”
（6）在单元格 J3 中输入公式“=IF(H3>=60,\合格\不合格\”，计算该学生的等级， 利用 Excel 2010 的序列填充功能，求出其他学生的等级，如图 7-6 所示。
图 1-6 计算“等级”
（7）在单元格 D26 中输入公式“=COUNTIF(H3:H23,\”，计算“0-59 分”的人 数；在单元格 E26 中输入公式“=COUNTIF(H3:H23,\-
COUNTIF(H3:H23,\”，计算“60-84 分”的人数；在单元格 F26 中输入公式 “=COUNTIF(H3:H23,\”，计算“≥85 分”的人数；在单元格 D27 中输入公式
“=D26/COUNT($H$3:$H$23)”，计算出该等级所占比例，其它依次类推，如图 7-7 所示。
图 1-7 统计各分数段人数
（8）选中 C25 至 F27，单击“插入”选项卡“图表”组中的“饼图”按钮，选择“三 维饼图”命令，根据各等级的比例生成如图 7-8 所示的三维饼图，放在统计表下。
图 1-8 各分数段人数所占比例图表
（9）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表” （略）；返回原始工作表，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，各字
段旁出现下拉按钮。单击“平均分”字段的下拉按钮，在弹出的下拉菜单中执行“数字筛
选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如
图 7-9 所示。筛选出平均成绩大于等于 85 分的学生记录，将这些记录复制到“优秀 学生信息表”中，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，取消“学生 信息表”中的记录筛选。
计算机应用基础课程excel综合实训材料
