用於對具有多個答案的多項選擇題進行評分的 Excel 公式/解決方案

Question 1

解決方案

讓我們用二進位表示答案，如下所示，位元順序為 EDCBA，即

意味著答案是

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".

長話短說

如果你畫出來的話，其實很簡單（睡覺後的感想！）：

Answer