【Excel】VBAで配列を使ってデータを高速処理する方法

Excel VBAで大量のデータを扱う際、処理速度に悩んだ経験はありませんか。

シート上のセルを一つずつ処理するのは、データ量が多いほど時間がかかり、非効率です。

この記事では、VBAで「配列」を活用し、データ処理を劇的に高速化する方法を解説します。

シート操作を最小限に抑え、メモリ上でデータを一括処理するテクニックを習得しましょう。

なぜVBA配列が高速処理を実現するのか

Excel VBAで大量のデータを処理する際、最も時間がかかる処理は、Excelシートへのアクセスです。

セルを一つずつ読み込んだり書き込んだりする操作は、Excelアプリケーションの内部処理を介するため、どうしても遅延が発生します。

特に、数千行、数万行といったデータになると、その差は顕著になります。

一方、「配列」は、VBAのプログラム内で直接扱えるメモリ上のデータ領域です。

配列変数は、Excelシートという外部アプリケーションを介さずに直接操作できるため、読み書きの速度が格段に速くなります。

この「シートアクセスを最小限にし、配列上で集中的に処理を行う」という考え方が、VBAによる高速データ処理の鍵となります。

配列を使ったデータ処理の基本的な流れ

VBAで配列を活用したデータ処理は、一般的に以下の3つのステップで構成されます。

ステップ1:シートデータを配列変数に読み込む

まず、処理対象となるExcelシート上のデータを、VBAの配列変数に読み込みます。

この際、シート全体または必要な範囲のデータを一度に配列変数へ代入することで、シートへのアクセス回数を大幅に削減できます。

この操作により、以降のデータ参照はすべてメモリ上で行われるようになります。

ステップ2:配列変数内でデータを処理する

配列変数に読み込んだデータに対して、必要な加工や集計などの処理をVBAコードで記述します。

このフェーズでは、Excelシートへのアクセスは一切行われません。

配列の要素をループで巡回しながら、条件分岐や計算などを高速に実行します。

配列の添え字(インデックス)を使って直接データにアクセスするため、処理速度は非常に速いです。

ステップ3:処理済みの配列データをシートに書き戻す

配列変数内でのデータ処理が完了したら、加工・集計された結果をExcelシートに書き戻します。

これも、一度に配列全体をシートの範囲に代入する形で行うのが効率的です。

これにより、シートへの書き込み回数も最小限に抑えられます。

具体的なVBAコード例:売上データの集計

ここでは、架空の売上データ(A列:商品名、B列:売上金額)を例に、商品ごとの合計売上を計算するVBAコードを紹介します。

シートには、例えば10,000行の売上データが入力されていると想定します。

サンプルコードの解説

以下のコードは、シートからデータを配列に読み込み、商品名ごとの売上を集計し、結果を別のシートに書き出す一連の流れを示しています。

1. 変数宣言と初期設定
   必要な変数を宣言し、定数などを設定します。`Option Explicit` は、変数の宣言漏れを防ぐために必ず記述しましょう。
2. データ範囲の取得
   処理対象となるシートの最終行を取得し、データ範囲を特定します。
3. 配列へのデータ読み込み
   `Range.Value` プロパティを使って、シートのデータを二次元配列変数 `vData` に一括で読み込みます。
4. 集計用配列の準備
   集計結果を格納するための配列変数 `vResult` を宣言します。ここでは、商品名と合計売上の2列を持つ配列とします。
5. 配列内での集計処理
   `vData` をループで巡回し、商品名ごとに `vResult` の該当する行の売上金額を加算していきます。
6. 結果のシートへの書き戻し
   集計が完了した `vResult` 配列の内容を、結果表示用のシートに一括で書き戻します。

サンプルVBAコード

以下のコードをExcel VBAエディタに貼り付けて実行してください。

「Sheet1」に元データがあり、「Sheet2」に集計結果を出力します。必要に応じてシート名を変更してください。

Option Explicit

Sub ProcessSalesDataWithArray()

    Dim wsSource As Worksheet       ' 元データシート
    Dim wsDest As Worksheet         ' 結果出力シート
    Dim lastRow As Long             ' 元データの最終行
    Dim vData As Variant            ' 元データ格納用配列
    Dim vResult As Variant          ' 集計結果格納用配列
    Dim i As Long                   ' ループカウンター (元データ)
    Dim j As Long                   ' ループカウンター (集計結果)
    Dim foundRow As Boolean         ' 該当商品が見つかったかフラグ
    Dim itemCount As Long           ' 集計結果の行数
    
    ' --- 初期設定 ---
    On Error GoTo ErrorHandler
    Application.ScreenUpdating = False ' 画面更新を停止 (高速化)
    Application.Calculation = xlCalculationManual ' 計算方法をマニュアルに (高速化)
    
    ' 対象シートの設定
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsDest = ThisWorkbook.Sheets("Sheet2")
    
    ' --- ステップ1: シートデータを配列変数に読み込む ---
    ' 元データの最終行を取得
    lastRow = wsSource.Cells(Rows.Count, "A").End(xlUp).Row
    
    ' データが1行もない場合は処理を終了
    If lastRow < 1 Then
        MsgBox "元データがありません。", vbInformation
        GoTo CleanExit
    End If
    
    ' シートのデータを二次元配列に読み込む (A1からB列最終行まで)
    ' 配列は0から始まるのではなく、シートの範囲に合わせて1から始まる
    vData = wsSource.Range("A1:B" & lastRow).Value
    
    ' --- ステップ2: 配列変数内でデータを処理する ---
    ' 集計結果用配列の初期サイズを仮に設定 (後でRedimで調整)
    ' 商品名と合計売上の2列
    ReDim vResult(1 To 1, 1 To 2)
    itemCount = 0
    
    ' 元データ配列をループ処理
    For i = 1 To UBound(vData, 1) ' 1行目から最終行まで
        foundRow = False
        ' 集計結果配列をループして、同じ商品名があるか探す
        For j = 1 To itemCount
            If vResult(j, 1) = vData(i, 1) Then ' 商品名が一致した場合
                vResult(j, 2) = vResult(j, 2) + vData(i, 2) ' 売上金額を加算
                foundRow = True
                Exit For ' 見つかったら内側のループを抜ける
            End If
        Next j
        
        ' 集計結果配列にまだない商品名の場合
        If Not foundRow Then
            itemCount = itemCount + 1 ' 集計結果の行数を増やす
            ' 配列のサイズを拡張 (Redim Preserveは末尾への追加のみ)
            ' ここでは、毎回Redim Preserveで拡張するより、
            ' 処理後に不要な部分を削除する方が効率的な場合もあるが、
            ' 例として、動的にRedim Preserveを使う方法を示す。
            ' より効率的な方法としては、最大想定行数でRedimしておき、
            ' 最後に不要部分を削除するか、Dictionaryオブジェクトを使う方法がある。
            ReDim Preserve vResult(1 To itemCount, 1 To 2)
            vResult(itemCount, 1) = vData(i, 1) ' 商品名を格納
            vResult(itemCount, 2) = vData(i, 2) ' 売上金額を格納
        End If
    Next i
    
    ' --- ステップ3: 処理済みの配列データをシートに書き戻す ---
    ' 集計結果シートをクリア
    wsDest.Cells.ClearContents
    
    ' ヘッダーを書き込む
    wsDest.Range("A1").Value = "商品名"
    wsDest.Range("B1").Value = "合計売上"
    
    ' 集計結果配列をシートに一括で書き込む
    ' vResult配列のサイズは (1 To itemCount, 1 To 2) なので、
    ' 書き込み範囲は A2 から始まる。
    If itemCount > 0 Then
        wsDest.Range("A2").Resize(itemCount, 2).Value = vResult
    End If
    
    ' 列幅を自動調整
    wsDest.Columns("A:B").AutoFit
    
    MsgBox "売上データの集計が完了しました。", vbInformation

CleanExit:
    ' --- 後処理 ---
    Application.ScreenUpdating = True ' 画面更新を再開
    Application.Calculation = xlCalculationAutomatic ' 計算方法を自動に戻す
    Set wsSource = Nothing
    Set wsDest = Nothing
    Exit Sub

ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    GoTo CleanExit

End Sub

コードのポイント解説

このサンプルコードは、配列処理の基本的な流れを理解するためのものです。

特に以下の点に注目してください。

• `vData = wsSource.Range(“A1:B” & lastRow).Value`: この一行で、シート上のA1からB列の最終行までの全データを `vData` という二次元配列変数に読み込んでいます。これは非常に高速な操作です。
• `For i = 1 To UBound(vData, 1)`: 配列の要素をループで処理する際の基本的な書き方です。`UBound(配列名, 次元数)` で配列の最大添え字を取得できます。
• `ReDim Preserve vResult(1 To itemCount, 1 To 2)`: 集計結果を格納する `vResult` 配列は、処理を進めるにつれて行数が増える可能性があります。`ReDim Preserve` を使うことで、既存のデータを保持したまま配列のサイズを拡張できます。ただし、`Preserve` は最後の次元のみ拡張可能なので、この例では1次元目のサイズを拡張しています。
• `wsDest.Range(“A2”).Resize(itemCount, 2).Value = vResult`: 処理済みの `vResult` 配列の内容を、結果シートのA2セルから始まる範囲に一括で書き込んでいます。これもシートアクセスを最小限にするための重要なポイントです。
• `Application.ScreenUpdating = False` と `Application.Calculation = xlCalculationManual`: これらの設定は、VBAコード実行中の画面更新やExcelの自動計算を一時的に停止させることで、処理速度を向上させます。処理完了後に元に戻すことを忘れないでください。

配列処理でよくある誤解と注意点

配列を使った高速処理は強力ですが、いくつか注意すべき点があります。

h3: 配列の添え字(インデックス)の開始位置

Excelシートのセル参照は通常1から始まりますが、VBAで宣言した配列変数の添え字は、デフォルトで0から始まります。

しかし、`Range.Value` でシートから配列に読み込んだ場合、配列はシートの範囲に合わせて1から始まる多次元配列として扱われます。

そのため、`vData = wsSource.Range(“A1:B” & lastRow).Value` のようにシートの範囲を直接代入した場合、`vData(1, 1)` がA1セルの値、`vData(2, 1)` がA2セルの値となります。

一方、`Dim myArray(10)` のように明示的に宣言した配列は、デフォルトで0から10までの11要素になります。これを1から10の10要素として扱いたい場合は、`Dim myArray(1 To 10)` のように範囲を指定して宣言する必要があります。

この添え字の開始位置を間違えると、意図したデータにアクセスできなかったり、エラーが発生したりするため注意が必要です。

h3: `ReDim Preserve` の制限事項

`ReDim Preserve` は、配列のサイズを変更する際に、既存のデータを保持するための便利な機能です。

しかし、`Preserve` を使用できるのは、配列の最後の次元のサイズ変更のみです。多次元配列の場合、最後の次元以外のサイズを変更したい場合は、`Preserve` を使わず、新しいサイズの配列を宣言し、元の配列のデータをコピーし直す必要があります。

この `ReDim Preserve` をループ内で頻繁に実行すると、その都度メモリの再確保とデータコピーが発生するため、かえって処理が遅くなることがあります。

そのため、配列のサイズが事前に予測できない場合でも、ある程度余裕を持ったサイズで一度宣言しておき、処理後に不要な部分を削除する、あるいは `Dictionary` オブジェクトなどの別のデータ構造を利用する方が効率的な場合があります。

h3: メモリ使用量への配慮

配列はメモリ上にデータを保持するため、非常に大きなデータを配列として扱う場合、PCのメモリを大量に消費する可能性があります。

特に、数百万行といった極端に大量のデータを一度に配列に読み込もうとすると、メモリ不足エラーが発生したり、PC全体の動作が不安定になったりすることがあります。

このような場合は、データを分割して処理する、あるいは `ADOX` や `DAO` といったデータベースアクセス機能を利用して、Excelファイルをデータベースのように扱うなどの工夫が必要になります。

h3: データ型の一貫性

配列にデータを読み込む際、Excelシート上のデータ型が混在していると、意図しない型変換が発生することがあります。

例えば、数値と文字列が混在する列を配列に読み込むと、VBAでは「バリアント型」として扱われますが、その後の処理で意図しない結果を招くことがあります。

必要に応じて、配列に読み込む前にシート上のデータを整形しておくか、VBAコード内で明示的に型変換を行うようにしましょう。

配列処理をさらに効率化するテクニック

基本的な配列処理に慣れてきたら、さらに処理速度を向上させるためのテクニックも検討しましょう。

h3: Dictionaryオブジェクトの活用

キーと値のペアでデータを管理する `Scripting.Dictionary` オブジェクトは、配列処理と組み合わせて使うと非常に強力です。

例えば、今回の売上集計の例では、`vResult` 配列を `Dictionary` オブジェクトに置き換えることで、商品名の検索と値の加算をより効率的に行えます。

`Dictionary` オブジェクトは、キー(ここでは商品名)が存在するかどうかの判定や、値の取得・追加が非常に高速です。

ループ内で `Dictionary` を使用し、処理後に `Dictionary` の内容を配列に変換してシートに書き戻す、という流れが一般的です。

h3: Filter関数やFindメソッドの活用

配列内の特定のデータを検索する際に、単純なループ処理よりもExcelの組み込み関数やメソッドを活用する方が効率的な場合があります。

例えば、配列に読み込んだデータに対して、特定の条件に合致する行を抽出したい場合、VBAの `Filter` 関数(文字列配列を対象)や、`WorksheetFunction.CountIf`、`WorksheetFunction.SumIf` などのExcel関数を配列に対して実行する方法があります。

また、`Range.Find` メソッドを配列に読み込んだデータに対して直接適用することはできませんが、配列の要素を一時的にシートの範囲に書き戻してから `Find` メソッドで検索し、その結果を元に配列の該当要素を処理するという間接的な方法も考えられます。

h3: 2次元配列の操作テクニック

Excelのデータは通常2次元(行と列)で構成されるため、VBAでも2次元配列を扱う機会が多くなります。

2次元配列のループ処理では、外側のループで行を、内側のループで列を処理するのが基本ですが、処理内容によっては列を先にループする方が効率的な場合もあります。

また、配列の特定の部分だけを別の配列変数にコピーしたい場合、単純なループ処理だけでなく、`Application.WorksheetFunction.Transpose`(行と列を入れ替える)や、`Application.Index` と `Application.Match` を組み合わせるなどのテクニックも有効です。

まとめ

この記事では、Excel VBAで配列を活用してデータ処理を高速化する方法を解説しました。

シートアクセスを最小限にし、メモリ上の配列で一括処理を行うことで、大量のデータ処理にかかる時間を劇的に短縮できます。

まずは基本的な「シート→配列→処理→配列→シート」の流れを理解し、サンプルコードを参考に実践してみてください。

さらに、`Dictionary` オブジェクトの活用や、2次元配列の効率的な操作テクニックを習得することで、より高度な高速処理が可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】