VBA Excelがフリーズするほど大量のデータを特定列の値で分類して別シートに分ける処理

※注意　今回の記事はアイデアを記したものであり、コードの全体は掲載していません。ヒントを求めている方向けです。答えを求めてる方はごめんなさい。

Excelシートの特定列の値でレコードを分類し、個別のシートに転記する処理を作りたい場合がある。
いつもなら、レコードを1件ずつ読み取りながら転記していく。

ただ、IT運用業務ではサーバーのアクセスログなどの大量データを扱うことがあり、この方法ではどうやってもフリーズしてしまう事態に遭遇した。今回は16万件のレコード。このような大量データを扱う場合、セルに一つずつアクセスする普通のコーディングではExcelが長時間フリーズしてしまう。(大抵、会社のPCというのは普通の事務処理ができれば十分というスペックなので、家のPCよりも酷いことになる。)

ここまでデータがスケールしてしまうと、レコードを1件ずつ読み取る方式では厳しいのだ。

そこで、高速化テクの1つである、動的配列への転記を使うことにした。
thom.hateblo.jp

以下のようにシートから動的配列に転記し、それを分類ごとの動的配列に分け、各シートに転記する方法である。分類ごとの動的配列は分類名(特定列の値)をキーにして辞書型データに持たせることにする。

ただ、二次元配列はそのまま扱うとやや面倒くさい。
私は普段からレコードをクラスモジュールに入れ、シートに作成したWriteLineメソッドで転記している。

たとえば、Sheet1モジュールに次のようなコードを挿入しておく。

Private Cursor As Long
Sub Init()
    Cursor = 2
End Sub
Sub WriteLine(ParamArray arr())
    For i = LBound(arr) To UBound(arr)
        Cells(Cursor, i + 1).Value = arr(i)
    Next
    Cursor = Cursor + 1
End Sub

すると、標準モジュールからは単にInitしてからWriteLineを実行するだけでデータを順次書き込むことができる。

Sub hoge()
    Sheet1.Init
    For i = Asc("A") To Asc("Z")
        Sheet1.WriteLine i, Chr(i)
    Next
End Sub

書き込み位置を指定するCursorはWriteLineメソッドの内部でインクリメントされるため、書き込みを指示するメインモジュールでは特に書き込み位置を意識しなくて良い。これは楽。

動的配列でもこれと同じ仕組みを使いたい。
そこで、WriteLineを実装したVirtualSheetというクラスを作って動的配列を格納することにした。

今回、配列の動的拡張も考えていたのだが、二次元配列なので以下の制約があって諦めた。
thom.hateblo.jp

どのみちデータ量が多いので動的配列の拡張を繰り返すのは望ましくない。
そこで、VirtualSheetにInitメソッドを実装し、引数としてあらかじめレコード数を与えて配列サイズを確定させることにした。
分類ごとのレコード数はあらかじめ分類列だけを配列転記し、Dictionaryを使ってカウントしておく。
thom.hateblo.jp

更に、VirtualSheetにWriteToSheetメソッドを実行し、そこに引数で指定したワークシートにデータを書き込む処理を実装した。

VirtualSheetのコードは以下のとおり。

Private arr()
Private cursor As Long
Const COLUMN_SIZE = 2
Enum Col
    列1 = 1
    列2
End Enum

Sub Init(row_size As Long)
    ReDim arr(1 To row_size, 1 To COLUMN_SIZE)
    cursor = LBound(arr, 1)
End Sub

Sub WriteLine(rc As Record)
    arr(cursor, Col.列1) = rc.列1
    arr(cursor, Col.列2) = rc.列2
    cursor = cursor + 1
End Sub

Sub WriteToSheet(ws As Worksheet)
    ws.Range(ws.Cells(1, 1), ws.Cells(UBound(arr, 1), UBound(arr, 2))).Value = arr
End Sub

今回は汎用性は犠牲にして、カラムサイズを定数で直接VirtualSheetに持たせた。WriteLineも汎用ではなく、Recordクラス型のオブジェクトを受け取って配列に格納するようにした。

Recordクラスは以下のとおり。

Public 列1 As Long
Public 列2 As String
Public Property Get Self() As Object
    Set Self = Me
End Property

最終的なデータ変換のプロセスは以下のようになった。

これで20万件くらいのデータならなんとか待てるレベル。

今回私が作ったものは全体データは動的配列のまま扱ったが、実際にはSheetモジュールに動的配列を持たせ、GetNextで動的配列から1つずつ、Recordオブジェクトとして取り出す処理をしてたので、実質クラスを使ったのと同じようなことをしている。

いつもはシートからGetNextで取り出しつつ、別のシートにWriteLine。
今回はシートからGetNextで取り出しつつ、VirtualSheetにWriteLine。最後にWriteToSheet。

データ量がどれだけスケールするかによってデータ構造は使い分ける必要がある。
ただし、基本的にデータがスケール際に発生する複雑さをクラスで上手くラップしてやれば、メインロジックはだいたいいつも通りになる。