BDAstyle

ビジネスデータ分析ツールの作成 with Excel

基本統計量の計算 with Excel 1/3

算術平均・幾何平均・加重平均の計算

1. 算術平均[相加平均, 単純平均](Mean)

Step 0シチュエーションの設定

下表は,企業Xの事業Yに関する12期(t)の売上です。

初期データ

Step 1AVERAGE関数

算術平均は,ひろく「平均」と呼ばれるところの値です。エクセルではAverage関数により求められます。

Average関数に拠らず定義式に沿って計算する場合(デフォルトで非表示|クリックで展開)

関数を利用しない場合,次の式を利用します。

x bar=Σ(xi)/n

このとき,式中の記号i,xは順に「no.」「売上」の系列に相当します。

すなわちxiは 系列「売上」のi番目の要素のことを意味します。

またnはデータのサイズを意味します。

最初に,式の分子の部分(赤い囲み)から計算したいと思います。これは1から12期までの売上の総和です。

ここでは,Sum関数を使って売上の合計を求めるものとします。

=SUM(B2:B13)

次にこれをnで除算する必要があるので…

Count関数でnを導き,下図のような式をととのえて完了です。

=B15/COUNT(A2:A13)

Step 2算術平均・計算完了

12期の売上に関する算術平均は,次のとおりです。

2. 幾何平均[相乗平均](Geometric Mean)

Step 0シチュエーションの設定

ここで先の表(企業Xの事業Yに関する12期(t)の売上)に関して,対前期比を加えてみます(下図)。

この対前期比については,算術平均を計算すると どういった値が導かれるのでしょうか? …深く考えずに11期の平均を求めると,値は1.678(倍)と示されました。

はたしてこの値が,対前期比の“平均”を表現するものとして妥当と言えるのかどうか,確認してみたいと思います。具体的には1期目の値を所与として,期を経るごとに1.678倍していきます。そして12期目においてどのような値が出現するのかを見ていくと…

…下のように,12期目における売上(16852千円)よりはるかに大きな値が導かれました。

このように,比の平均を確認する場合には,算術平均では的を射ないところがでてきます。そこで,これを幾何平均に替えてみたいと思います。

Step 1GEOMEAN関数

幾何平均は,エクセルではGeomean関数により求められます。

GeoMean関数に拠らず定義式に沿って計算する場合(デフォルトで非表示|クリックで展開)

関数を利用しない場合,次の式を利用します。

s^2=Σ(xi-xbar)^2/n

このとき,式中の記号i,xは順に「t」「対前期比」の系列に相当します。

すなわちxiは,系列「対前期比」のi番目の要素のことを意味します。またnXi要素の数となります。

それでは,式のルートの中の部分(赤い囲み)から計算したいと思います。これはx1からx112~12期)までの「対前期比」列の総積(この列すべての値を掛けたもの)です。

これはProduct関数にて計算できます。

=PRODUCT(C3:C13)

つづいて,上で求めた総積のn乗根 (この例ではn=11)をとる必要があるので…

Power関数の1番目の引数に 「対前期比」列の総積を,2番目の引数に"1/n"を指定してこれを求めます[または=C17^(1/COUNT(A3:A13))でも]。

=POWER(C17,1/COUNT(A3:A13))

Step 2幾何平均・計算完了

11期の対前期比に関する幾何平均は,1.530と求められました。

Step 3付記:確認

「2-Step 0 シチュエーションの設定」と同様にして,はたしてこの値が妥当と言えるのかどうかを確認しておきたいと思います。1期目の値を所与として,先と同様に,期を経るごとに1.530倍していくと…12期目において求められた値は売上金額と等しくなっていることがわかります。したがって,「対前期比」のように“前期のx倍”に関して平均を求めるような場合には,算術平均よりも幾何平均を用いた方がbetterなようです。

ただし,幾何平均の計算においては0および負の値をあつかうことができません(cf. 幾何平均―"Wikipedia")。

したがって「対前期比」でなく,当期と前期の差に注目する「伸び率」などに関しては,往々にして上記の性質に触れてしまいます。これに平均を据えておきたいとき,伸び率x%+1=対前期比となる点を鑑みて,「対前期比」の幾何平均から1を引いた値を便宜的にあてておくのもひとつの方法かと思います。


「2.幾何平均」に関して参考にしたWebサイト

3. 加重平均(Weighted Mean)

Step 0シチュエーションの設定

ライバル会社の商品Zに関する,取扱店の売上分布を記したヒストグラムを偶然に入手しました(下図)。これに関して,売上金額の平均を上司に報告したいと思うのですが,データの性格上,残念なことに元データまでは入手することができません。

そこでヒストグラムの階級値から算術平均を求めてみると,「50」という値が出てきました。

=AVERAGE(B2:B9)

はたして「50」は「平均」を示す値として適切か…といえば否でしょう。このようなケースにおいては,加重平均を用います(これを求めるデフォルトのワークシート関数はありません)。

Step 1度数分布表の作成

階級値につづき,度数をヒストグラムから読み取って,度数分布表を作成します(下表)。

Step 2定義式

加重平均は次の式で求めます。

x bar w=(Σwixi)/(Σwi)

Step 3記号の意味(1)

上のシート構成のとき,式中の記号i,x,wは順に「no.」「階級値」「度数」の系列に相当します。

Step 4記号の意味(2)

すなわちxiは,系列「階級値」のi番目の要素のことを,wiは,系列「度数」のi番目の要素のことを意味します。またnは階級の数(=Xiないしはwi要素の数)となります。

Step 5定義式の分子・「階級値×度数」の総和の計算(1)

最初に,式の分子の部分(赤い囲み)から計算したいと思います。これは階級値×度数の総和です。

Step 6定義式の分子・「階級値×度数」の総和の計算(2)

階級値×度数の総和を,たとえば下図のような式で求めます(ここではSumProduct関数を使用)。

=SUMPRODUCT(B2:B9,C2:C9)

Step 7定義式の分母・「度数」の総和の計算(1)

つづいて式の分母の部分(赤い囲み)を計算します。これは度数の総和です。

Step 8定義式の分母・「度数」の総和の計算(2)

度数の総和は,下の図のように求めるものとします(ここではSum関数を使用)。

=SUM(C2:C9)

Step 9「“階級値×度数” の総和」÷「“度数” の総和」

最後に,階級値×度数の総和度数の総和で除してやります。

=D13/C13

Step 10加重平均・計算完了

先のヒストグラムから求められた加重平均は,次のとおりです。

x bar w=31.8

Next

次頁は「中央値」および「最頻値」の例です。

その他の参照