Excelの計算式を教えて欲しい

質問

少し長文になりますが、読んでいただけると幸いです。

■やりたいこと
赤いセル（F20）に、自動的に正解率が入るようにしたい。

■計算式の考え方
F11～14の中で、○がつくとC列の点数が加算される。
例えば、Aの項目で○がつくと、20点加算。Bの項目で○がつくと、25点加算。
×がついた場合は、その項目は0点となる。
N/Aがついた場合は、その項目は採点対象から外れる。
0点とは違い「そもそも存在しなかったもの」として考える。

■詳細
添付したファイルだと、太郎はAが○なので20点、Bが×なので0点、
CがN/Aなので採点対象からはずす、Dが○なので30点。
これを合計すると、20点+0点+30＝50点。
N/Aは採点対象からはずすので、75点満点中、50点だったということ。

花子で考えてみると、すべてに○がついているので、20+25+25+30＝100点。
100点満点中、100点だったということ。

大介は、Bが○なので25点、Cも○なので25点、他はN/Aなので、
50点満点中、50点だったということ。

太郎　：75点満点中、50点　⇒　正解率は、66.7%
花子　：100点満点中、100点　⇒　正解率は、100%
大介　：50点満点中、50点　⇒　正解率は100%

この「正解率」が、赤いセル（F20）に自動的に反映されるようにしたい。

■その他
点数は時期によって変動するので、20点を加算する、25点を加算するという式ではなく、
「C11の値が加算される」「C12の値が加算される」という式になっていて欲しい。

以上です。
どうやっても解決しなかったので、Excelに詳しい方がいたら、教えていただければ助かります。
説明が下手で恐縮ですが、よろしくお願いします。

回答No.3

kagakusuki · ベストアンサー

この場合は、IF関数を使用する必要は無いと思いますから、

=SUMIF(F$11:F14,"○",$C$11:$C14)/(SUMIF(F$11:F14,"○",$C$11:$C14)+SUMIF(F$11:F14,"×",$C$11:$C14))

で良いと思います。

お礼

ありがとうございました。
おかげで、解決することができました。

回答No.4

imogasi

参考
この表の設計でＮ／Ａが関数＝ＮＡ（）の値、すなわち、エラー値だとすると、ほとんどの数式が使えなくなる。
セル範囲を引数に指定する関数式は、結果かエラーとなって正常に機能しない。
働くのは｛=SUM(IF(ISERROR(B2:B5),0,1)*(A2:A5))｝（配列数式）のように、エラー値を他の値に置き換えに成功したときだけで、この置き換えて、その結果で何かをさせる式を見つけるのが難しい。
文字列”Ｎ／Ａ”などに置きかえる（ＩＦ関数でその該当の場合、第2引数を”Ｎ／Ａ”にしたらしまい）ように設計すべきだ。
すると
例　Ｂ列は実際はＦ，Ｇ，Ｈ列にあっても式の列番号が変わるだけ。
Ａ列の配点は、その期ごとに変えれば式は変えないで済む。
生徒ごとの式は右方向の式の複写で対処できる。
Ａ列　　　　Ｂ列
20 ○
25 ×
25 N/A　←文字列
30 ○
　　の場合
獲得点は　=SUM((B2:B5="○")*(A2:A5)*(B2:B5<>"N/A"))　と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（配列数式）
で　５０点
ーー
満点は　=SUM(IF(B2:B5="N/A",0,1)*(A2:A5))　と入れてＳＨＩＦＴ＋ＣＴＲＬ＋ＥＮＴＥＲ（配列数式）
で　７５点
正解率は上記の割り算。
ーー
配列数式がイヤならＳＵＭＰＲＯＤＵＣＴ関数でも出来ると思う。

お礼

ありがとうございます。
おかげで、解決することができました。

回答No.2

noname#157410

まず　C1に =IF(ISERROR(F1:F13),COUNTIF(F1:F13,"○"))と入れると○の数が出ます
N/AとはERRではじかれた数字ですか？その場合ISERRORを使えばいいかと思います。

さらに　C2そのカウント数に点数をかければその数字が出ます
=C1*20

後は足して行って、セルを%に書式設定すればいいと思いますが？
F20
=C2+C3+・・・・

という感じではないですかね？

お礼

ありがとうございます。
おかげで、解決することができました。
説明が不足していてお手数をおかけしました。

回答No.1

keithin

○の付いた得点はSUMIFで数えます。
SUMIF(F11:F14,"○",$C11:$C14)

満点は，４教科の全てに○か×かNAが入る前提で
SUMIF(F11:F14,"<>N/A",$C11:$C14)
となります。

結果して
=IF(COUNTA(F11:F14)=4,SUMIF(F11:F14,"○",$C11:$C14)/SUMIF(F11:F14,"<>N/A",$C11:$C14),"")
といった具合に組み立てることが出来ます。

＃参考
満点の求め方については，○の付いた得点をSUMIFで数える（上述の通り）と，×の付いた得点をSUMIFで数える（上述の応用）を加算するという考え方も出来ます。
上述のように「４科目の成績が埋まって初めて計算できる」としたくないときは，こちらの考え方を使います。

お礼

ありがとうございます。
おかげで、解決することができました。
詳しい説明、ありがとうございました！