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

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

【Excel関数】西暦年度、和暦年度、年度開始日と年度末の日を表示する

国の会計年度や学校の年度のように4月から翌年3月までを「年度」といいます。Excelに年度を求める関数はありませんが、なぜ年度を求める関数が無いかと言うとEDATE関数を用いて比較的簡単に求めることができるからです。

企業の会計年度は企業ごとに自由に定めることができ、10月から始める場合は9月が決算となります。この場合もEDATEを使えば年度を求めることができます。

そこで、今回は、日付から西暦年度と和暦年度を表示する方法、さらに年度から年度開始日と年度末の日(決算日)を計算する方法と、EDATE関数が使えない場合の年度の求め方について解説します。

f:id:waenavi:20190823180446j:plain

 

 

目次

1.EDATE関数

問題

4つの日付2019/2/28、2020/2/29、2021/4/30、2022/3/31について、EDATE関数を用いて、12か月前~12か月後の日付を求めなさい。

f:id:waenavi:20190823163358j:plain

 

解説

年度の計算をするのに不可欠なEDATE関数について確認しておきましょう。EDATE関数はExcel2007以降で標準として使えるようになった関数で、何か月前または何か月後の日付を求めることができます(Excel2003以前は分析ツールアドインだった)

  • =EDATE(日付のシリアル値,加算または減算する月)

2019/2/28の12か月前の日付は、2018/2/28です。

  • =EDATE(C$1,$B2)

f:id:waenavi:20190823164807j:plain

 

日にちが1日~28日の場合、年月だけが変わり、日にちは変わりません。2019/2/28は月末ですが、EDATEが末日になるとは限りません。

f:id:waenavi:20190823164842j:plain

 

日にちが29日~31日の場合も、年月は必ず変わり、原則として日にちは変わりません。

f:id:waenavi:20190823164918j:plain

 

該当する日にちが無い場合は、同じ月の末日になります。

f:id:waenavi:20190823164944j:plain

 

2.4月から始まる年度(3月決算)

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

問題

年度を4月1日~3月末とする。セルA1の日付を用いて、セルB1に年度を求めなさい。

f:id:waenavi:20190823165044j:plain

 

解説

年度は、年と月だけで求められ、日は関係ありません。そこで、年度を求めるには年と月をもとに計算するEDATE関数を使って求めます。4月から始まる年度の場合、3か月前の日付「=EDATE(日付,-3) 」にして、その年を取得すればよいです(YEAR関数)。

f:id:waenavi:20190823174900j:plain

 

  • =YEAR(EDATE(日付,-3))

f:id:waenavi:20190823165212j:plain

 

(2)日付から和暦年度を求める

問題

年度を4月1日~3月末とする。セルA1の日付を用いて、セルB2に和暦の年度(ggge)を求めなさい。

f:id:waenavi:20190823165312j:plain

 

解説

日付のシリアル値から和暦年にするには表示形式(ユーザー定義)を「ggge」にします。「ggg」が元号を表し、「e」が和暦の年を表しています。数式で和暦年にするには、TEXT関数を用いて求めます。

  • =TEXT(シリアル値,"ggge")

3か月の日付を和暦に変換すればよいです。TEXT関数の中にDATEを入れます。

  • =TEXT(3か月前の日付,"ggge")
  • =TEXT(EDATE(A1,-3),"ggge")

f:id:waenavi:20190823165604j:plain

 

(3)年度から年度開始日と年度末を求める

問題

年度を4月1日~3月末とする。セルA1の日付から西暦の年度を求めた。年度の開始日と終了日をそれぞれ求めなさい。

f:id:waenavi:20190823165724j:plain

 

解説

年度を求めることができたら、開始日を求めるのは簡単です。その年度の4月1日なので、DATE関数で4月1日を求めればよいです。4月2日から始める場合はDATE(年度,4,2)です。

  • =DATE(年度,4,1)

f:id:waenavi:20190823165843j:plain

 

年度末は翌年の3月31日です。年度に1を足して、3月31日を求めます。

  • =DATE(年度+1,3,31)

f:id:waenavi:20190823165950j:plain

 

別解

DATE関数の日にちを0にすることで、末日を求めることができます。例えば、3月31日は4月0日と同じです。4月1日の前日と考えます。さらに、年度に1を足すのではなく、月に12を足すことで1年後を求めることもできます。

  • =DATE(年度+1,3,31)
  • =DATE(年度+1,4,0)
  • =DATE(年度,16,0) ※月に12を加算

f:id:waenavi:20190823170113j:plain

 

4月から始まる年度で、年度が分かっている場合、開始日は4月1日、終了日は16月0日です。

  • 開始日:=DATE(年度,4,1)
  • 終了日:=DATE(年度,16,0) ※月に12を加算して、日を0にする

 

3.翌年度、20XX/XX年度の形式

問題

セルA1に日付を入力した。4月から始まる年度の場合、「2023/24年度」のような年度を求めなさい。

f:id:waenavi:20190823170245j:plain

 

解説

4月から始まる年度の場合、年をまたぐので「2020/21年度」のように表記することがあります。
西暦から年度を求めるには3か月前の年を取得すればよいですが、翌年度を求めるには9か月後の年を取得すればよいです。

  • 西暦年度:YEAR(EDATE(日付,-3))
  • 翌年度:YEAR(EDATE(日付,9))

西暦年度と翌年度の下2桁を並べます。下2桁は100で割った余りで、表示形式を「00」にします。

  • =西暦年度 & "/" & TEXT(MOD(翌年度,100),"00年度")
  • =YEAR(EDATE(A1,-3))&"-"&TEXT(MOD(YEAR(EDATE(A1,9)),100),"00年度")

f:id:waenavi:20190823170438j:plain

 

4.令和元年度対応

問題

セルA1に日付を入力した。4月から始まる年度の場合、和暦の年度を求めなさい。ただし、2019年度の場合、令和元年度としなさい。

f:id:waenavi:20190823172438j:plain

 

解説

2019年度は4月が平成で、5月以降が令和なので、本来なら平成31年度と表記するべきですが、改元の特例として「令和元年度」と表記するきまりになっています。これは特例なので、計算式の中に入れようとせず、例外として処理するべきです。

  • =IF(西暦年度=2019,"令和元",和暦年度)&"年度"

西暦の年度「YEAR(EDATE(日付,-3))」と和暦の年度「TEXT(EDATE(日付,-3),"ggge")」を代入することにより、令和対応の年度を求めることができます。

  • =IF(YEAR(EDATE(A1,-3))=2019,"令和元",TEXT(EDATE(A1,-3),"ggge"))&"年度"

f:id:waenavi:20190823172517j:plain

 

5.年と月から年度を求める(EDATEが使えないパターン)

(1)年と月を分けて入力している場合

問題

年度を4月1日~3月末とする。年・月・日を分けて入力している場合、年度を求めなさい。

f:id:waenavi:20190823172715j:plain

 

解説

シリアル値でなければEDATE関数を使うことができません。年と月を数値で入力している場合、比較演算を使えば簡単な式で表すことができます。月が年度開始月より小さい場合、年から1を引きます。

  • =年-(月<開始月)
  • =A2-(B2<4)

f:id:waenavi:20190823173149j:plain

f:id:waenavi:20190823173248j:plain

 

(2)年月または年月日が1つの数値になっている

問題

年度を4月1日~3月末とする。6桁の数値YYYYMMまたは8桁の数値YYYYMMDDで入力している場合、年度を求めなさい。

f:id:waenavi:20190823173348j:plain

 

解説

6桁の数値YYYYMMまたは8桁の数値YYYYMMDDで入力している場合は、年と月を取得します。年は左の4文字、月は5文字目~6文字目です。

  • 年:LEFT(数値,4)
  • 月:MID(数値,5,2)

年と月を「年-(月<開始月)」に代入して年度を求めます。

  • =LEFT(数値,4)-(MID(数値,5,2)*1<開始月)
  • =LEFT(A1,4)-(MID(A1,5,2)*1<4)

f:id:waenavi:20190823173553j:plain

 

6.TODAY

問題

4月から始まる年度の場合、現在の入力時点における年度を求めなさい。

f:id:waenavi:20190823173734j:plain

 

解説

現在の日付はTODAY関数を使って求めますが、TODAYもシリアル値なので年度を求めることができます。

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

f:id:waenavi:20190823173736j:plain

 

7.年度で集計

問題

日付と売上金額を入力した表がある。ピボットテーブルを用いて、4月から始まる年度ごとの売上合計金額を求めなさい。

f:id:waenavi:20190823173844j:plain

 

解説

ピボットテーブルを挿入します。日付で売上金額を集計します。

f:id:waenavi:20190823173935j:plain

 

フィールドのグループ化で年度を選ぶことができません。

f:id:waenavi:20190823174158j:plain

 

年度は、通常4月から始まりますが、企業によって会計年度が異なるため年度による集計をすることができません。そこで、列を挿入して年度を求めます。

f:id:waenavi:20190823174304j:plain

 

ピボットテーブルを挿入します。年度で売上金額を集計します。これで完成です。

f:id:waenavi:20190823174405j:plain

 

8.X月から始まる年度の求め方

(1)X月から始まる西暦年度 

問題

セルA1に日付を入力し、年度の開始月をC2~C13、終了月をD2~D13に入力した。E列に年度を求めなさい。

f:id:waenavi:20190823174618j:plain

 

解説

通常、年度は4月から始まりますが、企業の会計年度は企業によって異なります。例えば、9月決算の会社は10月~翌年9月、12月決算の会社はカレンダーどおり1月~12月です。年度の開始月によって、年度を求めるのに何か月前の年を求めたらよいかが変わります。

f:id:waenavi:20190823174710j:plain

 

10月から始まる年度の場合、9か月前の日付を考えます。X月から始まる年度の場合、(X-1)か月前の日付を考えます。EDATE関数を使う場合は符号を変えて(1-開始月)と指定します。

  • EDATE(日付,1-開始月)

したがって、年度はYEAR(EDATE(日付,1-開始月))となります。

  • =YEAR(EDATE(日付,1-開始月))
  • =YEAR(EDATE($A$1,1-C2))

f:id:waenavi:20190823175102j:plain

 

別解

終了月(決算月)から年度を求めることもできます。終了月を12で割った余りにマイナスを付けます。

  • =YEAR(EDATE(日付,-MOD(終了月,12)))
  • =YEAR(EDATE($A$1,-MOD(D2,12)))

f:id:waenavi:20190823175155j:plain

 

(2)X月から始まる和暦年度

問題

G列に和暦の年度(ggge)を求めなさい。

f:id:waenavi:20190823175245j:plain

 

解説

日付のシリアル値から和暦年にするには表示形式(ユーザー定義)を「ggge」にします。

  • =TEXT(シリアル値,"ggge")
  • =TEXT(EDATE($A$1,1-C2),"ggge")

2019年度を令和元年度にすることも可能です。

  • =IF(西暦年度=2019,"令和元",和暦年度)
  • =IF(E2=2019,"令和元",TEXT(EDATE($A$1,1-C2),"ggge"))

f:id:waenavi:20190823175341j:plain

 

9.年度開始日と終了日を求める

問題

セルA1に日付を入力し、年度の開始月をC3~C14、終了月をD3~D14に入力した。また、セルA1の日付とC列の開始月を用いて、E列に西暦の年度を求めた。

f:id:waenavi:20190823175457j:plain

 

(1)年度の開始日と終了日をそれぞれ求めなさい。

f:id:waenavi:20190823180446j:plain

 

(2)開始=1、終了=0とするとき、開始日と終了日を一括で求めなさい。

f:id:waenavi:20190823180512j:plain

 

解説

年度の開始日は、その年度の開始月の1日です。

  • =DATE(年度,開始月,1)
  • =DATE(E3,C3,1)

f:id:waenavi:20190823175828j:plain

 

年度末は開始日の12か月後(1年後)の前日なので、開始月を用いて求めることができます。

  • =DATE(年度,開始月+12,0)
  • =DATE(E3,C3+12,0)

f:id:waenavi:20190823180550j:plain

 

ちなみに、終了月を用いる場合、1月から始まる年度を考慮してMOD関数を使う必要があります。

  • =DATE(E3,MOD(D3,12)+13,0)

f:id:waenavi:20190823181201j:plain

 

ここで、開始日と終了日を比較すると1つの式で統一できそうです。

  • 開始日:=DATE(年度,開始月,1)
  • 年度末:=DATE(年度,開始月+12,0)

月は12を加算するかしないか、日付は1か0かの違いなので、開始日=1、終了日=0のフラグを用いてそれぞれの日付を求めることができます。

  • =DATE(年度,開始月+12*(1-フラグ),フラグ)
  • =DATE($E3,$C3+12*(1-H$2),H$2)

f:id:waenavi:20190823181336j:plain

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]