エクセル、複数あるデータを順番にピックアップする方法

質問

すみません、教えてください。
下記のようなデータの中から、たとえばＣ行の中から”りんご”に当てはまるデータを検索し、その列のＡ行とＢ行を抽出したいとすると、どのような方法がありますでしょうか。
別の空のシートに抽出したいのですが、よくわかりません。
よろしくご教授ください。お願い致します。

【元データ】
　　　 列Ａ　　　列Ｂ　　　列Ｃ　　　列Ｄ　　　…
列1　　八百丸　　渋谷区　　りんご　　１００
列2　　八百吉　　港　区　　みかん　　８０
列3　　八百屋　　新宿区　　か　き　　１００
列4　　八百正　　新宿区　　りんご　　２００

【求めたい検索結果】
八百丸　渋谷区
八百正　新宿区

回答No.2

zap35 · ベストアンサー

元データがSheet1にあるとします。

別シートのA1セルに「りんご」を入力して、適当なセルに以下の式を貼り付けて右、下方向にコピー。ただし元データは１０行までにしていますので実際のデータに合わせて範囲の変更（Sheet1!$C$1:$C$10）の変更が必要です

=INDEX(Sheet1!A:A,SMALL(INDEX(SUBSTITUTE((Sheet1!$C$1:$C$10=$A$1)*1,0,10^5)*ROW(Sheet1!$C$1:$C$10),),ROW(A1)))
もしくは
=INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$10=$A$1)*ROW(Sheet1!$C$1:$C$10),),COUNTIF(Sheet1!$C$1:$C$10,$A$1)-ROW(A1)+1))

ただしこの式では参照するデータがなくなるとエラーになります。エラーが出ないようにするなら条件判定を追加して
=IF(COUNTIF(Sheet1!$C$1:$C$10,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,SMALL(INDEX(SUBSTITUTE((Sheet1!$C$1:$C$10=$A$1)*1,0,10^5)*ROW(Sheet1!$C$1:$C$10),),ROW(A1))),"")
になります。

お礼

ありがとうございます！
早速試してみます！まずは取り急ぎお礼まで。
まったく思いつかない関数でした。本当に助かります。

回答No.4

nobu-

no１です。
>「抽出範囲」あたりがちょっと微妙

「抽出したいシート」の「抽出したい場所」（例えば10行目以降）の先頭行にフィールド名をコピーして置き（２のところで、列Aを「店名」、列Bを「区」とすると例えば「店名」、「区」を事前にA10、B10等に貼り付けておく）

「抽出範囲」のところで、A10とB10を範囲指定します。

お礼

おぉ～、なるほど。。。
これを覚えて活用できるようになればいろいろ応用が利きそうですね。
ご教授ありがとうございました。
今回は、取り急ぎの処理業務でしたので、Ｎｏ．２様の式をそのままコピペさせていただきましたが、皆さん丁寧に教えてくださったので、点数をつけるのがしのびなかったです。ありがとうございました。

回答No.3

imogasi

「imogasi方式」でWEB照会してください。
OKWAVEの質問の回答ばかりで、私の回答が出ますが、他の方の回答も
あわせて出ます。昨日も同様の質問がありました。

お礼

ありがとうございます！
早速見させていただきます。
本当に助かります。

回答No.1

nobu-

>別の空のシートに抽出したい

１．抽出したいシートにまず条件を記入します。

列C(実際は、フィールド名)
りんご

２．先に抽出したいフィールドを作成しておきます。

列A、列B(実際は、フィールド名)

３．抽出したいシートの条件を書いた場所をクリックし、（１のところ）メニューの「データ」の「フィルタ」の「フィルタオプション」をクリックします。

４．「リスト範囲」で。実際のデータが入っているシートのデータを範囲指定し、「検索条件範囲」で、１．の条件を範囲指定します。

５．「抽出範囲」で、２．のフィールドを範囲指定し、「OK」を押します（何もないところをクリックすると全フィールドが出るのでココが大事）

いかがでしょうか？

お礼

ありがとうございます！
「抽出範囲」あたりがちょっと微妙なので（私の理解度が）、頑張ってやってみます。また質問させていただくことがあるかも知れませんがよろしくお願いします。