VBAで高速にデータを処理するコツを教えてください

Question

いつもお世話になっております。

VBAのことで教えて欲しいことがあります。

EXCEL2000 VBAであるデータを処理するコードを作っています。
データ数は１万行×５列×６シート＝３０万です。
処理する際のパラメータの違いから、この３０万個のデータをfor文を使って繰り返し処理します。
繰り返し回数は２０万回です。
一回の処理に１秒かかるとすると１秒×２０万回なので、２～３日かかります。

このデータを処理する速度の向上を考えています。

L1キャッシュ→L2キャッシュ→メインメモリ→HDD

の順に処理速度（アクセス速度？）が遅くなると調べて分かりました。

３０万個のデータを配列（メインメモリ？）に格納する方法は下記のようにすればいいと分かりました。
Dim myData as Variant
myData = Worksheets("myData").range("A1:E10000")
myDataから１行１列目のデータを取り出すのはmyData(1,1)でできると言うことも分かりました。

そこで質問です。
１．コードの記述により、メインメモリよりも高速なL1キャッシュ、L2キャッシュを意識して使うことはできるのでしょうか。
処理している間ずっと３０万個のデータをL1，L2キャッシュに格納しておけば、メインメモリに格納しておくよりも高速に処理ができると単純に考えました。

２．私のPCはVista、Core2Quad Q9450、4GB DRAMです。
VBAでは４つのコアをすべて使って実行すること（CPU使用率100%）はできないと聞きました。
上記の処理を実行する時、１つのコアで処理させるのがいいのか、複数のコアで処理させるのがいいのか、プロセスの優先度はどうすべきか、など、コードの記述以外の方法で処理速度を上げる方法はあるのでしょうか。

３．処理速度を向上させるコツなどありますでしょうか。

４．VBA以外の言語なら４つのコアをフルパワーで使えるよ、というのがありましたら教えてください。
（C言語はそれに該当するというのは聞きました。）

よろしくお願いいたします。

nda23 · Accepted Answer

>逆に処理速度が遅くなるように思うのですが・・・
「処理内容と環境による。」としか言えません。
入出力の多い処理の場合、マルチスレッド/プロセスは効果的です。
入出力の間のタイムスライスを他の処理に使用できるからです。
大抵、理論値よりも好結果が得られます。確かに、余りにも多数の
プロセスを同時実行すると、システム資源の枯渇により、遅くなる
懸念はあります。しかし、記載の環境では6プロセスくらいは問題に
ならないのではないかと思います。
処理中に入出力が全く無い場合はスレッド中にアイドリングタイムが
少ないので、効果が薄いかも知れませんが、単一プロセスと、複数の
プロセスではCPUコアの振り分けという面では後者が有利になると
思います。
先ずは２シート×１プロセスと、１シート×２プロセスで、時間や
CPU使用率を比較してみては如何でしょう。

noname#140971 · Answer

' --------------------------------------------------------------------------------
'　構造体変数 MyMenu を Menu.ini に保存
' --------------------------------------------------------------------------------
Private Function BSave(ByVal FileName As String, ByRef MyMenu As MENU) As Boolean
On Error GoTo Err_BSave
　　Dim isOK　　　　As Boolean
　　Dim intFreeFile As Integer
　　
　　isOK = True
　　intFreeFile = FreeFile
　　Open FileName For Random As intFreeFile Len = Len(MyMenu)
　　Put #intFreeFile, 1, MyMenu
Exit_BSave:
On Error Resume Next
　　Close #intFreeFile
　　BSave = isOK
　　Exit Function
Err_BSave:
　　isOK = False
　　Resume Exit_BSave
End Function

思わず構造体変数という言葉を使ってしまいましたが変数でも構いません。
このように、私は、BSave、BLoadという関数でエクセルやアクセスから吐き出してVB6.0のEXEで処理させてきました。
ですから、思わず構造体変数という言葉を使ってしまいました。
ただ、見れば判りますが変数の大きさは固定長でないといけません。
BSave、BLoadなんて関数名を付与していますが、要はランダムファイルにPut、Getしているだけです。

とことで、３０万回のループ部ですが・・・。

For I=1 TO 30000
　　For J=1 To 5
　　　　For K=1 To 6
　　　　　　処理コード
　　　　Next K
　　Next J
Next I

は、滅茶苦茶に遅いと思います。
０．３秒かかりました。
これを０．０５秒まで短縮するには、次の書き方をテスト。

For I=1 TO 30000
　　For J=1 To 5
　　　　K1用の処理コード
　　　　K2用の処理コード
　　　　K3用の処理コード
　　　　K4用の処理コード
　　　　K5用の処理コード
　　　　K6用の処理コード
　　Next J
Next I

さらに言えば、

For I=1 TO 30000
　　J1用の処理コード
　　J2用の処理コード
　　J3用の処理コード
　　J4用の処理コード
　　J5用の処理コード
Next I

と、書くほうが処理速度は向上する筈です。

【蛇足】

P=30000
For I=1 TO P

の場合、Iの値はメモリ上で書き換えられないで別の仕組みで高速処理されると推察します。
で、For部に変数を埋め込むとメモリ上で書き換えらると思います。
先ずは、メモリ上での計算を抑制することだと思います。
（多分、これはされていると思います）

また、IIFよりもIF THEN-ELSE 文が高速で処理されるとMicrosoftが注意を喚起しています。
つまり、かなり低レベルの基本的な制御文だけで書くのが高速化の秘訣かと思います。

【実験１】

BLoad、BSave する変数を６つ用意。
VB6のEXEは、それぞれを処理するように仕掛けます。
一斉に６つ起動するEXEを用意するなどでOKかと思います。
DoEventsは必須でしょうが・・・。
で、問題は、内部ループを多重書きした場合より高速化するかどうかでしょう。

【実験２】

２０万回ループも起動EXEで制御。
終了したら次のパラメータを渡して起動。
２０万ループの中で３０万ループを繰り返すのとどちらが速いかでしょう。
起動そのものは瞬時ですからループが多重でない方が早いような気がします。

※私は、プログラマではありません。
※ですから、あくまでも素人の意見として流し読みされてください。

nda23 · Answer

６個のブック（各１シート）を作り、それぞれにVBAを置きます。
そのVBAは１シート分の作業をするだけなので、for i=1 to 6　は不要です。
同期を取らず、画面の表示も気にしないのであれば、手動でも構いません。
手動で実行するのと、APIを使う方法では結果に差はありません。
手動で起動しても内部的にはCreateProcessが実行されます。

プロがユーザから依頼を受けて、このようなプログラムを作る場合、
ボタンの１クリックで動作が始まり、実行中はExcelのウィンドウを
見せず、終了時にメッセージボックスが表示される。というような
仕様になることが多いので、APIを使う方法を示しました。
手動で起動、終了確認を行うのであれば、APIを使う必要はありません。

nda23 · Answer

>１．
メモり領域の指定はできません。
>３．
処理速度向上をVBAで実行するとしたら、マルチプロセスを使う方法が
お勧めです。これは勿論、大変難しい技術ですが、劇的に処理時間が
減少します。
考え方としては、以下の手順になります。
(1)１シート分の処理を行うExcel（VBA）を作る。
(2)プロセスの起動で、6個のプロセスを立ち上げる。
(3)全プロセス終了後、各Excelのシートをコピーして１ブックにする。
独立したプロセスが処理を分散するので、処理時間は理論上、1/6に
なります。（大抵はこれより速い）

プロセスの起動は下記APIを使います。
http://msdn.microsoft.com/ja-jp/library/cc429066.aspx
難しいのはプロセス終了の同期を取ることですが、仮に自動的に
終了するVBAだったとすると、プロセスの終了を待機するには
下記APIなどを使います。
http://msdn.microsoft.com/ja-jp/library/cc429066.aspx

尚、プロセスの終了をポーリングしたりすると、CPUを浪費するので、
出来るだけ避けるようにします。止むを得ない場合はSleepを入れる
ようにします。
http://msdn.microsoft.com/ja-jp/library/cc429358.aspx

マルチスレッドを使うと、もっと良いのですが、VBではこの技は
無理なので、マルチプロセスにします。応用範囲の広い技法です。

noname#140971 · Answer

補足: テスト環境

Windows XP
1.80 GHz、737 MB RAM

noname#140971 · Answer

３０万ループ部は書き方で約０．０５秒～０．３秒。
この場合、最悪なケースで約１６．７時間。
この場合、最良なケースで約２．７時間。

これは、１万行×５列×６シートを構造体に代入してバイナリファイルとして出力。
それを、ＶＢ６．０で再度構造体に取り込んだ場合の処理時間です。

さて、こういうやり方でも短縮できて３時間まで。

＞繰り返し回数は２０万回です。

３０万ループ部を実際の処理を勘案して２倍の０．１秒を要したと仮定。
それでも５．６時間はかかる計算です。
ですから、＜繰り返し回数は２０万回＞が果たして必要なのかでしょうね。

が、少なくとも５．６時間以内の処理であることは間違いありません。

tonton-tribo · Answer

>１．

残念ながら無理だと思います。C言語などなら可能かもしれませんがVBではそこまで細かく指定できません。

>３．

・画面更新を止める
Application.ScreenUpdatingプロパティを処理開始前にFalseにして、終了後にTrueに戻すだけでも結構高速化します。配列に読み込んだ場合は差が小さいかもしれませんが無駄にはならないでしょう。

・大量のセルにアクセスする場合は配列に読み込む
これに関しては質問者さんがすでに知ってるみたいですね。

・セルをSelectしない
データを入力するセルをCells(1,1).Select→Selection.Value=1と記述するよりもCells(1,1).Value=1と記述したほうが高速です。同じようにWorksheets("Sheet1").Active→ActiveSheets.Name="Sheet1"と記述するよりもWorksheets("Sheet1").Name="Sheet1"と記述したほうが高速です。

・シートの指定を名前じゃなくて番号で行なう
通常はWorksheets("Sheet1")で指定しているところをWorksheets(1)と番号で指定すれば処理が高速化します。ただし、シートの番号を間違えないように注意が必要です。

>私のPCはVista、Core2Quad Q9450、4GB DRAMです。

これだけ高性能なパソコンなら配列に格納しただけで十分すぎるほどに高速ですよ。先ほど、簡単なプログラムを作って1万列×30行の全てのセルに数字を入力するのにかかる時間を計測しました。配列に読み込んだ場合と直接セルに入力した場合の両方で計測しましたが私が使っている低スペックパソコンでも直接が1分未満(約42秒)、配列が1秒未満(約0.96秒)でした。質問者さんのパソコンで同じプログラムを使って計測すれば直接でも30秒を切りそうですし、配列なら確実に1秒未満でしょうね。私が計測に使ったコードを載せるので試してみてください。多分、配列に読み込ませれば他の対策はやらなくてもかなり速いと思います。ちなみに、私が使っているパソコンはCeleron D 3.06GHz＋3GBメモリです。

・直接入力
Sub Test1()

Range("A1:CV10000").Clear

Dim Time_S As Double, Time_E As Double

Time_S = Timer

Application.ScreenUpdating = False

Dim i As Long, j As Long, buf As Long

For i = 1 To 30
  For j = 1 To 10000
    Cells(j, i) = i + j
  Next j
Next i

Application.ScreenUpdating = True

Time_E = Timer

Debug.Print Time_E - Time_S

End Sub

・配列
Sub Test2()

Range("A1:CV10000").Clear

Dim Time_S As Double, Time_E As Double

Time_S = Timer

Application.ScreenUpdating = False

Dim i As Long, j As Long, buf As Long, C As Variant

C = Range("A1:CV10000")

For i = 1 To 30
  For j = 1 To 10000
    C(j, i) = i + j
  Next j
Next i

Range("A1:CV10000") = C

Application.ScreenUpdating = True

Time_E = Timer

Debug.Print Time_E - Time_S

End Sub

lensent · Answer

ハードのことは分からないのですが、

１．コードの記述により、メインメモリよりも高速なL1キャッシュ
ＶＢＡでL1キャッシュなどの制御は出来なさそうな気がしますが・・。

３．処理速度を向上させるコツなどありますでしょうか。
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
などで余計な処理を止める。（すでにやってたら、ごめんなさい）
あとは、どうゆう処理を行っているのかわかりませんが
極力、ＳＥＬＥＣＴなどは行わないことと、余計な処理を
させないことぐらいでしょうか。
コードで私などは
GoToなどを使って、余計は処理をさせずに目的の処理まで
飛ばすようにしてますね。

FEX2053 · Answer

複雑なことを考える前に、Excel-VBAはワークシートとデータのやりとり
をすると極端に速度が低下する、と言うことをご存知ですか？

データは可能な限り一気に変数に取り込み、変数だけで処理して最後に
ワークシートに書き出すようにして下さい。

また、ワークシートを表示したままにすると処理速度が低下しますので、
ワークシートは更新しない設定にして処理する、そもそもワークシートを
表示させない、という方法も考えたほうが良いです。

VBAで高速にデータを処理するコツを教えてください

>逆に処理速度が遅くなるように思うのですが・・・

この回答への補足

' --------------------------------------------------------------------------------

６個のブック（各１シート）を作り、それぞれにVBAを置きます。

この回答への補足

>１．

この回答への補足

補足: テスト環境

３０万ループ部は書き方で約０．０５秒～０．３秒。

この回答への補足

>１．

この回答への補足

ハードのことは分からないのですが、

この回答への補足

複雑なことを考える前に、Excel-VBAはワークシートとデータのやりとり

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング