Excel成绩排名 [Excel使成绩分析与汇总更智能]

　　每逢考试结束,教师为了实现多元评价就需要快捷而准确地对成绩做出分析。在全县学校多、考生多、科目多、分析项目多的情形下,应用Excel对各学校、各班成绩表进行分析,并利用“三维引用”的方法汇总各项成绩分析,具有高效、快捷、准确、灵活等优势。
　　● 在工作簿中建立成绩表和成绩分析表
　　1.建立成绩表
　　在Excel工作簿中可以同时建立和管理多个工作表,并根据多个工作表的数据进行汇总计算,以便于数据的组织与分析。在Excel工作簿中运用“移动复制工作表”功能分别建立并命名“A中学、B中学、C中学… …成绩分析”等成绩表,如图1工作表标签所示。在成绩表中录入相应学校及全县考生成绩。
　　
　　 2.建立“成绩分析”工作表
　　在“成绩分析”工作表中,第一行设标题;第二行设表头,同时需合并单元格。在A2∶O2各单元格中依次键入科目、学校、参考人数等字段。在B4∶B11各单元格中依次键入全县、A中学、B中学等,复制B4∶B11单元格区域,分别粘贴到B12∶B19、B20∶B27等区域。分别合并A4∶A11、A12∶A19单元格区域,依次输入“总分”、“语文”以此类推键入其他科目(如图2)。
　　
　　● 成绩分析中应用的函数公式
　　函数公式在Excel工作表中可利用“插入”菜单中的“FX函数”命令导出“插入函数”对话框,也可直接使用“Shift+F3”调用该对话框。
　　利用统计函数LARGE求各学科优秀分数线。若全县高三参考人数是4304人,要求各科取1500名学生数作为学科优秀生,那么各科成绩中居全县第1500名的分数便是该科优秀分数线。譬如,获取数学优秀分,在全县成绩表中F4308单元格内输入“=LARGE(F4∶F4307,1500)”后按“回车”键,即可得到全县数学优秀分为“80”。
　　利用SUM、AVERAGE函数分别求每名学生的总分、各学科平均分。计算各学科优秀人数是在O4319单元格中输入“=COUNTIF(O4∶O4307,">=85")”(其中“85”为理综优秀分)。各分数段的计算方法是在V4326单元格中输入“=COUNTIF($V$4∶$V$4307,">=500")-COUNTIF($V$4∶$V$4307,">550")”。利用逻辑函数IF获取每名学生各科或总分对应的成绩等次。总分“=IF(V4>=470,"优秀",IF(V4>=377,"良好", IF(V4>=300,"合格","不合格")))”,学科“=IF(C4>=90,"A",IF(C4>80,"B", IF(C4>=72,"C",IF(C4>60,"D","F"))))”,按“回车”键确认。而后,利用填充柄完成其他学生总分等次的填充。
　　● 对多个成绩表实施成绩分析的策略
　　如果要分析多个班级或单元的成绩表,不必使用“宏”,只选人数最多的成绩表应用函数公式完成各项成绩分析,而对其他各成绩表也不用手工键入繁琐的函数公式。
　　1.创设相同的制表标准
　　在各中学、全县考生、班级工作表中创设出相同的制表标准是最关键的策略,其目的是为快捷完成各中学成绩分析与汇总成绩分析时的“三维引用”打下基础。因此,在各成绩表中总分、各科成绩必须同学科同列号,而平均分、优秀人数… …各项成绩分析必须同行号。并且保障各工作表中表头各科目所处行、列位置一致,以使各成绩表中函数公式引用数据源区域完全相同且不漏掉任意一名考生成绩(如图3)。
　　
　　2.首先分析人数最多的成绩表
　　选取考生人数最多的成绩表进行各项成绩分析,其他考生人数次之的各成绩表暂不分析。与各中学、班级成绩表相比,显然“全县考生”成绩表中考生人数最多。故此,先分析“全县考生”成绩表,激活最后一名学生姓名下的单元格B4315,在B4315∶B4327各单元格内依次输入参考人数、平均分… …成绩分析项目(如图4)。而后在交叉对应单元格中输入函数公式完成各项成绩分析。
　　
　　3.巧用“名称框”
　　应用“名称框”在各工作表中快速定位粘贴位置“B4315”(如图4)。完成“全县考生”成绩表各项成绩分析后,逐项检查各项分析对应单元格输入栏中所使用的各函数公式均准确无误。首先,选中并复制“全县考生”成绩表中的成绩分析区域B4315∶V4327,然后,把成绩分析区域逐一粘贴到各成绩表B4315∶V4327位置。譬如,打开A中学Excel工作表,只需在“名称框”中键入“B4315”,按“回车”键激活单元格B4315,再粘贴,便能得到和“全县考生”成绩表布局完全一致的“A中学”成绩分析表。同理,只需在各工作表中B4315位置粘贴便能快捷地得到其余各中学、班级成绩分析结果。因为各中学、各班成绩表创设了相同的制表标准,粘贴后就如愿地在各中学、班级成绩表相同位置分别使用了完全相同的函数公式,由于各函数公式对其计算区域内空白单元格不做统计,所以成绩分析准确。
　　● 应用“三维引用”汇总多个工作表中的成绩分析数据
　　所谓“三维引用”就是Excel允许共享同一个工作簿中多个不同工作表的数据和公式,可在一个工作表中输入公式,而在另一个工作表引用数据。这种建立在工作表之间的数据链接,在被引用工作表中相关单元格改变数据后,会通过更新链接而自动更新公式计算的结果,或重新引用新的数据。
　　因此,我们可以在“成绩分析”工作表中编辑“三维引用”公式,绝对引用多个工作表的不同单元格数据来分别自动汇总各中学某学科的成绩分析,实现将多个工作表的数据合并到一个工作表内,以构建大型而复杂的“成绩分析”汇总表。绝对地址引用是指要把公式复制或填入到新位置,公式中的固定单元格地址保持不变,引用来的数据(公式或文本)便是源工作表中固定单元格地址中的数据(公式或文本)。使用绝对引用“$”符号达到对单元格地址的“冻结”。
　　点击“成绩分析”工作表标签,首先汇总“全县考生”总分各项成绩分析结果。
　　1.借助“=IF()”编辑“三维引用”公式
　　激活“成绩分析”汇总表内全县总分与参考人数对应的C4单元格并键入“=IF()”,将光标定位闪烁在括号“()”中间。然后单击“全县考生”成绩标签,激活总分参考人数对应单元格,此时查看输入栏中出现“=IF(全县考生!V4315)”公式。用复制粘贴功能使“成绩分析”汇总表输入栏中公式改为三维引用公式“=全县考生!$V$4315”,按“回车”键确认,便得到从“全县考生”成绩表中引用过来的与全县总分交叉对应的参考人数,“全县考生”是源工作表标签名,“!”为分隔引用工作表与引用单元格,“$V$4315”是绝对引用的单元格区域。
　　2.填充并修改三维引用公式
　　选中“成绩分析”汇总表内全县总分与参考人数交叉的单元格,使用填充功能填充全县总分、平均分、及格人数… …单元格,于是,各项输入栏中均得到同一公式“=全县考生!$V$4315”。由于“成绩分析”汇总表中与全县总分对应的各项成绩分析引用数据源来自同一工作表中同列不同行。此时,只需修改行号“$4315”便可得到与“全县考生”成绩表总分列各项分析数据所在单元格相对应的三维引用公式。例如,在“成绩分析”工作表中,全县总分优秀人数单元格对应的输入栏中将“$4315”改成“$4319”,即得到其三维引用公式“=全县考生!$V$4319”。
　　为得到引用各成绩表中总分分析的三维引用公式,以完成各中学、班级总分各项分析数据的汇总,往往需要替换工作表的名称,如“A中学”变为“B中学”,直接利用查找替换功能即可实现。
　　而引用各中学成绩表中各科分析的三维引用公式时,在完成汇总“总分”各项分析后,首先复制其分析结果的矩形区域,即C4∶H11区域数据,分别粘贴在“成绩分析”汇总表语文、数学、英语等科目所在区域内。此时,语文、生物等科目的成绩分析汇总区域内各引用公式暂与总分区域对应的三维引用公式相同,我们观察到所有科目在各中学成绩表中所处列号相同,依次为$V、$C、$F、$I、$L、$O、$R、$U,即引用不同学科成绩分析数据源的各函数公式之间只存在列号差异。因此,分别选中各科分析区域替换科目所在成绩表中列号,对语文科目而言,只需将引用来的总分区域各项公式中的总分列号“$V”替换为语文列号“$C”,替换方法同上所述。汇总其他科目的各项成绩分析数据的方法亦同此。
　　注:①替换各公式中的相同文字,适用于每组引用数据源来自“同行同列,但不同工作表”的情况,譬如,总分优秀人数因不同学校、班级而替换工作表名称。②替换各公式中的相同字母,适用于每组引用数据源来自“同一工作表且同行,但不同列”的情况。例如,“A中学”的平均分因不同学科而替换学科所处列号字母。