利用excel 轻松实现记分册与试卷分析联动

张东仓

临近期末，各类学校需要对教学效果做出评定，需要给学生一学期各个科目的学习给出一个评定，记分册和试卷分析就是这一方面的重要教学资料，也是反映教学水平和指引教学改革的重要参照。购买这一方面的软件不一定适合本学校的具体情况，也不便于对其进行其它方面功能的拓展。能不能制作一个既方便广大教师平时操作，又适合学校的具体情况，也便于后续维护和拓展的这一方面软件或模版呢？

考虑到我院广大教师对办公软件一般都比较熟悉，excel 又具有强大的计算和数据处理能力。笔者利用空闲时间，结合我院的实际，2012年制作了一个试卷分析模版，受到广大教师的好评，今年由于教务对学生期末总评成绩的计算办法作出了调整：考察课、考试课等记分册中平时占40%，且包含四个子项：作业（占15%）、考勤（占5%）、课堂表现（10%）、平时测试（占10%），期末占60%，且总评成绩要求考察课五档制：优秀、良好、中等、及格、不及格。每个教师大都有多个任课班级，每班人数都在四五十人左右，这样数据处理的工作量很大，如何利用excel，结合以前的试卷分析实现同时处理数据，就显得尤为必要。经过笔者尝试，结合本学期期末自己的使用，自己编写的联动模版觉得很方便，现与大家进行分享。

1 记分册的设置与编写

（1）首先，平时成绩在教学接近尾声时就应处理好。因此，在excel 中制作记分册表格，由于g2 单元格未输入作业最高次数，m，j，k，s，u，v 等列数据均显示“#DIV/0!”，一旦输入次数，则会显示相应的数据。

（2）设置好该表格的页面，便于后面成绩处理完毕时打印。考虑到我院实际，记分册每面20 人，两面40 人，超过40 人，则在此表格的下方继续复制即可。

（3）默认每班不超过70 人，如超过，可对记分册模版和试卷分析模版同时修改，扩充人数即可。设置好记分册页面及表头后，准备学生名单。由于我院教务已在学籍管理系统输入了任课班级的学生名单，只需要导入即可，为了不对记分册造成干扰，在该电子表格下方，新建班级名单工作表.在将学籍系统中该班的学生名单直接粘贴至C 列。

（4）再在上面的工作表中的b3：b22 单元格做如下设置：将鼠标移到b3 单元格，在编辑兰输入“= 班级名单!$C$1”，将鼠标移到b4 单元格，在编辑兰输入“= 班级名单!$C$2”，……，同样依次设置b5:b22，以及m3:m22，b25:b44，m25:m34（注意：模版默认人数是70 人，班级名单每20 人占记分册一面）。这样，班级名单将快速进入记分册。

（5）接下来，在工作表的g2 单元格输入本学期作业的最高次数，将鼠标分别移到r2、g24、r24 单元，在编辑兰均输入“=$G$2”。在c3:c22、c25:c44、n3:n22、n25:n34 各列输入作业次数。考勤、课堂表现、平时测试三个子项均按百分制输入分数。以1号学生的平时成绩为例，将鼠标移到h3 在编辑兰输入“=(C3/$G$2*100*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”，同样设置其余学生对应的平时成绩，（当然，如果作业成绩直接就是百分制，则可以将鼠标移到h3 单元格，修改编辑兰为“=(C3*0.15+D3*0.05+E3*0.1+F3*0.1)/0.4”）。

注意：此时期末成绩由于还未进行试卷分析，暂时显示“0”。

（6）其次，设置总评成绩，即j 列，以1 号学生为例，将鼠标移到j3 单元格，在编辑兰输入“=H3*40%+I3*60%”，则总评成绩此时显示的期末为零分时的成绩。将鼠标移到i3 单元格，在编辑兰输入“= 试卷成绩!L7”，等待试卷分析工作表期末数据自动调入；如果是五档制，则需要设置k，v 两列。以1 号学生为例，将鼠标移到k3 单元格，在编辑兰输入“=IF(J3<60，"不及格"，IF(J3<=69，"及格"，IF(J3<=79，"中等"，IF(J3<=89，"良好"，"优秀"))))”，则1 号学生的总评成绩自动转换成五档制，只是现在还没有进行试卷分析，期末成绩还未传输过来。依照i3、j3、k3单元格的设置，分别设置其它学生期末成绩，总评成绩、等次等对应的单元格。

注意：如果学生休学或转专业则平时成绩可空缺。

2 试卷成绩工作表的设置与编写

（1）由于各个学校考试的性质不同，试卷分析涉及的具体项目亦不同。以我院为例，试卷分析主要涉及以下项目，相对比较简单，所以在该电子表格中创建试卷分析工作表。

（2）以上只是我院试卷分析的前两项，后两项主要是分析教学中存在的问题和经验教训以及教学建议，这里并未显示。根据上面的表头的要求，自己设计了下面的试卷成绩模版，如图1。

图1

图2

其中g1、i1、k1、f4、h4、j4、l4 七个单元格等待输入卷面信息。将鼠标移到a7 单元格，在编辑兰输入“= 记分册!$B$3”，则从记分册工作表调取1 号学生的姓名，以此类推，设置剩余的a8:a76 单元格，这时考试班级的学生姓名将陆续调入该模版，将鼠标移到b4 单元格，在编辑兰输入“=COUNTIF($A$7:$A$76，"<>0")”，则模版自动统计本班人数，这时输入表头信息，将d4 单元格设置为“=70- COUNTIF($B$7:$B$76，"")”，计算实际参加考试的人数，默认的是缺考学生第一题得分为空，即不输入成绩，将鼠标移到对应的总分输入“缺考”字样。默认考试共十道大题，修改模版进行超过可追加。其中b6:k6 为每题的满分，并将l6 单元格做如下设置：“=SUM(B6:K6)”，计算试卷总分。默认是100 分，当然可以设置的。然后，以1 号学生第一题得分为例，对b7 单元格作如下设置这样设置的目的是防止教师在输入每个学生的各题得分时超出该题满分或为负分，以此类推，设置1 号学生其它单元格对应的各题得分，并在l7 单元格设置“=SUM(B7:K7)”，计算该学生此次考试的总分，最后对其它学生各题得分做类似的设置。为了得到试卷分析的表头，在该工作表点的下方作了如下设计，如图2。

其中对应的各单元格的设置如下：

B78 设置为“=COUNTIF($L$7:$L$76，">0")- COUNTIF($L$7:$L$76，">39")”，计算成绩在039 之间人数；

C78 设置为“=COUNTIF($L$7:$L$76，">=40")- COUNTIF($L$7:$L$76，">59")”；，计算成绩在4059 人数；

D78 设置为“=COUNTIF($L$7:$L$76，">=60")- COUNTIF($L$7:$L$76，">79")”，计算成绩在6079 人数；

E78 设置为“=COUNTIF($L$7:$L$76，">=80")- COUNTIF($L$7:$L$76，">89")”，计算成绩在8089 人数；

F78 设置为“=COUNTIF($L$7:$L$76，">=90")- COUNTIF($L$7:$L$76，">100")”，计算成绩在90100 人数；

L78 设置为“=SUM(L7:INDEX(L:L，D4+6))/D4”计算全班平均分，缺考考生不参与平均；

B79 设置为“=B78/INDIRECT("D"&4)”，C79:F79 做类似的设置，计算成绩各档百分比；

B80 设置为“=AVERAGE(B7:B76)”，C80:K80 做类似的设置，计算各题平均分；

L80 设置为“=D79+E79+F79”，计算及格率；

B81 设置为“=(B6- B80)/B6”，C81:K81 做类似的设置，计算各题失分率；

B82 设置为“=MAX(B7:B76)”，C82:K82 做类似的设置，统计每题考试最高分数；

B83 设置为“=COUNTIF(B7:B76，B82)”，C83:K83 做类似的设置，统计每题最高分人数；

B84 设置为“=MIN(B7:B76)”，C84:K84 做类似的设置，统计每题考试最低分数；

B85 设置为“=COUNTIF(B7:B76，B84)”，C85:K85 做类似的设置，统计每题最低分人数；

B86 设置为“=ROW(INDIRECT("A"&MATCH(试卷成绩!B84，试卷成绩!B7:B76，0)+6))- 6&"号"”，C86:K86 做类似的设置，统计每题最低分学生学号；（只要一个学生学号）

B87:K87 输入考试题型；L82 设置为“=MAX(L7:L76)”，统计最高分；

L83 设置为“=INDIRECT("A"&MATCH(L82，L7:L76，0)+6)”，查找最高分学生姓名；

L85 设置为“=MIN(L7:INDEX(L:L，B4+6))”，统计最低分；L86 设置为“=INDIRECT("A"&MATCH(L85，L7:L76，0)+6)”，查找最低分学生姓名；

（3）试卷分析表的中单元格的设置。调取试卷成绩工作表中的相应数据填写到试卷分析表的中相应的单元格。以试卷分析表中全班人数为例，将鼠标移到试卷分析表工作表的A11（A11 与A12 合并）单元格，在编辑兰输入“= 试卷成绩!B4”，以此类推，将试卷分析表中的所有数据从试卷成绩工作表中调取即可。最终完成的试卷分析表格如图3：（考虑到个人隐私，对部分数据进行了处理）。

图3

这时即可得到相应的记分册，当然，记分册的封面亦可以通过单元格引用进行自动填写。

3 结束语

以上试卷分析和记分册模版能够实现快速的数据调用，提高了办公的高效率，为任课教师节约了大量时间，而且数据运算精度很高，能够使广大教师的从期末紧张的教学任务中解脱出来，真正实现信息化了办公。希望笔者的模版能给广大教师期末阅卷工作起到抛砖引玉的效果，不妥之处请批评指正。