☰

複数回答の多肢選択問題を採点するための Excel 式/ソリューション

tag-icon • tag-icon microsoft-excel worksheet-function
複数回答の多肢選択問題を採点するための Excel 式/ソリューション

生徒のテストの回答を次のように入力する Excel シートがあります。

  1. あ
  2. B
  3. A、B
  4. CD

これらの答えを正解と比較してください。

  1. あ
  2. あ
  3. B
  4. C、D、E

生徒の回答が正しい場合（たとえば回答 1）は IF 関数を使用してセルに「正解」を出力し、そうでない場合は「不正解」を出力する方法を知っています。

私ができないのは、正解の場合 (1) は「正解」、不正解の場合 (2) は「不正解」、生徒が 1 つの答えに正解したが、同時に別の選択肢に不正解だった場合は「間違い」 (3)、生徒が正しく答えたが、1 つの答えを間違えた場合は「間違い」 (4) と表示する式を見つけることです。

これを実行する方法はありますか? ワイルドカードと countifs を使用してみましたが、私の Excel レベルを超えています。

ご協力いただければ幸いです。

答え1

ソリューション

答えをEDCBAビット順で2進数で表すと次のようになります。

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

ここに画像の説明を入力してください

correct_answerLET を使用して、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))

関連情報