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点程度のバラツキがあることがわかりました。
