条件でリストが変わる連動Excelプルダウンの作り方【カテゴリ分けも自動反映】

エクセルの連動ドロップダウンリスト/プルダウン（多段階リスト）は業務効率を大きく高める機能ですが、構築手法によってメンテナンスの負荷が大きく変わります。

この記事では、連動リストの基本的な作り方から、運用コストを最小化する「テーブル機能」の活用法まで 解説します。データの追加だけで運用が完結する環境を構築できるようになります。

エクセルの連動ドロップダウンリストを構築する構成は、大きく2種類あります。一般的によく紹介されている基本的な構成と、テーブルを活用してメンテナンス性を高めた構成の2つです。

2つの構成の概要は次のとおりです。

参照するリストは同一シートだけでなく、別シートの場合でも問題なく動作します。

連動ドロップダウンリストを作るには、「データの入力規則」 と 「INDIRECT関数」 の役割を理解しておく必要があります。ここでは、この2つの基本機能について説明します。

Excelのドロップダウンリストは、「 データの入力規則 」機能を使って実現します。

「 データの入力規則 」機能を使うと、セルに選択肢を表示させることができます。設定は「データ」タブの「データの入力規則」から行い、入力値の種類を「リスト」に指定することで有効になります。

» ドロップダウンリストの作り方の基本はコチラ

INDIRECT関数 は、文字列を「参照先」として動的に切り替えるための関数で、連動リストを実現する上で欠かせない役割を担います。

INDIRECT関数を使うと、セルに入力された文字列をもとに参照先を動的に切り替えられます。

連動リストでは、以下の2つの目的で活用します。

• 1段階目のセルの値をもとに、対応する 「 名前の定義 」を動的に呼び出す
• 入力規則では直接扱えない 「 テーブルの参照式（構造化参照） 」を正しく認識させる

「 名前の定義 」と「 INDIRECT関数 」を組み合わせて連動ドロップダウンリストを作成する方法を説明します。連動リストの作成手順としてよく知られている方法ですが、運用上の課題があります。

構成Aの構築手順は以下の4ステップです。

この手順により、G2 に「営業部」と入力されるとINDIRECT関数が「営業部」という名前の範囲を参照し、対応する担当者リストが表示されます。

構成Aの課題は、「 リストの項目やカテゴリを追加するたびに、名前の定義を手作業で修正する必要がある 」点です。

たとえば担当者を1名追加した場合、名前の定義 で参照している範囲に新しいセルが含まれないため、「 名前の管理 」から手作業で範囲を修正する必要があります。カテゴリ（部署）を追加する場合も、新たに 名前の定義 が必要です。

参照するデータを テーブル化 し 「INDIRECT関数」と組み合わせて、項目の追加が自動反映される連動ドロップダウンリストを作成する方法を説明します。

この構成のメリットは、「 項目やカテゴリが増えるたびに範囲を修正するのが面倒 」という課題を解消できる点です。「テーブル機能」を使うことで、リストの末尾にデータを追加するだけでドロップダウンリストの選択肢が自動的に更新されるようになります。

» テーブル機能の詳細はコチラ

構成Bの構築手順は以下の4ステップです。

構成Bでは、テーブルにデータや列を追加するだけでドロップダウンリストに自動反映されます。 設定を変更する手間は不要です。

データの追加方法はシンプルです。

• 担当者（項目）を追加する場合 ：テーブルの対象列の最終行の下にデータを入力するだけで、ドロップダウンリストに自動反映されます
• 部署（カテゴリ）を追加する場合 ：テーブルに新しい列を追加するだけです。その列に名前の定義を設定するだけです

構成Bでは、ドロップダウンリストの中に 空白の選択肢 が表示される場合があります。すべてのカテゴリを1つのテーブルに格納している仕組み上、担当者の人数が少ないカテゴリの列には空白セルが生じてしまうためです。

空白を回避したい場合は、カテゴリごとに テーブルを分割 し、各テーブルに名前設定し、INDIRECT関数の参照先を書き換える対応が必要になります。（実は、テーブル設計の観点ではテーブルを分割した方が理にかなっています。テーブルを1つにまとめる場合、「1つのレコードに1セットのデータを持つ」という原則を無視することになるためです）

ただしテーブルを分割すると、カテゴリが増減するたびにテーブルの追加・削除と入力規則の再設定が必要となり、手間になります。運用のしやすさを優先するなら、空白が入ることを許容したうえで1つのテーブルにまとめる構成の方が扱いやすいでしょう。

参照データを 整然データ（正規化されたデータ） 形式で管理したい場合は、Power Query（パワークエリ） を使って連動リスト向けの形式に変換する方法が有効です。

データベース設計の観点では、再利用性の高いマスタとして機能させるために、リストのデータを「整然データ」形式（1列目に属性、2列目に値がある縦長の形式）で管理することが理想的です。

ただしこの形式は、そのままエクセルの連動リストに使いにくいという欠点があります。そこで Power Query（パワークエリ） を使って横長の形式に変換する方法が有効です。

» Power Queryについての詳細はコチラ

3段階以上の連動ドロップダウンリストは、構成Bの設定をカテゴリ階層の数だけ繰り返すことで実現できます。 「大分類 ＞ 中分類 ＞ 小分類」のように階層が深くなる場合も、構成Bの考え方を応用できます。

具体的には、中分類・小分類ごとにテーブルと 名前の定義 を用意し、入力規則で同様の INDIRECT関数 を設定します。階層が増えるほどテーブルの数は増えますが、各段階の設定ロジックは共通です。

構成としては基本的に 構成B（テーブル活用形） がオススメです。項目やカテゴリの追加がデータ入力だけで完結し、運用コストが抑えられます。

状況によっては構成を変えた方が適している場合もあります。

構成B（テーブル分割ver）は空白を排除できる反面、カテゴリが増減するたびに再設定が必要になります。

構成B ＋ Power Queryはデータ管理の再利用性が高まる一方、Power Query の設定が別途必要です。

それぞれのメリットと手間を天秤にかけて、運用環境に合った構成を選んでください。