スプレッドシートの条件付き書式で、特定の値が入力されるとセルや行に色付けする方法

こんにちは、いろんな効率化が大好きなたぬ(@tanuhack)です!

スプレッドシートをチームで使うときに、『土日』や『祝日』といった値に色を付けたり、『未完』と入力されたセルに色を付けたりしていると思います。

1つだけ聞かせてください。

「もしかして、まだ、手動でそれらを色付けしていませんか?」

スプレッドシートの『条件付き書式』という機能を使えば、自分で設定した条件ごとに、セルや行に自動で色付けすることが出来るんですよ!

そこで今回は、条件付き書式の使い方実務で役立つテクニックを3つほど紹介します。

条件付き書式の使い方

次の2つの方法で条件付き書式を設定することが出来ます。

  1. [範囲を選択]>[右クリック]>[条件付き書式…]を選択
  2. [範囲を選択]>[表示形式]>[条件付き書式…]を選択

conditional-formatting1

すると、上の画像のように『条件付き書式設定ルール』というサイドバーが右に表示されるようになります。

ショートカットキーが無いのが残念で仕方ありませんが、地道にメニューからポチポチと表示させていきましょう。

conditional-formatting2
条件付き書式設定ルール
条件付き書式設定ルール
  1. 範囲に適用:条件付き書式を適用させたい範囲を設定。,(カンマ)で複数範囲設定できる
  2. セルの書式設定の条件…:ここがメイン。適用させたい条件を設定
  3. 書式設定のスタイル:背景色や文字色、太字、イタリック、取り消し線などいろいろ設定できる

②セルの書式設定の条件…について、もう少し深ぼってみましょう。

セルの書式設定の条件

デフォルトの設定では、『空白ではないセル』が適用されています。

conditional-formatting1
値が入力されているセルの背景色が緑に塗りつぶされている。

このような条件は、現状(2018年6月4日現在)19個用意されています。

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

実際に条件付き書式を使う場合は、この中から適したものを選択するという訳ですね。

まだ、あんまりイメージがつかないと思うので、3つほど具体例を紹介して、条件付き書式の便利さを実感していただこうと思います

土日と入力されたセルに色を付ける

conditional-formatting3

スプレッドシートでは、日々の売上管理や勤怠管理、タスク管理で日付データを取り扱うことって多いですよね。

「土日だと、ひと目でわかるように色付けしたい」そんなニーズに答えてみましょう。

STEP1:B列を選択する

conditional-formatting4

STEP2:セルの書式設定の条件で『次を含むテキスト』を選択

conditional-formatting5

STEP3:土と入力し、書式設定のスタイルを文字色を青、背景色を白にする

conditional-formatting6

これで土曜日の設定が完了しました。

続けて日曜日の設定をしてみましょう。

STEP4:条件を追加を選択

conditional-formatting7

STEP5:日と入力し、書式設定のスタイルを文字色を赤、背景色をなしにする

conditional-formatting8

土曜日と同様に設定します。

条件付き書式設定ルールが2つ作成されていることを確認したら完成です。

conditional-formatting9

特定の値以下のセルに色を付ける

conditional-formatting10

条件付き書式では、ある値以上、以下、未満など値の大きさを比較して色付けすることができます。

STEP1:C列を選択する

conditional-formatting11

STEP2:セルの書式設定の条件で『次より小さい』を選択

conditional-formatting12

STEP3:値を入力し、書式設定のスタイルを文字色を赤、背景色をなしにする

conditional-formatting13

未完と入力されたら行全体に色を付ける

conditional-formatting14

いままでのやり方では、単一のセルだけしか色付け出来ませんでした。

今から紹介するやり方を実践すると行全体を色付けすることが出来るようになります。

STEP1:A〜D列を選択する

conditional-formatting15

STEP2:セルの書式設定の条件で『カスタム数式』を選択

conditional-formatting16

カスタム数式では、通常のシートで使っている関数を使うことができます。

[aside]使える関数の補足
計算結果をTrueもしくはFalseと返す関数のみ使用できます。[/aside]

より柔軟に条件を設定したいときはカスタム数式を使うという訳ですね。

挫折する人多数!スプレッドシートの『カスタム数式』の使い方を徹底紹介

STEP3:『=$D1="未完"』と数式を入力し、スタイルも適用させる

conditional-formatting17

少し特殊な書き方なので、難しいと思われるかもしれません。

『$D1』でD列は固定で、行は1~Xで可変になるといった具合です。

この例では、D1からD24にかけて値が未完であれば、その行に対してスタイルが適用されます。

conditional-formatting18

ところが、他の条件付き書式ルールが被っているところの背景色が変わっていません。

次に、これを直していきます。

STEP4:条件付き書式設定ルールの優先順位を入れ替える

conditional-formatting19
優先順位設定のルールを入れ替え

条件の左の方にカーソルを当てるとカーソルが十字になって、ドラッグ&ドロップすると入れ替えができます。

左の方じゃないと動かないので注意が必要です。

STEP5:土曜かつ未完のルールを作成する

STEP3で作成したルールから『新しい条件を追加』を選択して、新たに条件を作成します。

conditional-formatting20

『条件Aかつ条件B』という条件を実現したいときは、AND関数を使用します。

AND関数の構文
=AND(条件1,条件2)

なので今回は、『=AND($D1="未完",$B1="土")』という複合条件を使用します。

STEP6:日曜かつ未完のルールを作成する

conditional-formatting21

土曜と同様にして、『=AND($D1="未完",$B1="日")』という複合条件を作成します。

STEP7:売上100万未満かつ未完のルールを作成する

conditional-formatting22

土曜と同様にして、『=AND($D1="未完",$C1<=1000000)』という複合条件を作成します。

最後に書式設定ルールの優先順位を正しく入れ替える作業を行います。

STEP8:条件付き書式設定ルールの優先順位を入れ替えて完成

conditional-formatting23
優先順位を入れ替え

最後に

今回は条件付き書式を使って、自分で設定した条件ごとに、セルや行に自動で色付けする方法を具体例3つと一緒に紹介しました。

見やすい表を高速で作成するのに欠かせない条件付き書式。ぜひ使い方をマスターされてください〜。

結構使う頻度多めな機能なので、ショートカットキーがあればもっと便利なんだけどなあ。

それでは。

【保存版】実務の効率化が加速するスプレッドシートのショートカットキー18選!