Fórmula/solución de Excel para calificar preguntas de opción múltiple con múltiples respuestas

Question

Tengo una hoja de Excel donde ingreso las respuestas de las pruebas de los estudiantes de esta manera:

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

y compare estas respuestas con las respuestas correctas:

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

Sé cómo usar una función SI para generar "CORRECTO" en una celda si la respuesta del estudiante es correcta (por ejemplo, respuesta 1) y, en caso contrario, decir "INCORRECTO".

Lo que parece que no puedo hacer es encontrar una fórmula que diga 'CORRECTO' si es correcto (1); 'INCORRECTO' si es incorrecto (2); 'ERROR' si el estudiante acertó una de las respuestas, pero al mismo tiempo respondió incorrectamente otra opción (3); y 'PERDIDO' si el estudiante respondió correctamente pero omitió una respuesta (4).

¿Hay alguna manera de hacer esto? Intenté usar comodines y conteos, pero está más allá de mi nivel de Excel.

¡Gracias de antemano por cualquier ayuda!

Answer 1

La solución

Representemos la respuesta en binario de esta manera, con el orden de bits EDCBA, es decir

01010

significa que la respuesta es

B,D

Para convertir la respuesta de texto (estudiante u hoja de respuestas) utilizamos

=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

Ahora a comparar. Digamos que tenemos C1 la respuesta correcta y D1 la respuesta del estudiante, ambas en forma "bit a bit" usando la fórmula anterior. Entonces

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

La explicación

Podría intentar explicarlo, pero tendría que matarte a ti y luego a mí mismo. Tal vez pueda intentarlo... Es útil pensar en detectar el "error" como el más difícil, así que déjelo para el último caso del "si". Considere omitir esto en favor de la imagen a continuación:

    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

En realidad, es bastante fácil si lo dibujas (¡visión después de dormir!):

Answer 2

Puedes usar una fórmula como esta:

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

Usamos LET para asignar el nombre correct_answeral resultado de BUSCAR XL para recuperar la respuesta de la lista de respuestas correctas. Luego usamos IFSpara hacer comparaciones entre la respuesta del estudiante y la respuesta correcta.

Si ve un error de NOMBRE al usar esto, es probable que no tenga acceso a LET. En ese caso, debe eliminar la llamada a LET y reemplazar cada instancia de respuesta_correcta con una copia de la función BUSCAR XL.

Answer 3

Aquí hay otra alternativa de Microsoft365:

Fórmula en 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))