スプレッドシートの条件付き書式で文字や背景の色を変える方法

スプレッドシートに入力されたテキストや値に応じて、自動で文字や背景の色を変わるようにしたいと思ったことはありませんか？そんな時に便利な条件付き書式の設定方法と便利な使い方を、実例を用いて解説していきます。

条件付き書式とは

条件付き書式とは指定した条件に応じて見え方を変える約束事のことです。 簡単に説明すると、『〇〇だったら✗✗』という〇〇が条件、✗✗が書式設定（見え方）となります。

まずは条件付き書式の基本設定を説明してから、数式を使用した条件付き書式設定と文字列を使用した書式設定について、実例をまじえながら解説していきます。条件付き書式についての基本操作をすでに理解している方は目次より該当箇所へ飛んでください。

条件付き書式の基本的な設定方法

条件付き書式の設定をするには、適用させたい範囲を選択した上で

メニューの『表示形式』→『条件付き書式』

または

右クリック→『条件付き書式』

のどちらかでブラウザウインドウ右側に『条件付き書式設定ルール』エリアを表示することができます。

この画面で指定した範囲に対して『書式設定の条件』と『書式設定のスタイル』を設定していくことになります。なお、デフォルトでは『空白ではないセル』に対して緑色の背景色が設定されていますが不要なので書き換えて作成を始めるか『キャンセル』をクリックしてください。

セルの書式設定の条件

セルの書式設定条件には以下のものがあります。

沢山あって圧倒されてしまうかもしれませんが、大きく分類すると、テキスト、日付、数値、カスタム数式の４つに分類できます。

書式設定のスタイル

書式設定のスタイルでは、条件にあったものに対して適用させる文字やセルの装飾を設定します。書式設定をある程度使えるようになると必要以上に色を使ってしまいがちですが、視認性を損なわずに見やすい資料を作るには、セルの背景色に濃い色を使用しない、色数を絞る、色の系統や濃淡を揃えるなどして色味に一貫性をもたせると良いでしょう。

テキストの条件付き書式

それでは『書式設定の条件』を一つずつ確認していきましょう。 まずはテキストに関わるものから。

テキストを含むのか、含まないのか、最初か、最後か、完全一致かの５つですね。 使いみちとしては、合格、不合格という文字列によって文字色を変えるとか、アンケートの集計データなど、大量のテキストが入力されている書類の中から、特定の単語を含むセルを太字にしたり、背景色を設定して視認性を高めるというような使い方が想定できます。

数値の条件付き書式

次は数値に関する書式設定の条件です。

範囲内のセルに入っている値より数値が大きかったり、小さかったりといった条件なので特に難しく考える必要はありません。以上、以下、未満、超過といったどのような基準なのかだけ、しっかり決めておきましょう。

使い道としては、日報や売上管理シートなどで数値目標を達成してるかどうかで見た目を変えるとか、基準点によって段階的に色を変えるなどの使い方が想定できます。

セルに入力されている数値を基準値によって色分け

テストのスコアなど成績によって色分けする時などに便利です。

例：テストで80点以上は合格（背景緑）、30点以下は赤点（背景赤、赤字） 最初に範囲を指定してから条件付き書式設定を開き、書式設定の条件を80点以上に、背景色を薄緑を設定、さらに条件を追加して、条件を30点以下に設定、背景色として薄い赤、文字色も赤字に設定しています。

数値に関しては条件を0以下にすることで文字通り損益の赤字を赤色にしたり、googlefinance関数と組み合わせることで、前日の株価との差が目立つものを視覚的に見やすくしたりといった使いみちがあります。

スプレッドシートで株価や為替レートを取得し表示させる方法

スプレッドシートで資産管理を行うために株価や為替を取得して表示させる方法について説明します。

日付に絡んだ条件付き書式

日付に応じた条件付き書式では、締切が翌日のものを目立たせたり、締切日を過ぎたものの色をグレーアウトしたりといった使い方が想定できます。

個人的にはあまり使うことはありませんが、締切やスケジュール、進捗管理などが重要な職種では使い勝手がよく、使用頻度も高いのではないでしょうか。

カスタム数式を用いた条件付き書式

ここまでの解説で、条件付き書式設定が便利なことがわかってもらえたと思いますが、正直に言うとこれまで紹介した条件付き書式はあまり使いません。

カスタム数式を組み合わせることでもっと自由度の高い条件付き書式設定が可能となり、カスタム数式こそが条件付き書式の真骨頂といっても過言ではないからです。 いくつか具体的な例を紹介していきます。

チェックボックスにチェックが入った行に色をつける

例としてチェックリストを活用したシンプルなto doリストを作成してみました。チェックを入れるとその行がグレーアウトするようになっています。

チェックボックスはチェックされている状態がTRUE,外れている状態でFALSEの値を返します。 例えばBの列にチェックボックスがある場合、『=$B3=true』とすることで『B列にあるチェックボックスにチェックが入っていれば』ということになります。

=$B3=true

スプレッドシートのチェックボックスに関してはこちらの記事で解説しています。

スプレッドシートのチェックボックス作成、色の変更やカウント方法

スプレッドシートではチェックボックスを簡単に作成できます。チェックボックスの作成方法と、選択時に色を変えたり、チェック済みの数をカウントする方法についても詳しく解説していきます。

シフト表で土日の列に色をつける

シフト表で土日のセルは自動的に背景色をつけるようにします。

自分がスタッフ10名〜15名程度のシフト表を作成していた時に使っていました。 ※実際のものはもっと沢山の計算式が入っていますが、説明のためにかなりシンプルにしています。

範囲を指定した上で、土曜日用と日曜日用に2つの条件付き書式を設定しています。

土曜日は4行目から『土』を探したい

=C$4=”土”

日曜日は4行目から『日』を探したい

=C$4=”日”

なお適応する範囲は間の空白の部分があるため下記のように,（カンマ）で区切って二つの範囲を指定しています。

C3:AF15,C18:AF21

曜日を自動判別して色をつける

日付が入力されたセルに対して曜日を判別し、文字色や背景色を変えたい時はカスタム数式にWEEKDAY関数を使用することで可能です。WEEKDAY関数は1つ目の引数に対象セル、2つ目の引数に曜日を指定します。1は日曜日で、7まで数字が増えるごとに月から土となっています。下のサンプルでは日曜日を指定していることになります。

=WEEKDAY(E7,1)=1

二つのセルの値を比較して色をつける

よく利用する条件付き書式の利用例として、セルの値を基準となるセルの値より大きいか小さいかで色をつけることがあると思います。

シンプルなケースとして基準値を満たしていれば背景を緑に、満たしていなければ赤になるようなものを見ていきましょう。

C4からC15の範囲に対してカスタム数式を用いた条件付き書式を二つ設定しています。

=$C3<$C$2

=$C3>$C$2

なお参照セルに含まれる『$』は絶対参照として参照セルを固定するためのものです。

スプレッドシートで計算式コピー時に参照セルの行や列を固定する方法

スプレッドシートで計算式や関数の含まれるセルをコピーした時に計算式内の参照セルが意図せずズレてしまったことはありませんか？そんな時は絶対参照を複合参照を適切に使うことで解決できます。『$』をうまく使って意図しない参照値のズレを避ける方法について説明します。

セルの値を比較して色をつけることで、前年比や前月比の増減などが視覚的にわかりやすくなります。

その他のTIPS

ワイルドカードを使った文字検索

ワイルドカードを使用して条件を指定することで、文字列を扱う条件付き書式の設定に幅ができます。

?（疑問符）で任意の１文字

例えば文字列に『?』を入れると任意の１文字が挟まれた文字だけが条件に一致するようになります。

*（アスタリスク）で任意の数文字

間に任意の数文字を条件として指定したい場合は『*』（アスタリスク）を使用します。

例：下記のテキスト範囲に『?』と『*』を用いて条件付き書式を設定してみました。

• アイス
• アリス
• アムス
• アキレス
• アリストテレス
• アリとキリギリス

『?』や『*』を条件として指定したい場合は~（チルダ）

『?』や『*』自体を条件にしたい時はエスケープ文字として『~』を文字の前につけることで条件として指定できます。

カラースケールの設定

一致条件に幅をもたせたい場合、カラーの濃淡で視覚的にわかりやすくしてくれるのがカラースケールです。

例：東京都23区の人工と土地の面積をカラースケールで色付けしたものです。

『カラースケール』を選択し、プレビューのところで色を切り替えるだけで使えますが、色や見せ方にこだわりたい場合は個別に設定すると良いでしょう。

条件付き書式をコピーする方法

セルによっては条件付き書式と一緒に数式が入力されていることは珍しくありません。そのようなセルから条件付き書式のみをコピーしたい時の手順は、通常通りセルをコピーし、貼り付けたい箇所を選択した状態で右クリックして『特殊貼付け』→『条件付き書式のみ貼り付け』を選択することで可能となります。

条件付き書式の優先順位と削除（取り消し）

セルに勝手に色がついてしまったり、意図しないスタイルが適用されてしまう場合は、条件付き書式の優先順位が正しくないか、余計なものが反映されてしまっている可能性が高いです。 該当のセルで右クリックして『条件付き書式』を開き、どのようなものが適用されているかを確認しましょう。

条件付き書式は上にあるものが優先される

条件書式は上にあるルールが優先されて適用されます。同じ適用条件のものがあっても下のものによって上書きされるわけではないので注意が必要です。 条件付き書式リストはドラッグして並び替える事が可能です。 例として、同じセルに３つの条件付き書式を適用させ、並び替えていますが、一番上に来た書式の背景色に変わっているのが確認できると思います。

条件付き書式を削除、取り消したい場合

条件付き書式を取り消したい時は、ゴミ箱アイコンを押して削除することができます。

この時注意してほしいのが、セルから条件付き書式を表示させたとしても、削除する場合はセル単位ではないということです。条件付き書式自体の削除となりますので、他のセルにもその書式が適用されている場合、他のセルも影響を受けることになります、特定のセルに条件付き書式が反映されないようにしない場合は、範囲からそのセルを除外するようにしましょう。

まとめ

スプレッドシートの条件付き書式の設定方法について解説してきました。条件付き書式は使いこなせるようになると視認性が高く、きれいな書類をつくることができますので、ぜひ活用していただきたい機能です。

スプレッドシートの使い方については下記の記事でまとめていますので併せてお読みください。

スプレッドシートの使い方 完全マニュアル

スプレッドシート初心者が一通り使えるようになるまでの知識を体系的にまとめました。