国の会計年度や学校の年度のように4月から翌年3月までを「年度」といいます。Excelに年度を求める関数はありませんが、なぜ年度を求める関数が無いかと言うとEDATE関数を用いて比較的簡単に求めることができるからです。
企業の会計年度は企業ごとに自由に定めることができ、10月から始める場合は9月が決算となります。この場合もEDATEを使えば年度を求めることができます。
そこで、今回は、日付から西暦年度と和暦年度を表示する方法、さらに年度から年度開始日と年度末の日(決算日)を計算する方法と、EDATE関数が使えない場合の年度の求め方について解説します。
目次
- １．EDATE関数
- ２．4月から始まる年度（3月決算）
- ３．翌年度、20XX/XX年度の形式
- ４．令和元年度対応
- ５．年と月から年度を求める（EDATEが使えないパターン）
- ６．TODAY
- ７．年度で集計
- ８．X月から始まる年度の求め方
- ９．年度開始日と終了日を求める
１．EDATE関数
問題
4つの日付2019/2/28、2020/2/29、2021/4/30、2022/3/31について、EDATE関数を用いて、12か月前～12か月後の日付を求めなさい。
解説
年度の計算をするのに不可欠なEDATE関数について確認しておきましょう。EDATE関数はExcel2007以降で標準として使えるようになった関数で、何か月前または何か月後の日付を求めることができます（Excel2003以前は分析ツールアドインだった）。
- =EDATE(日付のシリアル値,加算または減算する月)
2019/2/28の12か月前の日付は、2018/2/28です。
- =EDATE(C$1,$B2)
日にちが1日～28日の場合、年月だけが変わり、日にちは変わりません。2019/2/28は月末ですが、EDATEが末日になるとは限りません。
日にちが29日～31日の場合も、年月は必ず変わり、原則として日にちは変わりません。
該当する日にちが無い場合は、同じ月の末日になります。
２．4月から始まる年度（3月決算）
（１）日付から西暦年度を求める
問題
年度を4月1日～3月末とする。セルA1の日付を用いて、セルB1に年度を求めなさい。
解説
年度は、年と月だけで求められ、日は関係ありません。そこで、年度を求めるには年と月をもとに計算するEDATE関数を使って求めます。4月から始まる年度の場合、3か月前の日付「=EDATE(日付,-3) 」にして、その年を取得すればよいです（YEAR関数）。
- =YEAR(EDATE(日付,-3))
（２）日付から和暦年度を求める
問題
年度を4月1日～3月末とする。セルA1の日付を用いて、セルB2に和暦の年度（ggge）を求めなさい。
解説
日付のシリアル値から和暦年にするには表示形式（ユーザー定義）を「ggge」にします。「ggg」が元号を表し、「e」が和暦の年を表しています。数式で和暦年にするには、TEXT関数を用いて求めます。
- =TEXT(シリアル値,"ggge")
3か月の日付を和暦に変換すればよいです。TEXT関数の中にDATEを入れます。
- =TEXT(3か月前の日付,"ggge")
- =TEXT(EDATE(A1,-3),"ggge")
（３）年度から年度開始日と年度末を求める
問題
年度を4月1日～3月末とする。セルA1の日付から西暦の年度を求めた。年度の開始日と終了日をそれぞれ求めなさい。
解説
年度を求めることができたら、開始日を求めるのは簡単です。その年度の4月1日なので、DATE関数で4月1日を求めればよいです。4月2日から始める場合はDATE(年度,4,2)です。
- =DATE(年度,4,1)
年度末は翌年の3月31日です。年度に1を足して、3月31日を求めます。
- =DATE(年度+1,3,31)
別解
DATE関数の日にちを0にすることで、末日を求めることができます。例えば、3月31日は4月0日と同じです。4月1日の前日と考えます。さらに、年度に1を足すのではなく、月に12を足すことで1年後を求めることもできます。
- =DATE(年度+1,3,31)
- =DATE(年度+1,4,0)
- =DATE(年度,16,0) ※月に12を加算
4月から始まる年度で、年度が分かっている場合、開始日は4月1日、終了日は16月0日です。
- 開始日：=DATE(年度,4,1)
- 終了日：=DATE(年度,16,0) ※月に12を加算して、日を0にする
３．翌年度、20XX/XX年度の形式
問題
セルA1に日付を入力した。4月から始まる年度の場合、「2023/24年度」のような年度を求めなさい。
解説
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年度")
４．令和元年度対応
問題
セルA1に日付を入力した。4月から始まる年度の場合、和暦の年度を求めなさい。ただし、2019年度の場合、令和元年度としなさい。
解説
2019年度は4月が平成で、5月以降が令和なので、本来なら平成31年度と表記するべきですが、改元の特例として「令和元年度」と表記するきまりになっています。これは特例なので、計算式の中に入れようとせず、例外として処理するべきです。
- =IF(西暦年度=2019,"令和元",和暦年度)&"年度"
西暦の年度「YEAR(EDATE(日付,-3))」と和暦の年度「TEXT(EDATE(日付,-3),"ggge")」を代入することにより、令和対応の年度を求めることができます。
- =IF(YEAR(EDATE(A1,-3))=2019,"令和元",TEXT(EDATE(A1,-3),"ggge"))&"年度"
５．年と月から年度を求める（EDATEが使えないパターン）
（１）年と月を分けて入力している場合
問題
年度を4月1日～3月末とする。年・月・日を分けて入力している場合、年度を求めなさい。
解説
シリアル値でなければEDATE関数を使うことができません。年と月を数値で入力している場合、比較演算を使えば簡単な式で表すことができます。月が年度開始月より小さい場合、年から1を引きます。
- =年－(月＜開始月)
- =A2-(B2<4)
（２）年月または年月日が1つの数値になっている
問題
年度を4月1日～3月末とする。6桁の数値YYYYMMまたは8桁の数値YYYYMMDDで入力している場合、年度を求めなさい。
解説
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)
６．TODAY
問題
4月から始まる年度の場合、現在の入力時点における年度を求めなさい。
解説
現在の日付はTODAY関数を使って求めますが、TODAYもシリアル値なので年度を求めることができます。
- =YEAR(EDATE(TODAY(),-3))
７．年度で集計
問題
日付と売上金額を入力した表がある。ピボットテーブルを用いて、4月から始まる年度ごとの売上合計金額を求めなさい。
解説
ピボットテーブルを挿入します。日付で売上金額を集計します。
フィールドのグループ化で年度を選ぶことができません。
年度は、通常4月から始まりますが、企業によって会計年度が異なるため年度による集計をすることができません。そこで、列を挿入して年度を求めます。
ピボットテーブルを挿入します。年度で売上金額を集計します。これで完成です。
８．X月から始まる年度の求め方
（１）X月から始まる西暦年度
問題
セルA1に日付を入力し、年度の開始月をC2～C13、終了月をD2～D13に入力した。E列に年度を求めなさい。
解説
通常、年度は4月から始まりますが、企業の会計年度は企業によって異なります。例えば、9月決算の会社は10月～翌年9月、12月決算の会社はカレンダーどおり1月～12月です。年度の開始月によって、年度を求めるのに何か月前の年を求めたらよいかが変わります。
10月から始まる年度の場合、9か月前の日付を考えます。X月から始まる年度の場合、（X-1）か月前の日付を考えます。EDATE関数を使う場合は符号を変えて（1-開始月）と指定します。
- EDATE(日付,1-開始月)
したがって、年度はYEAR(EDATE(日付,1-開始月))となります。
- =YEAR(EDATE(日付,1-開始月))
- =YEAR(EDATE($A$1,1-C2))
別解
終了月（決算月）から年度を求めることもできます。終了月を12で割った余りにマイナスを付けます。
- =YEAR(EDATE(日付,-MOD(終了月,12)))
- =YEAR(EDATE($A$1,-MOD(D2,12)))
（２）X月から始まる和暦年度
問題
G列に和暦の年度（ggge）を求めなさい。
解説
日付のシリアル値から和暦年にするには表示形式（ユーザー定義）を「ggge」にします。
- =TEXT(シリアル値,"ggge")
- =TEXT(EDATE($A$1,1-C2),"ggge")
2019年度を令和元年度にすることも可能です。
- =IF(西暦年度=2019,"令和元",和暦年度)
- =IF(E2=2019,"令和元",TEXT(EDATE($A$1,1-C2),"ggge"))
９．年度開始日と終了日を求める
問題
セルA1に日付を入力し、年度の開始月をC3～C14、終了月をD3～D14に入力した。また、セルA1の日付とC列の開始月を用いて、E列に西暦の年度を求めた。
（１）年度の開始日と終了日をそれぞれ求めなさい。
（２）開始＝1、終了＝0とするとき、開始日と終了日を一括で求めなさい。
解説
年度の開始日は、その年度の開始月の1日です。
- =DATE(年度,開始月,1)
- =DATE(E3,C3,1)
年度末は開始日の12か月後（1年後）の前日なので、開始月を用いて求めることができます。
- =DATE(年度,開始月+12,0)
- =DATE(E3,C3+12,0)
ちなみに、終了月を用いる場合、1月から始まる年度を考慮してMOD関数を使う必要があります。
- =DATE(E3,MOD(D3,12)+13,0)
ここで、開始日と終了日を比較すると1つの式で統一できそうです。
- 開始日：=DATE(年度,開始月,1)
- 年度末：=DATE(年度,開始月+12,0)
月は12を加算するかしないか、日付は１か０かの違いなので、開始日＝１、終了日＝０のフラグを用いてそれぞれの日付を求めることができます。
- =DATE(年度,開始月+12*(1-フラグ),フラグ)
- =DATE($E3,$C3+12*(1-H$2),H$2)
解説は以上です。
