【スプレッドシート】条件付き書式で別シートを参照して色を付ける！

Googleスプレッドシートで自動で色付けするのに便利な「条件付き書式」ですが、別シートを参照しようととしたときにエラーになることがあります。この記事では、INDIRECT関数を使って別シートのデータと連動して色を塗る方法を解説します。

【基本】別シートのセルを参照する

条件付き書式のカスタム数式では、別シートの参照（例：='数値２'!$A2）をそのまま使うとエラーになります。そのため、INDIRECT関数を使って有効な参照に変換する必要があります。

色を付けたい範囲（例：A2:Z5）を選択し、「表示形式」→「条件付き書式」を開きます。

INDIRECT関数は、「文字列」を「実際のセル参照」に変えることができる関数です。

たとえば、「数値１」シートのA列の数字が、「数値２」シートの数字よりも大きければ色を付けたい場合、書式設定の条件を「カスタム数式」に変更し、以下のような数式を入力します。

例：=$A2 > INDIRECT("'数値２'!$A2")

この数式は、"'数値２'!$A2"という文字列で書かれたセル参照を、実際の参照に変換し、「数値２」シートのA2のデータを参照します。

【ポイント】

• 文字列全体をダブルクォーテーション（"）で囲み、シート名をシングルクォーテーション（'）で囲みます。
• 参照は、$を付けて列のみ絶対参照（$A2）にし、列がズレないように固定します。

【応用】別シートの範囲を参照する

色を付けたい範囲（例：A2:X6）を選択し、「表示形式」→「条件付き書式」を開きます。

たとえば、「祝日表」シートを参照して「日報」シートの祝日に色を付けたい場合、書式設定の条件を「カスタム数式」に変更し、以下のような数式を入力します。

例：=COUNTIF(INDIRECT("'祝日表'!$A$2:$A$20"),$A2)=1

---

【数式の解説】

1.INDIRECT関数（文字列を実際の参照に変換する）

例：=INDIRECT("'祝日表'!$A$2:$A$20")

この数式は、"'祝日表'!$A$2:$A$20"という文字列で書かれたセル参照を、実際の参照に変換し、「祝日表」シートのA2:A20のデータを返します。

【ポイント】

• 文字列全体をダブルクォーテーション（"）で囲み、シート名をシングルクォーテーション（'）で囲みます。
• 範囲は、$を付けて絶対参照（$A$2:$A$20）にし、参照範囲がズレないように固定します。

2.COUNTIF関数（重複数を確認する）

COUNTIF関数は、検索範囲内に、条件と一致するセルがいくつあるかを数えます。

例：=COUNTIF($B$2:$B$20,$A2)

この数式は、INDIRECT関数が取得した「祝日表」の範囲内に、「検査対象の日付」がいくつあるかを数えます。

そのため、以下の数式は、祝日表に検査値の日付が含まれる（=1）場合にTRUEを返し、書式が適用されます。

例：=COUNTIF(INDIRECT("'祝日表'!$A$2:$A$20"),$A2)=1

【ポイント】

• COUNTIF関数の検索条件（$A2）は、色を塗る行に応じて日付が変わるように、列のみ絶対参照になっています。

別シートの参照が上手くいかない場合

シート名が完全に一致していない

【原因】

INDIRECT関数内のシート名（例：'祝日表'）が、実際のシート名と異なっていると、参照先のデータが存在しないため色が付きません。

【解決方法】

シート名をコピー＆ペーストし、数式内の文字列に貼り付けます。

---

その他の一般的な原因については、「条件付き書式が反映されない原因」をご覧ください。

その他の条件による色付け

• 重複：COUNTIF関数を使った重複データの色付けは「重複データに色付けする方法」をご覧ください。
• 最大値：MAX関数を使った最大値の色付けは「最大値に色を付ける方法（行ごと・列ごとも）」をご覧ください。
• 最小値：MIN関数を使った最小値の色付けは「最小値を色付け・抽出する方法」をご覧ください。
• 順位：RANK関数を使った順位別の色付けは「ランキング(順位)で色付け・抽出する方法」をご覧ください。
• 土日：WEEKDAY関数を使った土日の色付けは「土日に色を付ける方法」をご覧ください。
• 祝日：COUNTIF関数を使った祝日の色付けは「「祝日」に色を付ける方法【祝日リスト付】」をご覧ください。
• 今日の日付：TODAY関数を使った今日の日付の色付けは「今日の日付に色を付ける方法」をご覧ください。
• 交互の背景色：MOD関数を使った交互の背景色の付け方は「交互の背景色をつける方法」をご覧ください。