公開日：2023/09/07

Excel VBAでVLOOKUP関数の参照を別シートにする方法

この記事では、Excel VBAでVLOOKUP関数の参照を別シートにする方法をご紹介します。

VLOOKUP関数でデータを抽出する際、日付やカテゴリでシートを分けている場合でも別シートを参照してデータ検索をすることができます。

以下では、複数シートを参照してデータを抽出する場合に追加したシートも自動で反映されるVBAコードをご紹介します。

開発タブを表示する

VBAを使用してVLOOKUP関数の参照を別シートにする方法をご紹介するので、上記の画像のような「開発」タブが表示されていない場合は、「開発」タブを表示させましょう。

詳細は、以下の記事を参考にしてみてください。

エクセルの開発タブを表示する方法

VLOOKUP関数の参照を別シートにするVBAコード

VLOOKUP関数で別シートを参照して検索結果を取得したい場合に、自動で結果を表示するVBAコードを下記に記載するのでコピーして使用してください。

詳しい使い方は、下記「Excel VBAでVLOOKUP関数の参照を別シートにする方法」セクションでご紹介しています。

Sub 検索値に紐づく値を別シートから取得する()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim i As Long
    Dim lastRow1 As Long
    
    ' シート1とシート2を設定
    Set ws1 = ThisWorkbook.Sheets("在庫検索")
    Set ws2 = ThisWorkbook.Sheets("在庫表")
    
    ' シート1の最終行を取得
    lastRow1 = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    
    ' VLOOKUP関数を評価してセルに値を設定
    For i = 2 To lastRow1 ' A2からA列の最終行までの範囲をカバー
        Dim lookupResult As Variant
        lookupResult = Application.VLookup(ws1.Cells(i, 1).Value, ws2.Range("A:D"), 4, False)
        
        ' 検索結果をセルに設定
        If Not IsError(lookupResult) Then
            ws1.Cells(i, 2).Value = lookupResult
        Else
            ws1.Cells(i, 2).Value = "" ' 検索値が見つからない場合、空白に設定
        End If
    Next i

End Sub

ご紹介した上記のコードについて、例を挙げてご説明します。

上記の画像は、「VLOOKUP関数の参照を別シートにするVBAコード」を実行した例です。

「在庫検索」シートのA2セル以降のA列に入力した値を「検索値」とします。

検索値を「在庫表」シートのA列からD列の範囲で検索して、検索値に紐づく4列目（例：在庫数）の値を「在庫検索」シートのB2セル以降のB列に表示します。

この際、検索値が存在しない場合はエラーではなく空白が返されます。

シート名やVLOOKUP関数の引数などはデータ範囲に合わせて調整してください。

Excel VBAでVLOOKUP関数の参照を別シートにする方法

VLOOKUP関数で別シートを参照して結果を取得する際、VBAを使って自動で行う方法をご紹介します。

①開発タブ、②Visual Basicの順に選択します。

「Microsoft Visual Basic for Applications」が開きました。①挿入タブ、②標準モジュールの順に選択します。

「標準モジュール」が開きました。上記セクション「VLOOKUP関数の参照を別シートにするVBAコード」をコピーして貼り付けてください。コードの説明も参考にしてみてください。

作成したマクロを保存します。①ファイルタブ、②上書き保存の順に選択します。

「名前を付けて保存」ダイアログボックスが表示されました。①保存先（例：デスクトップ）を選択します。②ファイル名（例：検索値に紐づく値を別シートから取得する）を入力し、③ファイルの種類でExcelマクロ有効ブックを選択し、④保存ボタンを押します。

Excelシートに戻ります。①開発タブ、③マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。①作成したマクロ（例：検索値に紐づく値を別シートから取得する）を選択し、②実行ボタンを押します。

「在庫検索」シートの検索値（例：商品コード）に紐づく「在庫表」の在庫数を表示することができました。「商品コード」10011は存在しない値なので、空白が表示されました。

VBAでVLOOKUP関数の参照を最終シートまでする方法

VLOOKUP関数の参照を複数シートからしたい場合、すべてのシートを参照する方法をご紹介します。また、シートを追加しても結果を自動で抽出することができます。

上記の右の表は、1日の商品別の売り上げが入力されています。2日から5日までのシートにも同じように商品別の売り上げが入力されています。

上記の左の表は、以下でご紹介するVBAコードを使って検索値の商品コードに紐づく「売上」の値を1日から5日のそれぞれのシートから抽出したものです。

以下では、VBAを使ってVLOOKUP関数の参照を最終シートまで自動でする方法をご紹介します。

①開発タブ、②Visual Basicの順に選択します。

「Microsoft Visual Basic for Applications」が開きました。

①挿入タブ、②標準モジュールの順に選択します。

「標準モジュール」が開きました。

以下のコードをコピーしてモジュールに貼り付けてください。

Sub 検索値に紐づく値を最終sheetまで取得する()
    ' 「商品別売り上げ」シートを指定
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("商品別売り上げ")
    
    ' ループ用のカウンタ宣言
    Dim i As Integer
    
    ' A列のセルからA2セル以降をループ
    For i = 2 To targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
        ' 検索値を取得
        Dim searchValue As Variant
        searchValue = targetSheet.Cells(i, 1).Value
        
        ' 「最終シート」までループ
        Dim j As Integer
        For j = 2 To ThisWorkbook.Worksheets.Count
            With ThisWorkbook.Worksheets(j)
                ' VLookup関数で別シートを参照し、エラーハンドリングで結果を「商品別売り上げ」シートに書き込む
                Dim result As Variant
                result = Application.VLookup(searchValue, .Range("A:E"), 5, False)
                
                ' 結果を「商品別売り上げ」シートに書き込む
                targetSheet.Cells(i, j).Value = IIf(IsError(result) Or IsEmpty(searchValue), "", result)
            End With
        Next j
    Next i
End Sub

上記のコードは、「商品別売り上げ」シートに入力された検索値を他のすべてのシートで検索し、検索値に紐づく値を返します。

「商品別売り上げ」シートのA2セル以降のA列に入力された値を検索値として、その他すべてのシートのA列からE列の範囲を検索します。

エラーがない場合はVLOOKUP関数の結果を返し、エラーの場合は空白を返して「商品売り上げ」のB列から横方向データを並べます。

い。

作成したマクロを保存します。

①ファイルタブ、②上書き保存の順に選択します。

「名前を付けて保存」ダイアログボックスが表示されました。

①保存先（例：デスクトップ）を選択し、②ファイル名（例：検索値に紐づく値を最終sheetまで取得する）を入力します。③ファイルの種類でExcelマクロ有効ブックを選択し、④保存ボタンを押します。

Excelシートに戻ります。

①開発タブ、②マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。

①作成したマクロ（例：検索値に紐づく値を最終sheetまで取得する）を選択し、②実行ボタンを押します。

「商品別売り上げ」シートの検索値（例：商品コード）に紐づく1日から5日までのそれぞれのシートから「売上」を抽出することができました。

「商品コード」10011は存在しない値なので、空白が表示されました。

以下では、「6日」シートを追加した際にマクロを実行するとどうなるのか試してみます。

画面下の「+」を押して新しいシートを追加します。

今回は、分かりやすいようにG1セルに見出し（例：6日）を入力して「格子」で囲んでいますが、あらかじめデータを追加することが分かっている場合は、見出しを入力しておくと便利です。

①+をダブルクリックしてシート名（例：6日）を変更し、②A1セルからデータを入力します。

今回は、VLOOKUP関数の検索範囲でA列からE列を指定しているので参照範囲がずれないようにA1セルからデータを入力します。

①「商品別売り上げ」シートに移動し、②開発タブ、③マクロの順に選択します。

「マクロ」ダイアログボックスが表示されました。

①作成したマクロ（例：検索値に紐づく値を最終sheetまで取得する）を選択し、②実行ボタンを押します。

追加した「6日」シートの結果を反映することができました。