スポンサーリンク
はじめに
ExcelのPowerQueryで、下図のような日付ごとに連続する実績データがあるとする。
これについて、前日(というより、1行上)の実績データを「前日実績」列として追加し、比較するデータを作成したいというのが今回の課題だ。
簡単そうに見えるが、PowerQueryでは、というよりデータベースにおいては通常、データを1行上とか縦方向と比較するものではない。
今回のPowerQueryの例でも、ちょっと凝った手順が必要になる。
リストへの変換をしてみる
ではPowerQueryを実際に見ていくけど、元データの読み込み方法とかそういうのは過去記事でも見ていただければと思う。
そして下図では、PowerQueryのステップとして「基準ステップ」というのを挟んでいるが、これは単なる空のステップ。文字通り、ここを基準地点にしたいと定めるためだけのステップを挟み込んだものだ。
ではここで、扱いたい数値である「実績」列を、「リスト」として独立に切り離して扱ってみる。
「実績」列を選択した状態でリボン「変換」タブ→「リストに変換」を押す。
そうすると「実績」列だけが切り離され「リスト」形式になる。
ここで数式のところに
=基準ステップ[実績]
と書かれる。
これは、先ほど作成したステップ「基準ステップ」に対して「実績」列のリストだけ切り出したということで、この式をメモ帳にでもコピペしておこう。
そして「実績」というステップが追加されているが、これは削除しておこう。
リスト変換したこのステップを残しておいても、先に進めないはずだし。
インデックス・条件列の追加
では、1行上と比較したいということで、各データの行番号を管理できるようにしたい。
もうこういうときは、インデックスを追加するのが定石だと覚えておこう。
リボン「列の追加」→「インデックス列」とし、今回は必ず「0から」開始としよう。PowerQueryで、行番号は0開始ということになっているからだ。
0から始まるインデックスが追加された。
さて、現在行より1行上のデータというのは、「インデックスが現在行より1小さい」データだということだが、当然ながらインデックス0についてはそれが存在しない。そのときは前日実績というのは、強制0という設定で良いだろう。
では、「1行上の実績データ」を作るのは難しいけどまず、「インデックスが0なら0にし、それ以外の場合は実績の数値をまんま持ってくる」データを仮作成してみよう。
リボン「列の追加」→「条件列」とする。
次の条件指定画面で、
- 新しい列名→「前日実績」
- 列名「インデックス」が0と等しいなら値0を出力するという条件を指定
- 「それ以外の場合」に、「実績」列を出力するという条件を指定
と条件指定して「OK」を押す。
リストを活用して式を書き換え
これで、「インデックスが0なら0を、それ以外なら実績をそのまま」返す「前日実績」列が作られた。
この返される実績値を、前日の実績値すなわち、インデックスが1少ないときの実績値としなければならない。
さて数式欄を見ると、if文が書かれていて、最後に
else [実績]
とあって、ここを上手いこと書き換えれば良さそうだ。
では先ほど、「実績」列を単独リストに変換してみたけど、そのリストの中でインデックスが1少ないものを持ってくるという式に書き換えれば良さそうだけど、どうすれば良いか。
その、リストに変換したときの式を
=基準ステップ[実績]
と書き留めておいたけど、これを使う。たとえばこのリストの5行目の値を取得したいとすると、
=基準ステップ[実績]{4}
と、{}記号で行番号を挟んで書くことになる。先ほど述べたが、行番号は0開始で管理されているので、5行目を取得したいなら{}記号の中に指定する行番号は4だ。
では5行目ではなく、「現在行のインデックスより1小さい」行番号を指定すれば、コンスタントに現在行より1行上の行を取得できそうだ。
現在行のインデックスというのは、現在行の位置を示す[]記号を使って
[インデックス]
と書けば良い。
ではこれらを組み合わせ、先ほどの式の
else [実績]
の箇所は、
else 基準ステップ[実績] { [インデックス] – 1 }
とすれば良い。
そのように数式を書き換えたのが下図だ。
見事に、前日の実績を持ってくることができているだろう。
引き算のカスタム列を追加
後は、当日実績を前日実績との引き算で比較する列を追加すれば良いだけで、これは簡単だ。
リボン「列の追加」→「カスタム列」とする。
新しい列名を「対前日差」などと入力。
数式には
=[実績]-[前日実績]
と引き算の式を入れ「OK」を押す。
これで、当日と前日の実績差額を示す「対前日差」列が追加された。
グループ項目を追加した例
上記の例では項目が「日付」「実績」しか無く、全ての列に単純にインデックスを付ければ良かったが、実際はそう単純なケースは少ないだろう。
下図では、「地域」列を追加してみた。
今度はそれを、下図のように地域ごとで対前日比較をするように加工したい。
もちろん、11行目の福岡県が、10行目の鹿児島県と比較されるようでは駄目ということだ。
インデックス追加～グループ化
もうここは、以前に扱った難解な例の記事をもとに、サクっと手順を示す。
まず、地域とか関係なく全体にかかるインデックスを設置する。
このインデックスは、項目名を「行番号」とでもしておこう。
次に、問題となる項目「地域」の列を選択した状態で、
リボン「ホーム」タブ
→グループ化
とする。
次の条件指定画面で、
- 「新しい列名」→「G」としておく
- 「操作」→「すべての行」にする
として「OK」を押す。
特殊な式を入れていく
次に、リボン「列の追加」→「カスタム列」とする。
次の条件指定画面で、先ほどグループ化した中身に対してインデックスを入れる特殊な式
= Table.AddIndexColumn ( [G], “地域ごとインデックス” , 0 , 1)
と入れ「OK」を押す。
追加された列「カスタム」の右の□ボタンを押す。
重複する項目である「地域」のチェックは外し、また「元の列名をプレフィックスとして使用します」のチェックも外して「OK」を押す。
ステップの最後が「展開されたカスタム」となっていると思うが、分かりにくい名前だし、ここで一息入れたい。
このステップ「展開されたカスタム」を右クリック→「後にステップの挿入」として、空のステップ「基準ステップ」を挿入しておく。
そこからもう一度ステップを挿入し、もう面倒だから数式欄に直接、次のように数式を打ち込もう。
= Table.AddColumn( 基準ステップ, “前日実績”, each if [地域ごとインデックス] = 0 then 0 else 基準ステップ[実績]{[行番号]-1})
要はif文で、「地域ごとインデックス」が0に切り替わるタイミングで判定を掛けておき、「行番号」が現在行より1少ない箇所の実績と比較をしようということだ。
これでひとまず完成。後は最初の事例と同様に、整形していけば良い。
スポンサーリンク
