Excelで加重平均を計算するにはどうすればよいですか?

平均は、データの概要を把握する最良の方法です。しかし、平均値から偏った値が得られる場合があります。このような状況では、[毎回] 加重平均を計算するのが最善の方法です。

そして、SUMPRODUCT 関数と SUM 関数を組み合わせて数式を使用するのが最良の方法であることがわかりました。

この方法はシンプルで簡単に適用できます。そこで今回は、ExcelでSUMPRODUCTとSUMを使って加重平均を計算する方法を紹介したいと思います。

通常と加重平均の差

以下の例では、通常の平均として 1658、加重平均として 1445 があります。この違いを2つの点に分けて説明しましょう。

• まず、 1658 (平均) に 68 (数量) を掛けると、112742 が得られますが、これは合計金額と等しくありません。ただし、1445 (加重平均) に 68 (数量) を掛けると、合計金額に等しい 98289 が得られます。
• 2 番目:製品-1 は価格が最も低く、数量が最も多く、製品-4 は価格が最も高く、数量が最も少ないです。

通常の平均を計算する場合、数量は考慮されません。数量が変化しても平均価格には影響しません。そして、加重平均では、数量を重みとして取ることができます。

Excelで加重平均を計算する手順

加重平均を計算するには、上の例で示したのと同じデータ テーブルを使用します。

次に、次の 2 つの簡単な手順に従ってください。

• セル C7 に、数式 =SUMPRODUCT(B2:B6,C2:C6)/SUM(B2:B6) を入力します。

• 「OK」をクリックします。

これで、価格と製品の加重平均として 1445 が得られました。先ほども述べたように、SUMPRODUCT は Excel で加重平均または加重平均を計算する簡単な方法です。 SUMPRODUCT の最も優れた点は、単一セル内で配列の乗算と加算ができることです。

 =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

これはどのように作動しますか

これを理解するには、この式を分解する必要があります。

まず、SUMPRODUCT はすべての製品の価格と数量の積を計算し、すべての合計を返します。その後、いくつかの関数で数量の合計が得られます。そして最後に、両方の関数の値を除算して加重平均を取得します。

これをチェックしてください: SUMPRODUCTIVE

加重移動平均を計算する

もう少し進んでみましょう。データ分析の部分に移りましょう。同じ式を使用して、加重移動平均を計算することもできます。

手順は次のとおりです。

• セル D4 に以下の数式を入力し、最後までドラッグします。 =SUMPRODUCT(B2:B4,C2:C4)/SUM(C2:C4)

• この数式をドラッグすると、各月の 3 か月に基づいた移動平均が計算されます。

これはどのように作動しますか

上のセル D4 のスナップショットを確認すると、1 月、2 月、3 月の移動平均が表示されます。そして、セル D5 には、2 月、3 月、4 月の移動平均が表示されます。

したがって、新しい月に移動するたびに、今月と過去 2 か月を含む移動平均が取得されます。数式をドラッグするときにセル参照を相対的にするだけです。