Excel（エクセル）活用コミュの小問ごとにテストを採点

選択式テストの採点をシンプルな関数で行いたいです。

いま小問が5問あり、1問ごとに選択肢が4つあります。
ただし答えは1つではなく2つのときもあります。

(1)○○はどれか
　答え.Ａ,Ｃ
(2)××はどれか
　答え.Ｂ
：

この採点をするときに、答えが過不足なく合っていれば5点、1つ多いor少ないときは3点をつけようと思います。
つまり25点満点になります。
減点はありません。

回答者ごとにうまく採点する方法はありますでしょうか？

ちなみに下は現段階で取っている方法です。
・A1～A20に選択肢20コの正答を○or×で打ち込みます
・B1～B20に回答者の答えを○or×で打ち込みます。○が選んだ選択肢、×が選ばなかった選択肢です。
・A1=B1かつ…かつA4=B4なら+5点
かなり冗長で、シンプルなやり方を探しています。

説明足らずな部分があり申し訳ありません。
よろしくお願いします。

コメント(20)

最初
全て
最新の40件

[1] mixiユーザー 11月03日 04:09

例えば、B21～B25にそれぞれの問題の得点を表示するならば、
　B21=5*(A1=B1)*(A2=B2)*(A3=B3)*(A4=B4)
　B22=5*(A5=B5)*(A6=B6)*(A7=B7)*(A8=B8)
　B23=5*(A9=B9)*(A10=B10)*(A11=B11)*(A12=B12)
　B24=5*(A13=B13)*(A14=B14)*(A15=B15)*(A16=B16)
　B25=5*(A17=B17)*(A18=B18)*(A19=B19)*(A20=B20)
のような式で算出できると思います。

()で括った各部分式は、()内の条件が満たされるならばTrue(=1)として評価され、
満たされない場合はFalse(=0)として評価されます。
従って、5以降の式は、*で掛け算対象とした全ての部分式が満たされる場合のみ
1となり、そうでない場合は0となります。

また、B26に合計点を出すならば、
　B26=sum(B21:B25)
のよう算出できます。

[2] mixiユーザー 11月03日 08:52

(1)
○○
○☓
☓○
☓☓

(2)
○○
○○
☓○
☓○

上記の場合は何点になりますか？

[3] mixiユーザー 11月03日 11:05

> Minonさん
ありがとうございます

なるほど～全部掛け算にすれば見やすくなりますね！
１コだけ等しくないとき＋３点とするにはどうしたものでしょうか(´ω`)

[4] mixiユーザー 11月03日 11:12

> ミスキタさん

ありがとうございます

選択肢を上からa～dとしますね。
(1)は
・正答bを選んでいない
・誤答cを選んでいる
ので0点です

同様に(2)も
・誤答cを選んでいる
・誤答dを選んでいる
ので0点です

たとえば
(3)
a○○
b
c　○
d

(4)
a○
b○○
c
d

のとき＋３点となります。

[5] mixiユーザー 11月03日 12:35

もう一つ、以下の場合は何点ですか？

a ○○
b ○○
c ☓○
d ☓☓

[6] mixiユーザー 11月03日 13:07

次の手順を試してみてください。

C1　に　=(A1=B1)*1　と入力して20行目までオートフィル。

D4　に　=IF(SUM(C1:C4)=4,5,IF(SUM(C1:C4)=3,3,0))　と入力し、D1:D4を選択して20行目までオートフィル。

[7] mixiユーザー 11月03日 13:42

a ○☓
b ☓☓
c ☓☓
d ☓☓

このパターンもありますね。（答えを書かなかった場合）

> この採点をするときに、答えが過不足なく合っていれば5点、1つ多いor少ないときは3点をつけようと思います。

この条件からすると 3点になると思いますが、たぶん 0点にするのですよね？
この判定がめんどくさそう。

[8] mixiユーザー 11月03日 14:19

>>3 夜になったら舞踏会さん
ありゃりゃ、3点加点する条件を見落としていました…。
(酔っ払って投稿は今後控えます。。。)

ちょっと作業用セルを必要としますが、考え直してみました。
相変わらずスマートさからは程遠いですが、積み木のように
条件を積み重ねているだけなので考え方・処理の仕方は単純だと
思います。

(考え方)
・1問につき1箇所作業セルを設け、正解と回答者による回答との一致数を
　カウントします。
・問題ごとの得点セルでは、一致数>=3なら3点加点、
　更に、一致数>=4なら2点加点します。
　ただし、加点状況によらず回答が全て×なら0点とします。
・合計得点セルでは、単純に問題ごとの得点セルの値の
　合計値を出します。

(式の入力例)
・B21～B25を作業用の一致数セルとする

　B21=1*(A1=B1)+1*(A2=B2)+1*(A3=B3)+1*(A4=B4)
　B22=1*(A5=B5)+1*(A6=B6)+1*(A7=B7)+1*(A8=B8)
　B23=1*(A9=B9)+1*(A10=B10)+1*(A11=B11)+1*(A12=B12)
　B24=1*(A13=B13)+1*(A14=B14)+1*(A15=B15)+1*(A16=B16)
　B25=1*(A17=B17)+1*(A18=B18)+1*(A19=B19)+1*(A20=B20)

　　※式中の1*の部分には、ブール値を整数値に変換させる意味があります。
　　　省くと、整数値ではなくブール値のTrue/Falseのまま評価され、
　　　意図せぬ計算結果になる恐れがあります。

・B26～B30を得点セルとする

　B26=( 3*(B21>=3)+2*(B21>=4) ) * (COUNTIF(B1:B4,"=×")<>4)
　B27=( 3*(B22>=3)+2*(B22>=4) ) * (COUNTIF(B5:B8,"=×")<>4)
　B28=( 3*(B23>=3)+2*(B23>=4) ) * (COUNTIF(B9:B12,"=×")<>4)
　B29=( 3*(B24>=3)+2*(B24>=4) ) * (COUNTIF(B13:B16,"=×")<>4)
　B30=( 3*(B25>=3)+2*(B25>=4) ) * (COUNTIF(B17:B20,"=×")<>4)

・B31を合計得点セルとする

　B31=SUM(B26:B30)

[9] mixiユーザー 11月03日 18:58

> ミスキタさん
そう、一応それもあるんですよー。
ただそれはルール通り３点にします。
『答えは１つ以上あります』って言ってありますので、無回答はたぶんないと思います。
なので無回答の場合は考慮してくださらなくて大丈夫です^^

[10] mixiユーザー 11月03日 19:00

> ミスキタさん
あすいませんコメントを逆に見てました

a○○
b○○
c　○
d

のときも３点でよいです。
何度もすみません(∵`)

[12] mixiユーザー 11月03日 19:09

> Hal (ねこ使い)さん
あーなるほど。手順を素直にって感じですね！
参考になります(｀･ω･´)
ありがとうございます

[13] mixiユーザー 11月03日 19:24

こんなんでどう？
=CHOOSE(SUMPRODUCT(($A$1:$A$4=$B$1:$B$4)*1)+1,0,0,3,3,5)

検証したつもりだけど、どうかな？

[14] mixiユーザー 11月03日 20:32

たつや.comさんの方法でいいと思いますが、ちょっと間違えているようですよ。

=CHOOSE(SUMPRODUCT(($A$1:$A$4=$B$1:$B$4)*1)+1,0,0,0,3,5)

[18] mixiユーザー 11月03日 21:07

0>この採点をするときに、答えが過不足なく合っていれば5点、1つ多いor少ないときは3点をつけようと思います。

全然読んでなかった。

7>答えを書かなかった場合

9>それはルール通り３点にします。

そっか考慮しなくて良いのか…
なら、14の式で良いですね。
何度も書いたり消したりしてしまった…

[19] mixiユーザー 11月05日 00:00

> Minonさん
な・ながい