☰

用于对具有多个答案的多项选择题进行评分的 Excel 公式/解决方案

tag-icon • tag-icon microsoft-excel worksheet-function
用于对具有多个答案的多项选择题进行评分的 Excel 公式/解决方案

我有一张 Excel 表，用于输入学生测试答案，如下所示：

  1. A
  2. 乙
  3. A、B
  4. 光盘

并将这些答案与正确答案进行比较：

  1. A
  2. A
  3. 乙
  4. C、D、E

我知道如何使用 IF 函数，如果学生的答案正确（例如答案 1），则在单元格中输出“CORRECT”，否则输出“INCORRECT”。

我似乎无法找到一个公式，如果正确则显示‘正确’（1）；如果错误则显示‘错误’（2）；如果学生答对了一个答案，但同时错误地回答了另一个选项，则显示‘错误’（3）；如果学生回答正确但漏了一个答案，则显示‘未答’（4）。

有什么办法吗？我尝试过使用通配符和 countif，但这超出了我的 Excel 水平。

提前感谢你的帮助！

答案1

解决方案

让我们用二进制表示答案，位顺序为 EDCBA，即

01010

意味着答案是

B,D

要转换文本答案（学生或答题纸），我们使用

=ISNUMBER(SEARCH("A", A1))*2^0 + ISNUMBER(SEARCH("B", A1))*2^1 + ISNUMBER(SEARCH("C", A1))*2^2 + ISNUMBER(SEARCH("D", A1))*2^3 + ISNUMBER(SEARCH("E", A1))*2^4

现在来比较一下。假设我们有 C1 正确答案和 D1 学生答案，两者都是使用上述公式的“按位”形式。然后

=IF(C1 = D1, "Correct", IF(BITAND(BITXOR(C1,D1),C1)=C1, "Incorrect", IF(BITAND(BITXOR(C1,D1),C1)=BITXOR(C1,D1), "Missing", "Mistake")))

说明

我可以试着解释一下，但那我就得先杀了你，然后再自杀。也许我可以试试……把检测“错误”看作是困难的问题会有所帮助，所以把它留到 if 的最后一个“全部捕获”情况。考虑跳过这一步，转而看下面的图片：

    X XOR Y is a list of differences between lists X and Y
    X AND Z = X means the list Z must at least contain everything in list X
    X AND Z = Z means the list X must at least contain everything in list Z

Now lets say X is the list of correct answers (CA), Y is list of the student's answers (SA). Then:

    Z = X XOR Y is a list of differences between CA and SA

If Z = 0 then the list is empty and CA = SA i.e. "Correct", else if

    X AND Z = X 

then the list of differences must contain at least everything in list of correct answers (i.e. no correct answers = "incorrect"), else if

    X AND Z = Z 

then the list of correct answers must contain at least everything in the list of differences (i.e. no wrong answers = "missing", one or more correct), else

    NOT(all of the above)

then one or more correct answer and one or more incorrect answer = "Mistake".

总结

如果你画出来它实际上很容易（睡了一觉之后的领悟！）：

在此处输入图片描述

答案2

您可以使用如下公式：

=LET(correct_answer,XLOOKUP(A2,$I$1:$I$4,$J$1:$J$4,""),IFS(B2=correct_answer,"CORRECT",IFERROR(FIND(B2,correct_answer,1),0)>0,"MISSED",IFERROR(FIND(correct_answer,B2,1),0)>0,"MISTAKE",TRUE,"INCORRECT"))

在此处输入图片描述

我们使用 LET 将名称分配correct_answer给 XLOOKUP 的结果，以从正确答案列表中检索答案。然后我们使用IFS对学生的答案和正确答案进行比较。

如果在使用时看到 NAME 错误，则可能是您无权访问 LET。在这种情况下，您应该删除对 LET 的调用，并将 correct_answer 的每个实例替换为 XLOOKUP 函数的副本。

答案3

这是另一个 Microsoft365 替代方案：

在此处输入图片描述

公式C1：

=INDEX({"Correct","Incorrect","Mistake","Missed"},MATCH(AVERAGE(UNIQUE(ISNUMBER(FIND(FILTERXML("<t><s>"&SUBSTITUTE(B1,",","</s><s>")&"</s></t>","//s"),A1)))+(A1=B1)),{2,0,1,0.5},0))

相关内容