Excelの条件付き書式の使い方｜条件・適用先・色の変化を確かめる

こんにちは！なかびーです。

Excelの表で、ある数より大きい値だけを見つけたいとき、一つずつセルを塗ると、後から数値を直した際に色の付け直しが必要になります。条件付き書式は、条件に合うセルへ書式を適用する機能です。ただし、色が付いたことだけを見て設定完了とすると、表の最後の行が対象外だったり、境界の値まで含めてしまったりする違いを見落とします。

この記事では、Windowsのデスクトップ版Excelを想定し、五行の架空の資材表で、条件、適用先、書式の三つを分けて確認します。数値セルだけの色分けから始め、値の変更、行をまとめて色分けする数式、ルールの競合、解除まで進めます。教材の名前と数量は説明用に作ったもので、実在する在庫、筆者の業務実績、実機で確認した測定結果ではありません。

操作の根拠はMicrosoft公式の条件付き書式の説明です。Web版やMac版では画面や設定の入口が異なるため、この記事のWindows向け手順を同じ画面の説明として読み替えないでください。手元で変更できる新しい練習用ブックを使い、条件から予想した結果と、実際の表示を比べます。

1．値・条件・書式を分け、五行の教材を用意する

色で変えるのは、値そのものではない

条件付き書式を使っても、数量の6を「多い」という文字へ置き換えるわけではありません。セルには6という値が入り、その値について決めた条件が成り立つと、塗りつぶしなどの見た目が変わります。見るための目印と、集計や計算の材料になる値を別に扱うことが、この練習の出発点です。

たとえば、6が入ったセルを目立たせたいという同じ見た目でも、手動で青く塗った場合と、「5より大きい」というルールで青くした場合では、値を4へ直した後の意味が違います。前者は人が選んだ書式、後者は条件に基づく書式です。どちらで作った色かを、色の濃さだけで判別しようとせず、設定したルールから確かめます。

また、候補一覧から値を選ばせるプルダウンの入力規則とも目的が異なります。今回の教材では数量を数値として入力し、その後の表示を変えます。数値を入れられなくする制限を作ったり、条件を満たす行だけを残すフィルタを設定したりする操作は含みません。何を変える機能なのかを決めると、別の設定画面へ迷い込みにくくなります。

A列からC列へ、意味の違う三つの列を置く

新しいシートの一行目に、A列は資材ID、B列は資材名、C列は数量と入力します。二行目から六行目には、下の五件を置きます。見出しを含む表全体はA1:C6ですが、最初に条件付き書式を設定する数量の範囲はC2:C6です。表の大きさと、色を付ける対象は同じではないことに注意しましょう。

横に長い表は、左右へスクロールして全列を確認してください。

表の最後の列「5より大きいか」は、この記事で期待値を説明するための欄です。ExcelのD列へ入力する必要はありません。A列からC列までの三列を作り、数量を見て頭の中で判定できるようにしておきます。小さな表なら、設定前の値を読み直す負担が少なく、条件の意味を確かめることに集中できます。

数量に単位や説明を混ぜない

C列には、4、5、6、0、8という数値を入力します。「4個」「未確認」「約6」のような説明を混ぜると、いま確認したい数値比較とは別の問題が入ります。単位が必要なら見出しや表の外へ書き、まずは教材と同じ数値だけで進めてください。現実の表を扱う段階では、その表が数値と文章をどう区別しているかを先に点検します。

見た目が数字に見えても、取り込んだデータが文字列であることがあります。今回、既存表から値を貼り付けて始めるより、新しい教材へ数量を入力する方が、元データの取り込み方による違いを減らせます。色が付かなかった際に、その場で数量を別の値へ勝手に直すのではなく、入力内容とデータの扱いを分けて確認するための準備です。

条件の言葉を、設定前に決める

「5以上」と「5より大きい」は異なります。今回は後者なので、5は対象外、6と8が対象です。条件を「多いもの」とだけ決めると、どこから多いと考えるかが設定の途中で曖昧になります。まず比較の言葉と基準値を書き、境界の5を教材に残しておくと、条件を選び間違えていないかを確かめられます。

青を選ぶ理由は、数量が5を超えたセルを区別するためです。青だから安全、0だから問題という業務上の判断は、この架空教材には付けません。色は読者が選べる表示上の約束です。数量の良し悪しや補充の必要性まで、Excelがこの一つの条件から判断したとは考えないようにします。

2．数量の五セルへ一つのルールを設定する

C2:C6を選び、見出しを入れない

数量の最初のセルC2から、最後のセルC6までを選びます。この時点ではA列やB列を含めず、C1の見出しも選びません。選択範囲を小さく限定しておくと、「値を判定する場所」と「書式を付ける場所」が、どちらも同じ五つの数量セルであることを理解しやすくなります。

マウスの選択が一行ずれた場合は、ルールの作成へ進む前に選び直します。C2:C5だけなら封筒の8が対象から外れます。C3:C6だけなら無地カードの4が対象外になりますが、もともと4は条件を満たさないため、最初の色の付き方だけでは気付きにくいでしょう。色が付くセルの数が合うことと、全五件が設定範囲に入っていることは、別々に確認します。

ホームの条件付き書式から、数値の比較を選ぶ

Windowsのデスクトップ版では、ホームタブの条件付き書式を開きます。セルの強調表示ルールから、指定の値より大きいという比較を選び、基準値へ5を指定します。画面の表記が手元の版と少し異なる場合も、「大きい」「以上」「等しい」のどの比較を選んでいるかを読んでください。単に数値の5を入れたという記憶だけでは、条件を説明できません。

書式は淡い青の塗りつぶしに統一します。候補に目的の色がなければ、ユーザー設定の書式など、その版で利用できる書式の指定から塗りつぶしを選びます。文字色、罫線、太字まで一度に変えると、どの書式の変化を見ているのか分かりにくくなります。初回は一種類の変化へ絞り、確定する前に条件と色を見直しましょう。

同じ条件付き書式の中には、データバー、カラースケール、上位の項目など、別の表現もあります。今回は「5より大きい」という一定の境界を調べたいので、その目的に合う比較を使います。数量の大小をなめらかな濃淡で表したい場合と、境界を超えたかどうかを表したい場合では、選ぶ書式の意味が違います。

色の付いた場所を、セル番地と値で読む

設定を確定したら、C4の6とC6の8が条件を満たすかを見ます。C3の5には、このルールの青が付かないという期待です。「青が二つ」という数だけでなく、M03のひもとM05の封筒に対応するセルかを確認してください。違う二つのセルが青くなっていても、件数だけなら同じになってしまいます。

また、セルの中身を読んで、4、5、6、0、8がそのまま残っているかを確かめます。書式を設定したつもりで、数量を青や対象などの文字へ書き換えていないかを見るためです。セルの値を保持したまま、条件に合う場所の表示が変わるという目的に戻れば、見た目だけの確認を避けられます。

範囲とルールは、管理画面でも照合する

ホームの条件付き書式からルールの管理を開き、いま作った条件と適用先を読みます。現在選んでいるセルに対する表示だけになっている場合は、表示対象の設定にも注意してください。必要なルールが見当たらないときに、すぐ同じルールを追加するのではなく、どのセルやシートのルールを表示しているかを確認します。

最初の教材では、適用先がC2からC6の五セルに対応し、条件が5より大きいこと、書式が青の塗りつぶしであることを並べて読みます。適用先にはドル記号を含む参照で表示されることがあります。記号を消して見た目をそろえることが目的ではなく、対象の列と最初・最後の行が合っていることが確認の中心です。

この段階の控えは「C2:C6、5より大きい、淡い青」の三点で十分です。後から色を変更するときも、条件まで変える必要があるのかを判断できます。数値の比較と表示の選択を一行で説明できる状態にすると、設定を作った人以外も、表の色が何を意味するか読み取りやすくなります。

3．境界の値と適用先を変え、反応を確かめる

5を7へ変更する前に、予想を書いておく

初期状態を確認できたら、C3の小袋の数量だけを5から7へ変える練習をします。変更前は5が条件外、変更後は7が5より大きいので条件内です。ほかの四セルは変更しないため、青になる数量はC3の7、C4の6、C6の8の三つになるという期待を立てられます。

数値を変える前に予想を置くのは、出た色へ後付けで理由を合わせないためです。もしC3が青くならなければ、数量を7へ確定したか、C3が適用先に含まれているか、条件が本当に5より大きいかを調べます。一つのセルだけを変えたので、変更前後の差を追いやすくなります。

その後、C3を7から5へ戻します。ほかの設定を変えていなければ、C3はこの条件の青から外れ、最初の二つのセルが対象になる期待です。色を戻すために手動で白く塗るのではなく、値と条件の関係へ戻って確認してください。手動の塗りつぶしを加えると、どの設定が見えているのかという別の要素が入ります。

基準値だけを変える場合は、元の数量を保つ

次の練習では数量を元の4、5、6、0、8へ戻した上で、ルールの基準値を5から6へ変えると考えます。条件は「6より大きい」なので、6は対象外となり、8だけが対象です。同じ比較のまま境界だけを変更しても、含まれるセルは変わります。数量の変更と基準の変更を同時にしないことが、結果を説明する近道です。

管理画面で既存のルールを選び、編集する対象を確かめてから、基準の数値を直します。この操作で、新しいルールを別に追加する必要はありません。旧ルールを残したまま新ルールを足すと、同じセルが二つのルールを持ち、後で扱う優先順位の話が混ざります。いま何を一つだけ変更したいのかを決めてから、編集か追加かを選びます。

基準値の練習が終わったら、条件を5より大きいへ戻します。教材を途中状態のまま次章へ進めると、行全体の色分けで予想する二行と数が合わなくなります。数量五つとルールの境界を別々に読み、次に使う開始状態を整えてください。戻した設定も、操作したつもりで済ませず管理画面で確認します。

七行目を足しても、範囲に含まれるかを別に見る

元の五行が分かったら、追加の練習として七行目へM06、包装紙、9を置く場合を考えます。9は5より大きい値ですが、固定した適用先がC2:C6のままなら、C7はそのルールの対象外です。条件に合う値が存在することと、その場所にルールが適用されていることの両方が必要になります。

行を追加した後の範囲の扱いは、表の作り方や行の挿入、コピーなどの操作によっても変わり得ます。そのため、追加すれば必ず自動で広がる、逆に必ず広がらないと決め付けるのではなく、管理画面で現在の適用先を読みます。今回、普通のセル範囲としてC2:C6を設定した教材では、追加したC7が含まれるかを自分の画面で確かめるのが確認項目です。

六件全部を同じ条件で調べたいなら、適用先をC2:C7に対応する範囲へ変更し、追加した9も対象になるかを見ます。最終行の9だけを手動で塗ると、表全体が同じルールで動いているように見えてしまいます。追加した行を含めたつもりでも、実際は手動書式で補っていたという違いを残さないようにしましょう。

範囲の端にある、条件外の値も確認対象にする

最初のC2の4やC5の0は青くならないため、画面を見るだけでは設定の対象に入っているか分かりにくい値です。端のセルの値を、練習用に一時的に条件内へ変えて確かめる方法もあります。たとえばC2を4から6へ変えれば、C2も青になるはずです。ただし、この確認をしたら元の4へ戻し、変更を残さないようにします。

実際の業務表で確認のために数値を変える場合は、その値を変更してよいかという別の判断が必要です。ここで扱うのは、変更権限のある架空教材での検証です。本番の数値を書き換えて色の動作を試すことを、通常の点検手順にはしません。必要なケースを練習用の表へ再現してから、どの設定を見直すべきか考えます。

4．数式を使い、数量を見て行の三セルを色分けする

判定する列と、書式を付ける範囲を分ける

ここからは、元の五行の教材を別の新しい練習用シートへ用意します。前章の追加行は使わず、C2:C6は4、5、6、0、8です。先ほどの数量セルだけのルールも入れていない状態から始めます。同じシートに色々なルールを重ねる前に、数式一つの意味を確認するためです。

目標は、C列の数量が5より大きい行について、A列の資材IDからC列の数量まで、三セルを同じ青で示すことです。判定に読む場所はC列、書式を付ける場所はA2:C6になります。先ほどは判定と書式の範囲がどちらもC列でしたが、今回はその二つが異なります。この違いを理解してから数式を入力します。

左上をA2として、C列を固定する

A2:C6を選び、左上のA2を基準に考えます。ホームの条件付き書式から新しいルールを開き、数式を使用して書式設定するセルを決定する種類を選びます。教材で使う数式は=$C2>5です。書式は淡い青の塗りつぶしだけにし、条件と適用先を確かめて確定します。

$C2では、ドル記号の付いたC列を固定し、行番号の2は行に合わせて変わる参照として扱います。A2、B2、C2のどのセルの書式を考えるときも、二行目ではC2の数量を読みます。三行目ならC3、四行目ならC4というように、同じ行の数量を判定に使うための書き方です。

式の先頭の等号は数式としての入口、末尾の「>5」は数量が5を超えるかという比較です。数式の見た目を暗記するだけでなく、列はCにそろえる、行は対象の行へ合わせる、境界は5を超える、という三つへ分解して読むと、別の位置の表へ使う際も考えやすくなります。

二行ではなく、二つの資材IDを確認する

初期値では、M03のある四行目とM05のある六行目が条件を満たします。それぞれA列からC列の三セルが対象になる期待です。M02の小袋は数量5なので、三行目にはこの条件による青が付かないはずです。数量のセルだけが青いのか、同じ行の資材IDと資材名まで青いのかを分けて見ます。

この例では色の付くセルは合計六つですが、合計だけを確認することは勧めません。四行目の三セルと六行目の三セルという位置の組合せが大切です。別の二行が青くなったり、一つの行の違う列まで青くなったりしても、六セルという数だけなら見逃せます。IDと行番号を合わせて読みましょう。

一つの行を、三つのセルへ展開して考える

数式の確認に迷ったら、三行目と四行目だけを紙に書いて比べます。A3の書式を決めるときはC3の5、B3でもC3の5、C3自身でもC3の5を読みます。この行では三回とも条件は成り立ちません。次にA4、B4、C4の順へ移ると、いずれもC4の6を読むため、三セルとも条件が成り立つという関係になります。

横へ進んでもC列を読む点と、下へ進むと読む行が変わる点を別に説明できれば、列だけを固定した意味がつかめます。もし資材名のB列まで数量として比べるつもりになっていたら、書式を付ける対象と、数式で判定に使う対象が混ざっています。資材名の文字を5と比べたいわけではなく、同じ行の数量の判定結果を、その行の表示へ使う設定です。

確認用のメモには、対象セル、参照する数量セル、その値、比較結果の順で書くと整理できます。このメモはExcelへ別の数式列を増やす指示ではありません。現在のルールを説明するための読み取りです。式を修正するときも、どの対応が間違っていたかを特定してから直せるため、ドル記号を試しに増減させ続けることを避けられます。

固定の仕方を間違えたときの違いを読む

=$C$2>5にすると、列だけでなく行の2も固定されます。今回の初期値でC2は4なので、全ての対象が同じC2の判定を読む形では、期待する二行の青になりません。数量が多い行を調べたいのに、先頭の一つの数量だけを全体の基準にしていないかという観点で確認します。

一方、C列を固定しない=C2>5では、対象の列に合わせて参照が横へ変わるため、A列からC列へ同じ行の判定をそろえる目的とずれます。A列を考えるときのC2だけでなく、B列を考えるときには何を読む式になるのか、という順番で考えます。列固定のドル記号は、書式を付けるA列を固定するための記号ではありません。

もう一つの確認は、適用先の先頭行と式の行番号の対応です。A2:C6へ適用する教材なのに、式を=$C3>5としてしまうと、一行下の数量を見てしまう関係になります。適用先の左上、数式に書いた最初の参照、実際に色の付いたIDを並べれば、一行ずれた原因を追いやすくなります。

5．ルールの重なりと手動の色を管理画面で整理する

色を足す前に、何を重ねたいかを決める

複数の条件を使う段階でも、先に言葉で意味を決めます。たとえば数量が5より大きいセルを青、7より大きいセルを橙にしたい場合、8は両方の条件を満たします。二つの条件があるから必ず二色が同時に見えるわけではありません。同じ塗りつぶしへ異なる色を指定すると競合するため、ルールの優先順位が関係します。

練習する場合は、数量セルだけへ設定した最初の教材のコピーを使い、行全体の数式教材とは分けます。C2:C6へ「5より大きい、青」と「7より大きい、橙」という二つを作ると考えてください。基準値も書式もルールごとに意味を持つため、単に青のルール、橙のルールという色の名前だけで説明しない方が誤解を防げます。

両方に当てはまる8で、順序を確認する

ルールの管理では、同じ対象に関わる二つのルールと、その上から下への順序を見ます。8へ橙を優先して示したいなら、「7より大きい、橙」の方を上位に置くという考え方です。ここでは条件を満たす場合に停止する設定など、別の評価停止を加えない単純な二つのルールとして考えます。

6は5を超えますが7は超えないため、青のルールだけを満たします。8は両方を満たすので、競合する塗りつぶしについて上位のルールが重要になります。境界の5はどちらも満たしません。三種類の値を並べて読むと、条件の間違いなのか、優先順位による見え方なのかを切り分けられます。

新しいルールを作っただけで、思いどおりの順序になったと決め付けないでください。作成、コピー、貼り付けなどで、現在の一覧の構成が意図と違う場合もあります。青が出ないから同じ青のルールをさらに追加するより、いま存在する条件、適用先、順序を読み直す方が、不要な重複を増やさずに済みます。

競合する書式と、組み合わせられる書式を区別する

二つのルールが同時に成り立っても、一方が太字、もう一方が塗りつぶしなら、同じ属性を別の値へ変える競合とは異なります。Microsoftの説明でも、競合しない書式は組み合わせられる場合と、同じ書式が競合して上位が優先される場合を分けています。「最初のルール以外は一切適用されない」と全体を単純化しないことが大切です。

初めての表では、見分けたい意味が二つあるからといって、二つとも塗りつぶしへ割り当てる必要があるかを考えます。数値の境界と確認の完了状態は、同じ色の軸へ混ぜると意味が読みにくくなるかもしれません。まず一つの条件を確実に説明できる表示にしてから、追加の情報が必要か検討します。

手動の色が残る理由を、値の削除と混ぜない

条件付き書式と手動書式が同じ部分で競合すると、条件が成り立つ場合の表示は条件付き書式が優先されます。ただし、そのルールを取り除いた後も、元の手動書式が残る場合があります。ルールを消したのに色があるという現象だけで、ルールが削除できなかったと即断しないようにしましょう。

管理画面に出てくるのは条件付き書式のルールで、手動の塗りつぶしそのものは同じ一覧で管理する対象ではありません。必要なルールが残っているかを確認した上で、元の手動書式も点検します。色を消したいという目的のために、数量まで削除する必要はありません。値、手動書式、条件付き書式を別々の対象として扱います。

6．解除・引継ぎ・うまく動かないときの確認をまとめる

一つのルールを外すのか、対象の全ルールを外すのか

条件付き書式を使わなくなったら、まず外したい範囲を決めます。管理画面で特定のルールを削除したい場合と、選んだセルに関するルールをクリアしたい場合、シート全体のルールを消したい場合は、影響する対象が異なります。教材の一部分を戻すだけなのに、シート全体からルールを消す項目を選ばないよう、確定前の対象を読んでください。

今回の五セルだけで練習するなら、数量のC2:C6を対象としていることを確認し、条件付き書式のルールのクリアでは選択したセルのルールをクリアする範囲を選びます。二つのルールのうち一つだけを外したいときは、ルールの管理から該当する条件を識別して削除する方が、残したい条件を区別しやすくなります。

解除後は数量が4、5、6、0、8のままかを読みます。書式の解除で数量まで消すことは、今回の目標ではありません。また、手動で青や橙を塗っていたセルがないかを分けて見ます。ルールの一覧と、セルに残った値や見た目を両方確認して、操作の影響を理解します。

色が付かないときは、対象・条件・表示の順に見る

最初に確認するのは、そのセルが適用先に含まれているかです。表の外へ追加した9や、選択から外れた先頭の4は、数量の大小だけを見ても判断できません。次に、条件が5より大きいのか、5以上なのか、別の数値を基準にしていないかを確認します。数式なら参照する列と行も、この条件の確認に含めます。

その後に、現在の値を数値として入れているか、別のルールと競合していないか、期待する書式を指定したかを見ます。最初から色の候補を何度も選び直すと、原因を特定しないまま表示だけを変えてしまいます。小さな教材へ戻して、一つのセルと一つのルールの関係から確認すると、問題を説明しやすくなります。

反対に、色が付きすぎる場合は、範囲が広すぎるだけでなく、数式の固定や行の対応が違う可能性も考えます。「全部青い」という状況を一語で終えず、どのIDのどの列まで青いか、管理画面でどの範囲が指定されているかを控えます。画面の見た目を言葉へ置き換えると、人に相談する際にも条件を共有できます。

ほかの人へ渡すときは、色の意味と更新時の確認点を書く

表を引き継ぐなら、「青は数量が5より大きいセル」「対象はC2:C6」というように、色の意味と範囲を一緒に説明します。単に多いものを青にしたと伝えると、基準値や境界が読み取れません。さらに、新しい行を足したときは適用先を確認する、と書けば、今の設定が将来どこまで続くかを勝手に推測せずに済みます。

条件付き書式のあるセルを別の場所へコピーしたときも、同じ見た目になったから同じ意味だとは限りません。数式の参照先や、コピー先に既にあるルールを含め、現在の管理画面で読む必要があります。引継ぎでは完成した色だけを成果として示すより、値を一つ変えた際にどうなる想定かを添える方が、動作の確認へつながります。

表示を絞る必要があるなら、別の機能として考える

色を付けるだけでは、条件外の行は表から消えません。必要な行だけを見たいという課題なら、絞り込みの機能を検討します。Googleスプレッドシートを使う場合の別の操作として、フィルタ表示で自分用の条件を保存する記事では、見える行を選ぶことと元データの変更を分けています。Excelの条件付き書式の設定手順と、同じ操作として混ぜないようにしてください。

今回の教材では、五つの数値を保ち、二つの数量セルが条件を満たすこと、値や境界を変えると対象が変わること、行の書式にはC列を読む数式と適用先の関係が必要なことを確認しました。手元の表へ移すときは、先に値、条件、範囲、書式を書き出します。その四つが読めれば、見た目に頼るだけでなく、なぜその場所が目立つのかを説明できるようになります。