エクセルのデータ検索について(1)

質問

ご覧いただきありがとうございます。

写真の添付が1枚しか出来ない為、質問を2つに分けて、1枚ずつ添付します。

◯[Sheet1]に図のようなデータがあった場合なのですが、そのままだと見辛いので、それぞれ別のシートで検索出来るようにしたいなぁと思っております。
【A列】→Ｎｏ．
【B列】→高校名
【C列】→学科
【D列】→郵便番号
【E列】→住所
【F列】から、添付写真のように内容が入力されていて、県ごとに表が分かれています。(表全体を載せたいのですが、画像が粗い為、抜粋しました。分かりづらくてすみません。)

◯[sheet2]では、県名(プルダウンで選択)→高校名(県名に連動して、プルダウンで選択)→内容(H26年度 入塾生・H27年度 入塾生・H28年度入塾生・H27年度 体験授業参加人数…などをプルダウンで選択)→人数(クラス毎)で検索したいです。
(『エクセルのデータ検索について(2)』に添付の上の図)

◯[sheet3]では、内容(プルダウンで選択)→高校名一覧＋人数(sheet1の表で、人数が0の場合は表示しない)で検索したいです。
(『エクセルのデータ検索について(2)』に添付の下の図)

色々調べてみて、VLOOKUP・INDEX・MATCH関数を組み合わせると出来そうなのですが、どのように組み合わせて良いか分かりませんでした。

こんな感じの複雑な検索は可能でしょうか？
ご回答宜しくお願い致します。

回答No.2

kkkkkm · ベストアンサー

Sheet2のD3に
=IFERROR(INDEX(Sheet1!$B$5:$Q$11,MATCH($B3,Sheet1!$B$5:$B$8,0),MATCH(LEFT($C3,FIND("度",$C3,1)),Sheet1!$F$3:$Q$3,0)+COLUMN(E:E)-1),"")

Sheet３のB3に
=IFERROR(IF(INDEX(Sheet1!$B$5:$Q$11,ROW(1:1),MATCH(LEFT($A$3,FIND("度",$A$3,1)),Sheet1!$F$3:$Q$3,0)+COLUMN(F:F)-1)>0,INDEX(Sheet1!$B$5:$B$11,ROW(1:1),1),""),"")

として下方向に必要なだけコピーしてください。高校名は上から順に詰まっているものと考えています(歯抜けのようにとびとびで入力されていない状態)詰まっていないと間が空いて表示されます。

Sheet３のC3に
=IFERROR(INDEX(Sheet1!$B$5:$Q$11,MATCH($B3,Sheet1!$B$5:$B$11,0),MATCH(LEFT($A$3,FIND("度",$A$3,1)),Sheet1!$F$3:$Q$3,0)+COLUMN(E:E)-1),"")

としてF列までと下まで必要なだけコピーしてください。

なお、体験授業参加人数については3月4月5月のどれを表示するのか不明なので対応していません。

お礼

COLUMNという関数を使った事がないので、とても勉強になりました。
詳しくご回答いただきありがとうございました。

教えていただいた方法でやってみます！！
また困った際は質問させて下さい。
本当にありがとうございました。

回答No.1

heytommy811

あなたのおっしゃる要件を実現しようとすると作るのが大変です。
特に県名に連動した高校名のプルダウンを作るという作業です。

エクセルにフィルターという機能がありますので、そちらを駆使して必要なデータのみを表示するようにしてみてはいかがでしょうか？1シートのみで完結します。
フィルターであれば、一覧から絞り込むこともできますし、キーワード（例えば○○県）で絞り込むこともできます。

もし、どうしても別シートでプルダウンによるデータ出力がしたいのであれば、回答としてお伝えすることが大変なので遠慮させていただきます。
ヒントとしてはデータの入力規則にてリストを選択し、プルダウンに表示したいセルの範囲を選択します。その為、表示する内容をあらかじめ別のセルに用意しておく必要があります。47都道府県であれば簡単ですが、問題はもう一つのプルダウンに表示する高校名の一覧を各都道府県ごとに用意しておかなければなりません。
例えばA県はA列に高校名を、B県はB列に高校名を・・・という風にです。
まずこの作業が面倒だと思います。
そして高校名の方のデータの入力規則ではIF関数でどの都道府県が選択されているかを判定し、高校名の範囲を指定します。
=IF("都道府県のプルダウン" = "A県",A:A,IF("都道府県のプルダウン","B県",B:B,・・・),”都道府県未選択”)
ごらんの通り、全都道府県分を判定し個別に高校名の範囲を定義しておかなければなりません。

高校名さえ選択されればあとはVLOOKUP関数で必要な情報は全て取れるはずです。

Good Luck!!

お礼

ご回答ありがとうございます！！
説明不足で大変申し訳ございません。

プルダウンで選択する部分に関しましてはなんとか出来まして、今回教えていただきたかったのが、『エクセルのデータ検索について(2)』の添付写真の、
【上の図】→オレンジの部分(クラス毎の人数)
【下の図】→A列で選択した内容に該当する《高校名一覧》とオレンジの部分(クラス毎の人数)

に入力する関数になります。
写真が見辛くて申し訳ございません。