※注意 今回の記事はアイデアを記したものであり、コードの全体は掲載していません。ヒントを求めている方向けです。答えを求めてる方はごめんなさい。
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。
データ量がどれだけスケールするかによってデータ構造は使い分ける必要がある。
ただし、基本的にデータがスケール際に発生する複雑さをクラスで上手くラップしてやれば、メインロジックはだいたいいつも通りになる。
