Google Sheets約5分で読めます

スプレッドシートの条件付き書式|カスタム数式の書き方

スプレッドシートの条件付き書式を表したイメージ
記事の内容を表したイメージ(AI生成)

条件付き書式の「カスタム数式」を設定しようとしたとき、色が一切つかない、関係のないセルまで塗られる、という状態になって手が止まることがあります。あらかじめ用意されている「次より大きい」「テキストに含む」といった条件では対応できない場合に使うのがカスタム数式ですが、書き方にいくつか固有のルールがあり、そこを外すと意図どおりに動きません。

この記事では、カスタム数式が動かない原因の多くを占める「参照の固定の誤り」と「範囲とずれる先頭セルの指定」を中心に、書き方の構造を説明します。続けて、行全体を塗る・別シートの値と照合するといった実務でよく使うパターンを具体例で示します。画面の表示は版や設定により異なりますので、操作名は一般的な呼び名にとどめます。


カスタム数式が「色がつかない」「全部塗られる」原因

条件付き書式のカスタム数式では、入力した数式がTRUE(真)を返すかどうかでセルを塗るかを決めます。数式がFALSEまたはエラーを返せば何も起きません。

色がまったくつかない場合、よくある原因は次の三つです。

  • 数式が常にFALSEを返している(比較対象のセル参照や値が違う)
  • 数式をテキストとして入力している(先頭に=を書いていない)
  • 参照しているセルが空のまま

逆に「全部のセルが塗られる」場合は、範囲内すべてのセルに対して数式がTRUEになっている状態です。参照を固定すべき列や行を固定していないか、固定しすぎているかのどちらかがほぼ原因です。


参照の固定がカスタム数式の要

条件付き書式のカスタム数式は、設定した範囲の先頭セルに対して書くという原則があります。Googleスプレッドシートは、その数式を範囲内の各セルにコピーするように自動でずらして解釈します。

たとえば、A1:C10 に書式を設定するとき、カスタム数式の参照は A1 を起点に書きます。Excelの数式と同じく、$を付けない参照は行も列も相対的にずれ、$を付けた参照はずれません。

行全体を同じ条件で塗りたいとき

「D列の値が"未処理"なら、その行全体(A〜D列)を塗りたい」という場合を考えます。

設定する範囲を A1:D10 とすると、カスタム数式は次のように書きます。

=$D1="未処理"

$D とすることで、列だけを固定します。行番号の 1 には $ をつけません。こうすることで、2行目のA〜D列では $D2="未処理"、3行目では $D3="未処理" と行がずれて解釈され、各行の状態を正しく参照できます。

もし =$D$1="未処理" と書くと、範囲内のすべてのセルが常にD1のみを参照します。D1が"未処理"であれば全セルが塗られ、D1が違う値なら何も塗られません。

もし =D1="未処理" と $ をまったく書かなければ、列も行も相対的にずれるため、B2のセルを評価するときにはE3を参照するといったずれが起き、意図しない結果になります。

列ごとに条件を変えず、特定列だけを塗りたいとき

「D列のセルだけを、D列の値が"完了"なら塗りたい」ときは、設定する範囲を D1:D10 にして数式を次のように書きます。

=$D1="完了"

範囲が単一列なので、$D としても =D1 としても結果は同じになりますが、列固定を明示しておくと後から見たときに意図が伝わりやすいです。


よく使うパターンと具体例

数値の大小で行を塗る

架空の例です。A列に担当者名、B列に件数が入った一覧があるとします。件数が10件以上の行全体を塗りたいとき、範囲を A1:B20 として次のように書きます。

=$B1>=10

B列の数値が10以上のとき、その行のA〜B列が塗られます。B列が空白の行はFALSEとして扱われ、塗られません。

今日の日付より前の日付を色で警告する

C列に期限日(日付の値)が入っているとします。範囲を C1:C30 として、期限が今日より前のセルを塗りたいときは次のように書きます。

=$C1<TODAY()

TODAY() は数式を評価した当日の日付を返します。C列が空白のセルは日付として扱われないため、通常は塗られません。ただし、セルに見た目上は空白でもスペースが入っている場合は挙動が変わることがあります。空白セルを除外したいときは次のように組み合わせます。

=AND($C1<>"", $C1<TODAY())

別の列のどれかに一致したら塗る

A列の値が、E列に列挙された特定の語句のどれかに一致するセルを塗りたい場合(架空の例:E列に「休止」「停止」「保留」が入っている)は COUNTIF を使います。範囲を A1:A30 として次のように書きます。

=COUNTIF($E$1:$E$10,$A1)>0

$E$1:$E$10 は参照範囲が固定なので行も列も $ でとめます。$A1 は行を相対、列を固定にします。COUNTIF は一致する数を返すため、1以上であればTRUEと判定されます。


迷いやすい例外:先頭セルがずれているとき

範囲を A3:D20 のように途中の行から設定するときも、カスタム数式はその範囲の先頭セル、つまり A3 を起点に書きます。A1からではありません。

=$D3="未処理"

行番号を 1 から書くと、実際に書式が当たるセル(3行目以降)とずれた行を参照します。「なぜか1行目や2行目のデータに反応してしまう」という現象はここが原因であることが多いです。

設定前に範囲の先頭セルを確認してから数式を書く習慣をつけると、この種のずれを防げます。


設定の手順と確認方法

  1. 書式を設定したい範囲を選択します。
  2. メニューから「表示形式」→「条件付き書式」を開きます。
  3. 「条件を追加」または既存のルールを選択します。
  4. 条件の種類で「カスタム数式」を選びます。
  5. 入力欄に = から始まる数式を入力します。
  6. 書式(塗りつぶし色など)を設定して「完了」をクリックします。

確認のとき、数式が意図どおりに動いているかを簡単にチェックするには、範囲の外の空きセルに同じ数式を貼り付けて TRUE / FALSE が返ってくるかを見る方法があります。たとえば E1 のセルに =$D1="未処理" と入力し、D1 の値を変えて TRUE になることを確かめてから条件付き書式に設定すると、参照のミスに早く気づけます。

作業前にファイルを複製してバックアップを取っておくと、設定を間違えたときに元の状態に戻せます。


次の行動

まず「色が一切つかない」か「全部塗られる」かを確認し、それぞれ「数式がTRUEになっているか」「$の位置が正しいか」を先に切り分けてください。先頭セルを空きセルに数式を貼って TRUE / FALSE を確かめる方法で、数式の問題か参照のずれかをすぐに判断できます。設定範囲の先頭セルの行番号と、数式内の行番号が一致しているかをあわせて見てください。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ