エクセルの関数技2

SUM関数や移動平均分析ツールで移動平均を出す

時系列データを観察する時、データの変化が激しく、基本的な変化の傾向がつかみにくいことがあります。
たとえば、売上がほんとうは、上昇傾向にあるのか、それとも実際は停滞しているのかなどを判断するのが難しい場合です。
これを解決する一つの手段として移動平均という方法があります。

この移動平均とは、ある個数分のデータの平均値を連続的に求め、
そのデータ全体の変化の傾向を解析するものです。
株価を分析する時などでよく使われています。
(サンプルファイルは、こちらから 関数技48回サンプルデータ

移動平均とは?

売上が月ごとに上下して、傾向がつかみにくい時に、データを平滑化することで傾向を分析します。

月次データをベースに3か月移動平均を用いる場合、
①当該月の前後1か月のデータを平均する方法(例えば5月の平均値は4月、5月、6月の値を平均)、
②当該月以前の3か月のデータを平均する方法(例えば5月の平均値は3月、4月、5月の値を平均)、
③当該月以後の3か月のデータを平均する方法(例えば5月の平均値は5月、6月、7月の値を平均)、
の3通りのものがあります。

このように加工したデータを用いることにより、不規則変動も、
ある程度ならされ、時系列データの基本的な傾向が読み取り易くなります。

移動平均 SUM関数を使う場合

②当該月以前の3か月のデータを平均する方法で表を作成してみます。移動平均
使うのはSUM関数だけです。
AVERAGE関数でも同様にできますが、3ヶ月平均をとるということを明らかにするためにSUM関数を使い3で割っています。
2月、3月、4月の合計を3で割ります。
=SUM(B2:D2)/3
あとは、式をコピーします。移動平均2
2012年と2013年の3ヶ月移動が出ました。
移動平均3
グラフを作成すると、もっとハッキリします。移動平均4
スポンサーリンク
スポンサーリンク

アドインの移動平均分析ツールを使う

アドインの分析ツールを追加して、もっと簡単に移動平均を出すことができます。 先に、分析ツールを追加しておきます。移動平均5
(1)データ分析をクリックして、データ分析ダイアログボックスから、移動平均をクリックします。
[移動平均] ダイアログ ボックスが表示されます。
 [入力範囲] ボックスで、1 行または 1 列のデータを入力します。下図↓移動平均6
区間は、3ヶ月にしました。同様に出力先を指定します。
グラフにチェックを入れておくとグラフも同時に作成されます。
グラフは見やすくなるように調整してください。
3ヶ月平均なので、最初の2ヶ月は結果が現れません。4月で初めて平均が計算されています。
移動平均7
データが足りない最初の2ヶ月は、#N/Aと表示されています。
邪魔な時は、消しても出力データに影響はありません。
分析ツールは Microsoft Excel のアドイン プログラムです。Excel でアドインを使用するには、最初にそのアドインを読み込む必要があります。
スポンサーリンク
スポンサーリンク