表記が統一されていない和暦を西暦に変換

今回は、これまでに解説してきたコマンドを組み合わせて「和暦」を「西暦」に変換するときの具体例を紹介していこう。「文字」と「数値」が混在しているデータを計算する場合の例として参考にして頂ければ幸いだ。

「年」を数値として分離する

今回は、これまでに解説してきたコマンドの復習も兼ねて、パワークエリを使った具体的な処理の例を紹介していこう。

• 

  表記方法が異なる「和暦」を「西暦」に変換

まずは、以下の図に示したデータ表を見て頂きたい。この表にある「入社年度」のデータは、「平成27年」や「平17年」、「H14」のように表記が統一されていない和暦になっている。もちろん、和暦の元号は「平成」だけではない。「令和」や「昭和」の元号も含まれており、これらも表記方法がデータごとに変化している。さらに、年度が「西暦」で記されているデータもある。

• 

  「入社年度」の表記が統一されていないデータ表

このままでは「入社年度」でデータを並べ替えられないので、これらの表記を「西暦」に換算する処理をパワークエリで実現してみよう。「和暦」を「西暦」に換算するときは、元号に応じて以下のような計算を行えばよい。

・「昭和」の場合 ………… 1925年を加算する
・「平成」の場合 ………… 1988年を加算する
・「令和」の場合 ………… 2018年を加算する

こういった計算を行うには、あらかじめ各データから“数字”の部分だけを切り出しておく必要がある。まずは、各データから「年」の文字を削除して、データの末尾が数字になるように加工する。この処理は、「値の置換」を使って「年」→（文字なし）の置換を行えばよい。

• 

  「年」の文字を削除する操作

各データから「年」の文字が削除される。続いて、各データの先頭に“適当な文字”を追加する。この処理は、「変換」タブにある「書式」→「プレフィックスの追加」で実現できる。

• 

  「書式」→「プレフィックスの追加」を選択

今回の例では、適当な文字として「★」の文字を追加するように指定した。この文字は数字でなければ何でもよい。よって、各自の好きな文字を指定することが可能だ。

• 

  追加する文字の指定

各データの先頭に「★」を追加できたら、データを「文字」と「数値」に分割する。今回の例の場合、「列の分割」→「数字以外から数字による分割」を選択すると、この処理を実現できる。

• 

  「列の分割」→「数字以外から数字による分割」

「文字」から「数字」に変化する位置で、各データが2列に分割される。先ほど「プレフィックスの追加」で「★」の文字を追加した理由は、この処理を適切に行うためだ。というのも、「プレフィックスの追加」を行わなかった場合、もともと数字しかなかったデータが分割されず、1列目に残ってしまうからだ。

• 

  分割された列

これで「入社年度」のデータから数字の部分だけを切り出すことができた。現時点では、これらの数字は「テキスト」として扱われているので、データ型を「整数」に変更する。

• 

  データ型を「整数」に変更

列名が「入社年度.1」と「入社年度.2」のままでは紛らわしいので、列名も変更しておこう。ここでは、それぞれの列名を「元号」と「年」に変更した。

• 

  列名の変更

「条件列」を使って加算値を指定する

続いては、それぞれの「元号」に応じて「年」に加算する値を求めていこう。冒頭で示したように、「昭和」の場合は1925、「平成」の場合は1988、「令和」の場合は2018を加算すると、西暦に換算できる。

この処理は「条件列」で実現できる。「列の追加」タブにある「条件列」をクリックする。

• 

  「条件列」をクリック

「条件列」の設定画面が表示されるので、「新しい列名」を入力する。今回の例では、「加算値」という列名を指定した。続いて、昭和の元号が「S」と表記されている場合の処理を指定する。具体的に書くと、「元号」の列に「S」が含まれている場合は「1925」を出力する、と指定すればよい。

• 

  条件列の指定（1）

同様に、元号が「昭和」や「昭」と表記されている場合の処理を指定する。これらの条件は、「元号」の列に「昭」が含まれている場合、という形に統合できる。よって、「元号」の列に「昭」が含まれている場合は「1925」を出力する、と指定する。

• 

  条件列の指定（2）

同様の手順で、元号が「平成」のときの処理を指定する。具体的には、以下の2つの処理を追加すればよい。

・「元号」の列に「H」が含まれている場合は「1988」を出力
・「元号」の列に「平」が含まれている場合は「1988」を出力

• 

  条件列の指定（3）

元号が「令和」の場合も基本的な考え方は同じだ。以下の2つの処理を追加する。

・「元号」の列に「R」が含まれている場合は「2018」を出力
・「元号」の列に「令」が含まれている場合は「2018」を出力

• 

  条件列の指定（4）

上記に記した6つの条件に当てはまらない場合は、もともと西暦で「年」が入力されている、と考えられる。よって加算する値には「0」を出力すればよい。「それ以外の場合」に「0」と入力し、「OK」ボタンをクリックする。

• 

  条件列の指定（5）

データ表の右端に「加算値」の列が作成される。この列を「年」の右隣に移動すると、以下の図のようになる。「元号」に応じて「加算値」の値が変化していることを確認できるだろう。これで、それぞれの「年」に加算する値を求められた。

• 

  条件列により作成された「加算値」の列

現時点では「加算値」の列のデータ型が「指定なし」になっているので、これを「整数」に変更する。これで「加算値」の列を計算できるようになる。

• 

  データ型を「整数」に変更

「西暦」の数値を計算する

ここまで処理できたら、あとは「年」と「加算値」を足し算するだけ。ここでは「カスタム列」を使って計算を処理していこう。「列の追加」タブにある「カスタム列」をクリックする。

• 

  「カスタム列」をクリック

まずは、計算結果が出力される列の「列名」を指定する。今回の例では「入社年度（西暦）」という列名を指定した。続いて、「 = [年]+[加算値] 」と数式を入力し、「OK」ボタンをクリックする。

• 

  列名と数式の入力

データ表の右端に「入社年度（西暦）」の列が作成される。この列を「加算値」の右隣に移動すると、以下の図のようになる。

• 

  算出された西暦

現時点では「入社年度（西暦）」の列のデータ型が「指定なし」になっているので、これを「整数」に変更しておこう。

• 

  データ型を「整数」に変更

これで「入社年度」を西暦の数値に変換できた。ここまで作業できたら、処理中に利用した3つの列は不要になる。「元号」、「年」、「加算値」の3列を削除する。

• 

  不要になった列の削除

念のため、パワークエリで処理した結果をExcelに出力した例も紹介しておこう。「ホーム」タブにある「閉じて読み込む」をクリックする。

• 

  加工したデータ表をExcelに出力

先ほどのデータ表が「テーブル」としてExcelに出力される。もちろん、「入社年度（西暦）」の列を基準にデータを並べ替えることも可能だ。たとえば、数値の大きい順（降順）を指定すると、「入社年度の新しい順」にデータを並べ替えることができる。

• 

  Excelに出力されたデータ表

• 

  「入社年度」の大きい順に並べ替えた結果

このように、データの表記を統一して「数値」として扱えるように加工する場合にもパワークエリが活用できる。今回は「和暦」を「西暦」に換算する例を紹介したが、「文字」と「数字」が混在している他の事例にも応用できるだろう。

同様の加工をExcelだけで処理しようとしたら、相当に苦労すると思われる。一方、ステップ形式で処理を進めていけるパワークエリなら、その場で処理方法を考えながら最終形に近づけていくことが可能となる。

そういう意味では、「Excel関数を駆使したり、VBAでプログラミングしたりするよりも簡単」といえる。状況にあわせて便利に活用できるように、パワークエリの使い方にも慣れておくとよいだろう。