Aさん とある表で、下位2項目に該当するセルだけ塗りつぶしの色を変更することがありますが、手作業で対応するのが大変すぎます・・・。
何かお手軽な方法はありますかね？
森田 そんな場合、条件付き書式の「上位/下位ルール」という機能を使うと良いですよ！
では、詳細を解説していきますね。
はじめに
この記事は条件付き書式の概要を把握していることが前提です。
条件付き書式の概要については、以下の記事をご参照ください。
表を色やバー、アイコン等で強調したい場合は「条件付き書式」が有効 実務では、表を色等で強調することで利便性を高めたい場合があり、2種類のケースに大別できます。 集計表の着目すべきポイントを色/バー/アイコンで強調表示した …
特に、条件付き書式の共通操作（ルールの適用先変更/表示順変更/複製/停止/削除/クリア）は上記記事を参照のこと。
相対的な条件に一致するセルを「色」で強調したい場合は「上位/下位ルール」が有効
実務では、既存データに対して基準値を条件に評価や判定を行う機会が多いですが、データ内の上位/下位に該当するデータに着目したい場合があります。
たとえば、下位2項目（ワースト2位まで）のデータを特定したい等です。
こうした基準でセルを色付けする場合、データによって上位/下位の数値は変動するため、手作業だと対象のセルを特定することが大変です。
このように、「下位2項目」という相対的な条件で色付けしたい場合は、条件付き書式の「上位/下位ルール」を活用します。
上位/下位ルールを使うことで、相対的な条件に一致するセルのフォントや塗りつぶしの色付けを自動化することが可能です。
これにより、条件ごとの色付けを手作業で行うよりも効率的なのはもちろん、ヒューマンエラーも防止できます。
なお、上位/下位ルールは、指定の条件に一致（TRUE）する場合のみ、任意の書式が自動反映される仕組みです（FALSEの場合はセルに設定した書式のまま）。
また、相対条件という性質から、条件付き書式を設定したセルの値が数値である必要があります。
上位/下位ルールの使用イメージ
上位/下位ルールで「構成比」列の値が「下位2項目」に一致する場合の書式を「濃い赤の文字、明るい赤の背景」にしたイメージが以下です。
条件付き書式自体はC4~C12セルに設定していますが、その範囲内で条件（下位2項目）に一致するC5・C8セルのみを赤く色付けできています。
この後、データによって「構成比」列の数値の大小が変わったとしても、それに合わせて書式も自動で反映されます。
なお、自動反映可能な書式のメインは、セルの塗りつぶしやフォントの色ですが、表示形式や罫線、フォントサイズ等も変更することは可能です（実務での使用頻度は低い）。
上位/下位ルールの設定手順
上位/下位ルールを設定したい場合は、以下の手順となります。
- 上位/下位ルールを設定したいセル範囲を選択
※今回はC4~C12セル - リボン「ホーム」タブをクリック
- 「条件付き書式」をクリック
- 上位/下位ルールをクリック
- 任意の条件を選択
※今回は「下位10項目」 - 任意の値を入力
※今回は「2」 - 任意の書式を選択
※今回は「濃い赤の文字、明るい赤の背景」 - 「OK」をクリック
手順①のセル範囲に総計/小計等を含めないことを推奨（含めると上位/下位の判定に影響するため）。
手順⑤で選択肢にある条件以外を設定したい場合、「その他のルール」を選択することで、「新しい書式ルール」ダイアログにて詳細な条件を設定可能。
手順⑤で「平均より上」・「平均より下」を選択した場合、手順⑥は省略。
手順⑦で選択肢にある書式以外を設定したい場合、「ユーザー設定の書式」を選択することで、「セルの書式設定」ダイアログにて詳細な書式を設定可能。
上位/下位ルールで設定可能な条件の種類
手順⑤で選択した条件により、手順⑥以降のダイアログの内容が変わります。
条件別の詳細を順番に解説していきましょう。
上位10項目
「上位10項目」は、文字通り「条件付き書式を設定したセル範囲の中の上位10項目」という条件です。
数値の大きい順から数えたトップ10（ベスト10）と読み替えることもできます。
なお、条件名に「10」とありますが、この数値は「上位10項目」ダイアログにて変更可能です。
左側のボックスの数値を直接編集するか、スピンボタンで数値を「1」ずつ増減するかで任意の数値にしましょう。
ちなみに、上位にあたる値が複数セルある場合、書式が反映されるセルが指定条件の項目数と合わない場合があります（例：「上位1項目」に設定時、同率1位のセルが2つある場合、その2セルが書式反映される等）。
上位10%
「上位10%」は、文字通り「条件付き書式を設定したセル範囲の中の上位10%」という条件です。
こちらはデータ数ではなくパーセンテージのため、条件付き書式を設定したセル範囲の数値のバラツキ具合によって書式反映されるデータ数は変動します。
なお、条件名に「10」とありますが、この数値は「上位10%」ダイアログにて変更可能です。
左側のボックスの数値を直接編集するか、スピンボタンで数値を「1」ずつ増減するかで任意の数値にしましょう。
なお、上位◯%の基準値が知りたい場合は、関数の「PERCENTILE」で調べることも可能です。
なお、PERCENTILEは基本的に下位◯%の基準値を調べる仕様のため、上位%の場合は「=PERCENTILE(C4:C12,1-◯%)」のように2つ目の引数で「1」もしくは「100%」から対象の%を減算しましょう。
PERCENTILE内部の計算ロジックの詳細が気になる方は、以下の記事が参考になると思います。
下位10項目
「下位10項目」は、文字通り「条件付き書式を設定したセル範囲の中の下位10項目」という条件です。
数値の小さい順から数えたワースト10と読み替えることもできます。
なお、条件名に「10」とありますが、この数値は「下位10項目」ダイアログにて変更可能です。
左側のボックスの数値を直接編集するか、スピンボタンで数値を「1」ずつ増減するかで任意の数値にしましょう。
こちらも下位にあたる値が複数セルある場合、書式が反映されるセルが指定条件の項目数と合わない場合があります（例：「下位1項目」に設定時、同率1位のセルが2つある場合、その2セルが書式反映される等）。
下位10%
「下位10%」は、文字通り「条件付き書式を設定したセル範囲の中の下位10%」という条件です。
こちらはデータ数ではなくパーセンテージのため、条件付き書式を設定したセル範囲の数値のバラツキ具合によって書式反映されるデータ数は変動します。
なお、条件名に「10」とありますが、この数値は「下位10%」ダイアログにて変更可能です。
左側のボックスの数値を直接編集するか、スピンボタンで数値を「1」ずつ増減するかで任意の数値にしましょう。
なお、下位◯%の基準値が知りたい場合は、こちらも関数のPERCENTILEで調べることが可能です。
下位%の場合は「=PERCENTILE(C4:C12,◯%)」のようにPERCENTILEの普通の使い方でOKです。
平均より上
「平均より上」は、文字通り「条件付き書式を設定したセル範囲の中の平均値より上」という条件です。
言い換えれば、「平均値より大きい値」が書式反映されます。
この条件を選択時に起動する「平均より上」ダイアログは以下の通りです。
このダイアログは手順⑥が省略されます。
平均より下
「平均より下」は、文字通り「条件付き書式を設定したセル範囲の中の平均値より下」という条件です。
言い換えれば、「平均値より小さい値」が書式反映されます。
この条件を選択時に起動する「平均より下」ダイアログは以下の通りです。
このダイアログも手順⑥が省略されます。
その他のルール – 平均より上または下の値だけを書式設定
手順⑤の選択肢以外の条件にしたい場合、「その他のルール」をクリックしましょう。
そうすると、「新しい書式ルール」ダイアログが起動し、ルールの種類は「上位または下位に入る値だけを書式設定」が選択された状態になります。
しかし、「上位または下位に入る値だけを書式設定」は前述の「上位10項目」~「下位10%」のルールと同じ条件が設定できるのみです。
手順⑤の選択肢以外にしたい場合は、「平均より上または下の値だけを書式設定」を選択しましょう。
後は、「選択範囲の平均値」ボックスで詳細な条件を選択できます。
このダイアログでしか設定できない条件は、以下の通りです。
- 以上
- 以下
- より1標準偏差上
- より1標準偏差下
- より2標準偏差上
- より2標準偏差下
- より3標準偏差上
- より3標準偏差下
「よりN標準偏差上（下）」の条件は実務で使うことは少ないと思いますが、それぞれ以下が基準値となります（Nは1~3の数値）。
- 「N標準偏差上」：平均値+（標準偏差×N）
- 「N標準偏差下」：平均値–（標準偏差×N）
仮に、平均値が「10」、標準偏差が「5.5」のデータに対し「より1標準偏差上」にした場合、「15.5より大きい」という条件になるイメージです。
平均値（AVERAGE）と標準偏差（STDEVP）の詳細は、以下の記事をご参照ください。
上位/下位ルールのルールの編集
後から上位/下位ルールの詳細ルールを編集できます。
そのためには、以下の手順で「書式ルールの編集」ダイアログを起動しましょう。
- 編集したい上位/下位ルールが設定されたセル範囲を選択
※今回はC4~C12セル - リボン「ホーム」タブをクリック
- 「条件付き書式」をクリック
- 「ルールの管理」をクリック
- 任意のルールを選択
- 「ルールの編集」をクリック
- 任意の内容へ編集
- 「OK」をクリック
- 「OK」をクリック
手順⑤⑥は任意のルールをダブルクリックで省略可能。
手順⑦の「書式ルールの編集」ダイアログで設定変更できる内容は、前述の「新しい書式ルール」ダイアログ（その他のルール）と同様。
手順⑧は「適用」ボタンをクリックすると、「条件付き書式ルールの管理」ダイアログを閉じずにワークシート上でルール編集した結果を確認することが可能（「OK」ボタンをクリックするとダイアログが閉じてしまう）。
【注意】設定手順⑤の選択肢により「書式ルールの編集」ダイアログのルールの種類が違う
上位/下位ルールの設定手順⑤の選択肢により、「書式ルールの編集」ダイアログ上のルールの種類が異なります。
- 上位または下位に入る値だけを書式設定：上位10項目、上位10%、下位10項目、下位10%
- 平均より上または下の値だけを書式設定：平均より上、平均より下
後から上記ルールの種類をまたぐ条件を変更することがある際に混乱しないようお気をつけください。
【参考】上位/下位の「項目」↔「%」を切り替える場合
「書式ルールの編集」ダイアログ上のルールの種類「上位または下位に入る値だけを書式設定」で、後から上位/下位の「項目」↔「%」を切り替えたい場合は、「%（選択範囲に占める割合）」のチェックのON/OFFを切り替えましょう。
具体的には、条件別のチェックON/OFFは以下の通りです。
- 項目：チェックOFF
- %：チェックON
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 上位/下位ルールを設定したいセル範囲を選択
※今回はC4~C12セル - リボン「ホーム」タブをクリック
- 「条件付き書式」をクリック
- 上位/下位ルールをクリック
- 任意の条件を選択
※今回は「下位10項目」 - 任意の値を入力
※今回は「2」 - 任意の書式を選択
※今回は「濃い赤の文字、明るい赤の背景」 - 「OK」をクリック
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
上位/下位ルールは、相対的な条件でセルを「色」で強調できる機能です。
集計表の着目ポイントをランキング的に強調する機会があれば、覚えることをおすすめします。
なお、上位/下位ルール以外にもExcelでのデータ分析の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 上位/下位ルールとセットで覚えた方が良いのは、条件付き書式の「セルの強調表示ルール」です。
上位/下位ルールは相対的な条件でしたが、「セルの強調表示ルール」の方は「×と等しい」等の絶対的な条件で色付けしたい場合に使います。
ケースに応じて、この2種類の条件を使い分けできるとより便利です。
“【条件付き書式】相対的な条件でセルを色付けできる「上位/下位ルール」の使い方” への1件のフィードバック