便利機能

Excel-条件付き書式で色をつける

  1. HOME >
  2. 便利機能 >

Excel-条件付き書式で色をつける

大きな集計表を作ることはできても、わかりやすく説明ができるように分析をするのは大変です。例えば学力テストを実施して、結果の点数を教科別に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% ・・・下位?%に色をつける

⑤平均より上・・・選択範囲での平均より高い数字に色をつける

⑥平均より下・・・選択範囲での平均より低い数字に色をつける

上位/下位ルール

上位10項目の設定

 

データバー

選択範囲に入力されている数値が全体のどれくらいの位置にあるのか、パッと見てわかるようにする機能です。この機能は範囲を選択後、色を選択するだけになります。これがあるとその人が全体の中のどれくらいに位置しているのかがざっくり分かります。

データバー設定画面

データバー設定後

 

カラースケール

先程のデーターバーと同じような感じですが、今度は3色を使って大中小を表現する機能になります。セル範囲を選択した状態で【カラースケール】の中にある好きな色を選択してください。個人的にはこちらよりデータバーの方がわかりやすくスマートなので好きです。

カラースケール設定画面

カラースケール設定後

 

アイコンセット

数字の大小をアイコンで表現することも可能です。いろんなアイコンが用意されているのでお試しください。画像は【5つの矢印】を設定したものになります。

アイコンセット設定画面

5つの矢印

 

新しいルール

もっと細かい設定を使用したい場合は【新しいルール】から設定を行います。少し難しくなりますが、ここでは【MAX】という関数を使い、選択範囲内での最高得点を検索して、そのセルに色をつけたいと思います。セルG4からG503が選択されている状態で、【新しいルール】をクリックしてください。

新しいルール

新しい書式ルールという画面が開くので、をクリックしてその下に表示されたに =$G4=MAX($G$4:$G$503) と入力します。ここでは詳細は省きますが、入力されている数値がセルG4からG503の中で一番大きい場合、という条件になります。数式を入力したら右下の書式をクリックしてください。

新しい書式ルール

セルの書式設定画面が開くので、【塗りつぶし】をクリックしてお好きな色を選択して右下の【OK】をクリックしてください。ここでは黄色を選択しています。

セルの書式設定画面

セルの書式設定画面が開くので、【塗りつぶし】をクリックしてお好きな色を選択して右下の【OK】をクリックしてください。ここでは黄色を選択しています。前の画面に戻るので、これも右下の【OK】をクリックすると設定が完了します。

セルの書式設定画面

OKをクリック

456点が最高得点なので、セルG453が自動的に黄色になっているのが確認できました。

最高得点が黄色になる

 

まとめ

今回は条件に応じて書式を設定する方法を紹介しました。色の変更を例にしましたが、罫線や文字色などを設定することも可能なので応用してみてください。データが膨大な量になってくると人間の目で判別することは不可能に近くなり時間も浪費しますが、この技を使えば一瞬で間違いなく判別することが可能です。社内での説明資料として提出する時には、色などで強調してわかりやすくするというのが定石ですのでぜひマスターしてください。

 

おすすめ!!

本サイトでは常に最新機能を兼ね備えるMicrosoft365 PersonalのExcelを使用しております。旧バージョンでは使用できない便利な機能や最新関数が盛りだくさんなのでぜひ下記よりご購入ください。



新着記事

便利機能

2022/4/9

Excel-名前を姓と名に分ける【フラッシュフィル】の使い方

名簿などを作っている時に、名前を姓と名に分けて入力するか、スペースを空けて一つのセルに入力するか迷う時があります。後々後悔することもしばしばありますが、そんな時に便利なのがフラッシュフィルです。一つのセルに入力されている名前を姓と名に一瞬で分けてくれます。逆に姓と名が別々になっているものを一つにするこも可能です。実に簡単なので覚えておくと何かと便利な機能です。   Excel-フラッシュフィル 目次1 名前を姓と名に分割する2 別々になっている姓と名を結合する3 メールアドレスを分割・結合する4 ...

続きを読む

便利機能

2022/4/8

Excel-フィルター機能の使い方

エクセルのフィルター機能は膨大な量のデータベースから必要な条件に該当するデータだけを抽出する機能です。何万行というデータの中から「この条件に合うデータだけ抽出したい」という時に、目で見て抽出していたら何日かかるかわかりません。その時にフィルター機能を使うと一瞬で必要なデータだけが抽出できます。抽出したデータを基にいろんな分析ができるのがフィルター機能の醍醐味です。   Excel-フィルター機能 目次1 基本的な使い方2 SUBTOTAL関数を使った集計3 まとめ こんな時に便利 膨大なデータベ ...

続きを読む

便利機能

2022/4/8

Excel-入力規則の使い方

入力規則とはあらかじめセルに入力できる値を制限しておく機能です。入力する項目が限られている時はリスト化してマウスで選択するだけで入力することができます。また第三者に発注書などの資料を渡して入力してもらう際、予期せぬ入力をされてしまい、結果的におかしな資料になってしまうようなこともあるため、事前にセルに入力できる値を制限しておくことによりトラブルを未然に防ぐことができます。   Excel-入力規則 目次1 基本的な使い方1.1 【設定】1.2 【入力時メッセージ】1.3 【エラーメッセージ】1. ...

続きを読む

便利機能

2022/4/8

Excel ショートカット一覧【便利技収録】

基本的なコピー&貼り付けから、セルの移動・選択などマウス操作では時間がかかる操作も、ショートカットを使えば一瞬で操作が完了します。ここでは比較的よく使用するショートカットを紹介しますので、ぜひ覚えて活用してください。 参考 印刷用は下記よりダウンロード可能です。 ・ショートカット早見表(縮小版A4用紙1枚)JPG ・ショートカット集(拡大版A4用紙9枚)PDF   エクセル-ショートカット集 目次1 ファイル操作2 検索 / 置換3 入力 / 挿入 / 削除 / 編集4 ワークシート操作5 コピ ...

続きを読む

関数・数式

2022/4/8

Excel【INDEX】【MATCH】の使い方

INDEXは検索関数の一つで、VLOOKUPよりは知名度は低い関数ですが、柔軟な検索が可能となっており、VLOOKUPではできない検索も可能となります。INDEX単体で使ってもあまり意味がなく、MATCHなどとセットで使うのが基本だと覚えてください。MATCHの使い方も一緒に解説します。 エクセル関数-INDEX 目次1 INDEX関数の使い方1.1 基本的な使い方1.2 MATCH関数と組み合わせて使う1.3 領域番号を使って複数の表を切り替えて参照する2 MATCH関数の使い方2.1 完全一致の検索2 ...

続きを読む

-便利機能

© 2024 Excelエクセルを基礎から学び 上級者へ