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

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

【Excel日付】EDATE関数と他の関数を組み合わせた応用の練習問題と注意点

EDATE関数をExcelで用いる場合、単独で用いる場合と他のExcel関数を組み合わせて使う場合があります。他の関数と組み合わせる場合はその関数の知識が必要です。例えば、EDATE関数で元の日付のセルが空白だった場合に空白を返したい場合は、IF関数を使います。この手法はEDATE関数の問題ではなく、IF関数の基本です。IF関数を知っていれば処理できる問題です。

また、DATE関数やEMONTH関数のような日付関数と混同しがちなので、EDATE関数を用いるときには他の日付関数との違いも知っておく必要があります。

そこで、今回は、他の関数と組み合わせたEDATE関数の応用事例と、他の日付関数との違いについて出題します。

目次

1.EDATE関数の基本

EDATE関数の基本的な使い方についてはこちらの記事をご覧ください。

 

2.今日の日付が起算日の場合

(1)TODAY

問題

今日の日付を起算日として、翌月の応当日の前日を求めなさい。ただし、応当日が無い場合は翌月の月末とする。

解説

通常、期間の終了日は起算日の応当日の前日です(参考:月または年単位の期間の「終了日」をEDATE関数で計算するための基礎知識)。つまり、「EDATE-1」です。今日が起算日の場合はTODAY関数を使います。

  • =EDATE(TODAY(),1)-1

f:id:waenavi:20200407183017j:plain

 

EDATE関数は応当日が無い時は強制的に月末の日付になりますが、通常、民法の規定により、応当日が無い場合は、期間の終了日を月末の日付としますので1を引いてはいけません。したがって、応当日の有無を判定して、応当日がある場合に限り1を引きます。

  • =EDATE(TODAY(),1)-(DAY(TODAY())=DAY(EDATE(TODAY(),1)))

f:id:waenavi:20200407183148j:plain

 

(2)補足:NOWを使ってはいけない

問題

現在の日付と時刻を用いて1か月後の日付を求めるのに、TODAY関数ではなくNOW関数を用いてもよいか。

  • =EDATE(NOW(),1)

解説

NOW関数は現在の日付と時刻を合わせたものを返すため、シリアル値は小数です。整数部分が現在の時刻を表すシリアル値で、小数部分が現在の時刻です。これをEDATE関数の第1引数にしても、1か月後の日付が求められるように見えます。

f:id:waenavi:20200407183508j:plain

 

しかし、時刻を表示すると0時になっています。EDATE関数の第1引数は日付(整数のシリアル値)であり、時刻を含む場合(小数の場合)は小数部分が切り捨てになります。

f:id:waenavi:20200407183609j:plain

 

したがって、EDATE関数の第1引数はTODAY関数を用いるべきです。なお、時刻も含めて1か月後を求めるには、NOWの小数部分を足せばよいです。

  • =EDATE(TODAY(),1)+MOD(NOW(),1)

f:id:waenavi:20200407190307j:plain

 

3.IF関数を用いた例外処理

(1)元のセルが未入力の場合は空白にする

問題

A列の日付の1か月後を求めなさい。ただし、空白の場合は空白にしなさい。

f:id:waenavi:20200408105019j:plain

 

解説

EDATE関数を用いて1か月後の日付(シリアル値)を求めます。

  • =EDATE(A1,1)

f:id:waenavi:20200408105750j:plain

 

日付の表示形式にします。空白は0とみなされる(=1900/1/0)ので、1900年1月31日という意味不明な日付が表示されます。

f:id:waenavi:20200408105828j:plain

 

この表示を無くすにはIF関数を用いて空白にします。

  • =IF(A1="","",EDATE(A1,1))

f:id:waenavi:20200408105936j:plain

 

(2)日付以外は空白にする

問題

A列の日付の1か月後を求めなさい。ただし、日付以外が入力されている場合は空白にしなさい。

f:id:waenavi:20200408110640j:plain

 

解説

元の日付が空白の場合に空白にする計算式を入力すると、空白以外の場合エラーになります。

f:id:waenavi:20200408110609j:plain

 

この場合は、IFERROR関数で空白にすればよいです。

  • =IF(A1="","",IFERROR(EDATE(A1,1),""))

f:id:waenavi:20200408111015j:plain

 

別解

日付のシリアル値は数値なので、ISNUMBER関数がTRUEになります。ISNUMBER関数は数値かどうかを判定する関数です。日付のシリアル値として無効な数値の場合もISNUMBER関数はTRUEとなりますが、EDATE関数がエラーになります。

  • =IF(ISNUMBER(A1),EDATE(A1,1),"")

f:id:waenavi:20200408110719j:plain

 

このエラーをIFERROR関数で消しても構いませんが、その前に、元の日付の入力の誤りを修正したほうが良いです。

f:id:waenavi:20200408111459j:plain

 

4.文字列を連結した時に数値になる問題

問題

セルB1に入力した日付の3年後の前日を求め、「期限:令和〇年〇月〇日まで」と表示しなさい。

f:id:waenavi:20200408111721j:plain

 

解説

EDATE関数を用いて、3年後=36か月後の前日を求めます。

  • =EDATE(B1,36)

f:id:waenavi:20200408112433j:plain

 

文字列を連結するとシリアル値になります(参考:【Excel】文字列結合、スペース・改行・ダブルクォーテーションの連結)。いっぱんに文字列連結の演算をすると、「標準」の表示形式の状態で連結をするため、日付の場合はシリアル値となります。

  • ="期限:"&EDATE(B1,36)&"まで"

f:id:waenavi:20200408112613j:plain

 

文字列を連結したものは「文字列」なので、日付の表示形式にしたとしても無視されます。

f:id:waenavi:20200408112635j:plain

 

この場合は、TEXT関数で表示形式を変える必要があります(参考:【Excel】TEXT関数で数値を日付や時刻のシリアル値に変換する、日付の変換方法まとめ)。

  • ="期限:"&TEXT(EDATE(B1,36),"yyyy/m/d")&"まで"

f:id:waenavi:20200408113012j:plain

 

和暦にすることも可能です(参考:【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

  • ="期限:"&TEXT(EDATE(B1,36),"ggge年m月d日")&"まで"

f:id:waenavi:20200408113100j:plain

 

5.4月から始まる年度の計算

(1)日付から年度を求める

問題

現在の西暦年度を求めなさい。 

解説

4月から始まる年度の場合、3か月前にさかのぼれば西暦と一致します。したがって、3か月前の年を求めればよいです(参考:【Excel関数】西暦年度、和暦年度、年度開始日と年度末の日を表示する)。

  • =YEAR(EDATE(TODAY(),-3))

f:id:waenavi:20200408113709j:plain

 

(2)年度開始日

問題

現在の西暦年度を求めた。これを用いて年度開始日を求めなさい。

f:id:waenavi:20200408113750j:plain

 

解説

さきほどの問題で、現在の西暦年度を求めることができたので、その4月1日が年度開始日です。年・月・日が分かれている場合はDATE関数を使います。

  • =DATE(A1,4,1)

f:id:waenavi:20200408121328j:plain

 

西暦年度、年度開始日、年度終了日の求め方について、詳しくはこちらの記事をご覧ください。

 

6.DATE関数との違い

問題

年と月と日を分けて入力したため、DATE関数を用いて1か月後の日付を求めたい。次の2つの数式の違いを述べなさい。

  • =DATE(A2,B2+1,C2)
  • =EDATE(DATE(A2,B2,C2),1)

f:id:waenavi:20200408122345j:plain

 

解説

年・月・日を分けて入力しそれを1つのシリアル値にする場合はDATE関数を使います。さらに、1か月後の日付を求めるには月に1を足せばよいです。応当日があることがあらかじめ分かっている場合は、この方法で構いません。

  • =DATE(A2,B2+1,C2)

f:id:waenavi:20200408122327j:plain

 

DATE関数とEDATE関数の違いは応当日が無い場合の処理が異なることです。DATE関数の場合、応当日が無い時は、月末日を超えた分の日数を加算します。つまり、2月31日は、月末日である2月28日の3日後と解釈して3月3日となります。

f:id:waenavi:20200408122431j:plain

 

2月1日の1か月後は3月1日(応当日)ですが、1月31日の1か月後は3月3日となり、逆転してしまいます。

f:id:waenavi:20200408122839j:plain

 

これに対して、EDATE関数で応当日が無い時は月末日となります。これによって逆転を防いでいます。

f:id:waenavi:20200408122925j:plain

f:id:waenavi:20200408123130j:plain

 

7.EOMONTH関数との違い

問題

セルA1に入力した日付を基準として、翌々月の20日を求める場合、EDATE関数を使うべきか、それともEOMONTH関数を使うべきか。

f:id:waenavi:20200408123237j:plain

 

解説

翌々月=2か月後の日付を求めるということでEDATE関数を用いたとします。

  • =EDATE(A1,2)

f:id:waenavi:20200408125040j:plain

 

同じ月の20日を求めるにはTEXT関数を使います。数式が長くなります。

  • =(TEXT(EDATE(A1,2),"yyyy-mm")&"-20")*1
  • =DATE(YEAR(EDATE(A1,2),MONTH(EDATE(A1,2)),20)

f:id:waenavi:20200408131122j:plain

 

翌々月の20日は、翌月の月末から見て20日後にあたるため、EOMONTH関数を用いると簡単に求めることができます。月末または月初のように月替わりにかかわる場合や、日にちが固定の場合は原則としてEOMONTH関数を使い、それ以外はEDATE関数を使います。

  • =EOMONTH(A1,1)+20

f:id:waenavi:20200408131229j:plain

 

8.EDATE関数応用事例

EDATE関数を用いた応用問題については、こちらの記事をご覧ください。

 


解説は以上です。


 


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