漫画の地球儀

Googleスプレッドシートの条件付き書式の使い方!【日付、曜日分け、色、関数、記入漏れ】

スポンサーリンク

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

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

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

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

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

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

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

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

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

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

日付や曜日分けに最適

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

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

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

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

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

f:id:rick1208:20200622221457p:plain
スプレッドシート上でデータ記入をしていると、データの重複や記入漏れが大きな問題となります。なかでも重複データのチェックは人間の目で追うにはとても厳しいものです。しかしGoogleスプレッドシートの条件付き書式機能を使えば、重複データのチェックはきわめて容易なものとなります。

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

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

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

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

f:id:rick1208:20200622221516p:plain
するとカスタム数式か否かにかかわらず、条件付き書式が指定した範囲に施され、条件付き書式設定ルールの部分にも、新しい範囲(図の場合はA20〜E31)が追加されたことがわかります。
スプレッドシートの条件付き書式のコピー02
f:id:rick1208:20200622221530p:plain
1回作った条件付き書式をコピー&ペーストすることで、無駄な記入を省いて、どんどん楽をすることが可能なのです。

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

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

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

条件付き書式を設定するには、条件によって色をつけたい記入範囲を指定します。下図の例では、列を押してC列全体を指定します。
f:id:rick1208:20200622221634p:plain

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

範囲を指定したら、その上で指定範囲のどこかで右押してコンテキストメニューを出し、「条件付き書式」を選びます。
f:id:rick1208:20200622221839p:plain

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

すると画面の右側に「条件付き書式設定ルール」のウインドウが開きます。デフォルトでは「セルの書式設定の条件」が「空白ではないセル」になっているため、ここを「空白セル」に変更します。
f:id:rick1208:20200622221851p:plain
「セルの書式設定の条件」を「空白セル」に変えたことで、さっきとは反対部分に書式が設定されました。しかし現在のライトブルーの背景色では今一つ強調度が弱いので、これを変えることにします。
f:id:rick1208:20200622221900p:plain
条件付き書式で設定可能な書式は、左から「B(太字)」「I(斜体)」「U(下線)」「S(取り消し線)」「A(文字色)」「バケツ(背景色)」です。これらを使用して書式を変更すると、その上の「デフォルト」と表示された部分が書式に合わせて見え方が変わります。
f:id:rick1208:20200622221909p:plain
「バケツ」を押して背景色のパレットをポップアップさせ、赤色を選びます。
f:id:rick1208:20200622221921p:plain
すると空白セルが真っ赤に表示され、記入ミスがひと目でわかるようになりました。この赤色はC8の空白セル自体についているわけではないため、C8に適当な数値が記入されると「空白セル」ではなくなって赤い背景色は消えます。
f:id:rick1208:20200622221928p:plain

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

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

スプレッドシートの行全体を変更するやり方

f:id:rick1208:20200622221955p:plain
行全体を1回に書式設定するのでには、まず複数の行と列を範囲指定した上で条件付き書式で「カスタム数式」を設定します。

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

まずGoogleスプレッドシート上で、条件付き書式を施す範囲を指定します。下図ではA2〜E17までを指定しました。
f:id:rick1208:20200622222512p:plain
指定した範囲内の適当な場所で右クリックをして、コンテキストメニューから「条件付き書式」を選びます。コンテキストメニューの項目はかなり多いので、メニュー内でスクロールしなければならないこともあります。
f:id:rick1208:20200622222522p:plain

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

画面の右側に「条件付き書式設定ルール」のウインドウが開きますが、「セルの書式設定の条件」が「空白ではないセル」になっています。
f:id:rick1208:20200622222537p:plain
「空白ではないセル」を押してプルダウンメニューを表示し、その中から「カスタム数式」を選びます。
f:id:rick1208:20200622222546p:plain
「書式設定のスタイル」を設定して終了

「カスタム数式」では関数を使用して自由に条件を設定可能です。「値あるいは数式」と記された欄に関数を記入します。
f:id:rick1208:20200622222717p:plain
ここでは品名が「ボールペン(赤)」の行を書式設定します。「値あるいは数式」欄に「=$B2="ボールペン(赤)"」と記入して、B列に「ボールペン(赤)」を含む行を条件とします。
f:id:rick1208:20200622222725p:plain
するとB列に「ボールペン(赤)」を含む行がライトグリーンに着色されます。「書式設定のスタイル」に手を加えれば、好みの書式に変更可能です。

スプレッドシートの書式設定を消去するやり方

今度は逆に、スプレッドシートの書式設定を消去する方法です。この場合は、まずGoogleスプレッドシートのメニューバーから「表示形式」を押して、プルダウンメニューから「条件付き書式・・・」を選びます。
f:id:rick1208:20200622222735p:plain
すると画面の右側に「条件付き書式設定ルール」のウインドウが開きます。その中に、先ほど設定した条件付き書式のルールがありますので、その上にカーソルを運ぶと右側に現れる「ゴミ箱」アイコンを押します。
f:id:rick1208:20200622222742p:plain
すると条件付き書式が消去され、表全体に施されていた書式もすべてなくなります。
f:id:rick1208:20200622222749p:plain

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

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

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

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

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

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

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

条件をすべて満たすときは「AND」

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

この場合、カスタム数式の欄に「=AND*1」と複数条件を記入します。

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

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

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

この場合、カスタム数式の欄に「=OR*2」と複数条件を記入します。

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

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

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

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

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

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

まとめ

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

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

*1:$B2="ボールペン(赤)"),($C2>10

*2:$B2="ボールペン(赤)"),($C2>10

*3:$B2="ボールペン(赤)"),($C2>10