わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】日付の比較、期間の経過と期限が近付いていることの判定、色を自動で変える設定

Excelで、期限や納期が近付いている、もしくは指定している日付や日数を経過してしまったということを自動的に知らせるには、Excel関数を用いて表示する方法と、条件付き書式でセルの色を変える方法があります。

自動的に知らせてもらうためには、日付同士を比較する計算式を入力する必要があります。なぜ計算式を入力するかというとExcelは「表計算ソフト」だからです。特に、本日の日付TODAY()を基準とする場合は、TODAYを用いて式を入力しなければなりません。

色を付けることを考える前に、まずは、日付の比較演算の方法を習得し、TRUE・FALSEを求められるように練習しましょう

そこで、今回は、日付同士の比較演算、日数が過ぎた場合や期限が近付いていることの判定方法、条件付き書式によって特定の日付の色を変える方法について出題します。なお、月単位、年単位の場合については別の記事で解説します。

目次

1.日付の比較演算の基本

(1)日付のシリアル値の大小関係

問題

Excelで「2021/12/31」と「2022/1/1」ではどちらのほうが大きいか。 

f:id:waenavi:20200427123647j:plain

 

解説

大小を比較するときには比較演算を入力します(参考:【Excel】IF関数が使えない原因は「比較演算」の練習不足である)。「=A1<A2」と入力するとTRUEとなります。これはA2のほうが大きいことを表しています。

  • =A1<A2

f:id:waenavi:20200506130858j:plain

 

逆に「=A1>A2」と入力するとFALSEとなります。これはA1のほうが大きくないことを表しています。いずれにしても大晦日より翌年の元日のほうが大きいです。

f:id:waenavi:20200506130931j:plain

 

Excelで日付を入力すると自動的にシリアル値として保存されます(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。日付のシリアル値は1900年1月1日からの経過日数として整数値で表されるため、古い日付は小さい数値、新しい日付は大きい数値を入力したのと同じです。したがって、大晦日より翌日の元日のほうが大きいのです。

f:id:waenavi:20200506132951j:plain

 

引き算をすると1になります(表示形式=標準)。シリアル値は経過日数なので単位は「1日」です。引き算をして1になったということは1日差であることを表しています。

  • =A2-A1

f:id:waenavi:20200506133116j:plain

 

(2)日単位・週単位の比較

問題

セルA1に入力した日付を基準として、セルA2に入力した日付が10日以内であればTRUEと表示しなさい。また、1週間以内であればTRUEと表示しなさい。

f:id:waenavi:20200506133216j:plain

 

解説

セルA1に入力した日付を基準として10日以内ということは、10日を加算してその日付以下であればよいということです。TRUEなので10日以内であるということです。

  • =A2<=A1+10

f:id:waenavi:20200506142956j:plain

 

1週間は7日なので、7を加算します。FALSEなので1週間以内ではない、つまり1週間を超えて経過しているということになります。

  • =A2<=A1+7

f:id:waenavi:20200506143024j:plain

 

2.本日との比較

(1)過去の日付・将来の日付

問題

A列に入力した日付が過去の日付であればTRUEと表示しなさい。また、今日以降の日付であればTRUEと表示しなさい。

f:id:waenavi:20200506150212j:plain

 

解説

過去<未来なので、A列の日付<TODAY()であればA列の日付は過去の日付であることが分かります。

  • =A1<TODAY()

f:id:waenavi:20200506150636j:plain

 

また、将来の日付を判定する場合は不等号の向きが逆になります。なお、今日の日付を含める場合はイコールが必要です。

  • =A1>=TODAY()

f:id:waenavi:20200506150712j:plain

 

(2)日数経過の判定

問題

A列の日付を基準として、現時点で10日以上経過していればTRUEと表示しなさい。

f:id:waenavi:20200506151850j:plain

 

解説

A列の日付から見て、すでに10日以上が経過しているということは、A列の日付とTODAYの間に10日以上の空きがあるということです。

f:id:waenavi:20200506151740j:plain

 

つまり、A列の日付に10日を加算しても、TODAYのほうが大きいということです。

  • =A3+10<=TODAY()

f:id:waenavi:20200506151922j:plain

 

(3)期限が近づいていることの判定

問題

本日の日付を基準として、A列の日付が10日以内であればTRUEと表示しなさい。

f:id:waenavi:20200506152100j:plain

 

解説

本日から10日以内であるということは、A列の日付が10日後より前ということです。

f:id:waenavi:20200506152332j:plain

 

つまり、A列の日付が、TODAYの10日後より小さいということです。

  • =A3<=TODAY()+10

f:id:waenavi:20200506152514j:plain

 

(4)AND条件

問題

本日の日付を基準として、A列の日付が2週間前から今日までの日付であればTRUEと表示しなさい。

f:id:waenavi:20200506152825j:plain

 

解説

2週間前から今日までということは、14日前より大きく今日より小さいということです(14日前以上今日以下)。

f:id:waenavi:20200506153321j:plain

 

2つの条件を両方満たすのでAND関数を使います。

  • =AND(A3>=TODAY()-14,A3<=TODAY())

f:id:waenavi:20200506152936j:plain

 

3.IF関数で判定結果を表示する

(1)今日を過ぎていたら期限切れ

問題

本日の日付を基準として、A列の日付が過去の日付であれば「期限切れ」と表示し、そうでなければ空白にしなさい。

f:id:waenavi:20200506153630j:plain

 

解説

問題文からIF関数を使うのは明らかですが、IF関数の問題ではいきなりIF関数を考えるのではなく、判定条件だけを考えて、TRUE/FALSEを表示することを考えるべきです。
本日の日付はTODAY()関数であり、Excelのセルに入力している日付が今日を過ぎるということは、過去の日付になっているということです。前述のとおり、過去の日付の判定は、「A列の日付<TODAY()」です。

  • =A3<TODAY()

f:id:waenavi:20200506153717j:plain

 

過去の日付がTRUEになります。日付の大小比較ができれば、IF関数でTRUE/FALSEを別の文字列に置き換えることができます。

  • =IF(A3<TODAY(),"期限切れ","")

f:id:waenavi:20200506153809j:plain

 

(2)日数が経過していたら期限切れ

問題

A列の日付を基準として、現時点で2週間を超えて経過していれば「期限切れ」と表示し、そうでなければ空白にしなさい。

f:id:waenavi:20200506154038j:plain

 

解説

「2週間を超えて経過」していることを判定しようとしています。A列の日付に14日を加算(経過)して、さらにTODAYのほうが大きいということです。

  • =A3+14<TODAY()

f:id:waenavi:20200506154132j:plain

 

IF関数でTRUE/FALSEを別の文字列に置き換えます。

  • =IF(A3+14<TODAY(),"期限切れ","")

f:id:waenavi:20200506154354j:plain

 

(3)期限がせまっている

問題

A列の日付が本日から3日以内の日付であれば「至急」と表示し、そうでなければ空白にしなさい。

f:id:waenavi:20200506155219j:plain

 

解説

本日から3日以内ということは、本日の3日後を含めて、3日後の日付より小さいということです。

  • =A3<=TODAY()+3

f:id:waenavi:20200506155316j:plain

 

IF関数で「至急」と表示します。

  • =IF(A3<=TODAY()+3,"至急","")

f:id:waenavi:20200506155404j:plain

 

もし、本日以降という条件を追加するのであればAND関数を使います。

  • =IF(AND(A3>=TODAY(),A3<=TODAY()+3),"至急","")

f:id:waenavi:20200506155438j:plain

 

(4)COUNTIF関数を使ってカウントする

問題

A列の日付のうち過去の日付をカウントしなさい。

f:id:waenavi:20200506155941j:plain

 

解説

条件付きでカウントするのはCOUNTIF関数です。COUNTIF関数の検索条件で比較演算子を用いる場合は、ダブルクォーテーションで囲み、&で連結します。これで、過去の日付をカウントすることができます。ちなみに、COUNTIF関数の場合、空白のセルは除外されます。

  • =COUNTIF(A3:A12,"<"&TODAY())

f:id:waenavi:20200506160223j:plain

 

4.条件付き書式で色を付ける

(1)今日と明日の日付の色を変える

問題

セルA3以降の日付のうち、今日の日付の塗りつぶしの色を青色系にしなさい。また、明日の日付を緑色系にしなさい。

f:id:waenavi:20200506160703j:plain

 

解説

今日の日付に合わせて塗りつぶしの色を変更する場合、条件付き書式を使います。A列の日付を選択して、条件付き書式の新しいルールをクリックします。

f:id:waenavi:20200506213527j:plain

 

「数式を使用して、書式設定するセルを決定」を選び、「=A3=TODAY()」と入力します。この数式は小文字でも構いません。書式のボタンを押し、書式を設定します。

f:id:waenavi:20200506213629j:plain

 

塗りつぶしを青色系にします。

f:id:waenavi:20200506213707j:plain

 

これで、今日の日付について色が変わりました。もちろん明日になれば明日の日付の色が変わります。

f:id:waenavi:20200506213731j:plain

 

もう一度、A列の日付を選択して、新しいルールの画面を開きます。「数式を使用して、書式設定するセルを決定」を選び、「=A3=TODAY()+1」と入力します。書式のボタンを押し、書式を設定します。

f:id:waenavi:20200506213819j:plain

 

塗りつぶしを緑色系にします。

f:id:waenavi:20200506213844j:plain

 

これで、翌日の日付について色が変わりました。もちろん明日になれば明後日の日付の色が変わります。

f:id:waenavi:20200506213903j:plain

 

ここで、A列の日付を選択して、条件付き書式の「ルールの管理」をクリックします。

f:id:waenavi:20200506213956j:plain

 

2つのルールが設定されていることが分かります。この画面で、条件の順序、編集、削除などの変更ができます。

f:id:waenavi:20200506214117j:plain

 

(2)セルの日付が今日を過ぎたら塗りつぶす

問題

セルA3以降の日付のうち、過去の日付であれば赤色系で塗りつぶしなさい。また、3日以上経過している日付は青色系で塗りつぶしなさい。

f:id:waenavi:20200506214949j:plain

 

解説

今日より前の日付を塗りつぶします。A列の日付を選択して、新しいルールの画面を開きます。「数式を使用して、書式設定するセルを決定」を選び、「=A3<TODAY()」と入力します。

f:id:waenavi:20200506215040j:plain

 

これで今日より前の日付(過去の日付)に色が付きました。

f:id:waenavi:20200506215055j:plain

 

もう一度、A列の日付を選択して、新しいルールの画面を開きます。「数式を使用して、書式設定するセルを決定」を選び、「=A3+3<=TODAY()」と入力します。

f:id:waenavi:20200506215135j:plain

 

これで3日以上経過している日付に色が付きました。

f:id:waenavi:20200506215159j:plain

 

(3)空白だったら色を付けない

問題

セルA3以降の日付のうち、過去の日付であれば赤色系で塗りつぶした。ところが、セルの日付を削除したところ、空白セルであるにもかかわらず過去の日付と判定され、塗りつぶしの色がついてしまった。空白の場合に色が付かないように修正しなさい。

f:id:waenavi:20200506215359j:plain

 

解説

空白セルと数値(シリアル値)の大小比較をすると、空白は「0」とみなされます。したがって、常に「空白<TODAY()」が成り立ちますから、空白セルも過去の日付として色がついてしまいます(COUNTIFではカウントされませんが、条件付き書式では色がついてしまう)。

f:id:waenavi:20200506215823j:plain

 

しかし、期限の日付(本日)を過ぎていることを知らせてほしいのに、空白も色が付くのはおかしいです。条件式を修正します。

f:id:waenavi:20200506215908j:plain

 

ルールの管理で編集します。

f:id:waenavi:20200506220002j:plain

 

AND関数で空白以外の式を追加します。

  • =AND(A3<>"",A3<TODAY())

f:id:waenavi:20200506220038j:plain

f:id:waenavi:20200506220056j:plain

 

別解

ISNUMBER関数を用いても良いです。

  • =AND(ISNUMBER(A3),A3<TODAY())

 

(4)期限が近付いていれば塗りつぶす

問題

セルA3以降の日付のうち、今日以降の日付で1週間以内の日付であれば青色系で塗りつぶしなさい。

f:id:waenavi:20200506220518j:plain

 

解説

A列の日付を選択して、新しいルールの画面を開きます。「数式を使用して、書式設定するセルを決定」を選び、「=A3<=TODAY()+7」と入力します。

f:id:waenavi:20200506220612j:plain

 

これで今日から1週間以内の日付(7日後以下)に色が付きました。しかし、過去の日付にも色がついてしまいます。

f:id:waenavi:20200506220653j:plain

 

条件式を修正します。ルールの管理で編集します。

f:id:waenavi:20200506220723j:plain

 

AND関数で今日以降という式を追加します。

  • =AND(A3>=TODAY(),A3<=TODAY()+7)

f:id:waenavi:20200506220816j:plain

f:id:waenavi:20200506220841j:plain

 

(5)条件の順序に注意せよ!!

問題

さきほどの問題でさらに、今日以降の日付で3日以内の日付であれば赤色系で塗りつぶしなさい。

f:id:waenavi:20200506220841j:plain

 

解説

3日以内の日付に赤色系の条件付き書式を設定します。

  • =AND(A3>=TODAY(),A3<=TODAY()+3)

f:id:waenavi:20200506220934j:plain

f:id:waenavi:20200506220951j:plain

 

ここで、A列の日付を選択して、条件付き書式のルールの管理で確認します。3日以内の日付を塗りつぶす設定が優先となっています。

f:id:waenavi:20200506221013j:plain

 

3日以内のほうが優先になっているのが正しいです。

f:id:waenavi:20200506222127j:plain

 

この順序を逆にして適用します。順序を逆にすると3日以内の日付の色が変わらなくなります。

f:id:waenavi:20200506221036j:plain

 

3日以内の日付は、1週間以内の日付の条件に含まれるため、2番目の条件として設定しても意味がありません。このように条件付き書式で条件を複数設定する場合は条件の順序に注意する必要があります。

f:id:waenavi:20200506222208j:plain

 


解説は以上です。


 


Copyright(C)2018-2020 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ