Excel。事務職のデータ分析その20。分析ツールを使って「移動平均」とグラフを合わせて作っちゃおう
＜分析ツール＞
過去のデータを使って、その先の期間を予測することができる「移動平均法」。バラツキのあるデータを平均化して、
過去のデータから予測することができます。
平均化するということで、
AVERAGE関数を使うと簡単に算出することができるのですが、
「分析ツール」を使うと、
移動平均の算出と併せて移動平均グラフも作成してくれちゃいますので、
今回は、分析ツールを使った移動平均とそのグラフを作ってみましょう。
次の表があります。
2019年の売上高予測をしたいので、C列に移動平均を算出する表です。
3年ごとのデータで、平均を算出したいので、C4に数式を作るとしたら、
=AVERAGE(B2:B4)
という数式を作って、
C20まで、オートフィルを使って数式をコピーするわけですが、
今回は「分析ツール」を使って算出させていきます。
なお、「分析ツール」は、アドインで追加できる機能ですので、
データタブに「データ分析」が表示されていない場合には、
アドインで追加する必要があります。
では、データタブの「データ分析」をクリックします。
データ分析ダイアログボックスが表示されますので、
「移動平均」を選択して、OKボタンをクリックしましょう。
移動平均ダイアログボックスが表示されますので、
入力範囲には、$B$2:$B$19
今回は、データだけを範囲選択したので、
先頭行をラベルとして使用のチェックはOFFのままにします。
区間には、3年の平均を算出させたいので、3
出力先は、$C$2
グラフ作成にチェックマークをいれます。これで、グラフも作成してくれます。
さらに、標準誤差の表示にもチェックマークをいれることで、
標準誤差も算出してくれちゃいます。
では、OKボタンをクリックしましょう。
C列に移動平均が、D列に、標準誤差を算出してくれています。
なお、C列は、三桁区切りスタイルを設定してあります。
C4には、
=AVERAGE(B2:B4)
という数式が設定されています。
特に、D列の標準誤差ですが、D6の数式を確認してみると、
=SQRT(SUMXMY2(B4:B6,C4:C6)/3)
という数式が出来上がっていますね。
SQRTは、平方根を算出する関数で、
SUMXMY2関数は、2つの配列要素の差の平方和を算出する関数を使っていますが、
なかなか縁遠い関数ですから、
「分析ツール」を使うメリットはこういう点からもわかりますね。
さて、算出された、移動平均や標準誤差ですが、
移動平均の列に設定してあった罫線が消えていますね。
「分析ツール」をつかうと、設定してあった書式が消えてしまいますので、
再度設定する必要があります。
また、グラフをみても、そのままでは使えない感じですね。
見た目という点からもグラフをアレンジする必要があります。
そして、C20にも数式をコピーすることで、
移動平均でデータ予測をすることができますので、
忘れずに数式をコピーしておきましょう。
このように、「分析ツール」の移動平均を使うことで、
簡単な数式を作る場合でも、便利な機能がありますので、
機会がありましたら使ってみるといいかもしれませんね。
