エクセルで売上、在庫数、目標値などを扱うとき、数値の大小をすばやく判定したい場面は少なくありません。
2つのセルを比較するだけなら比較演算子で対応できますが、3つの値から最大値や最小値を探したり、結果に応じてセルの色を変えたりするには、関数と条件付き書式を使い分ける必要があります。
比較結果を数式で表示し、条件付き書式で見える化すると、表を確認する時間を大きく短縮できます。
この記事で扱う主な方法は、比較演算子による真偽判定、IF関数による表示の切り替え、MAX関数とMIN関数による3つ以上の値の比較、条件付き書式による自動色分けです。
サンプルデータは1行目が見出しで、2行目以降に数値が入力されている表を前提にします。
それでは、数値の大小を比較する基本から、実務で使いやすい色分けまで確認していきましょう。
エクセルで数値を比較する基本の数式
それではまず、2つの数値を比較して結果を表示する数式について解説していきます。
| 商品 | 実績 | 目標 | 判定 |
|---|---|---|---|
| A商品 | 120 | 100 | 達成 |
| B商品 | 85 | 100 | 未達 |
比較演算子によるTRUEとFALSEの判定
セル同士の大小をそのまま確認したいときは、比較演算子を使います。
たとえばD2セルに「=B2>C2」と入力すると、B2の実績がC2の目標より大きい場合はTRUE、小さい場合または同じ場合はFALSEが表示されます。
=B2>C2
>はより大きい、<はより小さい、=は等しいという意味です。
以上や以下まで含めて比較したい場合は、>=または<=を使います。
「=B2>=C2」は実績が目標以上か、「=B2<=C2」は実績が目標以下かを判定する式です。
TRUEとFALSEは単純な表示に見えますが、後からIF関数、AND関数、OR関数へつなげられるため、比較処理の土台になります。
IF関数による達成と未達の表示
TRUEやFALSEではなく、誰が見てもわかる文字を表示したい場合はIF関数を使いましょう。
=IF(B2>=C2,”達成”,”未達”)
この式はB2がC2以上なら達成、そうでなければ未達と表示します。
IF関数は、条件、条件が成り立つ場合の結果、条件が成り立たない場合の結果の順に指定する仕組みです。
文字列を数式に入れるときは、文字の前後を半角の二重引用符で囲みます。
判定セルのD2を下方向へオートフィルすれば、3行目以降ではB3とC3、B4とC4のように行ごとの比較へ自動調整されます。
空白セルと文字列を含むデータの注意点
比較するセルに空白や文字列が混じると、意図しない判定になることがあります。
入力前の行まで未達と表示したくない場合は、先に空白かどうかを確認する式が便利です。
=IF(OR(B2=””,C2=””),””,IF(B2>=C2,”達成”,”未達”))
この式ではB2またはC2が空白なら何も表示せず、両方に数値があるときだけ比較します。
見た目が空白でも、数式で空文字が返っているセルは完全な空白とは扱われない場合があります。
集計元のデータに数値が文字列として保存されている場合も、比較前に数値へ変換することが重要です。
【操作のポイント】比較式を入力する前に、数値セルが左ではなく右寄せで表示されているかを確認すると、文字列形式の数値を見つけやすくなります。
3つの値を比較するMAX関数とMIN関数
続いては、3つ以上の数値から最大値や最小値を判定する方法を確認していきます。
| 担当者 | 1月 | 2月 | 3月 | 最大値 |
|---|---|---|---|---|
| 田中 | 80 | 110 | 95 | 110 |
MAX関数による最大値の取得
複数の値のうち最も大きい数値を求めるにはMAX関数を使用します。
=MAX(B2:D2)
この式をE2セルへ入力すると、B2からD2までにある80、110、95のうち最大の110が表示されます。
MAX関数は比較対象が3つでも10個でも、範囲を指定するだけで最大値を返します。
「=IF(B2=MAX(B2:D2),”最大”,””)」のように書けば、B2が行内の最大値であるかも判定できます。
同じ最大値が複数ある場合には、該当するすべてのセルが最大と判定されます。
MIN関数による最小値の取得
最も小さい値を調べる場合はMIN関数を使います。
=MIN(B2:D2)
田中さんの例では80が返され、最も低い月の数値をすぐに確認できます。
最低点、最安値、在庫の最少数などを抽出するときにも使える関数です。
MAX関数とMIN関数は空白セルを基本的に無視して計算するため、未入力の月がある管理表でも扱いやすい特徴があります。
ただし、ゼロを空白の代わりに入力している場合、MIN関数はゼロを最小値として返します。
AND関数とOR関数による3条件の比較
3つの値のすべてを満たすか、どれか1つを満たすかという判定にはAND関数とOR関数が適しています。
=IF(AND(B2>=100,C2>=100,D2>=100),”全月達成”,”確認”)
この式では、B2からD2のすべてが100以上のときだけ全月達成と表示されます。
一方で、いずれか1か月でも100以上なら達成候補としたいなら、「=IF(OR(B2>=100,C2>=100,D2>=100),”達成候補”,”未達”)」とします。
ANDはすべての条件がTRUE、ORは1つでもTRUEならTRUEになる関数です。
【操作のポイント】3つの値を横方向で比較する場合は、対象範囲をB2:D2のように固定し、式を下へコピーできる形に整えると管理が楽になります。
比較結果を表示するIF関数の応用
続いては、比較結果を複数の段階に分けて表示するIF関数の応用を確認していきます。
| 商品 | 達成率 | 評価 |
|---|---|---|
| A商品 | 108% | 好調 |
| B商品 | 92% | 注意 |
IFS関数による段階別の評価
達成、未達の2段階ではなく、好調、標準、注意のように評価を分けるならIFS関数が便利です。
=IFS(B2>=1.1,”好調”,B2>=1,”達成”,B2>=0.9,”注意”,TRUE,”要改善”)
達成率が110パーセント以上なら好調、100パーセント以上なら達成、90パーセント以上なら注意、それ以外なら要改善と表示されます。
条件は大きい数値から順に並べることが、IFS関数を正しく使う重要なコツです。
先にB2が0.9以上という条件を書くと、110パーセントのデータも注意で止まってしまいます。
入れ子のIF関数による互換性への対応
IFS関数が使えない古いExcelでは、IF関数を入れ子にして同じ判定を作成できます。
=IF(B2>=1.1,”好調”,IF(B2>=1,”達成”,IF(B2>=0.9,”注意”,”要改善”)))
式は長くなりますが、考え方はIFS関数と同じです。
途中のかっこが不足すると数式エラーになるため、入力後は数式バーでかっこの対応を確認しましょう。
判定基準を別セルに置く設計にすると、基準変更時に数式を書き換える手間を減らせます。
絶対参照を使った基準値との比較
目標値がF1セルにあり、各行の実績をその共通目標と比較する場合は絶対参照を使います。
=IF(B2>=$F$1,”目標超過”,”目標以下”)
$F$1と書くことで、D2からD10へ数式をコピーしても比較する目標セルはF1のままです。
列だけ固定するF$1、行だけ固定する$F1もあり、表の向きに応じて使い分けます。
【操作のポイント】数式入力中にF4キーを押すと、相対参照と絶対参照の形式を切り替えられます。
条件付き書式による数値の色分け
続いては、比較結果に合わせてセルを自動で色分けする条件付き書式を確認していきます。
| 担当者 | 実績 | 目標 |
|---|---|---|
| 佐藤 | 125 | 100 |
| 鈴木 | 78 | 100 |
指定の値より大きい数値の強調
固定の基準値より大きい数値を色付けするには、条件付き書式のセルの強調表示ルールを使います。
色分けしたい実績の範囲B2:B10を選択し、ホームタブの条件付き書式からセルの強調表示ルール、指定の値より大きいを選択します。
表示された画面で100を入力し、淡い緑の塗りつぶしなどを選べば、100より大きいセルだけが自動で色付きになります。
条件付き書式は元の数値を変更せず、表示だけを変える機能です。
別セルとの比較に使う数式ルール
実績列Bと目標列Cを行ごとに比較する場合は、数式を使用して書式設定するルールを選びます。
まずB2:B10を選択し、条件付き書式、新しいルール、数式を使用して書式設定するセルを決定を順に選択します。
=B2>=C2
数式欄へ入力して緑色の塗りつぶしを設定すると、各行で実績が目標以上のセルだけが緑色になります。
未達を赤色にしたい場合は、同じ適用先に「=B2<C2」という2つ目のルールを追加します。
数式ルールでは、選択範囲の左上セルであるB2を基準に式を書くことが大切です。
色分けの優先順位とルール管理
複数の条件付き書式を設定したときは、条件付き書式のルールの管理から優先順位を確認できます。
たとえば100以上を緑、80未満を赤、80以上100未満を黄にする場合、条件が重ならないよう式を設計すると管理しやすくなります。
同じセルに複数のルールが当たる場合は、一覧の上にあるルールが優先されることがあります。
意図しない色になるときは、適用先の範囲とルールの順序を見直すのが解決への近道です。
【操作のポイント】条件付き書式の管理画面では、適用先が=$B$2:$B$10になっているかを確認し、不要な古いルールは削除して整理します。
大小比較で起こりやすいエラーと対処
続いては、数値の比較や色分けで起こりやすいエラーと対処法を確認していきます。
| 症状 | 主な原因 | 確認内容 |
|---|---|---|
| 色が付かない | 適用先が違う | 選択範囲 |
| 比較が逆になる | 参照のずれ | $の位置 |
数値が文字列として入力されている場合
見た目は100でも、セルに文字列として保存されていると、比較演算や条件付き書式が期待どおりに動かないことがあります。
セル左上の緑色の三角マーク、左寄せ表示、数式のエラー表示などは確認材料です。
警告アイコンから数値に変換を選ぶか、VALUE関数を使って数値へ変換します。
コピー元がWebページやCSVの場合は、文字列形式の数値が混在しやすいため注意が必要です。
パーセントと小数点の比較
セルに100パーセントと表示されていても、Excel内部では1として扱われます。
そのため、達成率セルを「=B2/C2」で計算してパーセント表示にしているなら、100パーセント以上の条件は「=D2>=1」と指定します。
「=D2>=100」と書くと10000パーセント以上という判定になるため、色が付かない原因になります。
表示形式と実際の数値は別に考えることが、割合を比較する際の基本です。
オートフィル時の参照ずれ
数式をコピーした後に判定がずれる場合は、相対参照と絶対参照を確認します。
共通の目標セルを参照する式で$記号を付けていないと、下へコピーするたびに目標セルも移動します。
条件付き書式では、適用先の先頭セルと数式中の先頭セルを一致させることも重要です。
【操作のポイント】数式をコピーした後は、先頭行と最終行の数式バーを見比べ、比較対象が正しいセルを参照しているか確認しましょう。
まとめ エクセルで数値の大小を比較する関数と色分けの方法
エクセルで数値の大小を比較するには、まず>、<、>=、<=などの比較演算子で条件を作り、必要に応じてIF関数で達成や未達の表示へ変換します。
3つ以上の値を比べるときは、MAX関数で最大値、MIN関数で最小値を求める方法がわかりやすく、AND関数とOR関数を組み合わせると複数条件の判定も可能です。
さらに条件付き書式を使えば、目標以上を緑、未達を赤のように自動色分けでき、比較結果を直感的に把握できます。
数値が文字列になっていないか、パーセントの実値が1であること、コピー時に参照先がずれていないかも確認しましょう。
比較演算、関数、条件付き書式を組み合わせて、見やすく判断しやすいExcel表を作っていきましょう。
