エクセルで社員名簿や顧客名簿を作るときに、年齢や勤続年数を入力することはないでしょうか?
これらの数字は毎年変わっていくものなので、年が変わるごとに修正するのは効率的な作業とは言えません。この記事では、エクセルで年齢を自動計算する方法をご紹介します。この記事でご紹介する関数を覚えておけば、勤続年数などを算出する時にも使うことができます。ぜひ、押さえておいてください。
※本文に記載した設定方法はすべてOffice2013の操作です。
1.生年月日をもとに、「DATEDIF関数」で現在の年齢を算出する
DATEDIF関数を使うと生年月日から自動で、現在(本日時点)の年齢を求めることができます。
【構文】
=DATEDIF(生年月日,基準日,”単位”)
では、算出方法を詳しく見ていきましょう。
【完成イメージ】
【算出方法】
1.E3セルに「=DATEDIF(D3,TODAY(),”Y”)」と入力すると年齢を求めることができます。”Y”は年齢を表す単位で「満年齢」で計算されます。
2.入力した式を残りのE4からE12のセルにオートフィルでコピーします。
2.指定した日の時点での年齢を算出したい場合
例えば、「2020年4月1日時点」の年齢を算出したい場合です。
【完成イメージ】
【算出方法】
1.E1セルに、「2020/4/1」と入力します。(場所はどこでも構いません)
2.E3セルに「=DATEDIF(D3,E1,”Y”)」と入力します。これで指定した日の年齢を算出することができます。
3.他の人の年齢も「2020年4月1日時点」にしたい場合、必ず基準日であるE1セルを参照するように、「E1」の間に、絶対参照を表す「$(ドルマーク)」を付け、構文を「=DATEDIF(D3,E$1,”Y”)」とします。
4.オートフィルでコピーをし、他の人の年齢を算出します。
なお、年齢計算された値に「歳」とつけるときは、セルに「=DATEDIF(D3,基準日,”Y”)&”歳”」と入力すると自動的に「●歳」という表記にすることができます。
3.年齢の表示を「●歳●ヶ月」としたい場合
年齢を●歳●ヶ月と詳しく計算したい場合の構文は
=DATEDIF(生年月日,today(),”Y”)&”歳”&DATEDIF(生年月日,today(),”YM”)&”ヶ月”
です。E3セルで算出した後、残りのセルにオートフィルでコピーします。
なお、”YM”は○ヶ月などの1年未満の月数を表示させたいときに入力する引数です。
4.年齢計算で知っておくと便利なこと
・DATEDIF関数の単位の引数
下記の引数を覚えておくと、「●歳●ヶ月●日」や「●年●ヶ月●日」などの表示をしたい場合に便利です。一覧にしましたので、ぜひ参考にしてください。
“Y”:期間内の年数
“M”: 期間内の月数
“D”:期間内の日数
“YM”:開始日から終了日までの1年未満の月数
“YD”:開始日から終了日までの1年未満の日数
“MD”:開始日から終了日までの1ヶ月以内の日数
※単位の引数は””(ダブルクォーテーション)で囲まなければなりません。
・DATEDIF関数で気をつけたいポイント
DATEDIF関数を使うときは、次の点に注意しなければなりません。
1、うるう年を含む計算
DATEDIF関数は、一定の問題をかかえている関数です。範囲内にうるう年などがあると間違った結果を出すことがあります。給与など正確性が必要な計算をする場合は、利用を控えるか、計算結果が正しいか再確認しましょう。
2、日本の法律の年齢計算
日本の法律(年齢のとなえ方に関する法律)では年齢を誕生日の前日に加算します。そのため、このルールにのっとる場合は、「=today()」のあとに「+1」を追加しましょう。また、DATEDIF関数は誕生日を過ぎると自動計算されるため、年齢が増えてしまいます。1日違うだけでも計算結果は大きく変わってきます。これらも踏まえて法律と一般慣習のどちらを基準にして運用するかよく考えてから利用しましょう。
=DATEDIF(D3,today()+1,”Y”)
・和暦で年齢計算する場合
ここまで読んでDATEDIF関数で年齢計算をするには生年月日を西暦で入力しなければならないと思っていませんか。安心してください。セルの表示形式を変更すれば和暦でもDATEDIF関数で年齢を求めることができます。以下の手順でセルの表示形式を和暦に変更してみてください。
【算出方法】
1.該当のセルを右クリックして「セルの書式設定」をクリックし、ダイアログを開きます。
2.「表示形式」タブから以下を選択して「OK」をクリックします。
・分類:日付
・種類:平成○年○月○日
・カレンダーの種類:和暦
・DATEDIF関数を使わずに年齢計算を行う方法
少し数式が長くなりますが、DATEDIF関数以外でも関数でも年齢計算が行えます。
・A2セル(今日の日付):2017/9/7
・B2セル(生年月日):1978/12/5
・C2セル:今日の年齢を計算
1.年齢計算は「今年-誕生年」を引くことが基本であるためYEAR(A2) – YEAR(B2)で算出します。
2.今日の日付が誕生日を過ぎているかどうかを比較するには年を統一しなければなりません。生年月日の年を「2017/12/5」に変換するのが以下の式です。
=DATE(YEAR(A2),MONTH(B2),DAY(B2))
3.次にA2(今日の日付)がB2セル(生年月日)を過ぎているか比較するのが以下の式です。
=A2<DATE(YEAR(A2),MONTH(B2),DAY(B2))
今日の日付の結果が小さければ今年の誕生日は来ていないため、上記の計算式から「-1」を引きます。
=IF(A2<DATE(YEAR(A2),MONTH(B2),DAY(B2)),-1,0))
4.1~3の式を組み合わせてC2セルに
=YEAR(A2)-YEAR(B2)+IF(A2<DATE(YEAR(A2),MONTH(B2),DAY(B2)),-1,0)
を入力します。
DATEDIF関数は年齢以外にも入社日から勤続年数や契約日から契約年数などさまざまなシーンで活用できます。慣れてしまえば難しい関数ではないため、これを機に覚えておきましょう。
監修者:たくさがわつねあき(著者・インストラクター)
パソコン教室を運営する傍ら、初心者への直接の指導経験を元に執筆活動を行う。2008年「これからはじめるパソコン超入門の本」で著書デビュー。代表作に「これからはじめるエクセル超入門の本」の他、「たくさがわ先生が教える」シリーズ(技術評論社)、「大きな字だからスグ分かる」シリーズ(マイナビ)がある。指導経験と自筆の漫画を活かした執筆が得意で、「たくさがわ先生が教えるパソコン超入門の本 Windows10 & Excel & Word対応版」など、初心者向け入門書やビジネス向け文庫本、計20冊を刊行。内容はiPad、Excel、ショートカット、困ったを解決、デジカメ、安全対策など多岐にわたる。いずれもメディアで紹介され、好調に売上を伸ばしている。寄稿に、「孫育てのツボ – デジタル機器を使う」(毎日新聞)、「どうしてる?パスワード&暗証番号」(女性セブン)などもある。
文:リクナビネクストジャーナル編集部