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

質問

いつもお世話になっております。

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言語はそれに該当するというのは聞きました。）

よろしくお願いいたします。

回答No.8

nda23 · ベストアンサー

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

お礼

nda23様、いろいろなアドバイスをして頂き、ありがとうございました。

これからもよろしくお願いいたします。

補足

nda23様、回答ありがとうございます。

ループ回数を減らして６プロセスにて時間を計測してみました。

１シート×１プロセスの時間よりは1.5倍ほどの時間延長が見られましたが、６シート×１プロセスよりも遙かに時間短縮となりました。

私の思いこみが時間短縮の弊害になっていたという結末に自分の技術力のなさを実感しました。

しばらくこの質問は締め切らずにおいておきます。
他に何か得られそうな気がしますので。

CreateProcessに関しては未だに理解できていないので、新たに質問させてもらうかもしれません。
そのときはよろしくお願いいたします。

回答No.9

noname#140971

' --------------------------------------------------------------------------------
'　構造体変数 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で制御。
終了したら次のパラメータを渡して起動。
２０万ループの中で３０万ループを繰り返すのとどちらが速いかでしょう。
起動そのものは瞬時ですからループが多重でない方が早いような気がします。

※私は、プログラマではありません。
※ですから、あくまでも素人の意見として流し読みされてください。

お礼

Husky2007様、回答ありがとうございます。

皆様からのアドバイスを取り入れて、さらなる高速化を目指していきたいと思います。

真っ先にHusky2007様ご指摘の低レベルな制御文やfor文の部分の見直しをしていきたいと思います。

ありがとうございました。

回答No.7

nda23

６個のブック（各１シート）を作り、それぞれにVBAを置きます。
そのVBAは１シート分の作業をするだけなので、for i=1 to 6　は不要です。
同期を取らず、画面の表示も気にしないのであれば、手動でも構いません。
手動で実行するのと、APIを使う方法では結果に差はありません。
手動で起動しても内部的にはCreateProcessが実行されます。

プロがユーザから依頼を受けて、このようなプログラムを作る場合、
ボタンの１クリックで動作が始まり、実行中はExcelのウィンドウを
見せず、終了時にメッセージボックスが表示される。というような
仕様になることが多いので、APIを使う方法を示しました。
手動で起動、終了確認を行うのであれば、APIを使う必要はありません。

補足

nda23様、早速の回答ありがとうございます。

この方法で疑問に思うのは、CPUに６つの負荷の大きい作業を同時にさせることになるので、逆に処理速度が遅くなるように思うのですが、それは私の思いこみなのでしょうか。
for i=1 to 6で処理するよりは速いとは思いますが、nda23様がおっしゃっているように１／６以下の処理時間で済むとは思えないのですが。

よろしくお願いいたします。

回答No.6

nda23

>１．
メモり領域の指定はできません。
>３．
処理速度向上を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ではこの技は
無理なので、マルチプロセスにします。応用範囲の広い技法です。

補足

nda23様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。

１．やはりだめなのですね。これはあきらめることにします。

３．複数のプロセスの起動の件ですが、紹介頂いたAPIの理解はできていませんが、確認したいことがあります。
(1)１つのブックの中で６個（６シート分）のVBAを作るということか、６ブックを作って各々のブックに１つ（１シート分）のVBAを作るのか。
(2)手動でEXCELを６個起動するのと何が違うのか。

私がやっているデータ処理は下記概略のように６シートのデータの同期をとる必要はないので、１シートずつ分散して処理することで処理時間が１／６になるのであれば最高なのですが、上記のように手動でEXCELを６個起動するのと何が違うのかが分かりません。

私が作ったコードの概略を示しておきますので、参考にしてもらえたらと思います。

for i=1 to 6
　シートiのデータ取り込み
　for j=1 to ２０万
　　データ処理部
　next
next

よろしくお願いいたします。

回答No.5

noname#140971

補足: テスト環境

Windows XP
1.80 GHz、737 MB RAM

回答No.4

noname#140971

３０万ループ部は書き方で約０．０５秒～０．３秒。
この場合、最悪なケースで約１６．７時間。
この場合、最良なケースで約２．７時間。

これは、１万行×５列×６シートを構造体に代入してバイナリファイルとして出力。
それを、ＶＢ６．０で再度構造体に取り込んだ場合の処理時間です。

さて、こういうやり方でも短縮できて３時間まで。

＞繰り返し回数は２０万回です。

３０万ループ部を実際の処理を勘案して２倍の０．１秒を要したと仮定。
それでも５．６時間はかかる計算です。
ですから、＜繰り返し回数は２０万回＞が果たして必要なのかでしょうね。

が、少なくとも５．６時間以内の処理であることは間違いありません。

補足

Husky2007様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。

私のコードではデータ処理部の処理時間は0.08～0.1秒でした。
この部分の高速化ができればと思い、質問をさせて頂いております。

私はVBA初心者でして、構造体というものの使い方がいまいちよく分かっていません。
質問で示させてもらった配列へのデータの格納とは違うのでしょうか。
単純に配列に格納するよりも構造体を使った方が処理速度が向上するというのなら是非試してみたいのですが、構造体のことがよく分かっていません。
>Dim myData as Variant
>myData = Worksheets("myData").range("A1:E10000")
>myDataから１行１列目のデータを取り出すのはmyData(1,1)でできると言うことも分かりました。
上記に対する構造体の具体例を示して頂けるととても助かります。

ループ回数は残念ながら減らすことはできないと思っています。
処理結果にはっきりとした規則性が見られればいいんですが、どうもそのような規則性が見いだせないので、すべてのパラメータを見ていかないと行けないと思っています。
現時点では３０万個のデータですが、今後データが増えていきます。データが増えていけばそれだけ処理時間が長くなりますし、データの規則性があったとしてもデータが増えることで違ってくるでしょうから、ループ回数は変更できないと思います。

データ処理部は配列を使って、シートとのやりとりを一切しないようにしていますし、条件分岐で処理速度が変わるならそのあたりを見直すことはできそうですが、現時点での私の技術力では手詰まり状態です。

ですので、クアッドコアを有効に使える方法などがないかなと思っています。

アドバイス頂ければと思います。

回答No.3

noname#78947

>１．

残念ながら無理だと思います。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

補足

tonton-tribo様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。

キャッシュの制御は無理そうですね。
そこまでしようと思うと他の言語に乗り換えないと行けないと言うことでしょうね。

画面更新、シートへのアクセスに関するアドバイスはすでに対応済みです。
データ処理部ではシートへ一切アクセスしないので、その部分の対応はする必要がないといった方が正しいかもしれません。

テストコード、試してみました。
確かに私のパソコンではtonton-tribo様よりも処理速度は速かったです。
ですが、所詮マルチスレッド非対応なので、CPU使用率は25%です。
クアッドコアを使いこなせていない（宝の持ち腐れ）状態です。

クアッドコアをうまく利用する方法がないでしょうか。
アドバイス頂ければと思います。

回答No.2

lensent

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

１．コードの記述により、メインメモリよりも高速なL1キャッシュ
ＶＢＡでL1キャッシュなどの制御は出来なさそうな気がしますが・・。

３．処理速度を向上させるコツなどありますでしょうか。
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
などで余計な処理を止める。（すでにやってたら、ごめんなさい）
あとは、どうゆう処理を行っているのかわかりませんが
極力、ＳＥＬＥＣＴなどは行わないことと、余計な処理を
させないことぐらいでしょうか。
コードで私などは
GoToなどを使って、余計は処理をさせずに目的の処理まで
飛ばすようにしてますね。

補足

lensent様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。

１．他の回答者様もそのようにおっしゃっているのでだめなのかもしれませんね。

３．再計算を手動にするというコードですが、この部分はあってもなくても関係ないようです。
そもそもシートには再計算をするような数式が入力されていませんので。
おまじない的に記述しておきたいと思います。
Application.ScreenUpdating = Falseは記述しています。

データ処理部ではシートに一切アクセスしないのでselectは使っていません。
余計な処理を行わないことと言うことですが、データ処理部ではif文により様々な条件分岐が含まれています。
条件分岐の仕方などでも処理速度は変わるのでしょうか。
たとえば、
if A=0 and B=0 then
...
endif
と
if A=0 then
if B=0 then
...
endif
endif
は同じ意味だと思いますが、これでどちらが余計な処理をしているとか、よく分からないのですが。
見た目で言えば、andを使っている方がすっきりしているので処理が速そうに思えますが・・・。
あと、if文をselect case文で表現することもできると思うのですが、それらの処理速度に対する影響とかもいまいち分かっていません。

GoToは私も使っています。

アドバイス、よろしくお願いいたします。

回答No.1

FEX2053

複雑なことを考える前に、Excel-VBAはワークシートとデータのやりとり
をすると極端に速度が低下する、と言うことをご存知ですか？

データは可能な限り一気に変数に取り込み、変数だけで処理して最後に
ワークシートに書き出すようにして下さい。

また、ワークシートを表示したままにすると処理速度が低下しますので、
ワークシートは更新しない設定にして処理する、そもそもワークシートを
表示させない、という方法も考えたほうが良いです。

補足

FEX2053様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。

データ処理部ではいっさいシートとのやりとりはしておりません。
最初にデータを配列に格納したあとは何もシートは関係していません。
計算結果も配列に格納して、最後にシートに貼り付けるだけです。

Application.ScreenUpdating=Falseなど、表示関係の部分もきちんとやっているつもりです。

一回のループが終わるたびにデータ処理部で使用した配列は初期化した方がいいのでしょうか。
そういう細かいところでも処理速度が変わるのか、全く分からないので、その他ご存じのことがあればアドバイス頂ければと思います。