こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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 を入力

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

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

投稿日時 - 2006-12-19 13:32:43

QNo.2612202

すぐに回答ほしいです

質問者が選んだベストアンサー

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

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

前回とは異なる式です。

正解PさんQさんRさんSさん正解数正解率
12121250
222224100
-1224112375
32312125
42414250
12215125
-1222124100
-2322324100
-122215375
1252124125235125
合計5776

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つ選んでも、正解と判定されます。念のため。

投稿日時 - 2006-12-19 20:37:12

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.2

配列数式より、下記の方が簡単ですね。
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って数値しか数えないと思うのですが、動作するのでしょうか?

投稿日時 - 2006-12-19 14:47:09

補足

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

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

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

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

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

投稿日時 - 2006-12-19 16:04:06

ANo.1

<その1>
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

<その2>
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

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

投稿日時 - 2006-12-19 13:50:14

補足

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

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

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

投稿日時 - 2006-12-19 14:37:15

あなたにオススメの質問