2.5 考试成绩公布表
案例背景
阅卷完毕，考试成绩统计出来以后，为了促进学生学习的积极性，大多数学校会公布学生的成绩。某中学高二年级需要创建期中考试成绩公布表，要求把各科中不及格的成绩用红色粗体显示，按班级打印排名表送各个班级，然后可以选出年级前50名学生公布。
通过本节的学习，主要应掌握自动筛选和条件格式的应用方法。
关键技术点
要实现本案例中的功能，学员应该掌握以下Excel技术点 。
●基础知识：冻结窗格、定义名称、条件格式、筛选下拉菜单、自定义筛选
● 函数应用 RANK函数
最终成果展示
2.5.1 创建考试成绩公布表
在Excel中可以应用一些巧妙的方法管理复杂的工程，可以用名称来命名单元格或者区域，而且可以使用这些名称进行导航和代替公式中的单元格地址，是工作表更容易理解和更新。
名称是单元格或者区域的别名，它代表单元格、单元格区域、公式或者常量的单词和字符串。例如用名称“语文”来引用区域“sheet1！C3:C12”，目的是便于理解和使用。在创建比较复杂的工作簿时，命名是有非常重要的好处的：使用名称表明单元格的内容比使用单元格地址更清楚明了；在公式或者函数中使用名称代替单元格或者区域的地址，如公式“=AVERAGE（语文）”比公式“=AVERAGE（sheet1！C3：C12）”更容易记忆和书写；名称可以用于所有工作表；在工作表中复制公式时，使用名称和使用单元格引用的效果相同。
默认状态下，名称使用的是单元格的绝对引用，形式为$C$3：$C$12。
Step1 创建工作薄、重命名工作表
参阅1.2.1小节Step1至Step2创建工作簿“考试成绩公布表.xls”，然后将工作表重命名为“成绩公布表”并删除多余的工作表。
Step2 输入表格标题
选中A1：J1单元格区域，设置格式为“合并及居中”，输入表格标题“期中考试成绩公布表”，然后设置字形为“加粗”、字体为“黑体”、字号为“16”。
Step3 输入表格各个字段标题
在A2：J2单元格区域分别输入各个字段的标题名称，并设置文本居中显示，字体为“黑体”，字号为“12”。
Step4 输入“班级”
①选中A3：A42单元格区域，输入“1”，按组合键确认即可完成批量输入相同的数据。
②同样在A43：A82单元格区域输入相同的数据“2”。
③在A83：A122单元格区域输入相同的数据“3”。
Step5 输入“学号”
①选中B3单元格，输入“8401”；选中B4单元格，输入“8402”。
②选中B3：B4单元格区域，将光标移到B4单元格的右下角，当光标变为+形状时按住鼠标左键不放向下拖曳填充柄至B122单元格，然后松开鼠标即可完成内容的填充。
Step6 输入原始数据
在C3：G122单元格区域输入原始数据。
Step7 统计“总分”
①选中H3单元格，输入以下公式。=SUM（C3：G3）
②选中H3单元格，移动光标到该单元格的右下角，当光标变为+形状时向下拖曳填充柄至H122单元格，然后松开鼠标即可完成公式的填充。
Step8 冻结窗格
当编辑过长或过宽的Excel工作表时，需要向下或向右滚动屏幕，这时表头也会相应地滚动，而不能在屏幕上显示出来。
单击第3行的行标以选中第3行，然后单击菜单“窗口”→“冻结窗格”。
这时在第3行的上方就会插入一条冻结线，之后拖动上下滚动条即可查看数据的详细情况，而表头行则始终存在。
单击菜单“窗口”→“取消冻结窗格”即可取消对窗格的冻结。
Step9 给单元格区域取名
①选择要命名的H3：H122单元格区域，单击菜单“插入”→“名称”→“定义”。
②在弹出的“定义名称”对话框中的“在当前工作簿中的名称”文本框中输入要定义的名称，如“zongfen”。
③单击“添加”按钮，定义好的名称就会显示在列表框中。此时H3：H122单元格区域已经定义名称为“zongfen”，然后单击“确定”按钮。
再次选中被命名的单元格区域时，名称框中会直接显示所定义的名称。而如果只是选择了某一个单元格，名称框则不会显示区域名称。
Step10 统计“年级名次”
选中I3单元格，输入以下公式，然后按键确认。=RANK（H3，zongfen，0）
Step11 自动填充公式
参阅2.1.1小节Step7，选中I3单元格，移动光标到该单元格的右下角，当光标变为+形状时向下拖曳填充柄至I122单元格，然后松开鼠标即可完成公式的填充。
Step12 使用“名称框”定义名称
使用“名称框”可以更方便地定义名称。
①首先选中要命名的H3：H42单元格区域，然后单击“编辑栏”左侧的“名称框”。
②输入要定义的名称，如“class1”。
③按键确认，完成名称的定义。
④同样选中H43：H82单元格区域，定义名称为“class2”。
⑤选中H83：H122单元格区域，定义名称为“class3”。
Step13 统计“班级名次”
①选中J3单元格，输入以下公式，然后按键确认。=RANK（H3，class1，0）
②移动光标到J3单元格的右下角，当光标变为+形状时向下拖曳填充柄至J42单元格，然后松开鼠标即可完成公式的填充。
③选中J43单元格，输入以下公式，然后按键确认。=RANK（H43，class2，0）
④向下拖曳J43单元格右下角的填充柄至J82单元格完成公式的填充。
⑤选中J83单元格，输入以下公式，然后按键确认。=RANK（H83，class3，0）
⑥向下拖曳J83单元格右下角的填充柄至J122单元格完成公式的填充。
Step14 设置条件格式
为了便于发现指标较低的部分以进行改善，可以对表格数据设置条件格式。本例设定以不同方式显示数值低于60的单元格。
①选中C3：G122单元格区域，单击菜单“格式”→“条件格式”弹出“条件格式”对话框。
②在“条件格式”对话框的“条件1”文本框中依次设置：“单元格数值”、“小于”和“60”，如图18所示。
③单击“格式”按钮弹出“单元格格式”对话框。
④在“单元格格式”对话框中切换到“字体”选项卡，在“字形”列表框中选择“加粗”。单击“颜色”右侧的下箭头按钮，在弹出的“颜色”调色板中选择“红色”。
⑤单击“确定”按钮返回“条件格式”对话框。
⑥单击“确定”按钮保存条件格式。
此时C3：G122单元格区域即考试成绩区域就都应用了条件格式，凡是小于60的单元格均显示为粗体红色，效果如图21所示。
Step15 调整表格列宽
参阅1.1.2小节Step2调整表格列宽。
Step16 设置表格边框
参阅1.2.1 Step19，选中A2：J122单元格区域，然后设置表格边框。
关键知识点讲解
1.名称命名的规则
● 名称可以使任意的字符与数字组合在一起，但不能以数字开头，更不能以数字作为名称，如7AB。同时名称不能与单元格地址相同，如A3。
● 如果要以数字开头，可以在前面加上下划线，如_7AB。
● 不能以字母R、C、r、c作为名称，因为R、C在R1C1引用样式中表示工作表的行、列。
● 名称中不能包含空格，可以用下划线或点号代替。
● 不能使用除了下划线、点号和反斜线（/）等之外的其他符号。允许使用问号（？），但不能作为名称的开头，如Range？可以，但？Range就不可以。
● 名称字符不能超过255个。一般情况下名称应该便于记忆且应尽量简短，否则就违背了定义名称的初衷。
● 名称中的字母不区分大小写。
2.RANK函数
RANK(number,ref,order)
函数用途
返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，那么数字的排位就是它当前的位置）。
参数说明
number：为需要找到排位的数字。
ref：为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。
order：为一个数字，指明排位的方式。如果order为0或省略，Excel对数字的排位是基于ref为参照降序排列的列表；如果order不为零，Excel对数字的排位是基于ref为参照升序排列的列表。
函数说明
● RANK函数对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如在一列按升序排列的整数中，如果整数10出现了两次，其排位为5，那么11的排位则为7（没有排位为6的数值）。
● 由于某些原因，用户可能会考虑重复数字的排位定义。在前面的示例中，用户可能要将整数10的排位改为5.5.这可通过将下列修正因素添加到按排位返回的值来实现。该修正因素对于按照升序计算排位（顺序=非零值）或按照降序计算排位（顺序=0或被忽略）的情况都是正确的。
重复数排位的修正因素=[COUNT(ref)+1-RANK(number,ref,0)- RANK(number,ref,1)]/2。
在下面的示例中，RANK（A2，A1：A5，1）等于3。修正因素是（5+1-2-3）/2=0.5，考虑重复数排位的修改排位是3+0.5=3.5。如果数字仅在ref中出现一次，由于不必调整RANK，因此修正因素为0。
函数简单示例
本例公式说明
本例中的公式为： =RANK（H3，zongfen，0）
其各个参数值指定RANK函数计算H3单元格在H3：H122单元格区域中按降序排位的位数。
2.5.2 筛选数据
利用Excel的筛选功能能够在一份复杂的数据清单中迅速地查找到满足条件的数据资料。
Step1 对单一值的筛选
①选中工作表中数据区域中的任意一个单元格，然后单击菜单“数据”→“筛选”→“自动筛选”，此时数据清单中的每一列都会添加一个下箭头的按钮。
②单击下箭头按钮将显示这一列所有的不重复的值，用户可以对这些值进行选择。
③选中某一个数值，如“2”后Excel将会显示出于这个数据有关的记录，其他的记录会被隐藏起来。筛选出来的记录的行标记会变为蓝色，这些蓝色的行标记是在原始数据清单中的行标记。
Step2 按班级打印排名表
①单击A2单元格右侧的下箭头按钮选中“1”，此时Excel将显示出与班级1相关的记录。
②参阅1.2.2小节Step2设置打印区域。
③单击菜单“文件”→“打印”即可打印班级1的排名表。
④在打印预览页中单击A2单元格右侧的下箭头按钮选中“2”，然后单击菜单“文件”→“打印”即可打印班级2的排名表。
类似的可以打印班级3的排名表。
⑤打印完毕单击菜单“视图”→“普通”恢复到普通状态视图，然后单击A2单元格右侧的下箭头按钮选择全部。
Step3 指定条件筛选
①单击“年级名次”的下箭头按钮，在下拉列表中选择“自定义”选项弹出“自定义自动筛选方式”对话框。
②在该对话框的“年级名次”组合框中，在左边的下拉列表中选择条件为“小于或等于”，在右侧的数值文本框中输入“50”，然后单击“确定”按钮。
筛选后的结果是“年级名次”在1~50之间的记录。
|
