【エクセルの条件付き書式を活用しよう】数式を使用してオリジナルのルールを作る


条件付き書式アイキャッチ3

こんにちは!もり(@excelprog)です!

「エクセルの条件付き書式を活用しよう」をシリーズでお届けしています。

前回の記事では、「新しいルール」を使用したルールの設定方法と、1セルに複数ルールを設定する場合の適用順位について紹介しました。

【エクセルの条件付き書式を活用しよう】さまざまな書式ルールの設定と適用順位を理解する
エクセルの条件付き書式をマスターするシリーズ。第三回目は「新しいルール」でルール設定をする方法と、ルール適用の順位について紹介しています。

これまでの記事では、エクセルであらかじめ用意されているルールを使用して書式設定をしてきましたね。

もっとオリジナルのルールを作りたい!そんなあなたのために、今回の記事では数式を使用したオリジナルルールの設定方法を紹介していきます。

自分だけのルールを作って資料をみやすくしましょう。

さぁみていきましょう!

「数式を使用して書式設定」でルール設定をするメリット

この方法を使用するメリットは2つあります。

  1. 「条件判定をするセル」と「書式を適用するセル」を分けることができる
  2. エクセルの関数を使用して複雑な条件設定ができる

今回の記事では1番の「条件判定をするセル」と「書式を適用するセル」を分ける方法を紹介していきます。

エクセルの「ホーム」タブ→「条件付き書式」→「新しいルール」をクリックします。

conf3-1

「数式を使用して、書式設定するセルを決定」を選択します。

書式設定の欄に数式を入力することでルール設定ができます。

con4-1

それでは、具体的な使い方をみていきましょう!

「数式を使用した書式設定」の使い方

こちらのなんちゃって個人情報から出力したサンプルデータを使って解説していきます。

conf4-0

数式を利用した設定を使いこなすには、この2つを理解する必要があります。

  • 条件判定をするセル
  • 書式を適用するセル

まずは、簡単なルールから設定してみます。

「B2セルの値が”男性”の場合、B2セルに書式を付ける」

B2セルを選択して、「条件付き書式」→「新しいルール」です。

ここでは、エクセル上で選択したB2セルが「書式を適用するセル」です。

con4-2

つづいて、数式を入力します。「B2セルの値が”男性”の場合」という条件を設定します。書式は自由に設定してください(ここではセルを水色にします)

con4-3

ここで入力する数式がポイントです。

=セル番地=条件

入力欄の説明書きに「次の数式を満たす場合に値を書式設定」と書いてありますね。

「セル番地=条件」が満たされる場合(真の場合)、つまり、B2セルが”男性”である場合に書式が付きます。

数式の最初に=(イコール)を付けるのを忘れずに!
=B2=”男性”

このルール設定を表にまとめます。

条件判定をするセル 条件 書式を適用するセル
B2セル “男性”の場合 B2セルに書式を付ける

適用結果がこちらです。条件に合致して書式が付きました。

con4-4

このパターンは、「条件判定をするセル」と「書式を適用するセル」が同じなので単純ですね。

1つの列を一括で書式設定をする

B列(性別)が”男性”のセルに一括で書式を付けます。

「書式を適用するセル」のB2セル~B21セルを範囲選択します。

con4-5

ここで設定する数式がこちらです。さきほどと同じですね。

con4-6

ここで不思議なのが、「書式を適用するセル」は「B2セル~B21セル」をまとめて範囲選択しているのに、「条件を判定するセル」には「B2セルのみ」を指定していることですね。

まずは適用結果をみてみましょう。”男性”のセルに色が付いています。

con4-7

「条件判定をするセル」に、先頭行のB2セルを「相対参照」で設定しているので、最終行のB列21行目まで相対的にルールが適用される仕組みです。

条件判定をするセル 条件 書式を適用するセル
B2セル “男性”の場合 B2セルに書式を付ける
B3セル B3セルに書式を付ける
B4セル B4セルに書式を付ける
・・・ ・・・
B21セル B21セルに書式を付ける

もしも、条件判定のセルを$B$2と「絶対参照」で指定した場合、条件判定セルが全てB2セルになってしまうのです。

  • B2セルの値が”男性”の場合 → B2セル〜B21セル全てに書式が付く
  • B2セルの値が”男性”でない場合 → B2セル〜B21セル全てに書式が付かない

「条件判定するセル」と「書式を適用するセル」を分ける方法

「B列(性別)が”男性”の場合、A列(名前)を水色にする」というルール設定をします。

「書式を適用するセル」はA列(名前)なので、A2セル~A21セルを選択します。

con4-8

つづいて、条件の設定です。「条件判定をするセル」はB列(性別)なので、これまでと同じように「=B2=”男性”」と設定すればOKです。

con4-9

適用結果がこちらです。B列(性別)が”男性”の場合、A列(名前)に書式が付きました。

con4-10

ルールの仕組みはこのようになります。

条件判定をするセル 条件 書式を適用するセル
B2セル “男性”の場合 A2セルに書式を付ける
B3セル A3セルに書式を付ける
B4セル A4セルに書式を付ける
・・・ ・・・ ・・・
B21セル A21セルに書式を付ける

ここでもB2セルを「相対参照」で設定しているので、書式を適用するA列が、1つ左の列を「相対的に参照」して最終行の21行目まで適用されています。

このように、「条件判定するセル」と「書式を適用するセル」を分けることができるのが、「数式を使用したルール設定」のメリットなのです。

複数の列に書式設定をする方法

最後に面白い設定を紹介します。

「B列の値が”男性”の場合、A列~C列の行全体を水色にする」です。

まず、「書式を適用するセル」のA2セル~C21セルを選択します。

con4-11

次に、「条件判定するセル」を指定して条件を入力します。ここで入力する数式がポイントです!

con4-13

=$B2=”男性”

列番号に$をつけて列を「絶対参照」にしています。B列を絶対参照にすることで、B列の判定結果を、A列・B列・C列に適用することができます。

適用結果がこちらです。B列の値が”男性”の場合、行全体に色が付きました。

con4-12

表にまとめるとこのようになります。

条件判定をするセル 条件 書式を適用するセル
$B2セル “男性”の場合 A2セルに書式を付ける
$B2セル B2セルに書式を付ける
$B2セル C2セルに書式を付ける
・・・ ・・・
$B21セル A21セルに書式を付ける
$B21セル B21セルに書式を付ける
$B21セル C21セルに書式を付ける

もし「=B2=”男性”」と、条件判定のセルを「相対参照」にした場合、書式を適用するセルは、相対的に1つ左の列を参照してしまいます。

つまり、このようになります。これでは使えませんね。

条件判定をするセル 条件 書式を適用するセル
B2セル “男性”の場合 A2セルに書式を付ける
C2セル B2セルに書式を付ける
D2セル C2セルに書式を付ける
・・・ ・・・
B21セル A21セルに書式を付ける
C21セル B21セルに書式を付ける
D21セル C21セルに書式を付ける

まとめ

今回の記事では、数式を使用した書式設定の方法を紹介しました。

この方法を使えば、色々なアレンジを効かせることができますね。

  • 「書式を適用するセル」と「条件判定をするセル」を分けることができる
  • 複数列をまとめて書式設定ができる

条件付き書式の数式設定をするうえで、セルの「相対参照」と「絶対参照」を理解しておくことで設定のバリエーションが広がりますので、頑張って身につけましょう!

次回は、AND関数などを利用して「もしセルの状態がXXかつXXならば、書式を設定する」といった高度な設定方法を紹介していきます。

どうぞお楽しみに!

連載目次:エクセル条件付き書式を活用しよう

エクセルの条件付き書式を使いこなせると資料の作成・管理がとても便利になります。指定の数字に等しいセルに色を付ける、重複データに色を付ける、そんな作業が自動でできる優秀な機能を一から紹介しています。

  1. 【エクセル条件付き書式】その仕組みがわかる最初の一歩
  2. 【エクセル条件付き書式】書式の設定方法をマスターして基本のルールを使いこなす
  3. 【エクセル条件付き書式】さまざまな書式ルールの設定と適用順位を理解する
  4. 【エクセル条件付き書式】数式を使用してオリジナルのルールを作る
  5. 【エクセル条件付き書式】関数を組み合わせて高度なルール設定をする

コメント