偏差値を計算するEXCELマクロ

 Excelのワークシートの任意の場所に右図のようにデータ(一番
上にデータ個数、続けてデータ)を入力し、データ個数を記したセ
ルをアクティブにして実行したとき、右図の@のセルにデータの平
均値、Aのセルに標準偏差、Bのセルに“偏差値”という文字、C
〜Lのセルには左隣のデータの偏差値を出力するマクロを作成しな
さい。データ個数がどのようなものであっても同様に出力できるよ
うにしなさい。

【ヒント】
 データ個数が常に10であればVBによるプログラムを作成する必要
性はないが、どのようなデータ個数でも計算可能とするためには、
プログラミングが必要となる。ExcelマクロをVBで直接作成するこ
とも可能であるが、ここでは、「自動記録によるマクロの作成」に
より作成されたマクロを「編集」することでマクロを完成させる。
 「新しいマクロの記録」を開始し、「相対参照」とし、ワークシ
ート上で現在アクティブなセルのすぐ下のセルをクリックし、マク
ロの記録を終了する。このようにして作成したマクロは、次図のよ
うになる。

  Sub Macro1()
  '
  ' Macro1 Macro
  ' マクロ記録日 : 2000/5/30 ユーザー名 : 経済学部
  '
  ' Keyboard Shortcut: Ctrl+e
  '
    ActiveCell.Offset(1, 0).Range("A1").Select   
  End Sub
 データ個数→






データ
   10   B
   8  C
   3  D
   5  E
   4  F
   6  G
   7  H
   1  I
   9  J
   5  K
   3  L
  @ 
  A 

(1) データ個数が記されているセルがアクティブであるので、変数Nに読み込むことにする。

        N = ActiveCell

(2) VBでデータを保存する配列を用意する。

        ReDim X(N)

(3) すべてのデータを読み込むためには、「アクティブセルを1つ下げて、読み込む。」操作をN回繰
 り返せばよいことになる。

        FOR K = 1 TO N
           ActiveCell.Offset(1, 0).Range("A1").Select
           X(K) = ActiveCell
        NEXT K

(4) 総和を保存する変数をSとし、Sに X(1)を足し込み、Sに X(2)を足し込み、...、Sに X(10)
 を足し込めばよい。それらの命令は、K=1、S=S+X(K)、K=2、S=S+X(K)、...、K=10、S=S+X(K) と
 なるので、繰り返し命令が使えることになる。さらに、平均値を保存する変数をHとすれば、プログ
 ラムは次のようになる。

        S = 0
        FOR K = 1 TO N
          S = S + X(K)
        NEXT K
        H = S / N

(5) 変数Sに求められた答を出力する場所を考える。(3)において最後のデータの読み込んだところがア
 クティブになっているので、@はすぐ下のセルである。したがって、命令は次のようになる。

        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell = H

(6) 標準偏差は分散の平方根であり、分散は偏差(データ−平均値)の2乗の平均値である。

        S = 0
        FOR K = 1 TO N
           S = S + (X(K) - H) ^ 2
        NEXT K
        P = Sqr(S / N)
        ActiveCell.Offset(1, 0).Range("A1").Select
        ActiveCell = P

 上記プログラムの5行目は、変数Sに偏差2乗の総和が求められているので、Nで割って平均値すなわ
 ち分散を求め、その平方根すなわち標準偏差を計算して変数Pに保存している。6・7行目は、標準
 偏差出力命令で(5)と同様なものである。

(7) Bに“偏差値”という文字を出力するためには、Aからその場所にアクティブセルを移動しなけれ
 ばならない。上にN+2、右に1いけばよいので次のようになる。

        ActiveCell.Offset(- N - 2, 1).Range("A1").Select
        ActiveCell = "偏差値"

 文字を出力するときは出力する文字の前後を「"」で囲まなければならない。

(8) 偏差値は、偏差(データ−平均値)を標準偏差で割り、それを10倍して50を加えることで求められ
 る。現在Bのセルがアクティブであるので、1セル下げて偏差値を計算し出力する作業をN回繰り返
 せばよいことになる。再びFor〜Next命令を用いて次のようになる。

        For K = 1 To N
           ActiveCell.Offset(1, 0).Range("A1").Select
           ActiveCell = 10 * (X(K) - H) / P + 50
        Next K

 3行目のように、計算結果を保存する必要のないときは、出力すべきものとして数式を記すこともで
きる。このとき、出力されるのは計算結果である。

 以上で説明してきた26行の命令をその順番に、マクロの編集画面でSub Macro1()行とEnd Sub行の
間に入力すれば課題が完成する。

 行頭に「'」を付けた行があるが、これらは注釈(コメント)行であり、プログラムを分かり易くす
るために記すもので、プログラムとしては意味のないものである。VBでは、「'」が出てくるとその後
ろを無視するようになっている。したがって、プログラムとして意味のある行の後ろに「'」を付けて
説明を記入しておく方法もよく利用される。