スプレッドシートの条件付き書式の使い方!特定の値に自動で色付けする方法など解説!

スプレッドシートの大事なセルやミスしたセルに色をつける必要があることは少なくありません。しかしその作業を手動より楽に間違いなく行える、条件付き書式を利用している人は数えるほどです。今回は、スプレッドシート上の作業を楽にする条件付き書式の利用方法をお伝えします。

スプレッドシートの条件付き書式の使い方!特定の値に自動で色付けする方法など解説!のイメージ

目次

  1. 1Googleスプレッドシートの条件付き書式の機能とは?
  2. スプレッドシートの条件付き書式とは
  3. 2Googleスプレッドシートの条件付き書式の活用方法
  4. 日付や曜日分けに最適
  5. 重複しているデータを見つけられる
  6. 条件付き書式をコピーして使える
  7. 3スプレッドシートの条件付き書式の使い方
  8. 条件で色を付けたい入力範囲を選択
  9. 右クリックをして条件付き書式を選択
  10. 背景色を変えたい条件を設定
  11. 4スプレッドシートで行全体の色を変更する方法
  12. スプレッドシートの行全体を変更する手順
  13. スプレッドシートの書式設定を削除する手順
  14. スプレッドシートの行の色を変えるときには範囲に注意
  15. 5スプレッドシートの条件付き書式の条件が複数ある場合
  16. 条件をすべて満たすときは【AND】
  17. 条件のいずれかを満たせばいいときは【OR】
  18. 条件のすべてが満たされないときは【NOT】
  19. 6スプレッドシートの条件付き書式を使って作業を楽にしよう

Googleスプレッドシートの条件付き書式の機能とは?

条件付き書式設定の画面

Googleスプレッドシートには多くの機能がありますが、その中で、最もかける手間の割に効果が大きい機能は「条件付き書式」機能であると断言していいでしょう。

度条件付き書式を設定しておけば、これまで入力されたデータだろうが新たに入力されたデータだろうが、しっかりとチェックしてセルに色をつけたり太字にしたりなどの書式を変更してくれるのです。これまでひとつひとつのセルを目で追って対応していたことを思えば、条件付き書式を設定したスプレッドシートとの効率の差は比べるべくもありません。

スプレッドシートの条件付き書式とは

工業用ロボット

Googleスプレッドシートの「条件付き書式」とは、その名の通り「条件」を満たした場合に指定した「書式」を施すというものです。

たとえば、記入漏れのセルを赤色の背景にしたり、平均未満の数値を赤字で示したり、未達成の項目のある行をオレンジ色の背景で埋め尽くしたり、チェック欄が「完了」ならば行全体の文字をグレー表記したりといったことを自動で行えるのです。

いずれも、「空白」という条件なら「背景を赤にする」という書式、「そのセルの数値が平均未満」という条件なら「文字を赤にする」という書式、というふうに、一度条件付き書式を設定するだけでスプレッドシート上の書式が自動的に処理されていくのです。

Googleスプレッドシートの条件付き書式の活用方法

PCを操作する女性

Googleスプレッドシートの条件付き書式は、簡単な条件から関数を使った複雑な条件までさまざまな条件を設定できます。関数を使う、となると敷居が高く感じられますが、あらかじめ用意されている「空白セル」「空白ではないセル」「次を含むテキスト」「完全一致するテキスト」などを使うだけでも、一度に利便性が増します

以下にこうした条件付き書式の利用例を挙げます。

日付や曜日分けに最適

スプレッドシートの条件付き書式による曜日分け

カスタム数式を利用して日付から曜日を求め、土曜には青色の、日曜には赤色の背景を施した条件付き書式の例です。この例ではたまたま日付が連続していますが、日付が飛び飛びになっていてもしっかりと土日を判断して、その日付の行に色付けをしてくれます。

この例を見ればわかる通りGoogleスプレッドシートでは、カスタム数式の土曜の条件付き書式と日曜の条件付き書式というふたつのルールを並べることで、複数条件を組み合わせた条件付き書式も設定できます

また、難しい関数をつかわずとも、あらかじめ用意されている「日付(その日付と完全一致する日付のこと)」「次より前の日付」「次より後の日付」を使ったり、これらを複数条件で組み合わせたりすることで日付を条件とした条件付き書式は容易に設定できます。

さらに関数を使ったカスタム数式を利用する方法で、偶数週と奇数週で背景の色を変えたり、過去の日付の行をグレー表記したりといったことが簡単にできるのです。

重複しているデータを見つけられる

スプレッドシートの条件付き書式による重複データ確認

スプレッドシート上でデータ入力をしていると、データの重複や入力漏れが大きな問題となります。なかでも重複データの確認は人間の目で追うには非常に厳しいものです。しかしGoogleスプレッドシートの条件付き書式機能を使えば、重複データの確認はきわめて容易なものとなります。

上図の例では、同じものの個数を数えるcountif関数をカスタム数式に使って、同じ品名が2個以上ある列を緑色の背景にしています。「ボールペン(赤)」「ノート」「クラフトテープ」の3種類が同じ品名で登場しているのがわかります(赤色、オレンジ色、青色のアンダーラインは見易くするために追加で処理したものです)。

条件付き書式をコピーして使える

コピー機

こうして苦労して作ったカスタム数式入りの条件付き書式も1回切りしか使えず、スプレッドシートを作るたびに手間をかけて入力し直さなければならないとしたら、折角の効率の高さが無駄になってしまいます。しかしGoogleスプレッドシートでは、条件付き書式をコピー&ペーストできるので、そんな無駄は必要ありません。

条件付き書式をコピー&ペーストするためには、条件付き書式を施した適当なセルで「コピー」した後、条件付き書式を施したい範囲を選び、そこで右クリック→「特殊貼り付け」→「条件付き書式のみ貼り付け」を選ぶだけです。

スプレッドシートの条件付き書式のコピー01

するとカスタム数式か否かにかかわらず、条件付き書式が指定した範囲に施され、条件付き書式設定ルールの部分にも、新しい範囲(図の場合はA20〜E31)が追加されたことがわかります。

スプレッドシートの条件付き書式のコピー02

一度作った条件付き書式をコピー&ペーストすることで、無駄な入力を省いて、どんどん楽をすることができるのです。

スプレッドシートの条件付き書式の使い方

ここまではGoogleスプレッドシートの条件付き書式の利用方法をお伝えしてきましたが、ここからは条件付き書式を実際に設定する方法を説明していきます。以下の節では、文具の購入記録用スプレッドシートを例に、記入漏れの空白のセルを赤色で表示する条件付き書式を設定する方法を順に説明します。

条件で色を付けたい入力範囲を選択

条件付き書式を設定するには、条件によって色をつけたい入力範囲を指定します。下図の例では、列をクリックしてC列全体を指定します。

スプレッドシートの条件付き書式の使い方01

右クリックをして条件付き書式を選択

範囲を指定したら、その上で指定範囲のどこかで右クリックしてコンテキストメニューを表示させ、「条件付き書式」を選択します。

スプレッドシートの条件付き書式の使い方02

背景色を変えたい条件を設定

すると画面の右側に「条件付き書式設定ルール」のウインドウが開きます。デフォルトでは「セルの書式設定の条件」が「空白ではないセル」になっているため、ここを「空白セル」に変更します。

スプレッドシートの条件付き書式の使い方03

「セルの書式設定の条件」を「空白セル」に変えたことで、さっきとは反対部分に書式が設定されました。しかし現在のライトブルーの背景色では今一つ強調度が弱いので、これを変えることにします。

スプレッドシートの条件付き書式の使い方04

条件付き書式で設定できる書式は、左から「B(太字)」「I(斜体)」「U(下線)」「S(取り消し線)」「A(文字色)」「バケツ(背景色)」です。これらを使って書式を変更すると、その上の「デフォルト」と表示された部分が書式に合わせて見え方が変わります。

スプレッドシートの条件付き書式の使い方05

「バケツ」をクリックして背景色のパレットをポップアップさせ、赤色を選びます

スプレッドシートの条件付き書式の使い方06

すると空白セルが真っ赤に表示され、入力ミスがひと目でわかるようになりました。この赤色はC8の空白セル自体についているわけではないため、C8に適当な数値が入力されると「空白セル」ではなくなって赤い背景色は消えます

スプレッドシートの条件付き書式の使い方07

スプレッドシートで行全体の色を変更する方法

前章では、Googleスプレッドシートの1列だけを範囲指定して条件付き書式を設定する方法をお伝えしました。この章ではある程度の行と列を指定して、そこにカスタム数式を使った条件付き書式を設定する方法をお教えします。

スプレッドシートの行全体を変更する手順

行全体を一度に書式設定するためには、まず複数の行と列を範囲指定した上で条件付き書式で「カスタム数式」を設定します。

条件で色を付けたい入力範囲を選択

まずGoogleスプレッドシート上で、条件付き書式を施す範囲を指定します。下図ではA2〜E17までを指定しました。

スプレッドシートの条件付き書式設定01

指定した範囲内の適当な場所で右クリックをして、コンテキストメニューから「条件付き書式」を選択します。コンテキストメニューの項目はかなり多いので、メニュー内でスクロールしなければならないこともあります。

スプレッドシートの条件付き書式設定02

【セルの書式設定の条件】を「カスタム数式」に設定

画面の右側に「条件付き書式設定ルール」のウインドウが開きますが、「セルの書式設定の条件」が「空白ではないセル」になっています

スプレッドシートの条件付き書式設定03

空白ではないセル」をクリックしてプルダウンメニューを表示し、その中から「カスタム数式」を選択します。

スプレッドシートの条件付き書式設定04

【書式設定のスタイル】を設定して完了

「カスタム数式」では関数を使って自由に条件を設定できます。「値または数式」と記された欄に関数を入力します。

スプレッドシートの条件付き書式設定05

今回は品名が「ボールペン(赤)」の行を書式設定します。「値または数式」欄に「=$B2="ボールペン(赤)"」と入力して、B列に「ボールペン(赤)」を含む行を条件とします。

スプレッドシートの条件付き書式設定06

するとB列に「ボールペン(赤)」を含む行がライトグリーンに着色されます。「書式設定のスタイル」に手を加えれば、好みの書式に変更できます。

スプレッドシートの書式設定を削除する手順

今度は逆に、スプレッドシートの書式設定を削除する方法です。この場合は、まずGoogleスプレッドシートのメニューバーから「表示形式」をクリックして、プルダウンメニューから「条件付き書式…」を選択します。

スプレッドシートの条件付き書式削除01

すると画面の右側に「条件付き書式設定ルール」のウインドウが開きます。その中に、先ほど設定した条件付き書式のルールがありますので、その上にカーソルを運ぶと右側に現れる「ゴミ箱」アイコンをクリックします。

スプレッドシートの条件付き書式削除02

すると条件付き書式が削除され、表全体に施されていた書式もすべてなくなります。

スプレッドシートの条件付き書式削除03

スプレッドシートの行の色を変えるときには範囲に注意

Googleスプレッドシートの条件付き書式は非常に便利な機能ですが、指定している範囲に注意する必要があります。下図ではA2〜E17でB列に「ボールペン(赤)」が入力されている行について書式を変更しています。

スプレッドシートの条件付き書式範囲外01

そのため、B15に「ボールペン(赤)」を入力すると15行目に指定した書式が適用されますが、18行目のB18に「ボールペン(赤)」と入力してもA2〜E17の範囲外なので書式は適用されません

同様に、C16に「ボールペン(赤)」と入力しても、A2〜E17の範囲内ではあるものの、B列に入力されたわけではないので、16行目には条件付き書式は適用されないのです。

スプレッドシートの条件付き書式範囲外02

条件付き書式を設定したはずなのにうまくいかない場合には、まず指定した範囲を確認してみてください。

スプレッドシートの条件付き書式の条件が複数ある場合

Googleスプレッドシートの条件付き書式の条件は、複雑にしようと思えばどんどん複雑にできます。しかしこの章では、1段階だけステップをのぼるために複数条件を処理する「AND」関数、「OR」関数と、条件を否定するための「NOT」関数を使う方法をお教えします。

条件をすべて満たすときは【AND】

複数条件を同時に満たしたいのであれば、カスタム数式で「AND」関数を利用します。「AでもありBでもある」という複数条件に対しては「=AND(A,B)」と入力します。下図の例では、「B列の品名に「ボールペン(赤)」がある」という条件と「C列の個数が10個超」という条件を同時に満たす行を着色しています。

この場合、カスタム数式の欄に「=AND(($B2="ボールペン(赤)"),($C2>10))」と複数条件を入力します。

スプレッドシートのAND関数

5行目はこの複数条件を同時に満たしているためライトグリーンに着色されますが、同じ「ボールペン(赤)」であっても10行目はC列の個数が10個以下なので、複数条件を同時に満たせず着色されないという結果になります。

条件のいずれかを満たせばいいときは【OR】

「AND」関数とは異なり、複数条件のいずれかを満たしたものを選びたいのであれば、カスタム数式で「OR」関数を利用します。「AかBである」という複数条件に対しては「=OR(A,B)」と入力します。下図の例では、「B列の品名に「ボールペン(赤)」がある」という条件か「C列の個数が10個超」という条件のいずれかを満たす行を着色しています。

この場合、カスタム数式の欄に「=OR(($B2="ボールペン(赤)"),($C2>10))」と複数条件を入力します。

スプレッドシートのOR関数

前節では着色されなかった10行目が、複数条件の片方である「ボールペン(赤)」の条件を満たしたので着色されているのがわかります。

条件のすべてが満たされないときは【NOT】

カスタム数式で、指定した条件をすべて否定するのが「NOT」関数です。これを使うと、「NOT」関数を使わなかった場合と使った場合とで真逆の結果となります。試しに先の節で使った「=OR(($B2="ボールペン(赤)"),($C2>10))」を「NOT」関数で否定して、「=NOT(OR(($B2="ボールペン(赤)"),($C2>10)))」と入力します。

すると、先ほど着色されていた部分が着色されなくなり、着色されていなかった部分が着色されます。

スプレッドシートのNOT関数02

もっとわかりやすく、「=$B2="ボールペン(赤)"」を「NOT」関数で否定して、カスタム数式を「=NOT($B2="ボールペン(赤)")」とすると、品名が「ボールペン(赤)」でないすべての行が着色されます。

スプレッドシートのNOT関数01

「AND」「OR」「NOT」を組み合わせることですべての条件を処理できますので、この3つの関数だけは覚えて条件付き書式のカスタム数式を使いこなしてください。

スプレッドシートの条件付き書式を使って作業を楽にしよう

スプレッドシートの複数条件付き書式

Googleスプレッドシートに、今回説明した条件付き書式を使うことで、注目しなければならない部分がはっきりと表示されます上図のように、空白セルが赤くなるような条件付き書式を設定すれば記入漏れがすぐにわかりますし、注意しなければならない項目に色をつけることもでき、条件付き書式を複数設定すれば、さらに便利になります。

慣れないと、条件付き書式を設定するのは面倒に感じますが、一度設定すればあとは非常に楽になります。ぜひとも条件付き書式を利用して、あなたのスプレッドシート運用を快適なミスのないものに作り替えてください

関連するまとめ

Original
この記事のライター
julyten
PDAの頃から携帯機器やPC関係を扱ってきた旧きプロレス愛好家。

人気の記事

人気のあるまとめランキング

新着一覧

最近公開されたまとめ