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

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

条件付き書式とは

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

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

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

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

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

または

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

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

条件付き書式設定とは

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

セルの書式設定の条件

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

セルの書式設定の条件
  1. 空白セル
  2. 空白ではないセル
  3. 次を含むテキスト
  4. 次を含まないテキスト
  5. 次で始まるテキスト
  6. 次で終わるテキスト
  7. 完全一致するテキスト
  8. 日付
  9. 次より前の日付
  10. 次より後の日付
  11. 次より大きい
  12. 以上
  13. 次より小さい
  14. 以下
  15. 次と等しい
  16. 次と等しくない
  17. 次の間にある
  18. 次の間にない
  19. カスタム数式

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

書式設定のスタイル

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

見やすい資料にするポイント
  • セルの背景色に濃い色を使用しない
  • 色を使いすぎない
  • 色の系統か、濃淡を揃え、一貫性をもたせる

テキストの条件

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

テキストに関する条件付き書式
  • 次を含むテキスト
  • 次を含まないテキスト
  • 次で始まるテキスト
  • 次で終わるテキスト
  • 完全一致するテキスト

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

数値で条件付き書式

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

数値に関する条件付き書式
  • 次より大きい
  • 以上
  • 次より小さい
  • 以下
  • 次と等しい
  • 次と等しくない
  • 次の間にある
  • 次の間にない

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

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

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

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

数値によって背景色と文字色を変える

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

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

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

日付に関する条件付き書式
  • 日付
  • 次より前の日付
  • 次より後の日付

日付に応じた条件付き書式では、長い期間のデータの古くなったものに色を付けたり、締切が翌日のものを目立たせたりといった使い方が想定できす。

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

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

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

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

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

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

To Doリストの完成イメージ

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

=$B3=true

条件付き書式を活用したToDoリスト
  1. 範囲を指定して、右クリックで『条件付き書式』
  2. 条件で『カスタム数式』を選択し、『=$B3=true』を入力
  3. スタイル(ここでは背景色)を設定

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

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

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

シフト表で土日を塗り分ける

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

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

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

=C$4=”土”

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

=C$4=”日”

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

C3:AF15,C18:AF21

その他のTIPS

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

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

?(疑問符)で任意の1文字

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

*(アスタリスク)で任意の数文字

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

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

  • アイス
  • アリス
  • アムス
  • アキレス
  • アリストテレス
  • アリとキリギリス
ワイルドカードを用いた文字列の判定

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

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

カラースケールの設定

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

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

カラースケール

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

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

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

手順
  1. 条件付き書式のはいったコピー元のセルを通常どおりコピー
  2. コピー先で右クリックして『特殊貼り付け』
  3. 『条件付き書式のみ貼り付け』

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

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

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

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

条件付き書式の優先順位と並び替え

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

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

条件付き書式の削除

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

まとめ

いかがだったでしょうか。条件付き書式は使いこなせるようになると視認性が高く、きれいな書類をつくることができますので、ぜひ活用していただきたい機能です。

条件付き書式のポイント
  • 条件付き書式設定とは、『範囲』を指定し、『条件』に応じて『スタイル』を反映させる約束事
  • 条件は、大きくわけて、テキスト、数値、日付、カスタム数式の4つ
  • 条件付き書式設定は上にあるものが優先される