函数助力考试服务一条龙

Excel函数能够在考试分场、分数录入、成绩统计等方面发挥其自身的强大功能，帮助教师你从繁重、琐碎的工作中解脱出来。

学校每学期都要进行若干次大型考试，无论是年级的阶段考试还是整个学校的大型期中、期末考试，如果光靠手工加计算器的方式对考试情况进行分析统计，这项工作是多么繁重、烦琐。借助Excel这个重要工具，我们只需几个函数就能左手茶杯、右手鼠标，轻松完成从考试分场、分数录入到成绩统计等整个考试过程。

考试巧分场

考试分场通常的做法是确定每场人数后，在Excel中将学生按某个字段（如总分等）排好序，从上到下按每场人数一场一填充，那你试想如果有几十场，这种操作是不是非常的费时费力呀？那有没有比较简单的方法呢？

实例：在有1646条记录的表中填上为学生分配考号及考场号，50人一个考场，学生的考号构成由考场号+座位号，其中12代表高一年级第二次考试（图1）。

笔者的做法是在工作表中的C3、D3位置输入图中的公式，然后双击圆圈处的向下填充柄进行填充，这样就为每个考生按照命名规则分配了考号。

也许你看了上面的公式有些不知所措，不用着急，那你就听我给详细道来。

CONCATENATE函数的功能是将多个文本字符串合并成一个，它的语法是CONCATENATE（text1,text2,….），在这个公式中它是把“12”、考场号和座位号合并在一起。

TEXT函数的功能是根据指定格式将数值转换成文本，其语法是TEXT(value,format_txt)，value指的是数值，format_txt就是转换后的格式，具体在公式中两个TEXT分别将考场号和座位号转换成“00”格式，即01、02、03……

IF函数的功能是判断一个条件是否成立，如果成立返回一个值，如果不成立则返回另一个值，它的语法是IF(logical_test,value_if_true, value_if_false)，其中logical_test指的是判断条件，value_if_true指当条件成立返回的值，value_if_false指条件不成立时返回的值。

具体公式中第1个TEXT包括的IF函数的作用就是当当前行号减2除以50后，如果整除则返回1，如果不整除再判断，如果当前行号减2除以50后取余数，如果余数是0，则返回整数商，否则返回整数商加1，这样设置的目的是每50个人分配一个考场号，考场号依次加1。同理，根据当前行号减2后除50取余数，如果余数是0，分配座位号50，否则座位号就是余数，即1、2、3……49。至于为什么行号减2，就是因为表头占去了两人行，需要填充考号的单元格不是从第一行开始的。

INT函数的功能是将数值向下取整为最近的整数，其语法是INT(number)，number指的是被取整的数值。

MOD函数的功能是返回两数相除的余数，其语法是MOD(number,divisor)。

LEFT函数的功能是从一个文本字符串的第一个字符开始返回指定个数的字符，其语法是LEFT(text,num_chars)。

解释了这么多，你看明白了吗？不明白也没关系，你可以把它复制到你的工作表中，使用时只需把其中的50换成相应的人数就行了。

成绩录入巧锁定

由于大量的录入工作不可能是一个人完成，所以我们可以把考号复制粘贴到一个新表中，注意：复制粘贴的时候，要选择“编辑→选择性粘贴”菜单，选择“数值”。在进行成绩录入时，一般都是教师根据考号按考场利用数字小键盘进行盲打录入，由于人数众多，录入难免有出错的时候，为了尽量减少出错，录入分数时不超出范围，比如满分为150分，如果比150分多或是小于0分就报错，需要将分数限制在一定的范围之内。我们可以选定需要录入的分数列，比如语文列，然后选择“数据→有效性”，在弹出的窗口中进行分数范围设置（图2）。

平均分、分数段人数巧统计

1. 分数汇总、排名及各科平均分统计

我们把分场表中的数据复制到一个新表中，注意复制粘贴的时候，要选择“编辑→选择性粘贴”菜单，选择“数值”。将录入完成后的各科成绩汇总到这个表中。然后，用SUM、RANK、AVERAGE、IF函数对表中数据进行成绩汇总、排名及求各科的年级平均分（图3）。

小提示：在图中的求年级平均公式中，在D1650单元中输入：=AVERAGE(IF(D$3:D$1648<>0,D$3:D$1648))，表示返回D3:D1648区域中不等于0的数值平均分。注意，输入完后，要按住Ctrl+Shift+Enter组合键用{}将它组成数组公式，方能得到正确结果。数组公式执行多个计算而生成单个结果。

2. 各班各科平均分及分数段人数统计

统计各班各科平均分及分数线人数，一般采取的方法是一个班一个班的自动筛选或是分类汇总或是手工计算，这在整个年级成绩库中做这些工作确实很麻烦、很烦琐。笔者设计了两人个公式能够轻松搞定上述任务，把你从繁重的工作中解脱出来。

如果统计各班各科平均分，首先新建一个表，设置好表头、班级、科目，在需要统计平均分的位置输入图中的公式（图4）。

上述公式也属于数组公式，所以输入完后也要按住Ctrl+Shift+Enter组合键用{}将其组合。其表示为返回班级等于A列中指定班并且分数不等于0的单元格的平均值。公式中为了进行向下或向右填充，所以不能将单元格区域设置成绝对区域。

如果统计各班的分数段人数，也需要建立一个新表，设置好表头、班级和所需分数线，在需要统计分数线人数的位置输入图中的公式，道理和求平均分是一样的（图5）。

成绩分析巧统计

对于考试成绩的分析对于任课教师来说是特别重要的，尤其是优秀率、及格率和低分率的统计。如果在年级成绩汇总表中一个班一个班的、一个学科一个学科的统计，我们的工作量是可想而知的，但是如果利用SUMPRODUCT函数就可以使我们的工作化繁为简，轻松自在了。

首先先建立一个新表，输入如表头及班级，并把要填写数据的单元格设置为百分比格式，然后在相应单元格中输入公式（图6）。

你一看这个大公式是不是有点懵了？咱们先看看SUMPRODUCT这个函数的作用吧。

使用SUMPRODUCT可以进行多条件计数，其语法是=SUMPRODUCT((条件1)*(条件2)*(条件3)*…(条件n))，作用是统计同时满足条件1、条件2到条件n的记录个数。

这下，你能够看懂图中公式的含义了吧？当然，不同的学科所卡的优秀率、及格率、低分率的分数也都不一样，并且各学科在成绩汇总表中所对应的列也都不一样，这都需要你在公式中修改。但是，我们只求一个班的，其他班的直接填充就可以了。

诛仙传BT

雷霆狂暴安卓版

光之萌约星耀版