複数一致からの検索

質問

シート１のような様な表があります。シート２に日付、部屋番を入力するとシート１の日付、部屋番の一致から※Ｃ１に名前が表示されるようにしたいです。
シート２のＣ１にどのような関数を入力したらよろしいでしょうか？
よろしくお願い致します。

シート１
　 A 　　B 　　C
　 日付　　部屋　　名前
1　　1/1　　101　 太郎
2　　1/1　　102　　花子
3　　1/1　　201　　次郎
4　　1/2　　101　　花子
5　　1/2　　102　　太郎

シート２
　　　A B C
　　日付　　部屋　　名前
1　　1/1　　102　　※花子

回答No.6

MackyNo1 · ベストアンサー

＞ROW関数の前の100は何を示しているのでしょうか？

((Sheet1!A2:A100=A2)+(Sheet1!B2:B100=B2)<2)*100+ROW(A2:A100)

この数字は条件に合致しない場合に、データ数よりも大きな行番号を返すための係数です（したがって1000でも10000でもOKです）。

配列数式の知識がないとわかりにくいところがありますが、数式を説明すると、Sheet1!A2:A100がA2と等しくSheet1!B2:B100がB2と等しい場合は、この2つの式を加算した式は2を返しますが、それ以外のセルは1または０を返します。

したがって、2つの式が2未満の条件に合致する場合はTRUEとなり、それに100を掛けると100になりますが、この条件に合致しない場合（すなわちA列とB列が一致するデータの場合）は、FALSEとなりこれに100を掛けると０が返ります。

この数字に行番号を加算すると、A列とB列が一致するデータの場合はそのまま行番号を返しますが、合致しないセルはデータの行番号よりも100大きい数字となります（この行は空白行）。

この配列の中から最小値（2つの列が一致する100より小さい数字）の行番号をC列からINDEX関数で引っ張ってくる数式になっています。

ちなみに最後の「&""」は該当データがない場合に、数式で求めた行番号が空白行の行番号となり、空白セルを参照した時の「0」が表示されるのを防ぐために入力しています。

＞あとシート１が100を超えそうな場合はどうしたらよいでしょうか？

上記のように、この数字は特に大きな意味がある数字ではないので、たとえばデータが1000以内なら1000としてください（多くのセルに配列数式を入力すると、計算負荷がかかりますので、配列部分のセル範囲は必要以上に大きめに設定しないようにしてください）。

お礼

ありがとうございました。関数って奥が深いですね
まだまだ勉強しないといけないと感じました。楽しいから続けられるんですがね。
助かりましたまた何かあればよろしくお願いいたします

回答No.5

tom04

えNo．2です！
たびたびごめんなさい。
補足に
>あとシート1が100行を超える時が来ると思います。その時はどうしたらよ　いでしょうか？
>指定の範囲をC全行などとしたらかなり動きが遅くなってしまいます。アドバイスお願いします。

とありましたので再びお邪魔しました。

当方使用のExcel2003の場合ですが
最終行は65536行目になると思います。
最終行までオートフィルでコピーするのも大変でしょうから、
そこで一つの方法ですが、
最終行まで数式をあらかじめ入力する方法です。
前回の画像で説明させていただくと
Sheet1の作業列D2セルにとりあえず、
=A2&B2
という数式を入れます。
そして、D列全てを範囲指定（列番号の「D」の部分でクリックすると列全てが範囲指定されます）
　→　右クリック　→　挿入
これで作業列がE列に移動しましたので、
挿入したD2セルに「2」と入力
D2セルをアクティブにし、メニュー　→　編集　→　フィル　→連続データの作成　を選択すると↓の画面がでますので、
ここで範囲を「列」・「停止値」欄に「65536」と入力してOK
これで最終行まで連続データが作成されましたので
先ほど移動したE2セルのフィルハンドルでダブルクリック
これで最終行まで数式が入力されます。
最後にD列全てを削除してSheet1の操作は完了です。
同様にSheet2のC2セルに数式を入力します。
この場合の数式は
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet1!$C$2:$C$65536,MATCH(A2&B2,Sheet1!$D$2:$D$65536,0)))
となります。
その後の操作は先ほどSheet1で行った操作と同様に、C列全てを範囲指定し、列を挿入　→　最終行まで連続データを作成し、移動したD2セルのフィルハンドルでダブルクリック
最後にC列全てを削除して完了です。
Sheet2も最終行まで数式が入っているはずです。

尚、だるるクリック後、表示されるのに若干時間がかかるかもしれません。
どうも何度も失礼しました。m(__)m

お礼

詳しい説明ありがとうございました
参考になりました。また何かありましたらよろしくお願いいたします

回答No.4

makiko1981

みなさまが既に回答されている方法と大差はないのですが、
こういうやり方もできますのでご参考までに。。。

手順としては
　(1)Sheet1のC列に作業列を作成
　(2)C1=A2&B2　の関数をうめこみ
　(3)Sheet2のC列に
　　=VLOOKUP((A2&B2),Sheet1!C2:D6,2,0)
　(4)気になるようであれば、作業列(Sheet1/C列）を非表示にする
　　→C列にマウスをあわせ、右クリック⇒非表示を選択

作業列は＆のかわりにconcatinateを使ってもよいと思います。

お礼

ありがとうございました。何とかできました

回答No.3

MackyNo1

2つの条件に合致するセルの値を表示するなら以下の式をC2セルに入力します。

=INDEX(Sheet1!C:C,MIN(INDEX(((Sheet1!A2:A100=A2)+(Sheet1!B2:B100=B2)<2)*100+ROW(A2:A100),)))&""

Sheet2の３行目以下にも検索データがあるなら、Sheet1のデータ範囲を絶対参照にしてください。

補足

ありがとうございます。作ることができました。
ROW関数の前の100は何を示しているのでしょうか？
あとシート１が100を超えそうな場合はどうしたらよいでしょうか？

お手数掛けますがご指導よろしくお願い致します

回答No.2

tom04

こんばんは！
色々方法はあるかと思いますが・・・
一例です。
↓の画像でSheet1に作業用の列を設けています。
作業列D2セルに
=A2&B2
としれオートフィルでずぃ～～～！っと下へコピー。

Sheet2のC2セルに
=IF(COUNTBLANK(A2:B2)>0,"",INDEX(Sheet1!$C$2:$C$100,MATCH(A2&B2,Sheet1!$D$2:$D$100,0)))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
尚、数式はSheet1の100行目まで対応できるようにしています。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

補足

回答ありがとうございます。出来ました
(1)あとシート1が100行を超える時が来ると思います。その時はどうしたらよ　いでしょうか？
　指定の範囲をC全行などとしたらかなり動きが遅くなってしまいます。
　アドバイスお願いします。
(2)シート１がいっぱいになった時にを入力する際いちいち行を足さなくても自動で増やすことは出来　ますか？

回答No.1

Tofu-Yo

シート１のB列とC列の間に１列設けて、非表示にするは反則ですか？
もしよいのなら

１．追加した新しいC列のC1に、「=MONTH(A1)*100000+DAY(A1)*1000+B1」と入力し、C5までコピペします。

２．シート２のC1に、「=vlookup(MONTH(A1)*100000+DAY(A1)*1000+B1,シート１!$C$1:$D$5,2,0)」と入力します。

複数のKeyで検索するときはこのように結合させたひとつのKeyを作るとVLOOKUPが使えて便利です。
なお、Keyの見た目にこだわらなければMONTH(A1)*100000+DAY(A1)の代わりにA1そのものを使っても大丈夫です。

お礼

ありがとうございました
参考になりました。また何かありましたらよろしくお願いいたします