【Excel】連動する2つのプルダウンをFILTER関数で作る｜名前の定義もINDIRECTも不要

1つ目のプルダウンで都道府県を選ぶと、2つ目のプルダウンにその都道府県の市区町村だけが出るようにしたいことがあります。

作り方をネットで検索すると、範囲に都道府県と同じ名前を付けて、INDIRECT関数で呼び出す方法がよく出てきます。実務では、名前を付けずにFILTER関数で作っています。この記事では、その方法を紹介します。

FILTER関数とUNIQUE関数は、Microsoft 365またはExcel 2021以降で使えます。

完成イメージ

入力用のシートのB1で都道府県を選ぶと、B2のプルダウンにその都道府県の市区町村だけが表示されます。

「データ」シートのA列に都道府県、B列に市区町村を入力し、テーブルにしておきます。テーブル名は「住所」にしました。

テーブルにしておくと、市区町村が増えたときも行を足すだけで、プルダウンの候補に入ります。テーブルの便利なところは、こちらの記事で紹介しています。

データのA列には、同じ都道府県が何度も並んでいます。そのままプルダウンにすると、京都府が何回も表示されてしまいます。

そこで、入力用のシートのY1に、UNIQUE関数で重複をなくした都道府県の一覧を出します。

=UNIQUE(住所[都道府県])

Y1から下に、京都府、大阪府、滋賀県が表示されます。

実務では、都道府県だけの列を別に用意しています。UNIQUE関数を使えば、その列を用意しなくても一覧を作れます。

次に、B1にプルダウンを設定します。

# は、Y1から表示されている範囲全体を指す書き方です。都道府県が増えて一覧が長くなっても、設定を直す必要はありません。

Z1に、B1で選んだ都道府県の市区町村だけをFILTER関数で出します。

=FILTER(住所[市区町村],住所[都道府県]=$B$1,"")

FILTER関数は、1つ目の引数に取り出す範囲、2つ目に条件を書きます。3つ目の "" は、該当するデータがないときに空白を表示するためのものです。B1が空のときに #CALC! エラーになるのを防げます。

B2にも、B1と同じ手順でプルダウンを設定し、［元の値］に =$Z$1# と入力します。

これで、B1で選んだ都道府県に合わせて、B2の候補が変わるようになります。

検索するとよく出てくる、範囲に名前を付けてINDIRECT関数で呼び出す方法と比べると、FILTER関数の方法には次のメリットとデメリットがあります。

	内容	理由
メリット	名前の定義がいらない	2つ目の候補をFILTER関数でデータから直接取り出すため
メリット	項目が増えても、行を追加するだけで済む	テーブルと `#` の参照で、候補の範囲が自動で広がるため
メリット	ブックが重くなりにくい	揮発性関数のINDIRECT関数を使わないため
デメリット	シートの隅に候補を表示しておく必要がある	プルダウンはFILTER関数の結果（Z列など）を参照するため
デメリット	1か所でしか使えない。行ごとにプルダウンを並べる場合はINDIRECT関数の方法を使う	候補を表示する場所がプルダウン1組分しかないため