PowerQuery（パワークエリ）で移動平均と移動合計を計算する方法を紹介します。中心データと前後データを用いた移動平均・移動合計の計算例やErrorの処理方法も解説していますので参考にしてみてください。
移動平均の計算
移動平均の計算式
移動平均の計算式はテーブル名・列名・行番号によるセル参照式「テーブル名 [列名] ｛行番号｝」とインデックス列を組み合わせることで作成することができます。
中心データと前後1個のデータの移動平均（計3項）
([列名] + 対象テーブル名 [列名] {[インデックス列]-1} + 対象テーブル名 [列名] {[インデックス列]+1})/3中心データと前後2個のデータの移動平均（計5項）
([列名] + 対象テーブル名 [列名] {[インデックス列]-2} + 対象テーブル名 [列名] {[インデックス列]-1}
+ 対象テーブル名 [列名] {[インデックス列]+1} + 対象テーブル名 [列名] {[インデックス列]+2})/5移動平均の計算式では セル参照の行番号を {インデックス列 – 1} や {インデックス列 + 1} とすることで計算している行の一つ上の行の値や一つ下の行の値を参照しています。これにより中心データとその前後のデータを用いて計算をすることが可能となっています。
列名と行番号を用いたセル参照の説明はこちらの記事でも紹介していますのでよければ見てみてください。
移動平均の計算手順・計算例
移動平均の計算手順は以下の2ステップです。
- インデックス列を追加する。
- カスタム列を用いて移動平均の計算式を作成する。
今回は下図の「年月」と「売上」データから対象月・前月・次月の3か月の移動平均を算出します。
「列の追加」タブ内の「インデックス列」を選択して列を追加する。ここでインデックス番号は0から始める。
- 「列の追加」タブ内にある「カスタム列」をクリックして新しい列を追加する。
- 下記の移動平均の計算式を入力してOKをクリックする。
([売上] + 追加されたインデックス[売上] {[インデックス]-1}+追加されたインデックス[売上] {[インデックス]+1})/3
計算式の各項目の内容は下記の通りです。テーブル名に「追加されたインデックス」、列名に「売上」、行番号に「[インデックス]±1」を用いることで計算行の一つ上と一つ下の行の売上値を参照して計算しています。
- [売上] ： 計算行の売上の値
- 追加されたインデックス[売上] { [インデックス] – 1 } ： 計算行の一つ上の行の売上の値
- 追加されたインデックス[売上] { [インデックス] + 1 } ： 計算行の一つ下の行の売上の値
- 3： 項数
以上で移動平均の計算は完了です。対象月と先月・次月の売上の移動平均が算出できています。
1つ注意点ですが、この移動平均の計算を行うと計算行の上の行や下の行を取得できない範囲で計算Errorが発生してしまいます。Errorを回避する方法は後述していますのでErrorが気になる方は確認してみてください。
Sponsored link
移動合計の計算
移動合計の計算式
移動合計の計算式はテーブル名・列名・行番号によるセル参照式「テーブル名 [列名] ｛行番号｝」とインデックス列を組み合わせることで作成することができます。
過去3個のデータの移動合計
[列名] + 対象テーブル名 [列名] {[インデックス列]-1} + 対象テーブル名 [列名] {[インデックス列]-2}過去6個のデータの移動合計
[列名] + 対象テーブル名 [列名] {[インデックス列]-1} + 対象テーブル名 [列名] {[インデックス列]-2}
+ 対象テーブル名 [列名] {[インデックス列]-3} + 対象テーブル名 [列名] {[インデックス列]-4}
+ 対象テーブル名 [列名] {[インデックス列]-5}移動合計の計算式では セル参照の行番号を {インデックス列 – 1} や {インデックス列 – 2}など とすることで計算している行の一つ上の行の値や二つ上の行の値を参照しています。これにより過去のデータを用いて計算をすることが可能となっています。
移動合計の計算手順・計算例
移動合計の計算手順は以下の2ステップです。
- インデックス列を追加する。
- カスタム列を用いて移動合計の計算式を作成する。
今回は下図の「年月」と「売上」データから対象月・前月・前々月の3か月の移動合計を算出します。
「列の追加」タブ内の「インデックス列」を選択して列を追加する。ここでインデックス番号は0から始める。
- 「列の追加」タブ内にある「カスタム列」をクリックして新しい列を追加する。
- 下記の移動合計の計算式を入力してOKをクリックする。
[売上] + 追加されたインデックス[売上] {[インデックス]-1}+追加されたインデックス[売上] {[インデックス]-2}
計算式の各項目の内容は下記の通りです。テーブル名に「追加されたインデックス」、列名に「売上」、行番号に「[インデックス]±1」を用いることで計算行の一つ上と二つ上の行の売上値を参照して計算しています。
- [売上] ： 計算行の売上の値
- 追加されたインデックス[売上] { [インデックス] – 1 } ： 計算行の一つ上の行の売上の値
- 追加されたインデックス[売上] { [インデックス] – 2 } ： 計算行の二つ上の行の売上の値
以上で移動合計の計算は完了です。対象月・先月・先々月の売上の移動合計が算出できています。
1つ注意点ですが、この移動合計の計算を行うと計算用の行データを取得できない範囲で計算Errorが発生してしまいます。Errorを回避する方法は後述していますのでErrorが気になる方は確認してみてください。
Sponsored link
Errorの処理方法（例外処理）
移動平均・移動合計の計算を行うと計算行の上の行や下の行を取得できないセルでErrorが発生してしまいます。Errorを回避する場合は例外処理を計算式に追加してください。
＝ try 通常の処理 otherwise エラー時の処理
使用例
上述の移動平均の計算式に例外処理を追加してみます。下記のエラー処理ではエラーが発生した場合は「null」となるように記載しています。otherwiseの後ろに計算式や文字列を記入することで任意の処理を設定できます。
try ([売上] + 追加されたインデックス[売上] {[インデックス]-1}+追加されたインデックス[売上] {[インデックス]+1})/3
otherwise null上記の計算結果は下図となり、1行目と最終行で発生していたErrorはなくなり代わりにnullとなっています。
Sponsored link
パワークエリの学習本についてはコチラの記事でまとめてますので参考にしてみてください。
・初心者向けおすすめ学習本
・初中級者向けおすすめ学習本
コメント