Excel。数値群を母集団全体とみなした標準偏差はSTDEV.P関数で求めます【STDEV.P】

Excel。数値群を母集団全体とみなした標準偏差はSTDEV.P関数で求めます

＜関数辞典：STDEV.P関数＞

STDEV.P関数

読み方： スタンダードディブ・ピー　　

読み方： スタンダードディビエーション・ピー

分類： 統計 

STDEV.P(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する

Pはポピュレーション

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

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

＜Excel関数辞典：VOL.75＞

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

STDEV関数

読み方： スタンダードディブ　　

読み方： スタンダードディビエーション

分類： 互換性 

STDEV(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する　

STDEV.P関数

読み方： スタンダードディブ・ピー　　

読み方： スタンダードディビエーション・ピー

分類： 統計 

STDEV.P(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する

Pはポピュレーション

STDEV.S関数

読み方： スタンダードディブ・エス　　
読み方： スタンダードディビエーション・エス

分類： 統計 

STDEV.S(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する

Sはサンプル

STDEVA関数

読み方： スタンダードディブエー　

読み方： スタンダードディビエーションエー

分類： 統計 

STDEVA([値1,[値2],…)

全データを標本データとみなした標準偏差を算出する　

STDEVP関数

読み方： スタンダードディブピー　

読み方： スタンダードディビエーションピー

分類： 互換性 

STDEVP(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する　

STDEVPA関数

読み方：スタンダードディブピーエー

読み方：スタンダードディビエーションピーエー

分類： 統計 

STDEVPA([値1,[値2],…)

全データを母集団全体とみなした標準偏差を算出する

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

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

＜Excel関数辞典：VOL.75＞

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

STDEV関数

読み方：スタンダードディブ　　

読み方：スタンダードディビエーション

分類： 互換性 

STDEV(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する　

STDEV.P関数

読み方： スタンダードディブ・ピー　　

読み方： スタンダードディビエーション・ピー

分類： 統計 

STDEV.P(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する　

Pはポピュレーション

STDEV.S関数

読み方： スタンダードディブ・エス　　

読み方： スタンダードディビエーション・エス

分類： 統計 

STDEV.S(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する　

Sはサンプル

STDEVA関数

読み方： スタンダードディブエー　　

読み方： スタンダードディビエーションエー

分類： 統計 

STDEVA([値1,[値2],…)

全データを標本データとみなした標準偏差を算出する　

STDEVP関数

読み方： スタンダードディブピー　　

読み方： スタンダードディビエーションピー

分類： 互換性 

STDEVP(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する　

STDEVPA関数

読み方： スタンダードディブピーエー　　

読み方： スタンダードディビエーションピーエー

分類： 統計 

STDEVPA([値1,[値2],…)

全データを母集団全体とみなした標準偏差を算出する　

Excel。AとB。得点も平均も同じだけど、どっちのほうに価値があるのかしりたい【Standardization】

Excel。AとB。得点も平均も同じだけど、どっちのほうに価値があるのかしりたい

＜STANDARDIZE・AVERAGE・STDEV.P関数＞

同じ数字には、同じ価値があるはずですが、全体のデータの中でどのような位置にその数値があるのかによって、価値が変わってきます。

例えば、次の表を見てみましょう。

B列には、Leadingの得点。C列には、Writingの得点の表です。

LeadingとWritingは、当然テスト内容も異なりますので、難易度も異なっています。

それを踏まえたうえで、数式を確認します。

B13:C13には、それぞれの平均値が算出されています。

B13の数式は、

=AVERAGE(B3:B12)

B14:C14には、標本標準偏差を算出しています。

B14の数式は、

=STDEV.P(B3:B12)

標準偏差とは、データのバラツキ度合いを数値として算出したものです。

B列のLeadingのほうが、Writingよりも数値が大きいので、Leadingはバラツキが大きいということがわかるというわけです。

なお、STDEV.P関数は、全データをサンプル（母集団）として算出したものです。

一部のサンプルから全体とみなす場合は、STDEV.S関数をつかいます。

さて、ここからが本題。

番号2番は、両方とも、85点ですが、同じ価値なのでしょうか。

要するに、平均が90点なのに、85点は、平均以下になってしまいますが、逆に平均が30点ならば、圧倒的な数値です。

このように、データによって、価値が変動します。

では、2番の人は、どちらのほうが、価値があるのでしょうか。

そこで、使う関数が、「STANDARDIZE関数」です。

STANDARDIZE関数は、標準化得点であるZ得点を算出する関数です。

D3の数式を確認してみましょう。

=STANDARDIZE(B3,B$13,B$14)

最初の引数は、データの数値なので、B3。

2番目の引数は、平均なので、B13。

オートフィルで数式をコピーするので、行固定の複合参照にしますので、B$13としています。

最後の引数は、標準偏差なので、B14。こちらも、行固定の複合参照にしますから、B$14と設定します。

あとは、オートフィルで数式をコピーしたのが、上記の表です。

2番のデータを見てみましょう。

Leadingが、0.681で、Writingが、1.664　と算出されました。

この数値が大きいほど、価値があることがわかるので、Writingの85点の方が、価値が高いことが判明しました。

確かに、2番のWritingの85点は、全体でトップの成績です。

5番目データは、LeadingのほうがWritingよりも点数自体は78点と高いのですが、Writingの標準化得点が高いので、点数こそ71点と低いですが、価値が高いと判断できるわけです。

このように、単純にその数値が高い、低いだけではなく、周りから見て、どうなのかという判断できるデータも、日頃使っている資料に追加してみると、違ったことが見えてくるかもしれませんね。

Excel。平均値は同じだけど、データのバラツキはどうなっているの。【Distributed】

Excel。平均値は同じだけど、データのバラツキはどうなっているの。

＜AVERAGE関数・VAR.P関数・STDEV.P関数＞

Excelで平均値を算出すること自体は、とても簡単に算出することができます。

しかしながら、平均値が同じ場合は当然データの中身を把握しないと、データの特徴などわかりません。

要するに、平均値に近いところにデータが集まっているのか、それとも、平均値よりも離れたところに、とても大きな数値、または、とても小さな数値が存在していることで、それらの数値に引っ張られた平均値なのかもしれません。

少ないデータならば、「目視」でなんとなくイメージすることもできますが、データの件数が増えた場合「感覚」で判断というわけにもいきません。

そこで、「分散」を算出することで、データのバラツキ具合を確認することができます。

今回は、LeadingとWritingのデータを用意しました。

LeadingとWritingの平均値は、それぞれ「65.0」と同じです。

B13の数式は、

=AVERAGE(B3:B12)

平均値だけみても、データの特徴はわかりません。

分散を算出するたけでも、データの特徴を少し把握することができます。

B14に標本分散を算出する数式を設定します。

=VAR.P(B3:B12)

となりのC14にオートフィルで数式をコピーしました。

このVAR.P関数は、標本分散を算出する関数です。

標本分散は、サンプルを全データとしたものです。

母集団すべてのデータを使って、バラツキを算出するのが、VAR.P関数です。

ただ、データの量がとても多い場合、サンプルを取得してそのサンプルを元にして、バラツキを算出するのが、不偏分散を算出する、VAR.S関数です。

今回はデータの都合上、大量なデータを作れなかったので、VAR.P関数をつかっております。

この数値が、大きくなればなるほど、平均値から離れた値がある。

すなわち、バラツキ具合が大きいことを意味しています。

Writingが860.2とLeadingの164.4と算出されています。

Writingのデータを見ると、確かに、Leadingよりも90点台から20点台とバラツキ具合が大きいことがわかります。

さて、この分散ですが、各数値と平均の差の二乗和をデータの個数で割ったものですが、関数をつかうことで、一発で算出することができます。

各数値の平均との差をそのまま足すと、プラスとマイナスで相殺されてしまうので、二乗してから足しているわけです。

このため、分散と元のデータとでは単位がことなっています。

そこで、一般に、分散の平方根を求めて、元のデータと単位を揃えたものが、「標準偏差」というわけです。

ざっくりですが、

√分散＝標準偏差　なので、標準偏差の二乗が分散ということになります。

この標準偏差にも、サンプルが母集団そのもの。

データ全部から算出する標本標準偏差を求めることができるSTDEV.P関数。

データの一部をサンプルとして算出する不偏標準偏差のSTEDEV.S関数。

この2つが、Excelには用意されています。

B15の数式は、標本標準偏差を算出するために、

=STDEV.P(B3:B12)

という数式を設定してあります。隣のセルにもオートフィルで数式をコピーしております。

分散も標準偏差も見慣れない関数ではありますが、数式を作ること自体は、とても簡単なので、分散という数値を日ごろ使っている資料に追加してみるだけでも、違ったことが見えてくるかもしれませんね。

Excel。偏差値って意外と簡単に算出することが出来るんです。【Deviation value】

Excel。偏差値って意外と簡単に算出することが出来るんです。

＜AVERAGE関数&STDEV.P関数＞

偏差値と聞くと、どうやって算出しているのか？

算出するには、とても難しいイメージがしますが、Excelをつかうと、比較的容易に算出することができるので、ご紹介していきます。

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

B列のWritingとC列のListeningの点数が入力されている表です。

WritingとListeningは同じ試験であるはずもなく、同じ点数を取ったとしても、難易度が異なっているので、その点数が、科目内での良し悪しは、単純にはわからないわけですね。

データの見た目から、Writingの点数がListening全体的に低い感じがしますね。

というか、見た目で判断するのはよくありません。

折角Excelというツールがあるわけですから、平均値から算出してきます。

F2の数式は、

=AVERAGE(B2:B11)

オートフィルで数式をコピーして、G2のListeningの平均も算出します。

最低限、平均値は算出したいところですね。

これで、Listeningのほうが20ポイントWritingより高いことがわかりました。

平均値は算出しましたが、WritingとListeningのそれぞれの中で、データの「ばらつき」も考える必要があります。

要するに、平均値は、平均値よりも離れた極端な値があると、極端に影響を受けてしまうからです。

この「ばらつき」を表すのが「標準偏差」です。

この標準偏差も、STDEV.P関数をつかえば、算出することができます。

F3の数式は、

=STDEV.P(B2:B11)

標準偏差は、値が大きいほど、「ばらつき」がありますので、今回のデータでは、Listeningよりも、Writingのほうが得点に差がある。

つまり「ばらつき」があることがわかりました。

平均値と標準偏差を算出することができれば、偏差値を算出するには、公式に当てはめてあげればいいわけです。

偏差値の公式は、

偏差値＝（データ－平均点）÷標準偏差×10+50

で求めることができます。

それぞれの偏差値を算出しますので、列を増やしておきます。

D2の数式は、

=(B2-H$2)/H$3*10+50

複合参照をつかっていますが、これは、オートフィルで数式をコピーするために複合参照をつかっておりますが、わかりにくい場合は、絶対参照にして、列ごとに算出するといいですね。

データ内で、どこに位置しているのかがわかるようになります。

例えば、Writingでは、67点で、Listeningが78点と、Listeningのほうが点数自体は高いのですが、そのグループの中では、Listeningのほうが低い位置にあることがわかるわけですね。

むしろ力を入れないといけないのは、Listeningのほうだったわけです。

数値は、見た目のイメージだけで、優劣をつけてしまいがちなので、注意したいところですね。

Excel。学生のためだけではない、偏差値はどのように算出するの？【Deviation value】

Excel。学生のためだけではない、偏差値はどのように算出するの？

＜AVERAGE&STDEV.P関数＞

受験生にはお馴染みの『偏差値』。

今回は、Excelで算出すればいいのか確認してみましょう。
次の得点表があります。

Aさんは、英語・国語ともに70点を取ったようです。同じ70点でも難易度が違うなど、条件が異なりますから、70という数値だけ見ても意味がありません。

最初に考えるのは、平均値との差がどうなのか？を考えます。

平均を算出するのは、Excelでは何の問題もありませんね。AVERAGE関数を使えば簡単に算出することができます。

すると、英語は59.8で国語は74.3と算出されました。
I2の数式は、=AVERAGE(C2:C16)

70点から考えてみると、英語は、平均よりも良い場所にあることがわかりました。
逆に国語は、平均以下ということがわかり、全体からすれば、下位にあることがわかりました。

成績の良しあしは、自分の点数が平均よりもどのぐらいの差があるのかが重要になります。

しかし、平均点との差だけを考えてはいけません。

見た目、英語は得点差のバラツキが少ないように感じますが、国語は100点の人が数人いますが、40点以下の人もチラホラいますよね。

まとまった塊なのか、二極化しているのかということも考慮する必要があります。
平均の差だけでなく、グループ全体のばらつきも考える必要があるわけです。

そこで、ばらつきの大きさを表すのが「標準偏差」です。

Excelで標準偏差を算出するには、STDEV.P関数を使えば簡単に算出することができます。

I3の数式は、
=STDEV.P(C2:C16)
平均と同じように、範囲選択するだけなので、簡単ですね。

平均との差とばらつきの大きさの数値をみて、どうなんだと考えるのは、少し面倒です。
そこで、登場する新たな指標が『偏差値』というわけです。

この偏差値は、次のような公式があって簡単に算出することができます。

偏差値＝（各データ ― 平均点） ÷ 標準偏差 × １０ ＋ ５０

わかりやすい数値にするために、×10＋50して、偏差値が20～80の間になるように調整しています。

それでは、偏差値の公式に当てはめて、偏差値を算出してみましょう。

E2の数式は、
=(C2-I$2)/I$3*10+50
です。

平均値と標準偏差は、オートフィルを使って数式をコピーすることを踏まえて、複合参照にしています。

このように、Excelを使えば、平均値、標準偏差、そして偏差値を簡単に算出することができます。

今回は、データの都合上たった15件ですが、もっと件数を増やせば精度が向上します。

会議の資料など、一つのデータの数値だけにとらわれやすい傾向にあるかもしれませんが、様々な角度からデータを見られるように、偏差値という一つの指標を学生さんだけでなく、ビジネスシーンでも使うことで、違った側面が見えるようになるかもしれませんね。

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。事務職のデータ分析その5。標準偏差を関数で算出してみよう。【STDEV.S】

Excel。事務職のデータ分析その5。標準偏差を関数で算出してみよう。

＜STDEV.P関数とSTDEV.S関数＞

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

ということで、
前回は、『標準偏差』の関数を使わないで算出してみましたので、
今回は、関数を使って、『標準偏差』を算出してみます。

まず、登場する関数は、
STDEV.P関数とSTDEV.S関数。

STDEV.P関数は、
スタンダード・ディビエーション・ポピュレーションと読みます。
データ全体の『標準偏差』を算出する関数です。

なお、Excel2007以前は、STDEVP関数なので、注意が必要です。
「.」（ドット）の有無の違いがあります。

もう一つの、STDEV.S関数は、
スタンダード・ディビエーション・サンプルと読みます。

データをサンプルと考え、そのデータから偏差の推定値を算出する関数です。
こちらの関数は、Excel2007以降に登場した関数です。

では、次の表があります。

H4をクリックして、STDEV.P関数ダイアログボックスを表示します。

数値1に、C3:C22を入力して、OKボタンをクリックします。

簡単に、『標準偏差』を算出することが出来ましたね。

なので、
前回、どういうプロセスで算出しているのかをご紹介したわけです。

でないと、どの数値を今後どのようにしていけばいいのかが、
わからなくなる危険性があるのです。

ではもう一つの、STDEV.S関数も算出してみましょう。

H5をクリックして、STDEV.S関数ダイアログボックスを表示しましょう。

数値1に、C3:C22を入力して、OKボタンをクリックします。

あとは、OKボタンをクリックしましょう。

算出されましたが、先ほどの、STDEV.P関数と算出結果が異なっていますよね。

これは、STDEV.P関数が、C3:C22の全データを使って算出しているのですが、
STDEV.S関数は、C3:C22のデータをサンプルとみて算出しています。

少し詳しく説明すると、
『分散』を求めるときに偏差の2乗の合計を算出して、
その値からサンプル数から-1（マイナス1）した値で除算して算出しています。

このため、算出された『標準偏差』の値は、
STDEV.P関数<STDEV.S関数となり、
STDEV.S関数の算出値のほうが大きくなります。

ただ、大きな差異がないので、
ビジネスの現場では、
どちらを使用してもかまわないといわれていますが…
数値がことなっているのですから、厳密には統計学上。違いはあるはずです。

さて、今回は、STDEV.P関数とSTDEV.S関数をご紹介しましたが、
『標準偏差』を短時間で算出できましたが、
分散を算出するために、平均から偏差を算出し、
偏差を2乗して、
その偏差の2乗の平均の平方根で『標準偏差』を求める方法も
知っていて損はないと思います。