日々の売上データなどをパワークエリで加工していると、累計情報も作りたくなりませんか？
今回は、それをパワークエリでやってしまおう！という話です。
まずは今回のポイントです。
- 累計対象の列をリスト化する方法
- 合計範囲を、List.FirstN関数で指定する
- 指定した範囲をList.Sum関数で合計する
- List.Sum関数の代用、List.Accumulate関数を使って合計する
Contents
この記事でやりたいこと
まず、今回やりたいことを確認しておきましょう。
こんな風に、左のテーブルからパワークエリを使って累計列を追加したい、これが今回のゴールです。
ちなみに、上の図のようにパワークエリを使わずにエクセル関数を使って累計を出すことは可能です。
今回の場合だとこのようにしてコピーすれば可能です。
でもそれではおもしろくないので、パワークエリを使って累計を出してみましょう。
パワークエリの操作
今回の結論
最初に答えを見ておきましょう。
取り込んだデータに対して、カスタム列の追加で以下の式を入力すれば完了です。
List.FirstN関数で取得したリストに対して、List.Sum関数で合計するように書けば出来上がりです。
内容はこのように分解してみると分かりやすいと思います。
ま、これで終わってもあれなので、実際の処理の流れを見ていきましょう。
テーブルデータの取り込み
まずはデータタブ「取得と変換」メニューより対象となるデータを取り込みます。
次に、今回はNO列があるので不要ですが、インデックス列を追加しておきます。ちなみにこの時の開始は１とします。
いずれにしても、行数を指定するための列を作っておくことが重要です。
合計する列をリストとして使う方法
ここで、今回計算対象となる金額列をリストとして使う方法を確認しておきましょう。
というのも、今回使う関数はリストを操作する関数だからです。
何を言ってるか分からないですね。笑
じゃあこのようにざっくり理解すればいいと思います。
今回、合計するために使う関数はList.Sum関数です。
これは「指定されたリストの合計値を返す関数」なので、合計対象としてリストを指定しないといけません。
なので、特定の列をリストとして指定する方法を覚えておかないといけないのです。
ではそれはどうするのか。
実際にリスト化してみれば分かります。
パワークエリエディターの変換タブに「リストに変換」があります。
これを、金額列を指定した状態で押すとリストに変換できます。
この時の数式バーに注目です。
簡単ですね。これはこのように考えればOKです。
= テーブル（つまりリスト化以前のステップ）[列名]
これで金額列をリストとして指定する方法が分かりました。
次に、合計範囲を指定する方法を見てみましょう。
合計範囲を指定する方法
各行における累計値を出すということはどういうことでしょうか。
理論的に考えると、このように定義できます。
「ｎ行目における累計値は、1行目からn行目までの合計である。」
つまり必要なことは、「対象のデータ(列）からn個のデータをリストとして返す」関数が必要になります。
それが、List.FirstN関数です。
そして、個数にはNO列(もしくはインデックス列)を指定すればOKですね。
これで合計範囲のリストを作成する方法が分かりました。
最後に累計列の追加方法です。
累計列の追加
最後に累計列を追加する方法を見ておきます。
列の追加タブから「カスタム列」を選択します。
そして列名を指定し、List.Sum関数のカッコ内を作りこみます。
NO列はデータの個数を指定する個所ですので、インデックス列を指定します。
後は「OK」を押して完了です。
このように、累計列が追加されました。
List.Accumulateを使った累計計算の方法
ついでに、List.Sum関数の代わりにList.Accumulate関数を使った方法も観ておきましょう。
答えはこちら。
カスタム列の式はこのように入力します。
=List.Accumulate(List.FirstN(ソース[金額],[NO]),0,(state, current) => state + current)
List.Sum関数と違うところは合計範囲リスト以外に指定する項目があることですね。
意味としては、リストに対して直前までの計算結果をstate変数、最新のデータをcurrent変数と指定して、それをstate＋currentとして返しています。ここでは直前までの合計と最新のデータを足した数値を返しています。
まぁつまり累計ですね。
ちなみに「０」を指定しているのは、最初のデータには直前結果が無いため、直前結果の初期値を指定しています。
これ、List.Sum関数と使い分ける必要があるのか？って思いません？
今回は累計値を出すので、state＋currentとしましたが、算式を指定できるということは、乗算やその他計算式を指定できるということです。
ただし乗算の場合初期値に1を指定しないといくら計算してもゼロ、という結果にはなりますが。
なので、使い道は限られると思いますが、加算以外の計算をする場合はList.Accumulate関数を使うことになります。
まとめ
今回、パワークエリを使って累計値を出す方法を見ていきました。
意外と簡単にはできないといった印象ですが、理解できれば使い道の幅は大きく広がると思いますので、ぜひとも使えるようにしておきたい方法だと感じました。
次回、ではこれをグループごとに累計する方法をお届けしたいと思います。
最後までお読みいただきありがとうございました！
