エクセルでは、数値や文字列、日付などの値に応じてセルの色、文字色、アイコン、データバーを自動的に変更できます。
売上の目標達成状況、在庫の不足、納期の遅れ、重複データなどを見つけやすくしたいときに便利なのが条件付き書式です。
複数条件の条件付き書式は、数式を使うことで柔軟に設定できます。
条件付き書式で値によって表示を変える基本手順です。
・対象範囲を選択する
・条件付き書式からルールを作成する
・複数条件は数式ルールとルールの優先順位で調整する
この記事では、1行目に見出しがある売上管理表を例に、エクセルで値によって表示を変える方法を詳しく解説します。
値によってセルの色を変える基本設定
それではまず、値を基準にセルの表示を変える基本的な条件付き書式について解説していきます。
| 担当者 | 売上 | 目標 | 判定 |
|---|---|---|---|
| 田中 | 120000 | 100000 | 達成 |
| 佐藤 | 85000 | 100000 | 未達 |
| 鈴木 | 100000 | 100000 | 達成 |
セルの強調表示ルール
数値が指定値より大きい、指定値より小さい、指定値と等しいといった単純な判定には、セルの強調表示ルールを使います。
たとえば売上が100000以上なら緑、100000未満なら赤にしたい場面です。
売上の範囲であるB2からB4を選択し、ホームタブの条件付き書式からセルの強調表示ルールを選びます。
次に指定の値より大きいを選択し、100000を入力して任意の書式を指定します。
対象範囲を先に選択してからルールを作ることが、意図しないセルへの適用を防ぐ基本です。
同じ範囲に対して指定の値より小さいルールを追加すれば、売上額の大小をひと目で把握できます。
文字列を条件にした表示変更
条件付き書式は数値だけでなく、完了、確認中、保留といった文字列にも利用できます。
たとえばD列の判定に未達という文字が入ったセルだけを赤く表示すると、対応が必要な行を見逃しにくくなります。
対象のセル範囲を選択してから、条件付き書式、セルの強調表示ルール、文字列を含むの順にクリックします。
入力欄に未達と入力し、濃い赤の文字列や淡い赤の塗りつぶしなどを選択しましょう。
文字列の条件では、全角と半角、余計な空白にも注意が必要です。
入力規則で候補を統一しておくと、表記ゆれによって色が付かない問題を減らせます。
日付を条件にした期限管理
納期や更新期限を管理する表では、日付に応じた条件付き書式が役立ちます。
期限が今日以前なら赤、7日以内なら黄色にすることで、優先順位を視覚的に整理できます。
条件付き書式の日付のルールには、昨日、今日、明日、過去7日間、来月などの定型条件が用意されています。
より細かな期限管理には数式を使う方法が適していますが、最初は日付のルールから試すと操作を理解しやすいでしょう。
【操作のポイント】日付が文字列として入力されている場合は、条件付き書式が日付として判定できないことがあります。
複数条件を設定するルールの管理
続いては、複数条件の条件付き書式を同じセル範囲に設定する方法を確認していきます。
| 進捗率 | 表示色 | 意味 |
|---|---|---|
| 100パーセント以上 | 緑 | 目標達成 |
| 80パーセント以上100パーセント未満 | 黄 | 確認が必要 |
| 80パーセント未満 | 赤 | 対応が必要 |
ルールの管理画面
複数の条件を設定した後は、ホームタブの条件付き書式からルールの管理を開きます。
この画面では、どの範囲にどのルールが適用されているかを一覧で確認できます。
適用先が=$B$2:$B$20のようになっているかを確認し、必要なら範囲選択ボタンで修正しましょう。
表示が想定と違うときは、最初に適用先の範囲を確認すると原因を見つけやすいです。
コピーしたセルに不要な条件付き書式が残ることもあるため、表を編集した後の確認が大切です。
ルールの優先順位
同じセルが複数の条件に当てはまるときは、ルールの管理画面で上にあるルールが優先されます。
たとえば売上が100000以上なら緑というルールと、空白ではないなら黄色というルールがある場合、並び順によって表示結果が変わります。
ルールを選択して上へ移動または下へ移動をクリックすると、優先順位を調整できます。
より限定的で重要な条件を上側に置くと、管理しやすい構成になります。
色分けの設計では、緊急の赤、注意の黄、正常の緑という順に考えると、利用者にも意味が伝わりやすいでしょう。
真の場合は停止の設定
ルールの管理画面には、真の場合は停止というチェック項目があります。
ここにチェックを入れると、そのルールが真になったセルでは、それより下のルールを判定しません。
たとえば空白セルを灰色にするルールを上位に置き、真の場合は停止を有効にすると、空白セルに他の色が重なることを防げます。
真の場合は停止は、複数条件が重なる表で表示を安定させる設定です。
【操作のポイント】ルールの順序を変えたら、条件に該当する値を実際に入力して結果を確認しましょう。
数式を使った複数条件の条件付き書式
続いては、数式を使って複数の条件を判定する設定を確認していきます。
| 商品名 | 在庫数 | 発注点 | 状態 |
|---|---|---|---|
| ノート | 15 | 20 | 要発注 |
| ペン | 50 | 20 | 通常 |
AND関数で二つの条件を満たす行
複数条件を同時に満たしたときだけ色を付けるには、AND関数を使います。
在庫数が発注点未満で、なおかつ在庫数が空白ではない行を赤くする例を考えます。
=AND($B2<$C2,$B2<>””)
この数式は、B2の在庫数がC2の発注点より小さく、B2が空白でない場合にTRUEを返します。
条件付き書式ではTRUEになったセルまたは行に書式が適用されます。
$B2の列記号だけにドル記号を付けることで、複数列へ書式を広げても在庫数の列を固定できます。
対象範囲をA2からD20のように行全体で選べば、要発注の商品を横一列で強調できます。
OR関数でいずれかの条件を満たす行
二つの条件のどちらかに該当すれば表示を変えたいときは、OR関数を使用します。
たとえば在庫数が10未満、または状態が販売終了なら、行全体を灰色にする場合です。
=OR($B2<10,$D2=”販売終了”)
OR関数は、指定した条件のうち一つでもTRUEならTRUEを返します。
数値の比較と文字列の比較を一つの数式に組み合わせられる点が、数式ルールの大きな利点です。
文字列は必ず半角のダブルクォーテーションで囲み、実際のセルの表記と同じ文字を入力しましょう。
新しい書式ルールの作成
数式ルールは、ホームタブの条件付き書式から新しいルールを選んで作成します。
ルールの種類で数式を使用して、書式設定するセルを決定を選択し、数式入力欄へ判定式を入力します。
次に書式ボタンをクリックして、塗りつぶし、フォント、罫線を設定します。
数式は選択範囲の左上セルを基準に作成することが重要です。
たとえば適用先がA2からD20なら、数式も2行目を基準にして作ります。
【操作のポイント】数式を入力した後は、書式のプレビューが意図した色になっているか確認しましょう。
セル参照と絶対参照の使い分け
続いては、条件付き書式の数式で重要になるセル参照の使い分けを確認していきます。
| 参照式 | コピー時の動き | 利用場面 |
|---|---|---|
| B2 | 行と列が変化 | 各セルを個別判定 |
| $B2 | 列だけ固定 | 行全体の色付け |
| $B$2 | 行と列を固定 | 基準値との比較 |
行全体を色付けする参照
A列からD列までの行全体を、D列の状態に応じて色付けする場合は、列を固定します。
=$D2=”未対応”
この式をA2からD20へ適用すると、どの列のセルを判定するときもD列の値を確認します。
行番号にはドル記号を付けないため、3行目ではD3、4行目ではD4というように判定対象が移動します。
行全体の強調では、判定に使う列だけを固定する$D2が基本形です。
基準セルと比較する参照
たとえばF1セルに目標値を入力し、売上がその目標値を下回るセルを色付けする場合があります。
=B2<$F$1
F1はどの行でも同じ目標値を参照するため、列と行の両方を固定します。
基準値を別セルに置くと、数式を作り直さずに判定基準だけを変更できます。
月ごとに目標が変わる管理表でも、運用しやすい方法です。
空白セルを除外する数式
空白セルまで条件に該当して色が付く場合は、空白ではないという条件を追加します。
=AND(B2<100000,B2<>””)
空白は比較の仕方によってゼロのように扱われることがあり、未入力行まで赤くなる原因になります。
条件付き書式では、空白をどう扱うかを数式に明記すると表の見た目が整います。
【操作のポイント】ドル記号はF4キーで切り替えられますが、条件付き書式では適用範囲との関係も確認してください。
アイコンセットとデータバーの活用
続いては、色だけでは伝わりにくい数値の差を見やすくする表示方法を確認していきます。
| 達成率 | アイコン例 | 利用目的 |
|---|---|---|
| 110パーセント | ● | 好調 |
| 85パーセント | ● | 注意 |
| 60パーセント | ● | 要対応 |
データバーによる数値比較
データバーは、セル内に横棒を表示して数値の大きさを比較できる機能です。
売上、作業時間、在庫数など、量の差を一覧で確認したい列に向いています。
対象範囲を選び、条件付き書式からデータバーを選択すると、数値に応じた棒が表示されます。
データバーは数値そのものを消さずに視覚化できるため、報告資料にも使いやすい機能です。
アイコンセットのしきい値
アイコンセットでは、矢印、丸、信号などの記号で状態を表せます。
既定の設定は割合による判定ですが、ルールの管理からルールの編集を開くと、数値を基準に変更できます。
達成率が100以上なら緑、80以上なら黄、それ未満なら赤というように、業務に合うしきい値を設定しましょう。
アイコンだけで判断させる場合でも、列見出しや説明を用意して意味を共有することが大切です。
カラースケールの注意点
カラースケールは、最大値から最小値までを色の濃淡で表す機能です。
大量の数値から傾向を探す用途には便利ですが、月によって最大値と最小値が変わると色の意味も変化します。
目標値を基準に明確な判定をしたい表では、数式ルールやセルの強調表示ルールの方が適する場合があります。
【操作のポイント】見た目の効果だけで選ばず、利用者が次に取るべき行動を判断できる表示を選びましょう。
まとめ エクセルで値によって表示を変える方法
エクセルで値によって表示を変えるには、条件付き書式を利用します。
単純な数値や文字列の判定にはセルの強調表示ルールを使い、複数条件を組み合わせる場合は数式を使用して新しいルールを作成します。
AND関数はすべての条件を満たす場合、OR関数はいずれかの条件を満たす場合の判定に使えます。
行全体を色付けするときは$B2のように列だけを固定し、共通の基準値を参照するときは$F$1のように行と列を固定することが重要です。
複数ルールを設定した後は、ルールの管理から適用先、優先順位、真の場合は停止を確認しましょう。
データバーやアイコンセットも組み合わせれば、数値の大小や進捗状況をさらに直感的に伝えられます。
条件付き書式を活用して、確認しやすく、見落としにくいエクセル表を作っていきましょう。
