Excel約5分で読めます

Excelで連動するプルダウンを作る|大分類と小分類

Excelで連動するプルダウンを作るを表したイメージ
記事の内容を表したイメージ(AI生成)

「部署」を選んだら「担当者」の選択肢が自動で切り替わる、「都道府県」を選んだら「市区町村」が絞り込まれる——こうした連動するプルダウンを作りたい場面は多いですが、手順がわからず手入力に戻してしまうケースはよくあります。

この記事では、INDIRECT関数と名前付き範囲を組み合わせる方法で、大分類と小分類が連動するプルダウンを作る手順を説明します。一度設定すれば、大分類のセルで選んだ値に応じて小分類の選択肢が自動的に変わります。ExcelとGoogleスプレッドシートでは操作名や関数の対応が異なる部分があるため、この記事ではExcelを前提に進めます。画面の表示は版や設定により異なります。

作業前に必ずファイルをコピーして元のデータを保護してください。途中で手順を誤っても、コピー元に戻れるようにしてから始めると安心です。


連動プルダウンの仕組みを先に理解する

通常の入力規則(ドロップダウンリスト)は、選択肢の範囲を固定で指定します。連動プルダウンでは、この「選択肢の範囲」を可変にするために INDIRECT関数 を使います。

INDIRECT関数は、文字列として渡した名前や参照先を実際のセル範囲として解釈する関数です。たとえば「=INDIRECT("東京都")」と書くと、「東京都」という名前が付いた範囲を参照します。

この仕組みを利用して、大分類のセルに「東京都」と入力されていれば、小分類の選択肢として「東京都」という名前の範囲を自動で読み込む、という連動を実現します。

ポイントは「名前付き範囲の名前」と「大分類の選択肢」を一致させることです。「東京都」という選択肢を選んだら、小分類の選択肢リストにも「東京都」という名前が付いていなければなりません。


準備:選択肢リストを別シートに作る

まず、選択肢データを管理しやすい専用のシートに用意します。ここでは「リスト」という名前のシートを作成します。

架空の例です。

表は横にスクロールできます

A列(大分類)B列(東京都)C列(神奈川県)D列(埼玉県)
東京都新宿区横浜市さいたま市
神奈川県渋谷区川崎市川口市
埼玉県千代田区相模原市所沢市
(以下空白)中野区藤沢市越谷市

A列には大分類(都道府県)を縦に並べます。B列以降には各大分類に対応する小分類を、列ごとに縦に並べます。各列の1行目が大分類の名称、2行目以降が小分類の値です。

このレイアウトにすることで、後に各列を「名前付き範囲」として登録するときに見通しがよくなります。


名前付き範囲を登録する

各列の選択肢に名前を付けます。この名前が大分類の選択肢と一致している必要があります。

  1. 「リスト」シートで、B1からB5(東京都の選択肢が入っている範囲)を選択します。
  2. 数式バーの左にある「名前ボックス」(通常はセル番地が表示されている欄)をクリックして「東京都」と入力し、Enterキーを押します。
  3. 同様に、C列(横浜市〜藤沢市)を選択して「神奈川県」と名前を付けます。
  4. D列(さいたま市〜越谷市)を「埼玉県」と名前を付けます。

名前に使える文字の注意点: Excelの名前付き範囲では、スペースや一部の記号が使えません。大分類の選択肢に「北海道・東北」のような記号が含まれる場合は名前として登録できないため、大分類の選択肢を記号なしの文字列に変更するか、後述のSUBSTITUTE関数を使う対処が必要です。シンプルな文字列だけで構成するのが最も安全です。

名前付き範囲が正しく登録されているかは、「数式」タブの「名前の管理」(または「定義した名前」グループ)から確認できます。


入力規則を設定する:大分類と小分類の順に

準備ができたら、実際に入力を行うシートに戻り、入力規則を設定します。ここでは「入力」シートのB列に都道府県(大分類)、C列に市区町村(小分類)を選ぶ想定とします。

大分類のプルダウンを設定する

  1. B2セルを選択します(1行目は見出しとします)。
  2. 「データ」タブから「データの入力規則」を開きます。
  3. 「設定」タブの「入力値の種類」で「リスト」を選びます。
  4. 「元の値」に、リストシートのA列の範囲(例:=リスト!$A$1:$A$3)を指定します。
  5. OKをクリックします。

B列に複数行設定する場合は、B2に設定した後でB3以降へコピーできます。

小分類のプルダウンをINDIRECTで連動させる

  1. C2セルを選択します。
  2. 「データ」タブから「データの入力規則」を開きます。
  3. 「設定」タブの「入力値の種類」で「リスト」を選びます。
  4. 「元の値」に次の数式を入力します。
=INDIRECT(B2)
  1. 「エラーメッセージ」タブで、エラー時にメッセージを表示する設定になっている場合、B2が空白のときにエラーが出ることがあります。一時的に「停止」から「情報」や「警告」に変えておくか、B2を必ず先に選ぶ運用で対処します。
  2. OKをクリックします。

C2を設定したら、C3以降へコピーします。コピーするとINDIRECT内の参照(B2)が自動でB3、B4…とずれるため、各行で対応した大分類を参照します。


迷いやすい例外と失敗しやすい点

名前と選択肢が一致していないとき

B2で「東京都」を選んでも小分類のリストが空になる場合は、名前付き範囲の名前と大分類の選択肢の文字列が完全に一致しているかを確認します。全角・半角の違い、空白の混入が原因になりやすいです。「名前の管理」で名前を確認し、大分類の選択肢をそのままコピーして名前に使うと一致ミスを防げます。

大分類を変更したとき小分類に古い値が残る

B2を「東京都」から「埼玉県」に変えても、C2には前に選んだ「渋谷区」が残り続けます。小分類のセルは自動でクリアされないため、入力担当者が手動で選び直す必要があります。この挙動を想定した運用ルールを、シートに注意書きとして残しておくと引継ぎ時のトラブルを減らせます。VBAを使えば自動クリアも可能ですが、マクロを使わない構成で運用したい場合は注意書きによる周知が現実的です。

大分類の選択肢が増えたとき

新しい大分類(たとえば「千葉県」)が増えたら、リストシートに列を追加し、名前付き範囲を新たに登録し、大分類の入力規則の元の値範囲を広げる——この3つをセットで行う必要があります。どれか一つ忘れると動作がおかしくなるため、「選択肢を追加したときの更新チェックリスト」として手順をメモに残しておくと安定します。


次の行動

リストシートを作り、名前付き範囲を一つ登録して、C2に=INDIRECT(B2)を設定するところまで試してみてください。まず2〜3件のデータで動作を確認してから、実際の選択肢全体に広げると、設定ミスを早く見つけられます。大分類の選択肢を変えても小分類が切り替わらない場合は、「名前の管理」で名前と選択肢の文字列が一字一句一致しているかを最初に確認してください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ