【Excel 関数】連動プルダウンをXLOOKUPで進化させる — INDIRECT不要の実践パターン

この記事は約7分で読めます。

この記事で分かること

  • INDIRECT関数と名前定義を使わずに連動ドロップダウンを作る方法
  • FILTER + UNIQUE + スピル演算子（#）でリストを動的に生成する手順
  • 担当者を選んだ後、XLOOKUPで部署・電話番号などを自動入力する仕組みの作り方

「名前定義の管理が増えていく」「エリアが増えるたびに名前定義を追加している」という方に向けて書いています。

どんな場面で使うか

  • 1段目でエリアを選ぶと、2段目に担当者だけが出てくる入力フォームを作りたい
  • 担当者を選んだら部署や電話番号が自動で入力されるようにしたい
  • INDIRECT+名前定義の管理が煩雑で、エリアや担当者が増えるたびに設定を追加したくない
  • マスターシートを更新するだけで全ドロップダウンが自動更新される仕組みを作りたい

基本説明

この記事では、次のような「マスター」シートを例に使います。

A: 担当者名 B: エリア C: 部署 D: 内線番号
田中 東京 営業1課 1001
鈴木 東京 営業1課 1002
佐藤 大阪 営業2課 2001
山田 大阪 営業2課 2002
伊藤 名古屋 営業3課 3001
…

INDIRECT方式との比較

連動プルダウン（INDIRECT方式）では、名前定義を手動で作ってINDIRECTで参照する方法を使っています。

INDIRECT + 名前定義 FILTER + スピル + XLOOKUP
エリア追加時の対応 名前定義を新しく作る必要がある マスターに行を追加するだけ
担当者追加時の対応 名前定義の中のリストを編集する マスターに行を追加するだけ
特殊文字・スペース 名前定義が使えない（エラー） 問題なし
対応バージョン 全バージョン M365・Excel 2021以降

マスターに行を追加するだけですべてのドロップダウンが更新されるのがこの方法の最大のメリットです。

手順

1段目：エリアのドロップダウンリストを作る

マスターのB列（エリア）から重複なし・昇順のリストを生成します。

例: H2セルに入力（別シートや空いている場所に置く）

=SORT(UNIQUE(マスター!B2:B100))

このスピル結果を入力規則の元の値に指定します（=$H$2#）。

H2 の結果イメージ:

H列（H2から下に展開）
東京
大阪
名古屋

マスターに5行以上あっても、エリアの重複が除かれて3件だけになります。 （漢字の並び順は、データの入力方法＝ふりがな情報の有無によって変わることがあります）

2段目：選んだエリアの担当者リストを作る

G1セルに1段目（エリア）のドロップダウンを設置しているとします。G1の値に応じて担当者リストをFILTERで生成します。

例: I2セルに入力

=SORT(UNIQUE(FILTER(マスター!A2:A100, マスター!B2:B100=G1, "担当者なし")))
  1. FILTER(マスター!A2:A100, マスター!B2:B100=G1, "担当者なし") → G1で選んだエリアの担当者を抽出
  2. UNIQUE(...) → 重複を除く（マスターに同じ担当者が複数行ある場合の対策）
  3. SORT(...) → 昇順で並べる

このスピル結果を2段目のドロップダウンの元の値に指定します（=$I$2#）。

G1のエリアを変えるたびに、I2のスピルが更新され、2段目の選択肢が自動で切り替わります。

動作イメージ:

G1で選んだエリア G2の選択肢（I2のスピル）
東京 田中、鈴木
大阪 佐藤、山田
名古屋 伊藤

3段目：XLOOKUPで関連情報を自動入力する

G2セルに2段目（担当者）のドロップダウンを設置しているとします。担当者が選ばれたら、XLOOKUPでマスターから部署と内線番号を自動入力します。

部署（G3セルに入力）:

=IFERROR(XLOOKUP(G2, マスター!A2:A100, マスター!C2:C100), "")

内線番号（G4セルに入力）:

=IFERROR(XLOOKUP(G2, マスター!A2:A100, マスター!D2:D100), "")

G2で担当者を選ぶと、G3に部署、G4に内線番号が自動で表示されます。

動作イメージ（G2 =「田中」を選んだ場合）:

セル 表示される値
G3（部署） 営業1課
G4（内線番号） 1001

サンプル例

入力フォームの完成形

セルの配置例:

セル 役割 内容
G1 1段目ドロップダウン（エリア） 手動選択
G2 2段目ドロップダウン（担当者） 手動選択
G3 部署（自動入力） =IFERROR(XLOOKUP(G2, マスター!A2:A100, マスター!C2:C100), "")
G4 内線番号（自動入力） =IFERROR(XLOOKUP(G2, マスター!A2:A100, マスター!D2:D100), "")
H2 エリアリスト（スピル・非表示） =SORT(UNIQUE(マスター!B2:B100))
I2 担当者リスト（スピル・非表示） =SORT(UNIQUE(FILTER(マスター!A2:A100, マスター!B2:B100=G1, "担当者なし")))

スピル用のH列・I列は入力フォームとは別の場所（別シートや非表示列）に置くと見た目がすっきりします。

マスターに担当者を追加するときの操作

テーブル機能（Ctrl+T）をマスターシートに適用しておくと、テーブルの末尾に行を追加するだけで自動的にUNIQUE/FILTERの参照範囲が広がります（テーブルは マスター[担当者名] のような構造化参照が使えます）。

=SORT(UNIQUE(マスター[エリア]))
=SORT(UNIQUE(FILTER(マスター[担当者名], マスター[エリア]=G1, "担当者なし")))

テーブルを使うと範囲の末尾（B2:B100 など）を気にしなくてよくなります。

よくあるエラー

2段目が更新されない

G1（エリア）を変えてもI2のスピルが変わらない場合、I2の数式の参照先が G1 になっているか確認してください。また $G$1 と絶対参照にしてしまうと別の場所にコピーしたときにずれることがあります（この場合は意図どおりに $G$1 でも問題ありません）。

「担当者なし」と表示される

FILTER の条件に一致するレコードがマスターに存在しない場合です。G1で選んだエリアの表記がマスターのB列と完全一致しているか（全角・半角、スペースの有無）を確認してください。

#SPILL! が出る

H2またはI2のスピル先のセルに既存の値が入っています。スピル先を空にしてください。

XLOOKUP で "" が返ってくる

G2（担当者）が空欄のとき、XLOOKUPが空欄を検索してもマスターに一致がないため IFERROR の第2引数 "" が返ります。担当者が選ばれていない状態では空欄になるので、これは正常な動作です。

まとめ

  • SORT(UNIQUE(マスター列)) でエリアリストを自動生成 → # で入力規則に指定
  • SORT(UNIQUE(FILTER(担当者列, エリア列=G1, "..."))) で連動リストを生成 → # で2段目に指定
  • XLOOKUP(G2, 担当者列, 部署列) で選択後の関連情報を自動入力
  • マスターに行を追加するだけで全リストが自動更新される（名前定義の管理不要）

INDIRECT + 名前定義方式はバージョンを問わず使えますが、担当者やエリアが増えるたびに設定変更が必要です。M365・Excel 2021以降が使える環境では、この記事の方法でメンテナンスフリーな連動ドロップダウンが作れます。

対応バージョン: Microsoft 365 または Excel 2021 以降

関連記事

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

タイトルとURLをコピーしました