EXCELでプルダウンを駆使したリストを作成したい

質問

添付データのような連動＋選択が可能なリストを作成したいと思っております。
プログラムを組むような知識はないのでエクセルの便利機能を使って作成ができるとありがたいです。
お知恵をお貸しください。

不明点あれば質疑お願いします。補足致します。

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

回答No.1

KURUMITO · ベストアンサー

シート1とシート2はともに1行目は項目名でＡ列からＧ列までにデータが有るとします。
シート1及びシート2にも作業列を作って対応します。
初めにシート1での作業を示します。
Ｊ2セルには次の式を入力して下方にドラッグコピーします。

=D2&A2

Ｋ2セルには次の式を入力して下方にドラッグコピーします。

=IF(D2="","",IF(COUNTIF(D$2:D2,D2)=1,ROUNDDOWN(MAX(K$1:K1),-4)+10001,INDEX(K$1:K1,MATCH(D2,D$1:D1,0))+COUNTIF(D$1:D1,D2)))

Ｌ１セルには次の式を入力して右横方向にドラッグコピーします。業種が表示されます。

=IF(COLUMN(A1)>MAX($K:$K)/10000,"",INDEX($D:$D,MATCH(COLUMN(A1)*10000+1,$K:$K,0)))

Ｌ2セルは空にしてＬ3セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(COUNTIF($K:$K,COLUMN(A1)*10000+ROW(A1))=0,"",INDEX($A:$A,MATCH(COLUMN(A1)*10000+ROW(A1),$K:$K,0)))

業種ごとに会社名が表示されます。
プルダウンに必要なデータにするためにさらに次の操作を行います。
Ｌ２セルから例えばＬ50セルまでを選択してから「数式」タブの「名前の定義」から「名前の定義」をクリックします。
表示の画面で名前の窓にはＬ1セルの名前が表示されているのでしたらそのままＯＫをクリックします。表示が無い場合にはＬ1セルの名前を入力します。
同様にＭ列から横の列についても「名前の定義」の操作を繰り返し行います。
以上でシート1での作業は終わります。

シート2のＡ列が業種でプルダウンができるようにするためにはＡ2セルからＡ50セルまでを選択してから「データ」タブの「データの入力規則」で入力値の種類では「リスト」を選択し元の値の窓には次の式を入力しＯＫします。

=INDIRECT("Sheet1!K1:AX1")

Ｂ列は会社名でＢ2セルからＢ50セルを選択してから上と同様に「データの入力規則」「リスト」で元の値の窓には次の式を入力してＯＫします。

=INDIRECT(A2)

電話番号のＥ列、ＦＡＸ番号のＦ列についてはＥ2セルに次の式を入力してからＦ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($A2="",$B2=""),"",INDEX(Sheet1!$B:$C,MATCH($A2&$B2,Sheet1!$J:$J,0),COLUMN(A1)))

Ｇ列の担当者についてはＡ列やＢ列のようにリスト表示のデータをシート1から直接取得することができませんのでシート2に作業列を設けてＧ列でプルダウンによる選択ができるようにします。
そのため例えばＪ2セルには次の式を入力してＬ2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",INDEX(Sheet1!$E:$G,MATCH($A2&$B2,Sheet1!$J:$J,0),COLUMN(A1)))

その後にＧ2セルからＧ50セルまでを選択してから「データの入力規則」「リスト」で元の値の窓には次の式を入力してＯＫします。

=J2:L2

お礼

早速のご回答ありがとうございます。
添付ファイルが縮小されてしまって見辛く申し訳ありませんでした。
記載して頂いた通りに入力したら希望通りのものができました。
また、業者と会社名が一致しない場合は電話番号等がエラーになるなどお願いしていない部分も意味合いを汲んで頂き恐縮です。
関数の意味は調べて分かりましたが組合せた場合の使い方が初級者の私には直ぐ理解できませんでしたので少しずつ理解していこうと思います。
半分諦めかけていたので助かりました。ありがとうございました。