エクセルで複数行の抽出結果データを表示したい

質問

こんばんは。行き詰ってどうしようもありませんので質問いたします。どうかご教授ください。
エクセルの抽出データ表示についてなのですが、sheet2にデータを置き、sheet1に結果表示を行おうと思っております。
問題なのは、sheet2にあるデータが複数行にわたっているということと、データがかなり多いということです。

例としてsheet2は以下のようだとします。

　　Ａ 　　　　　　Ｂ　　　　　　Ｃ　　　　　　Ｄ
１　ルート１　大阪→　　奈良→　　　京都
２　　　　　　　滋賀　　　←三重　　　　←　
３　ルート２　大阪→　　京都→　　　奈良
４　　　　　　　滋賀　　　←三重　　　　←
５　・・・以下　データが続く・・・・

Sheet1の任意のセルで「ルート１」を選択すると、選択セルの下あたりにsheet2のB1:D2を表示したいのです。

ルートが20ほどあるのですが、１つのセルに20回のＩＦ文を使えないし・・VLOOK関数、HLOOK関数などはそれぞれ１行、1列しか対応していないので複数行の表示ができないし・・
もちろんＶＢＡなどわからない素人です。
もう半分あきらめかけている状態ですが、もし！うまくいく方法があるのでしたらお教えください。
お願いいたします。

回答No.2

guchi_yama · ベストアンサー

別例ですが、

Sheet1のB1に

=INDEX(Sheet2!B1:B40,MATCH($A$1,Sheet2!$A$1:$A$40,0))

と入れ、B2にオートフィル
その後、B1,B2をD1,D2までオートフィルすれば出来ると思います。

因みにB1:B40　$A$1:$A$40　はルート入力の範囲なので、
任意で変えて下さい。

お礼

迅速にご返答いただきましてありがとうございます！
皆様が博識なのに驚きました！
無事解決いたしました。ありがとうございました！

回答No.5

imogasi

自称imogasi方式でやってみます。
作業列を使いますが、比較的式の理解が易しいとおもう。
本来はVBAなどが必要な課題。
Shweet1
ルート２
ルート１ 大阪→ 奈良→ 京都
ルート１ 滋賀 ←三重 ←
ルート２ 大阪→ 京都→ 奈良 1
ルート２ 滋賀 ←三重 ← 2
ルート２ → 岐阜→ 愛知 3
ルート３ 大阪→ 兵庫→ 岡山
E1の式は
=Sheet2!B1
です。
E2以下は
=IF(A2=$E$1,MAX(A$1:$E1)+1,"")
と入れて下方向に式を複写してください。
該当行に上から連番を振ってます。
ーー
Sheet2に行って、条件の「ルート２」をB1に入れます。
A2は式を
=INDEX(Sheet1!$A$1:$D$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),COLUMN())
と入れてD2まで式を複写します。行番号－１に当たるSheet1!でのE列の数の行を持ってきます。
A2:D2を下方向に式を複写します。
結果
ルート２ 大阪→ 京都→ 奈良
ルート２ 滋賀 ←三重 ←
ルート２ → 岐阜→ 愛知
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
ーー
＃N/Aを出さないようにするには
ROW()-1がmax(Sheet1！$E$1:$E$100)より大きければ空白というIF関数を加えてください。
ーー
A列の空白を上の値で満たす方法
別列にH2とかに
=IF(A2="",A1,A2)
と入れて下へ式複写
A列へ値で戻す。
上行と同じ値なら白色文字に条件付書式で設定する。見えなくする。（詳細略）

お礼

そうですよねー。本来はＶＢＡが必要なんですね。
でも皆様のお力で、自分の望む範囲では十分なくらいなものができました。本当にありがとうございました！

回答No.4

maron--5

◆「Sheet1の任意のセル」をA2としますと
◆Sheet1のB2の式
B2=IF($A2<>"",INDEX(Sheet2!B:B,MATCH($A$2,Sheet2!$A:$A,0)),IF(AND($A1<>"",$A2=""),INDEX(Sheet2!B:B,MATCH($A$2,Sheet2!$A:$A,0)+1),""))
★右と下にコピー

お礼

迅速にご返答いただきましてありがとうございます！
とても明快でわかりやすかったです。ありがとうございました！

回答No.3

zap35

まずシート２のA列は空白をなくしてください。以下の例で説明します

　　　　　　Ａ列　　　Ｂ列　　　　Ｃ列　　　　Ｄ列
１行目　ルート１　大阪→　　奈良→　　京都
２行目　ルート１　滋賀　　　←三重　　←
３行目　ルート２　大阪→　　京都→　　奈良
４行目　ルート２　滋賀　　　←三重　　←
５行目　ルート２　→　　　　岐阜→　　愛知
６行目　ルート３　大阪→　　兵庫→　　岡山

別のシートのＡ１セルに「ルート１」を入力して、その下あたりのセルに以下の式を貼り付け、下方向と右方向にコピーします。
=INDEX(Sheet2!B:B,SMALL(INDEX(SUBSTITUTE((Sheet2!$A$1:$A$100=$A$1)*1,0,999)*ROW(Sheet2!$A$1:$A$100),),ROW(A1)))

ただし上の式では、表示する値がないセルはエラーになります。そのエラーを出さないようにするには、条件判定を追加して以下の式になります。
=IF(COUNTIF(Sheet2!$A$1:$A$100,$A$1)>=ROW(A1), INDEX(Sheet2!B:B,SMALL(INDEX(SUBSTITUTE((Sheet2!$A$1:$A$100=$A$1)*1,0,999)*ROW(Sheet2!$A$1:$A$100),),ROW(A1))),"")

式中の「Sheet2!$A$1:$A$100」（３カ所）はSheet2のルート名が書かれた範囲です。単独で出てくる「$A$1」（２カ所）は別シートのルート名を入力したセルです。これらは実際のシートに合わせて変更してください。「ROW(A1)」はシートのセルとは意味が違いますので変更しないでください。

いきなり実際のシートではうまくいかないでしょうから、まずは回答の例の通りに試してみてください。

お礼

迅速にご返答いただきましてありがとうございます！
エラーが出ないようにすることまでお教えいただきましてありがとうございました。

回答No.1

chem_taro

私流のやりかたですが・・・。

2行で一組のデータということですよね。
であれば、ルート１は1行目、ルート２は3行目・・・ルートｎは(n-1)*2+1行目からはじまるわけです。

あとはADDRESSとINDIRECTでごり押ししてデータを取り出したりしています。もっとスマートなやり方もあるかもしれませんが、とりあえず。

=INDIRECT(ADDRESS((ルート番号-1)*2+ROW(A1),COLUMN(A1),,,"sheet2"))
これでsheet2の一番左上のセル（ルート１）の中身を取り出せます。
あとは、フィルハンドルでコピーしていくとと、A1の部分が動き、隣のセルも取り出せると思います。

なかなか原始的な方法かもしれないので、他の方のフォローを待った方がいいかもしれません！＾＾；
もしこの方法でわからないことがあればお答えしますので言ってください。

お礼

迅速にご返答いただきましてありがとうございます！
無事解決いたしました。ありがとうございました！