【Googleスプレッドシート】データ検証で他シート参照のリストを使う!共通マスタの活用

データ入力の効率化には、プルダウンリストが欠かせません。特に、複数のシートで共通のマスターデータを使いたい場合、リストを一元管理できれば便利です。しかし、データ検証のリスト範囲は基本的に同じシート内のセル範囲しか指定できません。そこで、この記事ではINDIRECT関数を使い、別シートのデータをプルダウンリストとして参照する方法を解説します。これで、共通マスタを活用した柔軟な入力フォームを作成できるようになります。

データ検証で別シートを参照する仕組み

データ検証の「リストを指定」では、通常は同じシート内のセル範囲しか直接選択できません。これは、ダイアログ上で範囲選択ボタンを押すと、アクティブシートのセルしか選択できない仕様だからです。しかし、リストの項目に「カスタム数式」を選び、INDIRECT関数を使うことで間接的に別シートの範囲を指定できます。

INDIRECT関数は、文字列で指定されたセル参照を実際の参照に変換する関数です。例えば、=INDIRECT(“マスタ!A1:A10”) と書けば、シート「マスタ」のA1:A10を参照できます。シート名にスペースや記号が含まれる場合は、シングルクォーテーションで囲む必要があります。例えば、=INDIRECT(“‘商品マスタ’!A:A”) のようにします。

他シート参照のリストを作成する手順

基本的なINDIRECTの使い方

1. リスト元のデータを別シートに用意する
   まず、リストとして使いたいデータを別のシートに入力します。例えば、「マスタ」という名前のシートを作成し、A列に商品名を一覧で入力します。データは空欄なく連続した範囲にしておくとトラブルが少なくなります。
2. データ検証を設定するセルを選択する
   プルダウンリストを表示させたいセルをクリックして選択します。複数のセルに同じリストを設定したい場合は、範囲をまとめて選択してください。
3. データ検証ダイアログを開く
   メニューバーから「データ」をクリックし、表示されたメニューから「データ検証」を選択します。データ検証ダイアログが開きます。
4. 条件に「カスタム数式」を指定する
   条件のプルダウンから「リストを指定」を選びます。対応するバージョンでは、数式入力欄の左側にある「カスタム数式」ラジオボタンを選択する必要があります。最新のGoogleスプレッドシートでは、「リストを指定」を選ぶと直接数式を入力できます。
5. INDIRECT関数の数式を入力する
   数式欄に =INDIRECT(“マスタ!A:A”) のように入力します。シート名が「マスタ」、範囲がA列全体です。範囲を特定の行に限定したい場合は、=INDIRECT(“マスタ!A2:A100”) とします。シート名にスペースがある場合は、=’商品マスタ’!A:A のようにシングルクォーテーションで囲みます。
6. エラーメッセージや通知の設定を行う
   必要に応じて、無効なデータが入力された場合のエラーメッセージを設定します。「無効なデータを拒否する」にチェックを入れ、エラーメッセージを入力しておくと便利です。
7. 設定を保存して動作を確認する
   「保存」ボタンをクリックして設定を完了します。設定したセルをクリックすると、リストのプルダウンが表示されるはずです。リストが表示されない場合は、数式のシート名や範囲が正しいか確認してください。

名前付き範囲を使う方法

1. リストの範囲に名前を付ける
   「マスタ」シートのA列全体を選択し、メニュー「データ」→「名前付き範囲」を開きます。範囲名を「商品リスト」などと入力し、「完了」をクリックします。
2. データ検証の数式を調整する
   データ検証の数式欄に =INDIRECT(“商品リスト”) と入力します。名前付き範囲の参照は、INDIRECT関数で文字列として指定できます。
3. 注意点
   名前付き範囲の参照先が別シートの場合でも、INDIRECT関数で正しく動作します。ただし、名前付き範囲を削除するとリストがエラーになります。

動的リストの作成(応用)

1. COUNTA関数でデータ数を取得する
   データの行数が変動する場合、=COUNTA(マスタ!A:A) でデータ数を取得します。
2. INDIRECTと組み合わせて可変範囲を指定する
   データ検証の数式に =INDIRECT(“マスタ!A2:A”&COUNTA(マスタ!A:A)) と入力します。これで、データが増えても自動的に範囲が拡大されます。ただし、データの途中に空白セルがあると正しく動作しないため、注意が必要です。
3. FILTER関数を使って条件付きリストを作る
   例えば、特定の条件を満たすデータだけをリストにしたい場合は、=INDIRECT(“Sheet1!A2:A”&COUNTA(FILTER(Sheet1!A2:A,Sheet1!B2:B=”有効”))) のように、FILTER関数の結果をCOUNTAで数える方法も考えられます。ただし、FILTERは配列を返すため、INDIRECTと直接組み合わせるのは複雑です。その場合は、事前に別シートにFILTER結果を出力しておくと簡単です。

注意点とよくあるトラブル

シート名を変更した場合の影響

INDIRECT関数内のシート名は文字列で固定されているため、シート名を変更すると参照が無効になります。シート名を変更した場合は、データ検証の数式も手動で修正する必要があります。名前付き範囲を使うと、シート名変更の影響は受けませんが、名前付き範囲の参照先がシート名を含んでいる場合、同様の問題が発生します。

データ範囲が変動する場合

リストの項目数が増減する場合、固定範囲(A1:A100など)では対応できません。その場合は、前述のCOUNTA関数と組み合わせて動的な範囲を指定します。また、空白セルが多いと意図しない範囲になるため、データは連続して入力するようにしてください。

他のシートが削除された場合

参照先のシートを削除すると、データ検証のリストはエラーになります。エラーメッセージが表示され、プルダウンは機能しなくなります。削除する前に、他のシートで参照していないか確認してください。名前付き範囲の場合も、参照先シートが削除されるとエラーになります。

IMPORTRANGEと組み合わせる場合の注意

別のスプレッドシートファイルからデータをインポートするIMPORTRANGE関数をINDIRECTと一緒に使うことはできません。IMPORTRANGEは配列を返すため、データ検証のリストとして直接指定できません。どうしても別ファイルのリストを使いたい場合は、事前にIMPORTRANGEでデータをインポートしたシートを用意し、そのシートをINDIRECTで参照する方法を取ってください。

各方法の比較

用途に応じて方法を選びましょう。一般的な共通マスタの活用には、名前付き範囲とINDIRECT関数の組み合わせがおすすめです。

まとめ

データ検証で他シートのリストを参照するには、INDIRECT関数が最も簡単な方法です。名前付き範囲と組み合わせると、シート構成の変更にも柔軟に対応できます。さらに、COUNTA関数を使えばリストの項目数が変動しても自動で追従します。これで、共通マスタを活用した効率的なデータ入力システムを構築できます。INDIRECT関数と名前付き範囲をマスターして、スプレッドシートのデータ入力をよりスマートにしてください。