エクセルの条件付き書式を設定する時、こんな経験をすることがあります。
- 「達成率」「構成比」など、「パーセント」が並ぶセルに、条件付き書式を設定する時
- データがパーセントだからと、設定項目も「パーセント」を選ぶと、思い通りにアイコンが分かれない。
- MOSの模擬試験でも「パーセント」を選ぶと「×」がつく
- 正しくは「数値」を選択しなればならない
本記事では、こんなとき迷わずに「パーセント」「数値」の選択ができるように解説していきます。
パーセントで表されたデータに条件付き書式を設定する
実際に試してみる
パーセントで表したサンプルデータを作り、実際に条件付き書式を設定してみます。
そのまま見るとスマホだと小さくて見づらいと思いますので、タップして画像を表示し、二本指で拡大してご覧ください。
| 設定 | 境界値1 | 境界値2 | |
| 左 | ﾊﾟｰｾﾝﾄ | 67 | 33 |
| 中央 | 数値 | 0.67 | 0.33 |
| 右 | 数値 | 67 | 33 |
(1)「59%」のセルは、実際は「0.59」が入っている
まず目につくのは、右側のデータがすべて真っ赤に設定され、明らかに正しくないことです。
これは、例えば「59%」と見えているセルは、実際にセルに入っているデータは「0.59」だからです。
セルの書式設定で「%」の書式を選ぶと、自動的にセルのデータを100倍したものに「%」がついて表示されるようになっています。
ですから、「59%」と表示されているセルの本当の値を知るには、100で割ってみる必要があるのです。
そうすると、「0%～100%」は、実は「0～1」のデータになります。
境界値を「数値」→「67」「33」と設定した場合には、
全部が 0～1なのですから「33よりも、ものすごく小さい」ことになり、いまの画像にあるように、条件付き書式は真っ赤に染まってしまうのです。
(2) 設定「パーセント」は、「データの並びの中での『かたより』」を示す設定
次に、設定として「パーセント」を選んだものを、並べ替えてみます。
すると、だいたい合っているのですが、一か所、おかしなところがあることに気づきます。
「パーセント」の設定は、このように望んだ結果と「かなり近いがちょっと違う」結果になることが多く、どうしてそうなるのか、分かりにくいことが多くあります。
この場合のアイコンの色分けは、次のような計算によって設定されています。
このように、条件付き書式の設定「パーセント」は、データそのものではなく、データ全体の幅の中で「かたより」に注目し、「かたより」の度合いをパーセントで計算して境界値にするものだ、ということができます。
ですから逆に、データそのものがパーセントでなくてもよいのです。パーセントにしない、そのままの売上データ、成績データなども、条件付き書式「パーセント」設定で、まったく同じように色分けすることができます。
なお、このような「かたより」に注目する設定は、さらに
- パーセント
- 百分位
の二種類があります。
パーセントと百分位の区別については、当ブログの別記事で詳しく解説していますので、そちらをご覧ください。
Excelの条件付き書式の設定。「百分位」って何のこと? | キュリオステーション志木店のブログ
(3) データそのものに応じて色分けするには「数値」「0.67」「0.33」を設定
セルに入っているパーセントのデータそのものにピッタリ合わせて色分けするには、「数値」「0.67」「0.33」の設定を使います。
「0.67」「0.33」は、それぞれ「67%」「33%」を境界値にする、という意味です。
そのとおりにピッタリ色分けされているのがわかると思います。
独特のややこしさがある設定ですが、仕組みを理解していただければ、間違うことはなくなると思います。
分からなくなったら、じっくり考えて、どれが正しいか、選択してみてくださいね。
コメント