Excelにある条件付き書式の設定方法

Excelの条件付き書式はよく使われる設定であり、Excelをより便利になりますが、多くの人が苦手です。

次の記事を読んで、Excelの条件付き書式を完全に理解できます。

条件付き書式は、条件を満たすセルを事前に設定された書式で表示します。条件に基づいてデータバー、カラースケール、アイコンセットを使用して、関連するセルを強調で表示し、異常値を強調し、データの視覚化を実現します。

Excel条件付き書式のメニューでは、完全な条件付き書式はルールと呼ばれます。（つまり、条件+書式=ルール）Excel条件付き書式には、5種類のルールがプリセットされています。このチュートリアルでは主にルールのカスタムを解説していきます。つまり、「新しいルール」によって作成されたルールです。

「新しいルール」をクリックすると、スクリーンショットのように6種類のルールを作成できます。

例を挙げます。

新しいルール１：売上高が5000未満のセルを赤と太字のフォントに設定します。

新しいルール2、条件は売上高は90未満で、フォントは緑色の太字です。

これらの6つのルールは、3種類に分けられます。

セルの値を視覚化にする（各セルの値に基づいてセルの書式を設定する）
全体に対する割合や状態（つまり、ルールの種類の2番目から5番目までのオプション）
計算式の結果と同じ（つまり、数式を使用して、書式設定するセルを決定）

セルの値を視覚化にする

セルの値を視覚化にするとは数値をチャートで表示することです。ここでの視覚化はただセルの書式を指します。数値の違いが書式の違いから反映され、数値をチャートに変換します。

ルールの種類から一番目の[セルの値に基づいてすべてのセルを書式設定]を選択すると、下の書式設定に4種類の書式スタイルがあります。

「3色スケール」でデータの分布を表示する

「2色スケール」と「3色スケール」はどちらも色スケールと呼ばれます。つまり、異なる数値は異なる色で表されます。使い方はほぼ同じで、「3色スケール」で各商品の販売数量を書式付けて表示します。

次には各商品の販売数量であり、色スケールによってデータの分布を表示します。

B3:J7を選択して、書式スタイルを[3色スケール]を選択します。ルールの説明には3つの色があり、その中で赤、黄、緑が最小値、中間値、最大値を表します。

（ヒント:最小値、中間値、最大値の種類は変更できます。デフォルトでは、最小値は値の中で最も低い値であり、中間値は50％です）

設定が完了すると、販売数量表が塗りつぶされ、色から販売数量の分布がわかります。

「データバー」を使用して値の大小を表示する

データバールールは、データを視覚化できます。つまり、データバーは、値の大小またはデータの進行状況を表示します。（最大値をターゲット値として設定します）。

下記の図のように、書式スタイルは[データバー]を選択します。デフォルトでは、「最小値」と「最大値」の種類は両方とも自動的です。このとき、最大値であるセルはセルを満たします。

この例では、「最大値」の種類を数値に変更し、値を3000に設定します。このとき、3000より大きい数値に対応するデータバーが満たします。他の値は比率に従ってデータバーを充填します。

設定が完了すると、販売数量表が棒グラフのように、数値の大小や比率が一目でわかります。

ヒント：データバーの[最大値]の[種類]はどのように選択しますか？

最大値は、データバーが満たす状態に対応する数値です。最大値は、最大値、数値、パーセント、数式、百分位、自動、6つの種類に設定できます。

常用の3種類は次のとおりです。

最大値	数値	数式
範囲内の最大値	ユーザーが自分で設定する数値	数式で定義する

データバーのもう1つの定番の使い方は、正と負の値を強調表示することです。データの趨勢を表すときに非常に明確です。たとえば、商品の販売数量表で、[前年比成長率]列にデータバーを設定すると、マイナス成長のデータバーが赤で表示されます。

負の値と軸の設定については、[ルールの内容を編集する]インターフェースで[負の値と軸]をクリックして設定できます。

ポップアップされた[負の値と軸の設定]ウィンドウで、負の棒グラフの塗りつぶしの色と軸の位置を設定できます。

「アイコンセット」でデータの特性を表示する

学生の成績の等級を判定する場合にIF関数やVLOOKUP関数はよく使用されます。もう一つのやり方は[アイコンセット]を使用します。

たとえば、点数が85以上は青い旗で表示し、75以上が黄色の旗で表示し、75未満が赤い旗で表示します。

Step1

[アイコンセット]を選択し、適切な[アイコンスタイル]を選択します。

Step2

ルールを設定します。

アイコンセットを使用する場合、アイコンは5つまで設定することに注意してください。ほとんどの場合には十分です。

全体に対する割合や状態

この記事の最初に説明したように、新しいルールの2番目から5番目は、数値の表示を強調する種類に属します。いくつかがありますが、作用は同じです。条件を満たす数値(気になるデータ)を設定した書式に変更して、強調の目的を達成します。

これらの条件は次のとおりです。

特定の値より大きい（または小さい）
特定のテキストが含まれる
上位または下位の数値
平均値より大きいまたは小さい
一意の値または重複する値

Excelのフィルタリング機能に精通している場合は、これらの条件がフィルタリングの条件とほぼ同じであることがわかります。

(よく考えると、特別の数値の表示を強調することもフィルタリングの1つ形式です。)

不合格の成績を強調する

条件付け範囲を選択し、[ルールの種類]から[指定の値を含むセルだけを書式設定]をクリックし、セルの値は60より小さいに設定します。

書式をクリックします。

書式を太字と赤に設定します。

点数が60未満の場合、設定した書式が自動的に表示されます。

上位と下位のトップ3を強調する

上記の図ではトップ３の設定方法だけを示しましたが、ワースト３の設定方法も同じです。

一意の値または重複する値を強調する

たとえば、学生の名前が重複で入力されているかどうかを確認し、重複入力の場合があれば、マークします。

数式を使用して書式を設定する

条件付き書式の中で数式を使用して書式を設定するのは一番難しいです。

難しさの1つは、必要に応じて有効な数式を書くことです。

もう1つは、条件付き書式の順序と範囲を設定することです。

[数式を使用して、書式設定するセルを決定]というルールでは、[条件]とは数式の計算結果を指し、条件が[Ture]の場合に設定した書式が有効になります。数式の結果は論理値である必要があります。（数式は必ず[Ture]または[False]を返します）

設定方法は非常に簡単です。次の図のように数式を入力して書式を設定すればいいです。

各科目の最低成績を強調する

Step1

B2:D11セル範囲を選択し、新しいルールを作成します。

Step2

数式を記述し、書式を設定します。

=C2=MIN(C$2:C$11)

ヒント

1、MIN(B$2:B$11)部分は、数式が配置されている列の最小値を計算します。ここでのセルはB$2:B$11で、列を相対参照、行を絶対参照することに注意してください。3つの列があるため、C$2:C$11とD$2:D$11を自動的に拡張する必要があります。

2、また、セルB2が数式が配置されている列の最小値に等しいかどうかを判断します。

3、条件付き書式が選択した範囲の各セルに適用されます。

複数の条件を設定する

次の図に国語と英語の列から両方とも90より大きいセルをマークします。

数式：

=AND($B2>90,$C2>90)

（AND($B2>90,$C2>90)は両方とも条件を満たすことを意味します。）

国語と英語、2つの列をマークする必要があるため、条件付き書式のセル範囲は$B$2:$C$11です。

複数の条件と複数のルール設定

条件とルールが複数の場合はより複雑です。次の例をご覧ください。

1つの科目が不合格の場合、行全体の背景に黄色をつけます。

2つの科目が不合格の場合、行全体の背景に赤色をつけます。

Step1

[1つの科目が不合格の場合、行全体の背景に黄色をつける]というルールを設定します。

数式は

=OR($B2<60,$C2<60)

で、塗りつぶしを[黄色]に設定します。

説明

OR関数は[或は]を意味します。その引数の1つがTRUEである限り、関数の返す値はTRUEであるため、=OR($C2<60,$D2<60)は、2つの科目の中で、 1つが不合格の場合、数式の結果はTRUEです。

行全体の背景に色を付けるため、[条件付き書式]を行領域全体に設定する必要があります。条件付き書式を設定する前に$A$2:$C$11領域を選択してもいいし、ルールの管理の[適用先]に領域を変更してもいいです。

[OK]をクリックすると、データ領域で1つの科目が不合格に限り、背景色が黄色になります。もちろん、例の9行目のように、科目がすべて不合格の場合にも含まれます。これより、2つの科目とも不合格の場合に背景色が赤色に設定する必要があります。

Step2

[2つの科目が不合格の場合、行全体の背景に赤色をつける]というルールを設定します。数式

=AND($B2<60,$C2<60)

を記述し、塗りつぶしの色を赤に設定します。

また、ルールの管理ウィンドウで、例のように、新しく設定した[条件付き書式]をデータ領域全体に適用します。ルールの管理ウィンドウでは、新しく作成されたルールが自動的にトップに配置されます。この並べ替え順序は、[条件付き書式]の[優先順位]です。2つの条件に当てる場合、最初の[条件付き書式]を実行します。下の図の右上隅にある三角形の記号で調整できます。