在日常的工作中,我们常常会需要制作一些测试试题。这种场景下,Excel不愧为一种十分适合的方式。我们可以通过一些设置,使Excel能够在提交后自动评分。

在我们平时的选择题中,常见的是单选题以及多选题,判断的规则常见的是:单选题只能选择一个答案,且答案正确得2分;多选题有多个答案,全部选对得满分2分,少选得1分,有错选不得分。今天,我就教大家如何实现根据以上规则自动评分。整体的界面设计如下图:

Excel制作选择题试卷,输入判卷码后自动评分,不错哟!


一、单项选择题实现

单项选择题比较简单,只需要判定用户所给的答案是否和正确答案一致,一致的得1分,不一致的0分。公式如下:

=IF(M4<>"excel","",IF(B3="",0,IF(B3=B2,1,0)))

二、多项选择题的实现

多项选择题的规则相对较复杂,实现公式如下:

=IF(M4<>"excel","",IF(G3="",0,IF(AND(ISNUMBER(FIND(MID(G3,{1,2,3,4},1),G2))),IF(LEN(G3)=LEN(G2),2,1),0)))

下面对这个公式做一个解释:

1.第一层先判断判卷密码是否等于预设的密码,不等于得分栏留空。

2.第二层判断答案栏是否为空,为空的直接0分,不为空的进入下一层判断。

3.MID函数依次取1-4个字符,通过FIND函数判断是否在正确答案中,如果在,则返回所在的位置,如果不在,返回错误,如果find的第一个参数为空,返回结果为1。使用ISNUMBER判断返回的结果,是数字的返回TRUE,不是数字的返回FALSE。AND函数对四个结果进行逻辑判断,只要存在一个FALSE,AND函数就返回FALSE。

4.如果3中返回FALSE,直接判断为0分。如果返回TRUE,继续进入下一层判断。

5.第3步中返回TRUE以后,需要判断是全部正确还是部分正确,如果是全部正确(即长度相等),则得2分,否则得1分。

完成以上的公式后,我们需要做的是将第2行正确答案行隐藏,然后通过【审阅】下的【保护工作表】功能,将所有公式进行隐藏,除了B3:K3以及M4允许编辑外,其余的单元格禁止编辑。

实现的效果如下:

Excel制作选择题试卷,输入判卷码后自动评分,不错哟!


将这个表发给需要测试的人员,收回表格后,在M4中输入判分密码"excel"(这个密码在公式相应位置设置,实际过程中需要设复杂点,避免别人猜测密码),就能自动判断总得分。效果如下:

Excel制作选择题试卷,输入判卷码后自动评分,不错哟!

Excel制作选择题试卷,输入判卷码后自动评分,不错哟!