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