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

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

＜Excel関数辞典：VOL.87＞

今回は、VALUE関数～VAR.S関数までをご紹介しております。

VALUE関数

読み方： バリュー　　

VALUE(文字列)

文字列を数値に変換する　

VALUETOTEXT関数

読み方： バリュートゥテキスト　　

VALUETOTEXT(値,[書式])

値のテキスト表現を返す　

VAR関数

読み方： バリアンス　　

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

数値群を標本データとみなした不偏分散を算出します　

VAR.P関数

読み方： バリアンス・ピー　　

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

数値群を母集団全体とみなした分散を算出します　

VAR.S関数

読み方： バリアンス・エス　　

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

数値群を標本データとみなした不偏分散を算出します　

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。事務職のデータ分析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

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