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

質問

こんばんは。
いつもお世話になります。
以下の関数に関してよい方法が見当たりませんでしたのでとぴにより質問をさせて頂きます。

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

｛シート２｝
A 　　　B 　　C
生徒ID 教科 評価
A001 　算数 　C
A001 　国語 　B
A001 　社会 　A
A002 　算数 　B
A002 　国語 　A
A002 　社会 　C
...

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

以前、以下のような数式を教えて頂きました。
このときは評価（文字）ではなく数値を返すものなので達成できましたが文字にしてからエラーが出てしまいます。
-------
◆Sheet1のB2の式

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

ご指導頂きたく、どうぞ宜しくお願い致します。

回答No.3

imogasi · ベストアンサー

例データ　Sheet1　A1:C１０
生徒ID 教科 評価
A001 算数 C
A001 国語 B
A001 社会 A
A002 算数 B
A002 国語 A
A002 社会 C
A003 算数 B
A004 国語 D
A005 社会 D
Sheet1に（下記A、B、C、Dは式の結果答えも出た様子）
生徒ID 算数 国語 社会 作業列
A001 C B A 0
A002 B A C 3
A003 B D D 6
式を簡単にするため、作業列はどこか秋列に作る（ここではF列とする）
式は=MATCH(A2,Sheet2!$A$2:$A$100,0)-1
意味は生徒IDの初出（開始）行のB1からの隔たり行数を出している。
ーー
B2に
=OFFSET(Sheet2!$A$2,$F2+MATCH(B$1,OFFSET(Sheet2!$B$2,$F2,0,100,1),0)-1,2)
B2no式をB列下方向に生徒数分、ｂ２：B4の式を右方向に科目数分
式を複写する（＋ハンドルを引っ張る等）
結果
上記の通り。
作業列はF列の式をB2の式にネストして、、組み込めば不要となるのは
ご存知でしょうが、理解のため、そのままにします。
ーー
やっているのはMATCH関数で生徒IDや科目命をみつけている。
ただし（B2以下でなく）その生徒IDの開始行以下で科目名を探さないといけないので、その分修正しているので複雑化している。

お礼

ご回答が遅くなりました。
ご提示頂きました計算式で試してみましたら、目的の形になりました。
すばやい対応を頂きありがとうございました。

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

回答No.2

Cupper

シート２ のデータが規則性を持って並んでいるのであればセルの位置を計算で指定できますから
INDIRECT関数で 示すことができると思います。

シート１のB2セルにはA001の数学の評価が入るのであれば、
　=INDIRECT("シート２!C2")
で示すことができます。

括弧の中は他の関数に置き換えることで規則性を持たせることができます。
教科の数が3つなら
　B2 =INDIRECT(ADDRESS((ROW(B2)-2)*3+(ROW(B2),3,,,"シート２"))
　C2 =INDIRECT(ADDRESS((ROW(C2)-2)*3+(ROW(C2),3,,,"シート２"))
　D2 =INDIRECT(ADDRESS((ROW(D2)-2)*3+(ROW(D2),3,,,"シート２"))
教科の数が5つなら
　B2 =INDIRECT(ADDRESS((ROW(B2)-2)*5+(ROW(B2),3,,,"シート２"))
　C2 =INDIRECT(ADDRESS((ROW(C2)-2)*5+(ROW(C2),3,,,"シート２"))
　D2 =INDIRECT(ADDRESS((ROW(D2)-2)*5+(ROW(D2),3,,,"シート２"))
　E2 =INDIRECT(ADDRESS((ROW(E2)-2)*5+(ROW(E2),3,,,"シート２"))
　F2 =INDIRECT(ADDRESS((ROW(F2)-2)*5+(ROW(F2),3,,,"シート２"))
など（ADDRESS、ROW以外の関数を使用しても可能です）
A002以下の行は、A001の数式をフィルでコピーするだけです。

規則性がない状態で入力されているとは考えにくいので、この方法で十分な気がするのですが如何でしょう。

※使用している関数の詳細はExcelのヘルプを参照してください。

お礼

お返事が遅くなりました。
ご提示いただきました関数を試してみたいと思います。

ありがとうございました。

回答No.1

zap35

シート１のB2に以下の式を入れます
=OFFSET(シート２!$C$1,SUMPRODUCT((シート２!$A$2:$A$700=$A2)*(シート２!$B$2:$B$700=B$1)*ROW(シート２!$A$2:$A$700))-1,0)

このセルを右方向、下方向にコピー

シート２に該当するデータが無い場合は、#REF!エラーになりますが、エラーになる方が点検はしやすいと思います。

なおシート２のデータは７００行まであるものとして式を書いていますので、それ以上なら変更して下さい

お礼

お返事が遅くなりました。
教えていただきありがとうございました。

今後ともどうぞ宜しくお願い致します。