[EXCEL]条件付き書式レッスン④ 数式の結果が異常値なら赤背景にする

【まとめ】
数式が入っているセルに色を付ける方法

①「条件付き書式」を設定したいセルを範囲選択
② Alt ⇒ L ⇒ N ⇒「書式ルールの編集」ダイアログ（小ウィンドウ）が開く

③「数式を使用して、書式設定するセルを決定」の数式欄に以下を入れる。
=ISFORMULA(範囲指定の左上セル）

④書式を設定（推奨：水色塗りつぶし）

【関連記事】条件付き書式　マガジン
イントロ　基本　
① 空欄なら色を付ける（入力モレ防止）
② あるセルにデータが入ったら、別セルへの入力を促す
③ 数式が入っているセルに色を付ける
④ 数式の結果が異常値なら赤背景にする
⑤ 一定数以上（以下）を目立たせる
⑥ 土日に色を付ける（日付以外の欄にも）
⑦ 「今日」の日付に色を付ける
⑧ 重複するデータに色を付ける
⑨ データが変わったら目立たせる（同じデータなら空白/違うデータなら白黒反転）
⑩ ガントチャート

【注意事項】　別記事参照
[EXCEL] 条件つき書式～注意～　設定したら行うこと（メモ付け、解除方法表示）と修正方法

【説明】
先日、
[EXCEL] 鬼門 or 魔法の杖？「条件付き書式」、使ってますか？ 使えてますか？　「違い」を目立たせ、仕事を楽にする
で、「条件付き書式」について書きました。
今回は具体例の４です。

数式の結果が異常値なら赤背景にする

例えばこんな表（前回の例と同じ）。

予算額900に対し、執行額が910（単位はさておき）、結果、執行率は101%。

執行率が100%を超えています。
民間企業の営業成績なら問題ない（むしろ歓迎）でしょうけれど、「お役所」の事業だと「予算オーバー」。
予算より多い額は（通常）執行できないので、これは「エラー値」と言えます。
あるいは単なる誤入力、誤計算という可能性もあります。

従って、100%を超えていたら目立たせる、ということも、時に必要です。
例えばこんな感じ。

正直、赤背景はドギつく、また黒文字が見づらいので、私は好みませんが、目立つことは目立ちます。
勿論、「営業成績が前年同期比100%を超えた！」というなら、青背景、という手もあります。

やり方は簡単です。

①「条件付き書式」を設定したいセルを範囲選択する。
＊本レッスンでは、対象セルを最初に全て範囲選択して、「条件付き書式」を設定しています。一方、１つのセルに「条件付き書式」を設定して、「コピー」で他のセルに設定する方法もあります。結果は同じです。
詳しくは、以下を参照願います。
[EXCEL] 条件つき書式～注意～ 設定したら行うこと（メモ付け、解除方法表示）と修正方法 ＋ 他のセルへのコピー方法

② Alt ⇒ H ⇒ L ⇒ N で「新しい書式ルール」を開く。

③「数式を使用して、書式設定するセルを決定」を選択

④「指定の値を含むセルだけ書式設定」を選び、「セルの値」を「次の値より大きい」を選びます。

⑤隣の欄に「１」（半角）と入れます。

＊注意
比較する値は「％」です。
「100より」ではなく、「100%より」「大きければ」という条件なので、整数の「１」を入れます（100%は、比率では「１」です）。

⑥ 「書式」の「塗りつぶし」で赤色を選択します。

⑧「OK」押下
「プレビュー」欄に色が付いていればOK。

ついていなければ、やり直し

⑨更に「OK」押下

これで、数式の答えが100%より大きければ、赤色背景になります。

基準となる数値は自由に設定できる

基準となる数値は「1」（１００％）以外も設定できます。

「～より大きい」だけでなく、「～より小さい」にもできます。

進捗率が著しく低い場合（例:40％未満）の場合に赤色背景にしたければ・・・
（以下は「条件付き書式」が何も設定されていない状態からの作業を想定しています。既存の条件を修正する方法は後述）

「セルの値」が「次の値より小さい」にして、「０．４」（４０％）を入れて、「書式」設定（赤背景）します。

すると、執行額200、執行率22.2％だと・・・

背景が赤くなります。

基準となる数値をセルに入れると、もっと楽

基準となる数値が常に一定（100%超とか40％未満とか）であれば、上の例のように条件の中に数値を入れてしまえばいいのですが、基準となる数値を自由に変えたい場合あります。

その場合は、基準となる数値をセルに入れて、そのセルを条件に入れます。

（以下は「条件付き書式」が何も設定されていない状態からの操作を想定しています。既存の「条件付き書式」を修正する方法は最後に載せてあります）

まず、基準となる数値をれるセルを作ります。
下の例だと、「最低値　４０％」のところです。
そして、この「４０％」（D６）が、執行率の欄（D4）より小さいかどうか、数式を入れて確認してみます。

数式は
=D4<D6
結果は
TRUE
つまり、上の図では、条件に合致しているので、これを条件に入れます。

Alt ⇒ H ⇒ L ⇒ N で「新しい書式ルール」を開き、
「数式を使用して、書式設定するセルを決定」選らび、上で作った数式　＝D4＜D6 を張り付けて、書式を選びます。

これで、出来上がり。
執行額は同じまま、最低値を40%から30%にしてみると・・・

赤背景は消えました。

「～より小さい」または「～より大きい」なら色を付ける方法は？

数値が一定の「幅」を超えている（＝最低値より小さく、最高値より大きい）場合、異常値として赤背景にするにはどうすればいいでしょうか？

これも難しくありません。

= 対象セルの数値 ＜ 最低値
= 対象セルの数値 ＞ 最高値
のいずれかに合致したら異常値として赤背景にする、ということです（最低値＜最高値であり、同時に上記２つを満たす数値はないという前提）。

従って、条件を２つ作ります。

既に
=対象のセルの数値 ＜ 最低値（例：４０％）
が設定されているのなら、
同じ操作をして、
=対象のセル ＞ 最高値（例：１００％）
という条件を追加します。

具体的には、
=D4>D7　＊D7：最高値
という条件を設定します（「書式」で赤背景の塗りつぶしも忘れずに）。

以上でOK。
これで、
最低値を下回っていると・・・

赤背景になります。

逆に、最高値を上回っていると・・・

赤背景になります。

最低値と最高値の間だと・・・

色は付きません。

なお、最低値と最高値の数値の右にある「FALSE」「TRUE」は、条件付き書式を作る際に作ったものですから、消して構いません。
今回は条件への合致の有無を見るために残してあります。

追記(2025/8/24）
「〇〇より小さい（未満）」「〇〇より多い（超過）」は数式を使わなくても設定できます。
「指定の値を含むセルだけを書式設定」を選び、
「セルの値」が「次の値の間」を選び、
２つのセルを入れます（欄をクリックしてからセルをクリック）。

ただし、設定した数値と同じ数値は対象になりません。

従って、「未満」と「超過」は設定できても、「以下」「以上」は設定できません（条件が一つだけなら、「以下」「以上」も設定可能）。
一方、数式を入れる例だと、以下は「＜＝」、以上は「＞＝」とすることで、設定可能です。
（以上、追記終わり）

「条件付き書式」は複数設定できる

上の例では「最低値より小さければ赤背景」という条件に、「最高値より大きければ赤背景」という条件を追加しました。
このようにセルには複数の条件付き書式を設定できます。

・セルに設定されている条件付き書式を確認する方法
Alt ⇒ H ⇒ L ⇒ R　で「条件付き書式ルールの管理」（下の画面）を開く。

＊この例では、既に「数式が入っているセルには水色」という条件も入っており、「条件付き書式」が３つ設定されている。

・複数の条件付き書式がある場合、上にある条件付き書式から適応される。

・「条件付き書式」の順番の入れ替え
条件を選択した後、下図の赤丸の部分で移動させる。
上に来る条件が優先される（一番下の条件を一番上にすると異常値の際の表示が出来なる）。

・特定の「条件付き書式」の修正方法
修正したい条件をクリックしてから「ルール編集」をクリックして修正。
＊
＊修正より、削除⇒作り直し、の方が簡単な場合あり。

・特定の「条件付き書式」の削除
削除したい条件をクリックしてから「ルール編の削除」をクリックします。

今回は以上です。

「条件付き書式」を設定した際の注意事項

これは、どの設定でも同じなので共通の記事にします。
[EXCEL] 条件つき書式～注意～　設定したら行うこと（メモ付け、解除方法表示）と修正方法