用Excel函数 整理期末成绩表
数字办公
统计分析完期末考试成绩，教师们就可以外出“行万里路”了。如果你不幸要从年级成绩表中提出本班的数据，偏偏又采用手工统计的方式，工作效率就会特别低，极有可能耽搁你的避暑旅游计划，怎样提高工作效率？下面就以统计分析“2007年XX学校初三期末考试统计表.xls” 为例（内含汇总表、分析表、打印成绩单表），介绍用Excel函数统计分析的详细过程。
一、统计出总分与排名
很多学校采用了多个班级混合排考场的方式，因此学生成绩统计表可能出现全年级混合统计的情况。为了便于统计，可以先定义几个名称。打开“2007年XX学校初三期末考试统计表.xls”文件，执行“插入→名称→定义”菜单命令，在弹出的“定义名称”对话框中定义如下名称：
“总分”的引用位置为“=OFFSET(汇总表!$N$3,0,0,COUNTA(汇总表!$D:$D)-1,1)”；“班级”的引用位置为“=OFFSET(汇总表!$D$3,0,0,COUNTA(汇总表!$D:$D)-1,1)”；“考号” 的引用位置为“=OFFSET(汇总表!$B$3,0,0,COUNTA(汇总表!$D:$D)-1,1)”；“姓名” 的引用位置为“=OFFSET(汇总表!$B$3,0,0,COUNTA(汇总表!$D:$D)-1,1)”。
小提示：考试统计表基本上所有形式都可以这样输入，只不过位置不一样结果不一样，下面的定义也类似。
“总表” 的引用位置为“=OFFSET(汇总表!$C$3,0,0,COUNTA(汇总表!$D:$D)-1,14)”；“S”的引用位置为“=OFFSET(汇总表!$D$3,0,MATCH(分析!O$3,汇总表!$E$2:$M$2,0),COUNTA(汇总表!$C:$C)-1,1)”。
在 “总分”下N3单元格中输入公式“=SUM(INDIRECT("D"&ROW()&":L"&ROW()))”；“本班级排名”下O4单元格中输入数组公式“{=SUM((班级=D3)*(总分>N3))+1}”；“本部级排名”下P4单元格中输入公式“=RANK(N3,总分)”。三个公式分别用来计算当前学生的总分、在本班的排名以及整个级部的排名情况，然后将公式复制到对应的单元格区域中即可。
小提示：由于{=SUM((班级=D3)*(总分>N3))+1}为数组公式，在输入时必须是先输入公式“=SUM((班级=D3)*(总分>N3))+1”，然后同时按下“Ctrl+Shift+Enter”组合键，才能得到公式两侧的花括号，直接输入不是行的。
最后选择“班级”所在的D列，执行“数据→筛选→自动筛选”命令，然后选择相应的班级名称，如1班，就得到1班所有学生的成绩情况了（图1）。
二、快速分析本班成绩
成绩分析一般都要包含班级的平均分、最高分、最低分以及合格率情况，这些都可用Excel的计算工具来实现。
切换到“分析”工作表，单击F2单元格，执行“数据→有效性”命令，在弹出窗口中选择“允许”列表框中的“序列”选项，在“来源”中输入班级的名称（其中的分号在半角英文状态下输入），同时选中“提供下拉箭头”复选项，然后单击“确定”按钮即可。
这里以语文统计为例，在“平均分”C5单元格中输入数组公式“{=IF(ISERROR(SUM((班级=$F$2)*S)/SUM((班级=$F$2)*(S>0))),"",SUM((班级=$F$2)*S)/SUM((班级=$F$2)*(S>0)))}”；“最高分”C6单元格中输入数组公式“{=MAX(IF(班级=$F$2,S,0))}”； “最低分”C7单元格中输入数组公式“{=MIN(IF(班级=$F$2,S))}”。
“合格人数”C8单元格中输入数组公式“{=SUM((班级=$F$2)*(S>=C4*0.6))}”；“合格率” C9单元格中输入数组公式“{=C8/SUM((班级=$F$2)*(S>0))}”，这些公式分别用来统计平均分、最高分、最低分、合格人数以及合格率（图2），其他科目统计分析也按照如上操作。
小提示：为了能够在“分析”工作表的标题行中明确显示具体班成绩资料，可以在此行输入公式“="2007年XX学校初三期末考试成绩"&F2&"统计分析明细表"”，这样标题中的班级名称自动随着单元格内容的更新而更新了。
三、制作打印成绩单
在“打印成绩单”工作表中输入标题，同时使用数据有效性工具在E2单元格设置下拉列表框，以便选择不同班级打印成绩单。
在A3单元格中输入数组公式“{=IF(ROW()/3-1>=COUNTIF(班级,$E$2),"",CHOOSE((MOD(ROW()+1,3)+1),"",汇总表!$A$2,INDEX(考号,SMALL(IF(班级=$E$2,ROW(班级)-2),COUNTIF($C$3:C3,C3)))))}”；B3单元格中输入数组公式“{=IF(ROW()/3-1>=COUNTIF(班级,$E$2),"",CHOOSE((MOD(ROW()+1,3)+1),"",汇总表!B$2,INDEX(姓名,SMALL(IF(班级=$E$2,ROW(班级)-2),COUNTIF($C$3:C3,C3)))))}”。
C3单元格中输入数组公式“{=IF(ROW()/3-1>=COUNTIF(班级,$E$2),"",CHOOSE((MOD(ROW()+1,3)+1),"",汇总表!$C$2,$E$2))}”；在D3~M3单元格中输入公式“=IF($C3="","",CHOOSE((MOD(ROW()+1,3)+1)；"",汇总表!$2:$2,VLOOKUP($B3,总表,COLUMN()-1,0)))”（图3），并将这些公式复制到相应的单元格中，就得到了学生成绩单，最后打印出即可。
编后：采用函数进行成绩统计分析的方法非常适合大批量的混班成绩统计工作，且工作效率极高、结果准确。如果不是混班成绩，也可利用该方法的原理统计分析，只要把公式简化即可。虽然应用定义名称、函数较多，但是只要照猫画虎，还是很简单的，推荐使用。