Excel答案非唯一考核点自动阅卷技术分析

陈永松

0 引言

Excel软件的使用是计算机基础最重要的实验教学环节之一，在Excel实验系统和考试系统中实现自动阅卷，对提高学生学习效果和减轻教师工作量都大有帮助。现有的Excel操作题自动阅卷研究，基本上都是利用VBA技术实现自动阅卷[1-3]，但多数研究只介绍答案唯一考核点阅卷方法[1,3]。Excel有一些操作，答案并不唯一，如果不进行适当的处理，很容易出现误判、漏判。Excel最可能出现出现答案非唯一的考核点包括高级筛选、数据库函数条件区域的设置和复杂函数的使用，本文将针对这两种考核点，举例说明实现准确自动阅卷的方法。

1 条件区域自动阅卷技术分析

高级筛选和数据库函数的使用都是Excel最重要的考核题型之一，其中条件区域的设置可能会有n种结果。例如从数据清单中高级筛选出学年总成绩大于等于400但小于500或者女学生信息，就可能有多种不同的定义条件区域方法，其中的两种定义方法如表1和表2所示，在标准答案中要穷举各种可能的条件区域是不现实的。可采用排序法[4]实现条件区域的自动阅卷。即对学生定义的条件区域依次按第一行、第二行、……排序，然后再按第一列、第二列、……进行排序，经过调整后将学生答案和标准答案进行比较，即可实现自动阅卷。例如表1定义的条件区域经过排序处理后可转换成表2，这样只需将标准答案设置成表2形式（如果提供其他答案可以按同样的方法进行转换后作为标准答案）即可实现对不同答案的检验。

?

?

2 公式函数自动阅卷技术分析

针对函数使用的多样性，文献[2]提到了一种方法，先判断考生是否输入函数，如果是函数则检验考生文档中应用函数计算出来的结果值，如果与参考答案的结果值相同则认为函数正确。这种方法用“=结果”的方式输入函数会误判为正确，为避免这种情况，使用这种方法进行阅卷必须同时检验函数名或运算符，只有函数名、运算符、结果都正确才能认为是正确的。但即使这样，还是很容易产生错判。例如使用 RANK函数并进行复制操作，有一种典型错误是第2个参数误用相对坐标，但如果考生不用复制，而是每个单元格分别输入函数，则计算出来的结果值和参考答案的结果值相同，这种方法将误判为正确。为提高阅卷的准确率，函数的阅卷应以函数表达式的检验为主。前面所介绍的排序法也可用于函数阅卷，例如4个数相加有多种组合，但排序后就剩一种组合了。除排序法外，还有四种方法可用于函数阅卷，下面分别进行介绍。

2.1 等效引用法

这种方法适用于使用不同方式引用单元格都正确的函数阅卷。例如根据A1:E49数据清单的数据(车间在C列，利润在E列)，使用SUMIF函数在单元格H2计算G2单元格内容对应车间的利润合计，然后复制公式到 H3:H10区域中。这时H2输入函数“=SUMIF($C$2:$C$49,$G2,$E$2:$E$49)”是正确的，然而输入函数时部分或全部列号没有加上“$”符号也是正确的。如果将每种可能答案都列举不太现实。采用等效引用法进行阅卷，阅卷时对复制的区域进行分析，如果复制区域的列标相同，则将参考答案和学生答案中列标前的“$”全部去掉，如果结果相同判断为正确。在本例中，只要学生输入的函数等效于“=SU MIF(C$2:C$49,G2,E$2:E$49)”则结果正确。同样如果复制区域的行号相同，或者输入的函数含有工作表、工作薄地址，可采用类似的方法进行处理。

2.2 列举法

对于只有少数几种正确结果的函数可采用列举法。例如根据H2:H26的总分，使用RANK函数在单元格I2计算H2单元格对应总分的名次，成绩越高名次越靠前，然后复制公式到I3:I26区域，经等效引用法处理后仍有三种答案正确，即“=RANK (H2,H$2:H$26,FALSE)”、“=RANK(H2,H$2:H$26)”和“=RANK(H2,H$2:H$26,0)”，这时可以用列举法，即只要是这三种答案中的一个就认为是正确的。

2.3 特殊参数验证法

如果函数某个参数为TRUE或任意非零值，则该函数有无数种正确结果。例如根据F2:F8的总杆数，使用RANK函数在单元格H2计算F2单元格对应总杆数的名次，排名原则为总杆数越少排名越前，并复制函数到H3:H8区域。其中一种正确答案是“=RANK(F2,F$2:F$8,1)”，第 3个参数可以是TRUE或任意非零值。这类函数可以采取特殊参数验证法进行阅卷，即先验证函数名是否正确，然后提取参数子串(本例为“F2,F$2:F$8,1”)，然后按“，”进行分割将每个参数作为一个元素存到数组中，再对每个参数进行逐一对照，如果前2个参数匹配且第3个参数为TRUE或非零数值则结果正确。

2.4 等效条件法

这种方法适用于嵌套IF函数的阅卷。例如根据D3单元格的成绩，在单元格G3中使用IF函数计算学生等级，如果成绩>=80为优秀，成绩<80但>=60为合格，成绩<60为不合格，然后复制公式到G4:G22区域中。这时函数为“=IF(D3>=80,"优秀",IF(D3>= 60,"合格","不合格"))”是正确的，函数为“=IF (AND(D3>=60,D3<80),"合格",IF(D3<60,"不合格", IF(D3>=80,"优秀")))”也是正确的，如果将各种可能正确结果都列举出来几乎是不可能的。

使用等效条件法进行阅卷，教师只需提供任意一种正确答案，下面以参考答案“=IF(D3>=80,"优秀",IF(D3<60,"不合格","合格"))”为例，说明这种方法的使用。首先从函数字符串中提取第1个条件“D3>=80”、第1个返回值“优秀”、第1个条件的反向条件“D3<80”；然后取第2个条件：“D3<60”、第2个返回值“不合格”、第2个条件的反向条件“D3 >=60”，再将第2个条件和第1个条件的反向条件合并得“AND(D3<80,D3<60)”，简化后得“D3<60”；第 2个无条件，返回值为“合格”，合并第 1、2个条件的反向条件得“AND(D3<80,D3>=60)”。转化后结果如表3所示。阅卷时将学生答题函数按照同样的方法进行转换，如果能得到表3结果则认为正确。

表3 IF函数条件转换结果

3 阅卷结果分析

经过本文所介绍方法进行处理后，在理论上仍会有误判、错判的情况发生，例如对B2:E2进行求和，如果考生输入=SUM(B2:C2,D2:E2)将误判为错误，但采用这种方法输入函数的学生微乎其微。为了检验本文介绍方法的有效性，针对函数考核点，分别采用本文所介绍方法和文献[2]所介绍方法对实际考卷进行阅卷处理，然后对比分数有差异的考卷，发现文献[2]所介绍方法误判率远高于本文所介绍方法。而在条件区域考核点方面，经过本文所介绍方法处理后阅卷准确性更是大为提高。

4 结束语

现有Excel操作题阅卷方案虽然很多，但是在对答案非唯一题型进行阅卷时容易出现错误。本文针对Excel最有可能出现答案非唯一的条件区域的设置和函数的使用，介绍了五种有效的解决方法，包括排序法、等效引用法、列举法、特殊参数验证法和等效条件法。经过本院多次实验和考试检验，将本文所介绍方法用于Excel自动阅卷当中，能够使阅卷准确性得到比较大的提高。

[1]李爱玲,宋志刚.基于VBA的Excel文档自动评阅技术研究[J].科学技术与工程,2011,11(34):8504-8508.

[2]彭作民.EXCEL文档自动阅卷评分算法设计[J].南京师范大学学报（工程技术版）,2007,7(3):70-73.

[3]林海宁.基于VBA技术的Excel自动评阅系统[J].现代计算机：下半月版,2012,(14):78-80.DOI:10.3969

[4]梁里宁.用VBA实现Excel工作表的自动排序[J].办公自动化（综合版）,2009,(1):60.