エクセル関数を使い、2つの条件にあった値を2つの条件にあったセルに返す。

質問

こんばんは。
いろいろとWEBで調べたのですが、よい方法が見当たりませんでしたのでとぴにより質問をさせて頂きます。

｛シート１｝
A B C D
生徒ID 算数 国語 社会
A001
A002
A003
...

｛シート２｝
A B C D
生徒ID 教科 点数
A001 算数 10
A001 国語 15
A001 社会 20
A002 算数 12
A002 国語 18
A002 社会 22
...

やりたいこと。
｛シート１｝の生徒A001の算数のセルに｛シート2｝の2つの条件（A001,算数）にあう点数を返したい。

できれば関数または関数の組合せで行いたい。

難しい場合、達成できる方法をお教え頂きたく存じます。

どうぞ宜しくお願い致します。

回答No.3

imogasi

質問例と少し違うが、３日たって、締め切られそうなので、とりあえず上げます。
中間列等は使っていません。
私の配列数式の回答の中でも新機軸の物です。
３条件にも増やせそうです。
山田ー算数等のデータが複数出現しないこと。
例データ
Sheet1のA1:C13
大田 理科 11
坂上 算数 13
山田 算数 23
音羽 算数 26
山田 国語 35
江田 算数 36
今田 算数 44
木島 算数 17
坂上 国語 67
音羽 国語 45
江田 国語 89
今田 国語 90
木島 国語 36
Sheet2の
A列と第１行に
氏名　算数 国語
山田
坂上
江田
音羽
木島
今田

Sheet2のB2に
=INDEX(Sheet1!$A$1:$C$13,MAX(IF((Sheet1!A$1:A$13=$A2)*(Sheet1!B$1:B$13=$B$1),ROW(Sheet1!A$1:A$13),0)),3)
と入れて、SHIFT+CTRL+ENTERを同時に押す（配列数式）
B2の右下に＋ハンドルを出しB７まで引っ張る。
C2に
=INDEX(Sheet1!$A$1:$C$13,MAX(IF((Sheet1!A$1:A$13=$A2)*(Sheet1!B$1:B$13=$C$1),ROW(Sheet1!A$1:A$13),0)),3)
と入れて、SHIFT+CTRL+ENTERを同時に押す（配列数式）
C2の右下に＋ハンドルを出しC７まで引っ張る。
結果　Sheet2のA1:C7に
氏名 算数 国語
山田 23 35
坂上 13 67
江田 36 89
音羽 26 45
木島 17 36
今田 44 90
B2の式を複写してC2の式が出せそうですが、とりあえず急いで上げます。

回答No.2

maron--5

◆Sheet1のB2の式

B2=SUMPRODUCT((Sheet2!$A$2:$A$10=$A2)*(Sheet2!$B$2:$B$10=B$1)*Sheet2!$C$2:$C$10)

★右と下にコピー

お礼

コメントありがとうございます。
早速数式を入れて見ましたらできました。
とても役に立ちました。
ありがとうございました。

回答No.1

poohron

他にもっと良い方法があるかもしれませんが、
とりあえずこんな方法ではいかがでしょう。

シート２でD2セルに
=A2&B2
と入れてD3以降にコピー。
（これでD2には「A001算数」、D3は「A001国語」・・・と表示されるはず。）
見た目が良くないのでD列は非表示、または文字色を白にするなどしておいて下さい。

シート１の生徒A001の算数のセルには
=OFFSET(シート２!$C$1,MATCH($A2&B$1,シート２!$D:$D,0)-1,0)
他の教科、他の生徒のセルにもこの式をコピー。
「シート２」の部分はシート名に合わせて変えてくださいね。

これでいけるのではないかと思います。
※シート１とシート２で、IDや教科の文字列がちょっとでも違っていると#N/Aエラーになります。
　 例えば「A001」ではなく「A001 」（最後にスペースが入っている）など、
　 一見同じに見えてもそこはエラーになってしまいますのでご注意下さい。

お礼

ありがとうございました。
頂戴しましたアドバイスはとても参考になりました。
今後ともどうぞ宜しくお願い致します。