エクセルで大量のデータを扱う場合、シートの読み込みや書き込みで処理が非常に遅くなる場合がある。1000個程度のデータで問題なく動いているのに実際に使う数十万個のデータだと処理時間が長すぎて実用できないこともある。ここでは処理速度と使いやすさを意識したVBAプログラムのテクニックを紹介したいと思う。
エクセルのマクロやVBAと言うともう十数年と使われている言語であるため、様々な参考書が出ていたり、ネットで紹介されていると思う。ただ、あまり速度まで気にしてサンプルプログラムが書かれていることはあまりない。プログラムの組み方に関してはプログラマの好みでいいと思うが、VBAに関しては組み方によって処理速度が大きく変わるので、作るのであればなるべく最速で動くようにしたい。(エクセルと連携したVBAが特殊ではあるが、仕事などではよく使われるだろう)
現在のパソコンであれば処理速度を意識しなくてもいいんでは?と思うが、エクセルVBAに関してはあくまでもエクセルに付随するものであり、チューニングがされていなかったり、マルチスレッドで動作できなかったりと制限が大きい。データが多ければ多いほど処理時間が膨れるため、ユーザーにとっては使用感が悪くなる。
紹介することは基本的なことではあるが、知っていると知らないではプログラムの作り方が変わってくると思うので参考にしていただければと思う。特にデータベースなどの大量のデータを扱う場合や照合するプログラムを作る場合は効果が大きいと思う。
エクセルVBAで主に処理時間に影響するのは「シート、セルの操作」「繰り返し処理」である。これをいかに少なくするか、である。私のように他の言語からやっている場合、「シート=2次元配列」と見てしまい、セルを1つ1つ見に行くループをつくってしまいがちである。これは処理を遅くしている原因になる。セル1つ1つにアクセスしているのがセルの操作になるし、座標を移すのに繰り返し処理を行っているからだ。
これはセルの読み込み、書き込み全てに影響する。ではどうすれば解決できるかというと、シートを2次元配列にして扱い、読み書きを配列単位で行うことである。1つ1つのセルに対してではなく、Range(範囲)で一度に読み込み、書き込みするという方法である。こうすることによって余計なループもないし、1回のシート指定だけで済む。計算や検索を行うのにも一度メモリ(2次元配列)にしたほうが高速に処理が出来る。
プログラムの流れとしては、シートのデータを2次元配列に読み込み、そのデータで演算し、結果を2次元配列に格納し、シートに貼り付けるというのが高速なプログラムになると思われる。
では実際にプログラムを紹介する。まずはシート全体を2次元配列にするプログラムである。標準モジュールに下記Sheet2Arrayを記入する。内容としてはシート内の最終行列を取得し、Rangeの範囲内を2次元配列にするというものである。
'シートを配列に読み込む
Public Function Sheet2Array(BookName As String, SheetName As String) As Variant
Dim RowNum As Double
Dim ColNum As Double
With Workbooks(BookName).Sheets(SheetName)
RowNum = .Cells(Rows.Count, 1).End(xlUp).row
ColNum = .Cells(1, Columns.Count).End(xlToLeft).Column
Sheet2Array = .Range(.Cells(1, 1), .Cells(RowNum, ColNum))
End With
End Function
使い方としてはメインフローのデータを読み込みたいところで、
Dim DataBase() As Variant
DataBase = Sheet2Array(ThisWorkbook.Name, "Sheet1")
のようにして使う。1行目で大きさ未定のDataBase配列(Variant型)を定義し、2行目でSheet2Array関数を呼び出してDataBase配列に値が入るようになっている。引数は1つ目がブック名(同じブック内であればThisWorkbook.Nameでブック名が取得できる)、2つ目がシート名である。
これでDataBaseが2次元配列になり、Sheet1の内容が丸々格納されているはずだ。見てお分かりの通り、ループ文はどこにも使われていない。シートの内容をかなり高速で配列化できる。
[adsense]
この2次元配列から必要な部分を抜き取ったり、演算したりする。メモリ上にあるので高速に処理が可能だ。ただし、配列内をループさせるには、どこからどこまでループさせるか把握しておかないといけない。先ほどのDataBase配列はデータが全て詰まっている配列であるのでUbound関数を使っても良いが、どこまでデータが入っているかをチェックする関数を作成したので、そちらでチェックしておくと良いだろう。
下記の関数を標準モジュールに記入する。1列目、1行目をチェックし、空欄になったところから1引いたものをデータ数としている。
'配列の有効列数を求める
Public Function ArrayColumn(ArrayData As Variant) As Double
Dim i As Double
For i = 1 To UBound(ArrayData, 2)
If ArrayData(1, i) = "" Then
ArrayColumn = i - 1
Exit For
End If
Next
If ArrayColumn = 0 Then
ArrayColumn = UBound(ArrayData, 2)
End If
End Function
'配列の有効行数を求める
Public Function ArrayRow(ArrayData As Variant) As Double
Dim i As Double
For i = 1 To UBound(ArrayData, 1)
If ArrayData(i, 1) = "" Then
ArrayRow = i - 1
Exit For
End If
Next
If ArrayRow = 0 Then
ArrayRow = UBound(ArrayData, 1)
End If
End Function
これを使って2次元配列内をループさせてみる。
Dim RowNum As Double '行数
Dim ColNum As Double '列数
Dim i As Double 'ループ変数
Dim j As Double 'ループ変数
RowNum = ArrayRow(DataBase) '行数取得
ColNum = ArrayColumn(DataBase) '列数取得
For i = 1 To RowNum
For j = 1 To ColNum
Debug.Print DataBase(i, j) '処理内容
Next
Next
処理内容のところにIF文や出力内容を入れれば必要なデータのみを抜き出すことが出来る。for Next文を使っているがVBAではインデックス(数字を変数にしてループ)を使ったものだとFor Nextが一番速い。For Each Nextを使ってインデックスループさせると処理速度が急激に遅くなるので注意しよう。
最後に2次元配列をシートに出力する方法を紹介する。先ほどのArrayRow,ArrayColumn関数を使用しているので標準モジュールに追加しておく必要がある。内容としては配列の大きさを求めて、指定したシートに書き込みする処理である。
'配列をシートに書き込む
Public Sub Array2Sheet(ArrayData As Variant, BookName As String, SheetName As String)
Dim RowNum As Double
Dim ColNum As Double
RowNum = ArrayRow(ArrayData)
ColNum = ArrayColumn(ArrayData)
With Workbooks(BookName).Sheets(SheetName)
.Cells.Clear
.Range(.Cells(1, 1), .Cells(RowNum, ColNum)) = ArrayData
End With
End Sub
使い方としてはシートに出力したいところで
Call Array2Sheet(DataBase, ThisWorkbook.Name, "Sheet4")
上記のように呼び出す。これでSheet4にDataBase配列の内容が出力されているはずである。引数としては1つ目が出力する2次元配列、2つ目がブック名、3つ目がシート名である。読み込みと同様、ループ文は使っていないので高速で処理ができる。
ちなみに2次元配列であればArray2Sheet関数でデバッグのようにSheetに出力できるのでプログラム作成中にも役立つはずだ。注意する点としては通常配列は(0,0)から始まるため、このままシートに出力すると余分な空白の1行、1列が出来てしまう。これを防ぐため(わかりやすくするためにも)配列を作成する時に(1 to 1000, 1 to 1000)と言う風に範囲を指定してやると良い。
dim test1(1000,500) '行0~1000 列0~500 の範囲になる
dim test2(1 to 1000, 1 to 500) '行1~1000 列1~500 の範囲になる
自分で作成した2次元配列を出力したい場合は(1 to 1000, 1 to 1000)という感じで範囲を指定して定義しよう。するとシートと座標が一致してわかりやすい。
Excel VBAにはシートを使った検索や、ソート等々があるので高速化させるには付属の機能を使ったほうが高速になる可能性があるが、シートの読み書きをすると速度が落ちるし、プログラムも煩雑になる。結局は昔ながらの2次元配列をfor nextで回すという方法が一番確実で高速になると思われる。
さらに実践的に使いたい方は実践!エクセルVBAで配列を使いこなすを読んでいただけると実用的なプログラムが作れるようになるだろう。また、実践!エクセルVBAでクイックソートを使うを読んでいただけると大体のデータ処理が出来るようになるだろう。
コメント
[…] 以前にも配列を使うと便利という記事を書いたが、もう少し踏み込んで紹介したいと思う。サンプルプログラムも載せていくので参考にしていただければと思う。ただ、筆者は趣味でプ […]
これだと動作しませんよー
書き込みありがとうございます。
どこがどのように動きませんでしょうか?
サンプルは載せていますが、あくまでも概念的な記事なので理解して使わないとうまく動かない可能性はあります。
一応、Excel2000,2010では使っていたので動くはずです。
整数を扱うもの全てdoubleで宣言するのが意味不明.
書き込みありがとうございます。読んでいただきありがとうございます。
宣言は厳密にいえば使用する変数に合わせて指定するとよいですが、数値系はここではdoubleで統一しています。メモリ使用量を減らしたいのであればintegerやlongを適切に使えばよいと思います。(私はプログラマではないし、趣味でやっているレベルなので、そのあたりを気にされるということは多分私よりはプログラミングのレベルが上なのかなと思います)
あとは扱うデータベースの大きさですかね。現在実務で使うようなデータベースだとintegerは簡単にオーバーフローしてしまうと思います。最新エクセルの行数にも対応できませんので。がちがちに使える範囲を限定してエラー処理を入れるのならそれでも良いと思います。
以上、よろしくお願いいたします。
初めまして、VBA初心者です。
数千行あるデータシートから、色が塗られて無かったら別のブックに抜き出すプログラムを作ったのですが、動作が重いため、方法を検索していたところこちらにたどり着きました。
そこで伺いたいのですが、配列に読み込んだ場合、色のデータも入っているのでしょうか?
読み込んだ配列から、色のあるなしで、データの抜き出しは可能でしょうか?
トンチンカンの事を言っていたら申し訳ありません。
ご教示頂けたら幸いです。
書き込みありがとうございます。
色のデータは入っていないです。文字列とか数字だけが配列にはいります。色の有無は何か規則性はあるのでしょうか?数字で何以上、何以下とか、文字列なら何かが含まれているとか。
そうであれば配列に読み込んだ後、条件式で該当する項目だけプログラムで抜き出すというのはできると思います。
また、色などはセルのプロパティになるので、色で判断して抜き出すにはfilterあたりを使うと速いかもしれません。