Excel。GEOMEAN関数で数値の相乗平均を算出できます。
＜関数辞典：GEOMEAN関数＞
GEOMEAN関数
読み方： ジオミーン
分類： 統計
GEOMEAN(数値1,[数値2],…)
数値の相乗平均を算出する Geometricの略
4/19/2024
2/03/2024
Excel。普通の平均では、おかしい感じがするときは、幾何平均で処理してみては【geometric mean】
Excel。普通の平均では、おかしい感じがするときは、幾何平均で処理してみては
＜GEOMEAN関数＞
データの中に突出した値がある場合、お馴染みのAVERAGE関数で平均値を算出すると、思っていたようなイメージと異なる結果になります。
これは、算術平均のAVERAGE関数の欠点でもあるのですが、総和してその件数で除算するので、どうしても、突出した値が含まれていると、その影響を大きく受けてしまうからです。
そのため、イメージと異なる平均値が算出されてしまうわけです。
このような場合、AVERAGE関数だけではなく、GEOMEAN関数をつかってみましょう。
E2には、
=GEOMEAN(B2:B8)
という数式を設定しました。
算出された結果は、突出した値に引っ張られていますが、算術平均のAVERAGE関数よりも影響は少なくなっています。
幾何平均のGEOMEAN関数をつかってみるのもいいかもしれません。
9/29/2021
Excel。AVERAGE関数だけではもったいない。データの特徴を把握する2つの関数を追加してみよう。【Grasp】
Excel。AVERAGE関数だけではもったいない。データの特徴を把握する2つの関数を追加してみよう。
＜AVERAGE・GEOMEAN・MEDIAN関数＞
Excelをつかってデータから合計値や件数。
それに平均値を算出してみる。
なんてことをするわけが、それだけでは、そのデータ全体がみえてこないので、簡単な関数をつかって、把握するための数値を算出してみましょう。
次のデータを用意しました。
説明用なのでデータ数は少ないですが、算出したいものを確認していきます。
E1には、平均値が算出されています。
オートSUMボタン内にもある、お馴染みの、AVERAGE関数をつかっています。
E1の数式は、
=AVERAGE(B2:B11)
算出結果は、「18」
だけど、何かおかしいですよね。
「18」ってみた感じで大きいですよね。
原因は、B9の「72」という値。
とびぬけていますね。
いわゆる「外れ値」ですね。このようにとびぬけた値があると、AVERAGE関数は、そのデータに引っ張られる特徴があります。
つまり、AVERAGE関数は、算数の世界で使用する「算術平均」だからです。
とびぬけたデータがあることを考慮すると、「幾何平均」を算出するといいわけですね。
幾何平均は、かけ離れた値の影響を受けにくい特性があります。
幾何平均を算出する関数は、GEOMEAN関数です。
E2の数式は、
=GEOMEAN(B2:B11)
算出結果は、13.21…と、外れ値の影響を受けてない印象の結果が算出できました。
AVERAGE関数だけをつかった資料よりも、GEOMEAN関数の結果も併記してあげると、いいですね。
次に、データ全体がだいたい同じ数値の幅ならばいいのですが、複数の塊で集まっていることもあります。
分布の偏りがあるかもしれません。
偏りがある場合には、平均値だけでは実際のデータとの印象に誤差が生じる恐れがありますので、「中央値」も算出したいところですね。
E3の数式は、
=MEDIAN(B2:B11)
MEDIAN関数をつかうことで、中央値を算出することができます。
中央値は、すべての値を昇順に並べて、ちょうど、真ん中にある値です。
単純に平均値を算出するとデータの偏りがみえてきませんが、中央値や幾何平均をつかうことで、分布の偏りがみえてきます。
あと、最頻値とかを算出ということも考えますが、データが極端に偏っている場合だとつかうのもいいかと思いますが、一般のデータでは、あまりつかわないように思われます。
ということで、オートSUMボタンにあるAVERAGE関数の平均だけではなく、幾何平均のGEOMEAN関数や、中央値のMEDIAN関数も合わせてつかってみると、よりデータを把握できます。
9/26/2021
Excel。いつもの資料にデータの特徴を把握する2つの関数を追加してみよう。【Data features】
Excel。いつもの資料にデータの特徴を把握する2つの関数を追加してみよう。
＜AVERAGE・GEOMEAN・MEDIAN関数＞
Excelをつかってデータから合計値や件数。
それに平均値を算出してみる。
なんてことをするわけが、それだけでは、そのデータ全体がみえてこないので、簡単な関数をつかって、把握するための数値を算出してみましょう。
次のデータを用意しました。
説明用なのでデータ数は少ないですが、算出したいものを確認していきます。
E1には、平均値が算出されています。
オートSUMボタン内にもある、お馴染みの、AVERAGE関数をつかっています。
E1の数式は、
=AVERAGE(B2:B11)
算出結果は、「18」
だけど、何かおかしいですよね。
「18」ってみた感じで大きいですよね。
原因は、B9の「72」という値。
とびぬけていますね。
いわゆる「外れ値」ですね。
このようにとびぬけた値があると、AVERAGE関数は、そのデータに引っ張られる特徴があります。
つまり、AVERAGE関数は、算数の世界で使用する「算術平均」だからです。
とびぬけたデータがあることを考慮すると、「幾何平均」を算出するといいわけですね。
幾何平均は、かけ離れた値の影響を受けにくい特性があります。
幾何平均を算出する関数は、GEOMEAN関数です。
E2の数式は、
=GEOMEAN(B2:B11)
算出結果は、13.21…と、外れ値の影響を受けてない印象の結果が算出できました。
AVERAGE関数だけをつかった資料よりも、GEOMEAN関数の結果も併記してあげると、いいですね。
次に、データ全体がだいたい同じ数値の幅ならばいいのですが、複数の塊で集まっていることもあります。
分布の偏りがあるかもしれません。
偏りがある場合には、平均値だけでは実際のデータとの印象に誤差が生じる恐れがありますので、「中央値」も算出したいところですね。
E3の数式は、
=MEDIAN(B2:B11)
MEDIAN関数をつかうことで、中央値を算出することができます。
中央値は、すべての値を昇順に並べて、ちょうど、真ん中にある値です。
単純に平均値を算出するとデータの偏りがみえてきませんが、中央値や幾何平均をつかうことで、分布の偏りがみえてきます。
あと、最頻値とかを算出ということも考えますが、データが極端に偏っている場合だとつかうのもいいかと思いますが、一般のデータでは、あまりつかわないように思われます。
ということで、オートSUMボタンにあるAVERAGE関数の平均だけではなく、幾何平均のGEOMEAN関数や、中央値のMEDIAN関数も合わせてつかってみると、よりデータを把握できます。
7/23/2020
Excel関数辞典 VOL.33。GAUSS関数～GROWTH関数
Excel関数辞典 VOL.33。GAUSS関数～GROWTH関数
＜Excel関数＞
今回は、GAUSS関数～GROWTH関数までをご紹介しております。
GAUSS関数
ガウス
指定した標準偏差の範囲になる確率を算出
GAUSS(値)
GCD関数
ジーシーディー
整数の最大公約数を算出
GCD(数値1[,数値2])
GEOMEAN関数
ジオミーン
数値の相乗平均(Geometric)を算出
GEOMEAN(数値1[,数値2])
GESTEP関数
ジーイーステップ
数値が境界値以上かを判定する
GESTEP(数値[,しきい値])
GETPIVOTDATA関数
ゲットピボットデータ
ピボットテーブル内の値を抽出
GETPIVOTDATA(データフィールド,ピボットテーブル[,フィールド1,アイテム1,フィールド2,アイテム2],…)
GROWTH関数
グロウス
指数回帰分析による値を算出
GROWTH(既知のy[,既知のx][,新しいx][,定数])
7/03/2019
Excel。簡単に資料にプラス。会議資料は平均値だけでいいの？【Average】
Excel。簡単に資料にプラス。会議資料は平均値だけでいいの？
＜AVERAGE・MEDIAN・GEOMEAN・MODE.SNGL関数＞
会議でお馴染みの数値で構成された様々な資料。多くの資料には、合計値や平均値を算出しています。平均値が曲者と何度か書いていますが、平均値だけではダメだったということケースも多々あります。
そこで、簡単に資料にプラスするといいものがありますので、確認していきましょう。
アンケート結果の表を用意しました。
最初に、サンプルAとサンプルBの平均値を算出してみましょう。
G2には、
=AVERAGE(C2:C16)
という数式を作っています。H列にオートフィルを使って数式をコピーします。
平均値もしっかり算出することができたので、資料としてはこれで十分といえば十分なのですが、サンプルBのデータをみると、なんか納得いかないというか…
【数字が偏っている場合】
サンプルの数値が、分散しているのならば、平均値は意味を成すのかもしれませんが、サンプルBのように、二極化しているように、（分布が）偏っている場合、平均値と実際のデータとの間に、「感覚の差」が出てしまうことがあります。サンプルBは、1の件数が結構あるのに、平均値が高いイメージが生じています。
このように、データが偏ていることも想定しないといけませんので、資料にプラスしたい計算式があります。
【データの真ん中にある数値＝中央値】
単純にデータを昇順に並べて、その中間地点の数値を、中央値と呼びます。
使用する関数は、MEDIAN関数です。
G9の数式は、
=MEDIAN(C2:C16)
と入力するだけで、中央値が算出することができます。
オートフィルを使って数式をコピーすると、サンプルBは、5と算出されています。
平均値と比べるとサンプルAもBも違っていることが確認できます。
中央値はあくまでも、データの中心地点でしかありませんので、1と10だけのデータだと、1か10が中央値になってしまうので、中央値も万全ではありません。
【数値が離れている時の平均は、幾何平均】
サンプルBのように、数値が偏っている場合や、一部に突出したデータ（外れ値）がある場合には、AVERAGE関数で平均を算出するよりも、幾何平均のGEOMEAN関数を使う方がいい場合もあります。幾何平均は、相乗平均とも呼ばれています。なお、AVERAGE関数は、算術平均です。
G4には、
=GEOMEAN(C2:C16)
という数式を設定します。
オートフィルを使って数式をコピーしてみましょう。
サンプルBは、3.26…とデータの感覚に近いようになってきました。
なので、算術平均のAVERAGE関数だけではなくて、幾何平均のGEOMEAN関数も併記して使用してあげると資料としてプラスすることができます。
ちなみに、もう一つExcel関数には、平均があって、調和平均のHARMEAN関数があります。速度の平均を算出する時に使う関数ですね。
【データの登場回数を知るには、MODE.SNGL関数】
平均値とは別で、データの中でどの数値が一番多いのかがわかれば、データの偏りがわかります。そこで、使用する関数が、MODE.SNGL関数です。
G5の数式は、
=MODE.SNGL(C2:C16)
と設定するだけで、簡単に算出できます。
サンプルAはデータ内で一番多いのが9で、サンプルBは1が一番多く登場していることがわかりました。
このように、平均値だけにとらわれずに、ちょっとしたデータをプラスしてみるといいかもしれませんね。
1/13/2017
Excel。AVERAGE。事務職のデータ分析その1。平均値は数種類ある？
Excel。事務職のデータ分析その1。平均値は数種類ある？
＜AVERAGE関数とGEOMEAN関数とHARMEAN関数＞
様々な分析手法はわからなくても、一般職や事務職でも、
会議資料の一つとして、データ分析用の資料作りをすることもあるようです。
そこで、事務職のデータ分析と題して、ご紹介していきたいと思っております。
そして、今回は、【平均値】について改めてご紹介していきます。
次のような表があります。
従業員の平均値を求めたいとしたら、お馴染みのAVERAGE関数で簡単に算出することには、
なんら問題はありませんよね。
C10の数式は、
=AVERAGE(C3:C9)
単なる平均でしょう。と思うかもしれませんが、データ分析の資料としては、基本の基本。
バカにしてはいけません。平均も大切なデータの一つです。
さて、このAVERAGE関数の平均ですが、実は、「相加平均」といいまして、
対象のグループに含まれる数値の合計を数値の個数で除算して求めますが、
データによっては、いわゆる「相加平均」では、
適切な平均を算出することが出来ない場合があります。
それは、次のケースです。
2012年から2017年までの出荷数と、
対前年の出荷数の比を求めた表なのですが、
この表から、対前年比の平均値を求めてみたいと思います。
H9の数式は、通常のAVERAGE関数を使ったもので、
=AVERAGE(H4:H8)
なんら問題はないように思えますが、検証してみましょう。
平均値なので、2012年の出荷数である1478に、
今求めた、103.36%を5回乗算すれば、2017年の出荷数になるはずです。
G9に次の数式を作成してみましょう。
=G3*H9^5
すると、次のように算出されました。
2017年の出荷数と異なった数値が算出されていますね。
実は比率における平均の計算には、「相加平均」ではなくて、
「相乗平均」で算出させる必要があるのです。
では、AVERAGE関数で求めようとしても
AVERAGE関数は「相加平均」なので求めることは出来ません。
そこで、【GEOMEAN関数】を使って算出させます。
GEOMEAN関数は、「相乗平均」を求めることが出来る関数なのです。
では、H10をクリックして、GEOMEAN関数ダイアログボックスを表示しましょう。
数値1に、範囲である、H4:H8を入力して、OKボタンをクリックしましょう。
H10の数式は、
=GEOMEAN(H4:H8)
では、先ほどと同じように、検証してみましょう。G10に
=G3*H10^5
という数式を作成してみましょう。2017年の数値と合致したのが確認できましたね。
このように、AVERAGE関数で算出できないものもあります。
さらに、もう一つあります。それは、「調和平均」。
調和平均は、【HARMEAN関数】で算出することができます。
調和平均を使うケースは、「平均時速」を求めるケースです。
今回は、データ分析の基本の基本である。平均値を紹介しました。
そして、平均には、
「相和平均」AVERAGE関数
「相乗平均」GEOMEAN関数
「調和平均」HARMEAN関数
というのがあるんですね。用途によって、使い分ける必要があります。
登録:
投稿 (Atom)