Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – 辞書×配列の最適化テンプレ

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

ねらい：辞書と配列を「なんとなく」ではなく「型」として使いこなす

辞書（Scripting.Dictionary）と配列は、VBAで高速化するときの主役です。
でも、なんとなく使うと「速いときもあるけど、たまに遅い・読みにくい・バグりやすい」状態になりがちです。

辞書×配列の最適化テンプレのゴールは、こうです。
「このパターンで書けば、だいたい速くて、読みやすくて、再利用しやすい」という“型”を持つこと。
特に、JOIN・集計・検索・マスタ参照のような処理を、安定して書けるようにします。

基本パターン：シート → 配列 → 辞書 → 配列 → シート

全体の流れを一枚の絵として持つ

辞書×配列の最適化は、だいたい次の流れに収まります。

シートから、必要な範囲だけを配列に一括読み込みする
配列をループしながら、辞書に「キー→値」を詰める（または辞書を参照する）
結果を別の配列に組み立てる
最後に、その配列をシートに一括で書き戻す

この「Excelと話すのは最初と最後だけ」「中身は全部メモリ内で完結」という構造が、
速さと安定性の源です。

例題1：辞書×配列で「マスタ参照JOIN」を最適化する

やりたいことのイメージ

明細シート Data にこういう列があるとします。

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

マスタシート Customer にこういう列があるとします。

A列：顧客コード
B列：顧客名

やりたいことは、「Data に顧客名を付ける」＝顧客コードでマスタ参照するJOINです。
これを辞書×配列で最適化します。

マスタを辞書に載せるテンプレ

まず、「顧客コード→顧客名」の辞書を作る関数を用意します。

Function BuildCustomerDict() As Object
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Customer")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then
        Set BuildCustomerDict = Nothing
        Exit Function
    End If
    
    Dim src As Variant
    src = ws.Range("A2:B" & lastRow).Value   ' 見出し除く
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare         ' 大文字小文字を区別しない場合
    
    Dim i As Long
    For i = 1 To UBound(src, 1)
        Dim key As String
        key = CStr(src(i, 1))
        
        If key <> "" Then
            If Not dict.Exists(key) Then
                dict.Add key, CStr(src(i, 2))
            End If
        End If
    Next i
    
    Set BuildCustomerDict = dict
End Function
VB

ここでの最適化ポイントを押さえます。

一度だけ配列に読み込んでから辞書を作る（Cellsで1行ずつ読まない）
CompareMode を設定しておくと、後の検索が安定する
重複キーは「最初の1件だけ採用」など、ルールを決めておく

辞書を作る処理は、JOIN以外でも何度も使うので、必ず関数化しておきます。

明細側に顧客名を付けるテンプレ

次に、Data シートに顧客名を付ける処理です。

Sub JoinCustomerName()
    Dim dict As Object
    Set dict = BuildCustomerDict()
    If dict Is Nothing Then
        MsgBox "Customerマスタが空です。", vbExclamation
        Exit Sub
    End If
    
    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)
    
    dst(1, 1) = "顧客名"
    
    Dim i As Long
    For i = 2 To rCount
        Dim key As String
        key = CStr(src(i, 1))
        
        If dict.Exists(key) Then
            dst(i, 1) = dict(key)
        Else
            dst(i, 1) = ""   ' 見つからない場合の扱いはルール化しておく
        End If
    Next i
    
    ws.Range("C1").Resize(rCount, 1).Value = dst
End Sub
VB

ここでの辞書×配列の最適化ポイントは、こうです。

マスタ側は一度だけ辞書化し、明細側は配列でループしながら辞書を参照する
結果は別配列にためてから一括書き戻しする
辞書の Exists→Item のセットを1回のループで完結させる

このパターンは、「マスタ参照JOIN」の基本テンプレとして、そのまま使い回せます。

例題2：辞書×配列で「高速集計」を最適化する

やりたいことのイメージ

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

A列：商品コード
B列：売上金額

これを「商品コードごとの合計売上」に集計したい。
ピボットではなく、VBAで柔軟に扱いたいときのテンプレです。

辞書で集計し、配列で出力するテンプレ

Sub AggregateSalesByItem()
    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")
    dict.CompareMode = vbTextCompare
    
    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 n As Long
    n = dict.Count
    
    Dim dst() As Variant
    ReDim dst(0 To n, 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("AggItem")
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = "AggItem"
    End If
    
    wsOut.Cells.Clear
    wsOut.Range("A1").Resize(n + 1, 2).Value = dst
End Sub
VB

ここでの最適化ポイントは、次の通りです。

集計対象は配列に一括読み込み
集計は辞書の中だけで完結（シートに触らない）
結果は配列に詰めて一括書き戻し

「辞書＝集計用の箱」「配列＝入出力の器」として役割を分けると、コードの見通しが良くなります。

例題3：辞書×配列で「差分検出」を最適化する

やりたいことのイメージ

Old シートと New シートに、同じ構造のデータがあるとします。

両方とも A列：顧客コード、B列：顧客名。
やりたいことは、「NewにあってOldにない顧客（追加分）」を抽出することです。

Oldを辞書化して、New配列を走査するテンプレ

Function BuildKeyDict(ByVal ws As Worksheet) As Object
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then
        Set BuildKeyDict = Nothing
        Exit Function
    End If
    
    Dim src As Variant
    src = ws.Range("A2:A" & lastRow).Value
    
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim i As Long
    For i = 1 To UBound(src, 1)
        Dim key As String
        key = CStr(src(i, 1))
        If key <> "" Then
            If Not dict.Exists(key) Then
                dict.Add key, True
            End If
        End If
    Next i
    
    Set BuildKeyDict = dict
End Function

Sub ExtractNewCustomers()
    Dim wsOld As Worksheet, wsNew As Worksheet
    Set wsOld = ThisWorkbook.Worksheets("Old")
    Set wsNew = ThisWorkbook.Worksheets("New")
    
    Dim dictOld As Object
    Set dictOld = BuildKeyDict(wsOld)
    If dictOld Is Nothing Then
        MsgBox "Oldシートにデータがありません。", vbExclamation
        Exit Sub
    End If
    
    Dim lastRow As Long
    lastRow = wsNew.Cells(wsNew.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim src As Variant
    src = wsNew.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
        Dim key As String
        key = CStr(src(i, 1))
        
        If key <> "" Then
            If Not dictOld.Exists(key) 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("DiffNew")
    On Error GoTo 0
    If wsOut Is Nothing Then
        Set wsOut = ThisWorkbook.Worksheets.Add
        wsOut.Name = "DiffNew"
    End If
    
    wsOut.Cells.Clear
    wsOut.Range("A1").Resize(outRow - 1, 2).Value = dst
End Sub
VB

ここでは、「Oldのキー集合を辞書に載せておき、New配列を走査しながら辞書にないものだけ抜く」という型を使っています。
差分ツールの中身は、ほぼこのパターンの応用です。

辞書×配列最適化の“重要ポイント”をもう一段深掘りする

キーと値の設計を最初に決める

辞書を使うときに一番大事なのは、「キーに何を使うか」「値に何を持たせるか」を最初に決めることです。

キーは「一意に識別できるもの」
値は「後で欲しくなる情報をまとめたもの」

例えば、JOIN用なら「キー＝顧客コード」「値＝顧客名だけ」でもいいですが、
将来「顧客区分も欲しい」となりそうなら、「値＝顧客名＆顧客区分の小さな配列」でも構いません。

dict.Add key, Array(customerName, customerType)
VB

こうしておくと、後で dict(key)(0) で名前、dict(key)(1) で区分、という形で取り出せます。
「値に何を持たせるか」を設計しておくと、辞書が“ただのマスタ”から“柔軟なレコード”になります。

CompareMode を意識しておく

辞書の CompareMode を設定しないと、「ABC」と「abc」が別キーとして扱われます。
顧客コードや商品コードが「大文字小文字を区別しない」前提なら、必ずこうしておきます。

dict.CompareMode = vbTextCompare
VB

逆に、「完全一致で区別したい」場合は、デフォルトのままでも構いません。
ここを曖昧にすると、「見た目同じなのにJOINできない」という地味に嫌なバグになります。

配列のサイズは「最大サイズで確保→使った分だけ出す」

ReDim Preserve をループの中で何度も使うと、メモリコピーが発生して遅くなります。
なので、辞書×配列テンプレでは、次のような方針を取ります。

出力行数の上限が分かるなら、その行数で最初に配列を確保
ループの中では outRow を増やしていくだけ
最後に outRow までの範囲だけを書き戻す

これだけで、数十万行〜数百万行でも安定して動きます。

まとめ：辞書×配列の最適化テンプレは「3つの型」を押さえれば十分

今日の話をぎゅっとまとめると、辞書×配列の最適化テンプレは次の3パターンです。

マスタ参照JOIN：マスタを辞書化 → 明細配列をループしながら辞書参照 → 結果配列を一括出力
集計：明細配列を辞書で集計 → 辞書内容を結果配列に展開 → 一括出力
差分：片方を辞書化 → もう片方の配列を走査しながら辞書にないものだけ結果配列へ

どれも共通しているのは、「シートは最初と最後だけ」「中身は配列と辞書だけ」という構造です。

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