<<目次へ

基本統計量の計算

　Excelには合計,平均,分散などといった基本統計量を計算するためのワークシート関数が用意されていますが,ここではそれらの関数は使用せずにVBAで各統計量を計算するプログラムコード書いてみましょう。

1.　合計を計算するためのVBAコード

Sub sum_prog()
    Dim R As Range, result As Range
    Dim dat As Variant
    Dim i As Integer, n As Integer
    Dim goukei As Double
    
    MsgBox "データは行方向に入力されており,かつ1列である必要があります"
    
    Set R = Application.InputBox(prompt:="データ範囲を選択", Title:="合計の計算", Type:=8)
    n = R.Rows.Count
    dat = R
    
　　'合計の計算
    goukei = 0
    For i = 1 To n
        goukei = goukei + dat(i, 1)
    Next
    
　　'結果の表示
    Set result = Application.InputBox(prompt:="結果を表示するセル", Title:="合計の計算", Type:=8)
    result = goukei
End Sub

2.　平均を計算するためのコード

　上の合計を求めるプログラムコードと同じものを書いてもしょうがないので,少し発展して,データ入力されているセル範囲が2列以上であり,かつ選択したセル範囲内に空白があっても計算できるようにしてみた。

Sub sum_prog()
    Dim R As Range, result As Range
    Dim dat As Variant
    Dim i As Integer, j As Integer
    Dim nrow As Integer, ncol As Integer, n As Integer
    Dim kuhaku As Integer, a As Range
    Dim goukei As Double, heikin As Double
    
    Set R = Application.InputBox(prompt:="データ範囲を選択", Title:="合計の計算", Type:=8)
    nrow = R.Rows.Count  'セル範囲の行数をカウント
    ncol = R.Columns.Count  'セル範囲の列数をカウント
    
    kuhaku = 0
    For Each a In R  '選択したセル範囲において,もし空白があればカウントしていく
        If a.Value = "" Then
            kuhaku = kuhaku + 1
        Else
        End If
    Next
    
    n = (nrow * ncol) - kuhaku  'データ数の取得
    dat = R  '選択したセル範囲のデータをdatに代入
    
　　'合計の計算
    goukei = 0
    For i = 1 To ncol
        For j = 1 To nrow
            goukei = goukei + dat(j, i)
        Next
    Next
    
    heikin = goukei / n　'平均の計算
    
　　'結果の表示
    Set result = Application.InputBox(prompt:="結果を表示するセル", Title:="合計の計算", Type:=8)
    result = heikin
End Sub

3.　分散を計算するためのVBAコード

　実際には上のコードに数行コードを追加するだけで分散の計算が可能である。しかし,選択したセル範囲に空白があると分散は計算できないので,さらに改良したものをあえて紹介する。

　今回は分散を計算するためのサブプロシージャとメインとなるプロシージャの2つが用意されている。下のものはサブプロシージャをメインプロシージャの下側に書いても問題ないが,C言語の名残で私はサブプロシージャは上に書くようにしている。

　ちなみにメインプロシージャは最後の1行を除いて,前述した平均を求めるためのプロシージャと全く同じである。

'分散を計算するためのサブプロシージャ
Sub var(dat As Variant, n As Integer, nrow As Integer, ncol As Integer, heikin As Double)
    Dim i As Integer, j As Integer, myCount As Integer
    Dim mySum As Double, bunsan As Double
    Dim result As Range
    Dim myData() As Double
    
    ReDim myData(n) '配列の大きさを指定
    
    'datに格納されているデータをmyDataに代入し直す
    myCount = 0
    For i = 1 To ncol
        For j = 1 To nrow
            myCount = myCount + 1
            If dat(j, i) <> "" Then  '空白でない場合
                myData(myCount) = dat(j, i)
            ElseIf dat(j, i) = "" Then  '空白である場合
                myCount = myCount - 1
            End If
        Next
    Next
    
    '分散の計算
    For i = 1 To n
            mySum = mySum + (myData(i) - heikin) ^ 2
    Next
    bunsan = mySum / n
    
    '結果の表示
    Set result = Application.InputBox(prompt:="結果を表示するセル", Title:="合計の計算", Type:=8)
    result = bunsan
End Sub

'メインとなるプロシージャ
Sub sum_prog()
    Dim R As Range
    Dim dat As Variant
    Dim i As Integer, j As Integer
    Dim nrow As Integer, ncol As Integer, n As Integer
    Dim kuhaku As Integer, a As Range
    Dim goukei As Double, heikin As Double
    
    Set R = Application.InputBox(prompt:="データ範囲を選択", Title:="合計の計算", Type:=8)
    nrow = R.Rows.Count  'セル範囲の行数をカウント
    ncol = R.Columns.Count  'セル範囲の列数をカウント
    
    kuhaku = 0
    For Each a In R  '選択したセル範囲において,もし空白があればカウントしていく
        If a.Value = "" Then
            kuhaku = kuhaku + 1
        Else
        End If
    Next
    
    n = (nrow * ncol) - kuhaku  'データ数の取得
    dat = R  '選択したセル範囲のデータをdatに代入
    
　　'合計の計算
    goukei = 0
    For i = 1 To ncol
        For j = 1 To nrow
            goukei = goukei + dat(j, i)
        Next
    Next
    
    heikin = goukei / n　'平均の計算
    Call var(dat, n, nrow, ncol, heikin)  '分散の計算をするためのプロシージャを呼び出す
End Sub

コードの解説

---

'datに格納されているデータをmyDataに代入し直す
    myCount = 0
    For i = 1 To ncol
        For j = 1 To nrow
            myCount = myCount + 1
            If dat(j, i) <> "" Then  '空白でない場合
                myData(myCount) = dat(j, i)
            ElseIf dat(j, i) = "" Then  '空白である場合
                myCount = myCount - 1
            End If
        Next
    Next

　これは2次配列（＝行列）に格納されているデータを1次配列（＝ベクトル）に格納し直している。理由は空白を無くすためである。空白があると分散の計算過程,すなわち

（i番目のデータ　-　データの平均）＾2

の部分で　（0　-　データの平均）＾2　と同じことになってしまい,正しい分散値が計算できないのである。

　この問題を解決するために「もし空白でなければ格納されているデータをmyDataに格納しなおす」,あるいは「もし空白であれば（データが格納されていなければ）何もせずに処理を続ける」というコードを書いている。

　まぁ,実際にはこのような面倒なコードを書く必要はない。なぜなら空白を作らないようにデータを入力すれば済むということだけの話だからだ。