VLOOKUP関数の代わりにVBAで高速処理をする方法！2個以上の結果取得も可！

VLOOKUP関数の代わりにVBAで同じ処理をしたいときはないでしょうか。

例えばVLOOKUP関数を数多く設定したために動作が重くなった時などです。

けど、そんな中で悩むことは、

・VLOOKUP関数の動作を軽くしたいが方法がわからない。
・VLOOKUP関数の代わりにVBAで同じ処理をしたいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する
VLOOKUP関数の代わりにVBAで同じ値を得る方法とさらに2個以上の検索結果を取得する方法について
まとめます！

VLOOKUP関数の代わりにVBAで同じ処理をする方法

Excel のVLOOKUP関数に代わり、VBAで同じ処理をする方法について説明をします。

今回のサンプルデータと実行結果について

今回のサンプルデータは以下の通り、A列に商品名、B列に単価、C列に産地が入力されていて、こちらの表を商品マスターとします。

次にE～I列を売上表とします。

こちらの単価の部分をVBAを使い、商品マスタから取得していきます。

まずF列に売れた商品名を検索値とし、

H列に商品マスターの商品名と合致した同じ行の単価を参照します。

次に商品マスタより取得した単価を売上表の単価列へ入力していきます。

タカヒロ

通常、売上表の単価列の各セルにVLOOKUP関数を入力しなければなりませんが、その手間が不要になり、
クリック一発で処理結果を得ることができるというわけです。

VLOOKUP関数よりVBAが高速である理由

VLOOKUP関数よりVBAが高速である理由は、VBAのほうが繰り返す処理回数が少ないためとなります。

VLOOKUP関数は一つの検索結果を得るために、数式を入力したセルの件数分と検索範囲のレコード数分処理を繰り返すことになりますが、
VBAでは一度の処理で2つや3つなど複数の検索結果を同時に取得したり、検索済みの検索値があればスキップすることができます。

VLOOKUP関数と同じ値を取得するVBA

VLOOKUP関数の処理をVBAで再現していきたいと思います。

まずはVLOOKUP関数と同様の結果である、1つの検索値に対して1つの検索結果の値を取得していき、

検索結果を別のものにする設定変更を行い、問題ないか確認をしていきます。

次に1つの検索値に対して、2個以上の検索結果の値を得る方法について説明していきます。

1つの検索値に対して1つの検索結果を取得するVBA

VBAは以下の通りです。

設定箇所は以下の通りです。
出力先の列を指定します。※H列の指定であればHを入力

strCol = “H”

出力開始行を指定します。※H2セル以降の指定であれば2を入力

k = 2

検索範囲を指定します。

strSerchKeys = Range(“F2:F12”)

検索結果値の列番号を指定します。※検索結果列が左から2番目であれば2を指定

l = 2

値のみをクリアする出力先をセルを指定します。今回は”H2:H12″を指定しています。

Range(“H2:H12”).ClearContents

処理対象のセルをレンジ指定します。商品マスタは”A2:C8″にありますので、”A2:C8″を指定します。

strTemp = Range(“A2:C8”)

Sub VLOOKUP関数と同様の結果を取得()

    Dim strTemp As Variant
    Dim strSerchKeys As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
   '出力先の列を指定します。※H列の指定であればHを入力
    strCol = "H"
   '出力開始行を指定します。※H2セル以降の指定であれば2を入力
    k = 2
    
    '検索範囲を指定します。
    strSerchKeys = Range("F2:F12")
    
    '検索結果値の列番号を指定します。※検索結果列が左から2番目であれば2を指定
    l = 2
    
    '値のみをクリアする出力先をセルを指定します。
    'https://extan.jp/?p=3525#%E3%82%BB%E3%83%AB%E3%81%AE%E5%80%A4%E3%81%AE%E3%81%BF%E3%82%92%E3%82%AF%E3%83%AA%E3%82%A2%E3%81%99%E3%82%8B
    Range("H2:H12").ClearContents
    
    '処理対象のセルをレンジ指定します。
    strTemp = Range("A2:C8")
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
            '検索結果が空欄の場合のみ検索をします。
            If Range(strCol & k + j - 1) = "" Then
                If strTemp(i, 1) = strSerchKeys(j, 1) Then
                    Range(strCol & k + j - 1) = strTemp(i, l)
                End If
            End If
            
        Next
    Next

End Sub

VBAを実行します。

はい、各商品名ごとの単価が出力されましたね。

なお、「こまつな」は商品マスタに存在しないため空欄となっています。

また商品マスタには「じゃがいも」が2種類存在しますが、VLOOKUP関数と同様に初回のレコードが対象となるようになっています。

タカヒロ

VBAは関数のように数値の変更があった場合はリアルタイムに反映されないので、変更が生じた際はVBAを再実行するようにしましょう。

検索結果を商品マスタの別の列の値に変更する

検索結果はこれまで商品の単価でしたが、商品マスタの単価の隣の列の「産地」を取得するよう変更をしてみましょう。

VBAは以下の通りです。

Sub VLOOKUP関数と同様の結果を取得()

    Dim strTemp As Variant
    Dim strSerchKeys As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
   '出力先の列を指定します。※G列の指定であればGを入力
    strCol = "J"
   '出力開始行を指定します。※G2セル以降の指定であれば2を入力
    k = 2
    
    '検索範囲を指定します。
    strSerchKeys = Range("F2:F12")
    
    '検索結果値の列番号を指定します。※検索結果列が左から2番目であれば2を指定
    l = 3
    
    '値のみをクリアする出力先をセルを指定します。
    'https://extan.jp/?p=3525#%E3%82%BB%E3%83%AB%E3%81%AE%E5%80%A4%E3%81%AE%E3%81%BF%E3%82%92%E3%82%AF%E3%83%AA%E3%82%A2%E3%81%99%E3%82%8B
    Range("J2:J12").ClearContents
    
    '処理対象のセルをレンジ指定します。
    strTemp = Range("A2:C8")
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
            '検索結果が空欄の場合のみ検索をします。
            If Range(strCol & k + j - 1) = "" Then
                If strTemp(i, 1) = strSerchKeys(j, 1) Then
                    Range(strCol & k + j - 1) = strTemp(i, l)
                End If
            End If
            
        Next
    Next

End Sub

出力開始列を指定します。J2セル以降の指定ですのでJを入力します。

strCol = “J”

検索結果値の列番号を指定します。産地は検索結果列の左から3番目ですので3を指定します。

l = 3

出力セルの値のみをクリアする範囲を指定します。今回は産地出力先の”J2:J12″を指定します。

Range(“J2:J12”).ClearContents

VBAを実行します。

はい、各商品名ごとの産地が出力されましたね。

タカヒロ

VBAを再実行すると前の出力結果はクリアされますので、消したくない場合はClearContentsをコメントアウトして無効化してください。

1つの検索値に対して２つ以上の検索結果を取得するVBA

これまで1つの検索値に対して1つの検索結果しか得られませんでしたが、2つの検索結果を同時に得られるように変更していきます。

Sub VLOOKUP関数と同様の結果を取得_2個取得版()

    Dim strTemp As Variant
    Dim strSerchKeys As Variant
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
   '出力先の列を指定します。※H列の指定であればHを入力
    strCol = "H"
   '出力開始行を指定します。※H2セル以降の指定であれば2を入力
    k = 2
    
    '検索範囲を指定します。
    strSerchKeys = Range("F2:F12")
    
    '検索結果値の列番号を指定します。※検索結果列が左から2番目であれば2を指定
    l = 2
    
    '値のみをクリアする出力先をセルを指定します。
    'https://extan.jp/?p=3525#%E3%82%BB%E3%83%AB%E3%81%AE%E5%80%A4%E3%81%AE%E3%81%BF%E3%82%92%E3%82%AF%E3%83%AA%E3%82%A2%E3%81%99%E3%82%8B
    Range("H2:H12").ClearContents
    
    '2個目の出力先セルを指定します。
    Range("J2:J12").ClearContents
    
    
    '処理対象のセルをレンジ指定します。
    strTemp = Range("A2:C8")
    
    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
            '検索結果が空欄の場合のみ検索をします。
            If Range(strCol & k + j - 1) = "" Then
                If strTemp(i, 1) = strSerchKeys(j, 1) Then
                    Range(strCol & k + j - 1) = strTemp(i, l)
                    
                    '2個目の検索結果を出力します。※出力先はJ列、商品マスタの産地を指定しています。
                    Range("J" & k + j - 1) = strTemp(i, l + 1)
                    
                End If
            End If
            
        Next
    Next

End Sub

設定箇所は以下の通りです。

値のみをクリアする2個目の出力先セルを指定します。

Range(“J2:J12”).ClearContents

2個目の検索結果を出力します。
※出力先はJ列、商品マスタの産地を指定しています。

Range(“J” & k + j – 1) = strTemp(i, l + 1)

検索結果の1つ目と2つ目の値をクリアします。

VBAを実行しましょう。

はい、2個の検索結果が同時に出力されましたね！

タカヒロ

2個目の指定は変数にして指定した方がよいでしょう。
3個目、4個目も同じ要領で追加することが可能です。

＜追加＞処理速度改良版 VLOOKUP関数と同じ値を取得するVBA

上記VBAで1万件のマスタ、1万件のデータを突き合わせ処理をおこなったところ15分ほどかかりました。

遅延の原因となっている箇所は、1件づつExcelシートへ書き込みをしている処理です。

そこを1件づつ書き込むのではなく、配列の中に検索結果を代入し、最後にまとめてExcelシートへ書き込むようにしてみました。

VBAは以下の通りです。

Sub VLOOKUP関数と同様の結果を取得_改良版()

    Dim strTemp As Variant
    Dim strSerchKeys As Variant
    Dim strSerchResults As Variant
    Dim strSerchResultsRange As String

    Dim i As Integer
    Dim j As Integer
    Dim l As Integer
    
    
    '検索値の範囲を指定します。
    strSerchKeys = Range("F2:F12")
    
    '検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。
    strSerchResultsRange = "H2:H12"
    
    '検索対象セルを範囲指定します。
    strTemp = Range("A2:C8")
    
    '検索結果に出力する列番号を指定します。
    l = 2
    
    '値のみをクリアします。
    Range(strSerchResultsRange).ClearContents
    
    '検索結果用の配列を作成します。
    strSerchResults = Range(strSerchResultsRange)

    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strTemp, 1) To UBound(strTemp, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
            '検索結果が空欄の場合のみ検索をします。
            If strSerchResults(j, 1) = "" Then
                If strTemp(i, 1) = strSerchKeys(j, 1) Then
                    strSerchResults(j, 1) = strTemp(i, l)
                End If
            End If
        Next
    Next
    
    
    '検索結果を出力します。
    Range(strSerchResultsRange) = strSerchResults

End Sub

処理時間は1万件×1万件のデータでおおよそ30秒ほどで、かなり短縮ができたかと思います。

タカヒロ

PCの処理性能に依存するところもありますが、はじめのVBAを使用して遅いなと感じましたらこちらのVBAを試してみてください。