VBAでSheet間のデータを照合し、結果を抽出する効率的な方法

Visual Basic

Excel VBAを使用して、複数のシート間でデータを照合し、結果を抽出する処理を効率化する方法を解説します。特に、大量のデータを取り扱う際、処理速度を改善するために、`Dictionary`オブジェクトを使用して照合を行うテクニックを紹介します。本記事では、データ照合の基本的な流れから、高速化を図るための最適な方法まで詳しく説明します。

1. シート間でデータを照合する基本的な流れ

まず、2つのシート間で共通のデータを照合する基本的なVBAコードの流れを確認しましょう。この例では、Sheet1のB列とSheet2のA列にある10桁の数値を比較し、一致するデータをSheet1に抽出する処理を行います。

1.1 データの範囲設定と検索処理

最初に、データを照合する範囲を設定します。Sheet1のB列（12行目から最終行）とSheet2のA列（2行目から最終行）を比較し、一致するデータが見つかればSheet1のI列に値をコピーし、J列に結果を記載します。

1.2 基本的なVBAコードの構造

以下は、VBAを使用してシート間のデータ照合を行う基本的なコードです。

Sub Verification()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRowData As Long, lastRowTarget As Long
Dim dataRng As Range, targetRng As Range
Dim cell As Range, targetCell As Range
Dim found As Boolean

Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

'データ範囲を設定
With ws1
    lastRowData = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set dataRng = .Range("B12:B" & lastRowData)
End With
With ws2
    lastRowTarget = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set targetRng = .Range("A2:A" & lastRowTarget)
End With

'照合処理
For Each cell In dataRng
    found = False
    For Each targetCell In targetRng
        If cell.Value = targetCell.Value Then
            ws1.Cells(cell.Row, "I").Value = targetCell.Value
            ws1.Cells(cell.Row, "J").Value = "OK"
            ws1.Cells(cell.Row, "J").Interior.ColorIndex = 4
            found = True
            Exit For
        End If
    Next targetCell

    If Not found Then
        ws1.Cells(cell.Row, "J").Value = "NG"
        ws1.Cells(cell.Row, "J").Interior.ColorIndex = 3
    End If
Next cell
End Sub

このコードでは、データを照合し、一致した場合には「OK」、一致しない場合には「NG」と表示し、色を付けて結果を視覚的に分かりやすくしています。

2. 高速化のためのDictionaryオブジェクトの利用

大量のデータを扱う場合、検索処理が遅くなることがあります。そのため、`Dictionary`オブジェクトを使用して検索速度を改善する方法を紹介します。

2.1 Dictionaryを使った高速検索の仕組み

VBAの`Dictionary`オブジェクトは、キーと値を効率的に格納し、データの検索を高速化するために非常に有効です。これを利用して、Sheet2のA列のデータをDictionaryに格納し、Sheet1のB列のデータと照合することで、検索速度を大幅に向上させることができます。

2.2 Dictionaryを使用したVBAコード例

以下は、Dictionaryを使用した高速検索のVBAコードです。

Sub VerificationWithDictionary()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastRowData As Long, lastRowTarget As Long
Dim dataRng As Range, targetRng As Range
Dim cell As Range
Dim dict As Object
Dim targetCell As Range
Dim found As Boolean

Set dict = CreateObject("Scripting.Dictionary")
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")

'データ範囲を設定
With ws1
    lastRowData = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set dataRng = .Range("B12:B" & lastRowData)
End With
With ws2
    lastRowTarget = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set targetRng = .Range("A2:A" & lastRowTarget)
End With

'DictionaryにSheet2のデータを格納
For Each targetCell In targetRng
    dict(targetCell.Value) = "OK"
Next targetCell

'データ照合処理
For Each cell In dataRng
    If dict.exists(cell.Value) Then
        ws1.Cells(cell.Row, "I").Value = cell.Value
        ws1.Cells(cell.Row, "J").Value = "OK"
        ws1.Cells(cell.Row, "J").Interior.ColorIndex = 4
    Else
        ws1.Cells(cell.Row, "J").Value = "NG"
        ws1.Cells(cell.Row, "J").Interior.ColorIndex = 3
    End If
Next cell
End Sub

Dictionaryを使用することで、`targetRng`のデータを一度格納し、その後はO(1)の時間でデータの存在確認ができます。これにより、照合処理の速度が大幅に向上します。

3. 結果を視覚的に分かりやすくするためのフォーマット

結果を表示する際に、セルの背景色や文字色を変更することで、照合結果を視覚的にわかりやすくすることができます。例えば、`OK`の場合には緑色、`NG`の場合には赤色にするなど、ユーザーにとって直感的なフィードバックを提供できます。

4. まとめ

VBAを使ってシート間でデータを照合する処理を効率化するためには、Dictionaryオブジェクトを活用することが非常に有効です。これにより、データ照合の速度が改善され、大量のデータを扱う際にも高いパフォーマンスを維持できます。また、結果を視覚的にわかりやすく表示するためのフォーマットを活用することで、ユーザーが結果を簡単に確認できるようになります。

コメント

タイトルとURLをコピーしました