Excel-Formel/Lösung zur Bewertung von Multiple-Choice-Fragen mit mehreren Antworten

Question

Ich habe eine Excel-Tabelle, in die ich die Testantworten der Schüler wie folgt eingebe:

1. A
2. B
3. A,B
4. CD

und vergleichen Sie diese Antworten mit den richtigen Antworten:

1. A
2. A
3. B
4. C, D, E

Ich weiß, wie man mit einer WENN-Funktion „RICHTIG“ in einer Zelle ausgibt, wenn die Antwort des Schülers richtig ist (z. B. Antwort 1), und andernfalls „FALSCH“ ausspuckt.

Was mir nicht gelingt, ist eine Formel zu finden, die „RICHTIG“ ausgibt, wenn die Antwort richtig ist (1); „FALSCH“, wenn sie falsch ist (2); „FEHLER“, wenn der Schüler eine der Antworten richtig hatte, gleichzeitig aber eine andere falsch beantwortet hat (3); und „VERFEHLT“, wenn der Schüler richtig geantwortet hat, aber eine Antwort falsch beantwortet hat (4).

Gibt es eine Möglichkeit, dies zu tun? Ich habe versucht, Platzhalter und Zählenwenn zu verwenden, aber das übersteigt meine Excel-Kenntnisse.

Vielen Dank im Voraus für jede Hilfe!

Answer 1

Die Lösung

Stellen wir die Antwort binär dar, mit der Bitreihenfolge EDCBA, d. h.

01010

bedeutet, dass die Antwort

B,D

Zur Konvertierung der Textantwort (Schüler- oder Antwortbogen) verwenden wir

=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

Jetzt vergleichen wir. Nehmen wir an, wir haben C1 als richtige Antwort und D1 als Antwort des Schülers, beide in „bitweiser“ Form nach der obigen Formel. Dann

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

Die Erklärung

Ich könnte versuchen, es zu erklären, aber dafür müsste ich Sie und dann mich selbst umbringen. Vielleicht kann ich es versuchen ... Es hilft, sich das Erkennen des „Fehlers“ als das Schwierigste vorzustellen, also lassen Sie es für den letzten „Catch-All“-Fall des if übrig. Überspringen Sie diesen Fall und sehen Sie sich stattdessen das folgende Bild an:

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

Kurz zusammengefasst

Es ist eigentlich ganz einfach, wenn man es zeichnet (Erkenntnis nach dem Schlafen!):

Answer 2

Sie können eine Formel wie diese verwenden:

=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"))

Wir verwenden LET, um dem Ergebnis von XLOOKUP den Namen zuzuweisen correct_answerund die Antwort aus der Liste der richtigen Antworten abzurufen. Anschließend verwenden wir, IFSum Vergleiche zwischen der Antwort des Schülers und der richtigen Antwort anzustellen.

Wenn bei der Verwendung ein NAME-Fehler auftritt, haben Sie wahrscheinlich keinen Zugriff auf LET. In diesem Fall sollten Sie den Aufruf von LET entfernen und jede Instanz von correct_answer durch eine Kopie der XLOOKUP-Funktion ersetzen.

Answer 3

Hier ist eine weitere Microsoft365-Alternative:

Formel in 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))