いまさら聞けないExcelの使い方講座

【Excel】勤続年数や満年齢を手入力しちゃダメ！毎年1回1を足す簡単な仕事を自動化

2023年7月5日 06:55

勤続年数や満年齢を手入力してはいけません

ある日付から指定した日付までの年数を求める

　名簿などの表には、毎年更新される勤続年数や年齢などのデータが含まれることがありますよね。しかし、手入力で更新するのは現実的ではありません。DATEDIF関数、もしくはYEARFRAC関数を使って自動計算する仕掛けを作っておきましょう。

例えば、勤続年数（①）を手入力で毎年更新するのは大変です

　DATEDIF関数は定番なのですが、Microsoftのサポートページにもあるように、古いアプリとの互換性を保つための関数です。関数の一覧には表示されず、数式の予測入力も使えません。「=DATEDIF(」とすべて入力する必要があります。

　一方、YEARFRAC関数は予測入力が使えます。ただ、切り捨て処理の必要があるので、ROUNDDOWN関数と組み合わせて使います。2つの方法をまとめて確認してみましょう。

　DATEDIF関数を使った年数の計算は定番のテクニックです。引数［開始日］と［終了日］の期間を引数［単位］に基づいて求めます。

DATEDIF関数の構文。［開始日］から［終了日］までの年数・月数・日数を［単位］で指定した計算方法に基づいて求めます。

　引数［単位］の主な指定方法は以下の通りです。数式中で指定する時は「"」で囲みます。勤続年数や年齢を求めるときは「年」なので、「"Y"」のように指定します。

F列に入力されている日付が［開始日］、［終了日］はセルG1の日付です。勤続年数を求めるため［単位］は「"Y"」です。これらをふまえて、セルG2には「=DATEDIF(F3,$G$1,"Y")」と入力します（②）。コピーしたいので［終了日］を参照するセルG1は絶対参照で指定しています。

オートフィルで数式をコピー（③）すれば完成です。セルG1の日付を変更すれば、勤続年数をまとめて更新できます

　YEARFRAC関数も［開始日］から［終了日］までの期間を求めますが、1年間に占める割合に基づいて計算します。

YEARFRAC関数の構文。［開始日］から［終了日］の期間が1年に占める割合を［基準］の日数に基づいて求めます。

　引数［基準］は以下を指定できますが、1年を365日（うるう年は366日）として計算する「1」と指定することがほとんどだと思います。

　例えば［開始日］が「2022/1/1」、［終了日］が「2023/1/1」の場合は、365（実際の日数）／365（実際の日数）なので、YEARFRAC関数の結果は「1」です。ただし、うるう年に注意してください。［終了日］が「2024/1/1」の場合、「2」ではなく「1.9981…」となります。

　先ほどの表にYEARFRAC関数を入力すると小数点以下の数値が並びます。

セルG2に「=YEARFRAC(F3,$G$1,1)」と入力（④）して、オートフィルでコピーしました

　勤続年数を計算する基準日（［終了日］）は、セルG1の「2023/4/1」です。例えば、3行目の吉本さんの入社は「2004/4/1」（開始日）なので、ちょうど勤続19年になりますね。

　では、小数点以下を四捨五入すればいいのか？と思いますが、6行目の品田さんでは計算が合いません。切り上げてもダメです。

　［開始日］と［終了日］が同日の場合のずれを回避したいので、［終了日］に「+1」して、ROUNDDOWN関数で小数点以下を切り捨てます。修正した数式は以下のようになります。

=ROUNDDOWN(YEARFRAC(F3,$G$1+1,1),0)

セルG2の数式を「=ROUNDDOWN(YEARFRAC(F3,$G$1+1,1),0)」と修正（⑤）します

オートフィルで数式をコピー（⑥）します。セルG1の日付によって勤続年数が更新されます

　DATEDIF関数とYEARFRAC関数のどちらでも「年」の計算はできます。必要になった時に思い出してください。予測入力は使えませんが、定番のDATEDIF関数のほうが使い勝手がいいですね。