データ分析を行う際は、数あるデータの中から「特異なデータ」を見つけ出し、そのような結果になった理由や背景を考察していくことが重要となる。こういった「特異なデータ」を見つけ出すときに役立つのが「条件付き書式」という機能。

今回は「条件付き書式」の基本的な使い方を紹介していこう。

「セルの強調表示ルール」で特異なデータを強調する

Excelで集計されたデータは、一覧形式の表にまとめられているのが一般的である。もちろん、表内にある数字の羅列をぼんやりと眺めているだけではデータの傾向をつかめない。データ分析を行うには、数あるデータの中から「特異なデータ」を見つけ出し、そのような結果になった理由を考えたり、今後の対策法を講じたりしていく必要がある。

そこで今回は、「あるTシャツ屋さん」の販売実績を例に「条件付き書式」の使い方を紹介していこう。以下の表は2019年・春の新作商品について、店頭での販売数、Webでの販売数、売上金額、在庫数などのデータをまとめた表である。

  • Tシャツの販売実績をまとめた表

    Tシャツの販売実績をまとめた表

まずは、「残在庫」が多い商品が目立つように「条件付き書式」で加工してみよう。

「残在庫」（J4:J18）のセル範囲を選択し、「条件付き書式」→「セルの強調表示ルール」から条件を選択する。今回の例では、在庫が400枚より多い商品を目立たせたいので「指定の値より大きい」を選択する。

  • セル範囲と条件の選択

続いて、以下の図のような設定画面が表示される。ここでは「条件とする数値」を入力し、好きな「書式」を選択すればよい。これで「400より大きいセル」を自動的に強調表示することが可能となる。

  • 条件（数値）の指定と書式の選択

  • 「条件付き書式」により強調表示されたセル

このように表を加工しておくと、「残在庫の多い商品」を一目で確認できるようになる。ちなみに、今回の例では「残在庫」のセルに（製作数）－（合計）の数式を入力してあるため、その計算結果に応じて「セルの書式」が自動的に変化する仕組みになっている。たとえば、E12セルのデータを75に変更すると、「残在庫」は395になり「400より大きい」の条件を満たさなくなる。その結果、セルの強調表示は自動的に解除される。

  • 数式の再計算による強調表示の解除

このように「条件付き書式」を使うと、「指定した条件」に合うセルを簡単に見つけ出せるようになる。その結果、売れ残りそうな商品を早めにセールにかける、もっと目立つようにディスプレイする、などの対策も講じやすくなるかもしれない。

ここで紹介した内容は、単に「400より大きいセル」に色を付けるだけの処理でしかないが、これをいちいち手作業で行っているようでは、いつまでたってもExcelの初心者から抜け出せない。データ数が多くなってくると、見落としなどによるミスを犯してしまう可能性も高くなるし、手作業による書式指定はいっそう分が悪くなる。機械に任せられる部分は機械にまかせて、データの考察にかける時間を増やすのがスマートなExcelの使い方といえるだろう。

「条件付き書式」は他人に自慢できるほど高度な機能ではないが、Excelに慣れている方ほどよく利用する、隠れた名機能といえる。いちど実務に活用してみれば、その便利さを実感できると思うので、ぜひ使い方を覚えておくとよいだろう。

上位/下位ルール

上記のほかにも「条件付き書式」には様々な機能が用意されている。続いては、トップテンやワースト▲項目などのデータを強調表示する方法を紹介しておこう。

この場合は、対象とするセル範囲を選択した状態で「条件付き書式」→「上位/下位ルール」から条件を選択する。ここには「上位10項目」や「上位10％」などの条件が並んでいるが、この数値は必ずしも「10」でなくても構わない。

  • セル範囲と条件の選択

条件を選択すると以下の図のような設定画面が表示され、「10」以外の数値も指定可能となる。たとえば、左側のボックスに「5」を指定すると、（選択したセル範囲内で）上位5項目のデータを強調表示することができる。

  • 条件（数値）の指定と書式の選択

  • 売れ筋ベスト5のデータを強調表示した場合

同様の操作を「Web」での販売数（E4:E18）に対しても行うと、「店舗」と「Web」のそれぞれで販売数が好調な商品を比較できるようになる。この結果を見ると、「店舗」と「Web」での人気は概ね似たような傾向になるが、若干の差があることがわかる。

  • 「店頭」と「Web」の売れ筋ベスト5を強調表示した場合

もちろん、D4:E18のセル範囲を選択した状態で同様の操作を行うと、実店舗とWebの両方を混在させた状態で、売れ筋ベスト5を調べることができる。

  • D4:E18のセル範囲を選択した場合

このように「条件付き書式」は、あらかじめ選択しておいたセル範囲に応じて結果が変化するようになっている。こういった基本的な考え方もよく理解しておくことが重要だ。

条件付き書式の解除

念のため、指定した「条件付き書式」を解除するときの操作手順も紹介しておこう。

ワークシート全体について「条件付き書式」を解除したい場合は、「条件付き書式」→「ルールのクリア」→「シート全体からルールをクリア」を選択すればよい。

  • 「条件付き書式」の解除

一部のセルについてのみ「条件付き書式」を解除したい場合は、そのセル範囲を選択した状態で「条件付き書式」→「ルールのクリア」→「選択したセルからルールをクリア」を選択する。

ただし、条件付き書式を「指定したとき」と「解除するとき」のセル範囲が一致していないと、予想外のトラブルが生じてしまう恐れもある。よって、一部のセルについてのみ「条件付き書式」を解除するときは、次回の連載で紹介する方法を使った方が無難といえる。

データバーなどの活用

そのほか、セル内に棒グラフを表示できる「データバー」などを利用する場合にも「条件付き書式」が活用できる。こちらについても簡単に紹介しておこう。

データバーを表示するときは、対象にするセル範囲を選択し、「条件付き書式」→「データバー」から色を選択する。

  • データバーの指定手順

こちらは、数値データを視覚的に見やすく示したい場合に活用できる。ただし、グラフの最小値と最大値が勝手に判別されるため、必ずしも見やすいグラフになるとは限らない。細かく書式を指定するには、「新しいルール」を使って「条件付き書式」を指定する方法も覚えておく必要がある（詳しくは次回以降の連載で解説）。

「カラースケール」を指定した場合は、各セルの数値データを「色調」や「濃淡」で表現できるようになる。

  • カラースケールの指定手順

  • 数値の大小を「背景色」で示した表

ただし、見やすい結果にならないケースが多く、正直な話、あまり使い勝手はよくない。「アイコンセット」も同様で、指定手順そのものは簡単であるが、どこを基準にアイコンを変化させるかが自動的に判断されるため、こちらも実際に利用する機会は極めて少ないといえる。

  • アイコンセットの指定手順

  • 数値の大小を「アイコン」で示した表

このように「条件付き書式」の指定そのものは特に難しくはないが、思い通りの結果を得るにはそれなりの工夫が求められる。よって、「新しいルール」を使って「条件付き書式」を指定する方法も学んでおく必要がある。

むしろ、「条件付き書式」を指定するときは「新しいルール」を使うのが基本、と考えておくべきであろう。この方法を知らないと、「▲▲以上」や「▲▲以下」といった基本的な条件すら指定できなくなってしまう（※）。

※「条件付き書式」→「セルの強調表示ルール」に用意されている条件は「▲▲より大きい」や「▲▲より小さい」であり、「▲▲以上」や「▲▲以下」とは微妙に異なる。