エクセルの条件付き書式の基本と複数条件の書き方【データ分析エクセルスキル】
条件付き書式の基本的なやり方から数式による条件付け、複数条件の書き方までを説明しています。次に何をしたら良いかのアクションを見つけ易くする状態を「アクショナブル」と言い、エクセルの条件付き書式で簡単にそれを実現出来ます。
(動画時間:7:55)
「アクショナブル」なデータ分析を毎回作る
こんにちは、リーンシグマ、ブラックベルトのマイク根上です。業務改善コンサルをしています。
最近ある友人からエクセルの条件付き書式についての質問を受けました。皆さんも質問や動画リクエストがあれば、このリンクから聞いて下さい。
上図は簡単なデータ分析例です。左の表は数字がただ並んでいるだけです。それに対して右の表では条件付き書式を使って「粗利の変動額」(M列)の結果が0以下の顧客をハイライトしています。これで次に何をしたら良いかのアクションを見つけ易くなっています。これを英語で「アクショナブル」と言います。
データ分析には必ず目的があり、作る人はそれを毎回明確にして、全てのデータ分析をアクショナブルに作るべきなのです。これについては以前動画を作りました。⇒「データ分析の基本は?やっぱりPDCAサイクルなんです。」
条件付き書式の基本的なやり方
この条件付き書式でとても簡単に「アクショナブル」を実現出来るのです。今まで使っていなければすごくもったいないです。今日から使い始めて下さい。
今回の例では顧客の最近二ヶ月の売上と粗利、そして粗利の変動額が並んでいます。粗利が下がっている顧客を直ぐに見つけて対策を取りたいです。変動額が0より小さいセルをハイライトする条件付き書式の設定をやってみましょう。
先ずは表内で条件付き書式を出したいセル範囲を選択し、「ホーム」、「条件付き書式」そして「セルの強調表示ルール」から「指定の値より小さい」を選びます(下図参照)。
次の小さい画面で0を入力して「OK」をクリックして完了です(下図参照)。簡単ですね。
今回のスクリーンショットは全てエクセル2016ですが、お使いのエクセルが2007以降であれば大体使い方は同じです。
「データバー」もとても使えます。例えばD列の今月の売上の列を選んで「データバー」の好きな色を選ぶと簡単に売上の違いを視覚化出来ます(下図参照)。
重複した値が入っていないのを確認するための「重複する値」機能
この様に色んな「条件付け」と色んな「書式」がありますが、この中で僕が一番良く使うのはどれだと思いますか?
上図の「重複する値」です。この機能は選択範囲内で重複した値のセルを全てハイライトしてくれます。実務ではデータの行が沢山あってその中で重複した値が入っていないのを確認したい時に良く使います。範囲を選んで「重複する値」を選ぶだけです。
その列のオートフィルターの「色フィルター」で(下図参照)ピンク色が無ければ重複した値は無いし、有ればそのピンクを選べば簡単にどれが重複しているかが分かります。
数式を使う条件付けのやり方
これまでは条件の参照するセルと、書式を付けたいセルが同じでした。もし他のセルの値によって書式を変えたい時はどうするのでしょうか?実務ではこっちの場合の方が多いです。
さっきの例で同じ条件で顧客の名前まで全ての行をハイライトしたいとします。まずはさっきの条件付き書式を削除しましょう。設定したセルを選択して、「条件付き書式」から「ルールの管理」を選んで、さっき作ったのが記録されています。それを選んで、「ルールの削除」をクリックして、「OK」すると削除されました(下図参照)。
表内の全ての列を選択状態にして、「条件付き書式」から今度は「新しいルール」を選びます。ルールの種類が沢山ありますが、僕は最後の「数式を使用して、書式設定するセルを決定」をいつも使います。
上のテキストボックスに「どんな条件付けをするか」の条件式を入れて、下の部分で「その条件の時どんな書式を出したいか」の書式設定するのです。
条件式にはいつも半角のイコールを最初に入れます。条件はF列の「粗利の変動額」が0より下の時です。F3が選択範囲の一番上ですので「=F3<0」と入力します(下図参照)。
次に「書式」をクリックして、その条件に合った時どんな書式にしたいかを設定します。さっきのピンクの「塗りつぶし」だけでなく、「表示書式」、「フォント」のスタイルや色を変えたり、「罫線」も変えたり出来ます。今回の例では「塗りつぶし」で好きな色を選びます。「OK」、「OK」をクリックします。
するとA列からF列まで色が出るはずなのですが、なんと上図のように結果が上手く出ていません。これは条件式が上手くいっていないからです。作った条件付き書式の編集をしましょう。また「ルールの管理」から今作った書式設定を選んで、「ルールの編集」ボタンを押すとさっきと同じ画面が出てきます。
条件付き書式での「セルの絶対参照/相対参照」
ここで「セルの絶対参照/相対参照」を使う必要があります。これについての動画を作りましたので、あまり得意でない方はそちらを先ず見て下さい。⇒「エクセルのセルの絶対参照/相対参照とオートフィル機能【データ分析エクセルスキル】」
条件式内のF3は$マークが無いので相対参照と見なされてしまっているのです。選択範囲がA列からF列ですので、A列のセルの書式の条件はF列を参照していて、F列が0より少ない時はちゃんと色が出ています。
しかし全て相対参照ですので、B列のセルの書式の条件はG列を、C列はH列を参照しているので、いずれも空白で値は0なので0より下ではないので色が出ていないのです。
解決策は簡単で相対参照を絶対参照に変えればいいだけです。つまり条件式のFの前に$マークを入れるだけです。F3の3の前にはドルマークを入れないで下さい。そのセルを下にコピペをする時には相対参照が必要だからです。結果を見ると上手く出来ました(下図参照)。
複数条件での条件付き書式
条件式にはエクセル関数を入れたり、条件を複数にしたり出来ます。二つの条件をAとBとした時に、AでありBでもあるとしたい時は「=AND(A,B)」と書き、A又はBであるとしたい時は「=OR(A,B)」と書きます。
例えばさっきの条件に今月の売上が5000以上という条件を加えてみましょう。イコールの後に「AND(」を入れて、さっきの条件の後にカンマを入れます。今月の売上はD列で絶対参照ですので「$D3>=5000」と入力し、OK、OKを押します。
すると、その二つの条件を満たすのは顧客Dだけでした。これの意味は「顧客Dは大きな顧客で、しかも粗利が下がっているので直ぐに対処が必要です。」となります。
後半の絶対参照/相対参照は分かり難かったかも知れませんね。前半の内容だけでも実践するとデータ分析の質がかなり向上しますので、ぜひこの条件付き書式を今日から使い始めて下さい。
<<データ分析の基本とエクセルスキルシリーズ>>