<<目次へ

データ行列に対する計算で役立ちそうな関数

　VBAを使用して簡単な計算プログラムを作る際に便利だと思われるものをいくつか紹介しておきます。通常は配列を宣言すると、配列の要素番号は0から始まりますが、以下のものはすべて配列の要素番号が1からであることを前提に書いています。

　ちなみに、私はワークシート上からデータを読み込むときにユーザフォームのRefEditを使うのが好きですが、Application.InputBoxを使用しても良いです。ただし、1行のみのデータ範囲、1列のみのデータ範囲を指定する場合は両者の読み込み方に違いがあるので注意してください。

dim myRange as string
dim dat as variant

dat = range(myRange)

としたときはdatには2次配列（行列）としてデータが格納されています。一方で、

dim myRange as range
dim dat as variant

Set myRange = Application.Inputbox()
dat = myRange

とした場合、datには1次配列（ベクトル）としてデータが格納されます。つまり、n行m列という概念（区別）がないのです。

　ホント、Excelってのは面倒ですね。。。

　それから、引数として渡された1次配列（ベクトル）や2次配列（行列）は勝手に要素の最小値が0にされているのかもしれないので注意!!　これが起こると、コードを走らせたときに"インデックスの範囲が有効ではありません"とエラーが生じる。

■　Variant型の変数に代入されているデータ行列の行合計と列合計を求める

Function dimsum(dat As Variant, x As Integer) As Variant
    Dim i As Integer, j As Integer
    Dim nrow As Integer, ncol As Integer
    Dim rowsum() As Variant, colsum() As Variant
    Dim mySum As Double
    
    nrow = UBound(dat, 1) ' データ行列の行数を取得
    ncol = UBound(dat, 2) ' データ行列の列数を取得
    
    ReDim rowsum(nrow) ' 各行の合計を格納する配列
    ReDim colsum(ncol) ' 各列の合計を格納する配列
    
    If x = 1 Then
        For i = 1 To nrow   'x = 1なら行合計
            mySum = 0
            For j = 1 To ncol
                mySum = mySum + dat(i, j)
            Next
            rowsum(i) = mySum
        Next
        dimsum = rowsum ' 行合計が格納された配列を返す
    ElseIf x = 2 Then   'x = 2なら列合計
        For i = 1 To ncol
            mySum = 0
            For j = 1 To nrow
                mySum = mySum + dat(j, i)
            Next
            colsum(i) = mySum
        Next
        dimsum = colsum ' 列合計が格納された配列を返す
    Else
        MsgBox "引数の指定が間違っています[x = 行:1 or 列:2]"
    End If
End Function

〜使用方法〜

　例えば、RefEdit1つとコマンドボタン1つをつけた簡単なユーザーフォームを作成し、コマンドボタンに次のようにプログラムを書けばよい。結果の表示にはこのページの下にあるvprint()を利用すると簡単。

Private Sub CommandButton1_Click()
    Dim myRange As String
    Dim dat As Variant
    Dim tameshi As Variant
    
    myRange = RefEdit1.Text
    dat = Range(myRange)
    
    tameshi = dimsum(dat, 1) 'この場合は行合計を求める
    vprint (tameshi) ' vprint()を利用してワークシート上に表示
    
    Unload UserForm1
End Sub

■　Variant型の変数に代入されているデータ行列の行平均と列平均を求める

Function dimmean(dat As Variant, x As Integer) As Variant
    Dim i As Integer, j As Integer
    Dim nrow As Integer, ncol As Integer
    Dim rowmean() As Variant, colmean() As Variant
    Dim mySum As Double, myMean As Double
    Dim count As Integer
    Dim FT As String
    
    nrow = UBound(dat, 1) ' データ行列の行数を取得
    ncol = UBound(dat, 2) ' データ行列の列数を取得
    
    ReDim rowmean(nrow)
    ReDim colmean(ncol)
    
    If x = 1 Then
        For i = 1 To nrow   'x = 1なら行平均
            mySum = 0
            count = 0
            For j = 1 To ncol
                mySum = mySum + dat(i, j)
                FT = IsEmpty(dat(i, j))
                If FT = "True" Then
                    count = count + 1 '欠損値のカウント
                End If
            Next
            myMean = mySum / (ncol - count)
            rowmean(i) = myMean '列jの平均値を配列へ格納
        Next
        dimmean = rowmean
    ElseIf x = 2 Then   'x = 2なら列平均
        For i = 1 To ncol
            mySum = 0
            count = 0
            For j = 1 To nrow
                mySum = mySum + dat(j, i)
                FT = IsEmpty(dat(j, i))
                If FT = "True" Then
                    count = count + 1 '欠損値のカウント
                End If
            Next
            myMean = mySum / (nrow - count)
            colmean(i) = myMean ' 行jの平均を配列へ格納
        Next
        dimmean = colmean
    Else
        MsgBox "引数の指定が間違っています[x = 行:1 or 列:2]"
    End If
End Function

〜使用方法〜

　行合計を求める関数dimsum()と同じ。

■　1次配列（ベクトル）を2次配列（行列）へ変換する

Function mconv(vector As Variant, nrow As Integer, ncol As Integer, byRow As String) As Variant
    Dim i As Integer, j As Integer, k As Integer
    Dim matrix() As Variant
    
    ReDim matrix(1 To nrow, 1 To ncol) 'nrow行、ncol列の2次配列を作成

    If byRow = "T" Then ' 引数のbyRowがTなら行単位で変換
        k = 0
        For i = 1 To nrow
            For j = 1 To ncol
                matrix(i, j) = vector(k)
                k = k + 1
            Next
        Next
    ElseIf byRow = "F" Then ' 引数のbyRowがFなら列単位で変換
        k = 0
        For i = 1 To ncol
            For j = 1 To nrow
                matrix(j, i) = vector(k)
                k = k + 1
            Next
        Next
    Else
        MsgBox "引数の指定が誤っています[byRow = T or F]"
    End If
    
    mconv = matrix ' 行列に変換されたものを返す
End Function

〜使用方法〜

　結果の表示にはこのページの下にあるdprint()を使用するとラク。

Sub sample()
    Dim vec() As Variant
    Dim mat As Variant
    
    ReDim vec(1 To 6)
    vec = Array(1, 2, 3, 4, 5, 6)
    mat = mconv(vec, 2, 3, "T")
    dprint(mat)
End Sub

■　2次配列（行列）から1次配列へと変換

Function vconv(matrix As Variant) As Variant
    Dim nrow As Integer, ncol As Integer
    Dim myVector() As Variant
    Dim i As Integer, j As Integer, k As Integer
    
    nrow = UBound(matrix, 1)
    ncol = UBound(matrix, 2)
    
    ReDim myVector(nrow * ncol)
    
    k = 0
    For i = 1 To ncol
        For j = 1 To nrow
            k = k + 1
            myVector(k) = matrix(j, i)
            Debug.Print myVector(k)
        Next
    Next
    
    vconv = myVector

End Function

〜使用方法〜

　最初にいったようなユーザーフォームを作成して次のようにコードを書く。

Private Sub CommandButton1_Click()
    Dim myRange As String
    Dim dat As Variant
    Dim tameshi As Variant
    
    myRange = RefEdit1.Text
    dat = Range(myRange)
    
    tameshi = vconv(dat)
    vprint(tameshi)
    
    Unload UserForm1
End Sub

■　1次配列（ベクトル）の中身をワークシート上に表示する

Function vprint(vector As Variant)
    Dim length As Integer, i As Integer
    Dim res As Range
    
    length = UBound(vector, 1)
    
    Set res = Application.InputBox(prompt:="データを表示させるセルを選択してください", Title:="データ表示", Type:=8)

    For i = 1 To length
        res.Cells(1, i) = vector(i)
    Next
End Function

〜使用方法〜

　単に引数に1次配列を指定するだけ

■　2次配列（行列）の中身をワークシート上に表示する

Function dprint(dmatrix As Variant)
    Dim nrow As Integer, ncol As Integer
    Dim i As Integer, j As Integer
    Dim res As Range
    
    nrow = UBound(dmatrix, 1)
    ncol = UBound(dmatrix, 2)
    
    Set res = Application.InputBox(prompt:="データを表示させるセルを選択してください", Title:="データ表示", Type:=8)
    For i = 1 To nrow
        For j = 1 To ncol
            res.Cells(i, j) = dmatrix(i, j)
        Next
    Next
    
End Function

〜使用方法〜

　単に引数に2次配列を指定するだけ

追記

　以上のものは完全なものではない、、、というか、データの取得方法によって左右されるので、参考程度にして実際に使用する際には細かいトコは自分で勝手に変更してください（私自身も細かい所まで試していない）。というか、こんなことをするくらいならRを使ってください。