Excel VBAで相関比を求める関数を作ってみた
前回紹介した『マンガでわかる統計学』で解説されている2変数間の関連の度合いを測る統計量のうち、相関比はまだユーザー定義関数を作成していませんでした。理由は、私がそれを必要としていなかったからですが、せっかくなので作ってみました。
相関比がどういうものかは、以下のサイトの解説がわかりやすいと思いますので、詳しく知りたい方はぜひこちらへ。
私のほうは架空の「クラス別点数データ」をエクセルExcelで計算しながら、ごく簡単に説明させていただきます。
[A1:B16]は、「クラス」という質的変数と「点数」という量的変数を持つデータです。順を追って計算していきます。
- B19セルに全体の平均を格納します。=AVERAGE(B2:B16)
- C列にクラスごとの平均を格納します。C2セルに=AVERAGEIF(A$2:A$16,A2,B$2:B$16) を入力してオートフィルするよいでしょう。
- D列に(点数-級内平均)の2乗を格納します。分散を計算するときにおなじみのやつですね。D2セルに=(B2-C2)^2 を入力して(以下略)。
- B20セルにD列の合計を格納します。クラス内の分散の和って感じですね。
- E列に(級内平均-全体平均)の2乗を格納します。E2セルに=(C2-B$19)^2 (略)。
- B21セルにE列の合計を格納します。*1
クラス間の分散って感じですね。って、う~ん自分でもわかるダメ説明。ここは(株)アイスタットさんのサイトを見ていただきたいです。 - で、最後にC23セルにを格納して完成です。
以上の計算をVBAでコーディングすると以下のようになります。*2
'相関比ηを求める Function Corratio(質的変数, 量的変数) Dim Sw '級内変動 Dim Sb '級間変動 Dim m 'グループ別平均 Dim Ms '全体平均 Dim i If 質的変数.Rows.Count = 量的変数.Rows.Count _ And 質的変数.Columns.Count = 1 _ And 量的変数.Columns.Count = 1 Then With WorksheetFunction Ms = .Average(量的変数) For i = 1 To 質的変数.Rows.Count m = .AverageIf(質的変数, 質的変数(i), 量的変数) Sw = Sw + (量的変数(i) - m) ^ 2 Sb = Sb + (m - Ms) ^ 2 Next End With Corratio = Sqr(Sb / (Sw + Sb)) Else Corratio = CVErr(xlErrNA) End If End Function
ごくシンプルなコードなので、解説は要しないと思います。
引数は2つ。どちらもセル範囲を指定してください。ただし、名称どおり1つめは質的変数を選択する必要があります。
また、選択範囲中に欠損値があるとエラーになります。