Excelで「A列が特定の値で、同じ行のD列が空ならD列へ色を付ける」には、D列のデータ範囲を選び、条件付き書式の数式ルールを使います。見出しが1行目、データが2行目からなら、適用先をD2:D1000、数式を=AND($A2="提出済",ISBLANK($D2))にするのが基本です。
ただし、Excelで「空白に見えるセル」には複数の状態があります。本当に何も入っていないセル、数式が空文字列を返しているセル、半角スペースだけのセルでは、使う判定式が異なります。まず何を未入力として扱うかを決めると、色が付かない、意図しない行まで色が付くといった問題を防げます。
空白の種類に合う数式を選ぶ
| D列の状態 | 見た目 | ISBLANK | D2=””／LEN(D2)=0 | 適した判定 |
|---|---|---|---|---|
| 完全な空セル | 空白 | TRUE | TRUE | ISBLANKまたは=”” |
| 数式が””を返す | 空白 | FALSE | TRUE | =””またはLEN=0 |
| 半角スペースだけ | 空白に見える | FALSE | FALSE | LEN(TRIM())=0 |
| 全角スペースや改行なしスペース | 空白に見える | FALSE | FALSE | 元データのクレンジングを優先 |
| 0 | 0 | FALSE | FALSE | 入力済みとして扱う |
ISBLANKは、セルにデータがない場合だけTRUEになります。D2に=IF(B2="","",B2)のような数式が入っていると、結果が空白に見えてもセルには数式があるため、ISBLANKはFALSEです。数式の空文字も未入力として扱いたい場合は、$D2=""またはLEN($D2)=0を使います。
TRIMは先頭と末尾の通常の半角スペースを除き、単語間の連続した半角スペースを1個にします。ただし、Microsoftの公式説明では、TRIMが対象とするのは7ビットASCIIのスペース文字、文字コード32です。Webページから貼り付けた改行なしスペースや、日本語入力の全角スペースをすべて除ける関数ではありません。
完成例：A列が「提出済」でD列が完全な空セルなら黄色
例として、A列に進捗、D列に確認メモがあり、1行目を見出し、2行目以降をデータとします。A列が「提出済」なのにD列が本当に未入力の行だけ、D列を黄色にします。
- D2:D1000を選択します。見出しのD1を含めず、実際に色を付けたいデータ範囲を先に選びます。
- ［ホーム］→［条件付き書式］→［新しいルール］を開きます。
- ［数式を使用して、書式設定するセルを決定］を選びます。別列のA列と、色を付けるD列を同時に判定するためです。
- 数式を入力します。
=AND($A2="提出済",ISBLANK($D2))- ［書式］を選び、塗りつぶし色を設定します。文字色だけでなく、印刷や色覚差も考えて濃すぎない背景と読みやすい文字色を選びます。
- OKでルールを保存します。「提出済／D列空」「提出済／D列入力済み」「未提出／D列空」の3パターンを作り、最初のパターンだけ色が付くことを確認します。
数式の$は何を固定しているか
| 参照 | 固定される部分 | 下の行へ評価したとき | 今回の用途 |
|---|---|---|---|
| $A2 | A列 | $A3、$A4へ変化 | 各行の進捗をA列で判定 |
| $D2 | D列 | $D3、$D4へ変化 | 各行のメモ欄を判定 |
| $A$2 | A列と2行目 | 常にA2 | 全行で1個の設定値を見るとき |
| A$2 | 2行目 | 列は動き、行は2のまま | 横方向へ適用する場合 |
条件付き書式の数式は、適用先の左上セルを基準に書きます。適用先がD2:D1000なら、数式の行番号も2から始めます。D列の各行を評価したいのに$D$2とすると、どの行でもD2だけを見続けるため、D2の状態によって全範囲が同じ色になることがあります。
逆に、設定値をE1に置き、A列がその値と同じときに判定したいなら、E1は固定します。数式は=AND($A2=$E$1,ISBLANK($D2))です。行ごとに動かす参照と、全行で共通の設定値を分けて考えてください。
数式が空文字を返すセルも色付けする
D列に数式が入っており、条件によって空文字列を返す場合はISBLANKでは色が付きません。見た目が空なら未入力として扱う要件なら、次のどちらかを使います。
=AND($A2="提出済",$D2="")
=AND($A2="提出済",LEN($D2)=0)どちらも、完全な空セルと空文字列をまとめてTRUEにできます。単純な未入力チェックなら$D2=""の方が読みやすく、文字数という考え方を他の条件へ広げたい場合はLENを使えます。チームで管理するブックでは、同じ意味の式を混在させず、どちらかに統一すると保守しやすくなります。
半角スペースだけのセルも未入力にする
手入力されたD列に、誤って半角スペースだけが入ることがあります。通常の半角スペースだけを未入力相当として扱うなら、次の式を使えます。
=AND($A2="提出済",LEN(TRIM($D2))=0)ただし、この式を「すべての空白文字に対応」と説明してはいけません。TRIMだけでは改行なしスペースや全角スペースが残ります。外部システムやWebから貼り付けたデータなら、条件付き書式へ複雑な置換式を重ねるより、Power Query、入力規則、データの置換、元システム側の整形などでデータを正規化する方が安全です。
複数の進捗を条件にする
A列が「提出済」または「確認済」で、D列が空なら色を付ける場合は、ANDの中にORを入れます。
=AND(OR($A2="提出済",$A2="確認済"),$D2="")条件値が増えるたびに式へ文字列を追加すると、変更漏れが起こりやすくなります。条件が数個を超えるなら、別シートに対象ステータス一覧を置き、COUNTIFやMATCHで一覧に含まれるかを判定する設計を検討します。Microsoftの条件付き書式は他のワークシートへの参照も入力できますが、ブックを共有する場合は、設定シート名や範囲変更でルールが壊れないよう管理してください。
列全体ではなく必要範囲へ適用する
適用先を=$D:$Dにすると、新しい行も自動的に対象になりますが、見出しを含む100万行以上へルールを持たせることになります。単純なルール1個では直ちに問題にならなくても、複数ルール、参照関数、共有ブックが重なると、計算や管理の負担が増えます。
| データの増え方 | 推奨する適用先 | 理由 |
|---|---|---|
| 最大行数が決まっている | $D$2:$D$1000 | 不要領域と見出しを除外できる |
| 行が随時増える一覧 | Excelテーブルのデータ列 | 行追加に追従しやすい |
| 少数行の一時表 | 現在の実データ範囲 | ルールを読みやすく保てる |
| 列全体が本当に必要 | $D:$D | 見出し除外と性能をテストして使う |
行が増える帳票では、範囲をExcelテーブルへ変換すると、列名を使った管理や行追加がしやすくなります。ただし、既存の条件付き書式をテーブル化した際は、［条件付き書式］→［ルールの管理］で適用先が意図どおり更新されたか確認してください。
実務で使える3つの設定例
例1：要対応なのに期限が未入力なら赤
A列が「要対応」で、D列の期限が空ならD列を赤くします。適用先をD2:D1000、数式を次のようにします。日付が0ではなく本当に空か、数式の空文字も未入力に含めるかを決め、この例では空文字も含めます。
=AND($A2="要対応",$D2="")例2：完了行なのに確認者が空なら黄色
B列が「完了」で、E列の確認者が本当に未入力のときだけE列を黄色にします。適用先をE2:E1000とし、数式は=AND($B2="完了",ISBLANK($E2))です。E列に数式を入れる設計へ変えた場合は、ISBLANKのままでよいか見直します。
例3：行全体を色付けする
A列が「保留」でD列が空のとき、A列からF列まで行全体を灰色にするなら、適用先をA2:F1000に変更し、数式を=AND($A2="保留",$D2="")にします。適用先の左上はA2ですが、判定列は$Aと$Dで固定されるため、同じ行のA列とD列を見ながらA～F列へ同じ書式が付きます。
行全体へ色を付ける場合は、既存の入力セルの色や期限超過ルールを上書きしないかを確認します。状態を強く示したいからといって複数の濃い色を重ねると判別しづらくなるため、優先順位を決め、必要なら文字列やアイコンも併用してください。
色が付かないときの確認順
| 症状 | 主な原因 | 確認方法 |
|---|---|---|
| どの行にも色が付かない | 数式がFALSE、適用先と先頭行が不一致 | D2だけで条件を作り、TRUEになるテスト値を入れる |
| 1行ずれて色が付く | 適用先はD2なのに数式が1行目基準 | 適用先左上と数式の行番号を合わせる |
| 全行が同じ色になる | $D$2のように行まで固定 | $D2へ修正する |
| 空白に見えるのに色が付かない | 空文字、スペース、非表示文字 | ISBLANKとLENの結果を補助列で確認する |
| 一部だけ別の色になる | 他の条件付き書式と競合 | ルールの優先順位と適用先を確認する |
| 式が正しいのに反映されない | 参照先の数式エラー | 参照セルの#N/Aや#VALUE!を確認する |
適用先と先頭行を確認する
［ホーム］→［条件付き書式］→［ルールの管理］を開き、表示対象を現在の選択範囲またはこのワークシートに切り替えます。適用先が=$D$2:$D$1000なら、数式は2行目を基準にします。コピーや行挿入の後は、適用先が複数の細切れ範囲に増えていないかも確認します。
他のルールとの優先順位を確認する
同じD列に「期限超過は赤」「提出済かつ空欄は黄」など複数ルールがあると、優先順位と書式の種類によって見え方が変わります。ルールを上から順に読み、同じセルで複数条件がTRUEになるテストを行います。［条件を満たす場合は停止］は、下のルールを評価させたくない場合だけ使います。
式をワークシート上でテストする
条件付き書式の画面では結果が見えにくいため、空いている補助列に同じ判定式を一時的に入力します。TRUEなら色が付く条件、FALSEなら付かない条件です。意図と違う場合は、A列の文字に前後スペースがないか、D列が空文字か、本当に2行目を参照しているかを確認します。確認後、補助列は削除できます。
設定を共有する前には、入力済み、完全な空セル、空文字、半角スペース、条件外の行を最低1件ずつ用意し、期待するセルだけに書式が付くことを確認します。条件付き書式は見た目だけで誤りに気付きにくいため、この小さなテスト表を残しておくと、列追加や判定文字の変更後にも短時間で再検証できます。
運用時の注意
- 色だけで状態を伝えず、「未入力」「要確認」などの文字や別列も併用します。
- 判定文字列を変更する可能性が高い場合は、数式へ直書きせず設定セルを参照します。
- 外部データの空白文字は条件付き書式で隠さず、取り込み時にクレンジングします。
- ルールの目的、適用先、判定式は別シートの仕様欄へ記録します。未確認の「ルールコメント機能」を前提にしません。
- ブックを古い形式へ保存する場合は、条件付き書式の互換性チェック結果を確認します。
よくある質問
セルのIF関数だけで背景色を変えられますか？
通常のワークシート関数は値を返すもので、セルの塗りつぶし色を直接変更しません。背景色を変える条件は条件付き書式へ設定します。VBAを使う方法もありますが、この用途なら条件付き書式の方が安全で共有しやすい方法です。
ISBLANKとD2=””はどちらを使うべきですか？
本当に入力されていないセルだけを対象にするならISBLANK、数式が空文字を返すセルも未入力として扱うならD2=""を使います。どちらが正しいかは、シートの業務要件で決まります。
TRIMを使えば全角スペースも消えますか？
標準のTRIMは通常の半角スペースを対象にし、すべてのUnicode空白を削除するわけではありません。全角スペースや改行なしスペースを含むデータは、元データの置換やPower Queryなどで整える方法を検討します。
行を追加しても自動で色付けしたい場合は？
データ範囲をExcelテーブルにすると、追加行へ書式が引き継がれやすくなります。列全体参照でも追加行を対象にできますが、見出しと不要行を含みます。ルール数とデータ量を考え、テーブルまたは十分な有限範囲を優先します。
Excel for the webでも同じ考え方ですか？
条件付き書式の数式と相対・絶対参照の考え方は共通です。ただし、ルール管理画面の配置や利用できる操作はデスクトップ版と異なる場合があります。画面名を無理に合わせず、［条件付き書式］の数式ルールと適用範囲を確認してください。
公式情報
- Microsoft Support：Use conditional formatting to highlight information in Excel
- Microsoft Support：Using IF to check if a cell is blank
- Microsoft Support：Switch between relative, absolute, and mixed references
- Microsoft Support：TRIM function
まとめ
別セルの値と空白を組み合わせて色を付けるときは、適用先の左上セルを基準に条件付き書式の数式を書きます。本当の空セルだけならISBLANK、数式の空文字も含めるなら$D2=""、通常の半角スペースだけも含めるならLENとTRIMを使います。適用先はD2:D1000やExcelテーブルなど必要範囲に限定し、$A2と$D2の列固定、他ルールとの優先順位、TRIMが扱えない空白文字まで確認すれば、意図しない色付けを防げます。
コメント