Excelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

質問

Excelですが、検索キーに、同一データ（レコード）が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行（レコード）を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、＜検索結果＞シートのように、該当のレコード３つ（行２～４）を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

＜データシート＞
　列A 　　列B 　　　　列C 　列C
行１ 　No 　会社名 　　　所属 　担当者
行２ 　1 　A株式会社　　○事業部　　坂下順人
行３ 　2 　A株式会社　　△事業部　　滿山友人
行４ 　3 　A株式会社　　△事業部　　目標達子
行５ 　4 　B株式会社　　設計部 山下清人
行６ 　5 　B株式会社　　営業部 横浜美人
行７ 　6 　C株式会社　　営業部 川崎次郎
行８ ・・ ・・・・・・・・・・

＜検索結果：別のシートにおいて＞
行１ 　 会社名 　所属 　　担当者
行２ 　 A株式会社 　○事業部　坂下順人
行３ 　 A株式会社 　△事業部　滿山友人
行４ 　 A株式会社 　△事業部　目標達子

回答No.5

zap35 · ベストアンサー

＃０３です
＞最後のROW（T1）で、T1を参照しておりますが

ROW(T1)は「１番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは２番目のという意味です

#REF!エラーになりますか？
私が示した条件と何か変えていませんか？
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥　（テストしてありますので）

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

お礼

ありがとうございました。お手数をおかけしました。

成功しました。完璧です。
（式は、このHPから、Excelへコピペしましたが、データシートのデータをA列から入れておりました。B列から入れましたところ、完璧に動作しました。）

今回、必要な該当不要なレコードは要らないところがミソですが、該当データは、10レコード以内ですので、10レコード分だけに、この式を入れておけば十分に目的を果たせます。

来週に実機でやって見たいと思います。

しかし、残念ながら、まだ、式の構成は解析できておりません。
もし、各関数で何をしているか、さらに、ご教示いただけると幸ですが・・・（特に、Index関数はよく分かりません）
ありがとうございました。

回答No.4

hallo-2007

オートフィルターと関数の組み合わせですが
＜データシート＞
　列A 　　列B 　　　　列C 　列C
行１ 　No 　会社名 　　　所属 　担当者
行２ 　1 　A株式会社　　○事業部　　坂下順人
行３ 　2 　A株式会社　　△事業部　　滿山友人
行４ 　3 　A株式会社　　△事業部　　目標達子
行５ 　4 　B株式会社　　設計部 山下清人
行６ 　5 　B株式会社　　営業部 横浜美人
行７ 　6 　C株式会社　　営業部 川崎次郎
のA列を=SUBTOTAL(3,C$2:C2)入れて下までコピィしておくと
オートフィルターをかけた後の表示に対して連番が出ます。
別シートには
1
2
3
4
と番号だけ準備しておいて　=VLOOKUP(A1,データシート!A:D,2,FALSE)
とかで番号を検索値にしてVLOOKUP関数を貼り付ける。

お礼

hallo-2007さま

ご回答ありがとうございました。
ご教示の方法は、別の業務処理に既に使っており、重宝しております。

今回は、自動で検索し該当データを全て表示したいので、その方法を探しておりました。

回答No.3

zap35

よく見かける質問です。事例もたくさんありますが、関数式は難しくなりますよ。

例えば適当なシートのA1セルに「A株式会社」と入力して、そのシートの適当なセルに以下の式を貼り付けて、下方向、および右方向にコピーしてください。
　=INDEX(データシート!B:B,SMALL(INDEX(SUBSTITUTE((データシート!$B$1:$B$100=$A$1)*1,0,10^5)*ROW(データシート!$B$1:$B$100),),ROW(T1)))

すると以下のような結果になります。
　　　　　Ａ列　　　　　　Ｂ列　　　　　Ｃ列
１行目　A株式会社
２行目　A株式会社　　○事業部　　坂下順人
３行目　A株式会社　　△事業部　　滿山友人
４行目　A株式会社　　△事業部　　目標達子
５行目　#VALUE!　　　　#VALUE!　　　#VALUE!

５行目以下のエラーを表示させたくないなら条件を追加して以下のような式になります
　=IF(COUNTIF(データシート!$B:$B,$A$1)>=ROW(T1),INDEX(データシート!B:B,SMALL(INDEX(SUBSTITUTE((データシート!$B$1:$B$100=$A$1)*1,0,10^5)*ROW(データシート!$B$1:$B$100),),ROW(T1))),"")

でもこのような式はデータ行数が多くなると「とっても重く」なります。そういう意味では＃０２さんが言われるように「現実的ではない」かもしれません。

抽出は一回切りでよいのであれば（データシートにデータを追加したら別シートの抽出結果がリアルタイムに変更される必要がなければ）、オートフィルタやフィルタオプションで条件を指定して抽出することも可能です。関数が理解できなければそちらをお使いください。

補足

zap35さま

ご教示ありがとうございました。
早速、式を、コピペしてテストしてみましたが、#REF!　のエラー表示なります。
最後のROW（T1）で、T1を参照しておりますが、T1には、何もデータがないように見えますが、これはOKでしょうか？

あまり使ったことのない関数（INDEX,SMALL,ROW）の組み合わせですので、この式の理解に少々時間が必要です。

なお、実際は、データ総数が200件はあり、検索該当の複数件数は、MAX6件ですので、検索結果が、200行になるのでは、この式やはり実務上は使うのは無理と思われます。VBAしかないのでしょうか。(@^^)/~~~

回答No.2

merlionXX

> オートフィルター機能は存じておりますが

#1のmerlionXXです。
関数で複数データを自動表示させるのは非常に難しいです。
どうしてもやりたければ=IF([Book1.xls]Sheet1!$B2="A株式会社",[Book1.xls]Sheet1!B2,"")　のような式を必要範囲にオートフィルで広げ、表示されたデータを作業列を使って上詰めで表示させることでしょうか。

わたしなら関数はあきらめVBAでやりますが。

補足

merlionXXさま

お手数をおかけしたました。
Excelで一連の事務処理処理をするように組み立てておりますが、この部分の自動化が残った部分です。これが、結構うっとうしいので、自動化したいと思った次第です。VBAは私が退職した時に、メンテナンスが難しかろうと思い、導入には慎重です。
ありがとうございました。

回答No.1

merlionXX

オートフィルターで抽出できますよ。
データを選択し、メニューの「データ」、「フィルタ」、「オートフィルタ」でＢ列の▼をクリックし、「A株式会社」をクリック

表示された結果をコピーして別シートに貼り付ければＯＫです。
（エクセル2007なら手順が多少違うかもしれません）

補足

merlionXXさん、ありがとうございます。

質問が不十分でした。
オートフィルター機能は存じておりますが、そうでなく、データのExcel　Bookとは別のExcel　Bookにおいて、検索した結果の、該当のレコードを全て、自動で表示したいのです。

よろしくお願いします。