【Googleスプレッドシート】プルダウンの選択肢をシートから参照!動的リストの作成

プルダウンメニューの選択肢を毎回手入力で更新するのは手間がかかります。シート上のデータを元に選択肢を自動で反映できれば、メンテナンスが格段に楽になります。この記事では、Googleスプレッドシートでプルダウンの選択肢をシートから参照する方法を3つご紹介します。データの入力規則やINDIRECT関数を使うことで、動的に変化するリストを作成できます。

プルダウンと動的リストの仕組み

Googleスプレッドシートのプルダウン(データの入力規則)は、セルに入力できる値を制限する機能です。通常はあらかじめ選択肢を手入力しますが、シート上のセル範囲を参照することで、その範囲の値が変わればプルダウンの選択肢も自動的に更新されます。これを動的リストと呼びます。動的リストを活用すると、商品リストや部署名など頻繁に変わるデータを一元管理でき、入力ミスを減らせます。

動的プルダウンリストを作成する3つの手順

ここでは代表的な3つの方法を順に解説します。状況に応じて最適な方法を選んでください。

方法1:データの入力規則で範囲を直接指定する

1. 元データを用意する
   プルダウンの選択肢となる値を、シートの1列または1行に入力します。例えばA1からA10に商品名を並べてください。
2. プルダウンを設定するセルを選択する
   プルダウンを適用したいセル(または複数セル)を選択します。
3. データの入力規則を開く
   メニューから「データ」→「データの入力規則」をクリックします。
4. 条件を設定する
   「条件」で「リスト(範囲)」を選び、範囲ボックスに元データの範囲(例:Sheet1!A1:A10)を入力します。必要に応じて「セルに無効なデータを入力した場合」の動作を指定します。
5. 保存する
   「保存」をクリックします。これでプルダウンに元データの値が表示されます。元データを追加・削除すると、プルダウンも自動更新されます。

この方法はシンプルで最も手軽です。ただし、データを追加するたびに範囲を手動で広げる必要がある点に注意しましょう。範囲を広げたくない場合は、次の方法をお試しください。

方法2:INDIRECT関数を使って別シートのリストを参照する

1. 別シートに元データを用意する
   例えば「リスト」というシートを作り、A1からA10に選択肢を入力します。
2. プルダウンを設定したいシートでセルを選択する
   プルダウンを適用したいセルをクリックします。
3. データの入力規則でINDIRECTを使う
   「データの入力規則」を開き、条件を「カスタム数式」に変更します。数式欄に =INDIRECT("リスト!A1:A10") と入力します。これで「リスト」シートの範囲を動的に参照できます。
4. INDIRECTの文字列をセル参照に変更する(応用)
   シート名や範囲を別のセルに記述し、INDIRECTでそのセルを参照すれば、選択肢を動的に切り替えられます。例えば =INDIRECT(B1) とし、B1セルに「リスト!A1:A10」と入力しておきます。
5. 保存して動作確認する
   「保存」ボタンを押し、プルダウンが正しく表示されるか確認します。

INDIRECT関数を使うと、シート名や範囲を変数化できるため、柔軟性が高まります。ただし、閉じたブックを参照するとエラーになる場合があるので、注意しましょう。

方法3:名前付き範囲を使って管理しやすくする

1. 元データに名前を付ける
   選択肢が入っているセル範囲(例:A1:A10)を選択し、メニュー「データ」→「名前付き範囲」を開きます。範囲に「商品リスト」などの名前を入力して「完了」をクリックします。
2. プルダウンを設定するセルを選択する
   プルダウンを適用したいセルをクリックします。
3. データの入力規則で名前付き範囲を指定する
   「データの入力規則」を開き、条件を「リスト(範囲)」にし、範囲ボックスに =商品リスト または =INDIRECT("商品リスト") と入力します。直接名前を入力する場合はイコールを忘れずに。
4. 保存して動作確認する
   「保存」をクリックします。名前付き範囲の内容を変更すると、プルダウンにも反映されます。

名前付き範囲を使うと、範囲の変更が一箇所で済むため、シートが複雑になっても管理しやすくなります。また、INDIRECTと組み合わせるとさらに強力です。

動的リスト作成時の注意点とよくあるトラブル

データを追加してもプルダウンに反映されない

範囲を固定で指定している場合、データを追加しても自動では反映されません。範囲を広げるには、データの入力規則を編集して範囲を更新する必要があります。または、次の方法を検討しましょう。範囲を大きめに取る(例:A1:A100)か、INDIRECT関数で「A:A」のように列全体を参照します。ただし、列全体を参照すると空白セルも選択肢に含まれるため、無効なデータの動作を「入力を拒否」に設定するなどの対策が必要です。

INDIRECTが正しく動作しない

INDIRECT関数を使う際に、シート名にスペースが含まれているとエラーになります。その場合はシート名をシングルクオーテーションで囲んでください。例えば =INDIRECT("'リスト 2'!A1:A10") のようにします。また、参照先のシートが削除されると#REF!エラーが発生します。

プルダウンに空白セルが表示される

範囲内に空白セルが含まれていると、プルダウンに空白行が表示されます。これを防ぐには、元データの範囲を空白でないセルのみに限定するか、元データをフィルタやQUERY関数で空白を除外した範囲に設定します。ただし、データの入力規則の範囲にQUERYは直接使えません。代わりに、補助列で空白を除外したリストを作り、その範囲を参照するとよいでしょう。

各方法の比較表

まとめ

プルダウンの選択肢をシートから参照する3つの方法を解説しました。直接範囲指定はシンプルで初心者にもおすすめです。INDIRECT関数を使えば動的な切り替えが可能になり、名前付き範囲を使えば管理が楽になります。まずは目的に合った方法を選び、実際に試してみてください。さらに応用として、複数のプルダウンを連動させる従属プルダウンにも挑戦すると、より高度な入力フォームが作れます。