Excelでの統計処理について

質問

Excelの表でマークシート試験の合計点、平均点、正解率などの統計をしているのですが、以下のような場合はどのような数式を入れたらよいのでしょうか？

通常は、

A1セル　正解の記号
B1セル　Pさんの選択した記号
C1セル　Qさんの選択した記号
D1セル　正解数　数式　=SUM(IF($A1=B1:C1,1,0)) を入力
E1セル　正解率　数式　=SUM(IF($A1=B1:C1,1,0)/COUNT(B1:C1))*100　を入力

で、この問題の正解数、正解率が出ると思いますが、
問題の不手際で、正解の記号が「イまたはロ」というように正解が２つ有り、
選択した記号がどちらか合っていれば正解というようなときは、
D1、E1セルにはどのような数式を入力すればよいのでしょうか？

アドバイスお願いします。

回答No.3

cafe_au_lait · ベストアンサー

正解が数値ということで・・・

以下の仮定のもとで機能します。
・選択肢は0～9の範囲で、2桁にはならない
・複数回答の場合は2桁以上になる。正解、解答とも同順に並んでいる（順序が入れ替わったら不正解と判断）
・正解が2つある場合も2桁以上になる。複数回答と区別するため、正解の先頭に"-"をつける
・上記の場合は、正解の中に解答が含まれていれば正解とする
・複数回答で不手際があった場合は対応しない

前回とは異なる式です。

正解 Pさん Qさん Rさん Sさん 正解数 正解率
1 2 1 2 1 2 50
2 2 2 2 2 4 100
-12 2 4 1 12 3 75
3 2 3 1 2 1 25
4 2 4 1 4 2 50
1 2 2 1 5 1 25
-12 2 2 1 2 4 100
-23 2 2 3 2 4 100
-12 2 2 1 5 3 75
125 2 124 125 235 1 25
合計 5 7 7 6

F2:=SUMPRODUCT(($A2>=0)*($A2=$B2:$E2)+($A2<0)*NOT(ISERROR(FIND($B2:$E2,$A2))))
G2:=$F2/COLUMNS($B2:$E2)*100
それぞれ下にコピーします。

B12:=SUMPRODUCT(($A$2:$A$11>=0)*(B$2:B$11=$A$2:$A$11)+($A$2:$A$11<0)*(NOT(ISERROR(FIND(B$2:B$11,$A$2:$A$11)))))
右にコピーします。

Sさんの3問目のように、本来1つしか選ばない場合に2つ選んでも、正解と判定されます。念のため。

回答No.2

cafe_au_lait

配列数式より、下記の方が簡単ですね。
D1:=COUNTIF(B1:C1,A1)
E1:=D1/COLUMNS(B1:C1)

複数回答の場合、A1に「イロ」と入れておいて、
D1=COUNTIF(B1:C1,RIGHT(A1))+COUNTIF(B1:C1,LEFT(A1))
E1は同じ

全て同じ式で行くのならば、文字数で割って、
D1=(COUNTIF(B1:C1,RIGHT(A1))+COUNTIF(B1:C1,LEFT(A1)))/LEN(A1)

COUNTって数値しか数えないと思うのですが、動作するのでしょうか？

補足

早々に有難うございます。

実際は「イロ」ではなく数値なんです。
文字にした方が、説明がわかりやすいかと思いまして。
アドバイスの方法でうまくいきました。
本当に助かりました。有難うございます。

もうひとつお伺いしたいのですが、
問題は100問あるのです。
そこで、Pさんの合計点を出すために、
B101のセルに　「=SUM(IF($A$1:$A$100=B1:B100,1,0))
としていたのですが、
複数回答があった場合、どう入力すればよいのでしょう？

ちなみに今回のように不適切問題で複数回答があった問題と、
「正解をふたつ選べ」というような問題も混ざってあるのです
このふたつ選ぶ場合も正解のところに「イロ」と続けて入力しているのです。

もしおわかりでしたらアドバイスお願いいたします。

回答No.1

rarril

＜その１＞
A2等にもう片方の回答を入れ、D1、E1内IF文の偽の時にもう一つIF文を作り、A2等もう片方の回答と照らし合わせてみてはいかがですか？

例)A2にもう一つの解があると仮定して
　D1セル　=sum(if($A1=B1:C1,1,if($A2=B1:C1,1,0)))
　E1セル　=sum(if($A1=B1:C1,1,if($A2=B1:C1,1,0))/count(b1:c1))*100

＜その２＞
A1セルに「イまたはロ」と入力した場合、left関数とright関数を利用してみて下さい。

例)A1セルに「イまたはロ」と入力されていると仮定して

　D1セル　=sum(if(left(A1,1)=B1:C1,1,if(right(A1,1)=B1:C1,1,0)))
　D2セル　=sum(if(left(A1,1)=B1:C1,1,if(right(A1,1)=B1:C1,1,0))/count(B1:C1))*100

恐縮ながら自身でも試してませんが、上記のいずれかでやれると思います。

補足

早々に有難うございます。
その１の方法で、列を増やしていくのはシートの管理上問題がありますので
できればその２のように数式を変える方法で解決したいと思っています。

私の説明不足だったのですが、B1、C1に入る記号は、ひとつなのです。
「正解をひとつ選べ」という問題で解答させたのですが、
あとから見直すと正解がふたつあったため、どちらの記号を書いていても正解にする・・ということなのです。

説明不足で申し訳ありません。
よろしくお願いいたします。