巧用Excel处理学生成绩

刘广孚，殷爱贞

0 引言

高校扩招后，学生数量增加，考试完后成绩处理的工作量也增大了。当学生班数较多，成绩组成较复杂时，就需要花较长的时间进行繁琐的成绩汇总和分析，这样出错的可能性也大大增加。虽然出错的只是少数，但对于学生来说却关系重大，甚至可能关系到能不能拿到学位，直接影响以后的就业等等问题。所以，作为一名教师，必须避免在成绩统计过程中出现错误。Excel 作为功能极强的电子表格软件，可应用于学生成绩处理，避免错误并大大提高处理效率，辅助完成大量的、繁琐的、重复性的计算和统计工作[1] [2]。

现在很多老师都借助于Excel进行成绩处理，一般只用其最基本的计算功能，把作业、考试、实验成绩等表格的结果复制到一个新表单中，再按照一定的算法进行计算得到汇总成绩。这种处理方法虽然简单，也确实减轻了老师的计算负担，并且不要求使用者对Excel有太深的了解，但存在着工作量大、容易操作失误的缺点。

虽然Excel具备VBA编程的功能[3]，不少人利用其编程功能进行成绩统计，但使用者必须具备VBA编程知识才能使用。作者在使用Excel软件进行成绩处理的过程中，不断摸索改进，制作了切实可行的模板，大大减轻了成绩处理的工作量，达到了事半功倍的效果。

本文以我校的国家级精品课—电工电子学课程的成绩处理过程为例，详细说明成绩处理模板的设计过程和使用方法。

1 设计思想

电工电子学(二)课程的上报成绩分为平时成绩、期末考试成绩和总评成绩，而平时成绩由作业成绩和实验成绩构成，实验的电机控制实验由张老师负责，而数字电路实验由郑老师负责。所以学生成成绩由四部分组成：作业成绩、考试成绩、张老师负责的实验成绩和郑老师负责的实验成绩，这四部分成绩都使用Excel软件进行统计生成，分别保存在一个Excel文件中，每个班又分别占用一个表单。

本文将利用这四部分成绩进行成绩汇总和统计分析，并考虑到可能各部分成绩会做修改甚至大的调整，以及名单可能不一致。如果处理不当，可能会使得工作量很大，甚至出现严重错误，造成很坏的影响。

由于分数对于学生是非常重要的，所以作为教师绝对不应该给学生错误的成绩。为了解决这个问题，作者针对成绩汇总和处理中的问题，充分利用Excel的处理功能，总结出了切实可行的处理方法。

本文的指导思想是，先对第一个班进行成绩处理，并生成成绩处理模板，其他班的成绩处理在模板的基础上稍做修改即可。所以下面所讲的都是模板的生成过程。本文介绍两种成绩汇总模板的生成方法，第一种方法对使用者的Excel知识要求不高，比较容易理解，模板的使用操作也比较简单；第二种方法对Excel的知识要求稍高，生成模板的过程有些复杂，但模板的使用却非常简单，在以后的成绩处理中只需非常少的改动即可，甚至只改动一个单元格的内容就能实现另一个班的成绩处理，效率是非常高的，并且该模板的通用性较好，达到了一劳永逸的效果。

在制作模板前，应该先做好以下的准备工作。

1) 准备好各种成绩的Excel表格文件。

本例中，这些文件为：“考试成绩.xls”、“作业成绩.xls”“实验成绩_张.xls”、“实验成绩_郑.xls”。这些文件中，每个文件都包含5个表单，分别为5个班的单项成绩，表单的名称均以班名来命名，分别为：车辆0701、车辆0702、安全0701、安全0702和安全0703。其实，有5个班还是50个班对模板的制作并没有影响，主要关注的是第一个班的处理方法。

2) 打开这4个文件。

3) 生成一个新的Excel文件。

新文件名为：“成绩汇总.xls”，用于对单成绩进行处理，并最终形成模板。把第一个表单的名称改为“车辆0701”。

2 成绩的直接引用模板

该方法的指导思想是，利用Excel软件的文件间数据引用的功能，把各单项成绩引用到成绩汇总表中，再进行成绩汇总和分析，并用函数实现各文件间的名单核对。

本文成绩处理的基本思想是：先把一位同学的各项成绩汇总到一张表中，再进行处理得到总成绩；先处理一位同学的成绩，再复制公式而处理其他同学的成绩；先处理一个班的成绩，再套用模板处理其他班的成绩。所以处理第一位同学的成绩的方法是本文的关键。

成绩处理模板的生成主要由以下5个步骤组成：

第1步，完成第一个班的第一位同学的信息成成绩的引用。

第2步，完成第一个班的第一位同学的成绩处理。

第3步，检验各份成绩单的学生名单是否一致。

第4步，完成第一个班的所有同学的成绩的处理。

第5步，对第一个班的成绩进行分析，生成模板。

第6步，以第一个班为模板，完成对所有班的成绩处理和分析。

下面详细介绍模板的生成方法。然后现简单介绍模板的使用方法。

第 1步，完成第一个班的第一位同学的信息及成绩引用。

在Excel中，可以把不同文件不同表单的单元格的内容引用到当前单元格中。这样就可以把每位同学的学号、姓名、作业成绩、实验成绩、考试成绩都引用到一个新的表单中。这样有两个好处：一是不对原数据表格进行直接操作，避免操作不慎修改了原始数据，二是当原始数据变动时，引用过来的数据也同时更新，不必进行两次操作。

先把第一个班—“车辆 0701”的第一位同学的所有主要相关信息都引用到汇总表单中。下面以学生学号为例说明引用的方法。

学号：每个成绩表中都有学号和姓名，本例中从考试成绩表格中进行引用。第一位同学的学号在“考试成绩.xls”的“车辆 0701”表单中位于 A4单元格，需要引用到“成绩汇总.xls”的“车辆0701”表单的A4单元格，则在“成绩汇总.xls”文件的“车辆 0701”表单的 A4单元格中输入 “=[考试成绩.xls] 车辆0701!A4”，该公式表示当前单元格(A4)的内容等于“考试成绩.xls”的“车辆 0701”表单的 A4单元格的内容，即第一位同学的学号。

姓名：与学号的引用方法类似，在B4单元格中输入引用公式“=[考试成绩.xls] 车辆 0701!B4”。

作业成绩：在 C4单元格中输入引用公式“=[作业成绩.xls] 车辆 0701!G4”。

郑老师的数字电路实验的成绩：在实验成绩中，第一位同学处于第5行的位置，三个实验的总成绩位于R5单元格，所以在汇总表的 D4单元格中输入公式“=[实验成绩_郑.xls] 车辆 0701!R5”。

张老师的电机控制实验的成绩：第一位同学也处于第5行的位置，两个实验的总成绩位于M5单元格，所以在汇总表的 E4单元格中输入公式“=[实验成绩_张.xls] 车辆0701!M5”。

期末考试成绩：在F4单元格中输入公式“=[试卷成绩.xls] 车辆 0701!L4”。

这样，第一位同学的所有信息及成绩都引用到新的表单中，可以进行成绩处理了。

第2步，完成第一个班的第一位同学的成绩处理。

在学生成绩处理中，需要得到学生的实验成绩、平时成绩和总评成绩，还要按总评成绩分出优、良、中、及格和不及格5个档次。因为学生成绩来自于4个文件，为了避免张冠李戴，还要进行名单审核。

张老师负责了2次实验，郑老师负责了3次实验，所以实验成绩的计算公式为“=D4*0.6+E4*0.4”，放在G4单元格。

在平时成绩中，作业成绩占1/3，实验成绩占2/3，并且该成绩一般应取为整数，以便上报。平时成绩放在H4单元格，计算公式为“=ROUND((C4+G4*2)/3,0)”，其中ROUND为四舍五入的取整函数。

在总评成绩中，平时成绩占 30%，考试成绩占 70%，结果取整，放在 I4单元格，计算公式为“=ROUND(F4*0.7+H4*0.3,0)”。

在总评成绩中，借助于条件格式用不同的颜色醒目地标示低于60分的不及格成绩，方法为：点击第一位同学的总评成绩单元格 I4，选择主菜单的“格式”下的“条件格式”，选择“单元格数值”和“介于”，在右边的框中分别输入“1”和“59“。点击“格式”，在弹出的界面中选择“颜色”，选中红色作为不及格的标志，“确定”。不及格的分数将显示为红色，其余仍为黑色。

参加正常考试的学生成绩不可能为零。如果等于零，则意味着没有参加考试或已转出该班，不应参与成绩分析。为了避免出现零分而影响以后的统计分析，将I4单元格的内容 改 为 “ =IF(ROUND(F4*0.7+H4*0.3,0)＞0,ROUND(F4*0.7+H4*0.3,0), "")”。

成绩档次的划分利用 IF语句完成，为“=IF(I4＞=90,"优",IF(I4＞=80,"良",IF(I4＞=70,"中",IF(I4＞=60,"及格",IF(I4＞0,"不及格","")))))”，其中，等于零的分数的成绩档次为空白，表示该学生为非正常学生，不参与成绩统计。

第3步，审核各份成绩单的学生名单是否一致

由于不同的文件是由不同的老师生成和填写的，如果班中有增减人的情况，就可能出现名单不完全一致的情况。如，有的老师把留级生放在前边而有的放在后边，有的把转走的学生从名单中除去而有的则保留，这样在引用时就会造成“张冠李戴”的成绩错乱。为了有效地避免这种情况出现，同时减轻老师审核的负担，在表单中用公式对各个文件的名单进行核对，以确认同一行中的成绩都来自于同一位学生。

利用IF函数实现名单的审核，如果一致，结果为“√”，如果不一致则指出与哪份名单不同，如果各份名单的名字均为空白则表示已到名单末尾，则结果为“空白”。公式为：

=IF(B4＜＞[作 业 成 绩 .xls] 车 辆 0701!B4,"作 业",IF(B4＜＞[实验成绩_郑.xls] 车辆0701!B5,"郑",IF(B4＜＞[实验成绩_张.xls] 车辆 0701!B5,"张",IF(B4=" ","空白","√")))

因为名单是从“考试成绩.xls”引用的，所以不再审核该文件。名单的审核可以避免大范围的成绩错误的发生，尤其在班数众多时，非常有效。

第4步，完成第一个班的所有同学的成绩的处理。

在完成了第一位同学的信息引用和成绩处理后，通过复制公式的方式就可以完成其他同学的成绩处理了。因为第一位同学成绩引用时用的是相对引用，所以可以很方便地利用Excel的填充柄的功能进行公式复制。

例如，要实现对该班所有学生的学号的引用，进行以下操作即可。

用鼠标点击A4单元格，再移到该单元格的右下角，当光标变成实心十字时，按住鼠标左键向下拖，当松开左键，就可以看到被拖过的单元格中出现了学生的学号，再从最后一个学号开始继续向下进行填充复制，直到出现空白内容为止，就实现了所有学生的学号引用。

用同样的方法，对B4到I4的公式都用填充柄向下复制，就可以实现姓名和各项成绩的引用，以及成绩的处理。名单审核的公式也同样复制，当名单出现不一致时，就会出现相应提示。

这样就实现了第一个班的所有同学的成绩处理。截图如图1所示。

图1 成绩引用和处理后的结果截图

第5步，对第一个班的成绩进行统计分析

对一个班的成绩分析可以有多项内容[4]，本文只统计几项主要内容：考试人数、平均成绩、方差、每一档次的人数。

由于每个班的人数是不相同的，这给统计工作带来困难。有的文献建议用的范围宽一些，比如考虑一个班最多不会超过50人，那就按照50人来统计。但作者建议利用Excel的整列处理的功能进行统计。

当使用整列处理功能时，C-I列的学生成绩下方单元格不再使用，而将统计信息放在右侧，即L列之后。

总评成绩平均值的计算公式为“=AVERAGE('总评')”，或“=AVERAGE(I:I)”，两个公式的结果是一样的，都表示对第 I列的所有数字求平均值。而标准差则用公式“=STDEV(I:I)”。

考试人数的统计公式为“=COUNTIF(I:I,"＞0")”。

统计每档次的人数，以良好为例，公式为“=COUNTIF(I:I,"＞=80")-COUNTIF(I:I,"＞=90")”，即 80 分以上的人数减去90分以上的人数，就是80～89的人数。

至此，一个班的成绩统计全部完成。部分截图如图 2所示。

图2 成绩统计截图

第6步，以第一个班为模板，完成对所有班的成绩处理和分析。

上面完成了一个班的学生成绩处理，其实也同时生成了一个成绩处理模板。

把车辆0701班的表单进行复制，更名为“车辆0702”。在车辆0702表单中，只需进行两项操作：

1) 修改引用

把第一位同学的信息和成绩的引用公式，以及审核公式中的“车辆0701”更改为“车辆0702”。

2) 复制公式

用填充柄对所有引用的公式及审核的公式进行复制。因为其余公式都是用的本表单的数据完成的，所以不必进行任何修改或复制。

这样通过对模板的简单修改就完成了另一个班的成绩处理。这种方式简单易学，可以在成绩处理过程中提高效率和准确率，减轻老师的负担。

按照这种方式就可以实现多个班以及下一次考试的成绩处理了。

3 成绩的间接引用模板

下面介绍一种更简单的成绩处理方法，只需修改班名称就可以实现另一个班的成绩处理。

上面的直接引用法在处理其余班的成绩时，重新修改引用公式并复制公式就可以了，故称为直接引用法。但在成绩的引用上仍有缺憾，因为每一列的引用都源自于同一个表单，但在处理时还需要重新复制公式。如果把这种修改集中在一个单元格中，处理效率就会更高。间接引用法就是基于这种思想进行处理的。

第1步，建立引用信息表

在空余单元格中设计如图3所示的表格。在表格中，将被引用的引用的信息内容进行拆分。表格中输入以下信息：

1) 班名(实际为表单名)。在本例中各文件的表单名均与班名一致，修改其内容即可实现另一个班的成绩处理。

2) 各成绩文件的文件名。当处理其他考试的成绩时，修改成实际的文件名称即可。

3) 列号，即各个成绩文件的平均成绩所在的列号。

4) 起始行号。不同的老师建的表格中第一位同学所在的行号会有所不同。

5) 行号补差。被引用表格的起始行号与汇总表格的起始行号间的差值，引用时用来计算被引用单元格的行号。

图3 被引用表格的信息表

第2步，生成间接引用公式

因为引用成绩时，涉及到不同文件、不同表单、不同列与不同行，所以将这些因素作为变量处理，已经列在了表3中。下面用INDIRECT函数实现对可变内容的引用。

INDIRECT函数的功能是返回由文本字符串指定的引用[5] [6]。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身，使用函数INDIRECT会非常方便。

该函数的语法为 INDIRECT(ref_text,a1)。在本例中，Ref_text的内容为对单元格引用的内容，a1省略。例如，“INDIRECT(“[作业成绩.xls] 车辆 0701!G4”)”就相当于“=[作业成绩.xls] 车辆 0701!G4”。

进一步，在 L1单元格中输入内容“[作业成绩.xls] 车辆0701!G4”(注意没有等号)，则该引用还可以写为“INDIRECT($L$1)”,其中“$”表示绝对引用。

INDIRECT方法是按照字符串的内容执行引用的，前面所讲的引用方法不同。“=[作业成绩.xls] 车辆 0701!G4”中的G4是相对引用，用填充柄复制公式时其值是随单元格位置不同而变化的，而 INDIRECT方法中的“G4”是一个字符串内容，不会变化。而在成绩处理时，信息引用需要随位置变化而变化，本文用拼凑的方法实现，即，把引用中的几项关键部分拆开成几个便于修改的变量，当使用时，只需把几个部分填写正确就可以了。

当完善了所有被引用文件的相关信息后，就可以编辑公式进行引用了。在A4单元格中输入学生考试成绩的引用公式“=INDIRECT(M3&M2&"!"&P3&(ROW()+(R3)))”。其中“M3&M2&"!"&P3”等效于“[试卷成绩.xls] 车辆 0701!L”，ROW()为当前单元格的行号，R3单元格中为要补偿的行数，为了在其为负数或零时不使公式出错，故用“(R3)”表示，而“(ROW()+(R3))”则为应该引用的单元格的行号。

其他引用公式类似。因学号和姓名的列数是固定的，故不作为变量处理。

第3步，成绩处理及统计，生成模板

把所有的引用公式向下进行足够多的复制，信息及成绩的引用就完成了。

成绩处理和统计的方法与直接引用法相同，不再赘述。

这样，间接引用模板就生成了，如图4所示。

图4 间接引用的成绩处理模板部分截图

但每班学生人数不一，究竟复制多少算合适呢？为了防止出现遗漏，在复制审核公式时，一定要比其他公式多复制一行，这样就保证一旦遗漏就必然出现审核错误，从而消除这一隐患。

第4步，利用模板处理成绩

利用该模板处理学生成绩时，可以进行表单复制，通过修改“班名”实现一个班的成绩汇总及统计，也可以直接利用模板，仅修改班名就可实现另一个班的成绩汇总及统计，从而实现了“一键搞定”的学生成绩的“动态”处理。

当用于其他课程的成绩处理时，只需改动文件信息和一些必要的表格文字就可以，如果成绩的计算公式不同，则还需改动公式并复制，从而形成新的模板，再进行成绩处理。

4 结论

本文巧妙利用Excel的公式处理功能，实现了两种成绩处理模板。第一种模板(直接引用法)操作简单，易学易用，第二种模板(间接引用法)在使用时非常方便快捷，可以达到“一键搞定”的效果，处理一个班的成绩仅需1～30秒的时间即可。不论用哪种方法，都大大提高了成绩汇总处理的效率，把老师从繁琐的数据处理中解放了出来。

本文独特的名单审核功能成功地避免了学生成绩“张冠李戴”的可能，保证了成绩的准确性，同时减轻了老师审核的负担。

总之，本文提出的学生成绩处理的方法大大提高了成绩处理的效率，能够广泛应用于各种课程的成绩汇总处理，具有很好的推广价值。

[1] 于爱兵.Excel 在实验数据处理中的应用[J] .微型电脑应用,2001年第17卷第4期:63-64.

[2] 刘玉英.Excel 函数在成绩统计表中的应用[J] .中国科技信息,2005年第14期:85,73.

[3] 曾宪栋,陈菲.学生成绩统计及考试成绩质量分析软件的研制[J] .广东药学院学报,2006年 8月第 22卷第 4期:476-477.

[4] 俞能福.成绩统计与试卷分析的Excel实现[J] .安徽建筑工业学院学报(自然科学版),2008年 10月第 l6卷第 5期:104-107.

[5] 张泽江.Excel 在工程算量中的运用技巧[J] .山西建筑,2009年4月第35 卷第10期:362-364.

[6] 安维默,EXCEL功能的扩展[J] .北京统计,2004-4,总第168期:52-54.