【スプレッドシート】プルダウンリストの選択肢を複数範囲から設定する方法

Google スプレッドシートのプルダウンリスト（データの入力規則）は、特定のセルに入力できる値を制限し、入力ミスを防いだり、選択肢を提示したりするのに非常に便利な機能です。通常、リストの選択肢はシート上の連続した1つのセル範囲を参照して作成します。

しかし、実際の運用では、リストの元データが複数の異なる範囲（例えば、別のシートにあるカテゴリリストや、同じシート内の離れた場所にある担当者リストなど）に散らばっているケースも少なくありません。このような場合に、それらの複数範囲のデータをまとめて1つのプルダウンリストの選択肢として表示させたい、というニーズが出てきます。

この記事では、Google スプレッドシートで複数の範囲にあるデータを結合し、それを元にしたプルダウンリストを作成する具体的な方法を、関数を活用した効率的なアプローチを中心に解説します。また、この機能が利用可能になった背景や、スマートフォンアプリでの利用に関する注意点についても触れていきます。

基本: 単一範囲からのプルダウンリスト設定 (復習)

まず、基本となる単一のセル範囲からプルダウンリストを作成する方法を簡単におさらいします。

手順

  1. プルダウンリストを設定したいセル（またはセル範囲）を選択します。
  2. メニューバーの「データ」→「データの入力規則」をクリックします。
  3. 「条件」の項目で「リストを範囲で指定」を選択します。
  4. 右側の入力ボックスに、リストの元データとなるセル範囲（例: `シート1!A1:A10`）を入力するか、ボックス横のグリッドアイコンをクリックして範囲を選択します。
  5. 必要に応じて「無効なデータの場合:」の設定を行い、「保存」をクリックします。

これで、選択したセルに指定範囲の値をリストとして持つプルダウンが表示されます。

課題: 複数範囲の直接指定は不可

「データの入力規則」で「リストを範囲で指定」を選択した場合、残念ながら直接的に複数の範囲（例: `A1:A5,C1:C5` や `シート1!A1:A5,シート2!B1:B5` のようにカンマで区切るなど）を指定することはできません。入力規則は、連続した単一の範囲のみをリストのソースとして受け付けます。

解決策: 複数範囲のデータを1箇所に集約する

複数の範囲をプルダウンリストの選択肢にするための最も一般的なアプローチは、それらの範囲に含まれるデータを別の場所に1つのリスト（マスターリスト）として集約し、その集約されたリストを「データの入力規則」で参照することです。

いつからできるようになった？
この方法、特に後述する関数を使って動的にマスターリストを生成するアプローチは、Google スプレッドシートに比較的新しい関数や機能が追加されたことで、より実用的になりました。例えば、UNIQUE関数やFILTER関数は2019年頃に導入され、配列リテラル{}による範囲結合や強力なQUERY関数と組み合わせることで、以前は手作業や複雑なGoogle Apps Scriptが必要だった複数範囲のデータの集約・整形が、シート上の数式だけで実現できるようになっています。

このマスターリストは、関数を使って動的に生成することで、元データの変更にも自動的に追従させることができ、メンテナンスの手間を大幅に削減できます。以下に主な方法を示します。

方法1: 手動コピー＆ペースト (非推奨)

最も単純な方法ですが、複数の範囲から値をコピーし、マスターリストを作成したい列に貼り付ける方法です。ただし、元データが変更・追加された場合に手動で更新する必要があるため、データの整合性を保つのが難しく、あまり推奨されません。

方法2: UNIQUE関数と配列リテラル {} を使う (推奨)

関数を使って動的にマスターリストを生成する、最も汎用性が高く推奨される方法です。

考え方:

  1. 配列リテラル {} を使って、複数の範囲を縦方向（または横方向）に結合した仮想的な配列を作成します。縦に結合する場合はセミコロン ;、横に結合する場合はカンマ , で範囲を区切ります。（プルダウンリスト用には通常、縦1列にするためセミコロンを使用します。）
  2. 結合した配列から、FILTER関数を使って空白セルを除外します（任意ですが推奨）。
  3. 最後にUNIQUE関数を使って、重複する値を除去し、一意なリストを作成します（任意）。

例: 「シート1」のA1:A10 と「シート2」のB1:B5 の内容を結合し、空白と重複を除いたマスターリストを作成する場合（例: 作業用シートのA1セルに入力）

=UNIQUE(FILTER({シート1!A1:A10; シート2!B1:B5}, {シート1!A1:A10; シート2!B1:B5}<>""))

解説:

  • {シート1!A1:A10; シート2!B1:B5}: 2つの範囲を縦に結合した配列を作成します。
  • FILTER(..., ...<>""): 結合した配列から、値が空白でないものだけを抽出します。
  • UNIQUE(...): 抽出されたリストから重複する値を取り除きます。

この数式をマスターリストを作成したい列の先頭セルに入力すると、結合・整形されたリストが自動的に展開されます。

方法3: QUERY関数を使う

QUERY関数も、データの結合、フィルタリング、並べ替えを同時に行える強力な関数です。

例: 「シート1」のA1:A10 と「シート2」のB1:B5 の内容を結合し、空白を除外し、昇順で並べ替えたマスターリストを作成する場合（例: 作業用シートのA1セルに入力）

=QUERY({シート1!A1:A10; シート2!B1:B5}, "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1")

解説:

  • {シート1!A1:A10; シート2!B1:B5}: 配列リテラルでデータを結合します。
  • "SELECT Col1 WHERE Col1 IS NOT NULL ORDER BY Col1": クエリ言語で処理を指定します。
    • SELECT Col1: 結合したデータの1列目を選択します。
    • WHERE Col1 IS NOT NULL: 1列目が空でない（NULLでない）行のみを抽出します。
    • ORDER BY Col1: 1列目の値で昇順に並べ替えます。

QUERY関数はSQLライクな記述が必要ですが、重複除去（SELECT DISTINCT Col1）や複雑な条件指定も可能です。

マスターリストの作成場所

上記の方法で作成するマスターリストは、以下のいずれかの場所に作成するのが一般的です。

  • 専用の作業用シート（推奨）: 「リストマスタ」などの名前で新しいシートを作成し、そこにマスターリストを生成します。管理がしやすく、他のデータとの干渉を防げます。
  • プルダウンを設定するシートの空いている列: シート構成がシンプルな場合は、入力規則を設定するシートの右端などの空いている列にマスターリストを作成しても良いでしょう。

プルダウンリストの設定手順

マスターリスト（例: 作業用シートのA列に作成）が準備できたら、それを参照してプルダウンリストを設定します。

  1. プルダウンリストを設定したいセル（またはセル範囲）を選択します。
  2. メニューバーの「データ」→「データの入力規則」をクリックします。
  3. 「条件」で「リストを範囲で指定」を選択します。
  4. 右側の入力ボックスに、作成したマスターリストの範囲を指定します。行数を指定せず列全体を指定する（例: 作業シート!A:A）か、マスターリストの先頭セルのみを指定する（例: 作業シート!A1）ことを推奨します。これにより、マスターリストの項目が増減しても自動的に追従します。

    ヒント: 作業シート!A1 のように先頭セルだけを指定する方法は、そのセルに関数（UNIQUEやQUERY）が入力されている場合に有効です。関数が返す結果全体が自動的にリストの範囲とみなされます。

  5. 必要に応じて他のオプションを設定し、「保存」をクリックします。

これで、複数の元範囲のデータを集約したマスターリストに基づいたプルダウンリストが設定されました。

応用とヒント

  • 名前付き範囲の活用: 作成したマスターリストの範囲（例: `作業シート!A:A`）に「名前付き範囲」（メニュー「データ」→「名前付き範囲」）で分かりやすい名前（例: `ItemList`）を付けておくと、入力規則の設定時に `=ItemList` と指定でき、管理がしやすくなります。
  • 重複除去の選択: プルダウンリストに重複した選択肢を表示したくない場合は、マスターリスト作成時に `UNIQUE` 関数を使用します。重複を許可する場合は `UNIQUE` 関数は不要です。
  • 並べ替え: マスターリストをアルファベット順や五十音順にしたい場合は、`SORT` 関数や `QUERY` 関数の `ORDER BY` 句を使ってマスターリスト自体を並べ替えておくと、プルダウンリストもその順序で表示されます。 
    =SORT(UNIQUE(FILTER({範囲1; 範囲2}, {範囲1; 範囲2}<>"")))

スマートフォンアプリでの利用について

この方法で作成したプルダウンリストは、スマートフォンのGoogle スプレッドシートアプリ（iOS/Android）でどのように利用できるでしょうか？

  • リストからの選択は可能:
    パソコンで設定したプルダウンリストは、基本的にスマホアプリ上でも選択肢として問題なく利用できます。セルをタップすれば、設定された選択肢が表示され、選ぶことができます。
  • 数式の作成・編集はPC推奨:
    リストの元データとなるマスターリストを作成するための関数（UNIQUE, FILTER, QUERY, 配列リテラル{}など）の入力や編集、デバッグ作業は、スマホアプリでは画面の制約や機能制限のため、非常に困難です。これらの複雑な数式の作成や管理は、パソコンのブラウザ版で行うことを強く推奨します。
  • 入力規則の設定画面:
    スマホアプリからも「データの入力規則」を設定・編集すること自体は可能ですが、パソコン版に比べて機能が簡略化されている場合があります。特に、範囲指定や数式のプレビューなどの操作性はパソコン版が優れています。

結論として、複数範囲を元にしたプルダウンリストの利用（選択）はスマホでも可能ですが、その設定やマスターリストの管理（数式作成など）はパソコンで行うのが現実的です。

注意点

  • 元データの更新反映: 関数（UNIQUE, QUERYなど）を使ってマスターリストを作成していれば、元データが変更されると、マスターリストも自動的に更新され、プルダウンリストの選択肢も最新の状態に保たれます。手動でコピペした場合は、手動での更新が必要です。
  • マスターリストの範囲指定: 入力規則でマスターリストを参照する際は、A1:A100のように固定的な範囲を指定するよりも、A:A（列全体）やA1（関数が入力されたセル）を指定する方が、リストの項目数が将来的に増減しても対応しやすいです。
  • パフォーマンス: 結合するデータ範囲が非常に大きい、または複雑な関数を使用する場合、スプレッドシートの計算に時間がかかる可能性があります。
  • バックアップ: 複雑な数式やデータの入力規則を設定する前には、ファイルのコピーを作成しておくことをお勧めします。

まとめ

Google スプレッドシートでプルダウンリストの選択肢を複数の範囲から設定するには、直接指定する機能はありませんが、①複数の範囲のデータを関数（UNIQUE, FILTER, QUERY, 配列リテラル{}など）を使って1箇所に集約した「マスターリスト」を作成し、②そのマスターリストを「データの入力規則」で参照する、という手順で実現できます。これは比較的新しい関数の登場により、数式だけで効率的に行えるようになりました。

関数を使うことで、元データが変更されてもプルダウンリストが自動的に最新の状態に保たれ、メンテナンスの手間も省けます。スマートフォンアプリでの利用は可能ですが、リストの作成や管理はパソコンで行うのが基本です。データが複数の場所に散らばっていてプルダウンリストの設定にお困りの際は、ぜひこの方法を試してみてください。

コメント