3.10 学生个人成绩查询
案例背景
作为学校中的一名班主任或者科任教师经常会遇到学生来查询个人成绩，在数据表字段比较多的情况下，由于数据表会比较宽，必须左右拖动水平滚动条才能够在数据表中看到同一记录的全部内容，使得成绩查询十分不便，为便于学生成绩查询，我们可以将同一记录的全部内容集中一张“学生个人成绩查询表”中，这样每个学生的情况在同一页面中就一目了然了。
在1.2节和1.3节中我们学习了“学生成绩档案页”和“学生入学情况汇总表”，那么是如何将“学生入学情况汇总表”中的个人信息集中到这个学生的“学生成绩档案页”查询表中的呢？在本节中主要使用VLOOKUP函数将分别置于各个工作表中的某个学生的各种文本信息和图片信息动态的集中到“学生个人成绩查询表”中，从而实现学生个人信息查询。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 数据有效性的下拉选框，
●函数应用 VLOOKUP函数 OFFSET函数 MATCH函数
●综述 定义动态区域名称，插入、编辑Excel控件 ，数据有效性来源区域的设置与切换 照片的自动更新
最终效果展示
3.10.1班级名单的数据有效性动态来源区域
在前面2.3节中已经介绍了使用数据有效性技术为“性别”字段设置下拉选框来输入“男”或“女”，在通常情况下，使用数据有效性时，只能处理一组数据来源，可是在某些场合，数据来源却不止一组，如本案例中数据表中涉及三个班的学生，通常情况下每个班的学生数在50名左右，如果以3个班的150多名学生的姓名作为一组数据来源去设置姓名下拉选框的话，那么在查询学生成绩时从下拉选框中的150多名学生的姓名中去选择某一位学生那是十分困难的。因此我们以班为单位分组，为数据有效性设置3组数据来源，通过使用OFFSET函数来实现数据有效性各组数据来源的切换。由于受到单屏可视区域所限，在这里每个班级仅取8名学生的信息为例来说明问题。
Step 1创建、保存工作簿
新建一个Excel工作簿，保存为”学生成绩查询表.xls”。
Step 2插入工作表，重命名工作表
①右键单击工作表标签“Sheet1”，在弹出的列表中选择“选定全部工作表”。
②单击菜单“插入”→“工作表”，这时就一次性插入了3张工作表
③将工作表重命名为“查询表”，“入学”，“高一上期中”，“高一上期末”，“高一下期中”，“高一下期末”，
Step 3输入学生原始信息
①在“入学”工作表中输入学生入学时的原始信息。
②在“高一上期中”工作表中输入学生高一上学期各科期中考试成绩和总分。
③在“高一上期末”工作表中输入学生高一上学期各科期末考试成绩和总分。
④在“高一下期中”工作表中输入学生高一下学期各科期中考试成绩和总分。
⑤在“高一下期末”工作表中输入学生高一下学期各科期末考试成绩和总分。
⑥单击工作表标签“入学”，在单元格区域M1:O1中输入“一班”，“二班”，“三班”，在单元格区域M2:O9中输入每个班的8个学生的姓名，并为单元格区域M1：O9设置边框。
Step4插入动态区域名称
①光标选中M列的列标选中M列，然后向右拖动到L列选中M、N、L三列，单击菜单“插入”→“名称”→“指定”，弹出“指定名称”对话框。
②在弹出的“指定名称”对话框中的“名称创建于”选项组中单击“首行”按钮，然后单击确定按钮即可完成名称的创建。
③单击M2单元格，按组合键，选中单元格区域M2:M65536，此时名称框显示该区域名称“一班”。
同样当选中单元格区域N2:N65536 和L2:L65536时，名称框分别显示该区域名称“二班”和“三班”。
Step5设置数据有效性
①单击工作表标签“”查询表，选中单元格Q1，，单击菜单“数据”→“有效性”，弹出 “数据有效性”对话框。
②在“数据有效性”对话框的“设置”选项卡中的“允许”下拉列表框中选择“序列”，在“来源“框中输入公式： “ =OFFSET(一班,,$R$1-1) ”。
③切换到“输入信息“选项卡，在”标题“选项框中输入”选择学生姓名“，在”输入信息“选项框中输入”请选择需要查询成绩的学生姓名“，然后单击确定按钮。
3.10.2创建学生成绩查询表
Step1设计学生成绩查询表
在“查询表”工作表中输入表头“学生成绩档案”以及表格的其他字段信息。
Step2输入学生的“入学“原始信息
①在单元格B3中输入公式:”=Q1”, 然后按键确认。
②在单元格C2输入公式：“="高一"&VLOOKUP($B$3,入学!$A$2:$K$25,2,0)&"班"”
然后按键确认。
③单击单元格F3，单击常用工具栏“合并及居中“撤销“合并及居中“，然后输入公式=VLOOKUP($B$3,入学!$A$2:$K$25,3,0) 然后再按键确认，
再选中单元格区域F3:G3单击常用工具栏“合并及居中。
④在单元格I3，输入公式=VLOOKUP($B$3,入学!$A$2:$K$25,4,0) ，按键确认，
在单元格K3，输入公式=VLOOKUP($B$3,入学!$A$2:$K$25,5,0) ，按键确认。
⑤在单元格C4，E4，G4，I4，K4，M4中分别输入以下公式：
=VLOOKUP($B$3,入学!$A$2:$K$25,6,0) ，=VLOOKUP($B$3,入学!$A$2:$K$25,7,0) ，
=VLOOKUP($B$3,入学!$A$2:$K$25,8,0) ，=VLOOKUP($B$3,入学!$A$2:$K$25,9,0) ，
=VLOOKUP($B$3,入学!$A$2:$K$25,10,0) ，=VLOOKUP($B$3,入学!$A$2:$K$25,11,0) ，
然后按键确认，完成该学生入学成绩的导入。
Step3输入语文考试成绩
①在单元格B7中输入以下公式，然后按键确认：
“=VLOOKUP($B$3,高一上期中!$C$2:$Q$51,COLUMN(),0) ”
②在单元格B8中输入以下公式，然后按键确认：
“=VLOOKUP($B$3,高一上期末!$C$2:$Q$51,COLUMN(),0) ”
③在单元格B9中输入以下公式，然后按键确认：
“=VLOOKUP($B$3,高一下期中!$C$2:$Q$51,COLUMN(),0) ”
④在单元格B10中输入以下公式，然后按键确认：
“=VLOOKUP($B$3,高一下期末!$C$2:$Q$51,COLUMN(),0) ”
Step4输入其他学科考试成绩
选中下一个区域B7:B10，向右拖曳单元格B10右下角的填充柄至O列即可完成其他学科考试成绩的导入，然后修复表格外边框。
3.10.3在查询表中输入学生照片
在Excel中，使用动态名称和ActiveX控件，就能够轻松地实现工作表中的图片自动更新。下面我们将为“学生成绩档案”插入照片，使学生的照片能够随着学生姓名的变化而变化。
Step1插入并重命名工作表
单击菜单“插入”→“工作表”，插入一张工作表并将其重命名为“照片”。
Step2输入学生原始信息
①单击工作表标签“入学”，选中单元格区域A1:D25，按组合键复制，单击工作表标签“照片”，按组合键粘贴。
②在单元格E1中输入“照片”，并未单元格区域A1:E25设置边框。
Step3调整行高与列宽
①单击列标E选中E列，单击菜单“格式”→“列”→“列宽”，在弹出的“列宽”对话框中输入“13.5”.
②光标选中第2行至第25行，单击菜单“格式”→“行”→“行高”，在弹出的“行高”对话框中输入“101”。
Step4创建照片库
①单击单元格E2，参照1.2节介绍的插入图片的方法将第一个学生的照片插入“照片”工作表。
②右键单击该学生照片，弹出“设置图片格式”对话框，切换到“大小”选项卡，在“大小和转角”选项组的“高度”框中输入“3.47”厘米，宽度框中输入“2.96”厘米。
③切换到“属性”选项卡，在“对象位置”选项组中单击“大小、位置随单元格而变”
④单击确定按钮，然后用鼠标将照片拖放到指定单元格E2。
⑤单击照片，然后单击绘图工具栏的“绘图”→“自动靠齐”→“对齐网格”。
⑥使用相同方法为其余学生插入照片
Step5定义名称
按组合键弹出“定义名称”对话框，在“定义名称”对话框中在“在当前工作簿中的名称”文本框中输入“pic”，在“引用位置”文本框中输入公式：
“=OFFSET(照片!$E$2,MATCH(查询表!$B$3,照片!$A$2:$A$25,0)-1,0) ”
然后单击确定按钮。
Step6插入控件
①切换到“查询表”工作表，单击菜单“试图”→“工具栏”→“控件工具箱”，弹出 “控件工具箱”，在“控件工具箱”。
②在“控件工具箱”的工具栏中单击“命令按钮”控件，此时鼠标光标呈细黑十字状，单击准备放置照片的N3单元格左上角，然后向右下方向拖动，画出一个符合单元格的大小的命令按钮。
③单击命令按钮，把光标定位到编辑栏，将原有内容：“=EMBED("Forms.CommandButton.1","")”
改为此前定义的名称“=pic”。这时，第一个学生的照片就显示出来了。
Step7调整命令按钮的大小
为了让照片的大小与单元格大小匹配，可以调整命令按钮的大小，方法同前
至此学生成绩查询表的设计工作全部完成，通过单元格Q1的下拉选框选择不同的学生，“学生成绩档案”中就会显示该学生的成绩记录和本人照片。
单元3操作练习
1.按照3.2节“学生座位号码表“的顺序按下图格式（8行6列），分别面向学生和面向教师重新安排座位表（也可以您任教的班级学生为例先设定学生座位号码，然后按8行6列安排座位表），
2.按照3.2节“学生座位号码表“的顺序按下图格式（6行8列），分别面向学生和面向教师重新安排座位表
3.参照3.3节的案例的方法，将您任教的班级上学期的各科考试成绩汇总起来形成“成绩汇总表“。
4.在练习3的基础上，参照3.4节的案例按如下格式用“添加辅助列“的方法制作“成绩条“。
5. 在练习3的基础上，参照3.4节的拓展案例的“成绩条”“函数嵌套”制作方法制作“成绩条“，并在成绩条下方增加一行“空白行”，“空白行”行高为“15像素”。
6.以您任教的班级上学期期中、期末和总评成绩为样本，参照3.5节提供的统计方法，统计应考人数、实考人数、及格人数、不及格人数、平均分、最高分、及格率、良好率、优秀率和标准差。
7.参照3.6节介绍的方法，使用COUNTIF函数统计你任教的班级上学期期末各科考试成绩的分数段。并作出分数段统计柱形图和饼图图表。
8.参照3.7节介绍的方法，以您任教的班级上学期期末各科考试成绩为例，制作图表，并应用自动筛选技术实现图表的交互功能，将每个学生的成绩与班级平均分最高分和最低分集中于同一图表进行比较。
9. 参照3.8节介绍的方法，应用“窗体”组合框控件制作具有交互功能的“单科成绩—名次组合图”。
10.参照3.10节介绍的创建照片库的方法，为你任教的班级学生建立一个照片库。
11.参照3.10节介绍的创建“数据有效性”班级学生姓名下拉选框的方法，班主任将本班学生分成6-8组，科任教师按照任教班级将学生分成6-8个班组，利用OFFSET函数创建具有多组动态数据源区域的 “数据有效性”下拉选框。
12. 参照3.10节介绍的方法，利用VLOOKUP函数创建“学生个人成绩查询表”。
13.按照3.9节介绍的随机抽取每个学生考场座位号的方法，以您任教的班级的实际学生数为考场容量，为你的学生随机抽取考场座位号。
|
