BDAstyle

ビジネスデータ分析ツールの作成 with Excel

基本統計量の計算 with Excel 3/3

分散・標準偏差と変動係数の計算

Step 0シチュエーションの設定

下表は,マーケティング調査会社Xが,ある1日に顧客に郵送した2種類のレポート(A・B)の重量です。送付の前にそれぞれ数百あるレポートの中から無作為に15のアイテムを抽出し,それを記録したものです。

初期データ

このデータから,両者の分散・標準偏差と変動係数を求めます。

なお 以下文中※印注の用語については,一意的に使用されているものではありません(cf.「名称の混乱」標準偏差―"Wikipedia")。このサイトでは,これら用語の字句としての適否は無視し,分母を区別する“記号”としてのみ扱います。たとえば,ここにいう「不偏標準偏差」は字句の指すところとは性格を異にします。これに抵抗があれば,たとえば「不偏分散の正の平方根の 標準偏差」などに適宜読みかえてください。また筆者個人は,無用の混乱を防ぐためレポート・分析資料などのかたちで第3者の目に入れる場合,アルファベット・ギリシャ文字による表記の方法も含め,数式による定義を入れておいた方がbetterだとは思います。

1. 分散(Variance)

[ギリシャ文字・アルファベットであらわされるとき]

たとえば,

母分散:σ2

分散:s2

  • 分母の違いで区別するなら
  • n の分散:s2
  • n-1 の分散:u2

など。

Step 1分散の計算/Varp, Var, Var.p, Var.s関数

エクセルでは,分散は関数でも求めることができます。この場合,

によって適した関数が存在します。下表はその一覧です(なおVarp,Var関数は後方に互換性があります)。

標本分散 不偏分散
2007 VARP VAR
2010 or later VAR.P VAR.S

これらの関数を利用してレポートA・Bの分散をともに求めるとき,式は下図のようになります(両バージョンでの場合を併記しています)。

Var系関数に拠らず定義式に沿って計算する場合(デフォルトで非表示|クリックで展開)

  • SUBTOPIC 1

関数を利用しない場合,次の式を利用します。

s^2=Σ(xi-xbar)^2/n

このとき,式中の記号i,xは順に「no.」「重量」の各列が相当します。

すなわち,xiは「重量」列のi番目の要素のことを意味します。

またnはサンプルサイズを,x barは「重量」列の平均を意味します。

最初に,式の中のこの部分(赤い囲み)に注目したいと思います。これは1から15番までの各レポートの重さと平均との差(偏差)であることがわかります。

具体的にこの偏差を求めると,下の図のようになります。

さて,ここで再度式に視点を移すと…

分散の計算においては,これら偏差の合計をとるようです。…たしかに偏差をすべて足し合わせれば,全体の“ばらつき”が表現できそうな気もします。

…ということですが,実際にこれを求めると0になってしまいます。

要素と平均との差(偏差)にはプラス・マイナス両方向の符号が出現します。したがってこれを単純に合計しても,両方向性は打ち消し合うようにはたらきます(0になる)。

これでは,“ばらつき”を表現するといった役割にかなわないため,式のこの部分がいきてきます。

式のとおりに,各要素についてあらためて偏差の2乗をとってみます。これによって符号をプラスで揃えることができるので,他の要素を足していっても打ち消し合うことがありません。

したがって,個々の要素の偏差を合計した値の大小が,“ばらつき”の程度をあらわすものとして意味をなすこととなります。

ここまでの計算の手続きは 先に掲げた分散を求める式の分子の部分に関するものです。この分子の式によって求められる値は偏差平方和と呼ばれます。

偏差平方和=Σ(xi-xbar)^2

またこの偏差平方和について,Excelにはこれを求めるための関数が別途あります(DevSq関数。下図のように引数には元データを指定)。

=DEVSQ(B4:B18)

ここでふたたびもとの式に戻り,今度は分母に注目したいと思います。

欲するものが標本分散か不偏分散かによってこの分母は選択を要します。前者の場合はnを,後者の場合はn-1を用います。このように偏差の2乗の合計をnないしはn-1…ざっくりと言えばサンプルサイズで割るという点に鑑みて,分散は 偏差の2乗についての平均をとったもの と見ることができます。

ということで,下のようなシートの構成の場合,それぞれの値は具体的には次のような式で求めることができます。この場合,単位も2乗[この例ではg2(グラムの2乗)]されているので「分散は××g」という表現はできません。

Step 2分散・計算完了

レポートA・Bの分散は,次のとおりです。

2. 標準偏差(Standard Deviation, SD)

[ギリシャ文字・アルファベットであらわされるとき]

たとえば,

母標準偏差:σ

標準偏差:s

  • 分母の違いで区別するなら
  • n の標準偏差:s
  • n-1 の標準偏差:u

など

Step 3標準偏差の計算/Stdevp, Stdev, Stdev.p, Stdev.s関数

つづいて標準偏差を求めます。こちらも関数で求めることができます。この場合も同様に,

によって適した関数が存在します。下表はその一覧です(STDEVP,STDEV関数は後方に互換性があります)。

標本標準偏差 不偏標準偏差
2007 STDEVP STDEV
2010 or later STDEV.P STDEV.S

これらの関数を利用してレポートA・Bの標準偏差をともに求めるとき,式は下図のようになります(両バージョンでの場合を併記しています)。

StDev系関数に拠らず定義式に沿って計算する場合(デフォルトで非表示|クリックで展開)

  • SUBTOPIC 2

関数を利用しない場合,次の式を利用します。

s=√(Σ(xi-xbar)^2/n)

ルートの中身はSUBTOPIC 1で掲げた分散の定義式です。

s=√分散

したがって,SUBTOPIC 1の例示でしたように先に分散が求めてあれば,標本標準偏差・不偏標準偏差ともにそのルートをとるだけです。この場合,単位は元に戻るので「標準偏差は××g」とわかりやすい表現が可能です。

[セルB25]=SQRT(B22), [セルB26]=SQRT(B23)

Step 4標準偏差・計算完了

レポートA・Bの標準偏差は,次のとおりです。

3. 変動係数(Coefficient of Variation, CV)

Step 5変動係数の計算/標準偏差÷平均

たとえば不偏標準偏差に関して,レポートABとでは大きな差があります(上表。13.9g39.7g)。そのまま単純な比較をすれば,Bの方がバラツキが大きいことは明白です。ただし,平均の違いに明らかなように,レポートABは同性質の商品ではありません。分布が異なる以上,そのまま単純に比較をしても腑に落ちないところがあります。

こうした場合,元データが比尺度であれば,変動係数で(平均の大きさをモノサシ[=いくつ分か]にして)相対的に比較することができます。


変動係数(単位なし) = 標準偏差 / 算術平均


エクセルの上では,これは下表のように求められます(固有の組み込み関数はありません[2016.10月時点])。

Step 6異なった商品のバラツキの比較

不偏標準偏差をもとに計算した変動係数は,下のように

レポートA(平均の15.7%レポートB(平均の8.1%

となりました。よって両者の比較においては,Bの方が重量のバラツキが少ないことがわかります。

ただし計算式において自明なように,分母に算術平均をとる以上,これが0や負の値をとるような性質の対象に変動係数を利用することはできません。

これらの統計量が計算できるexcelアドインソフト

その他の参照