エクセルでこんな抽出は可能ですか？

質問

関数を使用して抽出したいと思います。
まずシート１とシート２を使います。
シート２には住所録などの内容が5000件くらいあるものとします。
その住所録には「〒」「住所」「電話番号」「氏名」などのフィールドを作っておきます。
そして、シート１には　例えば「氏名」という欄をA1に書き、B1の空白のセルに「山田」と入力をすると、「山田」が含まれるものをすべて表示したいと思います。
このすべて表示とういうのは「山田」を含む「氏名」だけ表示されるのではなく、「〒」「住所」「電話番号」「氏名」を表示したいのです。
オートフィルタやフィルタオプションは極力使用したくないので、関数で出来ないものかと考え中です。

また、抽出結果を表示する欄はシート１のA3あたりからお願いします。
こんな文書で意味が分かった方、よろしくお願いします

回答No.2

zap35 · ベストアンサー

Sheet2で補助セルを使って実現する方法です。

Sheet2の「氏名」列（Ｄ列とします）の右隣列（Ｅ列とします）を補助セルとして使い、E2セルに以下の式を入れます
　=IF(NOT(ISERROR(FIND(Sheet1!$B$1,Sheet2!D2))),ROW(),"")
この式で、検索したい名前があればその行数が表示されます。E3～EnはE2をコピーしてください

次にSheet1でA3に以下の式を入れます。すると氏名が合致した最初の行の〒番号が参照されます。A3の式をA4～Anにコピーすると２番目、３番目も表示されます。

　=IF(ISERROR(SMALL(Sheet2!$E$2:$E$5000,ROW()-2)),"",INDIRECT("sheet2!a" & SMALL(Sheet2!$E$2:$E$5000,ROW()-2)))

住所、電話番号、氏名はINDIRECT("sheet2!a"のaをb,c,dに変更すれば参照が可能です。

配列関数を使って補助セルを使用しない方法もありますが、それはご自身で研究して下さい。

補足

ありがとうございます。
なかなか理想的な内容にせまってきました。
なるほど、こういう方法がありましたか。

回答No.4

NCU

「山田」で検索すればいいでしょう。

お礼

はいはい。
それは簡単ですね。

回答No.3

Wendy02

こんばんは。

昔、そういうデータベース関数がありましたね。今は、フィルタオプションに替わってしまいました。DGETに似ているけれども、DGET では、ご質問のようなケースではエラーが出ます。

フィルタオプションも、ちょっとした使い方しだいで、関数なみになります。ちょっとした使い方のコツがあるのですが、あまり、最初から拒否されてしまうと、回答側では、どうしようもありません。他にもありますが、それ以上に難しいです。本来は、自分が使う使わないは関係なく、いろんな種類を知っておくと、後々、応用が利くようになると思います。

今回は、配列数式になると思います。（配列の確定はしなくてよいです。)5000件のレベルだと、Version によっては、ちょっと厳しいものがあると思います。

A1：
検索値
を入れます。

B1:～フィルハンドル・コピー

=IF(ROW(A1)>COUNTIF(Sheet2!$D$1:$D$50,$A$1),"",INDEX(Sheet2!$A$1:$D$50,SMALL(INDEX((ISERROR(SEARCH($A$1,Sheet2!$D$1:$D$50))=FALSE)*ROW(Sheet2!$D$1:$D$50),,),COUNTA(Sheet2!$D$1:$D$50)-COUNTIF(Sheet2!$D$1:$D$50,$A$1)+ROW(A1)),COLUMN(A1)))

注意：データ範囲は正確に、空白行は入れないでください。

範囲を入れるときは、[編集]－[置換]　で入れ替えてください。範囲は、絶対参照がベターです。

必ず、最初のオートフィルでコピーする最初の数式が、上記のものです。

現行の数式は、Sheet2 のD列を検索対象としています。

山田を含む場合は、山田*　とA1 に入力してください。

お礼

ありがとうございます。

この関数の方法をいただきます。ちょっとチャレンジしてみます。
オートフィルタやフィルタオプションは拒否しているのではなく、オートフィルタで検索すると、簡単に検索できてしまうのがダメなのです。
これでは個人的に意味を持たないのです・・・ごめんなさい。

回答No.1

bari_saku

「“山田”を含むセルの数」でよければ、

データの範囲がA2：D5000とすると、

=COUNTIF(A2:D5000,"*山田*")

になると思います。
ただし、一行一件としたい場合、一行に2カ所「山田」があるとダブルカウントしてしまいます。

どこかに「この行に“山田”が含まれるセルがあるか否か」を判断する作業列を作って、その行数を合計するようにすればいいですが、あまりスマートではないですね…

お礼

さっそくありがとうございます。

しかし、個数ではダメなんです。「山田」と入力した時に「山田」の「〒」「住所」「電話番号」「氏名」を表示させたいのです。
「山田」が100人いたら、100件の、「〒」「住所」「電話番号」「氏名」を表示させたいのです・・・