Excelで連動するプルダウンリストの作り方/indirect関数の使い方

dominoes IT

たとえば東京都を先に選択しておくと東京の中の区・市だけが選択肢に現れるなど、他のセルに入力した値によってプルダウンリストの表示内容を変更する、プルダウンリスト同士を連動させる方法です。
その他プルダウン関連は別記事をご参照ください。また、以下記事では参照範囲をテーブル化して進めますが、特に拡張する予定などなければ普通のリストでも作れます。

Advertisements

成果物

今回の完成品のイメージです。
tblpull13
先にA市を選んでいればA市内の1,2,3町を、B市を選んでいればB市内の1,2,3町を選ぶことができます。市を入力していない場合はプルダウン自体が表示されません。

前準備

まずは先に参照するためのテーブルを作っていきます。

tblpull
一点注意事項として、「市」配下のA市、B市、C市は、それぞれ見出しのA市、B市、C市と一致する必要があります。


tblpull1
テーブルにする範囲を選択し、挿入タブ→「テーブル」をクリックします。


tblpull2
最初の行を見出しにするかどうかを選択し、OKをクリックします。


tblpull3
リストがテーブル化されますので、名称を変更します。


tblpull4
数式タブ→「名前の定義」をクリックします。


tblpull5
「City」テーブルの「市」列であれば=City[市]というように範囲を指定し、1列ずつ名前をつけていきます。ここではCity[市]にcity_mainと名前をつけています。


tblpull5.5
ただし、連動する(子の)プルダウンは、名前と見出しが一致するようにしてください。
※付けた名前は数式タブの「名前の管理」から確認・編集・削除が可能です。


tblpull6
プルダウンを表示する側の画面に移り、例でいう「市」を表示する範囲を選択して、データタブ→「データの入力規則」をクリックします。


tblpull7
入力値の種類を「リスト」、元の値に「=(設定した名称)」を入力します。たとえばここでは、=city_main(City[市]につけた名前)と入力しています。


tblpull9
まずは親のプルダウンが表示されるようになります。ここからが本題です。


プルダウンの連動の実装

tblpull10
今度は「子」のプルダウンを最初のひとつだけ選択し、データの入力規則で入力値の種類を「リスト」、元の値には=indirect(参照する親のセル)と入力します。ここでいうとA2セルの値により表示内容を変えるので、=indirect(A2)(A2文字列として参照)と入力します。


tblpull11
こんな感じで連動していれば成功です。


tblpull12
「子」のプルダウンを入力した欄を、下にオートフィルでコピーしていきます。


tblpull13
完成!



お疲れ様でした。一応、途中に出てきたindirectについてですが、

対象範囲を文字列として取得
=indirect(範囲)

もしこれがただの=A2とかですと、A2の内容をプルダウンの候補として表示するという意味になってしまいます。indirectを付けてあげることで内容を文字列として引っ張ってきて、=A市と入力したのと同じ状態を作り出している、という理屈となります。
連動するプルダウンについては以上です。

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント