函数助力 考试服务一条龙

精品文档-下载后可编辑函数助力考试服务一条龙学校每学期都要进行若干次大型考试，无论是年级的阶段考试还是整个学校的大型期中、期末考试，如果光靠手工加计算器的方式对考试情况进行分析统计，这项工作是多么繁重、烦琐。借助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）。

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

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

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

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

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

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

成绩分析巧统计

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

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

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