Excelで売上、点数、在庫数、進捗率などを管理していると、基準値を下回る数値をひと目で見つけたい場面があります。
そのような場合は、条件付き書式を使って100以下のセルだけを赤字に設定すると、確認すべきデータが表の中で目立つようになります。
100以下を赤字にする基本操作は、対象範囲を選択してから条件付き書式で「次の値以下」を指定する方法です。
数値そのものは変更されず、表示形式だけが自動で切り替わります。
この記事では、Excelで100以下を赤字にする条件付き書式の設定方法から、数式を使った応用、うまく赤字にならない場合の確認点まで解説します。
サンプルデータは1行目を見出し行として扱います。
エクセルで100以下を赤字にする条件付き書式の設定方法
| 商品名 | 在庫数 | 判定 |
|---|---|---|
| ノート | 85 | 補充対象 |
| ペン | 125 | 通常 |
| ファイル | 100 | 補充対象 |
それではまず、数値が100以下になったときだけ文字色を赤に変える基本操作について解説していきます。
条件付き書式はセルに条件を登録し、入力値が条件に一致したときだけ書式を適用するExcelの機能です。
在庫数がB2からB10に入力されている場合は、B2からB10を選択して設定します。
見出しであるB1を選択範囲へ含めないことが、表を見やすく保つポイントです。
対象セル範囲の選択
在庫数のように判定したい数値が入力されているセル範囲を、最初にドラッグして選択します。
今回の例では、B列の1行目が見出しのため、B2からB10を選択します。
列全体に将来の入力分まで適用したい場合はB2以降を広く選択できますが、空白セルが多い大きなシートでは必要な範囲に絞るほうが管理しやすいでしょう。
選択した範囲に新しい数値を追加しても自動判定したいときは、Excelテーブルへ変換しておく方法も便利です。
テーブルではデータ行の追加に応じて条件付き書式の適用範囲も伸びやすくなります。
次の値以下ルールの指定
続いて、ホームタブにある条件付き書式をクリックし、セルの強調表示ルールから指定の値以下を選びます。
表示された入力欄へ100と入力すると、100より小さい値と100と同じ値が条件の対象になります。
「以下」は100を含む表現なので、100ちょうどのセルも赤字になります。
100未満だけを赤字にしたい場合は、指定の値以下ではなく、数式を使用するルールで条件を作る必要があります。
【操作のポイント】設定前に数値セルだけを選択すると、商品名や見出しまで赤くなるミスを防げます。
赤い文字色の選択
指定の値以下のダイアログでは、右側の書式候補から赤い文字を選択できます。
既定の候補には淡い赤の塗りつぶしと濃い赤の文字が用意されているため、急いで設定したいときに役立ちます。
文字色だけを赤にしたい場合は、ユーザー設定の書式を選び、フォントタブで色を赤へ変更してください。
塗りつぶしを付けない設定にすると、表の配色を維持しながら重要な数値だけを目立たせられます。
OKを押した直後から条件に当てはまるセルが赤字になり、元の値は変化しません。
【操作のポイント】赤字と背景色を同時に使う場合は、印刷時にも読み取れる濃さかどうかを確認しましょう。
100以下の条件と100未満の条件の違い
| 入力値 | 100以下 | 100未満 |
|---|---|---|
| 99 | 赤字 | 赤字 |
| 100 | 赤字 | 通常色 |
| 101 | 通常色 | 通常色 |
続いては、条件付き書式で混同しやすい100以下と100未満の違いを確認していきます。
基準値ちょうどを警告対象にするかどうかは、在庫管理や成績管理の運用ルールに合わせて決める必要があります。
100以下の条件は値が100以下の場合です。
100未満の条件は値が100より小さい場合です。
100を含める指定の考え方
発注点を100個と決めている在庫表では、在庫が100個になった時点で補充対象にしたいことがあります。
この場合は100以下を赤字にすると、100、99、80のような数値をまとめて判別できます。
発注の判断を遅らせたくない表では、境界値を含める設定が実務に合いやすい傾向があります。
一方で、100点を合格ラインにする試験結果では、100点を赤字にする必要は通常ありません。
表が何を示すものかを考え、色の意味を利用者間でそろえることが大切です。
100未満を数式で指定する方法
100未満だけを赤字にしたいときは、条件付き書式の新しいルールから数式を使用して、書式設定するセルを決定を選択します。
数式の例
=B2<100
適用先がB2からB10なら、数式の先頭セルはB2にします。
Excelは各行に合わせて参照先を自動的にずらすため、B3ではB3<100、B4ではB4<100として判定されます。
数式のセル番地は、選択範囲の左上セルを基準に書くことが重要です。
【操作のポイント】100未満の条件では、比較記号を<にし、=を加えないよう注意してください。
文字列として入力された数値の確認
見た目は100でも、セル内で文字列として保存されていると、期待どおりに条件付き書式が動かない場合があります。
セル左上の緑色の三角形や、数式バーで値の配置を確認すると判断しやすくなります。
文字列の数字は、エラー表示の変換機能、区切り位置、VALUE関数などで数値へ直せます。
全角数字、先頭のアポストロフィ、スペースが混じるデータも判定エラーの原因です。
条件付き書式を疑う前に、セルの値が本当に数値として認識されているかを確認しましょう。
【操作のポイント】外部システムから貼り付けたデータは、文字列化していないかを最初に確認します。
条件付き書式ルールの管理画面
| 適用先 | ルール | 書式 |
|---|---|---|
| B2:B10 | セルの値が100以下 | 赤い文字 |
| C2:C10 | セルの値が80以下 | 赤い塗りつぶし |
続いては、設定済みの条件付き書式を変更、確認、削除するための管理画面について確認していきます。
複数のルールがあるシートでは、適用先と優先順位を把握することで表示の混乱を防げます。
条件付き書式のルール管理は、ホームタブの条件付き書式からルールの管理を選んで開きます。
適用先の範囲確認
ルールの管理画面では、どの条件がどのセル範囲に適用されているかを確認できます。
赤字にしたい範囲がB2からB100であるのに、B2からB10だけになっていれば、11行目以降は判定されません。
適用先の欄を編集すれば、既存ルールを作り直さずに対象範囲を広げられます。
離れた範囲へ同じルールを使う場合は、複数範囲を指定することも可能です。
【操作のポイント】表へ行を追加した後は、適用先の末尾まで範囲が伸びているかを確認しましょう。
ルールの優先順位
同じセルに赤字のルールと黄色背景のルールが重なる場合、ルールの順序によって表示結果が変わることがあります。
ルール管理画面の上へ移動、下へ移動を使うと、優先順位を調整できます。
条件が成立したら後続ルールを適用しない停止する場合は、条件を満たす場合は停止のチェックも検討します。
色が思ったとおりに表示されないときは、値だけでなくルールの並び順も確認対象です。
ただし、ルールを増やし過ぎると管理が難しくなるため、意味が近い条件は整理するとよいでしょう。
【操作のポイント】警告レベルが高いルールを上位へ配置すると、利用者に意図が伝わりやすくなります。
ルールの編集と削除
基準値を100から120へ変更したい場合は、該当ルールを選んでルールの編集をクリックします。
設定画面で100を120に書き換えれば、同じ書式のまま判定条件だけを変更できます。
不要なルールは、ルールの削除から選択したルールを削除します。
セルの色を手作業で元に戻しても、条件付き書式のルール自体は残ることがあります。
自動で再び赤くなる場合は、書式を消すのではなくルール管理画面で削除します。
【操作のポイント】削除前に適用先を確認し、別の表で使っているルールを消さないようにしましょう。
数式を使った行全体の赤字表示
| 商品名 | 在庫数 | 担当者 |
|---|---|---|
| ノート | 85 | 田中 |
| ペン | 125 | 佐藤 |
続いては、在庫数が100以下のときに、そのセルだけではなく該当行全体を赤字にする方法を確認していきます。
商品名、数値、担当者を一緒に見せたい一覧表では、数式を使う条件付き書式が役立ちます。
適用先がA2からC10の場合の数式
=$B2<=100
絶対参照と相対参照の使い分け
数式の$B2では、列Bだけを固定し、行番号は各行に合わせて変化させます。
そのためA2からC10へ条件を適用しても、各行では必ず同じ行のB列にある在庫数を確認できます。
$を付けずにB2<=100と書くと、C列ではC列の値を参照するようにずれ、意図しない判定になる可能性があります。
行全体を判定する数式では、判定に使う列だけを$で固定する書き方が基本です。
一方、基準値を別セルへ入力する場合は、基準値セルの列と行の両方を固定します。
【操作のポイント】行全体へ適用する前に、数式内の列記号だけに$が付いているかを確認してください。
Excel画面での設定イメージ
新しい書式ルールの画面で数式を選び、=$B2<=100を入力してから、書式で赤いフォントを指定します。
適用先にはA2からC10を指定するため、条件を満たす行では商品名から担当者まで赤字になります。
矢印で示したように、ホームタブの条件付き書式から新しいルールへ進む流れです。
【操作のポイント】数式を入力した後は、適用先が行全体の範囲になっているかを必ず確認しましょう。
基準値を別セルで管理する方法
基準値を毎月変更する表では、たとえばF1へ100と入力し、そのセルを参照する設定が便利です。
数式の例
=$B2<=$F$1
F1を120へ変更するだけで、条件付き書式の基準も自動的に120へ変わります。
数式内の$F$1は列と行の両方を固定する絶対参照です。
基準値をセルで管理すると、ルール編集を開かずに運用条件を変更できます。
基準値セルには「赤字判定基準」のような説明を近くに表示しておくと、第三者にも内容が伝わりやすくなります。
【操作のポイント】基準値セルを変更できる人が限られる場合は、シート保護も併用すると安心です。
赤字にならない場合の確認項目
| 確認箇所 | 主な原因 |
|---|---|
| 値の種類 | 数字が文字列として保存されている |
| 適用先 | 対象セルが範囲外になっている |
| ルール順序 | 別ルールの書式が優先されている |
続いては、100以下なのに赤字にならない、または関係ないセルが赤字になる場合の確認項目を確認していきます。
多くのトラブルは数値形式、適用先、参照セルの設定を見直すことで解消できます。
数値形式と空白セル
セルに100と表示されていても、先頭に空白やアポストロフィがあると文字列として扱われることがあります。
数値へ変換してから条件付き書式を確認すると、正しく赤字になる場合があります。
空白セルを赤字にしたくない場合は、通常の100以下ルールでは空白が影響する設定になっていないかを確認してください。
数式ルールなら、空白でない条件も追加できます。
空白を除外する数式の例
=AND(B2<>””,B2<=100)
AND関数を使うと、値が入っていて、かつ100以下という二つの条件を同時に指定できます。
【操作のポイント】空白を含む一覧表では、空白除外の条件を加えると誤表示を防ぎやすくなります。
コピーと貼り付け後の確認
別のシートからセルをコピーしたとき、条件付き書式が一緒に貼り付けられてルールが重複することがあります。
反対に値のみ貼り付けを行うと、必要な条件付き書式が適用されないこともあります。
貼り付け後に色の表示が変わった場合は、ルール管理画面で同じ条件が複数存在しないかを確認しましょう。
書式を維持したい場合は、コピー元とコピー先の適用先を見比べることが有効です。
データを更新した直後の表示異常は、貼り付け方法とルールの重複を確認するのが近道です。
【操作のポイント】定期更新する表では、値だけを貼り付ける場所と書式を維持する場所を決めておきましょう。
手動書式との競合
セルへ手動で黒以外の文字色を設定していると、条件付き書式との見分けが付きにくくなることがあります。
条件付き書式は通常のセル書式より優先して表示されますが、複数ルールがあると結果は複雑になります。
不要な手動書式はクリアから書式のクリアで整理できます。
ただし、書式のクリアは条件付き書式のルールを消す操作とは異なるため、実行前に対象範囲を確認してください。
色の原因を一つずつ切り分けると、表の表示ルールを安全に整えられます。
【操作のポイント】重要な表では、修正前にファイルを保存し、変更後の表示をすぐ確認しましょう。
まとめ エクセルで100以下を赤字にする条件付き書式の方法
Excelで100以下を赤字にするには、対象の数値セルを選択し、条件付き書式のセルの強調表示ルールから指定の値以下を選び、値に100を入力します。
文字色だけを赤くしたい場合は、ユーザー設定の書式でフォントの色を赤に設定すると、表のデザインを保ちやすくなります。
100ちょうどを含めるなら100以下、100を除外するなら数式で=B2<100を使うという違いを押さえることが重要です。
行全体を赤字にしたい場合は、=$B2<=100のように判定列を固定した数式を使います。
基準値を変更する機会が多い表では、別セルへ基準値を入力し、=$B2<=$F$1のように参照すると更新が簡単です。
赤字にならない場合は、数値が文字列になっていないか、適用先が正しいか、条件付き書式の優先順位に問題がないかを順に確認しましょう。
条件付き書式を活用すれば、膨大な表でも注意が必要な数値をすばやく探せるようになります。
