今回は条件付き書式について説明したいと思います。
データの大小が一目でわかったり、条件に合ったものの
文字の色やセルの色を変更できたりします。
データの見えやすい表示や対象の洗い出し等とても便利な機能になってます。
ぜひ参考にしてみてください。
それでは見ていきましょう。
■条件付き書式の設定方法
各条件の設定については後程説明しますので、「条件付き書式」がどこで設定できるか
説明したいと思います。
まず、条件を設定したい範囲を選択します。
そして「ホーム」の「条件付き書式」を押してみてください
ここで様々なメニューが出てきます。
「データバー」や「カラースケール」などすぐに設定できますが
今回は1から説明するため「新しいルール」を押してみてください。
下図のような画面が出てきました。こちらで詳細の設定ができます。
「セルの値に基づいてすべてのセルの書式設定」「 指定の値を含むセルだけを書式設定 」
など様々なルールがあるので１つずつ見ていきましょう。
■セルの値に基づいてすべてのセルの書式設定
ここでは書式スタイルを
「２色スケール」「３色スケール」「データバー」「アイコンセット」が選択できます。
それでは見ていきましょう。
・２色スケール
こちらは書式を設定したデータの値に対しての色の表示を変更できます。
下図の体温表を参考にしてみてみましょう。
データをまとめたはいいものの
詳しく数値を確認しないと体温の大小は確認できません。
そこで「２色スケール」を表示してみましょう。
条件を設定する「Ｃ3：Ｃ12」を選択して「条件付き書式」を選択してください。
書式スタイルを「２色スケール」で選択し
種類を「最小値」と「最大値」に設定しました。
さらに最小値の色を「青」、最大値の色を「赤」に選択し、体温が高いかどうか判断してみます。
表示結果はこのようになります。
体温が低ければ青、高ければ赤になっており、わかりやすくなりました。
今回は種類を最小値と最大値に設定しました。
なので一番低い「35.9」が一番青く、一番高い「38.5」が一番赤くなります。
最大値・最小値は
「最小値・最大値」「数値」「パーセント」「数式」「百分位」から選択できます。
仮に最小値を数値の「1」、最大値の数値の「100」に選択した場合
「1」が一番青く、「100」が一番赤くなります。
表示したい尺度によって変更してみてください。
・3色スケール
今度は「3色スケール」を見てみましょう。
先ほどの体温表を参考にしてみましょう。
先ほどと同様に条件を設定する「Ｃ3：Ｃ12」を選択して
「条件付き書式」を選択してください。
書式スタイルを「２色スケール」で選択し
種類の「最小値を青」・「最大値を赤」に設定しました。
そして中間値を「36.5」に設定し、色を「黄色」に設定してみます。
実行した結果がこちら。
先ほどより、わかりやすくなったのではないでしょうか。
こちらも種類を「最小値・最大値」「数値」「パーセント」「数式」「百分位」から選択できますが
中間値は「最小値・最大値」の概念がない為、選択できません。
・データバー
今度はデータバーについて説明します。
先ほどの体温表をもとに設定してみましょう。
条件を設定する「Ｃ3：Ｃ12」を選択して「条件付き書式」を選択してください。
書式スタイルを「データバー」で選択してください。
今回は最小値を「数値の36.0」・最大値を「自動」にしてみます。
自動で設定すると一番低い「35.9」が最小値、一番高い「38.5」が最大値になります。
こちらで実行すると…
棒グラフのような表示が現れました。こちらが「データバー」になっています。
さらに体温によってバーの長さが変わっており、見えやすくなっていますね。
最大値を「自動」にしていますので一番大きな「38.5」のデータバーがMAXになっています。
さらに「棒のみ表示」にチェックを入れると数値の表示は消えて
データバーのみの表示となります。
データバーに関しても「色」を変更できたり、データバーの「枠線」も追加できたりします。
さらに棒の方向も「右から左」「左から右」に変えることができます。
上記の見本は「棒のみ表示」にチェックを入れて軸の方向を「右から左」にしています。
・アイコンセット
こちらはアイコンと呼ばれる表示をすることで
判別しやすいようにするものです。
身近なもので表すと、信号機のようなものです。
またまた、体温表で見てみましょう。
今回は37.0以上を微熱として黄色信号、38.0は赤信号で表示してみます。
条件を設定する「Ｃ3：Ｃ12」を選択して「条件付き書式」を選択してください。
書式スタイルを「アイコンセット」で選択してください。
設定は下図に示します。
赤アイコンは値を「38以上の数値」にして、黄アイコンは値を「37以上の数値」にしています。
「37より下の値」を緑アイコンにセットしました。
実行した結果はこちら
データの判別がとてもわかりやすくなりました。
さらにアイコンもざまざまなものがあります。
好みのアイコンに合わせて使用してみてください。
■指定の値を含むセルだけを書式設定
次は「指定の値を含むセルだけを書式設定」について説明します。
こちらは「セルの値」「特定の文字列」「日付」「空白」「空白なし」
「エラー」「エラーなし」
の中から選択でき、条件に当てはまる書式を変えることができます.
・セルの値
では体温表でセルの値での書式設定を説明します。
こちらはセルの値を設定して当てはまる書式を変更できます。
今回は「次の値以上」を使用して「38.0」以上の書式を変更してみましょう。
さらに書式は「セルの塗りつぶしを赤」「文字の色を白」に変えてみましょう。
他にもセルの「罫線」や「フォント」等も変えることができます。
実行した結果はこちらです。
「38.0」以上のデータは「セルの塗りつぶしを赤」「文字の色を白」に
書式を変えることができました。
・次の値に等しい・等しくない
こちらは特定の数値を入力・参照して
等しいを選んだ場合は特定の数値と等しい場合、
等しくないを選んだ場合は特定の数値以外の書式設定を設定できます。
絞り込む数値が固定されている場合はとても便利ですね。
参照データも使用できるので活用してみてください。
・次の値より大きい・小さい
こちらは指定した数値を基準に
大きいを選んだ場合は指定数値より大きい場合、
小さいを選んだ場合は特定数値より小さい場合の書式を変えることができます。
例えば値を「50」にして、「次の値より大きい」で設定すると
「50」より大きい書式を変えれます。
・次の値以上・以下
こちらは１つ前で説明した「次の値より大きい・小さい」とほとんど変わりません。
先ほどの値を「50」で登録した際の例で紹介します。
次の値より大きい：「50」より上で50は対象外
次の値以上：「50」以上で50は対象となる
以下も同様で設定した値を含めて設定できます。
■上位または下位に入る値だけを書式設定
では次の説明に移ります。
こちらでは上位○○位・下位○○位の書式を変更することができます。
下図の点数表をもとに説明したいと思います。
では上位5位の書式を変えてみましょう。
条件を設定する「Ｃ3：Ｃ13」を選択して「条件付き書式」を選択してください。
そこから「上位または下位に入る値だけを書式設定」で選択してください。
「上位」を選択し「5」を入力してみます。
これで「上位５位」の書式を変えることができます。
書式はセルを黄色に塗りつぶしてみたいと思います。
設定した結果はこのようになります。
上位５位の得点を黄色に変えることができました。
さらに数値を打ち込む隣に「%(選択範囲に占める割合)」のチェック項目があります。
こちらにチェックを入れると上位○位から「上位○％」に変更することができます。
今現在「5」が入力されているので「上位5％」が黄色で塗りつぶされるようになります。
■平均より上または下の値だけを書式設定
今度は「平均より上または下の値だけを書式設定」について説明します。
先ほどの「上位または下位に入る値だけを書式設定」に似ていますね。
今回も下図の点数表をもとに説明します。
では平均値より下の数値のセルを水色に塗りつぶしてみましょう。
選択範囲の平均値を「より下」で選択します。
さらに塗りつぶしを「水色」にしました。
この条件で「平均値より下のセルを水色」に書式設定できます。
結果はこのようになります。
平均点以下が水色で塗りつぶされました。
今回は「より下」で説明しましたが
「以上・以下」や「標準偏差」を利用した条件も設定可能です。
■一意の値または重複する値だけを書式設定
こちらは「重複」「一意」の設定が可能です。
意味合いは下記をご覧ください。
重複：データが重複・被っているものの書式を変更
一意：データが重複していない・被っていない書式を変更
では下記の好きな食べ物アンケートをもとに説明します。
ここで重複があるかないか確認してみましょう。
条件を設定する「B3：B7」を選択して「条件付き書式」を選択してください。
そこから「重複」を選択してください。
さらに書式を「黄色に塗りつぶし」するように設定しています。
結果はこのようになります。
重複している「みかん」が黄色く塗りつぶされていますね。
対して「一意」を選択していると「みかん以外」が黄色く塗りつぶされるようになります。
■数式を使用して、書式設定するセルを決定
こちらでは自ら数式を入力して書式を設定出来ます。
この項目の強みとしては「絶対参照」が使えることですね。
下図の表をご覧ください。
果物とその値段が表に入力されています。
さらに対象に果物の名前を入力したら「果物」と「値段」が塗りつぶしてみましょう。
条件を設定する「B5：Ｃ9」を選択して「条件付き書式」を選択してください。
照合の項目は果物のB行のみにしたいので、対象範囲の先頭の「$B5」
対象は「Ｃ2」ですが参照は変わってほしくないので「$C$2」となります。
合わせると「=$B5=$C$2」となります。
書式は「黄色に塗りつぶし」で設定しました
実行した結果はこちら。
対象にメロンを打ち込むと対象の果物名と値段が黄色く塗りつぶされるようになりました。
さらに対象を打ち換えても追従して塗りつぶしてくれていますね。
■ルールの管理について
・新しいルールの追加
ルールは１つだけでなく、同じ適用先でも複数の条件付き書式を追加できます。
「条件付き書式」から「新しいルール」を選択してもいいですし、
「ルールの管理」から「新しいルール」を選択しても構いません。
・ルールの編集
設定したルールは変更が可能です。
「ルールの管理」で編集したいルールを選択し、「ルールの編集」を押せば
再度、ルールを編集できます。
作成したルールをダブルクリックしても編集可能です。
・ルールの削除
作成したルールを削除したければ
「ルールの管理」で削除したいルールを選択し、「ルールの削除」を押せば
選択したルールを削除できます。
さらに「条件付き書式」から「ルールのクリア」で
「選択したセルからルールのクリア」と「シート全体からルールをクリア」を
することができます。
・条件付き書式の優先順位を変更する
条件付き書式にも優先順位があります。
下図をご覧ください。
同じ範囲に同じ条件を設定しました。
条件は「Ｂ2」が「1」だったときに１つ目の条件は「ピンク色へ塗りつぶし」する条件。
２つ目の条件は「黄緑色へ塗りつぶし」する条件です。
一番上の条件が優先される仕組みになっており、
現在は「ピンクの塗りつぶし」が優先されているため、ピンク色で表示されています。
では優先順位を変更してみましょう。
「ピンクへ塗りつぶし」の条件を選択して「▼ボタン」を押してみてください。
「ピンクへ塗りつぶし」が下にずれて「黄緑色へ塗りつぶし」が一番上にきました。
表示もこのようになります。
表示が変わりましたね。
■公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
条件付き書式を使用して情報を強調表示する – Excel (microsoft.com)
■まとめ
いかがだったでしょうか。
条件付き書式によってデータがわかりやすく表示できるようになったり
対象データを塗りつぶして抽出しやすくしたりできます。
EXACT関数にはとても相性がいいですね。
EXACT関数についてはこちら☟
【EXACT関数】エクセルで同じ・等しいデータを判別・判断する ► 独学エクセル塾 (dokugakuexcel.com)
ぜひ身に着けてみてください。
それでは次回の記事でお会いしましょう。