Excel:二つのドロップダウンリストを連携する方法

Office製品

 Excelで、セルをクリックするとリストが表示されるという『ドロップダウンリスト』機能はとても便利です。

 ところで、項目と細目のリストがあり、一つの項目には複数の細目がある。項目をドロップダウンリスト』から選択すると、それに連動して細目選択の『ドロップダウンリスト』が変化する。

 今日は、そんな『ドロップダウンリスト』の作り方を紹介します。
 下のGIFアニメがサンプルです。



 上のGIFアニメでは、[項目]の中から[Kalafina]を選択し、[細目]リストの中からメンバーの[Wakana]を選択しています。[項目]の選択によって[細目]のリストが変化しています。

 この方法は、家計簿などをつくる場合にとても有効です。

1.「項目・細目」表をつくる

 最初に、[Sheet1]に項目・細目の表をつくります。(項目数30、細目数23の場合で説明します)
 下の画像のような感じ。



 A列には、セルA4から数字を入力しておきます。セルA4 ⇒ [1]、セルA5 ⇒ [2]、・・・セルA33 ⇒ [30]。
 次に、『項目』に名前を付けて登録します。(名前を付けなくてもできますが、名前を付けるとデバック作業がとても楽になります。)
① 項目のデータを入力するセル[B4]~[B33]を選択。 
② [数式]タブ ⇒ [定義された名前]グループの[名前の管理] ⇒ [新規作成]   [名前]の欄に『項目』と入力し、[OK]をクリック。
 次に、『細目』に名前を付けます。操作は上と同様です。
① 細目のデータを入力するセル[C4]~[Y4]を選択。
② [数式]タブ ⇒ [定義された名前]グループの[名前の管理] ⇒ [新規作成]   [名前]の欄に『細目1』と入力
③ 同様に、[C5:Y5]を選択し、名前を『細目2』とする。以下、細目30まで設定する。
 A列の数字と細目の番号が一致しています。

2.ドロップダウンメニューの設定

 [Sheet2]を開き、下図のような表をつくります。

① セルC4をクリック。
② [データ]タブ ⇒ [データツール]グループの[データの入力規則] ⇒ [データの入力規則]をクリック。
③ 開いたダイアログで、[入力値の種類]で[リスト]を選択。
④ [元の値]の欄に[=項目]と入力。
⑤ [OK]をクリックしてダイアログ閉じる。



 セルC4の枠の右下をクリックしたまま下にドローして、この設定をコピーします。コピー範囲は好きなだけ。

 次に、セルA4、及びセルB4に以下の式を入力します。



 これは、セルC4で選択された値を[Sheet1]のリストの細目の名前と関係づける式です。
 これも、下方にドラッグして必要なだけコピーします。
 次に、二つ目のドロップダウンメニューの設定です。
 セルD4に設定します。やり方はセルC4と同じです。
 データの入力規則ダイアログで以下のように入力します。



 設定後は、前回と同様に、セルD4の設定をセル枠をドラッグして下方にコピーします。

 [元の値]で[INDIRECT]関数を使っているところがコツです。参照している[セルB4]には[細目1]と表示されているのですが、この関数を使わないと「名前」を認識しないのでドロップダウンメニューになりません。

 作り方は以上です。
 この説明で分からない方のために、このエクセルブックを提供します。ダウンロードして自由にお使い下さい。
  ファイル名:pulldownlist -UP.xls
  ダウンロードurl: こちらからDL
  ダウンロードパスワード:  cesar2017

 管理人はこのような方法を採りましたが、Excelには「正解はない」ので、やり方はたくさんあります。
 生年月日や内容は、細目をキーにMatch関数とIndex関数を組み合わせてデータベースから抽出するのがオーソドックスでしょう。

 Excel上級者の方は、ほとんどを[名前]登録して、とてもすっきりしたものを作ります。でも、素人が見るとさっぱり分からない(笑)。