PowerQuery 数字と文字列(単位)に分割する

Microsoft Excel

スポンサーリンク

はじめに

下図の項目「耐用年数」は、月単位と年単位とが混在していて、数値の後に文字列もくっ付いているので、計算に不便だ。

これをExcelのPowerQueryにおいて、下図の項目「月数」のように、統一で月単位の数値に直したい。

まあPowerQueryをかじってれば、要領悪く幾つかステップを刻んででも、割と簡単にできるだろう。

ひとまず今回は、私が要領良いと考える方法をサッと書いてみる。

数値と数値以外に分割する

まず、数値の後に「年」「ヶ月」と邪魔な文字列が付いている項目「耐用年数」を、数値だけに直したいが、ここで

リボン「ホーム」タブ
→「列の分割」
→「数字から数字以外による分割」

とする。

「列の分割」は必須コマンドだが、その中でもこの「数字から数字以外による分割」は、あまり頻繁に使わないコマンドじゃないかと思う。

私も、関連記事を書いておきながら、最近まで盲点だった。

Excel PowerQuery 列の分割(2)文字種類などで分割する

これをすることで、項目「耐用年数」が、数値と数値以外との2列に分割される。

下図の「耐用年数.1」が数値項目の方なわけだが、まだこの時点では「ABC」とかいう表示が左上にあって、文字列項目のままだ。

これを、データ型の変更で「整数」にしておこう。
ここで数値項目にしっかり変更しておかないと、先に進めない。

数値項目に直したのが下図。

項目名が「耐用年数.1」「耐用年数.2」とあったらややこしいので、「耐用年数.2」の方は項目名を「単位」と修正しておく。

条件列の追加

では、月単位に統一するに当たっては、単位が「年」だったら数値を12倍するということで良い。そういう条件分けがあるので

リボン「列の追加」
→「条件列

とする。

Excel PowerQuery 計算式や条件を入れたオリジナルの項目列を追加する

条件設定の画面が出るが、ひとまず次のように進めていく。

 

  1. 新しい列名→「月数」と入れる
  2. 列名は「単位」を選択
  3. 演算子「指定の値で終わる」
  4. 値に「月」と入れる。正確には「ヶ月」で終わる場合を扱うわけだが、今回の例なら「月」だけで十分だろう。
  5. 「出力」において、左側の選択肢で「列の選択」を選んでから、右側の選択肢で「耐用年数.1」を選択する。
  6. 左下「それ以外の場合」項目についても同様に、左側の選択肢で「列の選択」を選んでから、右側の選択肢で「耐用年数.1」を選択する。

 

さてこれだと、いずれの条件であっても結果は単に「耐用年数.1」の値が返るだけだ。

下図の通り、新しい項目「月数」は、項目「耐用年数.1」と等しい値になっているだけ。

式を書き換え

ここで、PowerQueryエディターの数式バーを少しだけ書き換える。

先程の条件列追加によって、なんか ifthenelse  とかいうVBAで見るような式が書かれている。

Text.EndsWithとかいうのも書かれているが、予備知識がなくとも、なんか終わりの文字列を判定しているというのが分かるだろう。

そして式の else  以降の部分が、単位が「月」以外だった場合を示すというのが分かるはずだから、

else [耐用年数]

という文言の後に12倍する式を入れ

else [耐用年数]*12

とすれば良い。

これで、単位が「年」だった場合は耐用年数が12倍されるようになった。

項目「耐用年数.1」を12倍した新しい列を「カスタム列の追加」コマンドから追加するという手順を取っても良いのだけど、不要な列が増えてそれらの削除がまた面倒になるので、今回はダイレクトに数式バーを書き換える方法を取ってみた。

スポンサーリンク