t-hom’s diary

主にVBAネタを扱っているブログです。

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

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

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

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

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

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

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

ただ、二次元配列はそのまま扱うとやや面倒くさい。
私は普段からレコードをクラスモジュールに入れ、シートに作成した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というクラスを作って動的配列を格納することにした。
f:id:t-hom:20190227034114p:plain

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

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


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

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

Private arr()
Private cursor As Long
Const COLUMN_SIZE = 2
Enum Col
    列1 = 12
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クラスは以下のとおり。

Public1 As Long
Public2 As String
Public Property Get Self() As Object
    Set Self = Me
End Property

最終的なデータ変換のプロセスは以下のようになった。
f:id:t-hom:20190227042753p:plain

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

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

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

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

当ブログは、amazon.co.jpを宣伝しリンクすることによってサイトが紹介料を獲得できる手段を提供することを目的に設定されたアフィリエイト宣伝プログラムである、 Amazonアソシエイト・プログラムの参加者です。