【エクセル・マクロ・VBA】シート間のデータをVlook UPでマッチング

VLOOKUP関数

VLOOKUP関数：＊False:完全一致／False:部分一致

VLOOKUP(検索値, 範囲, 列番号, 検索の型)
WorksheetFunction.VLookup(Range("C1"), Range("A:B"), 2, False)

RangeオブジェクトにVBAから関数式を入力する場合

Rangeオブジェクト.Formula = “=数式”
Range("C3").Formula = "=VLOOKUP(B3, E3:G8, 4, False)"

シート間マッチング_Vlook up

このマクロは、ExcelのVLOOKUP関数を使用して、アクティブシートとその次のシートの間でマッチングを行います。

Sub シート間のマッチング_ブイルック()
    
Dim i As Long
On Error Resume Next

For i = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        
ActiveSheet.Cells(i, 5) = WorksheetFunction.VLookup(ActiveSheet.Cells(i, 1), ActiveSheet.Next.Range("A:D"), 4, False)

Next
  
On Error GoTo 0

MsgBox "完了"
End Sub

シートの説明

調べる基となるデータを左側のシートに準備（＝基データシート）
調べる対象のデータのある相手シートを基データシートの右隣りのシートに設置
基データシート(左側のシート)を選択してアクティブ状態にする
VLookupで基データシートの1列目をキーに右隣りのシートのA列からD列の範囲の4列目（＝D列）を取得
基データシートの５列目に値を記入

解説

VLOOKUP関数の第一引数に使用する検索キーを含む列を指定します。この例では、アクティブシートの列Aの2行目から最終行までを検索キーに指定しています。
VLOOKUP関数の第二引数に、検索キーを検索する範囲を指定します。この例では、アクティブシートの次のシート（ActiveSheet.Next）の列AからDまでを検索範囲に指定しています。
VLOOKUP関数の第三引数に、検索キーが見つかった場合に、検索結果を返す列番号を指定します。この例では、検索結果を含む列が4列目であるため、引数に4を指定しています。
VLOOKUP関数の最後の引数に、検索結果が見つからなかった場合に返す値を指定します。この例では、Falseを指定しています。これにより、完全一致で検索が行われます。
VLOOKUP関数の結果を、アクティブシートの同じ行の5列目に設定します。
次の行に移動して、同じ処理を繰り返します。
すべての行が処理されたら、処理が終了することを示すメッセージボックスが表示されます。

注意：このマクロには、エラーハンドリングが含まれています。これは、VLOOKUP関数が見つからない場合にエラーを返す可能性があるためです。エラーが発生した場合、処理が続行されないようにするためです。

シート間のデータを Vlook upでマッチング／結果シートに転記

このExcelマクロは、このマクロでは、3つのシートを使って、1つのシートから値を検索して、別のシートに結果を書き込みます。Sheet1のA2からA500の範囲にある値を、Sheet2のA2からB500の範囲内で検索して、Sheet3のB2からB500の範囲に一致する値を書き込むものです。

Sub シート間マッチング_シートへ出力()

    Dim SerchKey As Range
    Dim SerchRange As Range
    Dim OutputRange As Range
    Dim i As Long
    
    '検索値
    Set SerchKey = Worksheets("Sheet1").Range("A2:A500")
    '検索範囲
    Set SerchRange = Worksheets("Sheet2").Range("A2:B500")
    '出力範囲
    Set OutputRange = Worksheets("Sheet3").Range("B2:B500")
    
    Application.ScreenUpdating = False
    
    For i = 1 To SerchKey.Rows.Count
        OutputRange(i, 1) = WorksheetFunction.VLookup(SerchKey(i, 1), SerchRange, 2, False)
    Next
    
    Application.ScreenUpdating = True

MsgBox "完了"
End Sub

解説

変数の宣言
- 変数を宣言します。検索値、検索範囲、出力範囲、ループカウンタの4つの変数が宣言されています。変数の型は、SerchKey、SerchRange、OutputRangeはRange型、iはLong型です。
検索値、検索範囲、出力範囲の設定
- 検索値、検索範囲、出力範囲を設定しています。これらの範囲はそれぞれ、Sheet1のA2からA500、Sheet2のA2からB500、Sheet3のB2からB500になります。
アプリケーションのスクリーンアップデートをオフにする
- アプリケーションのスクリーンアップデートをオフにしています。これにより、処理中に画面がちらつくことがなくなり、処理の高速化が期待できます。
ループ処理
- For文を使って、検索値の範囲を1つずつ処理します。
- SerchKey範囲内の各行を順に処理して、各行の値を使ってWorksheetFunction.VLookup関数を呼び出し、その結果をOutputRange範囲の対応するセルに設定します。SerchKey範囲の各行は、i変数を使ってアクセスされ、SerchKey(i, 1)式で参照されます。
- WorksheetFunction.VLookup関数は、第1引数に検索値、第2引数に検索範囲、第3引数に返す値の列番号、第4引数に完全一致検索の指定を取ります。ここでは、検索値としてSerchKey(i, 1)を、検索範囲としてSerchRangeを、返す値の列番号として2を、完全一致検索の指定としてFalseを指定しています。
- OutputRange(i, 1)式は、OutputRange範囲のi行目、1列目のセルを表します。つまり、SerchKey範囲の各行に対して、対応するSerchRangeのセルから返された値をOutputRange範囲に書き込んでいます。
Application.ScreenUpdatingプロパティをTrueに戻して、画面の更新を再開します。
最後に、処理が完了したことをユーザーに通知するメッセージボックスを表示します。

シート名を指定してマッチング／VLook up

Sub 突合_ブイルック_シート名指定()
    
Dim i As Long, b As Variant, n As Long, p As Long
    
n = Worksheets("基ﾃﾞｰﾀ").Cells(Rows.Count, 1).End(xlUp).Row
p = Worksheets("検索").Cells(Rows.Count, 1).End(xlUp).Row
    
ReDim b(n - 2, 0)
    
For i = 2 To n
    
b(i - 2, 0) = WorksheetFunction.VLookup(Worksheets("基ﾃﾞｰﾀ").Cells(i, 6), Worksheets("検索").Range("A2:A" & p), 2, False) 'Range("A2:F" & LstRow2)

Next i

Worksheets("検索").Range(Range("A2"), Cells(Rows.Count, 6).End(xlUp)).Offset(0, 1) = b

MsgBox "完了"
End Sub

解説

i: Long型の変数であり、forループで使用されるループ変数です。
- b: Variant型の配列変数であり、Vlookup関数によって取得された検索結果が格納されます。
- n: Long型の変数であり、検索対象のシート「基ﾃﾞｰﾀ」の最後の行を取得します。
- p: Long型の変数であり、検索するシート「検索」の最後の行を取得します。
- ReDimステートメントは、配列変数 b を再定義します。n-2 行、0 列で配列を再定義します。
- i: Long型の変数であり、forループで使用されるループ変数です。
- b: Variant型の配列変数であり、Vlookup関数によって取得された検索結果が格納されます。
- n: Long型の変数であり、検索対象のシート「基ﾃﾞｰﾀ」の最後の行を取得します。
- p: Long型の変数であり、検索するシート「検索」の最後の行を取得します。
- ReDimステートメントは、配列変数 b を再定義します。n-2 行、0 列で配列を再定義します。
Forループを使って、検索対象のシート「基ﾃﾞｰﾀ」のセル範囲を反復処理し、Vlookup関数を使用して、検索シート「検索」から値を取得します
- Forループは、変数 i が 2 から n まで増加するように設定されています。
- WorksheetFunction.VLookup 関数は、”基ﾃﾞｰﾀ”シートのi行6列目のセルの値を検索キーとして、”検索”シートのA列で一致するものを探し、その2列目の値をbのi-2行1列目に代入する。つまり、”基ﾃﾞｰﾀ”シートの6列目と、”検索”シートの1列目を比較し、一致する行の2列目の値を配列bに格納している。
最後に、”検索”シートのA列の範囲を取得し、配列bをその右隣の列に出力する。
最後に、”完了”というメッセージボックスが表示される。

ダミーデータのダウンロード

dummy15-1 ダウンロード

【マクロ・VBA】シート間のデータをVLook UPでマッチング