エクセルマクロで大量データを処理すると、マクロの処理が遅かったり、重くなってしまったりします。
たとえば、1000行を超えるデータを扱うとなると、処理に10分以上かかってしまうこともあります。
この記事では、エクセルマクロのプログラムを高速で処理する方法を紹介します。
また、高速で使えるプログラムを無料ダウンロードできるようにしていますので、ぜひ利用してみてください。
- マクロvbaの処理速度が遅くなる原因
- マクロvbaの処理を高速化するときの基本
- マクロvbaで大量データのマッチング・照合・集計を高速で行う方法（VBAの高速化）
- WithステートメントやVariant型はそこまで影響しない
マクロvbaの処理速度が遅くなる原因
エクセルマクロVBAの処理速度が遅くなる主な原因は、以下の3つです。
[2] エクセル画面更新
[3] Selectの使用
上記の3つを使用すると、マクロの処理が一気に低下します。なぜ処理が遅くなるかというと、理由は以下の通りです。
エクセルワークシート関数の使用を処理が遅くなる
ワークシート関数とは、vlookupやsumif関数のことです。
実は、マクロで処理をするたびに、開かれている「すべてのエクセルファイル」の「すべてのシート」の「すべてのワークシート関数」が再計算されます。
つまり、ワークシート関数があればあるほど、関数の計算量が増えてしまい、エクセルマクロの動作が重くなります。もし、計算量の多い関数(vlookup,sumif,sumproductは要注意)が含まれていると、さらに重たくなります。
もし、あなたがエクセル仕事が遅くて困っているなら、 ・エクセルワークシート関数を使わない ・マクロ起動中だけ、エクセルワークシート関数の再計算をオフにする といった対策が必要です。
エクセルの画面表示の更新のせいで処理が遅くなる
マクロは、高速で処理対象を切り替えます。あなたも、マクロを使っているとき、画面表示がパッパッと次々と切り替わるのをみたことがあるでしょう。
しかし、この画面表示の切り替えは処理時間を奪っています。とくにエクセルファイルを2つ開いたり、パワポやワードなどの外部ソフトウェアを操作したりすると、時間がかかります。
そのため、エクセルの画面表示の更新設定をオフにすると、処理が早くなます。
Selectを使用すると処理が遅くなる
マクロでは、Selectを使用すると処理が遅くなります。たとえば、以下のは、処理が遅いプログラムです。
Range("C1").Select ActiveCell.Value = "100" Range("C2").Select ActiveCell.Value = "200" Range("C3").Select ActiveCell.Value = "300"
このプログラムの処理速度は著しく遅いです。なぜなら、一つ一つをセルを指定して、確認しながら作業をしているからです。100行くらいであれば、不便さは感じませんが、200,300行とデータが増えると、処理が遅くて仕事になりません。
Selectは、マクロの自動記録で作成したプログラムには、必ず記載されています。手書きで編集しないと、select頻発により、マクロの動きが遅いままです。
マクロの自動記録でプログラムを作成している人は、注意が必要です。
マクロvbaの処理を高速化するときの基本
マクロの処理速度を高速化したいときに、必ず取り入れてほしいプログラムを紹介します。
[2] Application.ScreenUpdating = False｜画面表示の更新を停止
[3] Selectを使わない
たとえば、以下のプログラムでは、マクロの処理が遅くなります。
Sub slow_program() Dim hida, migi As Long For migi = 2 To 11 Range("F" & migi).select For hida = 2 To 100001 If selection.value = Range("B" & hida).Value Then Range("C" & hida).Value = "OK" End If Next Next End Sub
逆に以下のプログラムを使うと、処理速度が速くなります。
Sub fast_program() Dim hida, migi As Long Application.ScreenUpdating = False '---画面の表示更新をオフ--- Application.Calculation = xlManual '---自動計算をオフ--- For migi = 2 To 11 '---Selectを入れない--- For hida = 2 To 100001 If Range("F" & migi).Value = Range("B" & hida).Value Then Range("C" & hida).Value = "OK" End If Next Next Application.Calculation = xlAutomatic '画面の表示更新をオン Application.ScreenUpdating = True '自動計算をオン End Sub
実際に、上記の2つのマクロの処理時間を計測してみると、以下の違いがあります。
| マクロ | [1] Application. Calculation =xlManual |
[2] Application. ScreenUpdating = False |
[3] Select | 処理時間 |
|---|---|---|---|---|
| No1 | なし | なし | なし | 35分12秒 |
| No2 | あり | あり | あり | 0分47秒 |
Application.Calculation=xlManual｜自動計算・再計算を停止
エクセル関数の自動計算や再計算を停止させると、マクロの処理速度が一気に上がります。
もし、あなたが使うエクセルファイルにワークシート関数が含まれているのであれば、Application.Calculation=xlManualのプログラムを入れましょう。
ただし、プログラムの使い方には、注意が必要です。上手に使わないと、ワークシート関数が自動計算されなくなってしまいます。
なぜなら、Application.Calculation=xlManualのプログラムは、関数を手動計算に変更するからです。正しい入れ方は、プログラムを参考にしてください。
Application.ScreenUpdating = False｜画面表示の更新を停止
マクロ動作中に切り替わる画面更新をオフにします。スクリーンの更新を解除できるので、処理が早くなります。
なお、Application.ScreenUpdating = Falseのプログラムを入れた後は、Application.ScreenUpdating = Trueを必ず入れましょう。
そうしないと、マクロを画面表示の更新がオフになったままになってしまいます。
Selectを使わない
Selectを使うと、遅くなるので、できる限りselectは避けましょう。
しかし、Selectを使わないようにするには、あなた自身がマクロのプログラムを自分で編集できるようになる必要があります。
もし、あなたが自分でマクロを編集できるようになりたいなら、こちらの無料動画で学んでみてください。
マクロvbaで大量データのマッチング・照合・集計を高速で行う方法（VBAの高速化）
マクロで大量データをマッチングすることがあります。その場合は、以下の5つの方法があります。
[2] for each文
[3] Findメソッド
[4] 動的配列
[5] SQLの利用
[1]に近いほど、プログラムとしてはカンタンですが、処理速度は遅くなります。逆に、[5]に近いほど、プログラムは難しいですが、処理速度は早くなります。
実際に、10万行あるエクセルデータをそれぞれのマクロで処理して、その時間を計測しました。
この処理を行ったマクロは、以下のような処理時間でした。
| No | マクロ | 処理の時間(秒) |
|---|---|---|
| 1 | for next文 | 24秒 |
| 2 | for each文 | 12秒 |
| 3 | find | 9秒 |
| 4 | 動的配列 | 6秒 |
| 5 | SQL | 未計測 |
方法1｜For Next 構文でマッチング
もっとも基本的なデータマッチングのやり方です。データ数があまりに増えると、遅く感じます。それでも、ワークシート関数で行くより格段に効率的です。
Sub sample1_for_next() Dim hida, migi As Long Application.ScreenUpdating = False Application.Calculation = xlManual For migi = 2 To 21 For hida = 2 To 100001 If Range("F" & migi).Value = Range("B" & hida).Value Then Range("C" & hida).Value = "OK" End If Next Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub
方法2｜For Each 構文でマッチング
調査対象のセルをすべてメモリで参照する方法です。個々のセルを順に見つけなおしている方法1より高速処理が可能です。
Sub sample2_for_each() Dim rM, rH As Range Application.ScreenUpdating = False Application.Calculation = xlManual For Each rM In Range("F2:F21") For Each rH In Range("B2:B100001") If rM.Value = rH.Value Then rH.Offset(, 1).Value = "OK" End If Next Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub
方法3｜Findメソッドでマッチング
.Findメソッドを使って条件に一致するセルを見つけます。エクセルの表の機能で言うと、「検索」に相当します。For Each構文でセルを探すよりさらに高速です。
Sub sample3_find() Dim rM, rH, rMy, rFirst, rU As Range Application.ScreenUpdating = False Application.Calculation = xlManual For Each rM In Range("F2:F21") Set rH = Range("B1:B100001") Set rMy = rH.Find(What:=rM.Value) If rMy Is Nothing Then Exit For Else Set rFirst = rMy rMy.Offset(, 1).Value = "OK" End If Do Set rMy = rH.FindNext(rMy) If rMy.Address = rFirst.Address Then Exit Do Else rMy.Offset(, 1).Value = "OK" End If Loop Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub
Findメソッドを使用すれば、
・完全一致/部分一致を指定
・大文字小文字を区別する/しないを指定
と、検索のバリエーションもあります。
上に示したサンプルは、完全一致、大文字小文字指定なし、の場合です。.Findメソッドを上記のサンプルを加工すれば、あなたの仕事に合わせたマクロを作成できます。
たとえば、 .Findメソッドについては、マクロの自動記録をしながら、それなりに動くマクロを入手できます。
ぜひ試してみてください。
方法4｜動的配列
動的配列を使えば、さらに高速でデータマッチングを行うことができます。ただし、動的配列はかなりレベルが高い概念なので、習得するまでに時間がかかるでしょう。
Sub sample4() Dim tate As Long Dim rH As Range Dim st() As String Application.ScreenUpdating = False Application.Calculation = xlManual For tate = 0 To 19 ReDim Preserve st(tate) st(tate) = Range("F2").Offset(tate).Value Next For tate = 0 To UBound(st) For Each rH In Range("B2:B100001") If rH.Value = st(tate) Then rH.Offset(, 1).Value = "OK" End If Next Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub
方法5｜SQLで処理
データが増えれば増えるほど、この方法は強力です。ただし、この方法を使えるようになるには、2つのスキルが必要です。
1.アクセスのファイル形式のファイルに接続する方法
2.複数テーブルをマッチングさせるSQLを新たに習得
データ件数が多いときには、正直これが最強です。なお、PCにアクセスがインストールされている必要はありません。
ちなみに、アクセスでの処理を行うには、
( x.x の部分は、あなたのエクセルのいちばん大きい数字のものを使えばOKです )
[2] ADODB型のオブジェクトを生成し、マッチングしたい表の入ったアクセス形式のファイルに接続
[3] あらかじめアクセスのテーブルに入れてあった2つの表から、必要な情報を取り出すSQLを発行し、その結果をエクセルに出力
マクロで大量データをマッチングする仕事は、こちらの記事で詳しく紹介しています。この記事で紹介している高速化のワザと組み合わせて、あなたの仕事に活かしてみてください。
WithステートメントやVariant型はそこまで影響しない
・Withステートメントを利用する
・Variant型を利用しない
といった知識は覚える必要はありません。なぜなら、そこまでマクロの処理速度に影響しないからです。もちろん、スピードは早くなります。しかし、そこまで影響しないものまで覚えていてはキリがありません。
それよりも、改善の効果が大きいプログラムに絞って実践する方が、成果につながります。この場合でいえば、この記事で紹介した以下の内容を実践すれば、かなりマクロの速度が早くなります。
[2] Application.ScreenUpdating = False｜画面表示の更新を停止
[3] Selectを使わない
照合作業では、
[2] for each文
[3] Findメソッド
[4] 動的配列
[5] SQLの利用
細かいテクニックは覚えるのは、これらを試した後です。もし、これらの改善を行った上で、さらに処理速度を上げたいなら、以下のことも試してみてください。
・Withステートメント
・Variant型で変数宣言
何かを覚えるとき、必ず優先順位があります。効果の高いモノから試すことを意識しましょう。
以下の記事をあわせて読んでみて下さい
