みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

投稿日時 - 2008-08-31 10:55:19

QNo.4292393

困ってます

質問者が選んだベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

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

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

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

投稿日時 - 2008-08-31 22:09:09

お礼

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

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

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

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

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

投稿日時 - 2008-09-06 09:46:32

ANo.5

このQ&Aは役に立ちましたか?

13人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.4

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

投稿日時 - 2008-08-31 15:20:55

お礼

hallo-2007さま

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

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

投稿日時 - 2008-08-31 20:39:07

ANo.3

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

例えば適当なシートの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列      B列     C列
1行目 A株式会社
2行目 A株式会社  ○事業部  坂下順人
3行目 A株式会社  △事業部  滿山友人
4行目 A株式会社  △事業部  目標達子
5行目 #VALUE!    #VALUE!   #VALUE!

5行目以下のエラーを表示させたくないなら条件を追加して以下のような式になります
 =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))),"")

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

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

投稿日時 - 2008-08-31 14:57:16

補足

zap35さま

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

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

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

投稿日時 - 2008-08-31 20:34:29

ANo.2

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

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

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

投稿日時 - 2008-08-31 14:23:48

補足

merlionXXさま

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

投稿日時 - 2008-08-31 20:19:54

ANo.1

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

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

投稿日時 - 2008-08-31 11:35:54

補足

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

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

よろしくお願いします。

投稿日時 - 2008-08-31 12:57:40

あなたにオススメの質問