エクセル実践塾 > 特集「条件付き書式」

条件付き書式を使って、平均より上または下の値を強調する

 

Excel 2013/2010/2007では「上位/下位ルール」を使うことで、Excel 2003/2002ではAVERAGE関数による条件式を使うことで、平均より上または下のセルを強調できます

「上位/下位ルール」では、平均や標準偏差より、上または下の値を容易に見つけることも可能です。

「上位/下位ルール」は2003や2002には用意されていない条件付き書式ですが、AVERAGEなどの関数で条件式を設定することで、同様の結果を得ることができます。

 

Excel 2013/2010/2007の場合

Excel 2003/2002の場合

@「ホーム」タブをクリックして、セル範囲を選択します

@セル範囲を選択します

A「条件付き書式」のメニューから「上位/下位ルール」→「平均より上」を選びます

A「書式」メニューから「条件付き書式」を選びます

B適当な書式を選んで、「OK」ボタンをクリックします

選んだ書式は、すぐにセル範囲で適用されます。

B「数式が」を選んで、次のような数式を入力し、適当な書式を設定してから、「追加>>」ボタンをクリックします

=E2>AVERAGE(E$2:E$40)

AVERAGEは、セル範囲の平均値を返す関数です。「AVERAGE(範囲)」という書式で使います。

この作例では、セル範囲の指定を、行番号のみの絶対指定にしていますが、これはあとから右側の他のセル範囲にも同様の条件付き書式を適用する予定があるからです。

C「条件付き書式」のメニューから「上位/下位ルール」→「平均より下」を選びます

C「条件付き書式の設定」ダイアログボックスが拡張されて、「条件2」の欄が表示されます

D適当な書式を選んで、「OK」ボタンをクリックします

選んだ書式は、すぐにセル範囲で適用されます。

D「条件2」も「数式が」に変えます

E「条件付き書式」のメニューから「ルールの管理」を選びます

 
 

F先ほど設定した「平均より下」のルールを選んで、「ルールの編集」ボタンをクリックします

 

E〜G次のような数式を入力し、適当な書式を設定してから、「OK」ボタンをクリックします

=(E2-AVERAGE(E$2:E$40))<=STDEVP(E$2:E$40)*(-1)

STDEVPは、 引数を母集団全体であると見なして、母集団の標準偏差を返す関数です。標準偏差とは、統計的な対象となる値が、その平均値からどれだけ広い範囲に分布しているかを計量したものです。

このSTDEVP関数の結果に1もしくは-1をかけて、平均値を引いたものと比較することで、対象となるセルの値が第 1 標準偏差より上か下かを判断できます。

セル範囲の指定を、行番号のみの絶対指定にしているのは、あとから右側の他のセル範囲にも同様の条件付き書式を適用する予定があるからです。

G「選択範囲の平均値」を「より1標準偏差下」にして、「OK」ボタンをクリックします

第 1 標準偏差に収まらないセルを強調します

H「適用」ボタンをクリックして、編集したルールの結果をシートで確認します

 
 

I「OK」ボタンをクリックして、条件付き書式ルールの管理を終えます

HI「OK」をクリックすると、平均より上のセルと、第1標準偏差より下のセルが強調されます

Jセル範囲の枠線を、隣のセル範囲に右ドラッグします

Jセル範囲のフィルハンドルを、「社会」の列まで右ドラッグします

K右ドラッグ直後に表示されるメニューから「ここに書式のみをコピー」を選びます

K右ドラッグ直後に表示されるメニューから「書式のみコピー」を選びます

L書式コピー先に、同じ条件付き書式が適用されます

同様にして、「理科」や「社会」にも条件付き書式をコピーします

L「国語」の列の条件付き書式が、「算数」〜「社会」の列にもコピーされます

※「上位/下位ルール」で適用した条件付き書式は、フィルハンドルでは正しくコピーされません

フィルハンドルで書式コピーすると、「上位/下位ルール」の設定範囲が広がります。作例で言うなら、「国語」の列に設定した「上位/下位ルール」が、「国語」〜「社会」の範囲に広がり、それらのセル範囲 全体の中から平均が求められ、該当するセルが強調されてしまいます。

セル範囲の枠線を右ドラッグして書式コピーした場合には、コピー先の「上位/下位ルール」は、コピー先のセル範囲のみが対象となります。作例で言うなら、各科目で 該当するセルが強調表示されます。

下の画面は、2つの書式コピーを比較したものです。強調されるセルがわずかに異なるのがわかります。

 
 

 

関連する他のページ

別シートの一覧に含まれる商品名が入力されたときに、そのセルを任意の色で塗りつぶして強調したい
カラースケールを使えば、数値の多少を簡単に色分けできます

他のシートに条件付き書式をコピーしたい
「形式を選択して貼り付け」を使って、「書式」のみを貼り付ければ、他の書式といっしょに、条件付き書式もコピーできます

40時間以上のセルは赤色で塗りつぶしたい
条件付き書式は、追加設定できます

条件付き書式で塗りつぶした色を、別の色に塗り替えたい@
塗り替えたいセルの条件付き書式をクリアすれば、任意の色で塗りつぶせるようになります

条件付き書式で塗りつぶした色を、別の色に塗り替えたいA
条件付き書式で設定されているもの以外の書式であれば、通常の書式設定が効きます

条件付き書式で塗りつぶした色を、別の色に塗り替えたいB
書式にこだわらず、文字で表すのも一つの方法です

条件付き書式で塗りつぶした色を、別の色に塗り替えたいC
新たな条件付き書式で塗り替えることができます

偏差値の上位10%にあたる生徒の行を強調したい
特定の列のセルを条件にして、行全体を強調する場合は、数式でその条件を指定します

各教科の偏差値上位の強調表示を優先したい
ルールの管理を使えば、条件付き書式の優先順位を自由に変更できます

他のシートやブックに条件付き書式だけをコピーしたい
条件付き書式の設定されているセルをコピーしておけば、その適用先を変えることで、他のシートやブックにも条件付き書式だけをコピーできます

20時間以上のセルを黄色で塗りつぶしたい
セルの値を対象とする、条件付き書式を用います

他のセル範囲に条件付き書式だけをコピーしたい
条件付き書式の適用先を変えることで、条件付き書式だけを他のセル範囲にコピーできます

同じ値が連続するセル範囲を強調したい
上のセルとの比較、そして下のセルとの比較を行い、いずれかの条件式がTRUEの場合、同じ値が連続するセル範囲の1つだと判断できます

空白のセルを強調したい
「指定の値を含むセルだけを書式設定」で「空白」を指定します

月別で回答数の一番多いセルに色をつけたい
条件付き書式を使えば、MAX関数の結果と照らし合わせることで、最大値のセルに色を付けられます

上位10人の得点を強調したい
条件付き書式の「上位/下位ルール」を使えば、選択中のセル範囲から、「上位10項目」の値を簡単に強調できます

スケジュール表の土日を塗りつぶしたい
日付や曜日がシリアル値(日時データ)で入力されていれば、WEEKDAY関数を使った条件付き書式で、土日だけを強調できます

上のセルと同じ値になっているセルを強調したい
条件付き書式で、上のセルとの比較を行えば、同じ値の続くセルを適当な書式で強調できます

40以上と30以上、それ以外のセルを明確に分けたい
アイコンセットを割り当てることで、明確にセルを分類できます

土曜日と日曜日の行を色分けしたい
条件付き書式を使えば、自動的な色分けが可能です