【必見】Power Query【生年月日から年齢】現在時点編

2 min 349 views
excel_sample

はじめに

今回ご紹介する内容は、ググってもなかなか情報を得られなかったくらい、かなりニッチな情報だと思いますが、困っている方もいらっしゃるかと思いましたので、2回にわたってご紹介しておきたいと思います。
まず、今回の記事の対象の方をアナウンスさせていただきます。

今回の記事の対象者
  • マーケティング担当者
  • データ分析の担当者
  • Excel、データ分析に興味のある方
  • これから、Power Queryを勉強したい方
目次
  • はじめに
  • 目次
  • power Queryとは
  • 生年月日から年齢【現在時点】
Power Queryとは

2016年に公開されたMicrosoft製のデータ分析用Excelアドインであり、Excel2010以降に対応しています。
ファイル・DB・Webサービス等からデータを読み込み、変換・加工してExcelのシートに出力できます。
VBAのマクロの記録と同じように、画面上で操作するとクエリ(読み込み手順の定義)が生成されます。
複雑な加工をしない限りプログラミングは必要ありません。

Excel内に全ての情報を格納するため、一つのファイルで完結し他のツールは必要ありません。
PowerQueryの入っていない端末では普通のExcelファイルとして閲覧することができます。

生年月日から年齢【現在時点】

さっそくですが、できるだけわかりやすく解説していきます。

  1. STEP

    ランダムデータの作成

    まずは適当に100個のランダムデータを作成していきます。
    やり方はいろいろあると思いますが、今回はExcelのRANDARRAY関数を使っていきます。

    sample01

    上図のように、A1セルにタイトルの「シリアル値」と入力しておきます。
    A2セルにこちらの関数を入力します。

    =RANDARRAY(100,1,10000,40000,TRUE)

    今回は、Power Queryの内容をメインにしておりますので、RANDARRAY関数についての説明と、なぜ5桁の数値を用意するかについては割愛します。

  2. STEP

    関数を数値に変換

    関数をそのままPower Queryには変換できないので、いったんデータを数値に変換しておきます。

    sample02

    上図のように、C1セルに「生年月日」と入力しておきます。
    C2セルにこちらの関数を入力します。

    =TEXT(A2,0)

    こちらの式を以下100行まで反映させておきます。

  3. STEP

    Power Queryへ変換

    C2セルにカーソルをセットした状態で、「データ」>「テーブルまたは範囲から」をクリックして、「OK」をクリック

    sample03

    先頭行をテーブルの見出しとして使用するに「☑」を入れておきます。

  4. STEP

    ランダムデータを日付に変換

    ここからようやくPower Queryの操作に入っていきます。
    STEP3でOKをクリックすると下図のようなウィンドウが開きますので、データを日付に変更します。

    上図の右上にある「変換」リボンの「データ型:整数」をクリックして、「日付」を選択するとウィンドウが立ち上がりますので、「新規手順の追加」をクリック

    「現在のものを置換」を選択するとエラーになるので注意してください。

    「生年月日」のタイトルの左にある「123」をクリックしても同じ操作ができます。

  5. STEP

    年齢を計算

    STEP4でようやく生年月日を生成できましたので、現在時点の年齢を計算していきます。

    上図のように、「列の追加」タブ>「カスタム列」をクリックすると、ウィンドウが立ち上がるので、新しい列名に「年齢」と入力し、カスタム列の式にこちらの関数を入力します。

    Number.RoundDown(Duration.Days(DateTime.Date(DateTime.LocalNow())-[生年月日])/365)

    少し難しい計算式ですが、詳しい式の内容を知りたい方は、MicrosoftのPower Queryの公式サイトを参照ください。

    計算式ボックスの下にある「Power Queryの式についての詳細」をクリックしても参照ページにとべます。

  6. STEP

    Excelに戻す

    年齢に変換できたので、最後にExcelに戻していきます。

    「ホーム」タブに戻り、一番左の「閉じて読み込む」をクリックすると、Excelに戻り、新しいシートにデータを構築できます。

次回は、任意の時点での年齢計算方法を紹介します。

関連記事