Excel

変化率・成長率の平均にAVERAGE関数は間違い!(相乗平均・幾何平均)

伸び率(成長率)や下落率などの変化率の平均を求める際に、単純平均(算術平均)やAVERAGE関数を使っていませんか?

比率の平均を求めるには、足してデータの数で割る算術平均より、掛けて累乗根をとる「相乗平均(幾何平均)」のほうが適しています。

 

この記事では、相乗平均(幾何平均)のほうが適している理由、「相乗平均(幾何平均)」を簡単に求めることができるGEOMEAN(ジオミーン)関数の紹介、そして、関数を使わないで「相乗平均(幾何平均)」を求める計算方法について説明します。

 

相乗平均・幾何平均が適しているわけ

過去の売上の伸び率から来年の売上を予測するときなどには、1年あたり、1期あたりの平均成長率が必要になることがあります。
この平均成長率のように比率の平均を求める際には、掛けて累乗根をとる「相乗平均(幾何平均)」のほうが適しています。

相乗平均・幾何平均が適している理由を、具体例を用いて説明します。

売上高が「80」→「100」→「120」→「150」と推移しているとします。
この推移の「算術平均」は 1.2333 、「相乗平均」は 1.2331 です。

売上高の推移と平均

 

では、「80」を基準として、「算術平均」 、「相乗平均」の3年後を計算してみましょう。
すると、「算術平均」の場合は 1.50.08 になるのに対し、「相乗平均」の場合は 150.00 になります。

算術平均、相乗平均の3年後

 

もともとの2023年の売上高は 150 でしたので、「相乗平均」の場合はズレが生じていないのに対し、「算術平均」の場合はズレが生じてしまいました。
具体例ではありますが、「相乗平均」の方が適しているというのが理解いただけたでしょうか。

 

相乗平均・幾何平均を求める

次は、GEOMEAN(ジオミーン)関数などを使って「相乗平均(幾何平均)」を求める方法と関数を使わないで求める計算方法を紹介します。

GEOMEAN関数を使う

「相乗平均(幾何平均)」には、GEOMEAN関数を使うのがオススメです。
「相乗平均(幾何平均)」を求めたい「値」が入力されたセル範囲を関数の引数に指定する(セル範囲を選択する)ことで求めることができます。

 

GEOMEAN関数の書式

GEOMEAN関数は、指定した「数値」の「相乗平均(幾何平均)」を求める関数です。
数値には、「」や「セル範囲」を指定します(255個まで指定可能)。
セル範囲に「文字列」「論理値(TRUE や FALSE )」「空白セル」がある場合、そのセルは無視されます

書式
GEOMEAN( 数値1 [, 数値2 ]・・・ )

 

今、C3セルに「1.25」、C4セルに「1.2」、C5セルに「1.25」という比率が入っているとします。
この3つの比率の「相乗平均(幾何平均)」をC7セルに求めるには、C7セルに =GEOMEAN(C3:C5) と入力します。
すると、「1.2331・・・」という値が返されます。

GEOMEAN関数

 

関数を使わないで計算する

比率の平均「相乗平均(幾何平均)」を関数を使わないで計算するには、比率を掛けて累乗根をとります
Excelで累乗根^(1/2)^(1/3) のように分数で表現します

C3セル、C4セル、C5セルに比率が入力されていますので、この3つのセルを[ * ](アスタリスク)で掛けます。
比率は3個あるので3乗根、つまり、 ^(1/3) をとります。

C7セルには、=(C3*C4*C5)^(1/3) と入力すれば「相乗平均(幾何平均)」を求めることができます。

関数を使わないで計算する

 

PRODUCT関数、COUNT関数を組み合わせる

例題のように比率が3つくらいなら、掛け算で求めても問題ありませんが、比率がたくさんある場合に、1つ1つ掛けていくのは面倒です。
そんなときは積を求めるPRODUCT関数と、個数を求めるCOUNT関数を組み合わせる方法もあります。

C7セルには、=PRODUCT(C3:C5)^(1/COUNT(C3:C5)) と入力します。

PRODUCT関数ほか

 

まとめ

伸び率(成長率)や下落率などの変化率の平均を求めるには、「相乗平均(幾何平均)」を求めることができるGEOMEAN関数を使いましょう

「相乗平均(幾何平均)」は、掛けて累乗根をとるということを知っていれば、PRODUCT関数とCOUNT関数を組み合わせる方法もあります。

いずれにしても、変化率の平均を求める際に、足してデータの数で割る単純平均(算術平均)やAVERAGE関数を使わないようにしてください

 

関連記事

 

 

 

参考文献

-Excel
-, ,

© 2022 まなびっと Powered by AFFINGER5