今回は、Excelの条件付き書式で、別のシートにあるセルの値を参照して色を付ける方法を紹介します。
条件付き書式で別シートを参照するメリット
Excelの「条件付き書式」は、セルの値が特定以上の数字になったり、特定の文字が入力されたりした時に、自動的にセルの背景色や文字色を変更してくれる視覚的に非常に便利な機能です。
通常、条件付き書式は「同じシート内」にあるデータを元にして色付けのルールを設定しますが、実務においては「設定用の基準値（例えば今月の売上目標額や、ステータスのマスタデータなど）」を、メインの入力シートとは別の専用シートに分けて管理することがよくあります。
設定用の数値を別シートに隔離しておくことで、メインシートのレイアウトがすっきりし、誤って基準値を書き換えてしまうミスを防ぐことができます。
このように、別シートにある基準値を参照してメインシートに条件付き書式を適用するテクニックを身につけると、より安全で保守性の高いExcelファイルを作成することが可能になります。
別シートのセルを参照する条件付き書式の設定手順
条件付き書式で別のシートのセルを参照するには、あらかじめ用意されたメニューから選択するのではなく、「数式」を使って独自のルールを定義する必要があります。
対象となるセル範囲を選択する
まずは、色を付けたい（条件付き書式を適用したい）セルが含まれるメインのシートを開きます。
- メインシート上で、色を自動的に変化させたいセルの範囲（例えば、売上金額が入力されている列など）をドラッグして選択する
- 画面上部のリボンメニューから「ホーム」タブを開く
- 「スタイル」グループの中にある「条件付き書式」をクリックする
- 表示されたメニューの中から「新しいルール」を選択する
数式を使用して書式を設定する
「新しい書式ルール」というダイアログボックスが表示されたら、ルールの種類を指定します。
- 「ルールの種類を選択してください」のリストから、一番下にある「数式を使用して、書式設定するセルを決定」をクリックする
- 「次の数式を満たす場合に値を書式設定」という入力欄が表示される
ここに入力する数式が、別シートを参照するための鍵となります。
別シートのセルをクリックして数式を作成する
数式の入力欄に直接文字を打ち込むこともできますが、マウス操作を組み合わせることで、より正確に別シートへの参照（シート名とセル番地）を入力することができます。
- 入力欄の中を一度クリックして、カーソルを点滅させる
- キーボードから「=（イコール）」を入力する
- 次に、比較の対象となるメインシートの先頭セル（例えばA2セルなど）をクリックする
- 入力欄に「=A2」と表示された後、比較演算子（「>」「=」とする
- そのままの状態で、画面下部のシート見出しタブをクリックし、基準値が入力されている別のシート（設定シートなど）に切り替える
- 別のシートに切り替わったら、基準となる数値が入力されているセル（例えばB2セルなど）をクリックする
この一連の操作により、入力欄には「=A2>=設定シート!$B$2」といった数式が自動的に完成します。
シート名にスペースなどが含まれている場合は、「’設定
シート’!$B$2」のようにシングルクォーテーションで囲まれる形になりますが、Excelが自動で処理してくれるため心配はありません。
数式内の絶対参照（$マーク）を調整する重要性
数式を使って条件付き書式を設定する際、最もつまずきやすいのがセル番地につく「$（ドルマーク）」の扱い、すなわち「絶対参照」と「相対参照」の使い分けです。
メインシート側のセル番地の調整
先ほど「=A2」と入力した際、もしマウスでクリックして入力した場合は「=$A$2」と表示されることがあります。
これでは、選択したすべてのセルが「常にA2セルの値」だけを見て判断するルールになってしまい、A3、A4と行が下がるにつれて判定がずれてしまいます。
- 数式入力欄の中にある「$A$2」という部分をクリックする
- キーボードのF4キーを数回押して、「A2」または「$A2」（列だけ固定）に変更する
行番号の前の$マークを外す（相対参照にする）ことで、条件付き書式のルールが下に向かってコピーされる際、A3、A4と判定対象のセルが正しくスライドしていくようになります。
別シート側のセル番地の調整
一方で、別シートにある基準値のセル（例：設定シート!$B$2）は、どの行を判定する時でも常に同じ場所を見てほしいため、「$B$2」のまま（絶対参照）にしておくのが基本です。
この「自分側のセルは動かす（相対参照）」「相手側（別シート）のセルは固定する（絶対参照）」というルールを意識することが、条件付き書式を正しく機能させるための最大のコツとなります。
書式（色やフォント）を設定して完了する
数式の入力と$マークの調整が終わったら、最後に条件を満たした時の見た目の変化を設定します。
- ダイアログボックスの右下にある「書式」ボタンをクリックする
- 「セルの書式設定」ダイアログボックスが開くので、「塗りつぶし」タブから背景色（例えば薄い赤色や黄色など）を選ぶ
- 文字色を変えたい場合は「フォント」タブで色を変更する
- 「OK」ボタンをクリックして「セルの書式設定」を閉じる
- もう一度「OK」ボタンをクリックして「新しい書式ルール」ダイアログを閉じる
これで設定は完了です。メインシートの数値が、別シートの基準値の条件を満たした場合にのみ、指定した色に自動的に変わる様子が確認できるはずです。
名前の定義を活用して数式をシンプルにするテクニック
別シートを参照する数式は、「設定シート!$B$2」のように少し長く、複雑な見た目になりがちです。
これをすっきりと分かりやすく管理するために、「名前の定義」という機能を組み合わせるテクニックがあります。
別シートのセルに名前を付ける
- 別シート（基準値があるシート）を開き、対象のセル（B2など）を選択する
- 画面左上の「名前ボックス（セル番地が表示されている白い枠）」をクリックする
- 「基準値」や「目標額」など、分かりやすい名前を入力してEnterキーを押す
条件付き書式で定義した名前を使う
名前を付けた後、メインシートに戻って条件付き書式の数式を再設定します。
- 数式入力欄に、「=A2>=基準値」のように入力する
このように、「シート名!セル番地」の部分を、先ほど定義した「名前」に置き換えることができます。
数式が短く直感的になるだけでなく、後から基準値の場所を別のセルに移動させたとしても、「名前」の参照先を変更するだけで済むため、メンテナンス性が大きく向上します。
別シートのリストと照合する（COUNTIF関数の応用）
1つの基準値との大小を比較するだけでなく、「別シートにある社員名簿リストの中に、入力された名前が含まれていれば色を付ける」といったケースでも、条件付き書式は活躍します。
このような場合は、COUNTIF（カウントイフ）関数を数式の中に組み込みます。
COUNTIF関数を使った別シート参照の数式
- 別シートのリスト範囲（例：社員名簿シートのA2:A100）に、「名簿リスト」という名前を定義しておく
- メインシートの色を付けたい範囲を選択し、「数式を使用して、書式設定するセルを決定」を開く
- 数式入力欄に、「=COUNTIF(名簿リスト, A2)>0」と入力する（A2はメインシートの先頭セル）
- 書式（色）を設定して「OK」をクリックする
この数式は、「入力された名前が別シートのリストの中に1つ以上（>0）存在するか？」を判定しています。
休日のリストと照合してカレンダーに色を付けたり、NGワードのリストと照合して入力ミスを防いだりなど、さまざまな業務に応用できる強力な組み合わせです。
まとめ
Excelの条件付き書式で、別のシートの値を参照する方法とその応用テクニックについてお伝えしました。
「数式を使用して、書式設定するセルを決定」のメニューを選ぶこと、そして相対参照と絶対参照（$マーク）を適切に使い分けることが、設定を成功させるための重要なポイントとなります。
さらに「名前の定義」やCOUNTIF関数などを組み合わせることで、より複雑で実用的なチェック機能をExcelファイルに持たせることが可能になります。
基準となるマスタデータを別シートに分けて管理する習慣は、ファイルの信頼性を高め、複数人で共有して使う際のトラブルを未然に防ぐことにつながります。
このテクニックを活用し、視覚的に分かりやすく、かつ保守性の高いExcelフォーマットの作成に役立てていけることと思います。