売上や達成率、在庫数などをExcelで管理していると、「80以上は緑」「50未満は赤」のように、決めた基準で数値を色分けしたいことがあります。
このような場合は、条件付き書式を使うと便利です。数値を変更するたびに色を塗り直さなくても、設定した条件に合わせてセルの色が自動で切り替わります。
この記事では、1つの基準で色を付ける基本操作から、3段階の色分け、行全体への適用、空白セルを除外する数式まで順番に解説します。
1. 条件付き書式で数値を3段階に色分けする
今回は、次のような達成率の表を例にします。
| 担当者 | 目標 | 達成率 |
|---|---|---|
| 青木 | 100件 | 85 |
| 伊藤 | 100件 | 72 |
| 上田 | 100件 | 48 |
| 遠藤 | 100件 | 91 |
| 大野 | 100件 | 63 |
達成率に応じて、次の3段階で色分けします。
| 条件 | 色 |
|---|---|
| 80以上 | 緑 |
| 50以上80未満 | 黄 |
| 50未満 | 赤 |
条件付き書式を設定しておけば、達成率を変更したときも色が自動で切り替わります。
2. 1つの条件だけなら「セルの値」で設定できる
最初に、「80以上のセルを緑にする」という1つの条件を設定してみましょう。
- 色を付ける範囲（例：C2:C6）を選択する
- 「ホーム」タブから「条件付き書式」→「新しいルール」を開く
- 「指定の値を含むセルだけを書式設定」を選択する
- 条件を「セルの値」「次の値以上」「80」に設定する
- 「書式」をクリックし、塗りつぶしを緑にする
- 「OK」をクリックして設定を確定する
これで、C列の値が80以上になったセルだけに緑色が付きます。
数値を変更すると判定も自動でやり直されるため、手作業で色を塗り直す必要はありません。
3. 複数の条件を数式で設定する
3段階に色分けする場合は、条件ごとにルールを作成します。境界となる数値を正確に分けるため、ここでは数式を使います。
（1）80以上を緑にする
C2:C6を選択した状態で、「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」を選択します。
数式には、次のように入力します。
=C2>=80
書式の塗りつぶしを緑に設定します。
（2）50以上80未満を黄にする
同じ範囲に、2つ目のルールを追加します。
=AND(C2>=50,C2<80)
この数式は、「50以上」と「80未満」の両方を満たすか確認しています。書式の塗りつぶしを黄に設定します。
（3）50未満を赤にする
3つ目のルールには、次の数式を使います。
=AND(C2<>"",C2<50)
書式の塗りつぶしを赤に設定します。
C2<>""は、「C2セルが空白ではない」という条件です。この条件を入れないと、未入力のセルまで50未満と判定され、赤くなることがあります。
空白セルに色が付く仕組みについては、関連記事「Excelの条件付き書式で空白セルまで色が付く原因は？空白を除外する数式」で詳しく解説しています。
4. 数式では「選択範囲の先頭セル」を指定する
今回の適用範囲はC2:C6なので、数式では先頭セルのC2を指定します。
=C2>=80
Excelはこの数式を、C3、C4、C5というように各行へ自動的にずらして判定します。
適用範囲がC3:C20から始まる場合は、数式も次のようにC3から始めます。
=C3>=80
適用範囲の先頭行と数式の行番号がずれていると、1つ上や下のセルを基準に色が付いてしまいます。
5. 条件に合う行全体を色分けする
達成率のセルだけでなく、担当者名や目標を含む行全体を色分けすることもできます。
表がA2:C6にある場合は、最初にA2:C6を選択します。そのうえで、次の3つのルールを設定します。
緑にする数式：
=$C2>=80
黄にする数式：
=AND($C2>=50,$C2<80)
赤にする数式：
=AND($C2<>"",$C2<50)
$C2の$は、判定に使う列をC列に固定する記号です。行番号には$を付けないため、2行目、3行目と順番に判定できます。
行全体へ条件付き書式を設定する手順と$の使い方は、「Excelの条件付き書式で行全体に色を付ける方法｜数式と「$」の意味をやさしく解説」も参考にしてください。
6. 基準値をセルに入力して変更しやすくする
基準となる80や50を数式に直接入力すると、基準を変えるたびに条件付き書式のルールを編集する必要があります。
今後変更する可能性がある場合は、基準値を別のセルに入力しておくと便利です。
例えば、F2セルに「80」、F3セルに「50」と入力した場合は、次の数式を使います。
緑にする数式：
=C2>=$F$2
黄にする数式：
=AND(C2>=$F$3,C2<$F$2)
赤にする数式：
=AND(C2<>"",C2<$F$3)
基準値を変更すると、色分けも自動で変わります。月ごとに目標が変わる表や、利用者が判定基準を変更する資料に向いています。
$F$2のように列と行の両方に$を付けるのは、数式をどのセルに適用しても、基準値のセルがずれないようにするためです。
7. 複数ルールの優先順位を確認する
同じセルに複数の条件付き書式を設定すると、ルールの条件が重なることがあります。
「条件付き書式」→「ルールの管理」を開くと、現在設定されているルールと優先順位を確認できます。
今回の3つの数式は、条件が重ならないように作っています。
- 80以上
- 50以上80未満
- 50未満
一方で、「50以上」と「80以上」という2つのルールを設定すると、80以上の数値は両方の条件を満たします。意図しない色になる場合は、条件の範囲を重ならないように直すか、ルールの順番を変更しましょう。
Excelの環境によっては「条件を満たす場合は停止」を使い、上位のルールが成立した時点で、それより下のルールを適用しない設定もできます。
8. 条件付き書式で正しく色分けされないときの確認点
（1）数値が文字列として入力されている
見た目が「80」でも、文字列として保存されていると、数値の大小を正しく判定できないことがあります。
セルの左上に緑色の三角形が表示されている場合は、警告メニューから数値に変換してみましょう。VALUE関数や「区切り位置」機能を使って変換する方法もあります。
（2）適用範囲と数式の先頭行がずれている
適用範囲がC2から始まる場合、数式もC2を基準にします。C3など別の行を指定すると、色が1行ずれて表示されます。
（3）列や行を固定する「$」の位置が違う
行全体を色分けする場合は、$C2のように判定列だけを固定します。$C$2とすると、すべての行がC2セルだけを見て判定してしまいます。
（4）空白セルが数値の0として判定されている
50未満を判定する数式には、空白を除外する条件を加えます。
=AND(C2<>"",C2<50)
（5）以前のルールが残っている
同じ範囲に古い条件付き書式が残っていると、想定外の色が表示されることがあります。「条件付き書式」→「ルールの管理」から、適用範囲とルールの内容を確認しましょう。
9. 固定した基準なら条件式、全体の傾向ならカラースケール
条件付き書式には、数値をグラデーションで表示する「カラースケール」もあります。
今回のように「80以上は緑」「50未満は赤」と基準が決まっている場合は、条件ごとにルールを設定する方法が適しています。
一方で、売上の最大値・最小値やデータ全体のばらつきを直感的に確認したい場合は、カラースケールが便利です。
| 目的 | 向いている方法 |
|---|---|
| 合格・不合格など、決まった基準で分ける | 条件式による色分け |
| 数値全体の大小や傾向を見る | カラースケール |
| 80以上、50以上など複数の区分を作る | 条件式による色分け |
| 最大値から最小値までを段階的に表す | カラースケール |
カラースケールの設定方法は、「エクセルの条件付き書式「カラースケール」で数値を色分けする方法」で解説しています。
10. まとめ
条件付き書式を使うと、数値を「以上・以下」の基準で自動的に色分けできます。
今回使用した3つの条件は、次のとおりです。
80以上を緑にする：
=C2>=80
50以上80未満を黄にする：
=AND(C2>=50,C2<80)
50未満を赤にする：
=AND(C2<>"",C2<50)
行全体を色分けする場合は、$C2のように判定する列を固定します。また、基準値を別のセルに入力しておけば、ルールを編集せずに色分けの境界を変更できます。
固定した基準による色分けとカラースケールを目的に応じて使い分けると、数値の意味が伝わりやすい表を作れます。
