エクセル 複数条件の検索でデータを抜き出す方法

質問

win７、エクセル２００７で作成中のデータベースで、複数の複数の条件に合ったデータ（レコードではなく１セル）を抜き出したいと思っています。

フィールド名は、時間帯、名前、教科、所属などです。

今作っている式は、

=IF(SUMPRODUCT((INDIRECT("'[時間割.xlsx]"&C$2&"'!$b$5:$b$150")=$B5)*(INDIRECT("'[時間割.xlsx]"&C$2&"'!$G$5:$u$150")=$M$28))=1,"〇","")

名前と時間帯が式の検索条件に入っています。
参照先が別ファイルの為、INDIRECTを使っています。

これで条件に合ったデータがあった場合に「〇」が表示されるようにまではできたのですが、さらに、条件に合ったデータの教科を表示するようにしたいと思っています。
ちなみに、人の時間割の為、条件に合うデータは１件しかないです。

DGETなどを使ってみつつ１日中考えていたのですが、訳がわからなくなってきました。。。

もし詳しい方おられましたら、教えて頂けないでしょうか？？
すみませんがよろしくお願いします！！

回答No.2

keithin · ベストアンサー

ちょっと数式に間違いがあったので手直します。ゴメンナサイ。
それと教科がＤ列にあるなら、そのように修正しないといけません。
=IF(A1<>"○","",INDEX(INDIRECT("'[時間割.xlsx]"&C$2&"'!D:D"),SUMPRODUCT((INDIRECT("'[時間割.xlsx]"&C$2&"'!b5:b150")=$B5)*(INDIRECT("'[時間割.xlsx]"&C$2&"'!G5:u150")=$M$28)*ROW($D$5:$D$150))))

また○になる、つまり該当する答えが「必ず１つしか無く」て「絶対に間違いなく１つはある」事がどんな時でも必ず保証できるなら、「IF(○なら」の部分は省略しても構いません。
具体的な例として、例えば一つ挙げるとＢ５やＭ２８とかにまだデータが未記入では、「答えが１つある」とはならないといった状況です。
「Ａ１に」を用意するのがイヤなら、あなたがご質問で書いた○の式を回答の式のＡ１の部分に直接放り込んでも、勿論計算できます。数式が倍の長さになるので、ただでさえごちゃごちゃしてるのがもっと見通しが悪く判りにくくなるだけですが。

ＩＮＤＥＸ関数は
=INDEX(範囲、位置)
のように２つの引数を使います。（今回のように範囲が１列あるいは１行の場合。範囲が面に広がってる場合は、範囲、縦、横の３つの引数になります）
最初のINDIRECT関数が範囲、続きのSUMPRODUCTの一式が位置です。

お礼

できたぁ～～～～！！！
お付き合いありがとうございました！！
うろ覚えで使っているのでわけわからなくなってきてました。
大変助かりました！！

回答No.1

keithin

今作ってる式（○になったりならなかったり）がＡ１に，教科がＡ列に入ってるとして
=IF(A1<>"○","",INDEX(INDIRECT("'[時間割.xlsx]"&C$2&"'!$A$5:$A$150"),SUMPRODUCT((INDIRECT("'[時間割.xlsx]"&C$2&"'!$b$5:$b$150")=$B5)*(INDIRECT("'[時間割.xlsx]"&C$2&"'!$G$5:$u$150")=$M$28)*ROW($A$5:$A$150))))
などのように，もう一工夫するだけです。

お礼

ありがとうございます！今日はちょっと時間が取れないので、明日試してみます。ありがとうございました！！

補足

説明と理解が悪く申し訳ありません。。。

まず、「時間割」というファイルにB列「時間帯」C列「学年」Ｄ列「教科」Ｆ列「ＩＤ」Ｇ列「氏名」という項目があり、これは日付ごとのシートで２０日間程あります。
次に「配布用」というファイルに、B列「時間帯」c列「7/20」D列「7/22」・・・・のように日付が入っています。これは３行目です。B列５行目から時間帯が入っていて、ｃ列からの日付の下に、M28の氏名、C列からの日付、時間帯を検索条件として、〇ではなく、実際の教科に入っている教科名が導き出される形で、関数を入れたいと思っています。

入れて頂いた式なのですが、作業用のセルを使って、〇で無い時には空欄という形で、式を分けたほうがわかりやすいのでしょうか？？それとも私の説明不足で、〇欄も作りたいと捉えさせてしまったのでしょうか？？

そして、INDEX関数の引数が２つになっているみたいだけどなぜなんだろう、、、ROW($A$5:$A$150)で行の何を決められているのか、、、理解できず（＞＜）、、、何となく試してみたけど、答えも導き出せずの状態で、、、

分かりにくい説明で申し訳ありませんが、もしよろしければもう少しお付き合いいただけると大変助かります。。。
よろしくお願いいたします！！