Excelで連動するプルダウンの作り方｜INDIRECT関数で2段階リストを作る
本ページはプロモーションが含まれています
Excelで「1つ目のプルダウンで選んだ内容によって、2つ目のプルダウンの選択肢が変わる」ようにするには、選択肢の範囲に「名前」を付け、2つ目のプルダウンの元の値に =INDIRECT(1つ目のセル) を指定するのが基本の方法です。関数は1つだけで、慣れれば5分ほどで作れます。この記事では、例を使って手順を順番に解説し、うまく動かないときの直し方も紹介します。
目次
連動プルダウンのしくみ
例として、「部署」を選ぶと「担当者」の選択肢が変わるリストを作ります。
| 1つ目（部署） | 2つ目（担当者）の選択肢 |
|---|---|
| 営業 | 佐藤、鈴木、高橋 |
| 総務 | 田中、伊藤 |
| 経理 | 渡辺、山本、中村 |
しくみは次のとおりです。
- 担当者の一覧の範囲に、部署名と同じ「名前」を付けておく（例: 「営業」という名前の範囲＝佐藤・鈴木・高橋）
- 2つ目のプルダウンで
=INDIRECT(A2)と指定する - INDIRECT関数は「セルに入っている文字を、範囲の名前として読みかえる」関数なので、A2が「営業」なら「営業」という名前の範囲がリストになる
つまり、「1つ目で選んだ文字」と「範囲の名前」が完全に一致していることがポイントです。
手順1：選択肢の一覧を作る
まず、別のシート（例: 「リスト」シート）に選択肢を並べます。入力用のシートと分けておくと、あとで管理しやすくなります。
| A | B | C | |
|---|---|---|---|
| 1 | 営業 | 総務 | 経理 |
| 2 | 佐藤 | 田中 | 渡辺 |
| 3 | 鈴木 | 伊藤 | 山本 |
| 4 | 高橋 | 中村 |
- 1行目に部署名（1つ目の選択肢）
- その下に担当者名（2つ目の選択肢）
という形で、部署ごとに1列ずつ並べます。
手順2：範囲に名前を付ける
担当者の範囲に、部署名と同じ名前を付けます。1つずつ付けることもできますが、「選択範囲から作成」を使うとまとめて付けられます。
- 「リスト」シートで、A1からC4までを選択する
- 「数式」タブ →「選択範囲から作成」をクリック
- 「上端行」だけにチェックを入れて「OK」
これで、1行目の文字（営業・総務・経理）を名前にして、その下の範囲に名前が付きます。「数式」タブ →「名前の管理」を開くと、付いた名前を確認できます。
空白セルが範囲に入る場合
上の例では「総務」の列は2人しかいないため、名前の範囲に空白セルが含まれ、プルダウンの最後に空欄が出ることがあります。気になる場合は「名前の管理」で総務の範囲をB2:B3に直してください。
部署名の一覧（1つ目の選択肢）にも、名前を付けておくと便利です。A1:C1を選択し、画面左上の「名前ボックス」に「部署」と入力してEnterキーを押します。
手順3：1つ目のプルダウンを作る
入力用のシートに戻って、1つ目のプルダウンを作ります。
- 部署を選ばせたいセル（例: A2）を選択する
- 「データ」タブ →「データの入力規則」をクリック
- 「入力値の種類」で「リスト」を選ぶ
- 「元の値」に
=部署と入力する（名前を付けていない場合は=リスト!$A$1:$C$1） - 「OK」をクリック
手順4：2つ目のプルダウンをINDIRECTで作る
- 担当者を選ばせたいセル（例: B2）を選択する
- 「データ」タブ →「データの入力規則」をクリック
- 「入力値の種類」で「リスト」を選ぶ
- 「元の値」に
=INDIRECT($A2)と入力する - 「OK」をクリック
A2がまだ空の状態だと、「元の値はエラーと判断されます」という確認が出ることがあります。この場合はそのまま「はい」を選べば大丈夫です。A2で部署を選ぶと、B2の選択肢が切り替わります。
下の行にもコピーするとき
$A2 のように列だけに$を付けておくと、B2をコピーして下の行に貼り付けたとき、B3はA3、B4はA4…と、同じ行の部署を参照してくれます。
うまく動かないときの原因と対処
| 症状 | よくある原因 | 対処 |
|---|---|---|
| 2つ目のリストが空になる・エラーになる | 1つ目で選んだ文字と、範囲の名前が一致していない | 「名前の管理」で名前の綴りを確認する。前後の余分なスペースも原因になる |
| 名前が付けられない | 名前に使えない文字や形が含まれている | 名前はスペースを含められず、数字から始めることもできない。「1課」なら「第1課」のように変える |
| 選択肢の最後に空欄が出る | 名前の範囲に空白セルが含まれている | 名前の範囲を実際のデータだけに直す |
| 1つ目を変えても2つ目に古い値が残る | 入力規則は、すでに入っている値までは消さない | 1つ目を変えたら2つ目も選び直す運用にする |
| 選択肢を増やしても反映されない | 名前の範囲が古いまま | 「名前の管理」で範囲を広げるか、一覧をテーブルにしておく |
特に多いのが名前と選択肢の不一致です。名前に使えない文字（スペースなど）を含む項目は、INDIRECT関数で直接読みかえられません。項目名を変えられない場合は、項目名と名前の対応表を作ってVLOOKUPなどで変換する方法もありますが、まずは項目名を名前のルールに合わせるのがいちばん簡単です。VLOOKUPでエラーが出たときは、VLOOKUPで#N/Aが出る原因と直し方も参考にしてください。
Microsoft 365で使える別の作り方
Microsoft 365やExcel 2021以降など、UNIQUE関数やFILTER関数が使えるバージョンでは、名前を付けずに作る方法もあります。
- 「部署」と「担当者」を2列で並べた表を用意する（例: 部署がE列、担当者がF列）
- 空いているセル（例: H2）に
=FILTER(F2:F20, E2:E20=A2)と入力する。A2で選んだ部署の担当者だけが、下のセルに自動で並ぶ - 2つ目のプルダウンの「元の値」に
=$H$2#と入力する（#は、自動で広がった範囲全体を指す記号）
この方法は、表に行を足すだけで選択肢が増やせるのが利点です。ただし、上の例は1行分（A2）にしか対応しないため、何行も連動させたい場合はINDIRECTの方法のほうが作りやすいです。お使いのExcelでFILTER関数が使えるかは、関数を入力してみて確認できます。
同じリストに重複した項目があって困るときは、Excelで重複を削除する関数の使い方で紹介しているUNIQUE関数で、重複のない一覧を作ってから使うと便利です。
まとめ
- 連動プルダウンは「範囲に名前を付ける」＋「2つ目の元の値に
=INDIRECT(1つ目のセル)」で作れる - 名前は「数式」タブ →「選択範囲から作成」でまとめて付けられる
- 動かないときは、まず1つ目の選択肢と範囲の名前が完全に一致しているかを確認する
- 名前にはスペースが使えず、数字から始めることもできない
- Microsoft 365などではFILTER関数とスピル範囲（
#）を使う方法もある
最初は小さな表で試し、動くことを確認してから実際の業務の表に広げると、つまずきにくくなります。