Формула/решение Excel для оценки вопросов с несколькими вариантами ответов

Question

У меня есть таблица Excel, куда я ввожу ответы на тесты студентов, например, так:

1. А
2. Б
3. А,Б
4. CD

и сравните эти ответы с правильными ответами:

1. А
2. А
3. Б
4. С,D,E

Я знаю, как использовать функцию ЕСЛИ для вывода в ячейке значения «ПРАВИЛЬНО», если ответ учащегося правильный (например, ответ 1), и как в противном случае выводить значение «НЕПРАВИЛЬНО».

Чего я, похоже, не могу сделать, так это найти формулу, которая выдаст «ПРАВИЛЬНО», если ответ правильный (1); «НЕПРАВИЛЬНО», если ответ неправильный (2); «ОШИБКА», если студент дал один из ответов правильно, но в то же время дал неправильный ответ на другой вариант (3); и «ПРОПУЩЕНО», если студент ответил правильно, но пропустил ответ (4).

Есть ли способ сделать это? Я пробовал использовать подстановочные знаки и countifs, но это выше моего уровня Excel.

Заранее благодарю за любую помощь!

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

TL;DR

На самом деле это довольно просто, если вы это нарисуете (прозрение после сна!):

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

Answer 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))