【保存版】Excelで数式を使った条件付き書式をマスターする方法|実践テクニック6選

条件付き書式は、セルの値に応じて色やアイコン、太字などの書式を自動的に適用できるExcelの強力な機能です。Excelには使いやすい組み込みルールが用意されていますが、数式を使用したカスタムルールを活用すれば、はるかに柔軟な書式設定が可能になります。本記事では、数式ベースの条件付き書式を使いこなすための実践的なテクニックを6つのパターンに分けて詳しく解説します。

数式ベースの条件付き書式ルールを作成する基本手順

条件付き書式は特定の条件に基づいて動作し、独自の数式でルールを定義することもできます。数式がTRUEを返した場合にのみ書式が適用される仕組みです。基本的な設定手順は以下の通りです。

• 条件付き書式を適用したいセル範囲を選択します。
• ホームタブ >> 条件付き書式 >> 新しいルールを選択します。
• 新しい書式ルールダイアログボックスで以下を設定します。
  • 「数式を使用して、書式設定するセルを決定」を選択します。
  • 「次の数式が満たされる場合に値を書式設定」ボックスにカスタム数式を入力します。
  • 書式ボタンをクリックして、適用したい書式(塗りつぶし・フォントなど)を選択します。
  • OKをクリックして確定します。

以降では、売上データセットを例に、数式ベースの高度な条件付き書式を紹介していきます。

1. 特定の条件を満たす行全体を強調表示する

売上が一定の金額を超える行だけを目立たせたい場合は、比較演算子を使ったシンプルな数式が有効です。

数式:

=$G2>=5000

この数式は、G列(売上)の値が5000以上かどうかを判定します。列に絶対参照($)を付け、行番号は相対参照のままにすることで、範囲内のすべての行に対して同じ列の比較が一貫して行われ、条件を満たした行全体に書式が適用されます。

• 書式をクリックし、好みの書式を設定します(ここでは緑色を選択)。
• OKをクリックしてルールを適用します。

2. 重複する売上データを検出して強調表示する

データセット内の重複エントリを見つけて削除したい場合、COUNTIFS関数を使えば複数列の組み合わせが完全に一致する行を検出できます。

数式:

=COUNTIFS($A$2:$A$17, $A2, $B$2:$B$17, $B2, $C$2:$C$17, $C2, $D$2:$D$17, $D2, $E$2:$E$17, $E2, $F$2:$F$17, $F2, $G$2:$G$17, $G2) > 1

この数式は、A列からG列までの各列について、その行の値の組み合わせが指定範囲内に何回出現するかをカウントします。カウント結果が1より大きい場合、その行は重複とみなされ、強調表示されます。

• 書式をクリックし、好みの書式を設定します(ここでは黄色の塗りつぶしを選択)。
• OKをクリックしてルールを適用します。

3. 1行おきに色を付ける(縞模様スタイル)

表を見やすく整えたいときは、偶数行と奇数行で交互に色を付ける「縞模様(ゼブラストライプ)」書式が便利です。

数式:

=MOD(ROW(),2)=0

ROW関数は現在の行番号を返します。MOD(ROW(),2)=0は、行番号を2で割った余りが0、つまり偶数行かどうかを判定しています。

• 書式をクリックし、好みの書式を設定します(ここでは薄いグレーの背景色を選択)。
• OKをクリックしてルールを適用します。

4. 今日より前の日付(期限切れ項目)を強調表示する

タスクの期限管理などで、今日の日付より過去の日付を自動的に目立たせたい場合は、TODAY関数を活用します。

数式:

=AND(ISNUMBER(A2), A2 < TODAY())

この数式は、A2の値が日付として有効な数値であり(TODAY関数との比較のため)、かつ今日の日付より前であるかを判定します。これにより、期限切れの項目だけを赤字などで強調表示できます。

• 書式をクリックし、好みの書式を設定します(ここでは赤色の塗りつぶしを選択)。
• OKをクリックしてルールを適用します。

5. 上位10%の売上値を強調表示する

売上データの中から特に優れた成績の上位10%だけを抽出して強調したい場合は、PERCENTILE(パーセンタイル)関数が便利です。

数式:

=$G2>=PERCENTILE($G$2:$G$17,0.9)

PERCENTILE($G$2:$G$17,0.9)は、指定範囲の90パーセンタイル値を計算します。この値以上のデータは上位10%に該当するため、G列の値がこのしきい値以上であれば、行全体に書式が適用されます。

• 書式をクリックし、好みの書式を設定します(ここでは太字+緑色の塗りつぶしを選択)。
• OKをクリックしてルールを適用します。

6. AND/OR関数で複数条件を組み合わせる

論理関数を使えば、複数の条件を組み合わせた複雑なルールも作成できます。AND関数やOR関数を活用することで、より精密なデータ分析が可能になります。

数式:

=AND($G2>=3000, $C2="East")

この数式は、「売上が3000以上」かつ「地域がEast(東)」という両方の条件を満たす行だけを強調表示します。どちらか一方でもよい場合は、ANDをORに置き換えてください。

• 書式をクリックし、好みの書式を設定します(ここでは斜体フォント+ピーチ色の塗りつぶしを選択)。
• OKをクリックしてルールを適用します。

条件付き書式ルールの管理方法

作成したルールは後から自由に編集・管理できます。

• ホームタブの条件付き書式 >> ルールの管理を選択します。
• この画面では、既存ルールの表示・追加・編集・削除・複製、さらに優先順位の変更が可能です。
• 「適用先」フィールドを編集すれば、ルールを適用するセル範囲も後から変更できます。

まとめ

数式ベースの条件付き書式を使いこなせば、Excelでのデータ可視化の幅が大きく広がります。特定の条件を満たす行の強調表示、重複データの検出、複数条件の組み合わせ、期限切れ項目のチェックなど、標準の組み込みルールでは実現できない柔軟な書式設定が可能です。この機能をマスターすれば、スプレッドシートはより洞察に富んだ、視覚的にも魅力的なものになります。さまざまな数式を試しながら、自分のワークフローに合った活用方法を見つけてください。