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株式会社 △事業部 目標達子
No.5ベストアンサー
- 回答日時:
#03です
>最後のROW(T1)で、T1を参照しておりますが
ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です
#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)
ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います
ありがとうございました。お手数をおかけしました。
成功しました。完璧です。
(式は、このHPから、Excelへコピペしましたが、データシートのデータをA列から入れておりました。B列から入れましたところ、完璧に動作しました。)
今回、必要な該当不要なレコードは要らないところがミソですが、該当データは、10レコード以内ですので、10レコード分だけに、この式を入れておけば十分に目的を果たせます。
来週に実機でやって見たいと思います。
しかし、残念ながら、まだ、式の構成は解析できておりません。
もし、各関数で何をしているか、さらに、ご教示いただけると幸ですが・・・(特に、Index関数はよく分かりません)
ありがとうございました。
No.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関数を貼り付ける。
hallo-2007さま
ご回答ありがとうございました。
ご教示の方法は、別の業務処理に既に使っており、重宝しております。
今回は、自動で検索し該当データを全て表示したいので、その方法を探しておりました。
No.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さんが言われるように「現実的ではない」かもしれません。
抽出は一回切りでよいのであれば(データシートにデータを追加したら別シートの抽出結果がリアルタイムに変更される必要がなければ)、オートフィルタやフィルタオプションで条件を指定して抽出することも可能です。関数が理解できなければそちらをお使いください。
この回答への補足
zap35さま
ご教示ありがとうございました。
早速、式を、コピペしてテストしてみましたが、#REF! のエラー表示なります。
最後のROW(T1)で、T1を参照しておりますが、T1には、何もデータがないように見えますが、これはOKでしょうか?
あまり使ったことのない関数(INDEX,SMALL,ROW)の組み合わせですので、この式の理解に少々時間が必要です。
なお、実際は、データ総数が200件はあり、検索該当の複数件数は、MAX6件ですので、検索結果が、200行になるのでは、この式やはり実務上は使うのは無理と思われます。VBAしかないのでしょうか。(@^^)/~~~
No.2
- 回答日時:
> オートフィルター機能は存じておりますが
#1のmerlionXXです。
関数で複数データを自動表示させるのは非常に難しいです。
どうしてもやりたければ=IF([Book1.xls]Sheet1!$B2="A株式会社",[Book1.xls]Sheet1!B2,"") のような式を必要範囲にオートフィルで広げ、表示されたデータを作業列を使って上詰めで表示させることでしょうか。
わたしなら関数はあきらめVBAでやりますが。
この回答への補足
merlionXXさま
お手数をおかけしたました。
Excelで一連の事務処理処理をするように組み立てておりますが、この部分の自動化が残った部分です。これが、結構うっとうしいので、自動化したいと思った次第です。VBAは私が退職した時に、メンテナンスが難しかろうと思い、導入には慎重です。
ありがとうございました。
No.1
- 回答日時:
オートフィルターで抽出できますよ。
データを選択し、メニューの「データ」、「フィルタ」、「オートフィルタ」でB列の▼をクリックし、「A株式会社」をクリック
表示された結果をコピーして別シートに貼り付ければOKです。
(エクセル2007なら手順が多少違うかもしれません)
この回答への補足
merlionXXさん、ありがとうございます。
質問が不十分でした。
オートフィルター機能は存じておりますが、そうでなく、データのExcel Bookとは別のExcel Bookにおいて、検索した結果の、該当のレコードを全て、自動で表示したいのです。
よろしくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) Outlookメール 連絡先の検索について 〈 ご説明 〉 Windows PC の Outlook 1 2022/09/23 14:43
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- 法学 全部取得条項付株式の取得と引換えにする株式の発行 申請書について 1 2022/12/21 17:32
- その他(IT・Webサービス) 不動産会社を起業し、ホームページを作りました。その際に、弊社は「◯◯不動産(株)」なのですが、県外に 1 2022/05/26 12:02
- Excel(エクセル) エクセルの数式で教えてください。 5 2023/02/10 15:11
- Excel(エクセル) Excelについて教えてください。 帳票データがあります。 アクセスに取り込むため、 データ形式にし 1 2022/06/08 19:59
- 医学 株式会社 3 2022/04/03 18:47
- 法学 全部取得条項付種類株式について 3 2023/01/28 10:53
- 法学 全部取得条項付種類株式について 1 2022/12/20 21:45
- Excel(エクセル) 至急です><Excelの関数を教えてください。 2 2022/03/22 17:56
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
複数シートからデータを拾って...
-
エクセルファイルのシート毎の容量
-
Excelでシートの違うデータでグ...
-
excelの不要な行の削除ができな...
-
シート削除して同名シート追加...
-
Excelで日付変更ごとに、自動的...
-
EXCELで2つのファイルから重複...
-
VBAで CTRL+HOMEの位置へ移動...
-
エクセルで名簿を50音で切り分ける
-
他のシートの一番下の行データ...
-
Excelファイルの容量が異常に大...
-
半導体熱抵抗の測定方法について
-
Excelで複数シートの内容を一覧...
-
エクセルのデータ振り分け方法...
-
EXCEL VBAで作成したス...
-
【エクセルマクロ】複数シート...
-
Excel 売上管理シートに入力し...
-
エクセル マクロ "特定の日付...
-
【マクロ】同じフォルダ内にあ...
-
オートフィルタで抽出したデー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルファイルのシート毎の容量
-
複数シートからデータを拾って...
-
Excelでシートの違うデータでグ...
-
シート削除して同名シート追加...
-
Excelで日付変更ごとに、自動的...
-
excelの不要な行の削除ができな...
-
VBAで CTRL+HOMEの位置へ移動...
-
(VBAにて)日付でデータを抽出す...
-
EXCELで2つのファイルから重複...
-
他のシートの一番下の行データ...
-
エクセルのカメラ機能について
-
トランジスタの選び方
-
別々のシートの表をピボットテ...
-
エクセル 縦に長い表の印刷時...
-
EXCEL 複数行のデータを1行にま...
-
【エクセル」 特定のセルで条件...
-
オートフィルタで抽出したデー...
-
Excel 売上管理シートに入力し...
-
エクセル VBA VLOOKUP
-
EXCEL の表を一行ずつシートに...
おすすめ情報