実践！エクセルVBAで配列を使いこなす

エクセルVBAで大量のデータを処理する場合、プログラムの組み方によっては大きく処理時間が異なってくる。処理時間が30分かかるプログラムが、3分に短縮できるということもある。短縮の方法としては様々なテクニックがあると思うが、一番効果がありそうな配列処理について紹介したいと思う。

以前にも配列を使うと便利という記事を書いたが、もう少し踏み込んで紹介したいと思う。サンプルプログラムも載せていくので参考にしていただければと思う。ただ、筆者は趣味でプログラムを組むぐらいなので細かい間違いがあるかもしれない。そこは読み解きながら理解していただけるとありがたい。（御連絡いただければ修正します）

まず、大量のデータ処理を行うにはメモリにデータを格納する必要がある。配列、コレクション、構造体などあるが、シンプルに配列を使うと処理速度も速く、プログラムもわかりやすくなるだろう。配列には1次元配列、2次元配列、3次元配列等、次元数を増やして使うことが出来る。ここではエクセルで使うことが多いであろう、2次元配列をメインで紹介していく。エクセルは表計算ソフトであるためセルを１つ１つのデータ格納領域とみることができ、非常に2次元配列に近いものがあるからだ。

基本的な宣言の仕方としては

Dim Data1(1 To 10) As Variant                        '1次元配列
Dim Data2(1 To 10, 1 To 10) As Variant               '2次元配列
Dim Data3(1 To 10, 1 To 10, 1 To 10) As Variant      '3次元配列
Dim Data4()as Variant                                '動的配列

と、書くことが出来る。「Dim 変数名(要素数) As 型」で書くのが基本だ。括弧の中は要素数で1次元配列で言うとData1(1)～Data(10)までの10個のデータを入れる箱ができる。同様にData2(1,1)～Data2(10,10)という2次元配列、Data3(1,1,1)～Data(10,10,10)という3次元配列が出来る。動的配列は要素数が未定のときに宣言するというものである。型は何でも入れれるようにバリアント型を今回は使用している。

本当は「Dim Data1(10) as Variant」という書き方でもよいが、この場合、配列はData1(0)～Data1(10)の11個の要素ができてしまう。このゼロから始まるとループするときなどややこしくなるので、1から始まるように指定している。また、エクセルのセルは左上が1,1（A1)から始まっているので1から始まるようにしたほうが、処理していくにはわかりやすい。

では実際に使っていこうと思うが、データ処理というと、大体は「入力→処理→出力」という流れである。これを実際に配列を使って一連のことをやってみようと思う。エクセルとの親和性を生かすためにシートのデータを読み取って、シートに結果を出力すると言うものを作成する。

始めに入力であるが、データをエクセルのセルから1つずつ読み取って配列に入れていては時間がかかる。これはエクセルのセルの操作が入るためで、できるだけセルやシートの操作をしないほうが高速なVBAプログラムを組むことができる。つまり最初（入力）と最後（出力）のときだけセルを操作すると言うのがベストなプログラムの組み方である。途中の計算はすべてメモリ上で行うのが高速化のコツである。

どうやって配列に入力するかというと、セルを範囲で選んで一気に2次元配列化するというのが一番速い。汎用的な操作なので２つの関数を作成する。

まずはシート全体を2次元配列に入れる関数である。モジュールなどに下記コードを入力する。

'-- シートを２次元配列化 --
Public Function Sheet2Array(BookName As String, Sheet As String) As Variant

    Dim MaxRow As Double                                            '最大行数
    Dim MaxCol As Double                                            '最大列数

    With Workbooks(BookName).Sheets(Sheet)
        MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row                '最大行数取得
        MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column      '最大列数取得
        Sheet2Array = .Range(.Cells(1, 1), .Cells(MaxRow, MaxCol))  '戻り値に配列
    End With

End Function

次に指定した範囲を2次元配列化する関数である。こちらは関数化する必要はないくらい短いので、毎回書いても問題ないレベルだろう。

'-- 指定範囲を２次元配列化 --
Public Function Range2Array(BookName As String, Sheet As String, _
P1R As Double, P1C As Double, _
P2R As Double, P2C As Double) As Variant

    With Workbooks(BookName).Sheets(Sheet)
        Range2Array = .Range(.Cells(P1R, P1C), .Cells(P2R, P2C)) '戻り値に配列
    End With
End Function

使い方としてはメインフローの中で、

Dim Data() As Variant
Data = Sheet2Array(ThisWorkbook.Name, "Sheet1")

と書くと、DataにSheet1のデータが2次元配列として格納される。Dataの大きさは不確定なので動的配列として宣言している。次にSheet2Arrayで先ほどの関数を呼び出して、引数にブック名、シート名を指定している。ThisWorkbook.nameはプログラムを走らせているブック名になる。他のブックを開いてそこから読み取る場合はこの引数を変更して対応可能である。

同様に範囲を指定して2次元配列化するには

Dim Data() As Variant
Data = Range2Array(ThisWorkbook.Name, "Sheet1", 4, 3, 25, 3)

と書く。この場合はSheet1のC列の4行目からC列の25行目までを2次元配列として読み込んでいる。1列しかないから1次元配列ではないかと思われるかもしれないが、Data(行数,1)の2次元配列になっている。

これでループ等使わず、まとめて一気に2次元配列化できるので、ひとつずつセルを読み込む場合に比べて非常に高速化できると思う。この2つの関数があればシートにあるデータを比較的簡単に配列に入れることが出来ると思う。入力については以上である。

[adsense]

配列を使っているときに不便だと思うのが、本当にデータの入っている要素数がどれだけあるかというのがわかりにくいところだ。コレクションであればAddされた数だけデータが入っているので要素数＝データ数になっている。配列の場合は先に固定要素数（大きめに格納領域）を指定してからデータを入れていくので、どこまでがデータが入っていてどこからが空なのかわからないというのがある。そこで次元数と有効な要素数を取得する関数を作成した。

' -- 配列情報(次元数、要素数）を取得 --
Public Function GetArrayInfo(Data As Variant, ByRef Items As Variant)

    Dim i As Double                                     'ループ変数
    Dim Temp As Variant                                 'テンポラリ変数

    On Error GoTo DtErr                                 'エラーチェック有効
    For i = 1 To 10                                     'とりあえず10次元までチェック
        Temp = UBound(Data, i)                          '指定次元のUBoundをチェック
    Next
DtErr:
    GetArrayInfo = i - 1                                'エラーが発生したとき-1が次元数
    On Error GoTo 0                                     'エラーチェック無効

    If GetArrayInfo = 1 Then                            '1次元配列のとき
        ReDim Items(1 To 1)                             '要素数　次元を1次元に
        For i = 1 To UBound(Data, 1)
            If Data(i) <> "" Then Items(1) = i          '空欄でなければ要素数を増やす
        Next
        If Items(1) = "" Then GetArrayInfo = -1         'データがなければ-1を返す
    End If

    If GetArrayInfo = 2 Then                            '2次元配列のとき
        ReDim Items(1 To 2)                             '要素数　次元を2次元に
        For i = 1 To UBound(Data, 1)                    '1次元の有効要素をチェック
            If Data(i, 1) <> "" Then Items(1) = i       '空欄でなければ要素数を増やす
        Next
        If Items(1) = "" Then GetArrayInfo = -1         'データがなければ-1を返す
            For i = 1 To UBound(Data, 2)                '2次元の有効要素をチェック
                If Data(1, i) <> "" Then Items(2) = i   '空欄でなければ要素数を増やす
            Next
            If Items(2) = "" Then GetArrayInfo = -1     'データがなければ-1を返す
        End If
End Function

使い方としてはメインフローで

Dim DFig As Double                                      '配列の次元数
Dim AItem() As Variant                                  '要素数

DFig = GetArrayInfo(Data, AItem)                        '次元数、有効要素の取得

と言う感じで使う。GetArrayInfo(調べたい配列、有効要素数を入れる配列)を実行すると次元数が戻り値になる。次元数が1次元であればAItem(1)に有効要素数が入っている。2次元であればAItem(1)に1次元目の有効要素数、AItem(2)に2次元目の有効要素数が入っている。今回は指定するData配列が1次元と2次元配列の場合のみ有効要素数と次元数を返すような関数にしている。

これで配列が何次元なのか、有効な要素はいくつあるかを簡単に調べることができる。ループする前に有効要素数を求めておけばループする回数を少なく出来るので高速化する場合もあるだろう。

最後にデータ処理した配列をシートに書き込む関数を作成する。本来であれば2次元配列しかシートに貼り付けできないが、今作成したGetArrayInfoの戻り値の次元数で1次元配列、2次元配列を判断し、シートにどちらでも書き込めるようにプログラムしている。

' -- 配列をシートに貼り付け --
Public Sub Array2Sheet(Data As Variant, BookName As String, Sheet As String)

    Dim DFig As Double                                     '配列の次元数
    Dim AItem() As Variant                                 '要素数

    DFig = GetArrayInfo(Data, AItem)                       '次元数、有効要素の取得

    If DFig = 1 Then                                       '1次元配列の場合
        Dim BufArray(1 To 100000, 1 To 1) As Variant       '2次元配列を用意(10万データまで）
        For i = 1 To AItem(1)
            BufArray(i, 1) = Data(i)                       '2次元配列に1次元配列を入れる
        Next
        Call Array2Sheet(BufArray, BookName, Sheet)        '2次元配列で再度呼出
    ElseIf DFig = 2 Then                                   '2次元配列の場合
        With Workbooks(BookName).Sheets(Sheet)             'シートの指定
            .Cells.Clear                                   '前の内容を削除
            .Range(.Cells(1, 1), .Cells(AItem(1), AItem(2))) = Data '2次元配列を貼り付け
        End With
    Else
        MsgBox "３次元配列以上またはデータが入っていないので貼り付けできません"
    End If
End Sub

使い方としてはメインフローで

Call Array2Sheet(Data, ThisWorkbook.Name, "Sheet2")

のように呼び出す。Data配列をThisWorkBook.NameのSheet2に書き出している。これも読み込みと同様に範囲を一括で書き出しているので、セルを操作する方法に比べると高速だ。1次元配列、2次元配列を気軽にシートに書き出すことが出来るので最終データの書き出し以外にも計算途中の配列にどのような値が入っているかの確認、デバッグにも使うことができるだろう。

では、ここまでの関数を使って簡単なメインプログラムを作ってみようと思う。Sheet1のA列,B列の数字を読み取って足した数字が1000を超えていれば出力するというプログラムである。条件式を変えれば必要な行のみの出力が可能である。基本的にこれがわかれば大体のデータ処理に対応できるだろう。

Sub main()

'入力
Dim Data() As Variant                                   'データを入れる配列を定義
Data = Sheet2Array(ThisWorkbook.Name, "Sheet1")         'Sheet1シートを配列化

'データ処理
Dim Result(1 To 10000, 1 To 3) As Variant               '結果を入れる配列を定義
Dim i As Double                                         'ループ変数
Dim j As Double                                         'カウント変数

j = 1                                                   '初期値
For i = 1 To UBound(Data, 1)
    Dim Cal As Variant                                  '計算用変数
    Cal = Data(i, 1) + Data(i, 2)                       '1列目と2列目を足す
    If Cal > 1000 Then                                  '1000以上の場合のみ出力
        Result(j, 1) = Data(i, 1)                       'そのまま元データを転写
        Result(j, 2) = Data(i, 2)                       'そのまま元データを転写
        Result(j, 3) = Cal                              '3列目に足した結果を格納
        j = j + 1                                       '出力配列カウントアップ
    End If
Next

'特にやらなくてよいが、配列の中にデータがいくつ入っているか調べてみる
Dim AItem() As Variant
    Call GetArrayInfo(Result, AItem)             'Resultの有効要素数を取得
    MsgBox AItem(1) & "件が1000を超えました。"   '1次元目の有効要素数を表示

    '出力
    Call Array2Sheet(Result, ThisWorkbook.Name, "Sheet2")'Sheet2シートにResult配列を出力
End Sub

このプログラムを実行すると、Sheet1のデータを読み取ってSheet2に結果を書き出すという動作になる。

Sheet1のデータ

↓↓　Sheet2に出力される　↓↓

A,B列は読み取った数字をそのまま出力、C列はAとBを足した値が入っている。（条件で1000を超えるものを出力としているで該当しない行は消えている）

配列を理解して使うことが出来れば、高速で読みやすいプログラムになると思われる。もちろん他の言語ではコレクションや構造体でもよいかもしれないが、VBAに限っては配列を使うことをおすすめしたい。

さらに、実践！エクセルVBAでクイックソートを使うを読んでいただけると大体のデータ処理が出来るようになるだろう。