スポンサーリンク
条件付き書式について
Excelの条件付き書式とは、文字通りではあるが、「なんらかの条件が満たされたとき、セルの背景色とかフォントといった書式を自動で変更する」ものだ。
これはExcelの基礎・必須テクニックであり、絶対に使えるようにならないといけない。
そしてExcelのセルの書式設定は、通常のセル背景色塗りつぶしなどでゴチャゴチャ設定するよりも、この条件付き書式を多用して、規則に沿って自動的に仕上がるようにしていくべきだ。
当ブログでもそれなりのExcel関係記事を書いてきたけど、この条件付き書式機能についてまだ書いていなかった。
他の記事の内容の一部で、条件付き書式について触れたものはあるけどね。
別に忘れていたわけではなかったけど、設定可能なメニューを全部丁寧に説明してたらとてつもない分量になるので、どう書いたものか決めかねていた。
そこで今回の条件付き書式の記事では、私自身が実際に使う機能に絞って、他の細かい説明は極力切り捨てていこうと思う。
私も、使い勝手が良いと思った一部の機能ばかり使ってワンパターンになっているし、そのレベルの説明で十分だと思う。
条件付き書式メニューで、リボンに組み込むべきもの
条件付き書式も使用頻度は高い(高いようでないといけない)機能なので、操作を素速くやれるようカスタマイズしておくべきだ。デフォルトのリボン設定からは、ボタンを何度も押さないといけなくて面倒だ。
では条件付き書式に関する操作コマンドを、クイックアクセスツールバーをカスタマイズして追加するか、それともリボンをカスタマイズして追加するか。
これは、見た目上から分かりにくいコマンドばかりなので、クイックアクセスツールバーに組み込むには適さなくて、リボンをカスタマイズして追加すべきというのが私の意見だ。
リボンのユーザー設定画面から、
「コマンドの選択」で「メインタブ」を選ぶ
→ホーム＞スタイル＞条件付き書式
とメニューを展開していき、そこから必要な操作コマンドを選んで自分オリジナルのリボンに追加していく。
必要なコマンドというのは人によって違うだろうが、
- 重複する値
- 新しいルール
- ルールの管理
の3つをひとまず追加すれば良いと思う。
私自身、これらしか追加していない。
以降の説明では、一応デフォルトのリボン設定に従って説明するが、自分でカスタマイズしたリボン設定に読み替えてもらいたい。
自セルの値に対する条件付き書式
たとえばA1セルを対象としたとき、条件付き書式は大きく分けて、
- A1セルが○○以上なら、A1セル自身の書式を変更する
- A1セルとは関係ない場所にある他セルの値など、自由な条件を数式を使用して参照し、その条件次第でA1セルの書式を変更する。あるいはA1セルの値の条件に対して、1行目全体とかA1セル以外のセルも書式変更する。
といった2つのパターンに分かれる。
実務上は後者の方が多いはずだが、まずは前者の、自セルのみ対象とした条件付き書式について書いていく。
ではまず下図のE列「年齢」の数値項目について、条件付き書式を設定してみる。
リボン「ホーム」タブ
→「条件付き書式」
→「新しいルール」
とする。
これは、先ほどオリジナルのリボンに追加した「新しいルール」のコマンドだ。
条件付き書式ルールの画面が出てくるが、ここで一番よく使うのが「指定の値を含むセルだけを書式設定」だと思う。
そして左端の選択肢ではデフォルトの「セルの値」を、その右側の選択肢では以上・以下などの中から選ぶ。
そして下図で「セルの値が60以上」と指定してみたが、そこで「書式」を押す。
この「書式」を押すのはもう、ショートカットキー
Alt + F
でやるクセを身に付けよう。
そして、「条件を満たしたときに付与される書式」を、表示形式やらフォントやら色々と指定できるけど、私は8割くらいは「塗りつぶし」でセルの塗りつぶし色を指定する。
他の2割は、文字のフォントを太文字や斜体字にしたり、文字色を変えたりとかだ。
そういった書式設定ができたら「OK」を押す。
前の設定画面に戻るので、また「OK」を押す。
すると指定した通り、60以上の数値のセルに色が自動で付けられる。
設定した条件付き書式の確認・修正
ルールの管理
では、いま設定した条件付き書式を確認してみる。
条件付き書式が設定されているであろうセルを選択して
リボン「ホーム」タブ
→「条件付き書式」
→「ルールの管理」
とする。これは、先ほどオリジナルのリボンに追加した「ルールの管理」のコマンドだ。
まず「現在の選択範囲」「このワークシート」とか、場合によっては「このテーブル」とかセル範囲を選ばせるものが最上部にある。
上図の例でいうと、選択したE列のセル以外にもシートのどこかに条件付き書式が隠れているかもしれないし、そういったことも考慮して適切なものを選ぼう。
そして「セルの値>=60」という条件とか、それに伴う書式のプレビューとか表示されている。
「適用先」が、その条件付き書式が適用されているセル範囲のアドレスだ(後述)。
複数条件付き書式の優先順位設定、停止条件設定
では先ほど「セルの値が60以上の場合」に塗りつぶしする条件付き書式を設定したセルに、「セルの値が70より小さい場合」に太字・斜体文字にするという条件付き書式を重ねて設定してみる。
こういう複数の条件付き書式については、ルール管理画面の上部にある上下ボタンで優先順位を変更できる。
それはそうと「セルの値が60以上」と「セルの値が70より小さい」というのは、たとえば値が63だったら条件が両方とも同時に満たされているわけだ。
そして、「セルの値が60以上」のときにセルの背景色を塗りつぶすのと、「セルの値が70より小さい」ときに太字・斜体文字にするのとは、競合し合わない書式設定だ。
だから下図のように、値が65とか63とか両方の条件を満たすセルであれば、両方の書式設定が適用されることになる。
では両方の条件を満たしたときも、「値が60以上であるという条件を満たしたから、セル背景色を塗りつぶすだけにして、太字・斜体文字にはしたくない」という場合は「条件を満たす場合は停止」にチェックを入れれば良い。
そうすれば、それ以降の優先順位の書式設定については判定を停止してくれる。
(下図の画像で、値が65や63のセルについて、太字・斜体文字の書式設定が消えている)
その他
このルールの管理画面で上部にある「ルールの編集」ボタンで、条件付き書式で設定する条件や書式を編集できる。
「ルールの削除」で、文字通り削除できる。
「重複ルール」は、条件付き書式のコピー(複製)ができる。
重複データに対する条件付き書式
条件付き書式では、上位10%とか平均より上とか、様々なデータに書式設定できて、それらを説明してたら切りがないけど、特によく使われるのは重複データに対する書式設定ではないかと思う。
だから私も、重複データへの条件付き書式設定だけは、オリジナルのリボンにコマンドを追加している。
下図で社員一覧に、重複して社員データを入力していないかチェックするため、A列のデータに対して重複データの強調表示を設定したい。
そこで
リボン「ホーム」タブ
→「条件付き書式」
→「セルの強調表示ルール」
→「重複する値」
とする。
丁寧にプレビュー付きで書式設定の選択肢が出るので、好きなものを選ぼう。
条件付き書式の適用されるセル範囲は、崩れるもの
先ほど、ルールの管理画面で「適用先」に、条件付き書式が適用されているセル範囲のアドレスが書かれていることを述べた。
しかしこの「適用先」は、行のコピー・挿入などしていくと、いつの間にか増殖していく。
たとえば上図で適用先$E$2:$E$7セルとなっていたシートで行のコピー・挿入をすると、下図のようになったりする。
下図で適用先に書かれている「$E$5」と「=$E$2:$E$4,$E$6:$E$8」というのは、実は総合すれば「=$E$2:$E$8」の1個で済むことなのだが、それをExcelは適切に判断してくれない。
この条件付き書式の「適用先」が増殖していく現象は、ちょっとお手軽で有効な対策は存在しない、はずだ。
いや、どこのセルにどういう条件付き書式を設定するというルールがきっちり固まりきっていて動かないのであれば、増殖を制御するマクロを作ることは可能だ。
しかし、そのルールが色々と変動し得る場面でも汎用的に設定できる対策は、残念ながら無い。有ったとしても、かなり複雑なマクロが必要になるはずだ。
だからこのルール画面は小まめに見直し、「適用先」セル範囲がおかしくなっていたら書き換えてメンテしていこう。
条件付き書式を組み込んだExcelファイルを積極運用していけば、絶対この増殖現象は起きていくし、それを恐れるべきでもない。
スポンサーリンク
