【Excel VBA】大量データを配列で一括処理して高速化する方法｜セルを1件ずつ処理しない書き方
みなさんこんにちは。
Excel VBAで大量のデータを処理していると、数百件程度では問題なかった処理が、数万件になると急に遅くなった経験をしたことがある方も多いかと思います。
特に、For文の中でCellsやRangeへ1件ずつ読み書きしている処理は、データ件数が増えるほど時間がかかりやすくなります。
このような場合は、シートのデータをいったん配列へまとめて取得し、配列内で処理したあとに結果を一括出力することで、大幅に処理時間を短縮できることがあります。
今回は、Cellsを1件ずつ処理する方法と、Rangeを配列へ一括取得して処理する方法をTimerで比較しながら、Excel VBAを高速化する方法についてご紹介したいと思います。
この記事はこんな人におすすめ
- VBAで大量データを処理すると時間がかかって困っている人
- Cellsを1件ずつ処理しているコードを高速化したい人
- Rangeの値を配列へ一括取得する方法を知りたい人
- VBAの処理時間をTimerで比較してみたい人
今回比較する処理
今回は、Sheet1のA列に50,000件の数値を用意し、その値を2倍した結果をB列へ出力します。
比較する処理は、次の2種類となります。
- Cellsを使用して、A列を1件ずつ読み取りB列へ1件ずつ出力する方法
- A列をRangeから配列へ一括取得し、配列内で計算してB列へ一括出力する方法
同じ結果を作成し、処理時間だけを比較してみます。
比較用データを作成する
まずは、処理速度を比較するためのテストデータを作成します。A2セルからA50001セルまで、1から50,000までの数値を入力します。
Sub CreateTestData()
Dim ws As Worksheet
Dim iRow As Long
'========================================
' テストデータを作成するシートを取得する
'========================================
Set ws = ThisWorkbook.Worksheets("Sheet1")
'========================================
' 既存データを削除する
'========================================
ws.Range("A:B").ClearContents
'見出しを設定する
ws.Range("A1").Value = "元データ"
ws.Range("B1").Value = "計算結果"
'========================================
' A2～A50001へ1～50000の数値を入力する
'========================================
For iRow = 2 To 50001
ws.Cells(iRow, 1).Value = iRow - 1
Next iRow
MsgBox "テストデータの作成が完了しました。", vbInformation
End Sub
このコードを実行すると、A列へ50,000件のテストデータが作成されます。
ここから同じデータを使用して、2種類の処理速度を比較します。
Cellsを1件ずつ処理する方法
1行ずつ読み書きするコード
まずは、For文の中でCellsを使用し、1行ずつ値を読み取りながらB列へ出力する方法です。
Sub SlowProcess()
Dim ws As Worksheet
Dim iRow As Long
Dim startTime As Single
Dim elapsedTime As Single
'========================================
' 処理対象となるシートを取得する
'========================================
Set ws = ThisWorkbook.Worksheets("Sheet1")
'前回の計算結果を削除する
ws.Range("B2:B50001").ClearContents
'========================================
' 処理開始時刻を取得する
'========================================
startTime = Timer
'========================================
' A列の値を1件ずつ取得して2倍し、
' B列へ1件ずつ書き込む
'========================================
For iRow = 2 To 50001
ws.Cells(iRow, 2).Value = ws.Cells(iRow, 1).Value * 2
Next iRow
'========================================
' 処理時間を計算する
'========================================
elapsedTime = Timer - startTime
'処理結果を表示する
MsgBox "Cellsで1件ずつ処理した時間：" & _
Format(elapsedTime, "0.000") & " 秒", _
vbInformation
End Sub
この方法でも正しく処理できますが、ループするたびにExcelシートへアクセスしています。
Excel VBAでは、ワークシートとVBAの間で値を何度も読み書きすると、その分だけ処理時間が増えやすくなります。
Rangeを配列へ一括取得して高速化する
Rangeの値をVariant配列へ取得する
次は、A2:A50001の値を一度に配列へ取得します。
'A2:A50001の値を配列へ一括取得する
inputData = ws.Range("A2:A50001").Value
複数セルのRangeをVariant型の変数へ代入すると、2次元配列として値をまとめて取得できます。
この状態であれば、計算中にワークシートへ何度もアクセスする必要がありません。
配列内で処理して一括出力する
取得した配列内で計算を行い、結果も配列へ保存します。最後にB2:B50001へまとめて出力します。
Sub FastProcess()
Dim ws As Worksheet
Dim inputData As Variant
Dim outputData() As Variant
Dim i As Long
Dim startTime As Single
Dim elapsedTime As Single
'========================================
' 処理対象となるシートを取得する
'========================================
Set ws = ThisWorkbook.Worksheets("Sheet1")
'前回の計算結果を削除する
ws.Range("B2:B50001").ClearContents
'========================================
' 処理開始時刻を取得する
'========================================
startTime = Timer
'========================================
' A列の50,000件を配列へ一括取得する
'========================================
inputData = ws.Range("A2:A50001").Value
'取得したデータと同じ件数の出力用配列を用意する
ReDim outputData(1 To UBound(inputData, 1), 1 To 1)
'========================================
' ワークシートへアクセスせず、
' 配列内だけで計算する
'========================================
For i = 1 To UBound(inputData, 1)
outputData(i, 1) = inputData(i, 1) * 2
Next i
'========================================
' 計算結果をB列へ一括出力する
'========================================
ws.Range("B2:B50001").Value = outputData
'========================================
' 処理時間を計算する
'========================================
elapsedTime = Timer - startTime
'処理結果を表示する
MsgBox "配列で一括処理した時間：" & _
Format(elapsedTime, "0.000") & " 秒", _
vbInformation
End Sub
処理の考え方は、次のようになります。
Rangeから一括取得 → 配列内で計算 → Rangeへ一括出力
For文自体は使用していますが、ループ中にセルへアクセスしていないところがポイントとなります。
Timerで処理時間を比較する
Timer関数で経過時間を計測する
今回のサンプルでは、処理開始前と処理終了後にTimer関数を使用しています。
'処理開始時刻を取得する
startTime = Timer
'ここで処理を実行する
'経過時間を計算する
elapsedTime = Timer - startTime
Timerは、午前0時から経過した秒数を返すため、開始時と終了時の差を計算することで処理時間を確認できます。
実行結果はパソコン環境によって変わる
実際の処理時間は、パソコンの性能やExcelの状態、ほかの処理内容によって変わります。
そのため、「必ず何倍速くなる」とは言えませんが、データ件数が多く、セルへの読み書きが多い処理ほど、配列を使用した一括処理の効果を確認しやすくなります。
記事へ掲載する際は、ご自身の環境で実行したTimerの結果を比較していただければと思います。
配列を使うときのポイント
セルへのアクセス回数を減らす
今回の高速化で大切なのは、単純に「配列を使うこと」ではなく、Excelシートへのアクセス回数を減らすことです。
大量データを扱う場合は、必要なRangeをまとめて配列へ読み込み、処理が終わったら結果をまとめて書き戻す方法を検討してみてください。
小さいデータでは無理に配列化しなくてもよい
数件から数十件程度の小さな処理であれば、Cellsを1件ずつ処理しても体感差がほとんどないことがあります。
コードの分かりやすさも大切なので、データ量や処理時間を確認しながら使い分けるのがおすすめです。
過去の記事のご紹介
VBAの処理速度を改善する方法として、画面更新・自動計算・イベントを一時的に停止する方法もあります。
今回の配列処理と組み合わせることで、さらに処理時間を短縮できるケースもあります。
最後に
いかがでしたでしょうか。
今回は、Excel VBAで大量データを配列へ一括取得し、配列内で処理してからRangeへ一括出力する方法についてご紹介しました。
Cellsを1件ずつ読み書きする方法でも処理できますが、データ件数が増えるとワークシートへのアクセス回数も増えてしまいます。
大量データを扱う場合は、Range → 配列 → 配列内で処理 → Rangeへ一括出力という流れに変更できないか確認してみてください。
VBAの処理速度で困っている方に、少しでも参考になれば幸いです。
開発依頼について
ココナラでWebスクレイピング開発サービスを出品しております。
自分で開発をしようと思ったけど、VBAでのネット記事が少なく困っている方も多いかと思います。
そんな方はいつでもお気軽にご相談ください。
また、本ブログからご依頼いただいた方については割引特典がございますので、
ご不明点と合わせてメッセージをいただけると幸いです。
Excelにてブラウザ操作自動化ツールを作成します その作業、webスクレイピングを使って自動化しましょう！Webスクレイピング以外にもWebアプリの開発サービスも出品しております。
こちらについても、本ブログからご依頼いただいた方については割引特典がございますので、
お気軽にご相談ください。
スモールスケールのWebアプリ開発します 先ずはスモールスケールのWebアプリから始めませんか？
