【Excel】エクセルのプルダウンを別シートと連動させる方法｜INDIRECT関数で動的リスト

当記事では広告を含む場合があります。 また当サイトでは、薬機法を順守してまいります。 そのため、各表現方法が曖昧・ふわっとしたものになりがちで読みにくい部分あるかもしれませんが、ご理解いただければ幸いです。

Excelでデータ入力をする際、プルダウンリスト（ドロップダウンリスト）を使うと入力ミスを大幅に減らすことができます。

しかし、同一シート内に選択肢をずらりと並べてしまうと、シートが見づらくなったり、データの管理が煩雑になったりと悩むことも多いのではないでしょうか。

そこで活躍するのが、別シートのデータと連動したプルダウンリストです。

さらに「INDIRECT関数」を組み合わせることで、最初のプルダウンで選んだ値に応じて、次のプルダウンの選択肢が自動的に切り替わる「動的リスト」を実現できます。

本記事では、別シート連動の基本設定からINDIRECT関数を使った応用テクニックまで、サンプルデータをもとに一気に解説します。

別シート連動プルダウンの基本とINDIRECT関数の結論

結論からお伝えすると、Excelで別シートと連動したプルダウンを作るには「名前の定義」＋「INDIRECT関数」の組み合わせが最も実用的で強力です。

データ入力規則の「リスト」機能だけでも別シート参照はできますが、動的に選択肢を切り替えるには工夫が必要になります。

まずはこの記事全体で使用するサンプルデータを確認しておきましょう。

商品カテゴリ	商品名	単価（円）	在庫数
和菓子	桜餅	180	50
和菓子	柏餅	200	40
洋菓子	マシュマロ	120	80
洋菓子	チョコ	150	60
青果	アボカド	198	30
青果	カボチャ	298	20
鮮魚	マグロ	580	15
鮮魚	カツオ	480	18
鮮魚	ハラス	650	10

このサンプルでは、「商品カテゴリ」シートと「入力シート」の2枚構成を想定します。

シート1（「マスタ」シート）にカテゴリごとの商品名を管理し、シート2（「入力」シート）でプルダウンを動作させる構成です。

1行目にはヘッダー（見出し行）があり、データは2行目以降に入力されているものとして解説を進めます。

マスタシートでは、A1セルに「和菓子」、B1セルに「洋菓子」、C1セルに「青果」、D1セルに「鮮魚」とカテゴリ名をヘッダーとして配置します。

そして各列の2行目以降に、そのカテゴリに属する商品名を入力しておくのが基本的な構成です。

この列ごとにカテゴリを分けて管理する配置が、後述するINDIRECT関数との連動を成立させる鍵となります。

別シートを参照するプルダウンリストの基本的な作り方

まずは土台となる「別シートを参照した単純なプルダウンリスト」の作り方を押さえましょう。

これはINDIRECT関数を使わない、最もシンプルな方法です。

データの入力規則を開く手順

プルダウンを設定したいセルを選択した状態で操作を始めます。

今回は「入力」シートのB2セルにカテゴリを選ぶプルダウンを設置する想定で進めましょう。

Excelのリボンから「データ」タブをクリックし、「データツール」グループ内にある「データの入力規則」ボタンをクリックします。

ダイアログボックスが開いたら、「設定」タブの「入力値の種類」を「リスト」に変更します。

別シートのセル範囲を直接指定する方法

「元の値」欄に、直接別シートのセル範囲を入力します。

マスタシートのA1〜D1（カテゴリ名が並ぶ行）を参照する場合、元の値欄には以下のように入力します。

ここで注意したいのが、「元の値」欄に直接シート名付きの範囲を入力する場合は、絶対参照（$マーク）を付けることが必須という点です。

相対参照のままだと、セルをコピーしたときに参照がズレてしまい、意図しない動作になることがあります。

入力後に「OK」をクリックすれば、B2セルにカテゴリのプルダウンが表示されるようになります。

名前ボックスを使った範囲参照の効率化

別シートの範囲を毎回手入力するのは手間がかかり、ミスの原因にもなります。

そこで活用したいのが「名前の定義」機能です。

マスタシートのA1:D1を選択した状態で、左上の「名前ボックス」（セル番地が表示されている欄）に「カテゴリリスト」と入力してEnterを押すだけで、その範囲に名前を付けることができます。

こうしておけば、「元の値」欄に「＝カテゴリリスト」と入力するだけで同じ参照が実現でき、数式がすっきり見やすくなります。

名前の定義でカテゴリ別リストを登録する方法

INDIRECT関数による動的プルダウンを実現するには、カテゴリごとに商品リストを「名前の定義」で登録しておくことが前提条件となります。

この準備がしっかりできていれば、後の連動設定は驚くほどスムーズに進みます。

各カテゴリ列に名前を定義する手順

マスタシートに移動し、「和菓子」列のデータ範囲（A2:A3）を選択します。

名前ボックスに「和菓子」と入力してEnterを押します。

同様に、B2:B3を選択して「洋菓子」、C2:C3を選択して「青果」、D2:D4を選択して「鮮魚」と定義していきます。

ポイントは「定義する名前」と「マスタシートのヘッダー名」を完全に一致させることです。

この一致があってこそ、INDIRECT関数がカテゴリ名から対応するリスト範囲を自動的に参照できるようになります。

「選択範囲から作成」機能で一括登録する方法

カテゴリが増えてきたとき、1つずつ名前を定義するのは大変です。

そこで便利なのが「数式」タブにある「選択範囲から作成」機能です。

マスタシートのA1:D4（ヘッダー含む全データ範囲）を選択した状態で、「数式」タブ→「選択範囲から作成」をクリックします。

ダイアログが表示されたら「上端行」にチェックを入れてOKをクリックするだけで、1行目のヘッダー名（和菓子・洋菓子・青果・鮮魚）を名前として、各列のデータ範囲に自動で名前が定義されます。

一括処理ができるため、カテゴリ数が多い場合でも作業時間を大幅に短縮できます。

名前に使えない文字と注意点

名前の定義には一部使用できない文字があります。

スペース・ハイフン・スラッシュなどの記号は名前に使えないため、カテゴリ名にこれらが含まれる場合は事前に変更しておく必要があります。

また、名前はアルファベットや数字から始めることはできますが、数字だけの名前は使えませんので注意が必要です。

日本語のカテゴリ名であれば基本的に問題ありませんが、定義後に「名前の管理」で確認するクセをつけておくと安心です。

INDIRECT関数の仕組みと構文の詳細解説

いよいよこの記事の核心、INDIRECT関数について詳しく見ていきましょう。

INDIRECT関数は「文字列として指定されたセル参照やセル範囲の名前を、実際の参照に変換する」関数です。

少し難しく感じるかもしれませんが、仕組みを理解すれば非常にシンプルな考え方です。

INDIRECT関数の構文と引数の意味

たとえば、A1セルに「和菓子」と入力されているとします。

このとき「＝INDIRECT(A1)」と入力すると、Excelは「和菓子という名前で定義された範囲を参照しなさい」と解釈します。

つまり、A1の値が変われば、参照先も自動的に切り替わるという動的な参照が実現するわけです。

プルダウン連動でのINDIRECT関数の使い方

「入力」シートのB2セルに第1段階のカテゴリプルダウンがあるとします。

C2セルに第2段階の商品プルダウンを設定する場合、C2セルを選択し「データの入力規則」→「リスト」を選択します。

「元の値」欄に以下の数式を入力します。

たったこれだけです。

B2セルで「和菓子」が選ばれていれば、C2のプルダウンには「桜餅・柏餅」が表示されます。

B2セルで「鮮魚」が選ばれれば、「マグロ・カツオ・ハラス」が表示されるように自動で切り替わります。

このシンプルな1行の数式が、名前の定義と組み合わさることで強力な連動機能を生み出すのがINDIRECT関数の醍醐味といえます。

別シートの名前を参照する場合の記述方法

名前の定義はブック全体で有効なため、別シートからでも「＝INDIRECT(B2)」と記述するだけで問題なく動作します。

ただし、シート名を明示的に含めた文字列でセル参照をしたい場合（名前の定義を使わないケース）は、少し記述が変わります。

文字列の連結に「&」演算子を使う点がポイントです。

もっとも、名前の定義を活用したほうが数式がシンプルになるため、可能な限り名前の定義と組み合わせる方法をおすすめします。

二段階の連動プルダウンを実際に設定する手順

ここからは、実際に操作画面をイメージしながら二段階の連動プルダウンを完成させる手順を詳しく見ていきます。

前提として、マスタシートへの名前定義が完了していることを確認してから進めましょう。

第1段階：カテゴリプルダウンの設定

「入力」シートのB2セルを選択します。

「データ」タブ→「データの入力規則」をクリックし、「入力値の種類」を「リスト」に設定します。

「元の値」欄には、カテゴリ名が入力されているマスタシートのA1:D1を参照する形で以下のように入力します。

これでB2セルに「和菓子・洋菓子・青果・鮮魚」から選べる第1プルダウンが完成します。

第2段階：商品プルダウンの設定（INDIRECT関数適用）

続いてC2セルを選択します。

同様に「データの入力規則」を開き、「リスト」を選択します。

「元の値」欄に以下を入力します。

OKをクリックして設定完了です。

これで、B2セルで「鮮魚」を選ぶとC2のプルダウンに「マグロ・カツオ・ハラス」が現れ、「和菓子」を選ぶと「桜餅・柏餅」に切り替わる動的連動が完成します。

B2セルの選択内容が変わるたびに、C2の選択肢がリアルタイムで更新されるのが最大の魅力です。

プルダウンを複数行に展開するオートフィルの活用

B2:C2で動作確認が取れたら、下の行にも同じ設定を展開します。

B2:C2を選択した状態で、右下の「フィルハンドル（＋マーク）」をドラッグして下方向に引っ張るだけで、各行に同じ入力規則が複製されます。

各行でカテゴリが異なっていても、それぞれのC列が対応するカテゴリの商品リストを表示するようになります。

数式はC2の「＝INDIRECT(B2)」がオートフィルによって各行に「＝INDIRECT(B3)」「＝INDIRECT(B4)」と自動調整されるため、手動で各行を設定する必要はありません。

プルダウンが動かない・反映されないときのトラブルシューティング

設定を終えたのにプルダウンが期待どおりに動かないことがあります。

よくある原因とその対処法を押さえておきましょう。

エラー「参照が正しくありません」が出る場合

このエラーはINDIRECT関数が参照しようとした名前が存在しないときに発生します。

最も多い原因は、B2セルのカテゴリ名と名前の定義が一致していないケースです。

たとえば、名前の定義では「和菓子」と登録しているのに、B2セルに「和菓子 」（末尾にスペース）が入っていると参照に失敗します。

「数式」タブ→「名前の管理」で登録されている名前を確認し、入力値との一致を確かめましょう。

また、プルダウンの選択値ではなく手入力でカテゴリ名を入れている場合も、スペルや全角・半角のズレが原因になりやすいです。

別シート参照が認識されない場合

データの入力規則の「元の値」欄に別シート参照を直接入力しようとすると、「ほかのシートまたはブックへの参照は、データの入力規則には使えません」といったエラーが出ることがあります。

これは直接参照では別シートを「元の値」欄に指定できないExcelの仕様によるものです。

この制限を回避するには、前述の「名前の定義」を使う方法が最も確実です。

名前の定義を使えばブック全体で有効な参照として扱われるため、このエラーは発生しません。

カテゴリを変更しても商品リストが更新されない場合

カテゴリを変更したのに商品プルダウンが前の選択肢のままになることがあります。

これはプルダウンの「選択肢」は更新されているものの、すでに入力済みの値はそのまま残るためです。

選択肢が切り替わっているかは、実際にC列のプルダウンをクリックして確認する必要があります。

また、計算モードが「手動」になっているとINDIRECT関数が再計算されないことがあります。

「数式」タブ→「計算方法の設定」が「自動」になっているかを確認しましょう。

テーブル機能を使って選択肢を動的に追加する応用設定

現状の設定では、マスタシートに商品を追加しても名前の定義の範囲が自動拡張されないため、新しい商品がプルダウンに反映されません。

この問題をスマートに解決するのが、Excelの「テーブル機能」との組み合わせです。

マスタシートをテーブル形式に変換する手順

マスタシートのデータ範囲（A1:D4）を選択し、「挿入」タブ→「テーブル」をクリックします。

「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認してOKをクリックします。

テーブルに変換すると、データを追加した際に範囲が自動的に拡張されます。

ただし、テーブル内の列を直接名前の定義に使う場合は、テーブルの「構造化参照」を意識する必要があります。

OFFSET関数で動的な範囲を定義する方法

テーブル機能と合わせて、OFFSET関数とCOUNTA関数を組み合わせた動的な名前定義も有効です。

この定義では、A列に商品を追記するだけで名前「和菓子」の参照範囲が自動で広がります。

COUNTA(マスタ！$A:$A)-1 の「-1」はヘッダー行の1行分を除外するための調整です。

カテゴリごとに同様の定義を行えば、商品追加のたびに名前の定義を更新する手間がなくなります。

名前の定義をOFFSET式に更新する操作手順

すでに作成済みの名前の定義をOFFSET式に変更するには、「数式」タブ→「名前の管理」を開きます。

変更したい名前（例：和菓子）を選択して「編集」をクリックします。

「参照範囲」欄の内容を上記のOFFSET式に書き換えてOKをクリックするだけで更新完了です。

同様に洋菓子・青果・鮮魚の各名前も更新すれば、動的拡張に対応したマスタ管理が実現します。

プルダウンの見栄えと使いやすさを高める仕上げの設定

機能として動作するプルダウンが完成したら、実際に使う人が迷わないよう見た目と操作性を整えることも重要です。

入力時メッセージで操作ガイドを表示する

データの入力規則ダイアログの「入力時メッセージ」タブでは、セルを選択したときに小さなポップアップを表示することができます。

「タイトル」欄に「カテゴリを選択」、「メッセージ」欄に「ここからカテゴリを選んでください」などと入力しておくと、入力者が迷わずに操作できるようになります。

特に複数人で共有するファイルでは、入力時メッセージによるガイドが操作ミスの防止に大きく貢献します。

エラーメッセージでリスト外入力を防止する

「エラーメッセージ」タブでは、プルダウン以外の値を手入力された場合に警告を出す設定ができます。

「スタイル」を「停止」にすると、リスト外の入力を完全に拒否することが可能です。

「情報」スタイルにすれば警告は表示しつつも入力は許可できるため、用途に応じて使い分けましょう。

データ品質を厳密に管理したい場合は「停止」、柔軟性を持たせたい場合は「注意」や「情報」がおすすめです。

条件付き書式でプルダウン選択状態を視覚化する

プルダウンで選択した値に応じてセルの色を変える条件付き書式を設定すると、入力状況が一目でわかるようになります。

たとえばカテゴリが「鮮魚」のときは青、「和菓子」のときはピンクなどの色分けをすると、大量のデータを扱う際の視認性が格段に上がります。

条件付き書式はB列のセル値を条件にするだけでよく、プルダウンと組み合わせることで自動的に色が切り替わるため、設定の手間もそれほどかかりません。

まとめ：エクセルのプルダウンを別シートと連動させてINDIRECT関数で動的リストを実現する

今回は、Excelのプルダウンを別シートと連動させる方法とINDIRECT関数による動的リストの作り方を解説しました。

まず、別シート参照の基本として「データの入力規則」→「リスト」で別シートのセル範囲を指定する方法を確認しました。

次に、名前の定義でカテゴリごとの商品リストに名前を付けておくことが、INDIRECT関数と連動させるための重要な前提条件であることを学びました。

INDIRECT関数は「＝INDIRECT(B2)」というシンプルな1行で、B2の選択値に対応した名前付き範囲を動的に参照する強力な関数です。

プルダウンが動かない場合は、名前の定義とカテゴリ名の一致・計算モードの確認が解決への近道です。

さらに、OFFSET＋COUNTA関数で動的な範囲定義を行えば、商品追加のたびに設定を変更する必要がなくなります。

「名前の定義」×「INDIRECT関数」×「データの入力規則」の三位一体が、別シート連動プルダウンの黄金パターンと言えるでしょう。

入力時メッセージや条件付き書式も組み合わせて、使いやすく管理しやすいExcelファイルを作り上げてみてください。