Excel。条件付き書式を行に設定するのには
複合参照が必須です。
条件付き書式と複合参照
前回ご紹介したABC分析（パレート図）の先の作業にあたるのですが、Excelのスキルの中で、簡単そうなんだけど、ちょっと面倒くさいというスキルに、
条件付き書式を行に設定する
というのがあります。どういうことかというと、下記の表で、E列のランクがAだったら、その行、つまり、
そのレコードに対して条件式書式を設定していきたいわけです。
E2がAなので、そのE2だけに条件付き書式を付けるのは、簡単ですが、
今回は、E2がAだったら、A2:D2にも条件付き書式がアクションしてくれるようにしたい訳です。
こんな感じになるという事ですね。
実は、これがなかなか大変なんですね。
条件付き書式のテクニックだけではダメで、複合参照のスキルも必要になってくるんですね。
今回ご紹介するテクニックは、企業研修でも質問が出ますし、
仕事でつかえるExcel講座でも、ご紹介しております。
ただ、初心者さんの講座でもご質問をいただきますが、
絶対参照と条件付き書式の基本がわかっていないと判断するときには、
即答を避けて、しっかりスキルが定着してからご紹介するようにしております。
パターンとして覚えていても、Excel力がアップしなければ結局、現場では使えませんのでね。
では、早速紹介していきます。
まずは、A2:D20を範囲選択しましょう。ホームタブの条件付き書式をクリックして、
新しいルールをクリックしましょう。
そうすると、新しい書式ルールダイアログボックスが表示されてきます。
この中の一番下にある【数式を使用して、書式設定するセルを決定】をクリックします。
この【数式を使用して、書式設定するセルを決定】を使うことによって、
行全部を該当する条件で書式を反映することが出来るようになりますし、
あと、【数式を使用して、書式設定するセルを決定】を使うことによって、
高度な条件付き書式を設定することも可能になります。
この
【数式を使用して、書式設定するセルを決定】
を使いこなせると、いいですよね。本来なら、書式ボタンは、あとで設定するのですが、
今回は、数式の作り方が大切なので、この書式ボタンから書式を設定する方法のご紹介を
先にしちゃいましょう。
書式ボタンをクリックします。
今回は塗りつぶしを設定しますので、
塗りつぶしタブをクリックしてお好みの色を選択してOKボタンをクリックすると、
先程の新しい書式ルールダイアログボックスへ戻ります。
さて、次の数式を満たす場合に値を書式設定のボックスに数式を作っていきましょう。
数式を作っていくわけですが、ポイントとなる点があります。
それは、通常関数などの数式を作ったあとに、オートフィルなどで数式をコピーするわけですよね。ところが、条件付き書式は、先に、範囲選択をして数式を作っていくことになりますので、
それぞれの行が事前に条件を満たすように考える必要があるということです。
つまり、オートフィルするイメージで数式を作る必要があるわけです。
例えば、E2がAだったら、塗りつぶしをするというルールを作っていくわけですね。
そこで、E2=”A”という式を入れてみると、どうなるでしょうか？やってみましょう。
まず、E2をクリックします。そして、=”A”と入力しましょう。Aは文字なので、””で囲いましょう。
数式は、=$E$2=”A”となりますね。
OKボタンをクリックすると、
ありゃま、全部赤！お気づきの方もいると思いますが、先程の数式。
=$E$2=”A”。E2が絶対参照になっているので、
範囲のセル全部がE2がAだったらという条件が成立してしまったので、
書式が反映されたわけですね。けど、これじゃ、全くダメなわけですね。
それでは、元に戻して、考えてみましょう。
それぞれの行が、E列を参照してくれればいいわけですよね。
E列がAですか？というようにしたい訳ですね。
ということは、E列が止まっていて欲しいわけですね。
で、行番号はオートフィルした時に変わるイメージなわけです。
そこで、絶対参照ではなく、複合参照を使うと、希望する数式が作れるわけですね。
=$E2=”A”
$マークがついているほうが、参照が固定されるわけです。
それでは、OKボタンをクリックしてみましょう。
ランクがAの行だけに塗りつぶしの書式が反映されてましたね。
同じ方法で、数式を、それぞれ、
=$E2=”B”
と
=$E2=”C”
として、条件付き書式を設定すると、
ABCで色分けできましたね。
