22 September, 2020

Excel関数を使用して年齢(年)を計算する3つの方法

Excelで人事管理と給与統計のために、入社日と退職日を使用し、入社年数を計算することは有り触れた話です。著者は仕事の関係で、その操作よく使用しました。読者が参考できるように、次には3つの計算方法を説明します。

DAYS360、CEILINGとTRUNC関数を使います。

関数の分類日付/時刻
説明この関数は 1 年を360 日 (30 日×12 か月)として、指定下2つの日付の間の日数を返します。
年齢(年)を計算する場合に、この関数を使用すれば便利です。
書式DAYS360(開始日,終了日,[方式])
引数開始日:間隔を求めたい2つの日付を指定します。
終了日:間隔を求めたい2つの日付を指定します。
方式:計算にヨーロッパ方式(TRUE)と米国NASD方式(FALSE)のどちらを使用するかを、論理値で指定します。
DAYS360関数
関数の分類数学/三角
説明指定された基準値の倍数のうち、最も近い値に数値を切り上げます。
書式CEILING(数値,基準値)
引数数値:対象となる数値を指定します。
基準値:倍数の基準となる数値を指定します。
注意点この関数は、絶対値の増加方向に沿って数値の最も近い(または最小倍数の基準値)整数を計算できます
CEILING関数
関数の分類数学/三角
説明数値の小数部を切り捨てて、整数または指定した桁数に変換します。
書式TRUNC(数値,桁数)
引数数値:小数部を切り捨てる数値を指定します。
桁数:切り捨てを行った後の桁数を指定します。桁数の既定値は0(ゼロ)です。
注意点この関数は、絶対値の増加方向に沿って数値の最も近い(または最小倍数の基準値)整数を計算できます
TRUNC関数

*この記事では、上記の関数を説明するにあたって、「社歴」と「在職期間」二つの概念を取り入れました。記事をよりよく理解するために、まず両者の違いについて説明いたします。

「社歴」とは、退社日引く入社日で得た年数の結果が小数点がある場合、小数点を切り捨ててから、年数をプラス1で得た結果です。

「在職期間」とは、退社日引く入社日で得た年数の結果が小数点がある場合、小数点を切り捨て得た結果です。

そのため、「社歴」と「在職期間」が場合によって、数1の違いが生じます。

1、社歴

図表にあるEの列で、「社歴」を計算するため、E2セルに次の書式を入力します。

=CEILING((DAYS360(C2,D2))/360,1)

C2とD2は就職日と退職日であり、「DAYS360(A1、B1)」数式中の「A1、B1」に対応し、2つの日付の間の日数を返します。「(DAYS360(A1、B1))/ 360」数式は1年360日として社歴を計算します。社歴は、年を単位として計算されるため、小数点が含まれる場合があります。小数点が含まれる場合はCEILING関数を使用して計算結果を絶対値が増加する方向に向け小数点を切り捨て、年数をプラス1で丸め、「社歴」を返します。

2、在職期間

図表にあるEの列で、「在職期間」を計算するため、E2セルに次の書式を入力します。

=TRUNC((DAYS360(C2,D2))/360,0)

数式では社歴と年功序列を計算する方法は同じです。

TRUNC関数は「(DAYS360(C2、D2))/ 360」数式の計算結果の小数部を切り捨てて、「在職期間」を返します。 計算結果が小数第1位を保留する必要がある場合は、数式を「= TRUNC((DAYS360(A1、B1))/ 360、1)」に変更すればいいです。

YEARとRIGHT関数

関数の分類日付/時刻
説明シリアル値(日付)に対応する年を返します。
書式YEAR(シリアル値)
引数Microsoft Excelで使用される日付/時刻コードを指定します。
YEAR関数
関数の分類文字列操作
説明文字列の末尾から指定された文字数の文字を返します。
書式RIGHT(文字列,文字数)
引数文字列:取り出す文字が含まれる文字列を指定します。
文字数:取り出す文字数を指定します。省略すると、1を指定したと見なされます。
RIGHT関数

1、在職期間

YEAR関数とRIGHT関数の特性により、組み合わせた数式は「在職期間」のみを計算でき、「社歴」は計算できません。数式は次のようになります。

=RIGHT(YEAR(D2-C2),2)

セルのD2とC2は退職日と就職日に対応します。(DAYS360関数があべこべです。)「YEAR(A1-B1)」数式は、2つの日付の間の年数を計算します。「RIGHT(YEAR(A1-B1),2)」数式は年数の最後の2桁を右から左に引き出します。(在職期間は通常1桁または2桁です。)

YEAR関数の計算結果は数字「19」が含まれるため、RIGHT関数を使用し、削除する必要があります。

計算結果は1桁で記録される場合は、在職期間の値の前に「0」を追加されます。年数の前に0が追加するのは常識と違うため、これも上記の数式の欠点です。

NとINT関数

関数の分類情報
説明この関数の分類は情報で、セルにあるデータを変換できます。非数値を数値に、日付をシリアル値に、TRUEの場合は1に、それ以外の場合は0に変換します。
書式N(値)
引数変換する値を指定します。
N関数
関数の分類数学/三角
説明切り捨てて整数にした数値を返します。
書式INT(数値)
引数切り捨てて整数にする実数を指定します。計算結果を整数にするの場合は、この関数を使って便利です。
INT関数

INT関数の特性により、N関数とINT関数が連携する場合には、「在職期間」のみ計算できます。書式は「=INT((N(D2-C2))/365)」です。数式中のD2とC2は退職日と就職日に対応します。「N(D2-C2)」数式は在職期間の具体的な日にちを計算します。数式の「(N(A1-B1))/365」は「N(A1-B1)」の計算結果を在職期間に変換します。実際の「在職期間」は年として小数点が含まれるため、INT関数を使用し、小数を切り捨てます。

在職期間を計算します

実際の在職期間=退職日-就職日(=2016/12/27-YEAR(A1)」
関数の分類日付/時刻
説明シリアル値(日付)に対応する年を返します。
書式YEAR(シリアル値)
引数Microsoft Excelで使用される日付/時刻コードを指定します。
YEAR関数

1、「在職期間」を計算します

社歴は勤務年数の増加によって、増加するものです。例えば、一年が増加するとプラス1になります。。YEAR関数を使用し、在職期間を計算する数式は次のようになります。

=YEAR(D2-C2)

数式のD2とC2は退職日と就職日に対応します。YEAR(C2)とYEAR(D2)は2つの日付に対応し、差し引いて実際の在職期間を返します。

返す結果は変のために、その数式を「=YEAR(D2-C2)-1900」に変更します。

2、年齢

年齡=2つの日付の間の差(誕生日、現時点)

年齡=DATEDIF(A1,TODAY(),"Y")

年齢=(現時点-生年月日)/365、小数を切り捨てます。

数え年=ROUNDDOWN((TODAY()-C2)/365,0)
年齢=ROUNDDOWN((TODAY()-C2)/365.25,0)

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です