いつもお世話になっております。
VBAのことで教えて欲しいことがあります。
EXCEL2000 VBAであるデータを処理するコードを作っています。
データ数は1万行×5列×6シート=30万です。
処理する際のパラメータの違いから、この30万個のデータをfor文を使って繰り返し処理します。
繰り返し回数は20万回です。
一回の処理に1秒かかるとすると1秒×20万回なので、2~3日かかります。
このデータを処理する速度の向上を考えています。
L1キャッシュ→L2キャッシュ→メインメモリ→HDD
の順に処理速度(アクセス速度?)が遅くなると調べて分かりました。
30万個のデータを配列(メインメモリ?)に格納する方法は下記のようにすればいいと分かりました。
Dim myData as Variant
myData = Worksheets("myData").range("A1:E10000")
myDataから1行1列目のデータを取り出すのはmyData(1,1)でできると言うことも分かりました。
そこで質問です。
1.コードの記述により、メインメモリよりも高速なL1キャッシュ、L2キャッシュを意識して使うことはできるのでしょうか。
処理している間ずっと30万個のデータをL1,L2キャッシュに格納しておけば、メインメモリに格納しておくよりも高速に処理ができると単純に考えました。
2.私のPCはVista、Core2Quad Q9450、4GB DRAMです。
VBAでは4つのコアをすべて使って実行すること(CPU使用率100%)はできないと聞きました。
上記の処理を実行する時、1つのコアで処理させるのがいいのか、複数のコアで処理させるのがいいのか、プロセスの優先度はどうすべきか、など、コードの記述以外の方法で処理速度を上げる方法はあるのでしょうか。
3.処理速度を向上させるコツなどありますでしょうか。
4.VBA以外の言語なら4つのコアをフルパワーで使えるよ、というのがありましたら教えてください。
(C言語はそれに該当するというのは聞きました。)
よろしくお願いいたします。
No.8ベストアンサー
- 回答日時:
>逆に処理速度が遅くなるように思うのですが・・・
「処理内容と環境による。」としか言えません。
入出力の多い処理の場合、マルチスレッド/プロセスは効果的です。
入出力の間のタイムスライスを他の処理に使用できるからです。
大抵、理論値よりも好結果が得られます。確かに、余りにも多数の
プロセスを同時実行すると、システム資源の枯渇により、遅くなる
懸念はあります。しかし、記載の環境では6プロセスくらいは問題に
ならないのではないかと思います。
処理中に入出力が全く無い場合はスレッド中にアイドリングタイムが
少ないので、効果が薄いかも知れませんが、単一プロセスと、複数の
プロセスではCPUコアの振り分けという面では後者が有利になると
思います。
先ずは2シート×1プロセスと、1シート×2プロセスで、時間や
CPU使用率を比較してみては如何でしょう。
この回答への補足
nda23様、回答ありがとうございます。
ループ回数を減らして6プロセスにて時間を計測してみました。
1シート×1プロセスの時間よりは1.5倍ほどの時間延長が見られましたが、6シート×1プロセスよりも遙かに時間短縮となりました。
私の思いこみが時間短縮の弊害になっていたという結末に自分の技術力のなさを実感しました。
しばらくこの質問は締め切らずにおいておきます。
他に何か得られそうな気がしますので。
CreateProcessに関しては未だに理解できていないので、新たに質問させてもらうかもしれません。
そのときはよろしくお願いいたします。
No.9
- 回答日時:
' --------------------------------------------------------------------------------
' 構造体変数 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しているだけです。
とことで、30万回のループ部ですが・・・。
For I=1 TO 30000
For J=1 To 5
For K=1 To 6
処理コード
Next K
Next J
Next I
は、滅茶苦茶に遅いと思います。
0.3秒かかりました。
これを0.05秒まで短縮するには、次の書き方をテスト。
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が注意を喚起しています。
つまり、かなり低レベルの基本的な制御文だけで書くのが高速化の秘訣かと思います。
【実験1】
BLoad、BSave する変数を6つ用意。
VB6のEXEは、それぞれを処理するように仕掛けます。
一斉に6つ起動するEXEを用意するなどでOKかと思います。
DoEventsは必須でしょうが・・・。
で、問題は、内部ループを多重書きした場合より高速化するかどうかでしょう。
【実験2】
20万回ループも起動EXEで制御。
終了したら次のパラメータを渡して起動。
20万ループの中で30万ループを繰り返すのとどちらが速いかでしょう。
起動そのものは瞬時ですからループが多重でない方が早いような気がします。
※私は、プログラマではありません。
※ですから、あくまでも素人の意見として流し読みされてください。
Husky2007様、回答ありがとうございます。
皆様からのアドバイスを取り入れて、さらなる高速化を目指していきたいと思います。
真っ先にHusky2007様ご指摘の低レベルな制御文やfor文の部分の見直しをしていきたいと思います。
ありがとうございました。
No.7
- 回答日時:
6個のブック(各1シート)を作り、それぞれにVBAを置きます。
そのVBAは1シート分の作業をするだけなので、for i=1 to 6 は不要です。
同期を取らず、画面の表示も気にしないのであれば、手動でも構いません。
手動で実行するのと、APIを使う方法では結果に差はありません。
手動で起動しても内部的にはCreateProcessが実行されます。
プロがユーザから依頼を受けて、このようなプログラムを作る場合、
ボタンの1クリックで動作が始まり、実行中はExcelのウィンドウを
見せず、終了時にメッセージボックスが表示される。というような
仕様になることが多いので、APIを使う方法を示しました。
手動で起動、終了確認を行うのであれば、APIを使う必要はありません。
この回答への補足
nda23様、早速の回答ありがとうございます。
この方法で疑問に思うのは、CPUに6つの負荷の大きい作業を同時にさせることになるので、逆に処理速度が遅くなるように思うのですが、それは私の思いこみなのでしょうか。
for i=1 to 6で処理するよりは速いとは思いますが、nda23様がおっしゃっているように1/6以下の処理時間で済むとは思えないのですが。
よろしくお願いいたします。
No.6
- 回答日時:
>1.
メモり領域の指定はできません。
>3.
処理速度向上をVBAで実行するとしたら、マルチプロセスを使う方法が
お勧めです。これは勿論、大変難しい技術ですが、劇的に処理時間が
減少します。
考え方としては、以下の手順になります。
(1)1シート分の処理を行うExcel(VBA)を作る。
(2)プロセスの起動で、6個のプロセスを立ち上げる。
(3)全プロセス終了後、各Excelのシートをコピーして1ブックにする。
独立したプロセスが処理を分散するので、処理時間は理論上、1/6に
なります。(大抵はこれより速い)
プロセスの起動は下記APIを使います。
http://msdn.microsoft.com/ja-jp/library/cc429066 …
難しいのはプロセス終了の同期を取ることですが、仮に自動的に
終了するVBAだったとすると、プロセスの終了を待機するには
下記APIなどを使います。
http://msdn.microsoft.com/ja-jp/library/cc429066 …
尚、プロセスの終了をポーリングしたりすると、CPUを浪費するので、
出来るだけ避けるようにします。止むを得ない場合はSleepを入れる
ようにします。
http://msdn.microsoft.com/ja-jp/library/cc429358 …
マルチスレッドを使うと、もっと良いのですが、VBではこの技は
無理なので、マルチプロセスにします。応用範囲の広い技法です。
この回答への補足
nda23様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。
1.やはりだめなのですね。これはあきらめることにします。
3.複数のプロセスの起動の件ですが、紹介頂いたAPIの理解はできていませんが、確認したいことがあります。
(1)1つのブックの中で6個(6シート分)のVBAを作るということか、6ブックを作って各々のブックに1つ(1シート分)のVBAを作るのか。
(2)手動でEXCELを6個起動するのと何が違うのか。
私がやっているデータ処理は下記概略のように6シートのデータの同期をとる必要はないので、1シートずつ分散して処理することで処理時間が1/6になるのであれば最高なのですが、上記のように手動でEXCELを6個起動するのと何が違うのかが分かりません。
私が作ったコードの概略を示しておきますので、参考にしてもらえたらと思います。
for i=1 to 6
シートiのデータ取り込み
for j=1 to 20万
データ処理部
next
next
よろしくお願いいたします。
No.4
- 回答日時:
30万ループ部は書き方で約0.05秒~0.3秒。
この場合、最悪なケースで約16.7時間。
この場合、最良なケースで約2.7時間。
これは、1万行×5列×6シートを構造体に代入してバイナリファイルとして出力。
それを、VB6.0で再度構造体に取り込んだ場合の処理時間です。
さて、こういうやり方でも短縮できて3時間まで。
>繰り返し回数は20万回です。
30万ループ部を実際の処理を勘案して2倍の0.1秒を要したと仮定。
それでも5.6時間はかかる計算です。
ですから、<繰り返し回数は20万回>が果たして必要なのかでしょうね。
が、少なくとも5.6時間以内の処理であることは間違いありません。
この回答への補足
Husky2007様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。
私のコードではデータ処理部の処理時間は0.08~0.1秒でした。
この部分の高速化ができればと思い、質問をさせて頂いております。
私はVBA初心者でして、構造体というものの使い方がいまいちよく分かっていません。
質問で示させてもらった配列へのデータの格納とは違うのでしょうか。
単純に配列に格納するよりも構造体を使った方が処理速度が向上するというのなら是非試してみたいのですが、構造体のことがよく分かっていません。
>Dim myData as Variant
>myData = Worksheets("myData").range("A1:E10000")
>myDataから1行1列目のデータを取り出すのはmyData(1,1)でできると言うことも分かりました。
上記に対する構造体の具体例を示して頂けるととても助かります。
ループ回数は残念ながら減らすことはできないと思っています。
処理結果にはっきりとした規則性が見られればいいんですが、どうもそのような規則性が見いだせないので、すべてのパラメータを見ていかないと行けないと思っています。
現時点では30万個のデータですが、今後データが増えていきます。データが増えていけばそれだけ処理時間が長くなりますし、データの規則性があったとしてもデータが増えることで違ってくるでしょうから、ループ回数は変更できないと思います。
データ処理部は配列を使って、シートとのやりとりを一切しないようにしていますし、条件分岐で処理速度が変わるならそのあたりを見直すことはできそうですが、現時点での私の技術力では手詰まり状態です。
ですので、クアッドコアを有効に使える方法などがないかなと思っています。
アドバイス頂ければと思います。
No.3
- 回答日時:
>1.
残念ながら無理だと思います。C言語などなら可能かもしれませんがVBではそこまで細かく指定できません。
>3.
・画面更新を止める
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
- 回答日時:
ハードのことは分からないのですが、
1.コードの記述により、メインメモリよりも高速なL1キャッシュ
VBAでL1キャッシュなどの制御は出来なさそうな気がしますが・・。
3.処理速度を向上させるコツなどありますでしょうか。
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
などで余計な処理を止める。(すでにやってたら、ごめんなさい)
あとは、どうゆう処理を行っているのかわかりませんが
極力、SELECTなどは行わないことと、余計な処理を
させないことぐらいでしょうか。
コードで私などは
GoToなどを使って、余計は処理をさせずに目的の処理まで
飛ばすようにしてますね。
この回答への補足
lensent様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。
1.他の回答者様もそのようにおっしゃっているのでだめなのかもしれませんね。
3.再計算を手動にするというコードですが、この部分はあってもなくても関係ないようです。
そもそもシートには再計算をするような数式が入力されていませんので。
おまじない的に記述しておきたいと思います。
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
- 回答日時:
複雑なことを考える前に、Excel-VBAはワークシートとデータのやりとり
をすると極端に速度が低下する、と言うことをご存知ですか?
データは可能な限り一気に変数に取り込み、変数だけで処理して最後に
ワークシートに書き出すようにして下さい。
また、ワークシートを表示したままにすると処理速度が低下しますので、
ワークシートは更新しない設定にして処理する、そもそもワークシートを
表示させない、という方法も考えたほうが良いです。
この回答への補足
FEX2053様、回答ありがとうございます。
そして、返事が遅くなり申し訳ありません。
データ処理部ではいっさいシートとのやりとりはしておりません。
最初にデータを配列に格納したあとは何もシートは関係していません。
計算結果も配列に格納して、最後にシートに貼り付けるだけです。
Application.ScreenUpdating=Falseなど、表示関係の部分もきちんとやっているつもりです。
一回のループが終わるたびにデータ処理部で使用した配列は初期化した方がいいのでしょうか。
そういう細かいところでも処理速度が変わるのか、全く分からないので、その他ご存じのことがあればアドバイス頂ければと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) エクセルVBA コードが同じでもファイルによって処理速度が大きく変わるのはなぜ 5 2022/11/06 21:34
- その他(IT・Webサービス) 高速処理可能な表計算ソフトについて ExcelやGoogleスプレッドシートのような表計算ソフトで、 2 2023/04/29 16:06
- BTOパソコン PCの選び方 6 2022/09/11 00:16
- Excel(エクセル) エクセルVBA マクロ処理中のポップアップメッセージについて 1 2023/08/04 21:34
- その他(プログラミング・Web制作) プログラミング 処理速度 1 2022/11/25 11:05
- Excel(エクセル) 【VBA】指定フォルダに格納中のテキストファイルをエクセルで処理し結果のエクセルを新規フォルダに保存 1 2022/03/25 14:19
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- デスクトップパソコン 仕事で使うPC 10 2023/04/23 00:27
- Visual Basic(VBA) EXCEL VBAで教えてください。 1 2022/12/22 04:20
- Visual Basic(VBA) 複数指定セルの可視セルのみを別シートに転記するVBAについて 2 2022/05/27 21:19
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
メルカリのメルカードで買い物...
-
「ご処理進めて頂きますようお...
-
エクセルで、日付を入力すると...
-
VBAでループ内で使う変数名を可...
-
【Excel】特定の文字を含むセル...
-
switch の範囲指定
-
DoEventsがやはり分からない
-
EXCEL VBA マクロ 実行する度に...
-
UMLでの例外処理
-
ボタンクリックイベント 重複...
-
VB.NET Excelを読み込んでDataT...
-
エクセルVBA マクロ処理中のポ...
-
【Find関数】あるのに見つから...
-
実時間処理ってなんですか?
-
Excel VBAにて2つの処理を同時...
-
お家デートをしててハグを長い...
-
FFTの結果ついて
-
リョウ・・・量?料?
-
条件付きコンパイル: #IF 1 Th...
-
再帰処理を途中で抜けるには
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「ご処理進めて頂きますようお...
-
メルカリのメルカードで買い物...
-
エクセルで、日付を入力すると...
-
VBAでループ内で使う変数名を可...
-
【Excel】特定の文字を含むセル...
-
EXCEL VBA マクロ 実行する度に...
-
DoEventsがやはり分からない
-
UMLでの例外処理
-
月度は何て読みますか?
-
switch の範囲指定
-
VBの質問#if 0 then ってどう...
-
セルの値が0はクリアするマクロ
-
VB.NET Excelを読み込んでDataT...
-
Do~Loopした回数をカウントしたい
-
Loadイベント中にほかのイベン...
-
Select Case文でこのようなこと...
-
findは動くがfindnextがマクロ...
-
緊急です。 知り合いから50kgの...
-
リョウ・・・量?料?
-
理不尽、行き場のないイライラ...
おすすめ情報