STUDY387

エクセルで仕事をもっと簡単にしよう

関数で生年月日から入学年度を計算する

time 2014/07/06

関数で生年月日から入学年度を計算する
※ エクセル2013で説明しています。

サクラ

別の記事で、関数を使って入社年月日から入社年度を作成しました。
基本な考え方はその記事でご紹介したとおりです。

しかし、入社年度と入学年度では異なることがあります。
それは、年度を区切る日にちです。

入社年度は3月31日と4月1日の間で区切ります。
しかし、入学年度は4月1日と4月2日の間で区切ります。
そのため、入学年度の関数が少し複雑になります。

結論から書くと入学年度の関数は以下のようになります。

小学校の場合
=IF(OR(MONTH(生年月日)<4,
AND(MONTH(生年月日)=4,
DAY(生年月日)=1)),
YEAR(生年月日)+6,YEAR(生年月日)+7)

中学校の場合
=IF(OR(MONTH(生年月日)<4,
AND(MONTH(生年月日)=4,
DAY(生年月日)=1)),
YEAR(生年月日)+12,YEAR(生年月日)+13)

以下でこの式の説明をします。

sponsored link

入社年度の関数の復習

おさらいです。
入社年度で作成した関数は、以下の通りでした。
IF(MONTH(入社年月日)<4,
YEAR(入社年月日)-1,YEAR(入社年月日))

「もし入社月が4未満ならば入社年から1を引き算した値を、
そうでないならば入社年を表示する」
という意味です。
入社年度の式

IF関数、YEAR関数、MONTH関数の使い方を思い出しましたか?
忘れているようであれば、以下の記事をご覧ください。

入社年度の関数を入学年度の関数にアレンジする

年に足す数値を変更する

入社年度の関数を入学年度の関数にアレンジするには、
まず年に足す数値を変更する必要があります。

小学校の入学年度の場合、生年月日の年に7か6を足します
※中学校の入学年度の場合は、13か12になります。
(例)
2006年4月2日生まれ → 小学校入学2013年度 (両者の差は7)
2007年1月1日生まれ → 小学校入学2013年度 (両者の差は6)

では、足す数値と入社年月日を生年月日に変更して、
入社年度の関数を書き換えます。
IF(MONTH(生年月日)<4,
YEAR(生年月日)+6,YEAR(生年月日)+7)

「もし生まれた月が4未満ならば、生まれた年に6を足し算した値を、
そうでないならば、生まれた年に7を足し算した値を表示する」
という意味です。
入社年度の関数を入学年度に書き換えるその1

4月1日生まれの人のケースを関数に追加する

これまでで9割完成しています。
4月1日生まれの人のケースを調整すればいいだけです。

上記の式の場合、4月1日生まれの人は偽の場合に属します。
これを真の場合に属すように変更します。

4月1日生まれの人は「月が4で日が1」です。これを関数で表すと「AND(MONTH(生年月日)=1、DAY(生年月日)=1)」です。
AND関数

AND関数を第1引数の条件式に加えます。
ただし、ただ加えればよい訳ではありません。
すでに「MONTH(生年月日)<4」という条件式があります。

この場合OR関数が必要です。
つまり、”「MONTH(生年月日)<4」、もしくは、
「AND(MONTH(生年月日)=4、DAY(生年月日)=1)」
の場合”という条件式を作ります。
IF関数の条件式

以上をまとめて式を作成すると、以下の通りになります。

(小学校の入学年度の場合)

IF(OR(MONTH(生年月日)<4,
AND(MONTH(生年月日)=4,
DAY(生年月日)=1)),
YEAR(生年月日)+6,YEAR生年月日+7)

入学年度の関数

エクセル上で確認すると、ご覧のとおりです。
4月1日と4月2日で区切られていることが分かります。
エクセルで入学年度の関数を入力した結果

参考に、実際に入力した式も貼っておきます。
入力した入学年度の式

AND関数とOR関数の記事
複数条件に必須のAND関数、OR関数

sponsored link



sponsored link

sponsored link

何はともあれOFFICEのことならマイクロソフト。意外とキャンペーンやっています。

コピー用紙あったかな?トナーもあったかな?事務用品はアクスルへGO!