Excel>他シートと文字比較後のリスト表示方法？

質問

始めて投稿します。
エクセルで以下のことを実施したいのですがどうしても
できずに詰まっています。

知恵をいただけないでしょうか。

------------------------------------------------
※イメージ画像を添付しています。

「Sheet1の分類」の値と、「Sheet2の分類」の値が等しかった場合、
Sheet1の「コース名」箇所に、Sheet2で一致した分類の隣セルにある
コース名のリストを表示したいのです。

その方法を教えていただけないでしょうか。

【Sheet1】
　　　A　　　　B　　　　　
１　分類　　コース名　　　
２　1.A　　　　　　　　　　←BセルにSheet2と一致したコース名のリストを表示させたい
３　1.A
４　2.B
５　　：

【Sheet2】
　　　　A　　　　　B　　　　　C
１　コード　　　分類　　　　コース名
２　　A　　　　　1.A　　　　　あ
３　　B　　　　　1.A　　　　　い
４　　C　　　　　1.A　　　　　う
５　　D　　　　　2.B　　　　　か
６　　E　　　　　2.B　　　　　き
７　　：　　　　　　：　　　　　　：

出来たら、エクセルの関数だけでどうにかなるといいなと思っていますが、
マクロを使って解決するならばそれでも構わないと思っています。

アドバイスをいただけないでしょうか。

以上、よろしくお願いいたします。

回答No.2

KURUMITO · ベストアンサー

少し面倒な操作になりますね。
シート2での表をそのまま使うことはできませんので作業に必要な表をシート2の中に作ります。
以下はシート2での操作になります。
D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=1,ROUNDDOWN(MAX(D$1:D1),-2)+100,MAX(D$1:D1)+1))

E1セルには次の式を入力して右横方向にオートフィルドラッグします。

=IF(COLUMN(A1)>INT(MAX($D:$D)/100),"",INDEX($B:$B,MATCH(COLUMN(A1)*100,$D:$D,0)))

E3セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(E$1="",ISERROR(INDEX($C:$C,MATCH(INDEX($D:$D,MATCH(E$1,$B:$B,0))+ROW(A1)-1,$D:$D,0)))),"",INDEX($C:$C,MATCH(INDEX($D:$D,MATCH(E$1,$B:$B,0))+ROW(A1)-1,$D:$D,0)))

次にE1セルから例えばE10までのセルを選択します。
「数式」タブの「名前の定義」をクリックします。
表示な画面で「名前」の窓には_1.AとE1セルと同じ文字の頭に _ を入れた文字を入力します。画面下の「参照範囲」には=Sheet2!$E$1:$E$13と表示されていますが、それを=Sheet2!$E$2:$E$10に改めてOKします。
同様の操作をF,G,H・・・列について行いそれぞれの列について1行目のセルの名称を名前として登録します。
これでシート2での作業は終了します。

次にシート1に戻ってB2セルから下方を範囲として選択します。
「データ」タブの「データの入力規則」をクリックします。
入力値の種類を「リスト」にしてから元の値の窓には次の式を入力してOKします。

=INDIRECT("_"&A2)

これでB2セルから下方のセルではA列のデータに即したプルダウンリストが表示されるようになります。
操作は面倒ですが頑張ってください。

お礼

朝早くからありがとうございました。

処理の仕方もよくわかり、やりたいことができました！

回答No.1

KURUMITO

シート2には同じ分類でもコース名がいくつかありますね。
どのように対応したらよいのでしょうか？

補足

ありがとうございます。

シート1とシート2で同じ分類であれば、
シート2のコース名をシート1のBセルにリストとして
表示させたいです。

例えば、
シート1で、分類が「1.A」だった場合、シート1のコース名セルには、
シート2の「あ」「い」「う」というリストで表示させて選べるように
したいのです。

よろしくお願いいたします。