Excelで計算結果に上限値と下限値を設け、範囲を外れた数値だけ赤字にしたい場合は、条件付き書式を使うと簡単に設定できます。例えば下限が10、上限が20なら、10未満または20超を赤字にし、10以上20以下は黒字のまま表示できます。
ところが、条件式の作り方を間違えると、範囲内の数値まで含めてすべて赤字になることがあります。特に「上限を超える」と「下限を超える」を別々に設定するときに、下限側の条件を「下限より大きい」にしてしまうと、ほとんどの数値が赤になるため注意が必要です。
上限・下限を外れたときだけ赤字にする基本条件
考え方は非常にシンプルです。正常範囲が「下限以上、上限以下」であれば、異常値はその反対である「下限未満、または上限超」となります。
例えば下限が10、上限が20の場合、赤字にしたい条件は「10未満」または「20より大きい」です。10から20までの数値は黒字のままにします。
下限側は「下限より小さい」、上限側は「上限より大きい」と設定するのがポイントです。「下限を超える」という言葉をそのまま「下限より大きい」と設定すると、正常範囲の数値まで赤になってしまいます。
数式を使わずに条件付き書式を2つ設定する方法
もっとも分かりやすい方法は、条件付き書式を2つ作る方法です。例えば計算結果がA2セルにあり、下限が10、上限が20だとします。
まずA2セルまたは対象範囲を選択し、「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「指定の値より小さい」を選びます。値に10を入力し、書式設定でフォントの色を赤にします。
次にもう一度、「条件付き書式」→「セルの強調表示ルール」→「指定の値より大きい」を選び、20を入力してフォントを赤に設定します。これで10未満と20超だけが赤になります。
数式を使って1つのルールにまとめる方法
条件付き書式を1つにまとめたい場合は、OR関数を使う方法があります。例えばA2セルの値が下限10未満、または上限20超なら赤字にする場合、条件付き書式の数式に次の考え方を使います。
=OR(A2<10,A2>20)
この式では、A2が10より小さい、または20より大きい場合にTRUEとなり、指定した赤字の書式が適用されます。A2が10以上20以下ならFALSEになるため、通常の黒字表示になります。
上限値と下限値を別セルに置くと管理しやすい
実務では、上限と下限を直接数式へ入力するより、別セルへ置いておくと変更が簡単です。例えばD1セルに下限、E1セルに上限を入力するとします。
その場合、A2セルに適用する条件付き書式は次のようにできます。
=OR(A2<$D$1,A2>$E$1)
こうしておけば、後から下限を8、上限を25に変更したい場合でもD1とE1の数値を書き換えるだけで済みます。条件付き書式そのものを編集する必要がありません。
なぜ計算結果が全部赤になるのか
すべて赤字になってしまう場合は、条件式の向きが逆になっている可能性があります。例えば「下限を超えると赤」というつもりで「A2>10」と設定すると、11、12、15、18、20、25など10より大きい数値がすべて赤になります。
そこへ「A2>20」という上限条件を追加しても、A2>10のルールですでに大部分が赤になっているため、正常範囲の11から20も赤になります。
正しくは下限を下回る場合に赤なので、「A2<10」とする必要があります。上限については「A2>20」で正しい設定です。
「以上」「以下」を赤にする場合は記号が変わる
境界値そのものを赤にするかどうかによって、不等号の使い方が変わります。例えば下限10、上限20で、10と20は正常値として黒にしたいなら「10未満」「20超」を使います。
| 条件 | 数式 |
|---|---|
| 10未満を赤 | A2<10 |
| 10以下を赤 | A2<=10 |
| 20より大きい値を赤 | A2>20 |
| 20以上を赤 | A2>=20 |
どちらを使うかは、その上限・下限の定義によって決めます。
範囲内を黒にするルールは通常不要
セルの通常フォント色が黒に設定されているなら、10以上20以下を黒にするための条件付き書式を別途作る必要はありません。赤にする条件だけ設定し、それ以外は通常書式に任せるほうが簡単です。
複数の条件付き書式を設定すると、どのルールが優先されるのか分かりにくくなることがあります。不要な黒字ルールを作らず、異常値だけ赤にするルールに絞るとトラブルを減らせます。
すでに黒字に戻す条件を作っている場合は、「条件付き書式」→「ルールの管理」で不要なルールがないか確認しましょう。
複数セルへ設定するときは参照方法に注意する
A2からA100までの計算結果へ同じ条件を設定する場合、まずA2:A100を選択してから条件付き書式を作ります。
数式を使う場合は、先頭セルに合わせてA2と記述します。Excelが各行に対してA3、A4、A5というように自動的に参照をずらして判定してくれます。
一方、下限値と上限値をD1、E1に置いている場合は、$D$1、$E$1のように絶対参照へしておきます。これにより、A3を判定するときでも下限と上限の参照先がずれません。
計算式が入ったセルでも条件付き書式は使える
対象セルに手入力の数値ではなく、SUM関数や割り算などの計算式が入っていても問題ありません。条件付き書式は、セルに表示された計算結果を基準に判定できます。
例えばA2に「=B2/C2*100」という式があり、計算結果が95なら、下限90・上限110の範囲内なので黒字のままになります。計算結果が85なら下限未満となり赤字になります。
つまり、計算式そのものを条件付き書式に含める必要はなく、最終的な数値を条件にすれば十分です。
空白セルまで赤になる場合の対処法
計算式によっては、見た目は空白でも内部的には0として扱われ、下限未満の条件に該当して赤くなる場合があります。
例えば下限が10の場合、空白相当のセルが0として判定されると「0<10」が成立するため赤になります。空白時には色を付けたくない場合は、条件式に空白判定を追加します。
=AND(A2<>\"\",OR(A2<$D$1,A2>$E$1))
これにより、A2が空白ではなく、かつ下限未満または上限超の場合だけ赤にできます。
数値が文字列になっていないかも確認する
条件付き書式が意図通りに動かない場合、セルの値が数値ではなく文字列として保存されていることもあります。見た目が「15」でも文字列の15になっていると、比較結果が期待と異なることがあります。
セル左上に緑色の三角形が表示されている場合や、数値が不自然に左寄せになっている場合は、文字列として保存されていないか確認します。
必要に応じてエラーアイコンから「数値に変換する」を選ぶか、VALUE関数などで数値化してから条件付き書式を設定します。
既存の条件付き書式を一度確認する
何度も設定を試していると、以前作った条件付き書式が残っていることがあります。その場合、正しいルールを追加しても古いルールによって赤字になり続けることがあります。
「ホーム」→「条件付き書式」→「ルールの管理」を開き、対象セルに設定されているルール一覧を確認します。不要な「セルの値>下限」などが残っていれば削除します。
ルールが複雑になりすぎた場合は、一度「ルールのクリア」で対象範囲の条件付き書式を削除し、最初から2つの条件だけ作り直す方法も分かりやすいでしょう。
具体例｜下限80・上限120で設定する場合
例えば測定結果がB2:B50へ表示され、正常範囲を80以上120以下とします。この場合、79以下と121以上を赤字にしたいことになります。
条件付き書式を2個使うなら、1つ目を「セルの値が80より小さい」、2つ目を「セルの値が120より大きい」とします。書式はどちらもフォントを赤にします。
1つの数式にまとめるなら、B2:B50を選択して次の式を設定します。
=OR(B2<80,B2>120)
これで70は赤、80は黒、100は黒、120は黒、130は赤という表示になります。
まとめ｜下限は「未満」、上限は「超」で設定する
Excelで「上限を超えると赤、下限を下回ると赤、その間は黒」にしたい場合は、条件付き書式で「値<下限」または「値>上限」という条件を設定します。
計算結果が全部赤になる場合は、下限側を「値>下限」と設定していないか確認してみましょう。下限は「超える」のではなく「下回ったときに異常」と考えると分かりやすくなります。
最も簡単なのは「下限より小さい」と「上限より大きい」の2つの条件付き書式を作る方法です。上限・下限を別セルで管理するなら、=OR(A2<$D$1,A2>$E$1)のような式を使うと変更にも対応しやすくなります。範囲内は通常の黒字書式に任せればよいため、必要以上にルールを増やさないことも設定を分かりやすくするポイントです。
コメント