Excel の条件付き書式を活用して視覚的に見やすい資料を作ろう

条件付き書式は、値に応じて文字の色を自動で変えることができる機能です。書式を設定したいセルを選択し、ホーム タブのスタイル グループにある条件付き書式から実行したいパターンを選んでいくだけで簡単に設定できます。

たとえば、売り上げの集計を行う際、条件付き書式を利用することで、どの程度進捗通り進んでいるか色の濃淡で視覚的に表現することができます。また、進捗状況が 90% 以下の行を探し出して自動的に色を変更する、などの設定も可能です。

よく利用されるパターンはテンプレートとして用意されているため、これらを使えばすぐに利用できます。実際にやり方を見ていきましょう。

特定の文字が含まれている場合に反応する条件付き書式を設定する場合は、セルの強調表示ルールを利用します。
文字列を選ぶとそのセル内の文字と一致したものが初期選択されるため、動作イメージをプレビューしながら書式を選ぶことが可能です。

例として、以下の商品一覧で「ねじ」の文字が含まれている製品分類セルに対して背景色とフォントの色を設定してみましょう。

1. 条件付き書式を設定するセルの範囲 (例では製品分類の列のセル) を選択する

2. 「ホーム」タブの「条件付き書式」をクリックし、「セルの強調表示ルール」から「文字列」をクリックする

3. 「次の文字列を含むセルを書式設定」の入力欄に、書式設定する文字列 (例では「ねじ」) を入力する (このとき選択範囲で該当するセルがあれば、書式で指定された形式で表示される)

4. 書式から「ユーザー設定の書式」を選択する

5. セルの書式設定の「フォント」タブで色 (例では青) を設定する

6. 「塗りつぶし」タブで背景色 (例では薄い水色) を設定し、「OK」ボタンをクリックする

7. 「OK」ボタンをクリックする

8. 条件付き書式が設定され、設定した文字列のセルが書式設定されて表示される

「ねじ」と入力されたセルが書式設定されて表示されました。
同じ範囲に複数の条件付き書式を設定することも可能です。

同じく、「プレート」の文字列でも条件付き書式を設定すると、以下のようなイメージになります。

選択した範囲に設定された最大値と最小値の中からグラデーションをつけて色を設定したい場合には、カラー スケールを利用します。

選択した範囲の内容を読み取ってくれるので、該当する値がどの範囲にあるのかがひとめで把握できます。また、数値の分布が視覚的に分かる点でも有用です。

以下の例では、売上表で売上金額が低いときに濃い赤で表示されるようカラー スケールを設定します。

1. カラー スケールを設定するセルの範囲 (例では売上金額の列のセル) を選択する

2. 「ホーム」タブの条件付き書式をクリックし、カラー スケールから設定するカラー スケールのボタンをクリックする (このときカラー スケールのボタンをポイントすると選択範囲がプレビュー表示される)

3. カラー スケールが設定される

金額や在庫数などによって色を設定したい場合は、「指定の値より大きい」「指定の値より小さい」という比較による書式設定が利用できます。

在庫数などは業種業態や会社などによって適正値が異なるため、自身で設定した値と比較したほうがより正確な状況を把握することが可能です。比較用の数値を別のセルで管理すれば、基準が変わっても条件付き書式の設定を変更しないで利用できます。

以下の例では、在庫表で最低在庫を下回った場合に赤で表示されるよう設定します。

1. 条件付き書式を設定するセルの範囲 (例では在庫数の列のセル) を選択する

2. 「ホーム」タブの「条件付き書式」をクリックし、「セルの強調表示ルール」から「指定の値より小さい」をクリックする

3. 次の値より小さいセルを書式設定の矢印ボタンをクリックする

4. 基準となる値が入力されたセル (例では最低在庫の下の F3 セル) をクリックし、矢印ボタンをクリックする (このとき選択範囲の中の該当するセルがプレビュー表示される)

5. 「OK」ボタンをクリックする

6. 条件付き書式が設定され、設定した値より小さい値のセルが書式設定されて表示される

「指定の値より大きい」「指定の値より小さい」のどちらかではなく、上限と下限の両方を設定するときに 1 つのルールを利用する場合は、「その他のルール」で関数を利用した条件を作ります。

1 つのルールで設定できる書式は 1 つのため、複数ある条件ごとに色を分ける場合は複数の条件と書式のパターンをルール設定します。
また、複数の条件のいずれも同じ書式を設定する場合は、OR 関数を利用して 1 つの書式設定に式を設定します。

OR 関数は引数に比較演算子を使った条件を設定し、設定した条件のいずれかが条件を満たすときに TRUE を返す関数です。値が 10 以下または 50 以上のような条件のときに利用します。
式で表すと「=OR(セル<=“10”,セル>=“50”)」となります。

さらに複雑な条件を設定したい場合は、AND 関数を利用するとよいでしょう。
AND 関数は OR 関数と同様に引数に比較演算子を使った条件を設定しますが、設定した条件のすべてを満たすときに TRUE を返す関数です。分類が A かつ 10 以下または  50以上のように AND 関数と OR 関数を組み合わせることで、1 つの式で複雑な条件が設定できます。
式で表すと「=AND(分類=A,OR(セル<=“10”,セル>=“50”)」となります。

以下の例では、製品分類が「ねじ」のときで、なおかつ在庫の上限と下限からセルの値が外れる場合に、条件付き書式を設定します。

1. 条件付き書式を設定するセルの範囲 (例では在庫表の在庫数の列) を選択する

2. 「ホーム」タブの「条件付き書式」をクリックし、「セルの強調表示ルール」から「その他のルール」をクリックする

3. 「数式を使用して、書式設定するセルを決定」をクリックし、「次の数式を満たす場合に値を書式設定」に条件式 (例では「=AND($B3=$F$3,OR($D3<$G$3,$D3>=$H$3))」、在庫表 B 列の値が在庫限度の製品分類「ねじ」と一致し、かつ在庫表 D 列の値が最低在庫 2,000 以下または 3,000 以上を示す) を入力して「書式」ボタンをクリックする

4. 「フォント」タブで色を設定し、「塗りつぶし」タブで背景色を設定して、「OK」ボタンをクリックする

5. プレビューを確認して「OK」ボタンをクリックする

6. 該当するセルが書式設定され表示される

たとえば Excel のスケジュール表において、セルの強調表示ルールを用いて期日の数日前からセルの色を変化させるようにすると、期日が迫っているものをひとめで把握することができます。

以下の「今月のスケジュール」の例で、日付が今週か来週の場合に条件付き書式を設定してみましょう。

1. 条件付き書式を設定するセルの範囲 (例では製造開始日と製造完了日の列のセル) を選択する

2. 「ホーム」タブの条件付き書式をクリックし、「セルの強調表示ルール」から「日付」をクリックする

3. 「次の期間内の日付を含むセルを書式設定」で「今週」を選択し、「OK」ボタンをクリックする

4. 1 ～ 2 を繰り返し、「次の期間内の日付を含むセルを書式設定」で「来週」を選択、書式で「濃い黄色の文字、黄色の背景」を選択し、「OK」ボタンをクリックする

5. 今週、来週の日付にそれぞれ書式設定され表示される

重複がないかどうかを確認する際は、重複チェックの条件付き書式が利用できます。

以下の例では、受注伝票で受注番号が重複している場合に条件付き書式を適用します。

1. 条件付き書式を設定するセルの範囲（例では受注番号の列）を選択する

2. 「ホーム」タブの条件付き書式をクリックし、「セルの強調表示ルール」から「重複する値」をクリックする

3. 「OK」ボタンをクリックする

4. 重複する値のセルが書式設定されて表示される

2 件までは許容するなど、複雑な重複チェックを行いたい場合は、「その他のルール」に COUNTIIF 関数を用いて件数をチェックするとよいでしょう。
COUNTIF 関数は、選択範囲の検索条件と一致するセルの個数を返す関数です。引数に検索範囲と条件を指定します。

以下は、数式に COUNTIF 関数を用いている例です。受注番号の範囲で、値が一致するセルの数が ２ より大きい場合に書式設定するように設定し、同じ値が 3 つある「12350」が表示されています。

2. 矢印ボタンをクリックする

3. テーブルに設定する範囲を選択し、矢印ボタンをクリックする

4. 「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認し、「OK」ボタンをクリックする

5. 選択範囲がテーブルに設定され、設定した書式設定で表示される

テーブルに設定すると、書式設定が簡単に行えるだけでなく、標準でフィルター ボタンが表示され、すぐにフィルター機能を利用することができます。
また、行や列を追加すると自動でテーブルの範囲が拡張されるので、フィルターの範囲や書式を再度設定する手間が省けます。