Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 数百万行対応テンプレ

Excel VBA Excel VBA
スポンサーリンク

ねらい：Excelでも「数百万行」を“落ちない・待てる・再利用できる”型で扱う

「CSVを開いたら固まった」「10万行超えたあたりからマクロが全然終わらない」
数万行までは“なんとなく書いたVBA”でも動きますが、数十万〜数百万行になると一気に限界が見えてきます。

数百万行対応テンプレのゴールは、こうです。
シートを直接ガチャガチャ触る書き方をやめて、「配列・バッファ・辞書」を前提にした“数百万行用の型”を持つこと。
そして、その型を一度作っておけば、JOIN・集計・差分・クリーニングなど、どの処理にも流用できるようにすることです。

ここから、「なぜ遅くなるのか」「どう書けば速く・安定して動くのか」を、初心者向けにかみ砕いてテンプレ化していきます。

数百万行で“絶対にやってはいけない”書き方

セルを1つずつ触るループ

典型的な遅いコードがこれです。

Dim i As Long
For i = 2 To lastRow
    If Cells(i, 1).Value = "A" Then
        Cells(i, 2).Value = "OK"
    End If
Next i
VB

数千行ならまだしも、数十万行〜数百万行になると、
「セルへの読み書き」がボトルネックになって、ほぼ動かなくなります。

理由はシンプルで、
セルを1つ読むたびに「Excel本体とのやり取り」が発生するからです。
これを数十万回・数百万回繰り返すと、VBAの処理ではなく「Excelとの通信」が詰まります。

数百万行対応テンプレでは、「セルを1つずつ触る」ことをやめて、
「一気に配列に読み込んで、配列の中だけで処理して、最後に一気に書き戻す」型に切り替えます。

Select / Activate / Offset 連発

もう一つの典型例が、こういう書き方です。

Range("A2").Select
Do While ActiveCell.Value <> ""
    If ActiveCell.Value = "A" Then
        ActiveCell.Offset(0, 1).Value = "OK"
    End If
    ActiveCell.Offset(1, 0).Select
Loop
VB

Select や Activate は、画面上の「選択状態」を変える命令です。
数百万行でこれをやると、画面更新と内部状態の切り替えで、あっという間に死にます。

数百万行対応テンプレでは、「Select/Activate禁止」を前提にします。
必要なのは「どのセルかを指す Range 変数」であって、「画面上で選択されているかどうか」ではありません。

基本方針：配列・辞書・バッファで“Excelの外側”で処理する

一括読み込み → メモリ内で処理 → 一括書き戻し

数百万行対応の基本パターンは、次の三段階です。

シートのデータを、Variant配列に一括で読み込む
配列の中で、ループ・条件分岐・集計・JOINなどを行う
結果を、必要な範囲に一括で書き戻す

このパターンにすると、「Excelとのやり取り」は
読み込み時の1回と、書き戻し時の1回だけになります。
あとはVBAのメモリの中だけで完結するので、桁違いに速くなります。

コアテンプレ：シート→配列→シートの骨格

まずは、どの処理でも使える「基本の骨格」を押さえましょう。

Sub Template_ArrayProcess()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    Dim lastRow As Long, lastCol As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    Dim src As Variant
    src = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Value  ' 一括読み込み
    
    Dim i As Long
    Dim rCount As Long
    rCount = UBound(src, 1)
    
    ' ここから先は「src配列の中だけ」で処理する
    For i = 2 To rCount
        ' src(i, 1) や src(i, 2) を使って処理
    Next i
    
    ' 必要なら、結果を別配列 dst に作ってから書き戻す
    ' ws.Range("G1").Resize(UBound(dst, 1), UBound(dst, 2)).Value = dst
End Sub
VB

ポイントは、Range.Value を配列に受けるとき、
「二次元の Variant 配列」として一気に入る、というところです。

src(行, 列) という形でアクセスできるので、
Cells(i, j).Value の代わりに src(i, j) を使えば、
Excelとの通信なしで高速に処理できます。

コア部品1：数百万行対応の「条件付き書き換え」テンプレ

例題：フラグ列を付ける処理

Data シートに、次のようなデータがあるとします。

A列：顧客コード
B列：売上金額

売上が 100,000 以上なら「優良」、それ以外は「通常」というフラグを C列に付けたい。
これを数十万行〜数百万行でやるときのテンプレです。

配列での実装

Sub FlagCustomerBySales()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim src As Variant
    src = ws.Range("A1:B" & lastRow).Value   ' A,B列を一括読み込み
    
    Dim rCount As Long
    rCount = UBound(src, 1)
    
    Dim dst() As Variant
    ReDim dst(1 To rCount, 1 To 1)          ' C列用の1列配列
    
    Dim i As Long
    dst(1, 1) = "フラグ"                     ' 見出し
    
    For i = 2 To rCount
        If IsNumeric(src(i, 2)) Then
            If src(i, 2) >= 100000 Then
                dst(i, 1) = "優良"
            Else
                dst(i, 1) = "通常"
            End If
        Else
            dst(i, 1) = ""
        End If
    Next i
    
    ws.Range("C1").Resize(rCount, 1).Value = dst   ' 一括書き戻し
End Sub
VB

ここでの重要ポイントを整理します。

読み込みは A:B の2列だけに絞る（不要な列は読まない）
結果用の dst 配列を別に用意し、そこにだけ書き込む
最後に C列に一括で書き戻す

このパターンは、「元データは触らず、結果だけ別列に出す」処理にとても向いています。

コア部品2：数百万行対応の「集計」テンプレ（辞書＋配列）

例題：顧客別売上集計

Data シートに、次のような明細があるとします。

A列：顧客コード
B列：売上金額

これを「顧客コードごとの合計売上」に集計したい。
ピボットテーブルでもできますが、数百万行でVBAから柔軟に扱いたいときのテンプレです。

辞書を使った集計

Sub AggregateSalesByCustomer()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim src As Variant
    src = ws.Range("A2:B" & lastRow).Value   ' 見出しを除いて読み込み
    
    Dim rCount As Long
    rCount = UBound(src, 1)
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim i As Long
    For i = 1 To rCount
        Dim key As String
        key = CStr(src(i, 1))
        
        Dim val As Double
        If IsNumeric(src(i, 2)) Then
            val = CDbl(src(i, 2))
        Else
            val = 0
        End If
        
        If dict.Exists(key) Then
            dict(key) = dict(key) + val
        Else
            dict.Add key, val
        End If
    Next i
    
    Dim outCount As Long
    outCount = dict.Count
    
    Dim dst() As Variant
    ReDim dst(0 To outCount, 1 To 2)
    
    dst(0, 1) = "顧客コード"
    dst(0, 2) = "合計売上"
    
    Dim idx As Long
    idx = 1
    
    Dim k As Variant
    For Each k In dict.Keys
        dst(idx, 1) = k
        dst(idx, 2) = dict(k)
        idx = idx + 1
    Next k
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = ThisWorkbook.Worksheets("AggCustomer")
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = "AggCustomer"
    End If
    
    wsOut.Cells.Clear
    wsOut.Range("A1").Resize(outCount + 1, 2).Value = dst
End Sub
VB

ここでの“数百万行対応”的なポイントは、次の通りです。

明細は配列に一括読み込み
集計は Dictionary の中だけで完結
結果は配列に詰めて一括書き戻し

顧客数が数万件程度なら、数百万行の明細でも十分現実的な速度で動きます。

コア部品3：数百万行対応の「フィルタ＋抽出」テンプレ（配列フィルタ）

例題：売上が 100,000 以上の行だけを別シートに抽出

Data シートの A:B 列から、「売上が 100,000 以上」の行だけを抜き出して、
HighSales シートに出したい、という例です。

可変長配列での抽出

Sub ExtractHighSales()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim src As Variant
    src = ws.Range("A1:B" & lastRow).Value   ' 見出し込みで読み込み
    
    Dim rCount As Long
    rCount = UBound(src, 1)
    
    Dim dst() As Variant
    ReDim dst(1 To rCount, 1 To 2)          ' 最大行数で確保しておく
    
    dst(1, 1) = src(1, 1)
    dst(1, 2) = src(1, 2)
    
    Dim outRow As Long
    outRow = 2
    
    Dim i As Long
    For i = 2 To rCount
        If IsNumeric(src(i, 2)) Then
            If src(i, 2) >= 100000 Then
                dst(outRow, 1) = src(i, 1)
                dst(outRow, 2) = src(i, 2)
                outRow = outRow + 1
            End If
        End If
    Next i
    
    Dim wsOut As Worksheet
    On Error Resume Next
    Set wsOut = ThisWorkbook.Worksheets("HighSales")
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = "HighSales"
    End If
    
    wsOut.Cells.Clear
    wsOut.Range("A1").Resize(outRow - 1, 2).Value = dst
End Sub
VB

ここでは、「最初に最大行数で配列を確保しておき、実際に使った分だけ書き戻す」という型を使っています。
ReDim Preserve をループの中で何度も使うと遅くなるので、
数百万行対応では「最初に大きめに取って、最後に“使った分だけ”出す」方が安定します。

数百万行対応の“お約束”テンプレ（高速化ラッパ＋エラー・ログ）

高速化ラッパは必ずセットで

数百万行を扱うときは、画面更新や再計算も大きな負担になります。
なので、処理の前後で必ず「高速化ラッパ」を挟むテンプレを使います。

Sub SpeedOn()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Sub SpeedOff()
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB

各メイン処理では、こう使います。

Sub RunHeavyProcess()
    Const MODULE_NAME As String = "RunHeavyProcess"
    On Error GoTo ErrHandler
    
    SpeedOn
    LogStart MODULE_NAME, "重い処理開始"
    
    ' 数百万行処理本体
    
    LogEnd MODULE_NAME, "重い処理終了"
    SpeedOff
    Exit Sub
    
ErrHandler:
    SpeedOff
    LogError MODULE_NAME, "MAIN", Err
End Sub
VB

「SpeedOn/SpeedOff＋ログ＋配列処理」のセットが、数百万行対応テンプレの“基本形”になります。

もう一歩踏み込んだ話：メモリと分割処理の考え方

配列のサイズとメモリ

数百万行×数十列を一気に配列に読み込むと、
PCのメモリによっては厳しくなることがあります。

例えば、100万行×10列の配列は、
ざっくり「100万×10×16バイト（Variantの中身＋オーバーヘッド）」くらいのオーダーになります。
実際にはもう少し複雑ですが、「数百万行×数十列」を一気に読むのは避けた方が安全です。

現実的な対策としては、次のようなものがあります。

列数を絞る（本当に必要な列だけ読む）
行をチャンク（塊）に分けて処理する

チャンク処理の簡単なテンプレ

例えば、「100万行を20万行ずつ5回に分けて処理する」イメージです。

Sub ProcessInChunks()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Data")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim chunkSize As Long
    chunkSize = 200000
    
    Dim startRow As Long
    startRow = 2
    
    Do While startRow <= lastRow
        Dim endRow As Long
        endRow = startRow + chunkSize - 1
        If endRow > lastRow Then endRow = lastRow
        
        Dim src As Variant
        src = ws.Range("A" & startRow & ":B" & endRow).Value
        
        ' src配列に対して処理
        
        ' 必要なら結果を書き戻し
        
        startRow = endRow + 1
    Loop
End Sub
VB

この型を使うと、「一度に抱える行数」を制御できるので、
メモリの心配を減らしつつ、数百万行を順番に処理できます。

まとめ：数百万行対応テンプレは「配列・辞書・バッファ前提の書き方」に切り替えること

今日のポイントをぎゅっとまとめると、こうなります。

セルを1つずつ触るループや Select/Activate をやめる。
Range.Value を配列に一括読み込みし、配列の中だけで処理する。
集計やJOINには Dictionary を使い、結果は配列に詰めて一括書き戻しする。
高速化ラッパ（ScreenUpdating/Calculation）とログをセットで使う。
必要に応じて、列数を絞る・チャンク処理で行数を分割する。

この“数百万行用の型”さえ一度身につけてしまえば、
今まで「10万行で限界だったマクロ」が、
「数十万〜数百万行でも現実的な時間で終わるマクロ」に変わっていきます。

スポンサーリンク
タイトルとURLをコピーしました