【Excel】エクセルで数値を以上・以下の条件で色分けする方法

エクセルで売上、点数、在庫数、進捗率などの数値を管理していると、基準を満たした値や注意が必要な値をすぐに見分けたい場面があります。

数値を手作業で塗りつぶす方法では、入力値が変わるたびに色を修正しなければならず、表が大きくなるほど見落としも増えます。

そこで役立つ機能が、条件付き書式です。

条件付き書式を設定すれば、指定した数値以上なら緑、以下なら赤といったルールに合わせて、セルの背景色や文字色を自動で変更できます。

この記事では、エクセルで数値を以上・以下の条件で色分けする基本操作から、数式を利用した応用設定、うまく色が付かない場合の確認方法まで解説します。

サンプルデータは、1行目に見出しがある表として説明します。

 

エクセルで数値以上を色分けする条件付き書式

それではまず、基準値以上の数値を自動で色分けする方法について解説していきます。

商品名	売上額	判定基準
商品A	125000	100000以上
商品B	85000	100000以上
商品C	110000	100000以上

この例では、売上額が100000以上のセルだけを緑色で表示します。

数値を変更した時点で判定結果も変わるため、日々更新する管理表にも便利な機能です。

 

色分けするセル範囲の選択

最初に、色分けしたい数値が入力されているセル範囲を選択します。

今回の例では、売上額がB列の2行目から10行目までに入力されているため、B2からB10をドラッグして選択します。

見出しのB1を選択範囲に含めないことが、意図しない色分けを防ぐ基本です。

表全体ではなく、判定対象となる数値列だけを選ぶと、設定内容が分かりやすくなります。

複数列に同じ判定を適用したい場合は、最初から対象範囲をまとめて選択しても問題ありません。

空白セルが含まれていても設定できますが、入力予定のない広すぎる範囲まで指定すると、後からルールの管理が複雑になるかもしれません。

 

指定の値以上ルールの設定

続いては、100000以上のセルを緑色にするルールを確認していきます。

対象範囲を選択した状態で、リボンのホームタブを開きます。

条件付き書式をクリックし、セルの強調表示ルールを選択して、指定の値以上をクリックしましょう。

表示された入力画面の左側に100000を入力します。

右側では、薄い緑の塗りつぶしと濃い緑の文字、または任意の書式を選べます。

設定後にOKをクリックすると、100000、110000、125000のような基準値以上の数値に自動で書式が付きます。

単位が円でも個数でも点数でも、セル内に数値として保存されていれば同じ手順で設定できます。

 

表示形式と基準値の確認

続いては、数値の見え方と判定基準の関係を確認していきます。

たとえば売上額を円単位で表示している場合、セルの表示が100,000円であっても、条件付き書式では通常100000という数値を入力します。

セルに通貨表示形式を設定していても、判定の基準値に円記号やカンマを付ける必要はありません。

条件付き書式はセルに表示される文字ではなく、セルが持つ実際の値を基に判定します。

数値の前後に文字が付いていて文字列になっている場合は、正しく判定できないことがあります。

その場合は、数値へ変換するか、後で紹介する数式ルールで条件を組み立てる方法を検討しましょう。

 

エクセルで数値以下を色分けする条件付き書式

続いては、基準値以下の数値を目立たせる設定を確認していきます。

受験者	点数	注意基準
田中	72	60以下
佐藤	55	60以下
鈴木	60	60以下

この設定は、在庫が最低数を下回った商品、合格点に届かない点数、予算を超えた残高の確認などに活用できます。

 

指定の値以下ルールの選択

続いては、セルの強調表示ルールから指定の値以下を選ぶ操作を確認していきます。

まず、点数が入力されたB2からB10の範囲を選択します。

ホームタブの条件付き書式からセルの強調表示ルールを開き、指定の値以下をクリックします。

以上と以下は似た名前ですが、選択する項目を間違えると判定結果が反対になるため注意が必要です。

入力画面が開いたら、基準値として60を入力します。

書式は薄い赤の塗りつぶしと濃い赤の文字を選ぶと、注意対象として分かりやすくなります。

 

低い数値を注意色にする活用

続いては、数値以下の条件付き書式を業務表で使う例を確認していきます。

在庫表では、在庫数が発注点以下になったセルを赤くすると、補充が必要な商品をすぐに発見できます。

進捗管理表では、進捗率が50パーセント以下の案件を黄色や赤にすれば、対応の優先順位を決めやすくなります。

成績表では、合格ライン以下の点数に色を付けることで、再試験や補習の対象者を確認しやすくなります。

注意を促す目的なら赤、確認が必要な段階なら黄、良好な状態なら緑というように色の意味を表内で統一すると効果的です。

色を増やしすぎると、かえって重要なセルが見えにくくなります。

まずは達成と注意の二段階から始めると、読みやすい表にまとまります。

 

空白セルとゼロの扱い

続いては、空白セルとゼロが混在する表での見え方を確認していきます。

通常の指定の値以下ルールでは、空白セルは数値として扱われず、色が付かないことが一般的です。

ただし、数式の結果が空文字列になっているセルや、文字列の0が入力されているセルでは、想定と異なる結果になることがあります。

ゼロは明確な数値なので、基準値が60以下なら色分けの対象です。

未入力とゼロは意味が異なるため、在庫数や実績値を扱う表では区別して運用しましょう。

空白を除外しながら色分けしたいときは、数式ルールで空白ではない条件を追加できます。

 

エクセルで以上と以下を同時に色分けする設定

続いては、同じ数値列に以上と以下の条件を同時に設定する方法を確認していきます。

担当者	達成率	表示
山田	105%	100%以上を緑
高橋	78%	80%超100%未満は無色
伊藤	65%	80%以下を赤

同じセル範囲に複数の条件付き書式を重ねることで、数値の状態を段階的に把握できます。

達成率のような指標は、上限側と下限側の両方を色分けすると、優先して確認すべき箇所が明確になります。

 

達成値以上と注意値以下のルール

続いては、二つの条件付き書式を順番に追加する操作を確認していきます。

まずB2からB10を選択し、100パーセント以上を緑にする指定の値以上ルールを設定します。

次に選択範囲をそのままにして、条件付き書式から指定の値以下を選び、80パーセントを入力して赤い書式を設定します。

設定を終えると、100パーセント以上は緑、80パーセント以下は赤、間の数値は通常の表示になります。

 

ルールの管理画面による優先順位

続いては、複数ルールを確認するための条件付き書式ルールの管理を確認していきます。

ホームタブの条件付き書式からルールの管理を選ぶと、現在のシートに設定されているルールを一覧で確認できます。

適用先の範囲、条件、書式を一画面で見られるため、設定を後から修正するときに便利です。

赤と緑のように条件が重ならない設定では、通常は表示順による問題は起こりません。

しかし、90以上を黄、100以上を緑のように条件が重なる場合は、ルールの並び順と条件を確認する必要があります。

ルールの管理画面では、適用先が意図したセル範囲になっているかを必ず確認しましょう。

停止条件を指定する機能もありますが、初めは条件が重ならない基準で作ると、判定を理解しやすくなります。

 

三段階の色分けへの拡張

続いては、良好、確認、注意の三段階で色分けする考え方を確認していきます。

100パーセント以上を緑、80パーセント以下を赤に設定したうえで、81パーセントから99パーセントまでを黄色にしたい場合があります。

このときは、セルの強調表示ルールにある指定の範囲内を使うと設定できます。

色の段階を増やす場合でも、各色が何を意味するのかを表の利用者が迷わず判断できることが大切です。

色だけでなく、C列に達成、確認、注意などの判定文字を表示すると、印刷時や白黒表示でも状況が伝わりやすくなります。

 

数式を使った以上以下の条件付き書式

続いては、基準値を別セルに置く場合や、空白を除外したい場合に使える数式ルールを確認していきます。

商品名	在庫数	発注点
ノート	18	20
ペン	8	10
封筒	15	15

数式ルールを使うと、行ごとに異なる基準値と比較した色分けもできます。

 

基準セルを固定する数式

続いては、単一の基準セルを参照する数式を確認していきます。

たとえばB2からB10の売上額を、F1に入力した目標額と比較して色分けする場合を考えます。

対象範囲をB2からB10まで選択し、条件付き書式、新しいルール、数式を使用して、書式設定するセルを決定を選びます。

この数式は、B2の値がF1の値以上なら真になるという意味です。

$F$1のように列と行の両方へドル記号を付けると、条件付き書式を範囲内へ適用しても基準セルがF1から動きません。

数式を入力したら書式をクリックし、塗りつぶしタブで緑色を選んでOKを押します。

基準値をF1で変更するだけで、対象範囲の色分けも自動的に更新されます。

目標額を月ごとに変える集計表などでは、数値をルール画面へ直接入力する方法より管理しやすい設定です。

 

行ごとに異なる基準値との比較

続いては、在庫数と発注点を同じ行で比較する数式を確認していきます。

在庫数がB列、発注点がC列にあり、B列の在庫数がC列以下なら赤くする場合、対象範囲はB2からB10です。

条件付き書式では、選択範囲の左上セルを基準にした数式を作ります。

この例ではB2とC2を比較する数式を入力すると、3行目ではB3とC3、4行目ではB4とC4というように、各行へ自動で調整されます。

行ごとに異なる数値を比較したい場合は、B2やC2にドル記号を付けず、相対参照のまま使うことが基本です。

在庫数が発注点以下なら赤、発注点より多い場合は通常表示という判定になります。

同じ考え方で、実績が予算以上、残業時間が上限以下、点数が合格ライン以上といった比較も設定できます。

 

空白を除外する数式

続いては、未入力セルを色分け対象から除外する数式を確認していきます。

セルが空白ではないことも条件に加えると、入力途中の表で不要な色が付くことを避けられます。

AND関数は、複数の条件がすべて満たされたときだけ真を返します。

最初のB2<>””は、B2が空白ではないという条件です。

二つ目のB2<=C2は、在庫数が発注点以下という条件になります。

数式に空白チェックを加えると、未入力と警告対象を明確に分けられます。

 

色分けできない場合の確認ポイント

続いては、設定したのに色が変わらない、想定外のセルまで色が付く場合の確認ポイントを解説していきます。

確認項目	よくある状態	対応
セルの種類	数値が文字列	数値へ変換
適用先	範囲がずれている	ルールの管理で修正
数式	参照が固定されすぎる	ドル記号を見直す

原因を一つずつ確認すれば、多くのケースで条件付き書式を修正できます。

 

数値が文字列として入力されている状態

続いては、セル内の値が数値ではなく文字列になっている状態を確認していきます。

セルの左上に緑の三角形が表示され、数値が文字列として保存されていますと案内される場合があります。

文字列として保存された100000は、条件付き書式の数値比較で期待通りに扱われないことがあります。

セルを選択して警告アイコンから数値に変換を選ぶと、数値として扱えるようになります。

Webサイトや他システムから貼り付けたデータは、見た目が数字でも文字列になっていることがあるため注意しましょう。

セルの表示形式を標準または数値に変更しただけでは、文字列が必ず数値に変わるわけではありません。

VALUE関数、区切り位置、数値に変換などを使い、元のデータ形式を整えることが大切です。

 

適用先の範囲とルールの重複

続いては、条件付き書式ルールの適用先を確認していきます。

ホームタブの条件付き書式からルールの管理を開き、表示する書式ルールを現在のワークシートに変更します。

各ルールの適用先に、=$B$2:$B$10のような範囲が表示されます。

本来はB列だけに設定するつもりが、=$A$2:$C$10になっていると、商品名や判定列まで色付くことがあります。

また、以前設定したルールが残っていて、新しいルールと競合しているケースもあります。

不要なルールはルールの管理で選択して削除し、似た条件を何度も重ねないようにします。

コピーしたセルに条件付き書式が付いたまま残ることもあるため、新しい表を作る際にはルール一覧を確認すると安心です。

 

数式参照と表示形式の見直し

続いては、数式ルールで起こりやすい参照ミスを確認していきます。

たとえばB2からB10へ設定する数式で、=$B$2>=$F$1と入力すると、すべてのセルがB2だけを基準に判定します。

この式はB2だけを確認したいときには正しいものの、各行の値を判定したい場合には不適切です。

各行を判定するなら、=B2>=$F$1のようにB2の列と行は相対参照にします。

日付や時刻もエクセルでは数値として保存されていますが、見た目だけでは比較しにくいことがあります。

数式バーに表示される実際の値と、セルの表示形式を見比べると、判定がずれる原因を見つけやすくなります。

 

まとめ エクセルで数値を以上・以下の条件で色分けする方法

エクセルで数値を以上・以下の条件で色分けするには、ホームタブの条件付き書式を使用します。

指定の値以上では、目標を達成した売上額や点数などを緑色にできます。

指定の値以下では、在庫不足、低い進捗率、注意が必要な数値を赤色や黄色で目立たせられます。

数値が変化しても自動で色が切り替わるため、条件付き書式は更新頻度の高い表ほど効果を発揮します。

以上と以下を同じ範囲に設定すれば、達成と注意を一目で把握できる表になります。

基準値を別セルで管理する場合、行ごとに異なる値と比較する場合、空白を除外したい場合は、数式を使った条件付き書式が便利です。

色が付かないときは、セルが数値として保存されているか、適用先が正しいか、数式の参照が適切かを確認しましょう。

まずは一つの数値列に以上または以下のルールを設定し、実際に値を変えながら動きを確認してみましょう。