利用Excel为你自动改试卷

hnchimney

    在中学的各种测试中，选择题占的比例不少。例如，高中生物少则十几个，多则四、五十个；理科综合卷也有20个；英语则更多，达到了七十多个。这些选择题批改虽不很难，统计起来却很麻烦。条件好一点的学校，可以用阅卷机批改。但多数学校都没有相关设备，不能进行机器阅卷。

    其实，只要指法稍微熟练的教师，都可以利用Excel为我们自动批改选择题。只要事先做好框架，在框架中输入每个学生的答案，Excel就能自动计算出每个学生的得分，以及每个题目的得分情况。因为输入答案所用的“A”、“B”、“C”、“D”、“Tab”五个主要的键都由左手一个手来控制，所以能快速输入、自动批改和分析，非常方便。先看一个已做好，并已输入学生答案的工作表(如图1)。

 http://s13/bmiddle/4d317464044b6ebfc528c
图 1  一个已做好，并已输入学生答案的工作表

    该工作表中所示考试共25题，1~20题为选择题，21~25题为简答题，参加考试的学生为41人。批改选择题的老师先将正确答案输入“答案/分值”行中(图1中行59)，再将每个学生的答案输入工作表中相应题号下；批改简答题的老师手工批改完成后，先把每题的分值输入“答案/分值”行中(图1中行59)，再输入每个学生每题的得分。这样Excel就能自动统计每个学生的得分，并对每个题目进行分析。教师还可以根据自已的需求，添加要分析的项目。

    下面就来介绍一下如何制作框架。

    新建一个工作表(以全班41个学生为例)，第1行填工作表的标题。第2行填各列的标题，如“序号”、“姓名”及各题的题号等等；从第3行开始为每个学生的答案和成绩；第44行至最后一行为各题及学生总分的分析情况。下面就工作表中自动完成部分所用的主要公式做祥细介绍。

     1.批改选择题的公式：(以第43行为例)
    =(COUNTIF(E43,E$59)+COUNTIF(F43,F$59)+COUNTIF(G43,G$59)+COUNTIF(H43,H$59)+COUNTIF(I43,I$59)+COUNTIF(J43,J$59)+COUNTIF(K43,K$59)+COUNTIF(L43,L$59)+COUNTIF(M43,M$59)+COUNTIF(N43,N$59)+COUNTIF(O43,O$59)+COUNTIF(P43,P$59)+COUNTIF(Q43,Q$59)+COUNTIF(R43,R$59)+COUNTIF(S43,S$59)+COUNTIF(T43,T$59)+COUNTIF(U43,U$59)+COUNTIF(V43,V$59)+COUNTIF(W43,W$59)+COUNTIF(X43,X$59))*2

    该公式利用了条件计数函数COUNTIF()。例如：COUNTIF(E43,E$59)，表示，如果E43单元的值(学生的答案)等于E$59单元的值(正确答案)就计1。将正确选项的个数乘以每个选择题的分值，就得出了该学生选择题所得的分数。
 

    2.计算总分的公式：(以第43行为例)
    =SUM(Y43:AD43)
    SUM()为求和公式。以上公式为求第43行的学生的选择题及各简答题分数之和，也就是该学生的总分。

    3.排名次的公式：(以第43行为例)
    =RANK(AF43,$AF$3:$AF$43)
    RANK()为排位的函数。以上公式为求第43行的学生总分，在全班范围内(AF3到AF43)的排名。为了使复制公式到别的学生时，排名的范围不发生变化，所以在表示全班范围的“AF3:AF43”中使了绝对引用符“$”。

    4.统计选“A”的人数：(以第E列为例)
    =COUNTIF(E$3:E43,"A")
    使用条件计数函数COUNTIF()，计算每个选择题被选“A”的人数。如果该题的正确答案为“A”，还会自动将数字以红色显示，以引起人们注意。自动显示红色的方法是使用了“条件格式”，“条件格式”的设置在菜单栏中“格式—条件格式”中，“条件”为“公式 =E59=”A””。统计选“B”、“C”、“D”的人数的方法相同。

    5.统计选“A”的人数占全班总人数的比例：(以第E列为例)
    =E44/COUNTA(E$3:E43)
    用选“A”的人数除以总人数。统计选“B”、“C”、“D”的人数占全班总人数的比例的方法相同。

    6.计算平均分：(以第AG列为例)
    =AVERAGE(AG$3:AG43)
    AVERAGE()为计算算术平均值的公式。以上公式为求AG列第3行到第43行的平均值，也就是全班该门功课的平均分。

    7.计算及格人数：(以第AG列为例)
    =COUNTIF(AG$3:AG43,">="&AG59*0.6)
    以上公式为求AG列第3行到第43行中，分数大于或等于60的人数。公式中的“AG59”为存放试卷分值的单元格，当试卷的分值发生变化时，只修改该单元格的数值就可以了，避免了修改引用试卷分值的每一个公式的麻烦。计算优秀人数的方法相同。

    8.分段人数统计：(以统计90%~100%得分的人数为例)
    =COUNTIF(AG$3:AG43,"<="&AG59*1)-COUNTIF(AG$3:AG43,"<"&AG59*0.9)
    前一个COUNTIF()函数为计算满分及以下的人数，后一个COUNTIF()函数为计算90%分数的人数，两者相减，即得出90~100分率的人数。求其他分数段的方法与此相同。

    此外，还有几个简单的公式不再介绍。没有输入答案和成绩的空白工作表框架见如图2。

 http://s15/bmiddle/4d317464823a3d42df1fe
图 2  没有输入答案和成绩的空白工作表框架