静粛に、只今統計勉強中

仕事でデータ分析をすることになったバリバリ文系アラフィフのおっさんが、独学で統計の勉強を始めました。

Excel VBAでraw dataから基準化変量と偏差値を求める関数を作ってみた

統計学の入門書には、必ずと行っていいほどデータの標準化と偏差値についての説明が載っています。そして、具体例としてよく挙げられるのが
「期末テストの結果、A君のテストの得点は国語70点、算数60点でした。また平均点は国語65点、算数55点、標準偏差は国語15点、算数10点でした。A君は国語と算数のどちらが学年で上の成績でしょう?」
みたいな例題です。

このような例題の場合、エクセルExcelにはピッタリの関数STANDARDIZEがあります。
f:id:cyclo-commuter:20180305130751p:plain
こんなふうに得点,平均点,標準偏差の順に引数を入力してあげれば、
f:id:cyclo-commuter:20180305130844p:plain
平均が0,標準偏差が1に変換された値(z値)が返ってきます。
ちょっぴりですが、A君は国語より算数のほうが学年の中では上位に位置するようですね。

しかし、実際のデータ分析ではあらかじめ平均や標準偏差が与えられるなんてことはないので、下図のように科目ごとの平均と標準偏差も計算しなければなりません。*1
f:id:cyclo-commuter:20180305133330p:plain

これがどうにもメンドクサイので、raw data から基準化変量*2とついでに偏差値を求める関数も作ってみました。

'基準化変量/偏差値を求める
Function StandardScore(x, 参照, Optional 種類 As Boolean)
    With Application.WorksheetFunction
        StandardScore = .Standardize(x, .Average(参照), .StDev_P(参照))
        If 種類 = True Then StandardScore = StandardScore * 10 + 50
    End With
End Function

と言っても、ワークシート関数のAVERAGESTDEV.Pを使ってワークシート上に入力する手間を省いただけのお手軽版ですが。

f:id:cyclo-commuter:20180305134133p:plain
こんなふうに引数に値とデータ範囲を指定すれば基準化変量が、さらに3つ目の引数にTRUEを指定すれば偏差値が求まります。

 

ところで、 ここでいう標準化って、正規化と呼ばれたりもするんですけど、あくまでも平均が0、標準偏差(と分散も)が1の分布に収まるように変換されるだけで、必ず標準正規分布に従うようになるわけではないんですよね。
標準化して標準正規分布に従うようになったとすれば、それは元のデータが正規分布に従っていたからで、多少タテヨコに伸ばしたり縮めたりしても、元の分布の形はしっかりと残っている。

なぜ、こんな当たり前のことを殊更に書くかというと、平均値と同じような勘違いの罠がここにも潜んでいると思うからです。*3

正規分布に従っているからこそ、 偏差値70(平均50+2σ)がスゲエ!って言えるんですよね。上位2.5%以内じゃん、と。
normal distribution

仮にデータが
f:id:cyclo-commuter:20180305153914p:plain 一様分布だったり、
f:id:cyclo-commuter:20180305153954p:plain 左に裾を引くような分布だった場合は、偏差値70のスゴさはそれなりにダウンしてしまうわけです。

中学,高校,大学入試で使われる偏差値について、

偏差値を見る際に注意したいのは、偏差値は母集団が異なれば比較できないということ。例えば、同じ高校内や、受験する層や人数がある程度一定の模試であれば、偏差値を指標に各回の模試で学力を比較することができるが、中学校の偏差値と高校の偏差値や、模試受験者の人数やレベルが異なる模試同士の比較は意味がない。

いまさら聞けない「偏差値」とは?|受験サポートニュース|進研ゼミ高校講座

ていう指摘はごもっともなんですけど、それ以前に得点の分布が正規分布に従っているかどうかもわからないのですから、あまり鵜呑みにしないほうがいいんじゃないでしょうか。

*1:分析ツールで乱数を発生させたら、上の例と値が少し違っちゃいました。

*2:この他に「標準得点」とか「標準化得点」とか「z-score」とか「スゴイ度」とかいろいろ呼ばれます。ややこしいですね。

*3:平均値の罠については以下の記事に書きました。