皆さん、こんにちは！Excel VBA講師の〇〇です。（※ブログでは実名が入りますが、ここでは仮に〇〇とします。）
日頃からExcelと格闘されている皆さん、データ分析や資料作成、本当にお疲れ様です。
Excelの数ある機能の中でも、特に「データを視覚的に理解しやすくする」という点で絶大な威力を発揮するのが「条件付き書式」ですよね。セルの値に応じて自動的に色を付けたり、アイコンを表示したりする機能は、まさにデータの羅列に「命を吹き込む」と言っても過言ではありません。
しかし、多くの方が使っている条件付き書式は、実はそのポテンシャルのほんの一部に過ぎません。
「特定の文字列が含まれていたら」「平均より大きかったら」「上位10%だったら」といった、セルの値そのものに基づいた条件設定は非常に便利です。ですが、Excelの条件付き書式には、もう一段階上の、まさに「神業」とも言える使い方があります。それが、**「条件に数式を設定する」** 方法です。
「え、条件に数式？」と少し構えてしまった方もいらっしゃるかもしれませんね。ご安心ください。本記事では、Excelの条件付き書式で数式を使いこなすための基本から応用、そしてベテラン講師ならではの「つまずきやすいポイント」と「解決策」まで、徹底的に解説していきます。
この記事を読み終える頃には、あなたは単なるExcelユーザーから、条件付き書式を自在に操る「データ表現の達人」へと進化していることでしょう。
さあ、一緒にExcelの奥深い世界へ旅立ちましょう！
条件付き書式、その基本を再確認
まずは、条件付き書式の基本的な使い方を簡単におさらいしましょう。
条件付き書式は、リボンの「ホーム」タブにある「スタイル」グループの中にあります。
1. **書式を設定したいセル範囲を選択します。**
2. **「条件付き書式」をクリックします。**
3. **「新しいルール」を選択します。**
ここまでは、皆さんお馴染みの操作かと思います。
「新しいルール」ダイアログボックスを開くと、いくつかの「ルールの種類」が選択できます。通常は、「指定の値を含むセルだけを書式設定」や「上位/下位ルール」などを選んで使いますよね。
しかし、今回注目するのは、その中にある**「数式を使用して、書式設定するセルを決定」**という項目です。
この項目を選ぶことで、Excelの持つ強力な計算能力を条件付き書式に持ち込み、セルの値だけでは表現しきれない、より複雑で高度な条件設定が可能になるのです。
なぜ数式を使うのか？その強力なメリットとは
「数式を使って条件を設定する」と聞くと、少し難しそうに感じるかもしれません。しかし、その手間をかけるだけの、いやそれ以上の絶大なメリットがあります。
1. **他のセルの値に基づいて書式を設定できる**
* これが数式を使う最大のメリットと言っても過言ではありません。例えば、「A列が『完了』だったら、その行全体を緑色にする」といった、参照するセルと書式を設定するセルが異なる条件を設定できます。
* また、「B列の日付が今日より前だったら、そのセルを赤色にする」といった、日付や時刻、他のシートのデータなど、さまざまな情報に基づいて書式を適用できます。
2. **複数の条件を組み合わせて書式を設定できる（AND/OR）**
* 通常の条件付き書式では、複数の条件を組み合わせるのが難しい場合があります。しかし、数式を使えば、`AND`関数や`OR`関数を駆使して、「A列が『未着手』**かつ**B列が今日より前の日付だったら」といった複雑な条件も簡単に実現できます。
3. **行全体や列全体に書式を適用できる**
* 特定のセルだけでなく、その条件を満たす行全体や列全体に書式を適用することで、データの視認性が格段に向上します。これは、データの傾向や問題点を一目で把握する上で非常に強力な機能です。
4. **Excelのあらゆる関数を活用できる**
* `SUM`、`AVERAGE`、`COUNTIF`、`TODAY`、`RANK`など、Excelが持つ200以上の関数を条件式の中に組み込むことができます。これにより、より高度な分析に基づいた書式設定が可能になります。
これらのメリットを理解すれば、「数式を使って条件を設定する」ことが、いかにExcel作業の効率化とデータ分析の深化に貢献するかがお分かりいただけるでしょう。
数式を設定する際の基本ルールと最重要ポイント：参照形式
さて、いよいよ数式を設定する具体的な方法に入りますが、その前に絶対に押さえておくべき基本ルールと、最も重要で、かつ最もつまずきやすいポイントである「参照形式」について解説します。
ルール1：数式は「真（TRUE）」か「偽（FALSE）」を返すべし
条件付き書式に設定する数式は、必ず**「真（TRUE）」**か**「偽（FALSE）」**のいずれかの論理値を返すように作成します。
* 数式が**TRUE**を返した場合、そのセルに書式が適用されます。
* 数式が**FALSE**を返した場合、書式は適用されません。
例えば、`=A1>100`という数式は、A1の値が100より大きければTRUE、そうでなければFALSEを返します。
`=A1=”完了”`という数式は、A1の値が「完了」という文字列と一致すればTRUE、そうでなければFALSEを返します。
もし数式が論理値以外の値を返した場合（例えば数値や文字列）、ExcelはそれをTRUEとみなす場合がありますが、意図しない結果を避けるためにも、常に論理値を返す数式を心がけましょう。
ルール2：数式は「適用先」の左上隅のセルを基準に評価される
これが、条件付き書式で数式を使う際の「肝」であり、多くの人が混乱するポイントです。
数式を記述する際、あたかも「適用先」として指定した範囲の**左上隅のセル**に、その数式を入力しているかのように記述します。そして、その数式が、適用範囲内の他のセルに「コピー」されていく、というイメージで考えてください。
この「コピー」の挙動を制御するのが、次に説明する「参照形式」です。
最重要ポイント：参照形式（相対参照、複合参照、絶対参照）の理解
Excelには、セルを参照する方法として以下の3種類があります。
* **相対参照（例: `A1`）**: 数式がコピーされると、参照先のセルも相対的に移動します。
* **絶対参照（例: `$A$1`）**: 数式がどこにコピーされても、常に指定したセル（この場合はA1）を参照し続けます。
* **複合参照（例: `$A1` または `A$1`）**:
* `$A1`（列絶対参照）: 列は固定（A列）されますが、行は相対的に移動します。
* `A$1`（行絶対参照）: 行は固定（1行目）されますが、列は相対的に移動します。
条件付き書式で数式を設定する際、この参照形式の使い分けが非常に重要になります。
なぜなら、前述の通り、数式は適用範囲の左上隅のセルを基準に作成され、それが他のセルに「適用」される際に、この参照形式に従って参照先が変化するからです。
具体的に見ていきましょう。
* **「特定の列の値に基づいて、その行全体を書式設定したい」場合**
* 例: B列の値が「完了」だったら、その行全体を緑色にする。
* 適用範囲: `A1:D10` (例えば)
* 数式: `=$B1=”完了”`
* 解説:
* 適用範囲の左上隅はA1セルです。数式はA1セルに設定されるものとして考えます。
* `$B1` の `$B` は、常にB列を参照するという意味です。これにより、A列、C列、D列のセルに書式が適用される際も、B列の値を参照し続けることができます。
* `B1` の `1` は、行が相対参照であることを意味します。これにより、A2、A3…といった下の行のセルに書式が適用される際には、それぞれB2、B3…といったその行のB列の値を参照するようになります。
* もし `$B$1` と絶対参照にしてしまうと、すべてのセルがB1セルの値だけを参照してしまい、意図した結果になりません。
* **「特定の行の値に基づいて、その列全体を書式設定したい」場合**
* 例: 1行目の値が「重要」だったら、その列全体を赤色にする。
* 適用範囲: `A1:D10`
* 数式: `=A$1=”重要”`
* 解説:
* `A$1` の `A` は、列が相対参照であることを意味します。これにより、B列、C列、D列のセルに書式が適用される際に、それぞれB1、C1、D1といったその列の1行目の値を参照するようになります。
* `$1` は、常に1行目を参照するという意味です。これにより、A2、A3…といった下の行のセルに書式が適用される際も、1行目の値を参照し続けることができます。
この参照形式の使い分けをマスターすることが、条件付き書式で数式を使いこなすための最大のカギとなります。
最初は混乱するかもしれませんが、実際に手を動かしながら試していくうちに、感覚が掴めるようになります。
実践例で学ぶ！具体的な数式設定テクニック
それでは、具体的なシナリオを通して、数式を使った条件付き書式の設定方法を学んでいきましょう。
以下のデータがシートに入力されていると仮定します。（A列：ID、B列：タスク名、C列：担当者、D列：ステータス、E列：期限、F列：売上）
| ID | タスク名 | 担当者 | ステータス | 期限 | 売上 |
| :– | :——- | :—– | :——— | :— | :— |
| 101 | 資料作成 | 田中 | 未着手 | 2023/12/31 | 50000 |
| 102 | 顧客訪問 | 山田 | 進行中 | 2024/01/15 | 80000 |
| 103 | 会議準備 | 田中 | 完了 | 2023/12/20 | 0 |
| 104 | 報告書作成 | 佐藤 | 未着手 | 2024/01/05 | 30000 |
| 105 | データ集計 | 山田 | 進行中 | 2024/01/10 | 60000 |
| 106 | 請求書発行 | 田中 | 完了 | 2023/12/25 | 0 |
例1：特定の文字列を含む行全体を強調する
「ステータス」列（D列）が「完了」の行全体を薄い緑色で強調したい。
1. **書式を設定したい範囲を選択します。**
* ここでは、データのある `A2:F7` を選択します。（見出し行を除く）
2. **「条件付き書式」→「新しいルール」を選択します。**
3. **「数式を使用して、書式設定するセルを決定」を選択します。**
4. **「次の数式を満たす場合に値を書式設定」のボックスに数式を入力します。**
* `=$D2=”完了”`
* 解説:
* 適用範囲の左上隅はA2なので、数式もA2を基準に考えます。
* `$D2` とすることで、D列は固定（`$D`）し、行は相対的に参照（`2`）します。A2、B2、C2…F2のどのセルに書式を適用する際もD2セルを参照し、A3、B3…F3のどのセルに適用する際もD3セルを参照します。
* `=”完了”` は、D列の値が「完了」という文字列と一致するかどうかを評価します。
5. **「書式」ボタンをクリックし、塗りつぶしの色（例: 薄い緑
