Excel。分析ツールの基本統計量を関数で算出してみよう。【Basic statistics】

Excel。分析ツールの基本統計量を関数で算出してみよう。

＜AVERAGE・STDEV.S・SQRT・COUNT・MEDIAN・MODE.SNGL・VAR.S・KURT・SKEW・MAX・MIN・SUM関数＞

アドインでデータ分析にある基本統計量をつかうことで、データのステータスを手早く確認することができます。

G列とH列に表示されているのが、C列のListeningの基本統計量です。

H列の基本統計量の値は、関数でも算出できるので、確認しておきましょう。

H2の平均は、AVERAGE関数です。

=AVERAGE(C2:C11)

標準誤差は、標準偏差をデータの件数の平方根で除算した値です。

=STDEV.S(C2:C11)/SQRT(COUNT(C2:C11))

で算出することができます。

STDEV.S関数は、「S」なので、数値をサンプルとした標準偏差を算出する関数です。

SQRT関数は、平方根を算出する関数です。

COUNT関数は、数値の件数を算出関数です。

中央値は、MEDIAN関数で算出できます。

=MEDIAN(C2:C11)

最頻値は、MODE.SNGL関数で算出できます。

最頻値は、データ内で一番多く登場した数値のことですね。

=MODE.SNGL(C2:C11)

標準偏差は、先程紹介した、STDEV.S関数で算出します。

=STDEV.S(C2:C11)

標準偏差は、データが平均値からどのぐらい外れているか（散っているのか）を表します。

分散は、VAR.S関数で算出します。

=VAR.S(C2:C11)

尖度（せんど）は、KURT関数で算出できます。

=KURT(C2:C11)

尖度は、正規分布を元に上下にどの程度偏っているかを表す値です。

歪度（わいど）は、SKEW関数で算出できます。

=SKEW(C2:C11)

歪度は、正規分布を元に左右にどの程度偏っているかを表す値です。

範囲は、最大値と最小値の範囲（レンジ）です。

最大値から最小値を減算すれば算出できます。

=MAX(C2:C11)-MIN(C2:C11)

最小は、MIN関数ですね。

=MIN(C2:C11)

最大は、MAX関数ですね。

=MAX(C2:C11)

合計は、SUM関数ですね。

=SUM(C2:C11)

最後は、データの個数なので、COUNT関数です。

=COUNT(C2:C11)

このように、基本統計量は様々な関数をつかって算出しています。

算出する項目が多いため、面倒なので、分析ツールをつかえるようならば、つかったほうが、楽なような気がします。

Excelの様々な関数の読み方や引数などを紹介。今回は、SQRT関数～STANDARDIZE関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SQRT関数～STANDARDIZE関数です。

＜Excel関数辞典：VOL.74＞

今回は、SQRT関数～STANDARDIZE関数までをご紹介しております。

SQRT関数

読み方： スクエアルート　　

分類： 数学/三角 

SQRT(数値)

平方根を求める　

SQRTPI関数

読み方： スクエアルート パイ　　

分類： 数学/三角 

SQRTPI(数値)

円周率と数値の積の平方根を算出する　

STANDARDIZE関数

読み方： スダンダーダイズ　　

分類： 統計 

STANDARDIZE(x,平均,標準偏差)

標準化得点を算出する　

Excel。データ同士に関係性があると出てるけど疑心暗鬼なんです。【correlation】

Excel。データ同士に関係性があると出てるけど疑心暗鬼なんです。

＜SQRT関数とT.DIST.2T関数＞

データがあれば、その関係性があるのかないのかをCORREL関数などをつかうだけで、相関係数を算出することができます。

その数値から、データに何らかの関係があるということがわかります。

例えば次の表でみてみます。

B列の来店数とC列の売上高の相関係数が、F1に算出されています。

F1の数式は、

=CORREL(B2:B11,C2:C11)

で、算出結果から、関係性は強いと判断することができます。

ただ、相関係数を鵜呑みにしていいのか、疑心暗鬼になるかもしれません。

そこで、根拠のある判断をするために検定を行うことができます。

この検定のことを「無相関の検定」と呼んだりします。

また、公式を使って、「検定統計量」を算出します。

検定統計量の公式は、

T=r√n-2 / √1-r^2

です。

検定統計量を算出しないと、T.DIST.2T関数をつかって、相関のあるなしを求めることができます。

F4に、検定統計量の公式に当てはめた、数式をつくってきます。

F4の数式は、

=(F1*SQRT(F3-2))/SQRT(1-F1^2)

SQRT関数（読み方：スクウェアルート）は、数値の平方根（√）を算出することができる関数です。SQRT(4)は√4なので、2を算出することができる関数です。

F5に、T.DIST.2T関数をつかって、無相関の検定を行っていきます。

F5の数式を確認しておきます。

=T.DIST.2T(F4,F3-2)

確率は、「0.0026」と確率が1%（0.01）以下なので、帰無仮説は棄却。

つまり、来店数と売上高には、なんらかの関係があることがいえるという結果がわかりました。

検定統計量の数式をつくるのが、結構面倒ではありますが、T.DIST.2T関数をつかうことで、F1で算出した相関係数の、ある種の裏付けができました。

今回使用した「T.DIST.2T関数」ですが、「ティー・ディスト・ツーテール」。

または、「ティー・ディストリビューション・ツー・テイルド」・「ティー・ディストリビューション・ツーテール」と読むそうです。

T.DIST.2T関数をはじめとして、Excelには、統計計算に関する関数もたくさん用意されているようなので、少しずつ、使える関数を増やして、日ごろ作成している資料にプラスしていくことが出来るといいですね。

Excel。平均値だけではみえてこない、データの散らばりを確認してみよう。【Scatter of data】

Excel。平均値だけではみえてこない、データの散らばりを確認してみよう。

＜AVERAGE関数・SQRT関数＞

会議資料などで、合計を算出したら、だいたい平均値も算出することが多いかもしれませんが、その平均値だけでは、データ全体がどのような状況なのか把握できないわけです。

そこで、せっかく資料を作るなら、色々情報をプラスしてみるというのはどうでしょうか？

次のデータを用意しました。

 

F1に、C2:C11までのデータの平均値を算出しております。

F1の数式は、

=AVERAGE(C2:C11)

算出結果は、「66.3」。

この数値から、だいたい、60～70ぐらいなんだろうというイメージを持つと思いますが、もしかしたら、100点と10点の両極端にわかれているなんてこともありえます。

そこで、データの散らばり具合を数値化していきます。

データと平均値との差を算出します。

データと平均値との差を「偏差」と呼んでいます。

一列増やして、D列に偏差を算出してみました。

D2の数式は、

=C2-$G$1

オートフィル機能をつかって、数式をコピーしました。

次に、算出した偏差の正負を取り除くために、偏差を二乗します。

E2の数式は、

=D2^2

算出したら終わりではなくて、この偏差二乗を合算します。

この合算したした値を「偏差平方和」とか「変動」と呼んでいます。

ちょっと難しい感じになってきましたが、計算式自体は大したことをしていませんので、慌てないようにしましょう。

ゴールへ向けた、途中計算結果だと思いましょう。

データ数が多くなると、当然、この偏差平方和は、とんでもなく大きくなってしまうので、合っているのか不安になることがあります。

H2の数式は、

=SUM(E2:E11)

算出した「偏差平方和」をデータ件数で除算したものが、「分散」と呼ばれています。

分散の公式が、

分散＝偏差平方和÷データ件数なので、偏差平方和を算出したわけですが、最初から、分散のみを算出するのであれば、偏差二乗の平均値を算出しても、「分散」を算出することができます。

分散というのは、データの散らばり具合を表す一つの指標です。

ここでちょっと、統計のお話し。

実際の統計では、処理するデータが、データ全体を表しているとは限りません。

通常は、一部を取り出して処理します。

この取り出した一部のデータを「標本」と呼んでいます。

標本を対象とした分散は、データ全体を対象とした分散よりも若干大きくなるので、データの個数から1を減算させた、不偏分散を算出します。

 

わかりにくく感じるのは、算出している数値は「平均値からの距離」だということ。

もとに単位（点数）に戻してあげる必要があります。

先程、二乗したので、平方根を取ればいいわけですね。

SQRT関数をつかっていきます。

この算出した結果を、「標準偏差」と呼んでいます。

なんか聞いたことがあるかもしれません。

標本標準のH5の数式は、

=SQRT(H3)

分散の平方根です。

標本標準偏差のH6の数式が、

=SQRT(H4)

不偏分散の平方根です。

これで、平均値からプラスマイナス14.6点程度のバラツキがあることがわかりました。

Excel。標本データ数が少ない場合は、t分布（スチューデントのt分布）を利用します。【Student's "t "distribution】

Excel。標本データ数が少ない場合は、t分布（スチューデントのt分布）を利用します。

＜COUNT・AVERAGE・STDEV.S・T.INV.2T・SQRT関数＞

データがどのようになっているのか確認したくても、母集団の標準偏差がわからない。

さらに、サンプルデータの件数も少ない場合は、「t分布」を利用して推測することができます。

t分布は、スチューデントのt分布の略称ですね。

次のデータを使って必要な値を算出してみましょう。

 

最初は、標本数を算出します。

データ件数を算出しますので、COUNT関数をつかいます。

E2の数式は、

=COUNT(B2:B11)

算出結果は、10

標本平均ですが、これは、お馴染みAVERAGE関数で算出しますので、

E3の数式は、

=AVERAGE(B2:B11)

算出結果は、141.9

標本標準偏差を算出するには、STDEV.S関数で算出することができます。

今回は、サンプルデータが少ないことと、母集団の標準偏差がわからないため、STDEV.S関数を使います。

E4の数式は、

=STDEV.S(B2:B11)

関数自体は馴染みが薄いのですが、範囲選択を設定するだけで、簡単に算出することができます。

STDEV.S関数とSTDEV.P関数がありますが、SはSamle=標本の頭文字で、PはPopulation＝母集団を意味しています。

今回はサンプルデータの標本から算出させるので、STDEV.S関数をつかって算出させます。

算出結果は、34.13518875

 

これで、境界値である、t値を算出するための数値を算出することができました。

今回は、信頼度を95%とします。

自由度のE7は、

=E2-1

という数式を設定しています。

自由度は、独立して自由に選べる数値の数です。

t分布の場合は、標本数-1が自由度になります。算出結果は9ですね。

境界値（t値）を算出していきます。

t値も関数で簡単に算出することができます。

使う関数は、T.INV.2T関数です。

T.INV.2T関数は、t分布の両側の累積確率からt値を算出することができる関数です。

E8の数式は、

=T.INV.2T(1-E6,E7)

1-E6の引数である確率は、100%-95%で5%をつかいます。

算出された結果は、2.262157163

 

境界値（t値）まで算出できたので、上限信頼限界と下限信頼限界を算出しておきましょう。

上限信頼限界と下限信頼限界は、公式があります。

上限信頼限界は、

標本平均＋t値×標準偏差÷√標本数

下限信頼限界は、

標本平均－t値×標準偏差÷√標本数

この公式にのっとって、計算式を設定していきましょう。

なお、√（ルート）は、SQRT関数で算出することができます。

よって、上限信頼限界のE9には、

=$E$3+$E$8*$E$4/SQRT(E2)

オートフィルで数式をコピーして、下限信頼限界の計算式を作るため、絶対参照を使っています。

下限信頼限界のE10には、

=$E$3-$E$8*$E$4/SQRT(E3)

という計算式を設定します。

 

計算式から、上限信頼限界は、

166.318843

下限信頼限界は、

135.417629

と算出することができました。

このことから、母平均の区間指定結果は、信頼度95%とした場合。

135.417629≦μ≦166.318843

ということがわかりました。

なので、4/17の204は、外れすぎというところでしょうか。

このようなことが、関数をつかうことで、比較的簡単に算出することができます。

Excel。事務職のデータ分析25。データ分析ツールで簡単に算出できるけど、自分で偏差値を算出するには？【Deviation value】

Excel。事務職のデータ分析25。データ分析ツールで簡単に算出できるけど、自分で偏差値を算出するには？

＜分散～偏差値：VAR.P/STDEV.P/SQRT/COUNT関数＞

Excelのアドインで「データ分析」機能を使えば、
あらかた分析系の数値を算出することが出来るのですが、

いったいどうやって算出したのか？
どこの数値からこの数値を算出したのか？
わかりにくいので、今回は、通しで、分散から偏差値までを求めていきます。

【分散を求める】

分散は、データのばらつき具合をみることが出来ます。
使う関数は、VAR.P関数ですね。

この表を使って、分散を算出します。

C12の数式は、
=VAR.P(C2:C11)

です。この数式をオートフィルでE12までコピーしています。

分散の数値が大きれければ、それだけ、ばらばら具合が大きいことを表しています。

3回目はあえて、全部同じ数値にしていますので、
ばらつきがないので、0（ゼロ）と算出されたわけです。

なお、分散は、各数値を平均の差の2乗和をデータの個数で割ったものなのですが、
「各数値を平均の差」はそのまま和算すると、
プラスとマイナスで相殺されてしまうので、2乗してから「和」することで、
相殺されることを防いでいます。

そこで、この分散の2乗をとったもの、
つまり、分散の平方根をとったのが、次の『標準偏差』です。

【標準偏差】

標準偏差を算出するには、STDEV.P関数を使います。

C13の数式は、
=STDEV.P(C2:C11)
となっています。

ちなみに、VAR.PのPやSTDEV.PのPですが、
このPは、母集団のPopulationのPなので、
母集団全体とみなした場合は、Pを使い、
一部のデータとみなした場合は、Sをついている関数を使います。

【標準誤差】

分析ツールを使うと、その中に、「標準誤差」という項目がありますが、
これはどのように算出しているのでしょうか？

標準誤差とは、母集団データの平均と標本データの平均とのずれを示しています。

そこで、標準誤差を算出する公式があります。
=STDEV.P(範囲)/SQRT(COUNT(範囲))

算出したのが次の図ですね。

C14の数式は、
=STDEV.P(C2:C11)/SQRT(COUNT(C2:C11))
SQRT関数は、平方根を算出する関数ですね。

【標準化得点：Z得点】

15行目に平均を算出しておきます。

データのばらつきを加味して、
全体の中での位置を知ることが出来るのが、「標準化得点」ですね。

Z得点ともいわれています。

この標準化得点は、偏差値の元になっている数値です。

正数ならば、平均値よりも大きく平均値よりも遠くなるほど、値は大きくなります。

負数ならば、平均値よりも小さく平均値よりも遠くなるほど、値は小さくなります。

その、F2の数式は、
=STANDARDIZE(C2,C$15,C$13)
となっています。

この数値を算出することで、偏差値を算出することができます。

【偏差値】

偏差値は、皆さんお馴染みの、
学力試験などの結果を比べる指標として使われていますよね。

この偏差値を一発で算出する関数はありません。
偏差値は次の公式で算出することができます。

=10×（得点－平均）÷標準偏差＋50

この公式の最後の+50は、偏差値は50を中心とした値になるように調整しています。

25～75までの間にデータの99%が分布することになります。

また、この公式よりも、Z得点を算出しているならば、
次の公式を使うと、もっと簡単に偏差値を算出することが出来ます。

=Z得点×10＋50

H2の数式は、
=10*(C2-C$15)/C$13+50

J2の数式は、
=F2*10+50

どちらでも算出することが出来ます。
今回は、アドインのデータ分析で算出された結果は、
どのようにして算出されたのか？ということで、
分散～偏差値まで流れでご紹介してみました。

Excel。事務職のデータ分析その4。標準偏差の算出方法を抑えておきましょう。【standard deviation】

Excel。事務職のデータ分析その4。標準偏差の算出方法を抑えておきましょう。

＜偏差・分散・標準偏差とAVERAGE関数・SQRT関数＞

最近は、事務職でも資料作りの一環で、
データ分析系の資料作成をする人が増えてきたそうですので、
あまり馴染みがないものも少しずつ慣れていくようにしましょう。

ということで、今回は、平均値や中央値を算出しても、
そのデータのバラツキがどのぐらいあるのか？
を知ることはビジネスで重要なコトです。

Excelでは、標準偏差を一発で算出することができる関数。
STDEV.P関数
STDEV.S関数
がありますが、一発で算出してしまうと、
何をやっているのか？が全くわかりません。

ですので、今回は、順を追って確認していく事にしましょう。
次のデータがあります。

まずは、
年齢というデータが年齢の平均値とどのぐらい差があるのかを算出します。

この『それぞれのデータとデータ全体の平均値の差』を
【偏差】と呼んでおります。

D列に【偏差】を算出します。計算式自体は簡単です。
=$C$23-C3
平均値はすでに算出しておりますので、
絶対参照にして、データで減算します。

オートフィルを使って数式をコピーしておきましょう。

ここで気をつけないといけないのが、
【偏差】というので、【標準偏差】と勘違いする人がいますが、
違いますのでご注意ください。

偏差は、それぞれ、
平均値からどのぐらい離れているのか？ということですので、
プラスもあればマイナスもあります。

次に、偏差の2乗を算出します。

D列の偏差を合計すると0（ゼロ）になってしまうので、
バラツキの平均を算出することが出来ません。

【2乗してバラツキの平均を算出】

そこで、2乗してバラツキの平均を算出します。

なぜ、プラスマイナスを除く絶対値を算出して、
その平均値を算出する方法をとらないのか？というと、
統計学的に扱いが難しいので、
絶対値を使った「平均偏差」と呼ばれる方法は、
実際の分析で用いられることは少ないそうです。

では、E列に、偏差の2乗の数式を作成してきます。
E3には、
=D3^2
という数式が設定されています。
オートフィルで数式をコピーしましょう。

偏差の2乗が算出できましたので、この平均値を算出しましょう。
この平均値の事を【分散】といいます。

E23の数式は、
=AVERAGE(E3:E22)
ですね。

単なる平均値を求めるだけですから、難しい関数は使っておりません。

しかし、この【分散】は、偏差の2乗の平均値でしかありません。
バラツキの大きさはわかっても、
もともとC列は年齢というデータでしたから、
分散は単位の無いデータなので、2乗してあるデータを元に戻すと単位。

今回は、「歳」という単位に戻せます。

戻してあげることによって、比較しやすい値になります。

【平方根はSQRT関数】

では、2乗したデータを元に戻すには、
平方根の関数である。SQRT関数を使っていきます。

E24にSQRT関数を使って、分散の平方根を算出します。

なお、この『分散の平方根』を【標準偏差】と呼んでいます。

E24の数式は、
=SQRT(E23)
これで、算出できました。

さて、何がわかったのかというと、
今回の20名の方の年齢のバラツキの度合いを求めることが
出来たということになります。