今回は、Excelのドロップダウンと依存リストを使って、入力を選びやすくする方法を紹介します。
依存リストは前の選択に合わせて候補を変える仕組み
Excelのドロップダウンは、入力候補をリストから選ばせる機能です。部署名、商品区分、担当者、状態など、入力内容をそろえたい場面で役立ちます。さらに依存リストを使うと、前のセルで選んだ内容に応じて、次のセルの候補を変えられます。たとえば、部門を選ぶと、その部門に属する担当者だけが表示されるような形です。
依存リストを使う目的は、入力者に余計な候補を見せないことです。候補が多いドロップダウンは、探す手間がかかります。前の選択で候補を絞れば、入力ミスを減らし、選択も速くなります。選択肢を段階的に絞ることで、表の入力ルールを自然に守りやすくなります。
候補リストを別シートにまとめる
依存リストを作る前に、候補データを整理します。入力表の横に候補を直接置くこともできますが、長く使うブックでは、候補専用のシートを作るほうが管理しやすくなります。候補シートには、大分類、中分類、詳細項目のように、関係が分かる形で一覧を置きます。
候補データは、見た目よりも更新しやすさを優先します。たとえば、部署ごとに担当者名を横方向へ並べる方法もありますが、担当者の増減が多い場合は、部署名と担当者名を縦の一覧で持つほうが扱いやすいことがあります。後から追加する人が迷わないように、見出しを付け、空白行を混ぜないようにします。
- 候補専用シートを用意する
- 候補の見出し名を分かりやすくする
- 同じ候補を重複して登録しない
- 前後の空白を入れない
- 使わなくなった候補は削除せず、必要に応じて管理欄で区別する
候補リストを整える段階で、表記ゆれも直しておきます。「営業部」と「営業」、「管理部」と「管理」などが混ざると、依存リストの条件が分かりにくくなります。入力者に見せる名称と、管理上の名称をそろえることが大切です。
基本のドロップダウンを作る
最初に、大分類のドロップダウンを作ります。入力セルを選び、データの入力規則から「リスト」を選択します。元の値には、候補シートに用意した大分類の範囲を指定します。これで、セルをクリックしたときに候補を選べるようになります。
入力規則を複数行に適用する場合は、最初のセルで設定してから下方向へコピーします。テーブルとして管理している場合は、列全体に入力規則を設定しておくと、新しい行にも反映しやすくなります。候補範囲を固定で指定すると、候補を追加したときに範囲を広げる必要があります。長く使うなら、テーブルや名前定義を使い、候補の追加に対応しやすい形にします。
INDIRECTを使う方法の考え方
依存リストの代表的な作り方に、名前定義とINDIRECT関数を使う方法があります。大分類の名前と同じ名前の候補範囲を作り、次のドロップダウンでは、前のセルの値をINDIRECTで参照します。たとえば、A列で部署を選び、B列で担当者を選ぶ場合、B列の入力規則でA列の値に対応する候補範囲を呼び出す形です。
この方法は仕組みが分かると便利ですが、名前定義の作り方に注意が必要です。名前には使えない文字があります。部署名にスペースや記号が含まれていると、そのまま名前定義にできない場合があります。その場合は、候補名を管理用の短い名前に変える、または別の方法で候補を抽出する設計にします。
表示名と管理名を分ける
部署名や区分名に記号が多い場合は、入力者に見せる表示名と、名前定義に使う管理名を分けると安定します。表示名は分かりやすく、管理名は数式で扱いやすくします。ただし、管理名が増えると保守が必要になるため、候補シートに対応表を置き、どの表示名がどの管理名に対応するか分かるようにします。
新しい関数で候補を抽出する方法
Microsoft 365環境では、FILTER関数やUNIQUE関数を使って候補を作る方法もあります。候補一覧から、前のセルで選んだ分類に一致する項目だけを抽出し、その抽出結果をドロップダウンの元にします。この方法は、候補データを縦持ちで管理しやすい点が利点です。
たとえば、候補シートに「分類」と「項目」の列を作り、入力シートで選んだ分類に一致する項目だけをFILTER関数で表示します。その結果範囲を入力規則のリストに指定すれば、前の選択に合わせた候補を使えます。候補の重複を避けたい場合は、UNIQUE関数を組み合わせます。
関数で候補を作る場合は、出力先の空き範囲を確保します。抽出結果が広がる場所に別の値があると、候補が正しく表示されません。候補作成用の補助範囲を別シートにまとめ、入力者が触らないようにしておくと管理しやすくなります。
空白時の動きを決めておく
依存リストでは、前のセルが空白のときに次の候補をどうするかを決めておく必要があります。大分類が未選択なのに詳細項目を選べると、入力の順番が崩れます。空白時には候補を表示しない、または「先に分類を選択してください」という案内用の候補を出すなど、運用に合わせて決めます。
入力規則のエラーメッセージも役立ちます。無効な値が入力されたときに、短い案内を出しておけば、入力者は修正しやすくなります。メッセージは責める表現にせず、「分類を選択してから項目を選んでください」のように、次に行う操作を示します。
候補変更時の古い値に注意する
依存リストでよく起きる問題は、前のセルを変更しても、後ろのセルに古い選択値が残ることです。たとえば、部署を「営業部」から「管理部」に変えても、担当者欄に営業部の担当者名が残る場合があります。入力規則は新しい候補を制限できますが、既に入っている値を自動で消すとは限りません。
この問題を避けるには、運用ルールを決めます。分類を変更したら詳細項目を選び直す、確認用の列で不一致を検出する、条件付き書式で古い値を目立たせるなどの方法があります。マクロを使えば自動で消すこともできますが、共有環境ではマクロを使えない場合もあるため、まずは関数や条件付き書式で気づける仕組みを作ると扱いやすくなります。
- 分類セルを先に入力する
- 分類に応じた候補から項目を選ぶ
- 分類を変更したら項目も選び直す
- 不一致チェック列で古い値を確認する
- 候補シートの変更履歴を残す
候補リストは一度作って終わりではありません。担当者や区分が変わるたびに更新が必要です。候補を変更した日や変更者をメモしておくと、後で表の値を確認しやすくなります。
まとめ
Excelのドロップダウンと依存リストを使うと、前の選択に合わせて次の候補を絞り込めます。候補専用シートを作り、表記ゆれや空白を整えておくことが安定した運用につながります。名前定義とINDIRECTを使う方法、FILTER関数で抽出する方法など、環境に合う作り方を選びます。空白時の動きや分類変更後の古い値にも注意し、入力者が選び直しやすい仕組みにしておくと、管理表の品質を保ちやすくなります。