Excelで2段階連動ドロップダウンを作る｜2解法と子残り対処

Excel のドロップダウンリストは、親セルの選択値に応じて子セルの選択肢を絞り込めます。「都道府県を選ぶと市区町村だけが選べる」というやつです。作り方は INDIRECT 関数 + 名前定義 (全バージョン対応) と FILTER 関数 (Excel 2024 / Microsoft 365 / Excel 2021) の 2 通りで、互換性とメンテ性で選び分けます。

あわせて、親選択を変えたときに子の値が残るバグの対処マクロもまとめます。Excel 2024 / Microsoft 365 で縦持ちマスターを使えるなら h2-3 の FILTER 解法が運用しやすいです。Excel 2019 以前で動かす必要がある場合や、横持ちデータが既にある場合は h2-2 の INDIRECT 解法を選んでください。

INDIRECT を使った多段階ドロップダウンの作り方は他サイトでも紹介されています。本記事では Excel 2024 で使える FILTER 関数版と、親を変更したときに子の値が残る問題への対処までまとめます。

完成イメージ (動きで見る)

都道府県のドロップダウンを選ぶと、市区町村のドロップダウンの選択肢が連動して変わる動きです。さらに親を変更すると、Worksheet_Change マクロで子の値が自動でクリアされます。

3 段階版 (大カテゴリ → 中カテゴリ → 小カテゴリ) への拡張も同じ仕組みで作れます (h2-5 で解説)。

解法 1: INDIRECT + 名前定義 (全バージョン対応の定番)

横持ちデータ (列ごとに親の値をヘッダーにし、配下に子の選択肢を並べたもの) と INDIRECT 関数を組み合わせる定番手法です。Excel 2016 以降の全バージョンで動きます。

手順 1: 横持ちデータと名前定義

列ごとにヘッダー (親の値) + 配下に子の選択肢を並べたデータを準備します。

1. データ範囲 (ヘッダー含む) を選択 (下のスクリーンショットでは横持ちデータ A8:C11 を選択した状態)
2. [数式] タブ → [選択範囲から作成] をクリック
3. 「上端行」にチェック → [OK]

これで各列のヘッダーが名前として登録され、配下の選択肢がその名前の参照範囲になります。詳細は Microsoft Support の 数式内の名前 ページを参照してください。

手順 2: 親と子のドロップダウンを設定

親セル (例: A4) に都道府県のドロップダウン、子セル (例: B4) に市区町村のドロップダウンを設定します。

親 A4 の入力規則:

東京都,神奈川県,大阪府

子 B4 の入力規則「元の値」:

=INDIRECT($A4)

列を絶対参照 $A4 にするのがポイントです。これで A4 の値 (= 名前定義のキー) を参照する範囲が、子のドロップダウン候補になります。

手順 3: 動作の注意点

• 親 A4 が空のまま子 B4 の入力規則を設定しようとすると「リストの元の値が間違っています」エラーが出ます。先に親に有効値を入れてから子を設定してください
• 親値が名前定義に存在しないと、子のドロップダウンを開いた瞬間にエラーになります
• 名前定義は標準ではブック全体 (Workbook スコープ) で有効です

INDIRECT 関数の詳しい動作原理は post 1902 INDIRECT 関数を理解しよう を参照してください。INDIRECT の公式ドキュメントは Microsoft Support にあります。

動的拡張 (行追加で増えるリスト) と組み合わせたい場合は post 14661 動的に更新されるドロップダウン を参照してください。

解法 2: FILTER 関数版 (Excel 2024 / Microsoft 365 / Excel 2021)

縦持ちマスターと FILTER 関数を組み合わせる現代的な解法です。データを縦持ちで運用できるので、市区町村の追加・削除がしやすく、3 段階以上に拡張しやすいのが利点です。

手順 1: 縦持ちマスターをテーブル化

都道府県と市区町村を 2 列で並べた縦持ちマスターを準備し、Ctrl + T でテーブル化してテーブル名を マスター にします。

手順 2: 親候補と子候補をスピルで作る

別のシート (またはマスターの右側) に親候補と子候補をスピルで展開します。

親候補 (例: D8 セル):

=SORT(UNIQUE(マスター[都道府県]))

子候補 (例: E8 セル、A4 = 親セルに依存):

=SORT(UNIQUE(FILTER(マスター[市区町村], マスター[都道府県]=$A$4)))

UNIQUE で重複を除外し、SORT で並び替えています。FILTER の詳細は Microsoft Support の FILTER 関数 ページを参照してください。

手順 3: 入力規則にスピル参照を渡す

親 A4 の入力規則「元の値」:

=$D$8#

子 B4 の入力規則「元の値」:

=$E$8#

末尾の # がスピル範囲演算子で、スピルの長さに合わせて自動で範囲が伸び縮みします。マスターに新しい都道府県や市区町村を追加すると、ドロップダウンの選択肢にも自動で反映されます。

=IFERROR(SORT(UNIQUE(FILTER(マスター[市区町村], マスター[都道府県]=$A$4))),"")

FILTER 関数は Microsoft 365 / Excel 2024 / Excel 2021 でのみ使えます。Excel 2019 以前は #NAME? になるので、その場合は h2-2 の INDIRECT 解法を選んでください。

親選択を変えると子の値が残るバグの回避 (Worksheet_Change マクロ)

連動ドロップダウンを作ったあと、こんな現象に気づくことがあります。

1. 親 A4 で「東京都」を選択
2. 子 B4 で「世田谷区」を選択
3. 親 A4 を「神奈川県」に変更
4. → 子 B4 に「世田谷区」が残ったまま (神奈川県には世田谷区はない)

これは Excel の入力規則の仕様です。入力規則は「現在の入力時点」しか検証しないので、親変更時に子の値を再検証する機能はありません。Excel 2024 build 19929 で確認したところ、親を変更しても子セルの Validation の式 (=INDIRECT($A4)) はそのまま維持されますが、現在値の再チェックは行われません。

解決: シートに Worksheet_Change マクロを追加する

親列を監視して、変更があったら子セルを自動でクリアするマクロを追加します。シートタブを右クリック → [コードの表示] → 開いたコードウィンドウに次のコードを貼り付けてください。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim changed As Range
    Set changed = Intersect(Target, Me.Range("A2:A100"))
    If changed Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False
    changed.Offset(0, 1).ClearContents

CleanUp:
    Application.EnableEvents = True
End Sub

コードの解説

• Intersect(Target, Me.Range("A2:A100")) で、変更されたセルと監視範囲 (A2 〜 A100) の交差を取得します
• 監視範囲外の変更なら Exit Sub で何もせず終了
• changed.Offset(0, 1).ClearContents で監視範囲の右隣 (B 列の対応セル) だけをクリアします
• On Error GoTo CleanUp で例外時も EnableEvents = True に確実に戻します
• マクロ内の ClearContents が再度 Worksheet_Change を呼ばないよう、Application.EnableEvents = False で抑制します

3 段階以上に拡張する

「都道府県 → 市区町村 → 町丁目」のように 3 段階の連動ドロップダウンも作れます。INDIRECT 版と FILTER 版で考え方が違います。

INDIRECT 版: 名前を _ で連結する

親値と中カテゴリ値を _ で連結した名前を作り、それを参照します。

名前定義の例:

• 名前 東京都 の参照範囲 = 「新宿区, 世田谷区, 港区」
• 名前 東京都_新宿区 の参照範囲 = 「歌舞伎町, 西新宿」
• 名前 東京都_世田谷区 の参照範囲 = 「三軒茶屋, 下北沢」

孫セル (C5) の入力規則「元の値」:

=INDIRECT($A5 & "_" & $B5)

組み合わせが多いと名前定義の数が爆発的に増えるので、規模が大きくなったら FILTER 版を検討してください。

FILTER 版: 第 2 引数に * 演算子で AND 条件を加える

FILTER の include 引数に AND 条件を渡すには、* 演算子を使って要素ごとに掛け算します。

孫セル (C5) のスピル元 (例: G20):

=SORT(UNIQUE(FILTER(マスター[町丁目], (マスター[都道府県]=$A$5)*(マスター[市区町村]=$B$5))))

入力規則「元の値」は =$G$20# のようにスピル参照で渡します。

× =FILTER(マスター[町丁目], AND(マスター[都道府県]=$A$5, マスター[市区町村]=$B$5))
   → #CALC! (ERROR.TYPE=14)

○ =FILTER(マスター[町丁目], (マスター[都道府県]=$A$5)*(マスター[市区町村]=$B$5))
   → 正常スピル

互換性と運用コストの比較表

選び方の目安:

• Excel 2019 以前で動かす必要がある → INDIRECT 一択 (FILTER は使えない)
• Excel 2021 以降 + 縦持ちマスターを既に持っている → FILTER がおすすめ
• 横持ちデータが既にある → INDIRECT が手早い

親変更時の子残り対処は、両解法とも同じ Worksheet_Change マクロで OK です (h2-4)。

うまく動かないとき + まとめ

• ドロップダウン自体が出ない → post 32078 ドロップダウンが表示されないとき
• リストの末尾に空白行が出る → 空白行を消す方法 (B-3)
• 「制限を満たしていません」エラー → post 31730 制限を満たしていません
• 動的拡張 (行追加で増えるリスト) と組み合わせたい → post 14661 動的に更新されるドロップダウン
• INDIRECT 関数の動作原理 → post 1902 INDIRECT 関数を理解しよう

まとめ

• 2 解法のおすすめ: Excel 2021 以降なら FILTER (h2-3)、それ以前なら INDIRECT (h2-2)
• 子残りバグは Worksheet_Change マクロ で一発解決 (h2-4)。Delete キーでの一括削除では拾わないことがある点だけ注意
• 3 段階以上に広げるなら、INDIRECT は名前を _ で連結、FILTER は * 演算子で AND 条件 (h2-5)。AND 関数を使うと #CALC! になる点に注意

配布ブック (xlsm)

シート構成:

• マスター: 縦持ちマスター (都道府県 / 市区町村 / 町丁目、テーブル名「マスター」)
• INDIRECT解法: マクロあり完成版 (Worksheet_Change で子残り対処)
• FILTER解法: マクロあり完成版 (スピル参照 + 改良マクロ)
• マクロなし版: VBA を使わずに INDIRECT / FILTER 両方を併置 (子残りは手動でクリア)
• 3段階拡張: INDIRECT 名前合成 + FILTER の * 演算子
• 罠デモ: CreateNames 自動 _ 置換、親値表記ゆれ判定、Worksheet_Change なしでの子残り再現

ダウンロードしたファイルを開くと「保護ビュー」が表示されます。発信元を確認のうえ、必要に応じて「マクロを有効にする」をクリックしてください。プロパティで「ブロック解除」が必要なケースもあります。