使用Excel对多项选择题自动评分并统计正确率

【内容摘要】

对选择题的答题卷进行批改和分析是教学中经常要做的重要工作。利用Microsoft Excel电子表格的强大功能，用FREQUENCY函数和数组公式、COUNT函数对选各题各选项的人

数及百分比进行统计；用IF函数和SUM函数自动计算单项选择题的总得分；用VLOOKUP 函数和SUM函数自动计算不定项选择题的总得分。

【关键词】

Excel 选择题自动结分统计分析

考试是教学中不可缺少的环节，是检查教学质量的重要手段。考试可以促进、激励学生的进步发展，让教师检查、诊断学生的学习是否达到教学大纲规定的要求，同时给教师提供反馈信息，以便及时调整教学进度，改进教学方法，提高教学质量。而要达到以上目的，试卷分析是很重要的环节，只有对考试结果进行全面细致的分析，才能找出教和学双方存在的问题，找出有针对性的解决方法，提高教学效果。而全面细致的分析必须要有详细的数据，如及格率、优秀率、平均分、标准差、各分数段人数、各题难度等，特别是对每题的难度分析，对搞清每个知识点的落实掌握情况是很重要的。而选择题答卷的统计分析，由于一般的平时测验都没有答题卡和读卡机，老师要对选择题进行手工逐题的批改记分，如要对各题的答题情况进行比较详细的分析，许多老师用打五划“正”的方法，来统计各题的做对和做错人数。本人也经常做这样的事，感到效率很低，且容易出错搞不准确。想到用Excel对考试结果数据进行及格率、优秀率、平均分、标准差、各分数段人数分析很容易，能不能也用Excel对选择题进行比较详细的分析，通过反复摸索，找到了以下方法。

下面以60人的班级，20题选择题为例，多于60人或少于60人可用插入行或删除行来调整，多于20题或少于20题用插入列或删除列来调整，都不影响表格的性质。

对单选题的分析

1．先新建一个空白的Excel工作簿，工作表Sheet1重命名为“1班”，输入表格头、题号、

学号、姓名，C3:V62为数据输入区(如图1)。

2．在W3、W4、W5三个单元格分别输入数值1.5、2.5、3.5。在姓名的下方的适当位置，

如隔二行B65:B68分别输入“选A项人数、…、选D项人数”。

3．选中C65:C68四个单元格，单击F2(编辑选定的单元格)，在公式编辑栏或直接在单元格输入“=FREQUENCY(C3:C62,$W$3:$W$5)”(注意：输入公式时字母、符号要用英文半角)，

按Ctrl+Shift+Enter组合键输入(如图2)。选中C65:C68光标移到右下角变为实心的十字时，向右拖拉至V65:V68。

4．填入学生的选择题答案，选A的填1，选B的填2，选C的填3，选D的填4。就会在65至68行中显示各题选择各选项的人数。至此主要任务完成。

5．为了使数据看起来更方便，可在B69输入“总人数”，在C69中输入公式“=COUNT(C3:C62)”，光标移到右下角变为实心的十字时，向右拖拉至V69，则69行显示答题总人数。

6．在B70:B73中分别输入“选A百分比、…、选D百分比”，在C70中输入

“=C65/COUNT(C$3:C$62)”，光标移到右下角变为实心的十字时，向下拖拉至C73，向右拖拉至V73，就会在70至73行中显示各题选择各选项的人数的百分比。注意设置C70:V73

单元格的格式，点“格式”中“单元格格式”，在“数字”中选“百分比”，“小数位数”设为1位，以免过多的小数位数显示不出。

7．为方便使用，选中第3行，点“窗口”中的“冻结窗口”，冻结表格头，在输入数据时让题号不滚动；选中W列，点“格式”“列”“隐藏”可隐藏W列，防止误操作这列数据；也可选中C3:V62数据输入区，点“数据”、“有效性”、“设置”，在“有效性条件”中“允许”“整数”，“数据”“介于”最小值1，最大值4，防止输入时误输入其它无效数字。

说明：Excel中函数是对单元格中的数值进行处理，而选择题的选项A、B、C、D这些文本形式很难处理，所以用数值“1、2、3、4”分别代替“A、B、C、D”。

这里用到函数“FREQUENCY”和数组公式。函数“FREQUENCY（data_array，bins_array）”是以一列垂直数组返回某个区域中数据的频率分布，Data_array为一数组或对一组数值的引用（即C3:C62），用来计算频率；Bins_array 为间隔的数组或对间隔的引用（即$W$3:$W$5，$符号是指绝对引用，不随拖拉而改变引用的单元格），该间隔用于对data_array 中的数值进行分组，在这里1.5、2.5、3.5三个数形成四个间隔：小于等于1.5；大于1.5小于等于2.5；大于2.5小于等于3.5；大于3.5。而我们用1、2、3、4这四个数去对应这四个区间，来代替A、B、C、D这四个选项，所以只要小于等于1.5的数都可以代替A，大于1.5小于等于2.5的数都可以代替B，以此类推，只是由于方便只用1、2、3、4来代替A、B、C、D。也就是函数FREQUENCY返回的数组中的元素个数比bins_array（W3: W5）中的元素个数多一。由于函数FREQUENCY 返回一个数组，所以必须以数组公式的形式输入，选中从公式单元格开始的单元格区域即C65:C68(四格，比W3: W5多一格)，按F2编辑选中的单元格，在公式编辑栏或直接在单元格输入公式，再按Ctrl+Shift+Enter以数组公式的形式输入。则在C65:C68中的数据分别是：小于等于1.5数值的个数(即1的个数，也就是选A的个数)、大于1.5小于等于2.5数值的个数(即2的个数，也就是选B的个数)、大于2.5小于等于3.5数值的个数(即3的个数，也就是选C的个数)、大于3.5数值的个数(即4的个数，也就是选D的个数)。如果公式未以数组公式的形式输入，则返回的结果为1。和一般公式的区别是数组公式括于大括号({ }) 中。按Ctrl+Shift+Enter 可以输入数组公式。

对不定项选择题的分析

不定项选择题的选项有15种可能情况，即A、B、C、D、AB、AC、AD、BC、BD、CD、ABC、ABD、ACD、BCD、ABCD；分别用数值1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234来代替，也就是字母A、B、C、D分别用数字1、2、3、4来代替。

1．和前面相同，建立一个空白的Excel文件，输入表格头、题号、学号、姓名，C3:V62为数据输入区。也可以在前面单选题表格的基础上接着进行操作。

2．在W3、W4、…、W16十四个单元格分别输入数值1.5、2.5、3.5、4.5、12.5、13.5、14.5、23.5、24.5、34.5、123.5、124.5、134.5、234.5；这14个数形成的15个区间分别对应1、2、3、4、12、13、14、23、24、34、123、124、134、234、1234，也就是A、B、…、ABCD 这十五种可能选项。在姓名的下方的适当位置，如隔二行B65:B79十五行分别输入“选A项人数、选B项人数、选C项人数、选D项人数、选AB项人数、选AC项人数、选AD项人数、选BC项人数、选BD项人数、选CD项人数、选ABC项人数、选ABD项人数、选ACD项人数、选BCD项人数、选ABCD项人数”。

3．选中C65:C79十五个单元格，单击F2(编辑选定的单元格)，在公式编辑栏或直接在单元格输入“=FREQUENCY(C3:C62,$W$3:$W$16)”，按Ctrl+Shift+Enter组合键输入数组公式(如图3)。选中C65:C79，光标移到右下角变为实心的十字时，向右拖拉至V65:V79。

4．在B80输入“总人数”，在C80中输入公式“=COUNT(C3:C62)”，光标移到右下角变为实心的十字时，向右拖拉至V80，则第80行显示答题人数；