スポンサーリンク
数式を使用した条件付き書式
Excelの条件付き書式に関し、今回は数式を使用して幅広い設定をしていく方法を取り上げる。
この方法は、絶対参照・相対参照をマスターしていないとまるで歯が立たないので、必ずそれらから押さえておこう。
下図の表で、「性別」列が「男」である場合に、その行(A列～C列)すべてセルの背景色を変化させたいとする。
この場合は前回のように、自セルのみに条件付き書式を設定しても駄目で、「性別」列の条件に対して「性別」列以外のセルにも書式が設定されないといけない。
では、対象となるA2セル～C列の最下端セルまでを選択し(選択の起点が、最上部のA2セルであるのを確認しておく)、
リボン「ホーム」タブ
→「条件付き書式」
→「新しいルール」
として条件付き書式の設定画面を開く。
数式の絶対参照・相対参照を調整
次に「数式を使用して、書式設定するセルを決定」を選ぶ。
「次の数式を満たす場合に値を書式設定」欄にカーソルを合わせて、そのまま性別条件であるB2セルをクリック。
すると最初は
=$B$2
という完全な絶対参照の式になるはずだ。
でもこの条件付き書式は、性別条件であるB列は固定で、参照を下にスライドさせていけば良いタイプなので、
=$B2
と、「B」の前にのみ「$」マークを付ける参照にする。この「$」マークの付け方を変えるのはF4キーでできるので、必ず押さえておく。
続けて性別が男であるという条件を完成させるため、追加で書き込んで
=$B2=“男”
という式にする。
「=」が2回出現する数式
= マークが2回出現するのが不気味な感じで、この違和感に慣れにくいかも知れない。
要はこれは、以前の記事で少し取り上げた、式の真偽(TRUE / FALSE)を判定するものだ。
上記の
=$B2=“男”
という式なら、「$B2=“男”」という式が真なのか偽なのかを問うということ。
条件付き書式を使うなら必ずこのような表記に慣れておかないといけないし、逆に条件付き書式の他には、こういう式をどうしても扱わないといけない場面はほぼ無い。
では、
=$B2=“男”
の式を入れた後は「書式」ボタンを押せば良い。
この「書式」は、なるべくショートカットキー Alt + F で押すようにしよう。
条件を満たしたときに付与される書式の設定画面が出るので、設定できたら「OK」を押す。
前の設定画面に戻るので、また「OK」を押す。
これで「性別」列が「男」である場合に、A列～C列のセルが全て、書式が変化した。
関数も使ってみる
この、数式も使用した条件付き書式は、色々と関数も使って複雑に設定できる。
その中で特に使う機会が多いのは、COUNTIFS関数を使ったものではないかと思うので、それを例に説明してみる。
たとえば下図のA～C列の社員一覧で、F～G列の退職者一覧の中に含まれる社員を、「コード」をキーにして調べ強調表示したいとする。
この場合、A列の「コード」がF列の「コード」の中に1個でも含まれているか、COUNTIFS関数で調べるのが有効だろう。
その場合まず、対象となるA3セル～C列の最下端セルまでを選択し(選択の起点が、最上部のA3セルであるのを確認しておく)、
リボン「ホーム」タブ
→「条件付き書式」
→「新しいルール」
として条件付き書式の設定画面を開く。
そして「数式を使用して、書式設定するセルを決定」を選ぶ。
予行練習してから関数を入れる
ここからCOUNTIFS関数など関数を入れていかないといけないが、普通にセルに入力する時のように関数のナビゲーションが出てくれないので、この画面では関数を組み立てていくのはかなり難しいだろう。
だから予め、普通のセルにおける入力画面で、関数の入力を予行練習して、その入力した数式をテキストデータとしてコピーしておくと良いだろう。
もちろんこの予行練習でも、絶対参照・相対参照は正確にしておくことだ。
今回の場合なら、A3セルを起点にした場合
=COUNTIFS($F$3:$F$7 , $A3)
という感じのCOUNTIFS関数を組み入れれば良い。
検索条件範囲となるF列側については完全な絶対参照にし、社員一覧のA列側は縦方向にだけスライドしていけるような相対参照にするということだ。
予行練習の通り、数式
=COUNTIFS($F$3:$F$7 , $A3)
を、条件付き書式ルールの数式欄に入力し、「書式」を押す。
塗りつぶしなどの書式を設定して「OK」を押す。
前の画面に戻るので、また「OK」。
これで、退職者一覧の中にいる社員のデータに塗りつぶしが設定された。
条件付き書式は今回の例のように、1つの条件が満たされた時に行全体の色を変えたりと、数式を使用して設定することが多い。
必ず使いこなせるようになっておこう。
スポンサーリンク
