【PowerQuery】生年月日から年齢を算出

パワークエリ1年生の苦悩シリーズ。

生年月日から年齢を計算する方法が案外めんどかったので、ここに記録します。（忘れるから。自分の備忘録もかねて）

１．生年月日から年齢を計算する
２．うるう年を勘案した計算方法
３．日付が8桁の数値の場合の年齢の算出方法
- （１）8桁数値の日付を日付値に変更してから年齢を計算する方法
- （２）日付に変換せず8桁数値のまま年齢を計算
４．最後に
５.参考記事

１．生年月日から年齢を計算する

こちらを参考とさせていただいています。ありがとうございます。
https://dono-log.com/powerquery-date-calculation/#index_id4

ざっくり以下の流れで年齢を算出していますね。
①今日の日付を取得
②今日の日付から誕生日を減算(日付の引き算)
③減算した日数を「期間」→「合計年数」で年単位にする
④小数点以下を切り捨て

（１）生年月日から年齢を算出する手順

①生年月日のある表をテーブルにして、「データ」→「テーブルまたは範囲から」でテーブルを読みこんでパワークエリエディタを起動します。生年月日のデータ型は「日付」にします。

②「列の追加」→「カスタム列」で以下のコードを入力します。
= Date.From(DateTime.LocalNow()) – [生年月日]
※列[生年月日]は実際の列名に合わせてください。
DateTime.LocalNow() で「今現在の日時」を算出し、Date.Fromでそこから日付データを取り出し。
そして、今日の日付から生年月日列の日付を減算しています。
※ここで、今日の日付ではなく別の列の日付値を使用する場合は（その列の列名が「基準日」とする場合）
= Date.From(DateTime.LocalNow()) – [生年月日]
　　　　↓
= [基準日] – [生年月日] で良いです。

直接日付を手入力で指定したい場合は
= #date(2024,3,31) – [生年月日]
のようにします。

③追加した「年齢」の列を選択した状態で、「変換」→「期間」→「合計年数」をクリックします。

④生年月日から今日の「期間」の「年数」が算出されました。
小数点を持った数値になりますので、「年齢」列を選択した状態で「変換」→「丸め」→「切り捨て」で、整数にします。

これで年齢が算出されました。

（２）ステップを短くしてみた

年齢を計算する場面はよくあります。使いまわしができるようにしたいです。
今のままではステップ数が多く、なんだかなという感じなので、短くしてみました。

↓元のコード

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"顧客ID", Int64.Type}, {"氏名", type text}, {"生年月日", type date}}),
    追加されたカスタム = Table.AddColumn(変更された型, "年齢", each Date.From(DateTime.LocalNow()) - [生年月日]),
    計算された合計年数 = Table.TransformColumns(追加されたカスタム,{{"年齢", each Duration.TotalDays(_) / 365, type number}}),
    切り捨て = Table.TransformColumns(計算された合計年数,{{"年齢", Number.RoundDown, Int64.Type}})
in
    切り捨て

↓変更後のコード
「追加されたカスタム」「計算された合計日数」「切り捨て」を1行にまとめてステップ名を「年齢算出」としました。…短くはなったけど逆にわかりにくい？

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"顧客ID", Int64.Type}, {"氏名", type text}, {"生年月日", type date}}),
    年齢算出 = Table.AddColumn(変更された型, "年齢", each Number.RoundDown(Duration.TotalDays(Date.From(DateTime.LocalNow()) - [生年月日])/365))
in
    年齢算出

（３）カスタム関数にしてみた

パワークエリエディタで「空のクエリ」でクエリを作成します。
詳細エディターに以下のコードを入力します。
※//で始まる部分はコメントです。関数には関係ないので省略可能です。

(生年月日 as date, optional 基準日 as date) =>
let
    //基準日省略の場合は今日の日付
    基準日_ = if 基準日 = null then　Date.From(DateTime.LocalNow()) else 基準日,
    //基準日と生年月日の乖離日数を計算
    乖離日数 = Duration.TotalDays(基準日_ - 生年月日),
    //乖離日数を365で割って年数を算出し小数点以下を切り捨て
    年齢 = Number.RoundDown(乖離日数 / 365)
in
    年齢

カスタム関数ができました。クエリ名を「年齢算出」に変更しました。

クエリ「年齢算出」を選択すると、パラメーターを入力する画面となります。
生年月日は指定が必須、基準日は省略可能です。
下図は基準日を指定した場合、指定しない場合のイメージです。
基準日を指定しない場合は現在日付での年齢が計算されます。

（４）カスタム関数を使用する

カスタム関数を使用して（１）のクエリを書き換えます。

（１）のクエリを表示します（関数省略前の状態でやります）

「計算された合計年数」「切り捨て」のステップを削除します。

「追加されたカスタム」の関数を書き換えます。

これで、今日現在の年齢を算出する列が追加されます。

基準日が現在日時ではなく、指定する場合は以下のように書きます。

基準日の列がある場合：年齢算出([生年月日],[基準日])

基準日を数式内で直接指定する場合：年齢算出([生年月日],#date(2024,4,1))

（５）カスタム関数の解説

(生年月日 as date, optional 基準日 as date) =>
引数をとる部分です。optional を付けると省略可能となります。
入力値は日付型に限定しています。

基準日_ = if 基準日 = null then Date.From(DateTime.LocalNow()) else 基準日,
新たに変数「基準日_」を作成し、引数の「基準日」が指定されていない場合、現在の日付を「基準日_」に代入、指定されている場合はそのまま「基準日」を「基準日_」に代入

乖離日数 = Duration.TotalDays(基準日_ – 生年月日),
「基準日_」から「生年月日」を減算→戻り値は「期間型」（日数.時：分：秒の型）となるので、Duration.TotalDaysで日付を取り出し。
※ここで日付のみ取り出しとしないと次のステップでエラーとなる

年齢 = Number.RoundDown(乖離日数 / 365)
乖離日数を1年を365日とし、365で割る→年数が算出される。
Number.RoundDown で算出された年数の小数点以下を切り捨てする。
※365で割るときに期間型のデータのままだと「数値じゃないから計算できないよ！」と怒られる。

（６）この計算方法の問題点

365で割って年数を出してますよね。だいたいの年齢がわかればいいんだ、ならいいんだけど、うるう年の日は366日なので、ちょっと早めに年を取る場合がある。50を過ぎたおばさんにとっては「え？まだ50なのに、51歳って出てる。。。」とショックなことになる場合がある。

２．うるう年を勘案した計算方法

ネットを検索すると、いろいろ出ています。が、私にもわかる単純な計算方法にしました。

（１）コード（カスタム関数）

カスタム関数でやってみました。
なお、2/29生まれの場合、うるう年以外は3/1に年を取る計算式となっています。正式には正しいのかはわかりません。おばさん的には歳をとるのは遅いに越したことはない。。。

(生年月日 as date, optional 基準日 as date) =>
let
    //基準日省略の場合は今日の日付
    基準日_ = if 基準日 = null then　Date.From(DateTime.LocalNow()) else 基準日,
    //基準日と誕生日を比較し、減算年数を1or0を算出
    //　基準日が誕生日より小さい場合減算年数1
　　//　誕生日と基準日が同じ、または基準日のほうが大きい場合は1を減算しない
    減算年数 = if Date.Month(生年月日) < Date.Month(基準日_) then 0 
        else if Date.Month(生年月日) > Date.Month(基準日_) then 1
        else if Date.Day(生年月日) <= Date.Day(基準日_) then 0
        else 1
        ,
    //年単位で引き算、誕生日が基準日より小さい場合減算1
    年齢 = Date.Year(基準日_) - Date.Year(生年月日) - 減算年数
in
    年齢

以下のように書いても同じです。

(生年月日 as date, optional 基準日 as date) =>
let
    //基準日省略の場合は今日の日付
    基準日_ = if 基準日 = null then　Date.From(DateTime.LocalNow()) else 基準日,
    //年単位で引き算、誕生日が基準日より小さい場合減算1
    年齢 = Date.Year(基準日_) - Date.Year(生年月日) - (
        if Date.Month(生年月日) < Date.Month(基準日_) then 0 
        else if Date.Month(生年月日) > Date.Month(基準日_) then 1
        else if Date.Day(生年月日) <= Date.Day(基準日_) then 0
        else 1
    )
in
    年齢

（２）使用例

テーブル１のクエリで利用してみます

結果　今日は2024年3月17日です。3月の誕生日の人が微妙に年齢が違っています。
1974年生まれの人は年齢が近いのでわかる。左の計算ではまだ誕生日が来ていないのに50になってる。キイイ。

（３）関数の解説

１のカスタム関数と同じところは割愛し、誕生日と基準日を比較し、1を減算するかどうかの減算年数を算出する部分について、解説します。

減算年数 = if Date.Month(生年月日) < Date.Month(基準日_) then 0
else if Date.Month(生年月日) > Date.Month(基準日_) then 1
else if Date.Day(生年月日) <= Date.Day(基準日_) then 0
else 1

同じ1973年生まれ
誕生日が1/10のAさん、3/1のBさん、3/31のCさん、5/10のDさん、
今日の日付(基準日)が2024年3月17日、の場合で考えてみます。

「年」の差は 2024 – 1973 で 51 です。

1行目：月で比較し誕生月が基準日の月より小さい場合は1を減算しません。
Aさん1月、基準日の月3月→Aさんは減算しないため、51歳です。
誕生日が過ぎているので歳をとったわけですね。

2行目：月で比較し誕生月が基準日の月より大きい場合は1を減算します。
Dさん5月、基準日の月3月です。1を減算するため、50歳です。
誕生日がまだ来ていない、ということですね。

3行目：ここからは基準日と誕生月が同じ場合の計算となります。日にちで比較し誕生日が基準日以下の場合、1を減算しません。
Bさん誕生日 3/1、基準日 3/17 です。1を減算しないため、51歳です。
誕生日を過ぎた、からですね。誕生日当日もここに当てはまります。

4行目：１～3行目に当てはまらなかった場合、つまり日にちで比較し誕生日が基準日より大きい場合、1を減算。
Cさん誕生日 3/31、基準日 3/17 です。1を減算し、50歳です。
まだ誕生日が来ていないから1歳若いです。

３．日付が8桁の数値の場合の年齢の算出方法

日付が 20240331 のような8桁の数値の場合。システムから吐き出されるデータによくある感じの。

（１）8桁数値の日付を日付値に変更してから年齢を計算する方法

列の追加＞例からの列で簡単に日付に変換できます。
日付に変換したら上記１または２の方法等で年齢の計算ができます。

日付値に変換したステップの数式を見てみると、
= Table.AddColumn(変更された型, “日付”, each Date.From(Text.From([生年月日], “ja-JP”)), type date)
となっています。

Date.From で日付値に変換しています。
Text.From で数値の19850510を文字列に変換しています。

Date.From(Text.From(19850510))
ということになります。

もともとの８桁の日付が数値ではなく文字列値の場合、
Date.From(“19850510”)
で大丈夫です。Text.Fromは不要です。

こちらで勉強させていただきました。ありがとうございます。
https://manabitoasobi-100.com/excel-powerquery-date-conversion2/#google_vignette

（２）日付に変換せず8桁数値のまま年齢を計算

日付に変換して。。。とかやらずにそのまま引いて1万で割って、切り捨てしちゃえば（下4桁を無視）良いのでは。と、やってみた結果

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル5"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"顧客ID", Int64.Type}, {"氏名", type text}, {"生年月日", Int64.Type}}),
    基準日 = Number.From(Date.ToText(Date.From(DateTime.LocalNow()),"yyyyMMdd")),
    年齢算出 = Table.AddColumn(変更された型, "年齢", each Number.RoundDown((基準日 - [生年月日])/10000))
in
  年齢算出

むしろ、基準日を今日の日付にするときにちょっと手間取った感がある。

基準日も同じ8桁の数値の場合は話が簡単。

Number.RoundDown(([基準日] – [生年月日] )/10000)

で、よい。

なお、この場合、365で割るというステップがないので、うるう年問題も大丈夫。
ただし、2/29生まれの人は、うるう年以外は3/1で歳をとる計算結果になります。

以下、「基準日」列と「生年月日」列が8桁数値で、カスタム列の追加と、変換の「丸め」の切り捨てを利用して年齢列を追加したコード

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル3"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"顧客ID", Int64.Type}, {"氏名", type text}, {"生年月日", Int64.Type}, {"基準日", Int64.Type}}),
    追加されたカスタム = Table.AddColumn(変更された型, "年齢", each ([基準日] - [生年月日])/10000),
    切り捨て = Table.TransformColumns(追加されたカスタム,{{"年齢", Number.RoundDown, Int64.Type}})
in
    切り捨て