Excelで利用するDEVSQ関数(ディビエーション・スクエア)のご紹介です。

DEVSQ関数

DEVSQ関数とは、偏差平方和を計算する関数です。
偏差平方和とは、あるデータの値から平均値を引いた値が、偏差になり、
偏差平方和とは、各データの偏差を2乗し、足し合わせたものです。
「偏差」→ データと平均の差
「平方」→ 二度掛け合わせること
「和」→ 足すこと、を示しています。
書式は、=DEVSQ(データ範囲)になります。
偏差平方和を計算する関数が、なぜ「DEVSQ」なのかというと、
偏差の意味するDEVIATION の頭文字、
2乗や平方を意味するSQUAREの頭文字をとって合わせて「DEVSQ」関数なんですね。
データ範囲は、もとの数値を指定します。引数は255個まで指定できます。
変動はデータの散らばり具合を表す値で、分散や標準偏差などを求めるのに使われる基本的な値です。

例1:10人の身長のデータがあります。こちらの身長データの偏差平方和を計算してみましょう。
下記の表を参照してください。

「C2」のセルに、=DEVSQ(A2:A11)と挿入します。
計算してみると偏差平方和は、336.1になりました。
注意※引数として指定した配列またはセル範囲に文字列、論理値、空白セルが含まれている場合、これらは無視されます。
ただし、数値として「0」を含むセルは計算の対象となります。

今度は横で偏差平方和を出してみましょう。
下の表を参照してください。「B2:D2」までの平均は10になります。各セルから平均を引いて2乗して計算してみてください。
結果は「50」になります。下にオートフィルすると「B3:D3」は72になりますね!手計算してみてください!

たとえで解説いたします。
ある5種類の電池があり、その容量/Ahが各々 16,19,18,25,22であったとします。このときの偏差平方和を求めてみましょう。
まず、標本平均を求めていきましょう。
標本平均=(16+19+18+25+22)/5 = 20となります。普通に平均を出します。
よって、偏差平方和 =(16-20)^2+(19-20)^2+(18-20)^2+(25-20)^2+(22-20)^2=16+1+4+25+4=50 となります。
定義は簡単ですが、計算がやや複雑になるのでこれをDEVSQ関数が一瞬で計算してくれているんですね。

DEVSQ関数のまとめ

まず『偏差平方和』ですが、『偏差』とは“平均値からの偏り”であり、それぞれのデータと平均値との差のことであり、『平方和』はその偏差の平方、つまり平均値との差の2乗の合計ということです。
したがって、20個の数値から平均値の42を引いたものの2乗の合計が『偏差平方和』となります。具体的には以下のようになります。
(38-42) 2乗+(33-42)2乗+(44-42)2乗+(50-42)2乗+(48-42)2乗+(51-42)2乗+(32-42)2乗+(42-42)2乗+(37-42)2乗+(34-42)2乗+(44-42)2乗+(35-42)2乗+(36-42)2乗+(38-42)2乗+(50-42)2乗+(43-42)2乗+(49-42)2乗+(33-42)2乗+(44-42)2乗+(49-42)2乗=784
以上のように、面倒くさいのですが難しくはありません。
次に、『標本分散』は『偏差平方和』の平均ですから、784/20=39.2 となります。
また、『不偏分散』ですが、標本分散は実際の集団(母集団)の分散、これを “母分散”といいますが、これよりも若干小さくなることが知られており、それを補正するために標本数、この場合は20ですが、これから1引いた数、つまり19で『偏差平方和』を割ったものとなります。
したがって、784/19=41.3 となります。
なお、以上のことはExcelを使えば、自動的にやってくれます。Excelに20個のデータを入力し、『偏差平方和』は「DEVSQ関数」20個のデータを選択すれば計算してくれます。
使い方をマスターすればExcelがこれまでよりももっと便利になるので、是非身に付けてくださいね!
これからも引き続き関数動画・ブログをアップして参りますので、気になる関数があったらまずはソフトキャンパスの動画を確認してみてください!