エクセル関数を使い、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.1 回答者： zap35 回答日時： 2007/02/18 00:57

シート１のB2に以下の式を入れます

=OFFSET(シート２!$C$1,SUMPRODUCT((シート２!$A$2:$A$700=$A2)*(シート２!$B$2:$B$700=B$1)*ROW(シート２!$A$2:$A$700))-1,0)

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

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

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

この回答へのお礼

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

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

お礼日時：2007/02/24 23:33

No.2 回答者： Cupper 回答日時： 2007/02/18 08:51

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

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のヘルプを参照してください。

この回答へのお礼

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

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

お礼日時：2007/02/24 23:32

No.3 ベストアンサー 回答者： imogasi 回答日時： 2007/02/18 10:38

例データ　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算数国語社会作業列
A001CBA0
A002BAC3
A003BDD6
式を簡単にするため、作業列はどこか秋列に作る（ここでは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の開始行以下で科目名を探さないといけないので、その分修正しているので複雑化している。

この回答へのお礼

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

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

お礼日時：2007/02/24 23:30