パワークエリで生年月日から年齢を計算する方法※うるう年対応版あり※【Excel PowerQuery】

パワークエリで、生年月日の日付をもとに、ある基準日時点の年齢を計算する方法を紹介します。

ちなみに、クエリを使わず、ワークシート上にある生年月日と基準日から年齢を計算したい場合は『DATEDIF』関数や、

『YEARFRAC』関数を使って求めることができます。

厳密に日単位でみると、『DATEDIF』と『YEARFRAC』では、歳を取るタイミングが若干違ってきます。

大雑把な年齢の傾向を知りたい、といった程度であればどちらでも構いませんが、日単位で正確な年齢を求めたいのであれば『DATEDIF』の方が正しいですね。

シート上に存在する生年月日から年齢を求めるのであれば、上に書いたような関数を利用すればいいです。

一方、他のExcelブックやCSVファイル等からクエリで直接データを取得した場合、パワークエリの中でワークシート関数は使えませんので、パワークエリ上で年齢を計算することになります。

その方法をこれから解説します。

動画で学ぶExcelパワークエリ・パワーピボット

Excelパワークエリ講座 入門編 実習でパワークエリの威力を体験 / 千葉悠市 @NO‼︎マクロ、NO‼︎VBAでExcelを効率化

やりたいこと

クエリで取得したデータの生年月日から、基準とする日付時点の年齢を、パワークエリ上で計算したい。

手順1. データの取得

生年月日と、年齢を求める基準となる年月日が含まれているデータを、パワークエリに取得し、データ型を日付に変換します。

※パワークエリにデータを取得する方法や、データ型を変更する方法が分からない場合は、パワークエリの基本的な使い方を解説している動画の中で説明していますので、そちらをご覧ください。

手順2. 日数の列を算出

『生年月日』の列を選択してから、列の追加＞日付＞期間を押します。

手順3. 基準日からの日数に変更

この時点で追加された『期間』というのは、『生年月日』の日付から今現在の日付までの日数です。

それぞれの『生年月日』から今現在の年齢を計算したいのであれば、手順3はスキップしてしてください。

今現在ではなく、『基準年月日』時点の年齢を計算したいのであれば、数式バーを以下のように変更します。

= Table.AddColumn(変更された型, "期間",
each Date.From(DateTime.LocalNow()) - [生年月日], type duration)
　　↓↓↓
《太字部分を書き換える》
　　↓↓↓
= Table.AddColumn(変更された型, "期間",
each [基準年月日] - [生年月日], type duration)

これで『期間』の値が、生年月日から今現在までの日数ではなく、生年月日から基準年月日までの日数に変更されます。

手順4. 期間を年単位に変換

『期間』の値は『生年月日』から『基準年月日』までの日数なので、これを年数に変換します。

手順5. 年数の小数点以下を切り捨て

この時点で『期間』の値は『生年月日』から『基準年月日』までの年数にはなっているのですが、小数点以下まで表示されています。

普通は年齢を言う場合「22.4歳」とは言いませんので、この小数点以下の数値は切り捨てます。

手順6. ヘッダー名を変更

最期に『期間』となっているヘッダー名を『年齢』に変更します。

おわり？

以上の手順で、『生年月日』から『基準年月日』時点の年齢をパワークエリ上で求めることができました。

ところが、実はこの方法だと正確な年齢を計算することはできていません。

こんな感じで、ワークシート関数のDATEDIFで計算した年齢と、先ほどのクエリによって計算した年齢とでは、歳を取る日が微妙にズレます。

どちらが正しいかといったら、DATEDIFで計算した年齢ですよね。

誕生日が2011/1/15の人は、2021/1/15に10歳になりますから。

うるう年問題

なぜクエリで計算した場合は、実際よりも早い日付で歳をとってしまうのでしょうか？

それは、クエリで日数を年数に変換した部分のステップが原因です。

このステップの数式を見てみると「 / 365」と書いてある部分があります。

つまり、厳密に言えば、これは日数を年数に変換しているわけではなく、日数を「÷365」しているだけということです。

普通の年であれば1年は365日なので÷365で問題ありません。

ところが4年に1度はうるう年がありますので、1年の日数が366日の場合があります。

先ほどのクエリの場合は、『生年月日』から『基準年月日』の間にうるう年があるかどうかは一切考慮していませんので、本来であれば366日経過した場合にはじめて1歳とカウントするところを、365日経過した時点で1歳とカウントしてしまっている、ということです。

『生年月日』から『基準年月日』が離れているほど、その間にうるう年が挟まる回数が増えますので、年齢カウントの日付が前にズレていきます。

大雑把な年齢の傾向を分析したい場合などは、年齢のカウントが数日分ズレても大して影響ありませんので、簡易的に上記の方法で年齢をカウントして問題ないとは思いますが、例えば法定書類に使う年齢を計算したい場合などは、『基準年月日』の時点での年齢を厳密にカウントしなければいけませんので、上で解説したようなクエリを使うことはできません。

パワークエリ上で、うるう年を考慮したうえで、厳密に年齢をカウントするためのカスタム関数を作成してみましたので、ここから下ではそのカスタム関数と使い方を解説します。

カスタム関数クエリによる年齢計算 ※うるう年対応版

『生年月日』と『基準年月日』を含むテーブルをクエリで取得します。

空のクエリを作成します。

作成した空のクエリで詳細エディターを開きます。

詳細エディターを、以下のカスタム関数のコードに置き換えます。