1. TOP
  2. お役立ち情報
  3. 今日からはじめるExcelデータ分析!第2回移動平均と季節調整でデータの本質を見極める

今日からはじめるExcelデータ分析!
第2回
移動平均と季節調整でデータの本質を見極める

今日からはじめるExcelデータ分析!第2回移動平均と季節調整でデータの本質を見極める

専門的な知識がなくてもできるExcelを使った簡単なデータ分析方法を全3回にわたってご紹介しています。
第1回目の記事ではデータの特徴を表す数値である3つの代表値、「平均値」「中央値」「最頻値」についてご紹介しました。第2回目となる今回は、平均値の応用となる「移動平均」「季節調整」を使った時系列データの分析方法をご紹介します。時系列データを分析することでそのデータの「傾向」を読み取ることができます。そして傾向がわかれば「予測」ができます!

今回も難しい計算式は出てきません。Excelが計算してグラフまで作成してくれますので、
「移動平均」と「季節調整」がどのような分析方法なのか、順を追って見ていきましょう。

目の前のデータに一喜一憂しない!
移動平均で長期的な傾向を見極める

まず、移動平均とは文字のとおり「期間を移動しながら平均をとっていくこと」です。直近3ヶ月の売上が不規則に変動している場合でも、長期的にみたときには売上が伸びている可能性もあります。それを確かめるためには、次の2つの変動要因を取り除く必要があります。

1.季節変動:曜日や季節など一定のサイクルで繰り返される規則的な変動要因
2.無作為変動:気温や天候など、一時的な変動要因や不規則な変動要因

文字だけではイメージしづらいと思いますので、移動平均を示したグラフを見てみましょう。

移動平均を示したグラフ

このオレンジ色の滑らかなグラフが青いグラフの「移動平均」を表しています。青いグラフだけでは変動の幅がばらばらでこのデータが「成長傾向」にあるのか「衰退傾向」にあるのかいまいち判断ができません。しかし、オレンジ色の移動平均のグラフをみると、緩やかに右肩上がりになっていることがわかります。

このように、移動平均を使うと実測値データだけではわからないデータの動きを見ることができます。
ではどのような仕組みでデータが平滑化されたのかExcelで移動平均を求めながら確認してみましょう。

Excelで移動平均を求めてみよう

Excelを使えば、次の3つのステップで移動平均を求めることができます。

Step1. 時系列データを整理する
Step2. [データ分析]機能を使って移動平均を求める
Step3. 結果をグラフで表示する

今回は経済産業省のオープンデータから「遊園地・テーマパーク売上高」の2013年~2019年のデータを引用して分析していきたいと思います。
(参照元:https://www.meti.go.jp/statistics/tyo/tokusabido/result/result_1.html

Step1. 時系列データを整理する

まずは、ダウンロードした統計データを作業しやすいように1列にします。
次のステップの準備として「移動平均」の列を作っておきましょう。

時系列データを整理する

Step2. [データ分析]機能を使って移動平均を求める

Excelの[データ]タブから[データ分析]をクリックするとダイアログボックスが開くので「移動平均」を選択します。

[データ分析]機能を使って移動平均を求める1

移動平均のダイアログボックスが開いたら、入力範囲を売上高のデータが入力されているセル、区間を「12」(月次データなので12ヶ月を1サイクルにします)、出力先を「移動平均」の列の先頭に設定します。

[データ分析]機能を使って移動平均を求める2

[OK]をクリックすると、計算された値が表示されます。数値が入ったセルを見ると「AVERAGE関数」が入っています。C13のセルには「=AVERAGE(B2:B13)」と入っていますが、C14には「=AVERAGE(B3:B14)」と入っており、以降も範囲を移動しながら各月の平均値が計算されています。なお、C2からC12のセルに表示された「#N/A」は計算に必要なデータが不足しているためのエラー表示となります。

[データ分析]機能を使って移動平均を求める3

今回は区間を「12」と設定しましたが、日ごとの売上データから分析を行いたい場合などは1週間(7日間)のサイクルで考え、区間を「7」に設定するとよいでしょう。

Step3. 結果をグラフで表示する

数値だけではわかりづらいので、グラフで視覚的に示します。
売上高と移動平均の列を選択し、[挿入]→[グラフ]から「折れ線グラフ」を選択します。

結果をグラフで表示する

冒頭でご紹介した移動平均を表すグラフが作成できました。数字を見ているだけではわからないことも、移動平均を使ってムラをなくし、さらにグラフで視覚化することでデータ全体の傾向をつかみやすくなります。

季節調整でデータの本質が見えてくる!

移動平均を使ってデータ全体の推移がわかりましたが、もっと細かい視点でデータを分析したいときには「季節調整」が有効です。世の中の人やモノの動きには季節的な要因(例:夏のレジャーやクリスマスなど)が大きく影響します。データに現れる季節的な要因を表す数値を「季節変動値」といい、この季節変動値を取り除くことを「季節調整」といいます。

たとえば、遊園地で3月に新しいアトラクションがオープンした結果、3月の売上が前月比200%だったとします。「すごい!2月の2倍も売上が伸びた!」とぬか喜びしては危険です。3月は春休みや卒業旅行などで毎年お客さんが多く、売上も伸びる月なので、新アトラクションのおかげで売上が伸びたかどうかは判断できません。この場合、季節的な要因を取り除くことで前月との比較の精度があがります

移動平均と比べて、季節調整を行うには少し工程が増えますが、特別難しい計算や操作はありません。まずは、「季節変動値」を求めてみましょう。

季節がどれくらい影響しているかわかる
「季節変動値」をExcelで求めてみよう

先ほど移動平均を求めた表を使用します。移動平均の横に「季節要因」の列を作成し、移動平均の数値が入っている横のセルに「=B13/C13」と入力します。

「季節変動値」をExcelで求めてみよう1

オートフィルなどを使い、下のセルに数式をコピーします。小数の値が表示されますので、わかりやすいように小数点第2位までの表示にしておきます。ここで求めた値をこの後の作業がしやすいように、次のようにまとめ直します。

「季節変動値」をExcelで求めてみよう2

これで、各月の季節変動値が求められました。この数値だけでもおおよその季節要因の影響度がわかります。ここからさらに季節調整を行うために、「トリム平均」という平均を使って季節変動値の平均「季節指数」を求めていきます。トリム平均は最大値と最小値を除外して計算する平均のことをいいます。前回説明したように、平均は「外れ値」の影響を受けやすいという弱点がありました。トリム平均はこの外れ値を排除できるメリットがあります。

精度の高い平均「トリム平均」を求める

トリム平均を求めるために、まず最大値と最小値を求めます。最大値は「MAX関数」、最小値は「MIN関数」で求めることができます。わかりやすいように最大値のセルは青、最小値のセルはオレンジにしました。

トリム平均1

求めた最大値と最小値を除いた値の平均値を求めます。これが「トリム平均」です。
※列で最大値、または最小値が重複する場合は1つだけ除外します。
トリム平均の合計値も求めておきましょう。

トリム平均2

今回、移動平均の区間を「12」に設定しているので、トリム平均の合計が「12」になるように補正します。「補正トリム平均」と「補正値」の行を作成し、補正値には、「12」をトリム平均の合計値(12.14)で割った値を入力します。その補正値を各月のトリム平均に掛けた値を「補正トリム平均」の行に算出します。

トリム平均3

補正トリム平均の合計が「12」になったことを確認しましょう。ここで求めた「補正トリム平均」を「季節指数」と呼びます。この季節指数を使って元のデータから季節要因を排除していきます。

ミクロな視点でデータを分析する

いよいよ季節調整データを仕上げていきます。「移動平均」「季節要因」を入力した表の横に「季節指数」の列を追加し、先ほど算出した季節指数を繰り返し配置していきます。さらにその横に「季節調整済み売上高」の列を作って本来の売上高を季節指数で割ります。オートフィルで数式をコピーしておきましょう。

季節調整1

「季節調整済み売上高」をすべて埋めることができたら折れ線グラフを作成します。移動平均では滑らかなグラフになりましたが、季節調整済みデータ(赤い点線)はよりミクロな動きが見えてきます。

季節調整2

このように、実際のデータから季節指数を考慮したデータを求めることでデータの大まかな傾向だけなく細かい変化を含めた分析をすることができます。また、季節指数を使えば季節に沿った予測をたてることも可能です。

最後に、、、

いかがでしたか?少し手間のかかる分析でしたが、実測値では見えてこなかった数値を見ることができました。データを眺めて一喜一憂するのではなく、データ全体から見えてくる傾向を探ってみると新しい発見があるかもしれません。

Winスクールには、ビジネスの現場で扱うさまざまなデータをExcelを使って分析する手法と、データや数値を正しく理解するための統計に関する知識が学べる「Excelビジネスデータ分析」という講座があります!今回ご紹介した「移動平均」と「季節調整」についてもさらに詳しく学ぶことができますので、もし興味を持っていただけたら一度「無料カウンセリング・受講相談」または「電話・オンライン説明会」にご参加ください。

関連記事