VLOOKUP関数で複数列の値を一度に抽出する方法!VBAで同じ結果も可!

VLOOKUP関数で複数列の値を一度に抽出をしたいときはないでしょうか。

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

・VLOOKUP関数の設定が複雑かつ大量に設定しなければならずカンタンな設定にしたいが方法がわからない。
・VBAで一気にVLOOKUP複数列の値と同様の値を抽出をしたいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する
VLOOKUP関数で複数列の値を一度に抽出する方法とVBAで同じ結果を得る方法について
まとめます!

VLOOKUP関数で複数列の値を一度に抽出をする方法

VLOOKUP関数で複数列の値を一度に抽出をする方法について説明をします。

まず結論としてはVLOOKUP関数ではなくXLOOKUP関数を使います。

その理由はVLOOKUP関数では複数列抽出に対応していないので列数分設置があり煩雑になってしまいますが、XLOOKUP関数は、VLOOKUP関数で足りない部分を拡張した関数で、VLOOKUP関数の機能に加え複数列に対応しているため、設置件数も最小限にすることができます。

次にVBAですが、VLOOKUP関数、XLOOKUP関数を使わずに、カンタンな設定で同じ結果を出力していきます。

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

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

次にF~L列を「売上表」として、G列に売れた商品名、I~K列に商品マスターから取得した各商品ごとの単価を商品マスタより取得し入力していきます。

売上金額は単価が入力された時点で個数と掛け合わせ算出されます。

各処理の実行結果となります。

■XLOOKUP関数

■VBA実行

さらにXLOOKUP関数では指定できない隣接しない列番号指定で出力をしていきます。

それでは早速使ってみましょう!

XLOOKUP関数を使い、複数列の値を取得する

XLOOKUP関数を使い、一つの関数設定で複数の値を取得し、複数のセルへ結果を出力していきます。

XLOOKUP関数の書式

XLOOKUP関数の書式は以下の通りです。

XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合],[ 一致モード], [検索モード])
[検索値]:検索キーがあるセルを指定します。
[検索範囲] :検索先の範囲を指定します。
[戻り範囲] :検索が一致した場合の戻り値の範囲を指定します。
[見つからない場合]:見つからない場合の処理を追加します。省略可能です。
[一致モード] :
0 – 完全一致。 見つからない場合は、#N/A が返されます。 これが既定の設定です。
-1 – 完全一致。 見つからない場合は、次の小さなアイテムが返されます。
1 – 完全一致。 見つからない場合は、次の大きなアイテムが返されます。
[検索モード]:使用する検索モードを指定します。
1 – 先頭の項目から検索を実行します。 これが既定の設定です。
-1 – 末尾の項目から逆方向に検索を実行します。
2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。
-2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。 並べ替えられていない場合、無効な結果が返されます。

参考:https://support.microsoft.com/ja-jp/office/xlookup-%E9%96%A2%E6%95%B0-b7fd680e-6d10-43e6-84f9-88eae8bf5929

XLOOKUP関数を実装する

XLOOKUP関数を実装してみましょう。

I2セルを選択し、以下を入力します。

=XLOOKUP(G2,A$2:A$7,B$2:B$7,"該当なし")

まずは一つの値が取得できました。

次に複数列の値を取得できるようにしましょう。

同じくI2セルへ以下を入力します。

=XLOOKUP(G2,A$2:A$7,B$2:D$7,"該当なし")

戻り範囲の指定で複数列を含めるとによりその列の値を取得し、同時に隣接セルへ結果を出力することができます。

表の最下部まで数式をドラッグしましょう。

はい、複数列の値が取得できましたね。

 

タカヒロ
タカヒロ
XLOOKUP関数を隣接セルに設定しなくてもよいのは手間が省けてうれしいですね。

つづいてVBAでXLOOKUP関数と同じ結果を取得していきましょう。

そしてさらに隣接列ではない単体の列指定で複数列の値を取得していきましょう!

XLOOKUP関数と同じ結果を出力するVBA

これまでXLOOKUP関数にて行ってきた処理をXLOOKUP関数を使わずにVBAだけで処理をしていきます。

まずはXLOOKUP関数と同様の結果である、1つの検索値に対して1つの検索結果の値を取得していき、隣接する複数列の値も同時にシートへ出力していきます。

フォーマットは関数と同じものを利用します。出力先を空欄にしましょう。

コード

VBAは以下の通りです。

Sub VLOOKUP関数と同様の結果を取得_複数列出力()

    Dim strSerchRange As Variant
    Dim strSerchKeys As Variant
    Dim strSerchResults As Variant
    Dim strSerchResultsRange As String
    Dim arrColNums As Variant


    Dim i As Integer
    Dim j As Integer
    Dim l As Integer
    
    
    '検索値の範囲を指定します。
    strSerchKeys = Range("G2:G12")
    
    '検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。
    strSerchResultsRange = "I2:K12"
    
    '検索対象セルを範囲指定します。なお、1列目が主キーになるようにします。
    strSerchRange = Range("A2:D7")
    
    '検索結果に出力する複数の列番号を指定します。
    arrColNums = Array(2, 3, 4)
    
    '値のみをクリアします。
    Range(strSerchResultsRange).ClearContents
    
    '検索結果用の配列を作成します。
    strSerchResults = Range(strSerchResultsRange)

    '配列に格納された値すべてを対象に処理します。
    For i = LBound(strSerchRange, 1) To UBound(strSerchRange, 1)
    
        For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)
        
                '検索キーと表の主キーがマッチしているか確認します。
                If strSerchRange(i, 1) = strSerchKeys(j, 1) Then
                    '検索結果を配列へ格納します。同時に指定列番号分の値も格納します。
                    For k = LBound(arrColNums) To UBound(arrColNums, 1)
                        strSerchResults(j, k + 1) = strSerchRange(i, arrColNums(k))
                    Next
                    
               End If
               
               'マッチしない場合代わりの値を格納します。
               If strSerchResults(j, 1) = "" Then
                    For k = LBound(arrColNums) To UBound(arrColNums, 1)
                        strSerchResults(j, k + 1) = "該当なし"
                    Next
               End If
        Next
    Next
    
    
    '配列に格納した検索結果を指定Rengeへ出力します。
    Range(strSerchResultsRange) = strSerchResults

End Sub

VBAを設定する

VBAの設定をしましょう。

検索値の範囲を指定します。
サンプルでは売上表の「売れた商品列」を指定しています。

strSerchKeys = Range(“G2:G12”)

検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。

strSerchResultsRange = “I2:K12”

検索対象セルを範囲指定します。
サンプルでは商品マスタの範囲をして指定ます。
なお、1列目が主キーになるようにします。

strSerchRange = Range(“A2:D7”)

検索結果に出力する複数の列番号を指定します。
サンプルでは商品マスタのB,C,D列を指定したことになります。

arrColNums = Array(2, 3, 4)

VBAを実行する

VBAを実行してみましょう。

はい、XLOOKUP関数と同様の結果が出力されていますね!

戻り値の列の内、隣接しない列番号を指定する

続いて、戻り値の列の内、隣接しない列番号を指定して、列の値をしシートへ出力してみます。

具体的には、「単価」、「産地」、「生産者」とマスタの列の並び順で取得していましたが、「単価」、「生産者」と列番号を指定し絞り込んでみます。

VBAを設定する

検索結果に出力する複数の列番号を指定します。

arrColNums = Array(2,4)

タカヒロ
タカヒロ
2,3,4から3を抜かした形ですね。

VBAを実行する

はい、隣接しない列の値も取得できていますね!

VBAの説明

検索値の範囲を指定します。

strSerchKeys = Range(“G2:G12”)

検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。

strSerchResultsRange = “I2:K12”

検索対象セルを範囲指定します。なお、1列目が主キーになるようにします。

strSerchRange = Range(“A2:D7”)

検索結果に出力する複数の列番号を指定します。

arrColNums = Array(2, 3, 4)

値のみをクリアします。

Range(strSerchResultsRange).ClearContents

検索結果用の配列を作成します。

strSerchResults = Range(strSerchResultsRange)

配列に格納された値すべてを対象に処理します。

For i = LBound(strSerchRange, 1) To UBound(strSerchRange, 1)
For j = LBound(strSerchKeys, 1) To UBound(strSerchKeys, 1)

検索キーと表の主キーがマッチしているか確認します。

If strSerchRange(i, 1) = strSerchKeys(j, 1) Then

検索結果を配列へ格納します。同時に指定列番号分の値も格納します。

For k = LBound(arrColNums) To UBound(arrColNums, 1)
strSerchResults(j, k + 1) = strSerchRange(i, arrColNums(k))

マッチしない場合代わりの値を格納します。

If strSerchResults(j, 1) = “” Then
For k = LBound(arrColNums) To UBound(arrColNums, 1)
strSerchResults(j, k + 1) = “該当なし”

配列に格納した検索結果を指定Rengeへ出力します。

Range(strSerchResultsRange) = strSerchResults

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」+「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、
VLOOKUP関数で複数列の値を一度に抽出する方法とVBAで同じ結果を得る方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です