エクセルで加重平均を計算するSUMPRODUCT 関数




加重平均は、エクセルのSUMPRODUCT 関数を活用して計算することができます。

SUMPRODUCT 関数は、対応する配列のデータを掛け合わせて、それらを足し合わせる関数です。

加重平均とは

加重平均とは、各データの重みを加味したうえで計算をした平均値のことです。

単純な平均値は、各データを足し合わせて、データ数で割れば計算できますが、各データの重みが異なる場合には、単純に足し合わせるよりも、各データの重みを加味するべきなのです。

下記の記事を参考にしてください。

参考記事 平均値の種類はひとつだけじゃない。算術平均、加重平均、幾何平均、移動平均を知ろう

参考記事 加重平均の意味と計算方法

とある おにぎり屋さんに、3種類のおにぎりがあります。単価、ある日の販売数であったとします。

おにぎりの種類別の単価、ある日の販売数

おにぎりの種類単価販売数
こんぶ\11060
しゃけ\12040
おかか\100100

この日の商品1個あたりの平均価格を計算するとき、単純に足し合わせていいのかというと、そうではありません。

各商品の販売数が違いますから、販売数が多い商品の単価に、平均値が引っぱられるはずです。120円のしゃけ よりも、100円の おかかの方が売れていますから、平均をとると、100円側のほうに寄らないとおかしいです。

ようするに、各商品の販売数の重みを加味したうえで、平均を計算しないといけないのです。

それをするのが、加重平均です。

加重平均の計算方法としては、

  • 各商品の単価と販売数を掛け、それらを足し合わせて、すべての販売数で割る方法。
  • 単価を各商品の販売数に応じた割合に変えて、それらを足しわせる方法

があります。

加重平均の計算方法

単価を各商品の販売数に応じた割合に変えて、それらを足しわせる方法

こちらのおにぎりの売上表から、商品の平均単価を計算してみましょう。

おにぎりの種類単価販売数
こんぶ\11060
しゃけ\12040
おかか\100100

総販売数200個です。このうち、

  • こんぶは、60個
  • しゃけは、40個
  • おかかは、100個

です。これら各商品の売上数の割合を、各商品単価に掛け合わせます。

  • \110×(60/200)=33.0
  • \120×(40/200)=24.0
  • \100×(100/200)=50.0

計算された数を、足し合わせます。

33+24+50=107

販売した商品の平均単価は、107円となりました。

各商品の単価と販売数を掛け、それらを足し合わせて、すべての販売数で割る方法

さきほど掲載した表に、各商品の単価と販売数を掛けた数値を追加しました。

おにぎりの種類単価販売数単価×販売数
こんぶ\11060\6,600
しゃけ\12040\4,800
おかか\100100\10,000

掛け合わせた数値の合計は、

6,600+4,800+10,000=21,400

となります。すべての販売数は200。これで割ると加重平均となります。

21,400÷200=107

販売した商品の単価は、107円となりました。

後者の「各商品の単価と販売数を掛け、それらを足し合わせて、すべての販売数で割る方法」は、SUMPRODUCT 関数で計算することができます。

SUMPRODUCT 関数での計算方法

エクセルで計算するには、セルに

「=SUMPRODUCT( )」

を入力し、

「=SUMPRODUCT(配列1, 配列2,…)」

データ配列を複数を指定すると、対応する配列のデータを掛け合わせて、それらを足し合わせます。

※SUMPRODUCTは、「和」の意味である「SUM」、「積」の意味である「PRODUCT」の文字を合わせたものでしょう。

  • \110×60
  • \120×40
  • \100×100

の結果を合計した数値が表示されます。

SUMPRODUCT 関数で計算した数値を、すべての販売数200 で割れば、加重平均となります。

加重平均は、107 となりました。