今回は、ノンプロ研PowerQuery講座第5回目から、
PowerQueryを使って特定の基準日の年齢を算出する方法をピックアップしてレビューします。
講師陣の回答例がとても参考になったので紹介しやす。
PowerQueryで基準日時点の年齢を算出する
特定の基準日において何歳なのか？知りたい、というのは実務においてよくあることです。
たとえば….
そろそろ今年の健康診断の実施時期だろ。従業員の健康診断のスケジュール入れとけよ。
へ～い。（定期健康診断か～。年齢によって生活習慣病診断、若年層健診とわけて病院予約しね～とだな。）
たしか名簿ファイルがあったはず。PowerQueryで、全メンバーの2022年3月31日(年度末)時点の「年齢」を算出してみよう！
回答例1 誕生日数を出して年齢を求める
生まれてから基準日までの日数を算出し、365日で割り小数点以下を切り捨てることで年齢を算出します。
手順は以下の３stepです。
- 「基準日」列を追加（2022/03/31）
- 「誕生日数」列を追加（基準日列-生年月日列で算出）
- 「年齢」列を追加（誕生日数÷365で算出し、小数点以下を切り捨てる）
①.基準日列を追加
リボン「列の追加」から「カスタム列」をクリックします。
カスタム列のダイアログが開くので、
新しい列名を「基準日」
カスタム列の式「＝”2022/03/31″」を入力。
「構文エラーが検出されませんでした。」を確認したら、OKをクリックします。
「2022/03/31」が入力された基準日列が作成されました。
データの型を日付に変更します。
②.誕生日数列を追加
基準日列から生年月日列を引くと、誕生日数が算出できます。
リボン「列の追加」から「カスタム列」をクリックすると、カスタム列のダイアログが開くので、
- 新しい列名「誕生日数」
- カスタム列の式「＝[基準日]-[生年月日]」
- 「構文エラーが検出されませんでした。」を確認したら、
- OKをクリックします。
誕生日数が算出された列が追加されたら、データ型を整数に変更します。
③.年齢列を追加
誕生日数を1年の365日で割り、小数点以下を切り捨てることで年齢を求めることができます。
リボン「列の追加」から「カスタム列」をクリックすると、カスタム列のダイアログが開くので、
- 新しい列名「年齢」
- カスタム列の式「＝[誕生日数]/365」
- 「構文エラーが検出されませんでした。」を確認したら、
- OKをクリック。
「年齢」列が追加されました。
小数点以下は365日で割り切れなかった日数=1年に満たない端数なので切り捨てます。
年齢列を選択し、リボン「列の追加」から「丸め」→「切り捨て」をクリックすると、
小数点以下が切り捨てられた新たな列が作成されます。
2022/3/31時点の年齢が算出されたぞ！
回答例2 誕生月から条件判定して年齢を求める
次の回答例は、誕生月で条件判定して年齢を算出する方法です。
基準となる年(2022年)から誕生年を引いて経過年数を求めたあと、誕生月が3月以下であれば基準月時点で誕生日を迎えているので、経過年数＝年齢、4月以降であれば、基準月時点でまだ誕生日を迎えていないので、経過年数-1＝年齢と判定します。
例：譜久村聖の誕生年は1996年なので2022年-1996年＝26年 誕生月は10月で3月時点でまだ誕生日を迎えていないので、26-1＝25歳となる
手順は以下の３stepです。
- 「生年月日」列を誕生年、誕生月、誕生日に分割
- 誕生月が3月以下かどうかを判定する列を追加
- 年齢列を追加
①.「生年月日」列を「年」「月」「日」に分割
リボン「変換」から「列の分割」→「区切り記号による分割」をクリックします。
「区切り記号による列の分割」ダイアログが開くので、
カスタムを選択し、「/」を入力したら、OKをクリックします。
列が「年」「月」「日」に分割されます。
分割された「年」「月」「日」のデータ型を整数に変更します。
②.誕生月が3月以下かどうかを判定する列を追加
条件列機能を使い、誕生月が3月より大きいかどうかを判定し、判定結果によって違う値を新しい列に返します。
リボン「列の追加」から「条件列」をクリックします。
条件列の追加ダイアログが開くので、
条件列名は「月」である「生年月日.2」
演算子は「次の値以下」
値は「3」
結果の出力は「0」
それ以外の場合は「1」
ここまで入力したらOKをクリックします。
これにより、誕生月が3月以下だったら「0」、4月以降であれば「1」を返す条件列が追加されます。
追加された条件列もデータ型を整数に変更します。
③.年齢列を追加
基準年から誕生月を引いた後、条件列の数値を引くと年齢が算出されます。
リボン「列の追加」から「カスタム列」をクリックすると、カスタム列のダイアログが開くので、
- 新しい列名「年齢」
- カスタム列の式「＝2022-[生年月日.1]-[条件1]」
- 「構文エラーが検出されませんでした。」を確認したら、
- OKをクリック。
「年齢」列が追加されました。
第5回目の感想
ある基準日時点での年齢を出す、というのは実務でわりとよく経験しています。なので今回出題された問題はとても興味深かったです。
また、データ整形のやり方はいろいろあるんだな～と知ることもできました。
しかし、私自身、自力で解答を導くところまで至らなかったです。
一つ一つの機能は使えても、組み合わせの極意を極めるにはまだまだ修行が足りないと感じました。
毎回講座レビューと銘打ってブログを書いていますが、実際の講座はここに書ききれないほど内容が濃いのでかなり端折っています。レビューを書く力量不足も感じています。
アウトプットって難しいですね。課題多し！
PowerQuery講義レビューはこれにて終了です。