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

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

【EDATE関数】年月を加算して年や月だけを表示したい場合であってもシリアル値で計算しましょう

例えば、2020年11月の3か月後の年月(2021年2月)を求めたい場合、年と月だけで3か月を加算することを考えるのではなく、2020年11月1日に3か月を加算したほうが簡単です。EDATE関数を使えばよいです(参考:【Excel日付】月単位や年単位の加算、EDATE関数の使い方基本編)。

このように、Excelで西暦年や月だけ入力して、その数か月後または数年後を求めるには、日付にしてからシリアル値で計算したほうが簡単です。また、Excelで年月を入力するときには、本当に年と月だけを入力するのが正しいのかを検討すべきです。

  • 元の年月は本当に年と月だけ入力すべきか?
  • 計算結果は本当に年と月だけで良いのか?

そこで、今回はExcelに年と月だけ表示して、その数か月後または数か月前を計算する方法について出題します。

目次

1.EDATE関数の基本

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

 

2.年と月を1つのセルに表示する

問題

セルA1に入力した日付を基準日として、3か月後の年と月を求めなさい。

f:id:waenavi:20200412185359j:plain

 

解説

セルA1の3か月後の日付は「=EDATE(A1,3)」です。年と月をそれぞれ表示するのであれば、YEAR関数とMONTH関数を用いて取り出すことが可能です。

  • =YEAR(EDATE(A1,3))
  • =MONTH(EDATE(A1,3))

f:id:waenavi:20200412185313j:plain

 

これで、3か月後の年と月を表示することは可能ですが、このような表示方法は不適切です。まず、EDATE関数で3か月後の日付を求めます。シリアル値のままでよいです(参考:【Excel】日付の「シリアル値」を本気で理解するには何を練習するべきか?)。

  • =EDATE(A1,3)

f:id:waenavi:20200412185553j:plain

 

表示形式を「yyyy年m月」の形式にします。これで年月を表示することができます。YEAR関数もMONTH関数も不要です。

f:id:waenavi:20200412185704j:plain

 

原則として、Excelでは年月を1つのセルで表示すべきであり、次のことを理解しなければなりません。

  • 計算結果が日付のシリアル値であれば、表示形式によって日にちの部分を非表示にすることができること。
  • 計算結果として「年月」の表示にしたい場合であっても、日付のシリアル値で計算すること。

f:id:waenavi:20200412190147j:plain

 

3.文字列を連結する場合

問題

現在の日付を用いて1か月前と1か月後を求め、それぞれ「〇年〇月分」と表示しなさい。

f:id:waenavi:20200412190401j:plain

 

解説

現在の日付はTODAY関数です。前月と翌月の日付をEDATE関数で求めます。シリアル値のままでよいです。

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

f:id:waenavi:20200412190452j:plain

 

ユーザー定義の表示形式で「yyyy年m月分」とします。これで年月を表示することができます。

f:id:waenavi:20200413023520j:plain

 

別解

表示形式ではなく、文字列連結演算「&」を使うとシリアル値で連結されてしまいます。

  • =EDATE(TODAY(),1)&"分"
  • =EDATE(TODAY(),-1)&"分"

f:id:waenavi:20200413024042j:plain

 

文字列を連結するときは必ずTEXT関数で表示形式を変換します。

  • =TEXT(EDATE(TODAY(),1),"yyyy年m月")&"分"
  • =TEXT(EDATE(TODAY(),-1),"yyyy年m月")&"分"

f:id:waenavi:20200413024147j:plain

 

4.変則的な年月の求め方

(1)月末の場合だけ当月、それ以外は前月

問題

A列の日付がその月の月末にあたる場合は当月、それ以外は前月を表示しなさい。

f:id:waenavi:20200414124529j:plain

 

解説

A列の日付に1を加算すると、月末にあたる日付の場合は翌月の1日になりますが、それ以外の日付は当月のままです。

f:id:waenavi:20200414125208j:plain

 

これを年月だけの表示にすると、月末の場合だけ翌月、それ以外は当月になります。

f:id:waenavi:20200414125631j:plain

 

さらに1か月前にするにはEDATE関数を使えばよいです。月末の場合だけ当月、それ以外は前月になります。

  • =EDATE(A1+1,-1)

f:id:waenavi:20200414125731j:plain

 

このように、求める答えが年と月だけであったとしても、年と月を取り出そうと考えてはいけません。シリアル値で日付を加算または減算して求めます。

f:id:waenavi:20200414133918j:plain

 

(2)月初の場合だけ当月、それ以外は翌月

問題

A列の日付がその月の月初にあたる場合は当月、それ以外は翌月を表示しなさい。

f:id:waenavi:20200414124529j:plain

 

解説

A列の日付から1を引くと、月初にあたる日付の場合は前月の月末になりますが、それ以外の日付は当月のままです。

f:id:waenavi:20200414125857j:plain

 

これを年月だけの表示にすると、月初の場合だけ前月、それ以外は当月になります。

f:id:waenavi:20200414125931j:plain

 

さらに1か月後にするにはEDATE関数を使えばよいです。月初の場合だけ当月、それ以外は翌月になります。

  • =EDATE(A1-1, 1)

f:id:waenavi:20200414130014j:plain

 

(3)15日までは当月、16日以降は翌月

問題

A列の日付がその月の15日までの場合は当月、16日以降は翌月を表示しなさい。

f:id:waenavi:20200414124529j:plain

 

解説

15日を区切りとする場合はいったん15日を引きます。

f:id:waenavi:20200414130302j:plain

 

この1か月後を求めると、15日までが当月、16日以降が翌月になります。

  • =EDATE(A1-15, 1)

f:id:waenavi:20200414130822j:plain

 

*補足*日にちを固定して表示する場合はEOMONTH関数のほうが良いです。例えば、20日で固定するにはEOMONTH+20です。

  • =EOMONTH(A1-15, 1)+20

f:id:waenavi:20200414130920j:plain

 

5.「年月だけ」と思ってはいけない

(1)翌月の年月を求める

問題

「2020年12月」と入力すると、隣のセルに自動で翌月が表示されるようにしなさい。

f:id:waenavi:20200414132819j:plain

 

解説

年月だけ入力して、その1か月後の年月だけを表示したいという場合、「年月に1か月加算する」と考えるのではなく、両方シリアル値で考えます。「2020年12月」と入力するのではなく、「2020年12月1日」と考えます。

f:id:waenavi:20200414133203j:plain

 

2020/1/1と入力して、表示形式で年月だけにします。

f:id:waenavi:20200414133326j:plain

 

1か月後を求めます。2021年1月と表示されますが、実質は2021/1/1のシリアル値です。

  • =EDATE(A1,1)

f:id:waenavi:20200414133413j:plain

 

(2)最終月を求める

問題

60回払いの開始月が「2020年12月」の場合、最後に支払う年月(最終月)を求めなさい。

f:id:waenavi:20200414134133j:plain

 

解説

「2020/12/1」を入力して、表示形式で年月だけにします。

f:id:waenavi:20200414134155j:plain

 

60回払いの場合、最終月は最初の月の59か月後です。59か月後を求めます。

  • =EDATE(B1,B2-1)

f:id:waenavi:20200414134238j:plain

 

6.例外:月から月を求めるだけの計算

問題

A列に1~12の整数を入力した。A列の数値を今月の月として、来月、3か月後、半年後、先月、3か月前の月を求めなさい。

f:id:waenavi:20200414135412j:plain

 

解説

月だけ数値で入力して、その数か月後または数か月前を求めるだけであれば、単なる12進数の計算なので、シリアル値にする必要はありません。また、EDATE関数も不要です。

f:id:waenavi:20200414135033j:plain

 

MOD関数で求めることができます。来月の場合、12で割った余りに1を加えるだけです。

  • =MOD(A2,12)+1

f:id:waenavi:20200414135450j:plain

 

3か月後は2を足してから、12で割った余りに1を足します。

  • =MOD(A2+2,12)+1

f:id:waenavi:20200414135526j:plain

 

6か月後は5を足してから、12で割った余りに1を足します。

  • =MOD(A2+5,12)+1

f:id:waenavi:20200414135558j:plain

 

Xか月後は、X-1を足してから12で割ったときの余りに1を足せばよいです。

f:id:waenavi:20200414135821j:plain

 

先月の場合は「-1か月後」と同じ意味なので、-2を足してから(2を引いてから)12で割った余りに1を足します。

  • =MOD(A2-2,12)+1

f:id:waenavi:20200414135950j:plain

 

3か月後は4を引いてから、12で割った余りに1を足します。

  • =MOD(A2-4,12)+1

f:id:waenavi:20200414140044j:plain

 


解説は以上です。


 


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