エクセルで条件に一致した別のシートにある行をシートに表示する方法

エクセルで、指定した条件に一致する別シートのセルを含む行をすべて抜き出す方法が知りたいです。

例
シート2
〈B列〉〈C列〉〈D列〉〈E列〉・・・〈M列〉
業者A　4月3日 数値D1　数値E1・・・数値M1　〈5行目〉
業者B　4月4日 数値D2　数値E2・・・数値M2　〈6行目〉
業者C　4月5日 数値D3　数値E3・・・数値M3　〈7行目〉

このような表があったとします。
①ここからシート1で業者Bを含む行をそのままの形で、
別のセルに抜き出したいのですが。
抽出結果を自動的に、別の場所に、常に表示させておきたいのと、
②同じくシート1で数値D2、数値E2両方に一致する行をそのままの形で別のセルに抜き出したいです。

①，②は同じシートでの表示が無理な場合は別々でも大丈夫です。
少し複雑な式になると思いますがわからないのでよろしくお願いします。

No.2 ベストアンサー 回答者： fujillin 回答日時： 2018/04/16 17:40

こんにちは

元データが変わっても自動的に抽出・反映できるようにしたいのなら関数かVBAによることになりそうですが、関数だとそれなりに長い式になります。
ご提示の例にはA列がありませんが、ひとまず、該当する行のA列からM列までを抽出するものとして…

抽出表示したい表示範囲の左上セル（A列に相当するセル）に以下の関数式を設定します。
=IFERROR(IF(INDEX(シート2!A:A,SMALL(IF(シート2!$B:$B="業者B",ROW($A:$A)),ROW(A1)))="","",INDEX(シート2!A:A,SMALL(IF(シート2!$B:$B="業者B",ROW($A:$A)),ROW(A1)))),"")

上式は配列数式ですので、Ctrl+Shift+Enterで確定します。（←これをやらないとダメです）
あとは、必要な範囲に（右方、下方へ）コピーフィルでいかがでしょうか？

※上式では、ひとまず検索対象をシートの列全体にしてありますが、ご質問文からすると5行目から（？）が対象なのかも知れません。対象の表の範囲が限定されている場合は、範囲を指定してください。
また、範囲を指定することで、余分な計算が省けるようになるので、反応は若干速くなるはずです。

※対象とする表内に空白セルが存在しないのであれば空白のチェックを省けるので、式も簡単になりますし、反応も早くなるものと思われます。（関数式で空白セルを参照すると「0」表示になるので、空白チェックをして空白表示になるようにしています）

※②についても同様の式で抽出可能ですが、条件が複数ある場合に IF(AND(条件1,条件2)～～　の形式の式にするとうまくいきません。
代わりに、IF((条件1) * (条件2),～～　の形式の式にしておくことで、複数条件でも抽出できるようになります。

この回答へのお礼

解答ありがとうございます。
わかりやすく説明していただきとても助かりました。
教えていただいた通りにしたらできました。

お礼日時：2018/04/17 11:53

No.3 回答者： mike_g 回答日時： 2018/04/16 19:39

B2: =INDEX(Sheet2!C$1:C$100,MAX(IF(Sheet2!$B$5:$B$100=$A2,ROW(B$5:B$100))))

【お断り】上式は必ず配列数式として入力のこと

この回答へのお礼

回答ありがとうございます。
画像まで載せていただきとても助かりました。

お礼日時：2018/04/17 11:55

No.1 回答者： yokomaya 回答日時： 2018/04/16 15:40

ファイルターしてコピペは試したのですか？

それを式にしなきゃダメな理由は？

この回答へのお礼

情報ありがとうございます。試してみます。

お礼日時：2018/04/16 15:56