オートフィルタを関数で実施する方法

Question

いつもお世話になります。

Sheet1に元データ〔Ａ列～Ｚ列、1行目項目名、2行～9000行に値（数値、文字）〕が有ります。

このデータのＥ列にお客様名が有るのですが、法人格の有無や支店名の有無等元データに問題が有り、

オートフィルタオプションの「～を含む」と同様の操作を関数で出来ないかご教示願います。

希望としては、Sheet2のB1にお客様名を入力すれば、A5以降にSheet1の該当データ（Ａ列～Ｚ列）が

表示されないかと考えております。

INDEX関数で試みたのですが、上手く行きませんでした。

皆様のご支援を宜しくお願い致します。

Answer 1

データ数が多くかつ自由な抽出条件で該当データを抽出したいなら「フィルタオプションの設定」（オートフィルタの｢オプション」ではない）を利用されることをお勧めします。

この場合は、E列の項目名をSheet2のB1セルに入力し、B2セルに抽出条件を「*検索したい名前*」というように入力して、Sheet2から

｢フィルタ｣｢フィルタオプションの設定」でリスト範囲をSheet1のデータ全体（列全体を選択するとよい）、検索条件範囲を「B1:B2」、抽出先を「指定した範囲」にチェックを入れ「A4」セルを選択してください。

どうしても関数で表示したいなら以下のような配列数式を利用することになります。

ご提示のレイアウトなら、まずSheet2のE5セルに以下の式を入力し下方向に適当数オートフィルしてお客様名を抽出してください。

=INDEX(Sheet1!E:E,SMALL(INDEX(ISERR(FIND($B$1,Sheet1!$E$2:$E$9000))*10000+ROW($E$2:$E$9000),),ROW(E1)))&""

抽出したお客様名（E列）に重複が無いならA5セルに以下の式を入力してその他の列にコピーしてください。

=IF($E5="","",INDEX(Sheet1!A:A,MATCH($E5,Sheet1!$E$2:$E$9000,0)+1))

E列に同じお客様名の重複データがある場合は、最初の式を利用して以下のような数式を使う必要がありますが、抽出データ数が多くなると再計算に時間がかかり、シートの動きが重くなるので関数処理することはあまりお勧めできません。

=IF($E5="","",INDEX(Sheet1!A:A,SMALL(INDEX(ISERR(FIND($B$1,Sheet1!$E$2:$E$9000))*10000+ROW($E$2:$E$9000),),ROW(A1))))

Answer 2

B1に入れた名前と完全一致するものだけをピックアップするのでしょうか？

可能は可能ですが、きわめて非実用的かつメンテ不能（多分）なものになります。

例えば（書き流しで良い式ではありません・・この手のものに良い式と言えるものがあればですがｗ）、

次のようなものになります。

Sheet1のE2:E11に名前が入っている

Sheet2のB1にピックアップする名前

Sheet2のA5に

=INDEX(Sheet1!E:E,MATCH(ROW()-4,COUNTIF(OFFSET(Sheet1!E$2,0,0,ROW($1:$10)),B$1),)+1)

として、CTRL+Shift+Enter（配列数式になります）

A5をA6以下にコピー

なお、この式はエラーエスケープもなにもしていませんので、例えば名前が２個しかなければ、3行目以降はN/Aになります。

要するにやめておいた方がよろしいという意味で書いただけです。

正攻法で、データを修正することをお勧めします。

Answer 3

y sakuda さん、マックちゃん さん、アドバイスありがとうございます。

一風 さん、こんにちは。 その後、いかがですか。

y sakuda さん、マックちゃん さんからいただいたアドバイスがご参考になればと思います。

同じような情報をお探しの方にもご参考になるよう、今回は私の方で \[回答としてマーク\] をいたしました。

もちろんマーク後も返信は出来ますので、さらに質問がある場合は、ご遠慮なく投稿も続けてください。

試した結果もお待ちしていますね。