期限を過ぎた未完了の行だけ、色を付ける

LWP | 期限を過ぎた未完了の行だけ、色を付ける

図解1|一行は一つの作業、色は対応の目印

基準日2026年10月8日の作業表。期限超過かつ未完了のT01とT06だけを色付けする。

図1|基準日2026年10月8日の作業表。期限超過かつ未完了のT01とT06だけを色付けする。

解説1|一行は一つの作業、色は対応の目印

作業表の一行は、一つの作業を表します。IDは作業を区別する印、期限は対応を終える予定日、状態は完了したかどうかです。今回は「基準日より期限が前で、まだ未完了の作業」を探します。単に古い日付を探すのではなく、対応が残っている行へ目を向けるための色です。

ココさんが確認するのは、次の6件です。基準日を固定すれば、読む日が変わっても同じ結果を照合できます。新しいシートを「作業確認」とし、A1:D7へ表のデータ部分を入力してください。列記号と行番号は位置を示す枠なので、セルには入れません。

入力例|シート「作業確認」のA1:D7

行

A列

B列

C列

D列

1

ID

作業

期限

状態

2

T01

見積回答

2026/10/06

未完了

3

T02

請求確認

2026/10/08

未完了

4

T03

資料送付

2026/10/05

完了

5

T04

日程調整

未完了

6

T05

在庫確認

2026/10/09

未完了

7

T06

報告書

2026/10/07

未完了

基準日の置き場所

同じシートのH1へ「基準日」、H2へ次の式を入れ、H2を日付表示にします。C列も日付として入力し、C5は空欄のままにします。日付として認識されない場合は、例えばC2へ=DATE(2026,10,6)と入力すれば、文字列と区別できます。

=DATE(2026,10,8)

DATEは年・月・日から日付の数値を作ります。H2の表示結果は2026/10/08です。H2を表の外へ分けることで、全作業に共通する判定基準を一か所で変更できます。各行へ同じ基準日を書き込む必要はありません。

読むポイント

期待する色はT01とT06の2行です。T02は期限が基準日と同じなので、まだ「過ぎた」に含めません。T03は古い期限でも完了しています。条件付き書式は表示を変える機能なので、作業名や状態を書き換えずに目印を付けられます。

図解2|期限と状態を別々に確かめる

期限が数値、基準日より前、未完了の3条件をANDで判定する。

図2|期限が数値、基準日より前、未完了の3条件をANDで判定する。

解説2|期限と状態を別々に確かめる

今回の判定は、期限の値、期限と基準日の前後関係、状態の3つから決まります。一つの条件だけでは目的の行を選べません。期限が古いという理由だけで色を付けると、T03のような完了済みの作業まで目立つからです。

関数の役割|3つの判定をANDへ渡す

条件付き書式の数式欄には、次の完全な式を使います。式は適用範囲の先頭行である2行目について書いています。

=AND(ISNUMBER($C2),$C2<$H$2,$D2="未完了")

式の部分

調べる内容

T01の途中結果

ISNUMBER($C2)

C2が数値か

TRUE

$C2<$H$2

C2が基準日より前か

TRUE

$D2="未完了"

D2が指定の文字列か

TRUE

AND(3つの判定)

すべてTRUEか

TRUE

ISNUMBERは値が数値ならTRUE、数値でなければFALSEを返します。スプレッドシートの日付は数値で扱われるため、空欄や文字列の期限を対象から外す確認に使えます。ただし、普通の数値でもTRUEです。入力された数値が妥当な期限かまで検査する関数ではありません。

ANDは、渡された条件がすべてTRUEのときだけTRUEを返します。T01では期限が数値、10月6日は10月8日より前、状態は未完了なので、最終結果もTRUEです。カスタム数式がTRUEになった対象セルへ、指定した書式が適用されます。

計算結果|一つ違えば色を付けない

T02では10月8日と基準日が同じです。「より前」を表す<の比較がFALSEになり、ANDもFALSEになります。T03では期限の比較はTRUEでも、状態の比較がFALSEです。期限と状態を別々に調べることで、色が付かない理由を説明できます。

読むポイント

「期限当日も含む」運用へ変えるなら<を<=へ変えますが、本記事は期限超過だけが対象です。また、「完了以外」を拾う式ではなく「未完了」と一致する値を選んでいます。空欄や「保留」を自動的に未完了とみなさないため、業務の状態区分に合わせて条件を決めてください。

図解3|列を固定し、行だけを変える

A列からD列の各セルが同じ行のC列・D列と固定H2を参照する対応表。

図3|A列からD列の各セルが同じ行のC列・D列と固定H2を参照する対応表。

解説3|列を固定し、行だけを変える

一つの行を色付けするには、その行のA列からD列までが同じ期限・状態を見て判定する必要があります。A2の条件がC2・D2を見ていても、B2ではD2・E2へ参照がずれると、行の途中で判定が変わってしまいます。そこで列だけを固定します。

関数の役割|固定する部分と動かす部分

参照

固定するもの

行を下へ進めたとき

$C2

期限のC列

$C3、$C4…

$D2

状態のD列

$D3、$D4…

$H$2

基準日の列と行

ずっと$H$2

$は、直後の列記号や行番号を固定する印です。$C2はC列を固定し、行番号の2は固定しません。対象セルが横へ移ってもC列を見て、下の行へ進めばC3、C4へ移ります。このように列と行の一方だけを固定する参照を、混合参照と呼びます。

H2は全作業で共通なので、$H$2と列・行の両方を固定します。期限や状態と同じように行だけ動かすと、3行目ではH3を見ることになり、共通の基準日ではなくなります。

計算結果|対象セルと参照先を対応させる

色を判定するセル

期限の参照先

状態の参照先

基準日

A2〜D2

C2

D2

H2

A3〜D3

C3

D3

H2

A7〜D7

C7

D7

H2

表の右へ進んでも参照する列が変わらず、次の作業へ進むと参照する行が変わります。この動きが「同じ作業の情報を使って、その作業の行全体を色付けする」という関係を作っています。

読むポイント

適用範囲A2:D100の起点はA2です。式の$C2と$D2も、この起点と同じ2行目にそろえます。もし$C3で始めると、2行目の色を次の3行目の期限で決めてしまいます。逆に$C$2へ変えると、すべての行がC2を見るため、作業ごとの期限を判定できません。別の表へ移すときは、期限列・状態列・開始行・共通セルの4点を置き換えます。

図解4|カスタム数式を行全体へ設定する

条件付き書式の適用範囲A2:D100とカスタム数式の設定図。

図4|条件付き書式の適用範囲A2:D100とカスタム数式の設定図。

解説4|カスタム数式を行全体へ設定する

条件付き書式の設定には、色を変える場所と、色を変える理由の二つがあります。「範囲に適用」が場所で、「カスタム数式」が理由です。理由にC列を使っていても、場所をA列からD列へ広げれば、作業の行全体へ書式を付けられます。

入力例|範囲と判定式

同じシートのH2に基準日2026/10/08が入り、A1:D7の入力が済んでいる状態で設定します。A1の見出しは判定しないため、対象の開始はA2です。

設定するもの

今回の指定

意味

範囲に適用

A2:D100

A〜D列の2〜100行目

条件の種類

カスタム数式

数式がTRUEなら書式適用

書式

淡い赤の塗りつぶし

対応が必要な行の目印

操作|数式は条件付き書式の欄へ

A2:D100を選び、[表示形式]→[条件付き書式]を開きます。[範囲に適用]を確認し、[セルの書式設定の条件]で[カスタム数式]を選びます。数式欄へ次の式を入れ、塗りつぶし色を指定して[完了]を押します。

=AND(ISNUMBER($C2),$C2<$H$2,$D2="未完了")

この式はA2などのデータセルへ入力するものではありません。作業表の中身はそのままに、サイドパネルのルールへ登録します。日付のC2や状態のD2を上書きしないよう、入力場所を区別してください。

計算結果

条件を満たす2行目と7行目では、A列のIDからD列の状態まで同じ淡い赤になります。C列だけが色付いた場合は、式を直す前に適用範囲を確認します。C2:C100と指定していれば、正しい判定でも期限セルだけが対象だからです。

読むポイント

新しい空のシートで、今回のルールを1件だけ作ると結果を確かめやすくなります。既存の作業表では、すでに別の条件付き書式や手動の色がある場合もあります。今回の式がTRUEかという確認と、最終的に見える色の確認を分けると、数式と書式設定のどちらを調べるべきか判断できます。

図解5|色とTRUE/FALSEを照合する

E2からE7の検証結果はTRUE、FALSE、FALSE、FALSE、FALSE、TRUE。

図5|E2からE7の検証結果はTRUE、FALSE、FALSE、FALSE、FALSE、TRUE。

解説5|色とTRUE/FALSEを照合する

表示された色だけでは、条件そのものの成否を見落とすことがあります。検証用のE列へ同じ式を入れると、各作業がTRUEかFALSEかを値で確かめられます。E列は確認に使う列で、条件付き書式の対象A2:D100には含めません。

入力例|確認用の式とコピー先

E1へ「確認用判定」、E2へ次の式を入れ、E7まで下へコピーします。H2は2026/10/08のままです。$C2・$D2は下の行へ動き、$H$2は固定されます。

=AND(ISNUMBER($C2),$C2<$H$2,$D2="未完了")

計算結果|E1:E7

行

E列

1

確認用判定

2

TRUE

3

FALSE

4

FALSE

5

FALSE

6

FALSE

7

TRUE

作業ID

判定

色が付く/付かない理由

T01・T06

TRUE

期限超過かつ未完了

T02

FALSE

期限が基準日と同じ

T03

FALSE

状態が完了

T04

FALSE

期限が空欄

T05

FALSE

期限が基準日より後

TRUEは2件で、色が付くのもT01とT06の2行です。色を付けたセルの内容がTRUEへ変わるわけではありません。A〜D列には元の作業情報を残し、別のE列に検証結果を表示しています。

読むポイント

E列の結果が想定と違う場合は、その行のC列・D列とH2を調べます。例えばT01だけFALSEなら、C2が日付の数値になっているか、D2の文字列に余分な空白がないかを確認します。ISNUMBER(C2)を空いているセルで試せば、数値かどうかを切り分けられます。期限が文字列でも、見た目だけは日付と似ている場合があります。

E列がTRUEなのに行の色が違う場合は、適用範囲、条件付き書式に登録した式、他のルールを確認します。検証が済んだE列は残しても削除してもかまいません。条件付き書式はE列を参照していないので、その列に依存する仕組みにはなっていません。

図解6|追加行と基準日の変更を確かめる

基準日を10月9日に変えるとT02が加わる。追加行と適用範囲も確認する。

図6|基準日を10月9日に変えるとT02が加わる。追加行と適用範囲も確認する。

解説6|追加行と基準日の変更を確かめる

運用中に変わるのは、作業の行数、状態、基準日です。条件付き書式のルールを毎回作り直す代わりに、表の入力と共通セルを更新します。ただし、追加した作業が適用範囲の外なら、その行は判定されません。

入力後|A8:D8へ作業を追加

行

A列

B列

C列

D列

8

T07

追加確認

2026/10/07

未完了

8行目はA2:D100に含まれるため、H2が2026/10/08なら色が付きます。101行目以降へ作業を入れた場合は、適用範囲を必要な末尾行まで広げてください。対象の列を増やす場合も、色を付けたい列まで範囲を広げます。

計算結果|基準日だけを変える

次の比較は、追加前の6件についての結果です。H2を10月9日へ変えると、前日までは期限当日だったT02が期限超過に変わります。10月9日が期限のT05は、まだ対象になりません。

H2の表示

色が付く作業ID

元の6件での行数

2026/10/08

T01・T06

2行

2026/10/09

T01・T02・T06

3行

関数の役割|固定日と今日を使い分ける

固定した例を再現するときはH2に=DATE(2026,10,8)、今日を基準にするときは次の式を使います。TODAYは時刻を含まない現在の日付を、シートが再計算した時点で返します。常に秒単位で更新する時計として扱わず、日が変わった後は基準日の表示も確かめます。

=TODAY()

読むポイント

期限は日付の数値、状態は「未完了」「完了」など表記を統一し、追加行を範囲に含めます。完了へ変更した行は条件がFALSEになり、今回のルールの色が外れます。色を付けるだけでは作業自体は完了しないので、確認後の対応は状態の更新につなげます。

出典メモ

書籍『ちゃんとわかる Googleスプレッドシート』V044の7.3.3・7.3.5・7.12.3、8.1.3・8.1.5を参照し、例題を再構成しました。仕様はGoogle公式の条件付き書式、AND、ISNUMBER、DATE、TODAYで確認しています。確認日:2026年10月8日。

利用について

Copyright © 2026 LWP 山中 一弘

本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。