Excel活用

【パワークエリ】グループごとの累計値を出す方法

前回はパワークエリを使って累計情報を計算する方法を書きました。

そこで今回はその応用編として、グループごとに累計情報を出せないかというテーマでお届けします。

今回は前回と違い、いろいろと操作しないといけませんが、ポイントはこちらです。

  • グループごとにインデックスを付番する方法
  • グループごとのリストを指定する方法

この2点さえクリアできれば今回のテーマは簡単です。

この記事でやりたいこと

まず今回使うデータがこちら。

日々の売上データを想定していますが、部門と日付の並びをあえてランダムにしてみました。

これを、部門コード・日付の順で並び替えます。ちなみにこのステップは複数条件の並び替えを紹介しますが、日付さえ並び替え出来ていればOKです。

そして部門コード別に累計額を算出した列を追加します。

では、さっそく見ていきましょう。

事前準備のための処理

データの取得

まず、データタブ「データの取得と変換」よりデータの形式に応じてデータを取得します。

データを複数条件で並び替える

データの取得が完了したら、これを部門コード別・日付別に並び替えてみましょう。

ちなみに、あとで部門コード別にグループ化しますので、実際並び替えが必要なのは日付だけですが、ここでは複数条件で並び替える方法を紹介します。

と言っても、優先する順番で並び替え(ここでは「昇順で並び替え」)を選ぶだけなんですけどね。

これで複数条件で並び替えた状態になります。

このように、並び替えた順番が列名に表示されました。

部門コード別のインデックス列を追加する

次に、まず部門コード別にグループ化します。

部門コード列を選択し、右クリックでグループ化を選択します。

次に、対象の列に「部門コード」が選択されていることを確認し、操作に「すべての行」を選択します。

ちなみに新しい列名は何でも構いません。ここでは分かりやすく「部門別テーブル」としています。

そして列の追加タブより「カスタム列の追加」を選択し、下記のように入力します。

ポイントはカッコ内で、対象列以降の「列名」「初期値(1)」「増加値(1)」を忘れないことですね。

インデックス列などの展開

ここまででクエリはこのように部門ごとのテーブルに集約されてますので、各項目を展開します。

カスタム列タイトル右端の展開ボタンより日付・金額・INDEXを展開します。

これで準備処理は完了です。

ちなみに、ここでは展開した列名が「カスタム.・・・」となっていますが、

数式バーの列名をこのように変更すれば、「カスタム.」を取ることもできます。

要は編集できるってことですね。

ではいよいよグループごとに累計していく方法です。

グループごとに累計値を算出する方法

カスタム列追加で入力する数式

まず結論を見ておきましょう。

列の追加タブ「カスタム列」より、このように入力します。

では詳しい説明に移ります。

累計値を出す関数

ここは前回と同じ関数、List.Sum関数を使います。

列の追加タブより「カスタム列」を選択し、「カスタム列の式」にList.Sum関数を入力するところまでは同じですが、カッコ内の”合計対象リスト”をどうするかが問題です。

この”合計対象リスト”に部門コードごとのリスト、さらには各データに応じてデータ数が変動するようにリストを指定する必要があります。

グループごとのリストの取り出し方

ここが今回一番重要なポイントです。

部門コードごとのリストをどうやって指定するかです。

では、任意の部門の金額列がどうやってパワークエリ上でリストに変換されるのかについて見てみましょう。

インデックスを追加した状態から見ていきます。

この状態で、上の赤枠をダブルクリックします。

すると、部門コード1の部門別テーブルが展開されました。

次に金額列を選択し変換タブ「リストに変換」を実行します。

するとこのようにリストに変換されました。

この状態で、詳細エディターを見てみましょう。

必要なのは赤枠の2行です。

これは1行にまとめるとこうなります。

つまり、グループごとのテーブルをリストとして取り出す方法はこのようになります。

グループ化したとしても、結局リストにするにはテーブル・列の順で指定することに変わりはないということです。

金額に応じたリストの指定

ここは前回と同じくList.FirstN関数を使います。

=List.FirstN( 部門コードごとのリスト , 部門コードごとのインデックス ) とすれば、「部門コードごとの、上から○番目の金額リスト」が出来上がるわけです。

ここでの「部門コードごとのリスト」とは前段で説明したリストがそれにあたります。

いったんここで、参照する部門コード列と上から○番目を示す列を確認しておきます。

ここまでで作成したクエリはこのようになっています。

そして、部門コードは1列目「部門コード」そして上から部門コードごとのインデックスが5列目「INDEX」列です。

List.FirstN関数にはこの二つの列を参照します。

部門ごとにリスト:追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額]

部門コードごとのインデックス:[INDEX]

この2つのパーツをList.FirstN関数に使います。

List.FirstN(追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額],[INDEX])

これで、部門コード別の合計対象リストの取得ができました。

合計関数との組み合わせ方

ここまでくれば、あとはList.Sum関数に投入するだけです。

List.Sum(List.FirstN(追加されたカスタム{[部門コード=[部門コード]]}[部門別テーブル][金額],[INDEX]))

これでList.FirstN関数で取得したリストをList.Sum関数で合計する関数ができました。

これを、カスタム列の数式として入力したわけです。

これでOKすると・・・

このように累計列が追加されました。

注意点

ここで気を付けることがあります。

List.FirstN関数でリストを取得する際に参照しているステップのことです。

参照しているステップが、直前の「展開されたステップ」ではなく、グループごとのインデックスを追加したステップ「追加されたカスタム」となっていることです。

ポイントは、「展開前のステップを参照していること」です。

展開後のステップでは部門コードに対して複数のテーブルが存在するため、エラーとなるのです。

そこで展開する前、つまり部門コードに対して一意のテーブルが指定されている状態のステップを参照しています。

あとがき

いかがだったでしょうか。

長々と書きましたが、やはりポイントはグループごとのリストをどうやって指定するか、でしょう。

それさえできれば前回の記事と同じ要領で出来るはずです。

ところで、これもっと簡単になりそうな気がするんですけどね。。。

もうちょっと考えてみましょうか。

いずれにせよ、最後まで読んでいただきありがとうございました!

にほんブログ村 士業ブログ 公認会計士へ
こちらもぽちっとお願いします。