EXCEL VBAマクロの高速化シート同士のマッチング

追記もっと早い方法があるじゃん　しかも簡単　はずかしー
うーむ俺すごいあほだった - uoz 作業日記

Excelのマクロを高速化する方法というのはネット上にいくらでも情報があるが、一般的な方法で全然解決しなかった時に効いた方法を紹介します。

情報や数学の勉強をちゃんとした人には当たり前の話で笑われるかもしれませんが、まあ僕みたいな人もいると思うので、記録しておきます。

良くある方法

一般的には、この辺のサイトに言われているように、Application.ScreenUpdating = Falseで画面表示を止めたり、RangeオブジェクトをVariant型の変数にいれてから操作するという方法があります。

ExcelVBAを高速化する7つの方法
http://d.hatena.ne.jp/megascus/20120318/1332070840

VBA高速化テクニック
http://officetanaka.net/Excel/vba/speed/index.htm

あるマクロを作っていた時、これらの方法で数十パーセントの高速化ができたのですが、そもそもが終了に数十分もかかる(これは推測で、実は遅すぎて最後まで実行できたことがなかった)ものだったので、それが15分くらいになっただけで、あまり意味がないなあ、という状況がありました。

劇的に改善した方法

しかし、マクロのアルゴリズムを改良することで、それを15分から3分程度まで、つまり1/5くらい、オーダーが変わるか変わらないかくらいまで高速化できました。

これは、このマクロが行っている処理内容に依存した対策なので、いつでも使えるわけではありません。一応誰かの役に立つかもしれないので記録しておきます。

まず、前提として、そのマクロは2つのシートからある列が共通する行を抽出して結合するというものでした。リレーショナルデータベースの結合みたいなもんです。シート1のA列と、シート2のB列の値が同じ行をそれぞれ抽出して別なとこに記録しておく、そして合致する行が複数あったらエラーをはく、という感じです。

最初の方法:両方の行を全部見る

この処理を実現するため、最初は、シート1のA列を上から順に1行ずつ見ていき、一行ごとに、シート2のB列を上から1行ずつ全部見ていき、値が合致するかどうかチェックするという非効率なものでした。

合致する行が複数あるかどうかも知りたいので、シート2の走査では、見つかったら途中でやめることもできません。この処理では、シート1とシート2の行数をかけた数に比例する時間がかかることになります。

f:id:uoz:20140401011511p:plain

改善した方法:事前にソートする

このかけ算をなんとか無くすことはできないか、せめて片方のシートの行数に比例するくらいの時間でできないか、と考えました。このマクロでは、両方のシートが数万行あったのですが、ネットを検索してみると、n万行の処理はたいした時間がかからないという話がたくさんでてきました。これはたぶん一つのシートの話です。

そこで、両方のシートを事前に当該の列でソートし、B列の方は見つかった次の行から始める、という処理に変えました。

処理の最初は、改善前と同じです。シート1のA列を1行ずつ見ていって、その値と合致するシート2のB列をこれまた1行ずつ探します。ただし、ソートされているので、シート1の最初の行にあるのと同じ値は、もしあるとすれば、おそらくシート2の上の方にあります。最初の値が見つかるのにかかる時間はかなり短縮されて、うまく行けばある行を1行読み込むのとそれほど変わらなくなります。

そして、次に、シート1のA列の次の行と同じ値を探すのに、シート2の最初から探すのではなく、シート1の前の行が見つかった次の行から探し始めます。ソートされているので、シート1の前の行が見つかった行より前に見つけたい行があることはあり得ないからです。これも、うまく行けば一瞬で終わります。

こんな風に、シート1を1行進めてシート2をちょっと進め、またシート1を1行すすめてシート2をちょっと進め、とシート2の方で走査する行数をかなり少なくすることができました。その効果は絶大でした。

f:id:uoz:20140401012812p:plain