大きな集計表を作ることはできても、わかりやすく説明ができるように分析をするのは大変です。例えば学力テストを実施して、結果の点数を教科別に500人分集計した表を作ったとして、その中から一番点数が高かった人と一番低かった人を見つけ出すのは1教科だけでも大変な作業になりますし、間違えてしまう可能性は大です。そんな時に役立つのが条件付き書式です。説明用に使用する学力テストのファイル【sample01.zip】を下記リンクよりダウンロードできるようにしていますのでご準備ください。
注意ポイント
ダウンロードしたファイルは圧縮されている状態で、そのままでは閲覧しかできない状態になっているので、zipフォルダを右クリックして【すべて展開】をクリックしてファイルを解凍してからご使用ください。解凍しないとファイルの編集ができません。
エクセル-条件付き書式
目次
指定した数値より大きい(小さい)セルに自動的に色をつける
ここでは例として各教科で75より大きい数字のセルに色をつける方法を紹介します。セルB4からF503までを選択してください。
上級テクニック!
大きな範囲を選択する時、マウスを動かして選択するのは少々時間がかかります。そこでキーボードのショートカット技を使って一発で選択できる技を紹介しておきます。ここではセルB4からF503までを選択する手順を説明します。
①セルB4からF4までをマウスで選択する。
②その状態でキーボードのとを押しながらを押す。
③すると選択しているセルからデータが入力されている最終行までが全て選択される。
これで最終行であるセルF503までが選択できたかと思います。仮にデータが何万行あったとしても一発で選択できるので、この技を知っているだけでも作業時間は大きく短縮できます。自然と指が動くようになるまでになれば百人力です。ぜひともマスターしてください。
※ただしデータが連続して入力されているというのが条件になります。例えば学力テストの表のセルB300が空白になっていた場合は一つ上の299行目までしか選択できません。
次にリボンの【編集】の中にある【オートSUM】をクリックする。リボンの【条件付き書式】→【セルの強調表示ルール】→【指定の値より大きい】をクリックする。
一番上に【指定の値より大きい】と表示されたウィンドウが開くので【75】を入力して、好みの色を選択してOKをクリックする。ここでは初期値の赤のままにしています。
75より大きい数字が入力されているセルに自動的に指定した色がついたと思います。一度設定した条件は削除しない限りそのままなので、数字が変わり条件を満たせば色がつきます。
ポイント
指定した数値より小さいセルに色を付ける場合も同じ要領でできます。最初に選ぶのが【指定の値より小さい】にするだけで後は同じです。
設定したルール(条件)を削除する
1度設定した条件は削除しない限り残ります。このまま次の条件を設定すると2つの条件で色をつけることになり、わかりづらくなってしまうので、先程設定した条件は削除しましょう。【ルールの管理】をクリックします。
ルール(条件)の一覧が表示されます。今は一つしかルールを設定していないので1行だけのはずですが、複数ルールを作成すると2行目、3行目と別行に表示されます。
削除したいルールの行をクリックして上にある【ルールの削除】をクリックします。
先程選択したルールが画面から消えます。確認後【OK】をクリックして終了です。先程赤色になっていたセルが全て色なしに戻ったかと思います。
ポイント
慣れてくると複数のルール(条件)を設定することもありますが、練習段階で複数設定すると非常にわかりづらくなるため、面倒ではありますが次の設定をする前に削除するようにしましょう。
指定した範囲内のセルに自動的に色をつける
次は2つの値の範囲内にある数値が入力されているセルに色をつけます。【指定の範囲内】を選択します。
範囲を入力して色を選択してOKをクリックするだけ。ここでは25以上75以下の数値が入力されたセルを赤色にする設定にしました。
指定した値に等しいセルに自動的に色をつける
次は指定した値に等しいセルにだけ色をつけます。【指定の値に等しい】をクリックします。
色をつけたい値を入力して色を選択する。ここでは50が入力されているセルを赤色にするように設定します。設定後は50が入力されたセルだけ自動的に赤色になります。
指定した文字列を含むセルに自動的に色をつける
数値だけではなく文字列を使った条件設定もできます。次は特定の文字列を含むセルに色をつけるように設定します。【文字列】をクリックします。
特定の文字列を入力して色を選択し、OKをクリックします。ここでは7を含む文字列が入力されたセルが赤色になるように設定します。設定後は7を含んでいるセルが全て赤色になります。
日付を使った条件設定で自動的に色をつける
使用頻度は低いと思いますが、日付を使った条件設定もできます。今回は日付が入力されたセルが必要になるので用意しましょう。基本操作-その他で紹介したオートフィル機能を使えばサクッと作成できますので画像と同じように作成してください。出来上がったら日付全体を選択し、条件付き書式のリボンの中にある【日付】をクリックしましょう。
期間を選択する項目があるのでお好みで選択してください。期間を選択するたびに該当する期間が入力されたセルだけ色が変わるのが確認できるかと思います。ここでは【昨日】を選択した時の画像と【先月】を選択した時の画像を紹介していますので、実際に試してご確認ください。
重複(一意)データに自動的に色をつける
次は重複しているかしていないかによって色をつける設定です。セルG4からG35までを選択してから条件付き書式の【重複する値】を選択する。
重複を選択した場合は、選択したセルの範囲内で同じデータが入力されているセルが赤色に変わります。ここで選択した範囲内では209が二つ存在しているので赤色に変わっているのが確認できます。
一意を選択した場合は先程とは逆の状態になり、選択したセル範囲内で同じデータが存在しない値が入力されたセルに色がつきます。つまり209以外が入力されたセルが赤色になります。
上位/下位ルール
選択範囲の上位や下位に色をつけたい場合は、【上位/下位ルール】を使用します。ここでは合計点数を分析したいと思いますので、セルG4からG503までを選択してください。セルの選択には先ほど上級テクニックとして紹介したショートカットキーを使うように心がけてください。設定は難しくないのでまとめて紹介します。
①上位10項目・・・上位?位に色をつける
②上位10% ・・・上位?%に色をつける
③下位10項目・・・下位?位に色をつける
④下位10% ・・・下位?%に色をつける
⑤平均より上・・・選択範囲での平均より高い数字に色をつける
⑥平均より下・・・選択範囲での平均より低い数字に色をつける
データバー
選択範囲に入力されている数値が全体のどれくらいの位置にあるのか、パッと見てわかるようにする機能です。この機能は範囲を選択後、色を選択するだけになります。これがあるとその人が全体の中のどれくらいに位置しているのかがざっくり分かります。
カラースケール
先程のデーターバーと同じような感じですが、今度は3色を使って大中小を表現する機能になります。セル範囲を選択した状態で【カラースケール】の中にある好きな色を選択してください。個人的にはこちらよりデータバーの方がわかりやすくスマートなので好きです。
アイコンセット
数字の大小をアイコンで表現することも可能です。いろんなアイコンが用意されているのでお試しください。画像は【5つの矢印】を設定したものになります。
新しいルール
もっと細かい設定を使用したい場合は【新しいルール】から設定を行います。少し難しくなりますが、ここでは【MAX】という関数を使い、選択範囲内での最高得点を検索して、そのセルに色をつけたいと思います。セルG4からG503が選択されている状態で、【新しいルール】をクリックしてください。
新しい書式ルールという画面が開くので、①をクリックしてその下に表示された②に =$G4=MAX($G$4:$G$503) と入力します。ここでは詳細は省きますが、入力されている数値がセルG4からG503の中で一番大きい場合、という条件になります。数式を入力したら右下③の書式をクリックしてください。
セルの書式設定画面が開くので、【塗りつぶし】をクリックしてお好きな色を選択して右下の【OK】をクリックしてください。ここでは黄色を選択しています。
セルの書式設定画面が開くので、【塗りつぶし】をクリックしてお好きな色を選択して右下の【OK】をクリックしてください。ここでは黄色を選択しています。前の画面に戻るので、これも右下の【OK】をクリックすると設定が完了します。
456点が最高得点なので、セルG453が自動的に黄色になっているのが確認できました。
まとめ
今回は条件に応じて書式を設定する方法を紹介しました。色の変更を例にしましたが、罫線や文字色などを設定することも可能なので応用してみてください。データが膨大な量になってくると人間の目で判別することは不可能に近くなり時間も浪費しますが、この技を使えば一瞬で間違いなく判別することが可能です。社内での説明資料として提出する時には、色などで強調してわかりやすくするというのが定石ですのでぜひマスターしてください。
新着記事