Excel制作能够实现自动评分的试卷(用excel制作比赛自动评分表)

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

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

一、单项选择题实现

单项选择题比较简单，只需要判定用户所给的答案是否和正确答案一致，一致的得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允许编辑外，其余的单元格禁止编辑。

实现的效果如下：