work done计算公式使用Excel对多项选择题自动评分并统计正确率教学教材

2020年11月8日发(作者：和谷岩)




使用
择题
xcel对多项选
动评分并统计
正确率
E
自
精品资料

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

【内容摘要】
对选择题的答题卷进行批改和分析是教学中经常要做的重要工作。利用
Microsoft Excel 电子表格的强大功能，用FREQUENCY函数和数组公式、
COUNT函数对选各题各选项的人数及 百分比进行统计；用IF函数和SUM函数
自动计算单项选择题的总得分；用VLOOKUP函数和SU M函数自动计算不定
项选择题的总得分。
【关键词】
Excel 选择题 自动结分 统计分析
考试是教学中不可缺少的环节，是检查教学质量的重要手段。考试可以促< br>进、激励学生的进步发展，让教师检查、诊断学生的学习是否达到教学大纲规
定的要求，同时给教 师提供反馈信息，以便及时调整教学进度，改进教学方
法，提高教学质量。而要达到以上目的，试卷分析 是很重要的环节，只有对考
试结果进行全面细致的分析，才能找出教和学双方存在的问题，找出有针对性
的解决方法，提高教学效果。而全面细致的分析必须要有详细的数据，如及格
率、优秀率、平均 分、标准差、各分数段人数、各题难度等，特别是对每题的
难度分析，对搞清每个知识点的落实掌握情况 是很重要的。而选择题答卷的统
计分析，由于一般的平时测验都没有答题卡和读卡机，老师要对选择题进 行手
工逐题的批改记分，如要对各题的答题情况进行比较详细的分析，许多老师用
打五划“正” 的方法，来统计各题的做对和做错人数。本人也经常做这样的事，
感到效率很低，且容易出错搞不准确。 想到用Excel对考试结果数据进行及格
率、优秀率、平均分、标准差、各分数段人数分析很容易，能 不能也用Excel
对选择题进行比较详细的分析，通过反复摸索，找到了以下方法。
下面以60人的班级，20题选择题为例，多于60人或少于60人可用插入行或
删除行来调整，多于2 0题或少于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行中显 示各题选择各选项的人数。至此主要任务完成。
仅供学习与交流，如有侵权请联系网站删除 谢谢
2
精品资料
5．为了使数据看起来更方便，可在B69输入“总人数”，在C 69中输入公式
“=COUNT(C3:C62)”，光标移到右下角变为实心的十字时，向右拖拉至V 69，
则69行显示答题总人数。
6．在B70:B73中分别输入“选A百分比、…、选D 百分比”，在C70中输入
“=C65COUNT(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多一格)，按 F 2编辑选中的单元格，在公式编
辑栏或直接在单元格输入公式，再按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、ABC D；分别用数值1、2、3、4、
12、13、14、23、24、34、123、124、134、2 34、1234来代替，也就是字母
A、B、C、D分别用数字1、2、3、4来代替。
1． 和前面相同，建立一个空白的Excel文件，输入表格头、题号、学号、姓
名，C3:V62为数据输 入区。也可以在前面单选题表格的基础上接着进行操作。
仅供学习与交流，如有侵权请联系网站删除 谢谢
3
精品资料
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行显示答题人数；
5．在B81:B95中分别输入“选A百分比 、…、选ABCD百分比”，在C81中输
入“=C65COUNT(C$$3:C$$62)”，光标移到 右下角变为实心的十字时，向下拖拉
至C95，向右拖拉至V95，就会在81至95行中显示各题选择 各选项的人数的
百分比。
6．隐藏W列；冻结表格头；有效性条件允许“整数”，数据介于最 小值1，最大
值1234。在C3:V62中输入和A、B、C、D对应的1、2、3、4值就统计出各 题
的选项情况。但要注意的是，如：选项AB和BA等效，但在输入数字时只能
是12，不能输 入21，而ABC只能是123，不能是132或231，也就是数字只能
从小到大排列。
只要把每个同学选择题答卷中的答案字母用相应的数字填入，就可以方便地
统计出每题的答题情况。
自动计算单项选择题的总得分
哎！统计倒是不错，可这些选择题是批改了再来统计，还 是统计了再来一遍
批改，这不是干了二遍吗？岂不是和当初提高效率的初衷相违背了吗！所以如
果在得出统计结果的同时，每个同学的选择题分数也出来了多好。看下面操
作：
如果是单选题，在前面单选题表格中的题号下面插入一行成为第3 行，存放每
小题的相应的答 案数字；在姓名后面插入一列成为C列，存放每个同学选择题
的总得分。在第一个同学的存放成绩的单元 格C4里输入：
“=SUM(IF(D4=$$D$$3,
4=$$H$$3,，…，…)*$$C$$3 ”，然后向下拖拉到最后一个同学止(如图4)。意
思是比较D4单元格如果和D3单元格值相同，赋值 为1；比较E4单元格如果
和E3单元格值相同，赋值为1；…；直至最后一题；再把所有“1”求和( 即正确
的题数)；再乘以每题的分值“$$C$$3”。
每次不同的选择题要改动的只是答 案行的正确答案的数字，和在C3单元格填
入每小题的题分值，每个学生选择题的总得分在各人姓名后就 显示出来了，还
是很方便的。
注意的是，如果选择题只有10题，剩下其它几题的答案 要填入一个数如5，
不能为空格，因为空格和空格比较表格也认为是相同的，返回赋值1，这样最
后结果就不对了。
仅供学习与交流，如有侵权请联系网站删除 谢谢
4
精品资料
这里用到求和函数SUM，和判断函数IF，IF的语法是这样的：
IF(logical_ test,value_if_true,value_if_false)，Logical_test 是判 断条件，如满足返
回Value_if_true，不满足返回Value_if_false，Log ical_test和Value_if_false可以
是逻辑值，零，也可以是其它公式。
而计算不定项选择题的得分，也可用这个公式方法，只不过麻烦一点。比如
第一题正确答 案是ABC，答A或B或C，或答AB，或答AC，或答BC，均可
得部分分数，但一般如果有错误的答 案选上就不得分了。首先答案行一行肯定
不够了，但如果不引用单元格而直接比较答案，答案行也可不用 增加。函数 IF
最多可嵌套七层，也就是可比较七个条件，如第一题判断可用：
“IF(D 4=123,F(D4=12,
，0)))))))”，即如选ABC得3分，选AB或AC或BC得2 分，选
A或B或C得1分，否则为0分。而这只是一题，还要用SUM函数将所有各
题判断加起 来，如这样：“=SUM(第1题判断，第2题判断，…，…)”，式子很
长。且每次不同的选择题都要 修改公式，很麻烦易出错，必须另想办法。
自动计算不定项选择题的总得分
如果把不 定项选择可能的答案的答题项很多行都列出来，数据很多，所以在
上面工作表基础上要另建一个工作表命 名为“da”。在“da”工作表中建立如图5
所示表格，A列是所有可能的选项，共15种可能，只不 过答案是ABCD的情况
一般不会出现，这一项等会另作他用。每一列的题号后面都插入一列，作为相< br>应选项的得分值，如只有一个选项“C”，则数字“3”后面填“1”，其它各项都填
“0”；如 正确选项是“ABC”，则“ABC”相应数字后面“123”填“1”，而选AB、
AC、BC都可得 部分分数，如得小题满分的23分数，则在相应空格中都填上
0.667(或23，单元格格式中数字项 要设置一下) ，其它各项都填“0”；选A或B
或C也可得分，如得小题满分的13分数，则在“1” 、“2”、“3”后面都填上
0.333(或13)，其它各项都填“0”。为方便对照，在第3列列出 正确的答案；在
左上角空格中选一格如C1，放置每小题的分值，每次改变题分只要修改这一格
就可以了。
回到工作表“1班”，在“C4”单元格中输入如下公式(如图6)：
“ =SUM(VLOOKUP(D4,da!$$B$$4:$$C$$18,2,FALSE),VLOOKUP(E4, da!$$D$$4:$$E$$18,
2,FALSE),VLOOKUP(F4,da!$$F$$4:$$G$$1 8,2,FALSE),…… ,VLOOKUP(W4,da!$$AN
$$4:$$AO$$18,2,FALSE))*da!$$C$$1”
如选择题有20题，则从“D－W”有20项，要有细心和耐心嗬！如果你确定以
后肯定 没有20个选择题，也可以少输入几个，如果不能保证的话，那只能为以
后少点麻烦现在辛苦点了，因为 用插入列增加题数在这里要改变公式。求和
SUM函数里的VLOOKUP函数是查找和引用函数，作用 是在表格或数值数组的
首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。其基本语法形式为：
VLOOKUP(lookup_value,table_array,col _index_num,range_lookup)。
Lookup_value表示要查找的值， 它必须位于自定义查找区域的最左列，可以为
数值、引用或文本字符串，这里是“D4”，学生选的第一 题答案项；Table_array
用于查找数据的区域，上面的查找值必须位于这个区域的最左列，可 以使用对
区域或区域名称的引用，这里是引用da工作表中的$$B$$4：$$C$$18，即第一题选
择题所有可能的答案项及相应分值，对不同工作表的引用和对单元格的引用之
间用感叹号“!”分开， 绝对引用加“$$”；逻辑值为 FALSE，table_array 不必进行
仅供学习与交流，如有侵权请联系网站删除 谢谢
5
精品资料
排序，否则table_array 的第一列中的数值必须按升序排列；Col_index_num 为
table_array中待返回的匹配值的列序号，最左列为1，其右边一列为2，依此类
推，“2”也就是返回C列，即第一题选择题“得分”情况这一列。range_lookup为
一逻辑 值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配，range_value
为 FALSE，函数VLOOKUP 将返回精确匹配值。
VLOOKUP(D4,da!$$B$$4:$$ C$$18,2,FALSE)语法解释：在da工作表的$$B$$4:$$C$$18
范围内（即选择题第一题 可能选项及得分两列中），精确找出与D4单元格相
符的行，并将该行中第二列的内容计入单元格中。
其余各题选择题以此类推。然后用SUM函数求和再乘以da!$$C$$1(每题的分
值) ，填入C4格，即为B4同学的选择题总得分。用拖拉的办法将C4公式拖拉
到最后一个同学为止，这样 自动计算不定项选择题的表格基本就完成了。
如果有10题不定项的选择题，每小题3分。将d a工作表中的C1格填上“3”；
将做好的正确答案填入“3”列，然后将各种答案组合的分数填入相应 格中；再把
工作表“1班”中各个同学的选项情况用相应的数字填入。本来应该在每位同学后
面 这一格出现的总分，却出现了错误值#NA。原因是从11~20格都空着，精确
匹配出现了错误，如果 有那一格没填是空格，也要出错。解决办法是把工作表
da中的选项“1234”(也就是“ABCD” 选项)全换成“X” (字母x)，得分为0；然后在
答卷行中把空的都填上X，好在不要一个一个填， 拖拉一下就可以了；这样就
可以精确匹配显示正确结果了。如果那一天有ABCD全选的选择题，也没关
系，只要在工作表da中改一下就可以了；如果不止一个班级，可复制工作表“1
班”到She et2、Sheet3…，重命名为“2班”、“3班”…，然后改动一下学生的姓名
就行了。为了防止 以后改动，可以保存为模板文件，方便以后应用。
【参考书目】（1）Microsoft Excel 2002帮助文件。
（2）全日制普通高级中学《物理教学大纲》（最新版）。

仅供学习与交流，如有侵权请联系网站删除 谢谢
6