エクセルにとても困っています。どなたか助けてください！

質問

皆さまこんにちは。
いつも勉強させて頂いております。
どうにも困った事態に陥りまして、初投稿に至りました。
類似質問も一通り拝見させて頂いたのですが、
近いものが見つかりませんでしたので、質問させて頂きます。内容は以下の通りです。

　　　　　　　　Ａ　　　　　　　Ｂ
　１　　　　2006年度1学期　　2006年度2学期・・　
　２ 　 　国語(プルダウン)
　３ テストあ　　　◎
　４ テストい　　　○　　　　　
　５ テストう　　　
・
　50 合計点数　60点
　51 判定評価　ok

★Ａ２はプルダウンリストになっており、
　そこから教科を選択します。
★Ａ５０のセルの「合計点数」は、
　◎＝２点、○＝１点として集計しています。
★Ａ５１のセルの「判定評価」は、
　「合計点数」が別枠で設けた「目安点数」より上回　っていたら「ＯＫ」下回っていたら「ＮＧ」とし　ています。
最終的には、
プルダウンリストより教科を選択
　　　　　↓
その教科の「合計点数」を計算
　　　　　↓
その教科の「判定評価」を表示

↑この処理がしたいのです…。
関数のみでこの様な処理は可能でしょうか？
最近までは教科が１科目のみであった為、ＩＦ関数を使用しておりましたが、最近テスト項目・教科共に増えることになり、ＩＦ関数ではネスト制限にかかり、まかないきれなくなりました。
これまでのご回答を拝見させて頂き、
ＶＬＯＯＫＵＰ関数を使用した方が良い、という所までは理解出来たのですが、
自分の知識力・理解不足により先に進む事が出来ません。上司にも急かされておりまして、当方大変困っております…。

以上、皆さま年度末でご多忙のところ申し訳ありませんが、どうかご教授頂けませんでしょうか？
未熟者にご経験豊かな皆さまのお知恵をお貸し下さいませ。よろしくお願いいたします。

回答No.4

shinkun0114 · ベストアンサー

#1です。

補足拝見いたしました。

●問題点整理
　説明不足というよりは、解決していく問題点が多いんでしょうね。
いったん整理してみます。

(1)ドロップダウンリストを作成する(A2セル)
(2)ドロップダウンリストを参照して、A3～B49セルにテストのデータが
　　反映される。
(3)◎と○から合計点数を算出する(B50セル)
(4)合否判定を行う(B51セル)

このうち、(3)・(4)については、#2・#3のお二方の回答で解決したと思います。
ですから、残る問題は(1)と(2)ですね。

●(1)について
　まずは別の場所にリスト項目を並べたセルを用意しておく必要があります。
　これは別の新しいシートに用意しておいた方がいいでしょうね。
　別のシートのA1から
　　国語
　　数学
　　英語
　　理科
　　社会
　　　：
　など必要な科目を順に下に入れていきます。
　次にこれらの科目のセルをすべて範囲指定し、名前ボックスにlistを入力してください。
　これで、科目にlistという名前が付きました。

　次に最初のシートのA2に戻ります。
　ここで、メニューバーから、データ(D)-入力規則(L)を選びます。
　入力規則の設定画面が出ますので、
　「入力値の種類(A)」：「リスト」
　「ドロップダウンリストから選択」　→　チェックを入れる
　「元の値」：「=list」と入力

　これで、ドロップダウンリストについては作成できたはずです。

　ここまでやってみてください。

●(2)について
　#1でも書きましたが、どこかに元のデータはないのでしょうか？
　◎や○でなくとも、点数などで判定ができれば、データとして加工することは可能です。
　いずれにせよデータがないと、どうしようもありません。
　最初から入れるのでしたら、リストで選ぶのではなく、シートにそのまま書き込んだ方が
　早いと思いますよ。

お礼

どうもご丁寧にありがとうございました！
勉強させて頂きます。

回答No.5

imogasi

テストには国語、理科、数学・・があると思うが、プルダウンで国語を選んだとして、そのテストが国語のテストだという情報はどうなっているのですか。
各全科目のテストが混合入力された、科目名が入った元データが別シートにあるのですか。それを言わずして質問するのは、片手落ち。
関数で、国語だけ抜き出すのも関数では簡単ではない。
http://okwave.jp/kotaeru.php3?q=1179274
などimogasi方式というので多数回答しているのも１つの方法ですが。

お礼

ご回答ありがとうございました。
色々な方法があるのですね…。説明不足で申し訳ございませんでした。
急に頼まれたものであたふたしてしまいまして…(^^;

回答No.3

S-Fuji

どうも、イメージが出来ないところが多いのですが・・・
＃１サンの指摘にもありますが、プルダウンで教科を選択したとき、
テスト評価は、教科によって入れ替わるのでしょうか？。
　また、すぐ隣に２学期の成績を表示する事になると、他の教科を見るにはプルダウンで選択して見るのでしょうか？。
プルダウンリストの作り方はお解りでしょうか？。
判定評価で「目安点数」と同じ場合は、「OK」なの「NG」なのですか？

取りあえず、A3からA49までの、「◎と○」による合計点数の出し方だけ
=COUNTIF(A3:A49,"◎")*2+COUNTIF(A3:A49,"○")
これとても、直接数字を入力した方が早いし間違いが少ないと思いますけれど。

お礼

ご回答ありがとうございました！
参考にさせて頂きます！

回答No.2

mshr1962

A50=COUNTIF(A$3:A$49,"○")+COUNTIF(A$3:A$49,"◎")*2
A51=IF(A50>目安点数,"OK","NG")
合計と評価はこれでできますが、呼び出しの方の元の表はどのように登録されていますか？
それによって使う関数は変わってきますよ。

お礼

アドバイス頂きましてありがとうございます！
呼び出し元の表が無い状態で行おうとしておりました…。無知が恥ずかしいです。
こちらのやり方も今後活用出来るように勉強させて頂きます。ありがとうございました。

回答No.1

shinkun0114

なんとなくできそうな気がしますが、書かれているシートの配置がよくわかりません。

たとえば、A3セルには「 テストあ　◎」ここまでが入るのでしょうか？
それとも「◎」は隣のセルになるのでしょうか？

また、A2セルをリストから選んだ場合、そのリストの内容に応じ、
表示されるテストの結果が変わると考えてよろしいですか？
その場合、各テストの結果はどこかに置いてあるのでしょうか？

補足

こんにちは、お世話になります。
お返事頂きありがとうございます。

>A3セルには「 テストあ　◎」ここまでが入るのでしょうか？
それとも「◎」は隣のセルになるのでしょうか？

A3セルには「テストあ」のみが入ります。
　A列→テスト種類
　B列→◎、○など評価
になります。
C列に2006年2学期のデータが入ります。
メモ帳から貼り付けをした為、表示がおかしくなってしまったようです。

>A2セルをリストから選んだ場合、そのリストの内容に応じ、表示されるテストの結果が変わると考えてよろしいですか？
その場合、各テストの結果はどこかに置いてあるのでしょうか？

はい、選択教科に応じ、表示結果を変えられれば…と思っております。
その場合には個々テストの結果を置かなくてはいけないのですよね、やはり…。
全くの無知で申し訳ございません。
また、説明不足をお詫びいたします。