役立つ! 総務マガジン

Excel関数で和暦変換や年齢計算も自由自在

官公庁向け書類でも慌てない! 西暦の日付は一括変換できる

総務が管理している文書を官公庁や自治体に提出するときには、西暦ではなく和暦での記載が求められる場合も少なくありません。いざというときに慌てないよう、西暦で入力してある生年月日を関数で和暦に変換する方法を覚えておきましょう。また、満年齢や「今年何歳になるか」の計算方法もあわせて解説します。

[2018年 6月11日公開]

日付や年齢の記載で立ち止まらず業務効率アップ!

社会保険や税金関係の書類など、総務が行政機関向けに作成する書類は多く、それらへの日付の記載は原則として「元号を用いた和暦」となります。しかし、通常の業務では西暦のほうが分かりやすく、社員名簿の生年月日なども西暦で入力していることが多いでしょう。西暦の日付の一覧を、すぐに和暦に変換できると安心ですよね。

また、従業員の健康診断は、年齢によって内容が異なります。1人ひとりの満年齢や今年(または今年度中に)何歳になるかを目視で確認できるようにしておくことも、総務・人事の業務効率化につながるでしょう。今回は和暦変換や年齢計算の疑問を一挙に解決します!

日本独自の関数「DATESTRING」関数で和暦変換

西暦で入力してある日付を和暦に変換するには、「DATESTRING」(デートストリング)関数を使います。引数として日付が入力されたセルを指定するだけで、そのシリアル値に対応する和暦を取り出します。

日本向けに用意された特殊な関数ということもあり、[fx](関数の挿入)ボタンからは入力できず、数式バーに一部を入力したときにも候補が表示されません。セルや数式バーに、全てを手入力する必要があります。

なお、DATESTRING関数で求められるのは、シリアル値が始まる1900年1月1日にあたる明治33年1月1日から、9999年12月31日にあたる平成8011年12月31日まで。サポートが終わっていない2010以降のバージョンのExcelであれば、改元後に、通常の更新プログラム配布と同時に、対応するものと思われます。

DATESTRING関数の構文
DATESTRING(シリアル値)

(1)和暦に変換する関数を入力するための列を挿入する

和暦を挿入したい場所の列番号を右クリックし、表示されるプルダウンメニューから[挿入]をクリックします。

選択していた列が右に移動し、空白列が挿入されました。

(2)DATESTRING関数を入力する

DATESTRING関数を入力します。全て手入力となるため、「=DATESTRING(J3)」とそのまま入力しましょう。なお、関数名や引数となるセル番号は、大文字でも小文字でも問題なく認識されます。

(3)西暦に対応する和暦が表示された

DATESTRING関数により、西暦に対応する和暦が表示されました。

満年齢は生年月日と今日の日付の差分から取り出す

生年月日の一覧から満年齢を求めるには、「DATEDIF」(デートディフ)関数を使います。指定した開始日から終了日までの「差分」を、指定した単位(計算方法)に基づいて取り出す関数です。

単位には「日」「月」「年」(いずれも端数切り捨て)、「年未満の月」「月未満の日」「年未満の日」の6種類があります。単位を表すアルファベットは大文字でも小文字でもかまいませんが、必ず「”」(ダブルクォーテーション)で囲みます。

引数「単位」で指定する値
“Y” 満年数
“M” 満月数
“D” 満日数
“YM” 1年に満たない月
“YD” 1年に満たない日
“MD” 1月に満たない日

開始日には入力済みの生年月日を指定し、終了日には今日の日付を指定します。ここで「TODAY」(トゥデイ)関数を組み合わせると、Excelファイルを開いたときに今日の日付が自動的に更新され、現在の満年齢をいつでも確認できるようになります。TODAY関数に引数は不要ですが、丸括弧の中にスペースなどが入らないように注意しましょう。

DATEDIF関数の構文
DATEDIF(開始日, 終了日, 単位)
TODAY関数の構文
TODAY()

(1)DATEDIF関数を入力する

DATEDIF関数を入力します。この関数も[fx](関数の挿入)ボタンなどから入力できないため、セルや数式バーに手入力します。TODAY関数も組み合わせると「=DATEDIF(J3,TODAY(),”Y”)」といった数式で表せます。

(2)生年月日と今日の日付から満年齢が求められた

DATEDIF関数とTODAY関数により、満年齢が求められました。上図は2018年3月時点の画面であり、この社員は2018年の誕生日をまだ迎えていないため、「40」という数字が取り出されています。

「今年何歳になるか」を求めるには工夫が必要

満年齢ではなく「今年何歳になるか」を求めるには、DATEDIF関数の終了日として今日ではなく、今年の末日を指定します。ただし、シリアル値で指定する必要があるため、ひと工夫が必要です。

具体的には、「年」「月」「日」の三つの引数で表される日付をシリアル値に変換する「DATE」(デート)関数を使います。DATE関数の引数として、例えば「2018」「12」「31」を指定すれば、2018年12月31日のシリアル値である「43465」が求められます。

ただ、それでは2019年になったときに「年」の引数を修正しなければなりません。ここではさらに、シリアル値から「年」の部分の数字だけを取り出す「YEAR」(イヤー)関数も使うことにします。

YEAR関数とTODAY関数を組み合わせれば、今日の日付から「年」だけを取り出せます。それをDATE関数の「年」の引数にすれば、1年ごとに修正する必要がなくなり、メンテナンスフリーのファイルが完成します。

上記を踏まえると、最終的な数式は「=DATEDIF(J3,DATE(YEAR(TODAY()),12,31),”Y”)」のようになります。このように、関数の中に関数を入れ子状に入力することを「関数のネスト」と言います。

DATE関数の構文
DATE(年, 月, 日)
YEAR関数の構文
YEAR(シリアル値)

(1)DATEDIF関数を入力する

DATEDIF関数、DATE関数、YEAR関数、TODAY関数をネストした数式を入力します。「=DATEDIF(J3,DATE(YEAR(TODAY()),12,31),”Y”)」をそのまま手入力しましょう。

(2)今年何歳になるかが表示された

関数が正しく入力され、「今年何歳になるか」が表示されました。2018年3月時点でのこの社員の満年齢は「40」でしたが、今年の末日には「41」歳になることが分かります。

(3)フィルハンドルをダブルクリックして数式をコピーする

満年齢または「今年何歳になるか」を数式で求めたら、DATESTRING関数で求めた和暦と一緒にオートフィルでコピーしましょう。ここではセルK3からL3をまとめて選択し、表示されたフィルハンドルをダブルクリックします。

(4)和暦と年齢がまとめてコピーされた

オートフィルにより、和暦と年齢を求めた数式が一気にコピーされました。オートフィルだけでも便利ですが、2項目まとめて実行すれば、さらに時短できますね。

関数のネストは丸括弧の数に注意すれば失敗しない

生年月日から年齢を求めるときには、TODAY関数やDATE関数を駆使し、単体の関数では難しい処理を行うことができました。

このような関数のネストを使いこなせば、Excelでできることの幅がさらに広がります。多少複雑ではありますが、「一つの関数には一組の丸括弧」という原則をしっかり覚えておけば、失敗することはありません。

関数を自由に組み合わせられるようになると、作業効率も上がっていきます。ぜひチャレンジしてみてくださいね。

協力メディア

できるネット(https://dekiru.net)

もっと関数のスキルを磨きたい! という人はぜひ大塚商会のExcel教室へ

大塚商会では、企業の人材育成をトータルにサポートする「人材育成支援サービス」をご提供しています。ITやCAD、ビジネススキルなどさまざまなジャンルをご用意しており、もちろんExcelを学ぶコースも充実しています。

Excelを基礎から応用まで体系的に学べる「標準コース」や、関数やマクロなど目的に応じて学べる「目的別コース」をご用意しています。大塚商会の研修施設で定期開催されているオープンコースのほか、貴社の業務に合わせてカスタマイズした企業研修(ご来場または出張)も可能です。

Excel研修の詳細情報を見る

Excelをもっと便利に活用するにはOffice 365をおすすめします

マイクロソフトの「Office 365」は、ExcelやPowerPointを始めとしたOffice製品、メール、スケジュール管理などをクラウド上で使用できます。非常に利便性の高いシステムを実現することが可能です。

Office 365 について見る

おすすめの記事