VLOOKUP関数で複数条件を設定する方法!VBAで同じ結果も可!

VLOOKUP関数で複数の条件を設定して処理をしたいときはないでしょうか。

たとえば突合せ元のデータに同じ名前のキーがあり、条件を加えて判別したいときなどです。

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

・VLOOKUP関数で複数条件を設定する方法がわからない。
・VLOOKUP関数の代わりにVBAで複数の条件を設定して同じ処理をしたいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する

・VLOOKUP関数で複数条件を設定する基本技
・VBAで複数条件を設定してVLOOKUP関数と同じ値を得る方法

についてまとめます!

VLOOKUP関数で複数条件を設定するイメージ

ExcelのVLOOKUP関数で複数条件を設定するイメージについて説明をします。

今回のサンプルデータは以下の通り、A列に商品名、B列に単価、C列に産地が設定されていて、
産地は千葉県と東京都の二種類でそれぞれおなじ商品と商品ごとの単価が入力されています。
こちらの表を商品マスターとします。

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

こちらの単価の部分をVLOOKUP関数、次にVBAを使い、商品マスタから各商品の産地ごとの単価を取得していきます。

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

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

最後にB12セルに指定産地を入力します。

空欄であれば産地の指定はなし、値があれば商品マスタの産地と合致した場合、該当する商品単価を出力するようにします。

結果は以下のイメージとなります。

産地に「東京」を指定すると、東京の単価、

「千葉」を指定すると千葉の単価を出力していきます。

それではVLOOKUP関数版とVBA版の2種類の方法を用いて算出してみましょう。

VLOOKUP関数で複数条件を指定する基本技

VLOOKUP関数で複数条件を指定する基本技について説明をします。

VLOOKUP関数では複数条件の指定ができない仕様ですので、
まずは複数条件の値となる文字列を結合して1つの検索値を作成していきます。

商品マスタに検索用キーワード列を追加する

今回は商品マスタにある商品名と産地を組み合わせたいので、
A列に1列追加し検索用のキーワードを作成します。

B2に

=B2&D2

と入力し、B7までドラッグします。

B12からC12に移った産地指定セルに「東京」と入力します。

売上表にVLOOKUP関数を入力する

次に売上表にVLOOKUP関数を入力していきましょう。

I2に

=VLOOKUP(G2&C$12,A$2:D$7,3,FALSE)

と入力しI12セルまでドラッグします。

この時点で結果が出力されましたね。

確認すると、同じ商品名ですが、東京都のほうの単価であることがわかります。

次に産地を千葉県に変えてみましょう。

はい、千葉県の商品単価に変わりましたね。

タカヒロ
タカヒロ
ただ、VLOOKUP関数で複数条件を指定する際には、検索用の文字列を作成する必要があるので、レイアウトが変わってしまうのが難点ですね。

列を追加したくないときはVBAで処理すればできます。

次はVBAで、VLOOKUP関数で複数条件を指定した結果と同じ値を出力する処理を行ってみましょう。

VBAでVLOOKUP関数で複数条件を指定した値と同様の結果を得る

VBAで、VLOOKUP関数で複数条件を指定した結果と同じ値を出力する処理を行っていきます。

出力先セルを用意する

まず検索用の列は不要となりますので、VLOOKUP関数で追加したA列は削除します。

また、VLOOKUP関数も使用しないのでI列の関数を削除します。

タカヒロ
タカヒロ
つまり元のレイアウトに戻すということですね。

初回は東京都の商品単価を出力しますので、B12セルに「東京」と入力します。

 

VBAを用意する

VBAは以下の通りです。

Sub 複数条件指定のVLOOKUP関数と同様の結果を取得()

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

    Dim i As Integer
    Dim j As Integer
    Dim l As Integer

    '検索値の範囲を指定します。
    strSerchKeys = Range("F2:F12")
    
    '2個目の検索値を指定します。
    strSecondSerchKeys = Range("B12").Value
    
    '検索結果出力先を指定します。※2つ以上の場合は列を変更してください。隣り合わせの列のみ指定できます。
    strSerchResultsRange = "H2:H12"
    
    '検索対象セルを範囲指定します。
    strTemp = Range("A2:C7")
    
    '検索結果に出力する列番号を指定します。
    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
                '2個の条件に合致した場合のみ値を取得します。
                If strTemp(i, 1) = strSerchKeys(j, 1) And strTemp(i, 3) = strSecondSerchKeys Then
                    strSerchResults(j, 1) = strTemp(i, l)
                End If
            End If
        Next
    Next
    
    
    '検索結果を出力します。
    Range(strSerchResultsRange) = strSerchResults

End Sub

VBA内のパラメータを設定する

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

検索値の範囲を指定します。”F2:F12″を指定します。

strSerchKeys = Range(“F2:F12”)

2個目の検索値を指定します。B12セルを指定します。

strSecondSerchKeys = Range(“B12”).Value

検索結果出力先を指定します。単価列の”H2:H12″を指定します。

strSerchResultsRange = “H2:H12”

検索対象セルを範囲指定します。商品マスタの”A2:C7″を指定します。

strTemp = Range(“A2:C7”)

検索結果に出力する列番号を指定します。
出力対象の単価は商品マスタの左から2番目なので2を指定します。

l = 2

VBAを実行する

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

はい、東京都の商品単価が出力されましたね。

タカヒロ
タカヒロ
商品マスタの商品と該当しない商品名は空欄になっていますね。VLOOKUP関数の場合#N/Aとなるので、VBAのほうが仕上がりがよいです。

千葉県を指定してVBAを実行してみましょう。

はい!千葉県の商品単価も一瞬で出力されましたね!

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

今回は、

・VLOOKUP関数で複数条件を設定する基本技
・VBAで複数条件を設定してVLOOKUP関数と同じ値を得る方法

についてまとめました。

様々な使い方ができるVLOOKUP関数ですが、VBAを使うとスッキリ軽快に同じ結果を得ることができることがわかりましたね。

用途に応じて使い分けて頂ければと思います。



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

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








コメントを残す

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

CAPTCHA ImageChange Image