【Googleスプレッドシート】条件付き書式で他シートの値を参照!INDIRECTでの動的参照

条件付き書式で他のシートの値を参照したいとき、セル範囲を直接指定できないため困ったことはありませんか。通常の条件付き書式では、同じシート内のセルしか参照できません。しかし、INDIRECT関数を使えば文字列でセル参照を組み立てて動的に参照できます。この記事では、INDIRECT関数を使って条件付き書式で他シートの値を参照する具体的な手順を解説します。

INDIRECT関数が条件付き書式で使える仕組み

条件付き書式は、通常は同じシート内のセル範囲しか入力規則の参照先にできません。しかし、カスタム数式モードでは任意の関数を使えるため、INDIRECT関数を利用できます。INDIRECT関数は文字列として渡されたセル参照を実際の参照に変換します。この性質を利用して、シート名やセル番地を動的に構築し、他シートの値を条件付き書式の判定に使えるようになります。例えば、=INDIRECT(”Sheet2!A1”) と書けば、Sheet2のA1セルの値を参照できます。この仕組みにより、複数のシートに同じ書式ルールを適用する場合でも、ルール自体は一か所で管理できるのです。

条件付き書式で他シートの値を参照する手順

1. 条件付き書式を設定するシートを開く
   適用したい範囲を選択します。例えばA1:A10を対象とします。
2. メニューから条件付き書式を開く
   「書式」→「条件付き書式」をクリックします。
3. ルールの種類を「カスタム数式」に変更する
   条件付き書式ルールのプルダウンから「カスタム数式」を選択します。
4. カスタム数式にINDIRECTを使った数式を入力する
   例えば、別シート「Sheet2」のB1セルの値が100より大きい場合に書式を適用するには、次の数式を入力します:
   =INDIRECT(”Sheet2!B1”)>100
   なお、シート名にスペースが含まれる場合はシングルクォーテーションで囲みます:=INDIRECT(”’Sheet 2′!B1”)>100
5. 書式スタイルを設定して完了
   塗りつぶし色など好みの書式を選び、「完了」をクリックします。

セルアドレスを動的にする応用

上記の手順ではシート名とセルを固定で指定しましたが、INDIRECTの引数を他のセルから参照することで、動的に変更できます。例えば、セルC1にシート名、D1にセルアドレスを入力しておき、数式を=INDIRECT(C1&”!”&D1)>100 とします。こうすると、C1やD1の値を変えるだけで参照先を変更できます。複数の条件付き書式ルールを統一したい場合に便利です。

複数シートの値を参照する場合

同様のルールを複数のシートに適用する場合、各シートごとにルールを作成する必要はありません。適用範囲をシート全体(例:Sheet1!A:A)に設定し、カスタム数式でINDIRECTを使って他シートを参照すれば、一つのルールで全シートに同じ条件を適用できます。ただし、参照先のシートが存在しない場合エラーになるため、事前に存在確認をするか、IFERRORでエラー処理を行うと良いでしょう。

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

シート名に引用符が必要なケース

シート名にスペースや記号が含まれる場合、INDIRECTの文字列内でシート名をシングルクォーテーションで囲む必要があります。例えばシート名が「Sales Data」の場合、=INDIRECT(”’Sales Data’!A1”) と書きます。この引用符を忘れると参照エラーになります。

循環参照に注意

条件付き書式を設定したシート自身を参照すると循環参照が発生し、スプレッドシートが重くなる原因になります。必ず別のシートを参照するようにしてください。

INDIRECTで参照範囲を指定する際の制限

INDIRECT関数は単一セル参照だけでなく、範囲参照も文字列で指定できます。例えば=SUM(INDIRECT(”Sheet2!A1:A10”)) のように使えます。条件付き書式でも=INDIRECT(”Sheet2!B1”) の代わりに=INDIRECT(”Sheet2!B:B”) とすることで列全体を参照できます。ただし、INDIRECTで範囲を指定する場合、条件付き書式の適用範囲とサイズが合わないと意図しない結果になることがあります。通常は単一セルとの比較で使うことをおすすめします。

シートを削除したときのエラー

参照先のシートを削除すると、INDIRECTは#REF!エラーを返します。条件付き書式のルールも無効になります。削除前にルールを更新するか、エラーを考慮したIFERRORの利用を検討してください。

固定参照とINDIRECTによる動的参照の比較

まとめ

条件付き書式で他シートの値を参照するには、INDIRECT関数をカスタム数式のルールに組み込むのが最も簡単な方法です。INDIRECTは文字列でセル参照を扱えるため、シート名やセルアドレスを動的に変更でき、複数のシートにわたる管理も効率的になります。また、シート名の引用符や循環参照に注意すれば、トラブルなく利用できます。応用として、名前付き範囲をINDIRECTと組み合わせると、さらに柔軟な条件設定が可能です。ぜひ試してみてください。