Excelでタスク管理表を作っていると、「状況欄に『完了』を含む行だけ色を付けたい」ということがあります。
状況が「完了」だけなら、条件付き書式の数式は簡単です。しかし、「確認完了」「処理完了」のように前後に文字が付く場合は、完全一致では判定できません。
先に結論をお伝えすると、特定の文字を含む行に色を付ける場合は、SEARCH関数とISNUMBER関数を組み合わせます。 状況がD列にある場合の基本式は次のとおりです。
=ISNUMBER(SEARCH("完了",$D2))
ただし、この式では「未完了」にも色が付きます。この記事では、完全一致と部分一致の使い分けから、意図しない文字まで一致する場合の対処まで順番に解説します。
1. 完全一致と部分一致は使い分ける
（1）「完了」だけを対象にするなら完全一致
セルの内容が「完了」と等しい場合だけ色を付ける数式は、次のとおりです。
=$D2="完了"
この数式では、「完了」には色が付きますが、「確認完了」や「処理完了」には付きません。
入力する言葉が「未対応」「対応中」「完了」のように決まっている表では、完全一致が安全です。想定外の言葉を拾いにくく、ルールの意味も分かりやすくなります。
条件付き書式で行全体を色付けする基本や$D2の意味は、Excelの条件付き書式で行全体に色を付ける方法で詳しく解説しています。
（2）前後に別の文字が付くなら部分一致
「確認完了」「処理完了」「完了（連絡済み）」など、セル内のどこかに「完了」があれば色を付けたい場合は、部分一致を使います。
=ISNUMBER(SEARCH("完了",$D2))
この数式は「完了」という文字がD列のセル内にあるかを調べます。
完全一致と部分一致は、似ていますが対象になる文字が異なります。
| D列の内容 | 完全一致 | 部分一致 |
|---|---|---|
| 完了 | ○ | ○ |
| 確認完了 | × | ○ |
| 処理完了（連絡済み） | × | ○ |
| 未完了 | × | ○ |
最後の「未完了」まで部分一致になる点が重要です。検索する文字を含んでいる以上、Excelにとっては正しい判定です。意図と違う場合は、後ほど紹介する除外条件を追加します。
2. 特定の文字を含む行全体に色を付ける
ここでは、A列からD列までのタスク管理表を例にします。
| 列 | 項目 |
|---|---|
| A列 | 管理番号 |
| B列 | 作業内容 |
| C列 | 担当者 |
| D列 | 状況 |
状況欄に「完了」を含む行全体へ、薄い緑色を付けます。
（1）色を付ける表の範囲を選択する
見出しが1行目にあり、データが2行目から100行目まである場合は、次の範囲を選択します。
A2:D100
D列だけではなく、色を付けたい行全体を最初に選ぶことがポイントです。
（2）数式を使うルールを開く
「ホーム」タブから「条件付き書式」→「新しいルール」を開きます。
続いて、「数式を使用して、書式設定するセルを決定」を選択します。Excelのバージョンによって、項目名が少し異なる場合があります。
（3）部分一致の数式を入力する
数式の入力欄に、次の式を入力します。
=ISNUMBER(SEARCH("完了",$D2))
（4）塗りつぶし色を設定する
「書式」をクリックし、塗りつぶしから薄い緑色などを選びます。最後に「OK」をクリックしてルールを確定します。
D列に「確認完了」や「処理完了」と入力すると、その行のA列からD列までに色が付きます。
3. SEARCH関数とISNUMBER関数の役割
（1）SEARCH関数で文字の位置を調べる
SEARCH関数は、指定した文字がセル内の何文字目にあるかを返します。
=SEARCH("完了",D2)
D2が「確認完了」なら、「完了」は3文字目から始まるため、結果は数値の3になります。
一方、D2に「完了」が含まれていない場合、SEARCH関数は#VALUE!エラーを返します。
（2）ISNUMBER関数でTRUE・FALSEに変える
条件付き書式の数式は、条件に合うときにTRUE、合わないときにFALSEとなる必要があります。
そこで、SEARCH関数の結果が数値かどうかをISNUMBER関数で確認します。
=ISNUMBER(SEARCH("完了",$D2))
「完了」が見つかればSEARCH関数は数値を返すため、ISNUMBER関数の結果はTRUEです。見つからなければ、結果はFALSEになります。
条件付き書式はTRUEになった行だけに、指定した色を付けます。
4. COUNTIF関数とワイルドカードでも判定できる
部分一致は、COUNTIF関数でも設定できます。
=COUNTIF($D2,"*完了*")>0
*は、任意の文字が0文字以上あることを表すワイルドカードです。"*完了*"は、「前後にどのような文字があっても、途中に『完了』を含む」という条件になります。
COUNTIF関数が1以上を返した場合、>0の条件を満たすため行に色が付きます。
SEARCH関数を使う式とCOUNTIF関数を使う式は、どちらも部分一致に使えます。
| 数式 | 特徴 |
|---|---|
=ISNUMBER(SEARCH("完了",$D2)) | 文字を探す処理が数式から読み取りやすい |
=COUNTIF($D2,"*完了*")>0 | *を使った部分一致に慣れている場合に分かりやすい |
COUNTIF関数は、条件に合うセルの件数を数えるときにも使える関数です。ここでは、判定結果が1件以上なら色を付ける仕組みとして利用しています。
5. 「未完了」まで色が付く場合の対処
（1）入力候補が決まっているなら完全一致にする
状況欄へ「未対応」「対応中」「完了」のいずれかを入力する運用なら、部分一致を使う必要はありません。
=$D2="完了"
完全一致にすれば、「未完了」や「確認完了」は対象外になります。
ステータスを固定できる表では、入力規則のドロップダウンリストを使うと表記の揺れも防げます。設定方法は、入力ミスをゼロに！データの入力規則でドロップダウンリストを作る方法で解説しています。
（2）部分一致を使いながら「未完了」を除外する
「確認完了」や「処理完了」は対象にしつつ、「未完了」だけ除外したい場合は、次の数式を使います。
=AND(ISNUMBER(SEARCH("完了",$D2)),ISERROR(SEARCH("未完了",$D2)))
この数式は、次の2つを確認しています。
- D列に「完了」が含まれている
- D列に「未完了」は含まれていない
2つの条件を両方満たす場合だけ、行に色が付きます。
除外したい表現が複数ある場合は数式が長くなります。入力するステータスを整理できるなら、複雑な除外式を増やすより、完全一致とドロップダウンリストを使う方が管理しやすくなります。
6. 空白・余分なスペース・英字の違いに注意する
（1）空白を条件から明示的に除外する
SEARCH関数とISNUMBER関数の組み合わせでは、空白セルは通常FALSEになります。それでも、数式の意図を明確にしたい場合は「空白ではない」という条件を追加できます。
=AND($D2<>"",ISNUMBER(SEARCH("完了",$D2)))
$D2<>""は、D列が空白ではないという意味です。
日付や数値の条件で空白行まで色が付く場合は、Excelの条件付き書式で空白セルまで色が付く原因も確認してください。
（2）完全一致では前後のスペースも判定に影響する
D2に完了 のような余分な半角スペースがあると、次の完全一致は成立しません。
=$D2="完了"
半角スペースを取り除いて判定する場合は、TRIM関数を使います。
=TRIM($D2)="完了"
TRIM関数は、文字の前後にある通常の半角スペースを整えます。全角スペースや見えない制御文字が混ざっている場合は、元データを修正する方が安全です。
（3）英字の大文字と小文字を区別する場合
SEARCH関数は、英字の大文字と小文字を区別しません。
たとえばdoneを検索すると、DONEやDoneも一致します。大文字と小文字を区別したい場合は、SEARCH関数の代わりにFIND関数を使います。
=ISNUMBER(FIND("DONE",$D2))
日本語の「完了」を検索する今回の例では、大文字・小文字の違いを考える必要はありません。
7. 条件付き書式が正しく反映されないときの確認点
（1）数式の行番号を適用範囲の先頭行に合わせる
適用範囲がA2:D100なら、数式も2行目から始めます。
=ISNUMBER(SEARCH("完了",$D2))
適用範囲がA3:D100なら、数式の行番号も$D3にします。ここがずれると、1つ上または下の行を見て色が付くことがあります。
（2）列だけを固定する
行全体の色付けでは、判定する列をD列に固定し、行番号は動かします。
$D2
$D$2にすると、すべての行がD2だけを確認します。D$2でも2行目が固定されるため、行ごとの判定になりません。
（3）適用先がD列だけになっていないか確認する
数式が正しくても、条件付き書式の「適用先」が$D$2:$D$100ならD列にしか色が付きません。
行全体に色を付ける場合は、適用先を次のようにします。
=$A$2:$D$100
（4）ほかのルールと重なっていないか確認する
同じ範囲に複数の条件付き書式があると、ルールの優先順位によって見た目が変わることがあります。
「条件付き書式」→「ルールの管理」を開き、次を確認します。
- 同じ範囲に古いルールが残っていないか
- ルールの適用先が意図した範囲か
- どのルールが上にあるか
- 「条件を満たす場合は停止」の設定が影響していないか
数値を複数の範囲に分ける場合の優先順位は、Excelの条件付き書式で数値を以上・以下に色分けする方法でも紹介しています。
8. まとめ
特定の文字を含むセルや行に色を付けるときは、入力される言葉に合わせて完全一致と部分一致を使い分けます。
今回のポイントは次のとおりです。
- セルが「完了」と等しい場合だけなら
=$D2="完了"を使う - 前後に別の文字があっても判定するなら
=ISNUMBER(SEARCH("完了",$D2))を使う - COUNTIF関数と
*でも部分一致を設定できる - 「未完了」にも「完了」が含まれるため、部分一致では色が付く
- 「未完了」を除外する場合はAND関数で除外条件を追加する
- 行全体に色を付けるときは、適用範囲と
$D2の行番号を合わせる
状況欄の入力候補が決まっている場合は、完全一致が分かりやすく安全です。自由入力の文章から特定の言葉を探す場合に、SEARCH関数やCOUNTIF関数による部分一致を使いましょう。
