Excel VBAで複数列の条件を照合し、一致したデータを別の場所へ抽出する処理では、Dictionaryを利用すると高速で効率的なマクロを作成できます。ただし、1つのキーに対して商品名だけでなく数量など複数の値を取得したい場合は、配列や複数項目を持つデータ構造を利用する必要があります。この記事では、担当者とコードをキーにして一致した商品の数量や商品名を別列へ出力する方法について解説します。
Dictionaryで複数の値を管理する基本的な考え方
VBAのDictionaryは、キーと値をセットで管理できる便利なオブジェクトです。例えば「担当者＋コード」をキーにして、「商品名」を取得するような処理では、Dictionaryの値に商品名だけを保存すれば対応できます。
しかし、今回のように一致したデータから「数量」と「商品名」の2種類を取り出したい場合、Dictionaryの値には1つの情報だけではなく複数の情報を持たせる必要があります。
その場合は、値として配列を登録します。例えば、Array(数量, 商品名)のように保存すると、キー1つに対して複数のデータを保持できます。
一致データを数量と商品名の2列へ出力するVBA例
以下のようにDictionaryへ配列を登録すると、条件一致した行から数量と商品名を取り出せます。
Sub MatchAndCopyData_Array()
Dim dic As Object
Dim src As Variant, dst As Variant
Dim i As Long
Dim key As String
Set dic = CreateObject("Scripting.Dictionary")
'元データ取得（A～D列）
src = Range("A2:D" & Cells(Rows.Count, "A").End(xlUp).Row).Value
'キーごとに数量と商品名を登録
For i = 1 To UBound(src, 1)
key = src(i, 1) & "_" & src(i, 2)
If Not dic.Exists(key) Then
dic.Add key, Array(src(i, 3), src(i, 4))
End If
Next i
'検索側データ（F～I列）
dst = Range("F2:I" & Cells(Rows.Count, "F").End(xlUp).Row).Value
For i = 1 To UBound(dst, 1)
key = dst(i, 1) & "_" & dst(i, 2)
If dic.Exists(key) Then
dst(i, 4) = dic(key)(1)
dst(i, 5) = dic(key)(0)
Else
dst(i, 4) = ""
dst(i, 5) = ""
End If
Next i
Range("F2").Resize(UBound(dst, 1), UBound(dst, 2)).Value = dst
End Sub
この例ではDictionaryの値に配列を登録し、dic(key)(0)で数量、dic(key)(1)で商品名を取得しています。
ReDimを使って結果用配列を作成する方法
大量データを扱う場合は、検索結果を書き込む専用配列を作成する方法も有効です。セルへ1件ずつ書き込むよりも、配列へ保存して最後に一括出力する方が処理速度が向上します。
例えば、検索結果をI列とK列へ出力したい場合は、以下のように結果配列を準備します。
ReDim result(1 To UBound(dst, 1), 1 To 2)
そして一致した場合に、result(i,1)へ数量、result(i,2)へ商品名を保存し、最後にRangeへ書き戻します。
この方法は、検索結果が多い場合や、元データを変更せず別の場所へ結果だけ出力したい場合に適しています。
現在のコードで修正が必要なポイント
元のコードでは、Dictionaryへ登録している値が商品名だけになっています。
a.Add key, b(i, 4)
この部分ではキーに対して商品名しか保存していないため、数量を取り出すことができません。
数量も必要な場合は、以下のように変更します。
a.Add key, Array(b(i, 3), b(i, 4))
これにより、1つのキーに対して「数量」と「商品名」をセットで保存できます。
また、出力時には単純にa(searchKey)とするのではなく、配列の中から必要な項目を指定します。
数量 = a(searchKey)(0)
商品 = a(searchKey)(1)
重複キーが存在する場合の注意点
Dictionaryでは同じキーを複数登録することはできません。そのため、担当者とコードの組み合わせが同じ商品が複数存在する場合は注意が必要です。
例えば同じコードの商品が複数行あり、数量を合計したい場合は、Dictionaryへ追加するのではなく、既存値へ加算する処理が必要になります。
例として、数量を合計する場合は以下のような考え方になります。
If dic.Exists(key) Then
dic(key)(0) = dic(key)(0) + 数量
Else
dic.Add key, Array(数量, 商品名)
End If
データの重複ルールによって処理方法が変わるため、実際の表構成に合わせて調整することが重要です。
まとめ
Excel VBAで条件一致したデータから数量と商品名など複数の項目を抽出したい場合は、Dictionaryの値に配列を登録する方法が便利です。
Dictionary.Add キー, Array(取得したい項目1, 取得したい項目2)
という形にすることで、1つのキーから複数の情報を取り出せます。
また、大量データを処理する場合はReDimで結果用配列を作成し、最後に一括でセルへ出力すると高速化できます。Dictionaryと配列を組み合わせることで、商品管理や在庫照合などのExcel作業を効率的に自動化できます。
コメント