ExcelのVBAでアドバイス頂ければ幸いです。


For~Nextステートメントで、かなり大きな繰り返し処理を行うことがあります。
---
Dim n as long
For n = 1 To 1000000
'処理
Next
---
のような感じです。かなり力技。Excel2010を使っています。

処理内容にもよるかもしれませんが、序盤10,000くらいまでは快調なスピードなのですが、以降段々遅くなってしまうようです。100,000以上くらいになると悲しいくらいゆっくり…。パソコンのスペックによるところもあるかもしれませんが、VBAの記述で工夫できる余地があれば改善したいと思っています。

原因とアドバイスを頂ければ嬉しいです!
どうぞよろしくお願い致します!

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/04/30 07:46:47
  • 終了:2012/05/04 13:35:56

回答(2件)

id:nmori No.1

morinatsu回答回数72ベストアンサー獲得回数82012/04/30 17:58:26

ポイント100pt

コメントに書いたことと重なりますが、ここでは「こういう時に自分がやること」という観点で書きます。
 
1. VLOOKUP関数の利用の見直し、その1。VLOOKUPでやる処理をVBAで書き直す。
 特に検索するのが同じテーブルの中だったりすると、新しいデータを追加する度に古いデータも新しいデータをふまえて再計算、ということになりかねません。計算量が爆発してしまいます。
 データの変更をリアルタイムで反映するのがLOOKUP系関数のウリです。リアルタイムでなくて良い場所はLOOKUPを使わず、VBAで直接書き込んだり、一旦VLOOKUPで得た結果を値コピペして再計算させないようにしたり……といったことをします。
 
2. VLOOKUP関数の利用の見直し、その2。VLOOKUPで検索する場所を別の表に切り出す。
 容易に増えるデータ(例:ゴキブリ)と、あまり増えないデータ(例:ゴキブリが棲む都道府県)が別立てになれば、多少爆発の勢いを押さえられるかもしれません。
 これが出来るかどうかはデータの内容によります。
 
3. 処理の分割。集計の場合、100万件を10万件に、10万件を1万件に集約してから他の加工を行うなど。
 
4. Excel以外のソフトに乗り換える。
 今でこそ仕様上可能ですが、100万件というのはExcelで扱うには大きすぎるデータ量です。他方、Accessやその他DB系のソフトはこの手の大量データを扱うのが得意だったりします。
 現実にはAccessでも対応しきれなくて、多数のサーバに処理を分散して実行するHadoopなどの助けを借りるようなものもあり得ますが、そこはもう素人には……。
 
 1,2,3どれが効果あるか、楽か、実現可能かは扱うデータと欲しい結果に寄ります。自分はまず3から考慮します。3の観点でなんとかできるものは将来的にも問題を起こしにくいので。
 パフォーマンスの問題を起こすのは簡単ですが、解決はソフトウェアの技術を学ぶ学生、職業プログラマでも考え込んでしまう問題で、職業プログラマでも不得意な人が多いです。なので、まずは量を押さえ込む工夫を。他はそれからの問題としても良いと思います。

  • id:taknt
    For~Nextステートメントが問題だと思うなら、10,000くらいまででやめて切り替えたらいい。


    ま、問題は処理内容だと思うけど。
  • id:a-kuma3
    処理が書いてないので、あてずっぽ。
    画面描画をオフにしたり、再計算をオフにしてみる。
  • id:miku1973
    皆様ありがとうございます!

    処理内容ですが、
    VLOOKUP関数を組んでいるところが300箇所くらいあって、1回1回計算は必要です。
    既に画面描画はオフにしてますね~。

    少ない経験からですが、処理内容に関係なく、試行回数が10万以上とかになってくると、序盤より終盤の方があきらかに処理速度が落ちている気がしています。1/10くらいのスピードに。
  • id:SALINGER
    仮にメモリが足りなくなってスワップが頻繁になってるならタスクマネージャーで確認してみるといいよ。
  • id:miku1973
    メモリは16GBあるうち4GBを消費していますが、まだ余力がありそうです。CPUも33%で推移しています。使用率の変動はほぼありません。PCは見た目では安定してそうですがよくわかりません。

    今回のケースだと、序盤は24時間で20万くらい進んだのですが、2日めは10万くらいと、処理スピードが落ちているのがわかります。
  • id:nmori
    >>VLOOKUP関数を組んでいるところが300箇所くらいあって、1回1回計算は必要です。
    セル同士で互いに参照しあいながら計算するアルゴリズムだと、正比例どころか、データ量2倍で実際の計算量は2の2乗になったり、2の3乗になったり、それ以上にもなり得ますね。
     根本的な見直しが必要だと思います。
  • id:hathi
    For~Nextのループで1千万回 回しても、最初の頃と終盤で処理速度が変わることはないと思います。 ご自身で、処理内容を別のものにして確認してみてください。
    単純な処理なら、1000万回はさほど待たずに終わります。
     
    どのような処理内容なのかがわかりませんが、シートに300箇所もVLOOKUP関数を組んでいると1回の処理にそれなりの時間がかかると思います。

    例えば、500回の処理ごとに、その時刻をセルに書き残してはいかがでしょう。
    適当なところで(待つのがあきたところでESCで中断して、各500回ごとの時間記録を眺めてから、マクロを再開すれば)数万回、数十万回の状況確認ができます。
     
    (rand()200箇所のテーブルを毎回書き換え、テーブルを検索するvlookup()4箇所を、rand()で発生させた検索語で検索する)を、forループでやってみました。 結構時間がかかり、途中数回escで処理を中断、内容を確認してから再開で、合計700万回ほどです。
    時間計測の結果は、なぜか、終盤の方が、初めの頃より、15パーセント遅くなっているのですが、それは計測結果でわかる範囲で、体感実感できるような差ではないです。
    段々に遅くなるというのは、別のことではないでしょうか。
    テストプログラムだから、検索テーブルを毎回書き換え、検索キーを書き換えて、結果を書き出すような単純なものです。 実際に作るような内容ではありません。 300箇所のvlookupのセルがあり、それを使って数万回もforループで検索するのは、何かファイルの処理をしているのではないですか。
    ファイルの書き換えをやっているとか、そのあたりの処理で、何か起こしている可能性がないか、そのあたりを調べるのも方法でしょう。
    大量の検索を単純に繰り返すだけなら、エクセルを使うよりも、アクセスを使った方が得だとよく言われます。
  • id:miku1973
    関係あるかわかりませんが、処理内容の1つにテキストファイルを毎回吐き出しているというのがあります。各6KB前後ですが、数が多いと処理速度に影響が出る要因になりうるでしょうか。

    序盤は1秒で10個くらい出力できていましたが、n値が10万を超える頃には1秒1個くらいしか出力できなくなりました。
  • id:taknt
    Accessって100万件、得意?

  • id:miku1973
    VLOOKUPの参照範囲(縦100万、横10くらい)が広いからかもしれないです。
    n値を1から始めるとそれないりに快速なのですが、90万くらいから始めたらとんでもなく遅いことがわかりました。
    VLOOKUP該当が終わりの方にあるほど結果出力に時間がかかるのかな?
  • id:nmori
    >>Accessって100万件、得意?
     すいません。キーとかリレーションが活かせればExcelよりは……というレベルの話です。無条件じゃないですね。
  • id:nmori
    >>VLOOKUPの参照範囲(縦100万、横10くらい)が広いからかもしれないです。
     10行のデータから該当する値を探す、という操作を10回繰り返す(10の2乗)のと、
     1万行のデータから探すのを、1万回繰り返す(1万の2乗)のと、
     10万行のデータから探すのを、10万回繰り返す(10万の2乗)のと、
     ……
  • id:nmori
    >>関係あるかわかりませんが、処理内容の1つにテキストファイルを毎回吐き出しているというのがあります。
     クローズせずに、開きっぱなしなら影響あるかもしれません。
     
     が、同じマクロの中で、読み直して再処理とかでもしない限り、”遅い”原因にはなっても、”だんだん遅くなる”原因としては、疑う優先度は低いと思います。
     
    書き出すところだけコメントアウトして見れば確認できると思います。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません