巧用EXCEL中的函数快速解决考务管理中的几个常见问题

黄英

摘要：在教务管理中让教务人员最头疼的是每学期的学生期末监考安排、统计每门课程的及格率和监考安排，如何才能又快又准确的完成这些工作，本文将阐述自己在考务管理中如何使用Excel函数解决这些问题。

关键词：考务管理；函数

中图分类号：G64 文献标识码：A 文章编号：1009-3044（2015）10-0120

在日常的考务管理工作中会遇到大量的数据、繁杂的统计，无形中增加了许多工作量。Office 中的Excel 是功能丰富和数据处理能力强大的电子表格软件，具有数据筛选、排序、查询、统计等功能，许多信息的存储与处理都是通过Excel 电子表格来实现的。Excel 既容易掌握，又方便实用，是办公自动化的好助手。下面就如何运用Excel 的函数来解决考务管理管理工作中统计每门课程的不及格率、监考安排表中如何随机安排监考教师及监考场地等问题。

1 统计每门课程的不及格率

每学期学期初为了评价教师的教学水平都会统计每门课程的不及格率，由于每们课程在期末考试时并不是所有的学生都参加考试如有休学、缺考、病假等各种情况，如果采用常规的方法，对于学校班级数量多、课程多无疑是又费时而且还会发生差错，如果使用Excel中的SUMPRODUCT函数可以快速解决这个问题。尤其是职业学校，学校专业多、课程多，每个班级学生的人数是动态的（因为会出现转专业的学生），所以在统计每门课程的不及格率时必须考虑会出现的各种情况，在使用这个函数时需注意，当表示多个条件相与时，多个表达式之间使用*，当表示多个条件相或，即多个条件仅满足其中之一，多个表达式之间使用+，如果条件中有且和或时，要注意括号的使用。我在统计我校各门课程的不及格率时，考虑到有缺考的、作弊的和学生已经转专业但是在表中仍有其信息这三种情况，尤其是第三种情况由于在EXCEL中空白单元格的值默认为0，所以在设计函数时必须区分空白单元格和考试成绩为0的这两种情况，最后公式为：=SUMPRODUCT（（（F7：F65<60）*（0<=F7：F65）*NOT（ISBLANK（F7：F65））+（F7：F65="缺"）+（F7：F65="作弊"））/COUNTA（F7：F65）），在这个公式中分子表示所有分数在0到60之间且不是空白单元格的学生人数与缺考、作弊人数之后，分母中函数COUNTA表示统计所有非空单元格即表示这个班级实有人数。

2 统计不及格学生的姓名

在统计各门课程不及格学生的信息时，我们一般都是采用筛选功能，其实在现实情况中当E学生信息表连第一范式都不能满足时，使用筛选并不能实现我们的目的，所以我们可以通过Excel自身带的函数构造一个自定义函数来实现我们的目的，统计不及格学生的相关信息，我们自然会想到COUNTIF（）函数的使用，但是仅这个函数不能实现我们的目的，通过自定义一个函数便可以实现统计不及格学生的姓名，我设计的自定义函数如下：

Public Function bkxs（rng1 As Range， rng2 As Range， criteria As String， separator As String） As String

Dim arr（）

Dim rCell As Range

Dim i As Integer， j As Integer

On Error Resume Next

j = WorksheetFunction.CountIf（rng2， criteria）

If j > 0 Then

ReDim arr（0 To j - 1）

For Each rCell In rng2

If WorksheetFunction.CountIf（rCell， criteria） Then

arr（i） = rng1.Item（1）.Offset（rCell.Row - rng2.Row， rCell.Column - rng2.Column）.Value

i = i + 1

End If

Next

For i = 0 To j - 1

cif = cif & arr（i） & IIf（i <> j - 1， separator， ""）

Next

End If

End Function

自定义函数的使用与Excel的内置函数使用时一样的，下面这个公式便是该函数的调用：=bkxs（'14151'！$C$7：$C$60，'14151'！$F$7：$F$60，A2，"、"），最终得到如下图1所示结果：从这个自定义函数的代码段我们可以看出，关键的代码也是对内置函数COUNTIF（）的使用。

3 随机安排监考教师及考场

每学期期初与期末安排监考教师和考场，为保证公平我们希望监考教师和考场的安排具有随机性，保证每个教师的监考次数基本一致。下面简单叙述如何随机安排监考教师和监考考场。

首先将学校教师的各种情况进行分类，设置2张工作表分别是主考教师和监考教师，2张工作表中都有3列信息：考场、教师姓名、随机数。随机数我们通过随机函数RAND（）得到，考场通过对随机数的排序RANK（）函数得到，形成如下图2的主考表。其次监考教室的安排，根据学校各教室的使用频率分别设置主考教室和备考教室，备考教室实在某一场考试涉及的班级多时才用。主考教室和备考教室工作表中也是3列信息：考场、教室房间号、随机数，设置的方法与监考教师工作表的设置方法一样，形成如图3所示的主考教室表。最后是监考安排表的形成，监考安排表有5列信息：考试时间、考场、教室房间号、主考教师、监考教师。其中考场可以通过填充得到，而主考教师、监考教师、教室房间号可以通过函数VLOOKUP（）得到。通过以上步骤得到我们需要的监考表如图4所示。当然这个监考表也可以具体执行时进行局部更改，通过Excel的函数可以轻松地解决我们在排监考时的问题。

4 统计监考教师监考的次数

通过使用COUNTIF（）可以轻松统计每个教师监考的次数，而且还可以通过判断某场监考次数是否大于1来解决教师监考是否排重现象。

5 结束语

以上这些都是我在平时的教务工作中积累下来的的Excel函数的巧妙应用，希望大家能够借助我以上的实践经验，对以后的学习和工作有所帮助。

参考文献：

[1]Excel在教务管理中的综合应用[J].中国新技术新产品，2011（9）.