Excelで単純移動平均・加重移動平均・指数移動平均を計算する方法

学校の授業で、平均値の計算方法を学んだことがある方は多いでしょう。平均の計算式は非常にシンプルで、データ内のすべての値を合計し、その結果を値の個数で割るだけです。しかし、移動平均も実生活で幅広く活用されているもう一つの平均です。株式市場や売上分析など、さまざまな分野で利用されています。

移動平均には「単純移動平均」「加重移動平均」「指数移動平均」の3種類があります。この記事では、Excelでこれら3種類の移動平均をそれぞれ計算する方法を詳しく解説します。

関連記事: ExcelのMIN関数・MAX関数・AVERAGE関数の使い方

Excelで移動平均を計算する方法

Excelで移動平均を計算する方法は主に2つあります。1つはMS Excelに組み込まれたツールを使う方法、もう1つは数式を使う方法です。

1] データ分析ツールを使用する方法

まず、Excelのデータ分析ツールを使って移動平均を計算する方法を紹介します。MS Excelには単純移動平均を計算できるツールが用意されています。ただし、データ分析ツールはアドインのため、初期状態では有効になっていない場合があります。以下に、このツールを有効化する手順を示します。

1] 「ファイル」タブをクリックします。

2] 「オプション」を選択します。

3] ダイアログボックスが開くので、「アドイン」をクリックします。

4] ダイアログボックス右側の下部にあるドロップダウンメニューから「Excel アドイン」を選択し、「設定」ボタンをクリックします。

5] 「Analysis ToolPak(分析ツール)」にチェックを入れ、「OK」ボタンをクリックします。

6] 以上の手順でデータ分析ツールがMS Excelにインストールされます。「データ」タブで確認できます。

関連記事: Excelで2つの数値の差異(パーセント)を計算する方法

これで単純移動平均を計算する準備が整いました。ここでは、ある月の最初の10日間の気温データをサンプルとして作成し、計算方法を説明します。

以下の手順に従って、データ分析ツールで単純移動平均を計算してください。

1] 「データ」タブをクリックし、上のスクリーンショット(手順6参照)のように「データ分析」オプションを選択します。

2] 小さなダイアログボックスが開くので、「移動平均」を選択して「OK」をクリックします。これが単純移動平均です。

3] 単純移動平均を計算したいデータの入力範囲を指定します。サンプルデータではB2からB11セルにデータがあるため、「B2:B11」と入力します。次に「区間」を入力します。出力範囲には、結果を表示したいセルのアドレスを入力します。ここではE2を選択しました。入力が完了したら「OK」をクリックします。

4] 結果が表示されます。下のスクリーンショットのように、最初の3行に#N/Aエラーが表示されています。これは「区間」に4を入力したためで、4日移動平均(SMA)であることを意味します。

関連記事: Excelで単利を計算する方法

上記の方法1では、Excelのツールを使って移動平均を計算しました。ただし、方法1で計算できるのは単純移動平均のみです。方法2では、数式を使って3種類すべての移動平均を計算します。

2] 数式を使った単純移動平均(SMA)の計算

ここでも同じサンプルデータを使用します。

1] 4日SMAを計算したい場合は、5行目(4日目)にあたるセルに次の数式を入力し、「Enter」キーを押します。

=AVERAGE(B2:B5)

2] 次に、選択したセルの右下隅にカーソルを合わせます。カーソルが「+」マークに変わったら、マウスの左クリックを押しながらE11セルまでドラッグします。これで数式が他のセルにコピーされます。

3] 結果が表示されます。

関連記事: Excelで長方形・三角形・円の面積を計算する方法

3] 数式を使った加重移動平均(WMA)の計算

次に、加重移動平均を計算しましょう。ここでも同じサンプルデータを使用します。WMAを計算するには、各値に重みを割り当てる必要があります。加重移動平均の計算式は次のとおりです。

WMA = [(最新の値 × 重み) + (前の値 × 重み) + ...] / (すべての重みの合計)

ここでは、最新の値に70%、その前の値に20%、さらにその前の値に10%の重みを割り当てた3点WMAを計算します。この条件に基づき、E4セルに次の数式を入力します。

=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)

最初の2つのセルが空欄になっているのは、3点WMAを計算しているためです。あとは、SMAの計算時と同様に、セルを最後のセルまでドラッグして数式をコピーすれば、結果が得られます。

関連記事: Excelで中央値を計算する方法

4] 数式を使った指数移動平均(EMA)の計算

EMAを計算するには、最初のEMA値が必要です。これはSMAを計算することで求められます。また、重み乗数または平滑化定数(K)も必要です。EMAの基本計算式は次のとおりです。

EMA = 最新の値(当日の値) × K + 前日のEMA値 × (1 - K)

1] SMAを計算したときと同じ表を使用します。この例ではK = 0.3とします。最初のEMA値は最初のSMA値と等しくなります。ここでは4日SMAとEMAの値を計算します。

2] 次に、F6セルに次の数式を入力します。

=B6*$G$1+F5*(1-$G$1)

なお、上記の数式ではG列のセル参照を絶対参照($G$1)でロックしています。これは、すべてのEMA計算で同じKの値を使用する必要があるためです。

3] 最後に、セルをドラッグして残りのセルに数式をコピーします。

以上で、MS Excelで3種類すべての移動平均を計算できました。この記事がお役に立てば幸いです。

こちらもおすすめ: ExcelでGPA(成績平均点)を計算する方法