さて、累計列が作れるなら移動合計列もパワークエリで作ってしまいましょう！
今回の記事はこんなテーマで書いてみました。
累計列を追加する方法について書いたのがこちらです。
そして、移動合計とは。
移動合計とは、過去のある一定期間における合計値のことである。 移動合計は、売上高や顧客数といった数値の推移を調べる時に用いられる。 … 例えば、今年の6月の移動合計は、前年の7月から今年の6月までの売上高の合計額となり、今年の12月の移動合計は、今年の1月から12月までの売上高の合計額となる。
移動合計とは何？ Weblio辞書
さらに移動合計と累計を使った分析手法にＺチャート分析というものがあります。
それについてはこちらの動画をご覧ください。
https://chokozemi.smrj.go.jp/min10/1911/
立場によって、また分析の目的によって見方が変わるが、それらが一目で分かるように図示したものが「Ｚチャート」というわけですね。
で、このＺチャートを作るための移動合計をどうやって出すのかという話です。(前振りが長かった…！笑）
Contents
移動合計の計算方法
まずは移動合計の計算方法を見ておきましょう。
このように、2年分の各月売上のデータがあったとします。
ここから、2020年4月から2021年3月までの、各月の移動合計を出すにはどうすればいいでしょうか。
このように、横に展開すれば分かりやすいですね。
2020年4月であれば2019年5月から2020年4月までを、
2020年5月であれば2019年6月から2020年5月までを
合計します。
これを図示したのが上の図です。黄色のセルが各月の合計対象です。
ではこれをパワークエリでやっていきましょう。
List.Range関数で各月の合計範囲リストを生成する。
ポイントはこの1点です。
パワークエリの操作
データの取得
まずはデータタブ「取得と変換」よりデータ形式に合わせてデータを取得します。
さすればクエリエディターが起動し、このような画面が開きます。
こんな感じですね。
インデックス列の追加
次にインデックス列を追加します。
この時、開始は０でも1でも構いません。今回は０で解説していきます。
次、移動合計列を追加します。
移動合計列の追加
ここでさっき作ったインデックス列を使います。
列の追加タブより、「カスタム列」を選択します。
では、カスタム列の数式に入力の内容を見てみましょう。
各月の合計範囲リストの取得
先に説明したように、移動合計を出す場合各月によって合計範囲が変わります。
では、どうやって合計範囲を動的に把握すればいいのでしょうか。
これには、指定したリストから指定した範囲をリストにして返すList.Range関数を使います。
中身はこんな感じです。
合計範囲リスト＝List.Range(対象リスト,開始行までの移動幅,終了行までの移動幅）
そして、リストの指定の仕方はこうでしたね。
対象リスト＝テーブル[列]
＝前ステップ[リスト化対象列]
このようにすれば、テーブルから自在にリストに変換することができます。
今回の場合は金額列をリストにします。
次に、開始行までの移動数の出し方を考えてみましょう。
開始行までの移動数は、先ほど追加したインデックス列を使います。
対象月に対応したインデックス列のレコードから、12減じた数値が開始行までの移動数を指しています。
先ほどの表で確認してください。
月ごとの金額を横に展開し、下に移動合計額を書いています。
このように、2020年4月(11)の時、開始行は１２戻ったひとつ下が開始行です。
このように考えると、開始行に指定すべき数値は[インデックス]-11となります。
念のため確認してみてくださいね。
そして終了行までの幅は、12ですね。
まとめると、List.Range関数はこのようになります。
合計範囲リスト：
List.Range(追加されたインデックス[金額],[インデックス]-11,12)
合計する範囲として取得したリストを合計する
次に合計範囲リストを合計するにはList.Sum関数を使います。
これは簡単です。上で取得したリストをList.Sum関数の引数に入れるだけです。
移動合計＝List.Sum(
List.Range(追加されたインデックス[金額],[インデックス]-11,12)
)
カスタム列の追加
以上をカスタム列の列名と数式として入力します。
これで移動合計が算定できました。
不要行の削除
さて、上の図では移動合計にERRORが発生しています。
これは合計範囲が取得できてないからですね。
具体的には開始行がマイナスになってしまい、リストにできない状態になっています。
今回、必要なデータは2020年4月からの移動合計ですのでこのERROR行は不要なので削除しておきます。
移動年計列を選択した状態で右クリックし「エラーの削除」を選択します。
もしくはフィルターで必要な期間に絞ってもいいですね。
あとはインデックス列も削除して整理するとこんな感じになり、各月の発生データから移動合計を算出・追加することができました。
最後に
いかがだったでしょうか。
使う頻度はあまりないかもしれませんが、List関数を使えば移動合計を出すことができるので、こんなこともできるんだぐらいに覚えておいても損はないと思います。
今回も、最後までお読みいただきありがとうございました！
