LWP | 期限を過ぎた未完了の行だけ、色を付ける
図解1|一行は一つの作業、色は対応の目印

図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|期限と状態を別々に確かめる

図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|列を固定し、行だけを変える

図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|カスタム数式を行全体へ設定する

図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を照合する

図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|追加行と基準日の変更を確かめる

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