エクセルでデータ抽出

質問

エクセル初心者です。
エクセルでに次のような表を作っております。
（Sheet1）
担当　会社　郵便番号　住所
菊地　Ａ社　0000000　○○○○
菊地　Ｂ社　0000000　○○○○
菊地　Ｃ社　0000000　○○○○
田中　Ｄ社　0000000　○○○○
田中　Ｅ社　0000000　○○○○
山本　Ｆ社　0000000　○○○○
山本　Ｇ社　0000000　○○○○
　　　　　　　・
　　　　　　　・
　　　　　　　・
と800件くらいあるのですが、
これを
別シート（Sheet2）で名前を菊地と入力すると菊地のデータが一覧で出るようにしたいのですが、どうしたらよいでしょうか。
できれば、マクロを使わずに関数でやりたいです。
VLOOKUPかな？と思い、いろいろ調べたのですが、抽出したいデータが複数あるので、悩んでおります。

すみませんが、どなたかご指導お願い致します。

回答No.6

sakenomo · ベストアンサー

式を修正しました。表はＡ列からＤ列までとしています。式をＢ１に入れた後、下と右方向へオートフィルしてください。

=INDEX(Sheet1!$A$1:D$1010,IF(COUNTIF(Sheet1!$A$1:$A$1000,$A$1)>=ROW(),MATCH($A$1,Sheet1!$A$1:$A$1000,0)+ROW(A1)-1,1010),COLUMN()-1)

該当データが無い場合は、各列の１０１０行のセルの内容が表示されます。０を消す方法を＃４に書きましたが、データ中に０があることが考えられる場合は、スペースを使った方法の方がいいかもしれません。

お礼

回答ありがとうございます。

おお！できました！感謝です！
とりあえずコピペしてやっただけなんですが、これから式のほうも、研究させていただきます☆
わがままにつきあっていただきありがとうございました。

回答No.5

sakenomo

＃４です。m(__)m

すみません。＃４のは、名前しか抽出しませんね。寝起きの頭で考えていて、ボケてました。ああ、恥ずかしい。

回答No.4

sakenomo

表はあらかじめ担当名をキーに、ソートしてあるものとします。また、表の最終行は1000行としています。

Sheet２のＡ１に検索する名前を入力するものとして、
Ｂ１に以下の式を入れて、下へ必要分だけオートフィルしてください。

=INDEX(Sheet1!$A$1:$A$1010,IF(COUNTIF(Sheet1!$A$1:$A$1000,$A$1)>=ROW(),MATCH($A$1,Sheet1!$A$1:$A$1000,0)+ROW()-1,1010),0)

名前が入らないセルには０が表示されますが、この０はSheet1のＡ1010の内容を表しています。
０を表示したくない場合は、Ａ1010にスペースを入力するか、式を入れたセルの書式の表示形式を、ユーザー定義で＃にしてください。

回答No.3

imogasi

関数は、条件に合った行の抜き出しが不得手です。
だからマクロはいや、操作がいやというのは考えを改めるべきと思います。
本質問コーナーで関数式に拘る方が多いのですが、関数式の得手不得手を考えないと。
関数の回答を上げますが、正直のところ技巧的だと思います。
(データ）サンプルとして、Ｓｈｅｅｔ２のＡ１：Ｂ１１に
東田 大分市
大川 別府市
菊地 下関市
大川 防府市
三田 三原市
河野 新見市
大川 倉敷市
山田 姫路市
西野 明石市
浜野 尼崎市
大川 長岡京市
（質問では、同名は同一営業マンのケースですが、変えてますがよろしく）
（条件）
探す人の名前をＳｈｅｅｔ３のＡ１セルに「大川」と入れます。
（関数式）
Ｓｈｅｅｔ３の
Ｂ１セルに=IF(Sheet2!A1=$A$1,1,"")といれＢ１１（最下行）まで複写します。
Ｃ１セルに=COUNT($B$1:B1)といれ最下行まで複写します。
Ｄ１セルに=IF(ROW()<MAX($C$1:$C$11)+1,OFFSET(Sheet2!$A$1,MATCH(ROW(),$C$1:$C$11,0)-1,0),"")
といれ最下行まで複写します。
Ｅ１セルに=IF(ROW()<MAX($C$1:$C$11)+1,OFFSET(Sheet2!$A$1,MATCH(ROW(),$C$1:$C$11,0)-1,1),"")
といれ最下行まで複写します。（Ｄ列と１字違いだけです。）
Ｄ，Ｅ列のような式をＦＧ・・列に項目（フィールド）数だけ作ります。
（結果）Ｓｈｅｅｔ３のＤ１：Ｅ４に
大川 別府市
大川 防府市
大川 倉敷市
大川 長岡京市
（その他）
Ｓｈｅｅｔ３のＢとＣ列は（楽屋裏のデータで）邪魔なので、見え難いＸ、Ｙ列などに持っていくのがよいかも。
上記を理解し、自分のケースではどこをどう変えるべきか
を会得してください。

お礼

回答ありがとうございます。

＞だからマクロはいや、操作がいやというのは考えを改めるべきと思います。
すみません。色々わけありでして、このデータを私本人が管理するのであれば、マクロでも操作でも問題ないのですが、上からとにかく使いやすくしろとのことで。。
こんなわがままにつき合わせて申し訳ありません。m(_ _)m

これから回答の方試してみます。
まずは、御礼だけ先にさせていただきます。

回答No.2

tibinkoron

[データ]->[フィルタ]->[フィルタオプション]で、抽出先をSheet2の任意の場所、リスト範囲をリスト全体、検索条件範囲をSheet2の任意の場所（上の方がよい）二設定します。

抽出条件は、
担当　会社　郵便番号　住所
菊地

のようにします。
AND条件のときは、同じ行に
OR条件のときは、別の行に
条件を設定します。

お礼

回答ありがとうございます。

すみません。できればメニューの機能などは使わずに、別シートに名前を入力すると一覧が表示されるというような設定にしたいのですが。。

回答No.1

fiva205c

同じシートになりますが、フィルターを使えば菊地分だけ抽出できますけど、それはもうご存知でしたか？

http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu9_2.htm

参考URL：

http://homepage1.nifty.com/kenzo30/ex_kisosyo/ex_ks_syokyu9_2.htm

お礼

回答ありがとうございます。

フィルターは知りませんでした(^^;
しかし、やはりメニューの機能などは使わずに、別シートに名前を入力すると一覧が表示されるというような設定にしたいのですが。。