Excel使いこなしの基礎と応用


複数のドロップダウンリストを連動させる

データの入力規則「リスト」設定の応用編「連動するドロップダウンリスト」です。
連動するドロップダウンリストの表示イメージ
上図のように、B列セルで選んだ値に従ってC列セルのリストを表示させ、そこで選んだ値から更にD列セルのリストを表示させるという連動型複数段階式の設定方法を図解していきます。セル内容を参照するINDIRECT関数を使いますが、操作は簡単です。

スポンサーリンク

目次


リスト項目を作り、名前を定義する

リスト項目用のシートにリスト(1)とリスト(2)に表示させる選択肢を下図のような表にまとめて作成しました。
連動するドロップダウンリストのリスト項目表1
「軽食」「デザート」「ドリンク」のセル範囲(B2:D2)を選択して「名前ボックス」に名前を入力します。
名前ボックスにリスト項目の名前を入力
最初の選択肢であるリスト(1)の名前は「メニュー」としました。

表から簡単に名前を定義できる

次にリスト(2)のドロップダウンリストに表示させる選択肢の名前を定義しますが、これは一括で行えます。

表全体(B2:D5)を選択します。
表全体を選択
「数式」タブの「定義された名前」グループにある「選択範囲から作成」をクリック
※下の画像をクリックで拡大表示
数式タブの「選択範囲から作成」ボタン
表示されたボックスの「上端行」にチェックを入れて「OK」します。
「選択範囲から作成」ボックスで「上端行」チェック
この時点でリスト(1)とリスト(2)の名前が全て定義されました。「セルの名前を定義する」についての詳細

リスト(3)はそれぞれ別表を作る必要があります。

「軽食」配下のリスト項目
「軽食」に連動させるリスト項目
「デザート」配下のリスト項目
「デザート」に連動させるリスト項目
「ドリンク」配下のリスト項目
「ドリンク」に連動させるリスト項目
リスト(2)に並ぶ選択肢の名称とリスト(3)各表の「上端行」セルの名称は完全に一致させましょう。後の操作は前章「表から簡単に名前を定義できる」と同じです。

1.表全体を選択 ⇒ 2.「数式」タブの「選択範囲から作成」をクリック ⇒ 3.表示されたボックスの「上端行」をチェック

この操作を3つの表で繰り返したら、リスト項目用のシートでの作業は完了です。

データの入力規則を設定

実際にデータを入力していくシートに切り替えます。

リスト(1)をドロップダウンで表示させる最初のセル(サンプルではB2)を選択し、「データ」タブの「データの入力規則」アイコンをクリックします。
セルを選択し、データの入力規則ボタンをクリック
「データの入力規則」ダイアログボックスが開いたら、「設定」⇒「リスト」で「元の値」のテキストボックスに前章でリスト(1)に付けた名前「=メニュー」を入れます。この操作の詳細はドロップダウンリストの簡単設定へ
データの入力規則ダイアログボックスにリスト名を入力
セルの右横に▼が現れたら、Altキー+↓キーでドロップダウンリストが表示されることを確認しておきます。
メニューのドロップダウンリスト表示確認

INDIRECT関数で連動させる

さて、ここからがこのページの重要ポイントです。

「B2」セルで「軽食・デザート・ドリンク」の3つの選択肢から一つを選んで入力した場合、「C2」セルでは「B2」セルで選ばれた文字列に紐付くリスト(2)をドロップダウンで表示させる設定をします。

即ち、B2で「軽食」を選んだら、C2では「パスタ・サンドイッチ・グラタン」から選んで入力できるようにするというわけです。

「C2」セルを選択し、先ほどの「B2」セルの時と同様に「データの入力規則」ダイアログボックスを開き、「設定」の「入力値の種類」を「リスト」にします。

そして「元の値」のテキストボックスに「=INDIRECT(B2)」と入力します。
データの入力規則ダイアログボックスに=INDIRECT(B2)を入力
「OK」を押すと、↓こんなエラー画面が出ますが、構わずEnter押し。
INDIRECT関数入力のエラー画面
INDIRECT関数についてのExcelヘルプの説明は

指定される文字列への参照を返します。セル参照はすぐに計算され、結果としてセルの内容が表示されます。

となっています。

=INDIRECT(B2)」とは「()内に指定されたセル(ここではB2)の内容(ここでは設定した入力規則)を参照して、現在選択されているC2セルに反映させますよ」ということです。

試しにB2セルのリストで「軽食」を選び、C2セルのドロップダウンリストに「パスタ・サンドイッチ・グラタン」が表示されるか確認しておきます。
メニューのドロップダウンリスト表示確認
「軽食」のドロップダウンリスト表示確認

次は「D2」セルを選択し、上記と同様に「データの入力規則」ダイアログボックスの「元の値」に今度は=INDIRECT(C2)」と入力します。データの入力規則ダイアログボックスに=INDIRECT(C2)を入力
これも「C2セルの内容を参照してD2セルに反映させる」ということです。

B2セルのリストで「軽食」を、C2セルのリストで「パスタ」を選んで、D2セルに「ミートソース・カルボナーラ・ボンゴレ」が表示されるか確認しておきます。
「パスタ」のドロップダウンリスト表示確認

データの入力規則をオートフィルでコピー

3つのセルに連動したドロップダウンリストがちゃんと表示されることを確認したら、入力した文字列は一旦削除しておきます。

スポンサーリンク

B2、C2、D2それぞれのセルのフィルハンドル(アクティブになったセルの右下角の突起)を下へドラッグして、同列のセル全てに入力規則をコピーします。これはオートフィルという連続コピー技です。
入力規則をオートフィルでコピー
これで3行目以降の全てのセルにドロップダウンリストが表示されるようになります。
ドロップダウンリストの表示確認
▶データの入力規則の基本設定
▶入力規則ドロップダウンリストの簡単設定

Officeで作れるもの