【エクセルの条件付き書式を活用しよう】関数を組み合わせて高度なルール設定をする


条件付き書式5

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

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

前回の記事では、数式を使用したオリジナルルールの設定方法を紹介しました。

【エクセルの条件付き書式を活用しよう】数式を使用してオリジナルのルールを作る
エクセルの条件付き書式をマスターするシリーズ。第四回目は「数式を使用した書式設定」です。条件判定するセルと書式設定するセルを分ける方法、複数列に一括で書式設定をする方法を紹介しています。

「数式を使用して書式設定」でルール設定をするメリットが2つあるとお伝えしました。

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

前回は1の「条件判定をするセル」と「書式を適用するセル」を分ける方法を紹介しました。

今回の記事では、2の「エクセルの関数を使用した高度な条件設定」の方法を紹介します。
関数を組み合わせて、オリジナルのルール作りをしていきましょう!

数式を入力するときのポイント(カーソルの移動)

この記事では、数式の入力フィールドに長い関数式を入力していきます。

すでに使用したことのある方はお気づきと思いますが、入力欄でカーソル移動をしようと矢印キー(←・→)を押すと、意図しないセル番地が設定されてしまうのです。

conf5-11

これを回避する方法があります。

エクセルで、セルの入力値を「編集モード」にするショートカットキーはF2ですね。

ここでも同じく、数式の入力フィールドでF2キーを押すと「編集モード」になり、矢印キー(←・→)でカーソル移動ができるようになりますよ。

ぜひ試してみてください。さぁそれでは条件設定をしていきましょう!

関数を組み合わせて条件設定をする

このような「エクセル家計簿」を使用して紹介していきます。

conf5-1

AND関数を使用したルール設定

1,000円以上の日用品を購入した場合に「備考」を忘れずに入力するよう、条件付き書式でセルに色を付けてみます。(高額な買い物をしたらメモしておきたいですよね。)

今回も「条件判定をするセル」と「書式を適用するセル」の考え方がとても重要になりますよ。

まず、「書式を適用するセル」はD列ですね。D列全体を選択します。

conf5-2

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

数式の入力フィールドに条件を設定します。

conf5-3

ここでエクセルのAND関数を使用しています。
AND関数は、引数に指定した全ての条件がTRUEの場合、TRUEを返します。

AND(条件1,条件2,条件3,・・・)

「条件判定をするセル」は、B列(費目)とC列(金額)なので、2つの条件を指定します。

  • 条件1:B列(費目)が「日用品」
  • 条件2:C列(金額)が1,000円以上

条件1と条件2の両方がTRUEの場合、つまり、日用品&1000円以上の場合に書式が付きます。
書式の設定方法はこれまでと同じです。ここでは警告の赤色にしてみます。

日用品&1,000円以上の行の「備考」が赤色になりました!これで入力忘れの心配がありませんね。

conf5-4

でも、この条件設定では「備考」を入力してもセルが赤色のままです。
入力忘れを防ぐことが目的なので、入力したら色を消したいですね。

conf5-5

AND関数の引数に「備考のセルが未入力の場合」という条件を加えます。

  • 条件1:B列(費目)が「日用品」
  • 条件2:C列(金額)が1,000円以上
  • 条件3:D列(備考)が未入力(空白)

conf5-6

セルの空白判定は、ダブルクォートを2つ並べます。

セル番地=””

これで、「備考」を入力したら色が消えるようになりました。

conf5-7

AND関数とOR関数を組み合わせたルール設定

つづいて、OR関数を使用したパターンを紹介します。

費目が「医療費」か「その他」の場合、金額に関わらず「備考」を入力させるよう、ルールを設定します。

その条件式がこちらです。

conf5-8

OR関数は、引数に指定したいずれか1つ以上の条件がTRUEの場合、TRUEを返します。

OR(条件1,条件2,条件3,・・・)

まずOR関数を使用して、費目が「医療費」または「その他」であるか判定します。

  • OR条件1:B列(費目)が「医療費」
  • OR条件2:B列(費目)が「その他」

つぎにAND関数を使用して、「指定の費目」かつ「備考が未入力」であるかを判定します。

  • AND条件1:OR関数の結果がTRUE(=費目が「医療費」または「その他」)
  • AND条件2:D列(備考)が未入力(=空白)

conf5-12

指定費目の「備考」に色が付きました。

conf5-9

「備考」を入力すれば色が消えます。

conf5-10

エクセルの動作をご覧ください

それでは、ここまでのルール設定をしたエクセルの動きをご覧ください!
この2つがよーくわかりますよ!

  • 指定の条件に合致した場合、セルが赤色になる
  • 指定の条件に合致しなくなった場合、セルの色が消える
Excel条件付き書式

まとめ

今回の記事では、エクセルの関数を使用したルール設定の方法を紹介しました。

もうここまでマスターできれば、条件付き書式はバッチリです。データ分析の資料を見やすくしたり、入力忘れを防ぐ設定をしたり、ちょっとした気配りの設定ができますね。

条件付き書式の使い方はわかったけど、実務での使いどころがいまいちわからない…

そんな方のために、次回、私が実務で条件付き書式を使用した実例を紹介していきますよ。

「なるほど、こんなところで使えるのか!」と共感してもらえるようなネタをお届けします!

どうぞお楽しみに!

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

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

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

  投稿者プロフィール

もり
もりシステムエンジニア・Webライター
「ラクするために全力を尽くす」をモットーに日々勉強。退屈なことはプログラミングで片づけよう。
事務作業をとことんラクにできるITネタを発信していきます。

お気軽にフォローしてくださいね!

コメント