Excelで解答と正解を比較する関数

質問

自分でも色々調べてみたのですが、どうしてもうまくいかないので、エクセルの関数にお詳しい方がいらっしゃれば回答をお願いします。

エクセルで５択テストの採点を行いたいと思っています。正解と１０名の解答者がそれぞれの解答した答えを比較して、平均の正解率を出したいのですが、どのような関数式を入れたら良いのか分かりません。

例えば、
セルA1に５択の正解（１～５）
セルB1~K1に解答者Bさん~Kさん（１０名）の解答（１～５）

上記がそれぞれ入力されているとして、A1の正解とB1～K1の解答をそれぞれ比較して、正解なら１、不正解なら０で計算し、最終的に１０名の平均の正解率を出すにはどのような関数を入れたら良いでしょうか？

ネットで調べたところ、下記のサイトにあるサンプルが自分の意図するものに近かったのですが、サンプルには１名の解答者の答えと正解との比較しかない為、解答者が複数いる場合にどうすれば良いのかわかりませんでした。お分かりになる方、よろしくお願いいたします。

http://bizfaq.jp/excel-hikaku/

回答No.2

imogasi · ベストアンサー

ここに質問する要領として、質問に、いろいろ自分個人の状態や、したことを書くよりも、シートのセルのデータの例でも挙げてもらうほうが良い。
回答しようとする人は、一応エクセルのベテランと仮定して質問してよい。
例
正解番号 Aさん Bさん Cさん Dさん Eさん Fさん Gさん Hさん Iさん　Jさん
2 3 1 2 3 5 1 2 2 3 1
3 3 1 2 3 3 1 2 3 3 1
L2セルに
=COUNTIF(B2:K2,A2)/COUNT(B2:K2)
％の場合は、書式を％に設定。
ーー
エクセルの関数で、条件を付けて考えるときは、関数の解説書で「〇〇IF」のような関数を探し、まずやってみること。
今回はAVERAGEIFという関数も別にあるがこれは、本件では使えない。
点数について、男性に限っての平均の場合などに使う。
本件は該当（正答）人数なのでCOUNTIFを使うことになる。

お礼

回答していただき、ありがとうございました。おかげで、うまく正解率を求めることができました。

回答No.4

tsubu-yuki

ＤＥＬＴＡ関数ですか、なるほど。
数値二つを比較して、合致していれば１、相違があれば０を返しますから、
質問者さんの考え方にはピタリの関数かもしれません。

せっかくですので、
> セルA1に５択の正解（１～５）
> セルB1~K1に解答者Bさん~Kさん（１０名）の解答（１～５）
及び
> 正解なら１、不正解なら０で計算し、
を最大限に生かしつつ、
> 最終的に１０名の平均の正解率
を出しましょう。

図の黄色部分、Ｂ２：Ｋ２セルに、ＤＥＬＴＡ関数を使っています。
　　Ｂ２セル：=DELTA($A$1,B1)
と入力し、横方向（列方向）にフィルすると楽ちんです。
比較すべき「正答」であるＡ１セルは絶対参照、
比較される「回答」であるＢ１セルは相対参照にしておくのがミソです。
これで、正解なら１、不正解なら０を立てることができました。

正答数を数えます。
正解なら１が立っているわけですから、合計してやれば正答数が出てきます。
つまり、図の緑の部分、Ｍ２セルにはおなじみのＳＵＭ関数を入れてやります。
　　Ｍ２：=SUM(B2:K2)

正答率を求めましょう。
同様に１か０が立っている状態ですので、この範囲の平均を求めれば
自然と正答率を導いてくることができますね。
なので、平均を求めるＡＶＥＲＡＧＥ関数をＮ２セルに入れてやります。
　　Ｎ２：=AVERAGE(B2:K2)
これでお望みの処理が完了です。

ただし、設問が一つしかない、なんてこともないでしょう。
設問が複数あるとＤＥＬＴＡ関数は使いづらいかもしれません。
なので、ほかの皆さんもおっしゃる通り、
このようなときはＣＯＵＮＴＩＦ関数やＣＯＵＮＴ関数を使うのが
楽ちんであると私は思います。
正答数を数えるためにＣＯＵＮＴＩＦ関数、
　　Ｍ９セル：=COUNTIF(B9:K9,A9)
正答率を算出するために回答全数をＣＯＵＮＴ関数で
　　Ｎ９セル：=M9/COUNT(B9:K9)
とそれぞれしてやります。
これであとは下方向（行方向）にフィルしてやれば
設問が幾つあってもＯＫです。
各個人の正答率も同じ考え方で可能ですので、
そちらは考えてみましょう。

お礼

回答していただき、ありがとうございました。

回答No.3

bunjii

>上記がそれぞれ入力されているとして、A1の正解とB1～K1の解答をそれぞれ比較して、正解なら１、不正解なら０で計算し、最終的に１０名の平均の正解率を出すにはどのような関数を入れたら良いでしょうか？
DELTA関数を必ず使う場合は作業用のテーブルを使う必要があります。
N列からW列までを作業用のテーブルとしたときN1へ次の数式を入力します。
=DELTA($A1,B1)
N1セルを右へW1セルまでコピーすれば設問設問毎の正誤が1または0の結果を得られます。
設問数に応じてN1:W1を下へ必要数コピーすれば回答者毎の正誤が1または0の結果が得られます。
Excel 2013で検証した結果ではAVERAGE関数で作業用テーブルの縦に集計すれば回答者毎の正解率、横に集計すれば設問毎の正解率が求められます。
DELTA関数を使わずに同じ論理で集計するときはAVERAGE関数とINDEX関数を使えば作業用テーブルを必要としません。
設問が10問としたとき次の数式で回答者（B）の正解率が求められます。
=AVERAGE(INDEX(($A1:$A10=B1:B10)*1,0))
右へ9個コピーすれば各自の正解率が求められます。
設問毎の正解率は次の数式になります。
=AVERAGE(INDEX((A1=B1:K1)*1,0))

お礼

回答していただき、ありがとうございました。

回答No.1

SI299792

=COUNTIF(B1:K1,A1)
で、正解者の人数が出ます。
これを10で割り、表示を％にすればいいです。

お礼

回答していただき、ありがとうございました。