Excel：INDIRECT関数不要！XLOOKUPで作る2段階ドロップダウンリスト徹底解説

今回は、ExcelでINDIRECT関数を使わずに、「2段階ドロップダウンリスト」を作成する方法を詳しく解説します。

INDIRECT関数不要！XLOOKUPで作る2段階ドロップダウンリスト徹底解説

2段階ドロップダウンリストとは？

ご存知の方も多いと思いますが、2段階ドロップダウンリストとは、1つ目のドロップダウンリストで項目を選択すると、その選択内容に応じて2つ目のドロップダウンリストの選択肢が自動的に切り替わる仕組みのことです。

概要ランにダウンロード資料も用意しているので、一緒に操作しながら学べますので、仕事の効率が グンと上がる ので、ぜひ最後までご覧ください✨

例えば、

• 1つ目のリストで「経理課」を選択 → 2つ目のリストには経理課のメンバー名が表示される
• 1つ目のリストで「営業課」に変更 → 2つ目のリストは営業課のメンバー名に自動で変更される

このような連動するリストを作成できます。従来はINDIRECT関数を使用して作成するのが一般的でしたが、今回は、XLOOKUP関数を活用します。

それでは、早速具体的な手順を見ていきましょう。

---

ドロップダウンの元データをテーブル化する

まず、ドロップダウンリストの選択肢となる元データを用意し、これをExcelの「テーブル」機能を使ってテーブル形式に変換します。

テーブルへの変換手順

1. 用意したデータ範囲（見出し行から最終データまで）をすべて選択します。
2. Excelのリボンメニューから「挿入」タブをクリックし、「テーブル」を選択します。
3. 「テーブルの作成」ダイアログボックスが表示されます。
   • データ範囲が正しく選択されていることを確認します。
   • 「先頭行をテーブルの見出しとして使用する」に必ずチェックが入っていることを確認し、「OK」ボタンをクリックします。

これで、選択した範囲がテーブルに変換されます。テーブルにすると、水色や白の交互の縞模様など、自動的にスタイルが適用されます。

テーブル名の変更 (任意)

テーブル化すると自動的に「テーブル1」のような名前が付きますが、後々の管理や数式での参照をしやすくするために、分かりやすい名前に変更しておくことをお勧めします。

1. テーブル内のいずれかのセルを選択します。
2. リボンメニューに「テーブルデザイン」（または「テーブルツール」の「デザイン」）タブが表示されるので、それをクリックします。
3. 左端の方にある「テーブル名」という入力ボックスに、任意の名前を入力します。（例：「T_名簿」や「部署メンバーリスト」など）

フィルターボタンの非表示

テーブルを作成すると、見出し行にフィルターボタン（▼マーク）が自動的に表示されます。今回のドロップダウンリスト作成では使用しないため、不要であれば非表示にしてスッキリさせましょう。

1. 「テーブルデザイン」タブの中にある「フィルターボタン」のチェックを外します。

これでステップ1は完了です。データが構造化され、後のステップで扱いやすくなりました。

---

テーブル範囲に名前を定義する

次に、ステップ1で作成したテーブルの特定の部分（1段階目のリストになる見出し部分と、2段階目のリストになるデータ部分）に、「名前の定義」機能を使って名前を付けていきます。これで、後のデータ入力規則の設定が格段に簡単かつ正確になります。

1段階目のリスト範囲（テーブルの見出し）に名前を定義する

1. テーブルの見出し行（例：人事、経理、営業と入力した行）全体を範囲選択します。
2. Excelのリボンメニューから「数式」タブをクリックし、「名前の管理」を選択します。
3. 「名前の管理」ダイアログボックスが表示されたら、「新規作成」ボタンをクリックします。
4. 「新しい名前」ダイアログボックスが表示されます。
   • 名前: 分かりやすい名前を入力します。これが1段階目のドロップダウンリストの元データを示す名前になります。「L_部署名」
   • 参照範囲: 選択したテーブルの見出し範囲が自動的に設定されているはずです。確認しましょう。
5. 「OK」ボタンをクリックして名前を登録します。

2段階目のリスト範囲（テーブルのデータ部分）に名前を定義する

1. テーブルのデータ部分（見出し行を除いた、2段階目のリストの選択肢が入力されている全てのセル）を範囲選択します。
2. 再び「数式」タブの「名前の管理」を開き、「新規作成」をクリックします。
3. 「新しい名前」ダイアログボックスで以下のように設定します。
   • 名前: 2段階目のリストデータを示す分かりやすい名前を入力します。「L_氏名」
   • 参照範囲: 選択したテーブルのデータ範囲が自動的に設定されていることを確認します。
4. 「OK」ボタンをクリックして名前を登録します。

これで、「名前の管理」ダイアログボックスに、定義した2つの名前（例：L_部署名、L_氏名）が登録されていれば、ステップ2は完了です。「閉じる」ボタンでダイアログを閉じます。

---

データの入力規則を設定してドロップダウンリストを作成する

1段階目の部署をプルダウンリストから選択したら、隣の名前のセルのドロップダウンリストが反映するように設定しましょう。

1段階目のドロップダウンリストを設定

1. 1段階目のドロップダウンリストを設定したいセル（またはセル範囲）を選択します。
2. Excelのリボンメニューから「データ」タブをクリックし、「データの入力規則」（アイコンはチェックマークと×マークが書かれたもの）を選択します。
3. 「データの入力規則」ダイアログボックスが表示されたら、「設定」タブを選びます。
   • 入力値の種類: ドロップダウンリストから「リスト」を選択します。
   • 元の値: （数式タブから、名前の定義で見出しに名前を付けたものを入力します。）ココでは、テキストボックスに 「L_部署名」 と入力します。（ステップ2で1段階目のリスト範囲に定義した名前の先頭に = を付けます）
4. 「OK」ボタンをクリックします。

これで、選択したセルに1段階目のドロップダウンリスト（例：人事、経理、営業が選択できる）が作成されました。

2段階目のドロップダウンリストを設定

1段階目の選択に応じて内容が変わる2段階目のドロップダウンリストを、XLOOKUP関数を使って設定します。実際に操作してみましょう。

1. 2段階目のドロップダウンリストを設定したいセル（またはセル範囲）を選択します。（例：B1セル。A1セルで部署を選んだら、B1セルにその部署のメンバーが表示されるようにする）
2. 再び「データ」タブの「データの入力規則」を開きます。
3. 「設定」タブで以下のように設定します。
   • 入力値の種類: 「リスト」を選択します。
   • 元の値: ここにXLOOKUP関数を使った数式を入力します。 入力する数式の基本形：=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合])具体的な入力例 (A1セルが1段階目のドロップダウンの場合)：=XLOOKUP(A1, L_部署名, L_氏名, "")
     • A1 (検索値): 1段階目のドロップダウンリストが設定されているセルを指定します。このセルの値（選択された部署名）を基に、2段階目のリスト内容を検索します。
     • L_部署名 (検索範囲): ステップ2で定義した、テーブルの見出し行の名前（1段階目のリストの元データ）を指定します。この範囲内で A1 の値を検索します。
     • L_氏名 (戻り範囲): ステップ2で定義した、テーブルのデータ部分の名前（2段階目のリストの元データ）を指定します。検索範囲 で A1 の値が見つかった列に対応する、この範囲のデータを返します。
     • "" ([見つからない場合] – 任意): 検索値が見つからなかった場合に表示する値を指定します。ここではダブルクォーテーション2つで「空白」を指定していますが、例えば「該当なし」などの文字列を指定することも可能です。
   • 動画内でのテクニック紹介: 動画では、いきなり「元の値」に複雑なXLOOKUP関数を書くのではなく、まずExcelのワークシート上の適当なセルに XLOOKUP関数関数を入力して、正しく動作するかを確認していました。 正しく動作することが確認できたら、入力した数式を数式バーからコピーし、ドロップダウンを選択したい箇所を範囲選択して、「データの入力規則」の「元の値」ボックスに貼り付ける、という手順が紹介されていました。これは、複雑な数式を確実に入力するための良い方法だと思います。
4. 「OK」ボタンをクリックします。

これで設定は完了です！ 1段階目のドロップダウンリストで部署を選ぶと、2段階目のドロップダウンリストの選択肢が、その部署に所属するメンバーの名前に自動的に切り替わるはずです。例えば、1段階目で「人事」を選べば人事部のメンバーが、次に「経理」を選べば経理部のメンバーが2段階目のリストに表示されます。

XLOOKUP関数の「スピル」について 最近のバージョンのExcelでは、XLOOKUP関数は結果を「スピル」で返します。これは、結果が複数ある場合に、隣接するセルに自動的に展開して表示する機能です。データの入力規則内で使用する場合、このスピルした結果がそのままドロップダウンリストの選択肢として利用されます。これで、INDIRECT関数を使わず、2段階ドロップダウンリストが作成されます。

---

この方法のメリット

INDIRECT関数の代わりにテーブルとXLOOKUP関数、そして名前の定義を組み合わせるこの方法には、データの拡張に強いからです。

行の追加（メンバーの追加）： 元データとなっているテーブルに新しい行を追加してメンバーを増やすと、特別な操作なしで自動的に2段階目のドロップダウンリストに反映されます。

列の追加（部署の追加）： テーブルに新しい列を追加して新しい部署、ここでは、設計課としておきます。
そのメンバーを入力すると、1段階目のドロップダウンリストにも新しい部署が自動的に追加され、もちろん2段階目も正しく連動します。

---

まとめ

今回は、ExcelでINDIRECT関数を使わずに、より柔軟でメンテナンス性の高い「2段階ドロップダウンリスト」を作成する方法を詳しく解説します。この方法でドロップダウンリストを作成すると、データの拡張に強いかと思います。

---