エクセルVBA|VLookup関数を実行するマクロのつくりかた

実務でエクセルワークシートをつかった事務作業をしていると、大量のリストデータの中から条件に該当したものだけを取り出したい場面があります。

例えば、提出期限までにスタッフの提出物を回収する必要があり、未提出の人だけを取り出したリストがほしい。とか、売上が〇〇円以上の顧客だけのリストが作りたいなど。
元のデータから特定の条件に当てはまったものだけを取り出し、利用したいときに便利につかえる関数がVlookup関数です。

今回はそんなVlookup関数を、VBA(マクロ)で実装する方法を紹介していきたいと思います。
また、実データをつかってのコードも掲載していますので、ご自身の作業用に応用してつかって頂ければ幸いです。

※注:ここに掲載したプログラミングコードを流用する場合、コードの書き損じなどでデータの破損や、紛失が発生する可能性があります。コードの流用については自己責任で判断してください。テスト用のリストデータなどで、プログラムが思ったとおりに動くことを確認した上で、実装することで安全に利用できます。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか?

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか?
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「1日速習講座」がおすすめです。

エクセルのVlookup関数とは

さっそくVBAのVlookup関数の実装方法について紹介しておきたいところですが、まずはそもそもエクセルのワークシートでVlookup関数とはどういったものか。また、どんな結果が得られるものか。といった点おさらいしておきましょう。

Vlookup関数をつかうためのエクセルワークシートサンプルデータ

Vlookup関数を説明する上で、こちらのワークシートの表をつかっていきます。

このサンプルデータは、架空の学校の学生のテスト結果(国語・数学・英語・理科・社会)と5教科合計と平均をリストにしたものです。

Vlookup関数の動作を確認するためのサンプルデータリスト

エクセルのワークシートだと、A列から順番にID、氏名、性別(値は男性:1 女性:2)、学年、クラス、各教科のテストの点数、合計、平均をまとめています。

Vlookup関数の使用方法

それでは、前項の表から実際にVlookup関数をつかってデータを取り出してみます。
今回は、IDをつかって特定の人物をデータだけを右側に貼り付けてみましょう。

Vlookup関数の構文

=VLOOKUP(検索値, 範囲, 列番号, 検索の型

Vlookup関数を書くときは、4つの引数を指定できます。それぞれの引数についての説明は以下のとおり。
検索値:どんな値をもつデータを取り出すかを指定します。(入力は必須)
範囲:検査をする範囲を指定します。(入力は必須)
列番号:検査で値が該当したときに取り出す列を指定します。(入力は必須)

検索の型:近似一致の場合 1もしくはTRUEまたは、完全一致の場合 0もしくはFALSE (入力は任意)

それぞれの引数を具体的な値におきなおすと、以下のような数式です。

Vlookup関数をつかった数式(O2セルに入力する)

=VLOOKUP(22,$A$2:$L$58,COLUMN()-14,0)

数式を見ると分かりますが、一つめの引数(検索値)に22を指定していますので、IDが22の生徒が抽出されます。
余談ですが、数式を他の列に貼り付けるときに、引数の列番号を書きかえなくて良いように、COLUMN関数で列数を取得しています。こちらの数式をP2セルから右側の列の終点(Z列)まで入力します。

実行結果は以下のとおりです。

ワークシートでVlookup関数をつかった実行結果画面

IDが22の宮本 優香さんのデータが取り出されました。
Vlookup関数をあつかう上での注意点があります。それは、検索値が入っている列よりも右側のデータしか取り出せない点です。言いかえれば、検索値よりも左側にある値は取り出すことができません。

今回は検索値にIDをつかったため、ID列より右側にある、すべてのデータを取り出すことができています。
もし仮に、名前の”宮本 優香”という文字列を検索値にしたら、ID列の22は取り出せません。
※列の構成を入れ替える方法で、Vlookup関数はつかえますが、他の関数をつかうことで解決するため、Vlookup関数にこだわりがなければ無理してまでつかう必要はありません。Vlookup関数の代わりにつかえる関数についてなどは、他の記事でも紹介しているのでそちらを参考にしてください。

Vlookup関数のおさらいのさいごに

さて、前項でエクセルのVlookup関数についておさらいをしました。マイクロソフトのサポートにもあるようにVlookup関数の改良版である、Xlookup関数(Microsoft 365で使用可能)などや、他の関数の組み合わせで同じことが実現できるため、現在はあまりつかわれることがなくなってきている関数ですが、その機能は上位互換が作られるほど人気であることがうかがえます。

マクロでVlookup関数を使うと処理が遅い?便利なVlookup関数は使いたいけど、処理が遅くなるのは避けたい。そんな方法が知りたい人はこちらの記事を参考にマクロ開発してみてはいかがでしょうか。

PCでスキルアップをしたい・Excelをしっかり学んで社内の評価を高めたい人は必見!
実務をプロから学べる「ユースフル」は講座の動画は永年見放題。安心のQ&A機能で分からないを解決。

VBAでVlookup関数をつかったマクロの基本的な使い方

VBAでVlookup関数をつかうマクロ

ここからはVlookup関数をVBA(マクロ)であつかう方法を紹介していきます。

まずは、VBA(マクロ)の開発ができるように、VBEを起動しましょう。
VBEの起動方法を忘れた、わからない人については、以前の記事を参考にすすめてください。

VBEを起動して「標準モジュール」の作成が完了すると、いよいよコードを入力していきますが、VBAであっても、構文はエクセル関数のVlookup関数と大きく異なる部分はありませんので、次項では軽く触れるくらいに留めておきます。

VBAでVlookup関数をつかうときの構文(WorksheetFunction.VLookup メソッド)

WorksheetFunction.VLookup (検索値, 範囲, 列番号, 検索の型)

VBAでワークシート関数を呼び出すときは、WorksheetFunctionクラスの後に呼び出す関数名と引数を指定します。
引数の検索値、範囲、列番号は必須で、検索の型は省略できます。省略した場合は、True(近似一致も含める)の指定と同じです。

それでは、Vlookup関数をあつかったサンプルコードを紹介しますが、こちらでもワークシート関数と同じで、テスト結果一覧から、IDを検索値にしてデータを取り出すサンプルコードとします。

Sub vba_VLookup_sample()

Dim ro As Long
Dim key As Long
Dim name As String

'インプットボックスに入力された文字列を検索値として変数keyに代入する
key = Application.InputBox("検索値となるIDを入力してください", "検索値を入力", Type:=1)

With ThisWorkbook.Worksheets("テスト結果一覧 (2)")
    ro = .Cells(Rows.count, 1).End(xlUp).row'最終行を変数roに代入する
    
    .Range("O2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 1, False)
    .Range("P2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 2, False)
    .Range("Q2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 3, False)
    .Range("R2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 4, False)
    .Range("S2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 5, False)
    .Range("T2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 6, False)
    .Range("U2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 7, False)
    .Range("V2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 8, False)
    .Range("W2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 9, False)
    .Range("X2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 10, False)
    .Range("Y2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 11, False)
    .Range("Z2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 12, False)
    
    name = .Range("P2").text
    
    MsgBox "入力されたIDより「" & name & "」さんのデータを取り出しました。", vbOKOnly, "抽出結果"
End With

End Sub

長文ですが、中身はシンプルなので順番に説明していきます。
このサンプルコードは3つのブロックで構成されていて、コードは上から順番に変数の宣言ブロック、引数となる変数の代入ブロック、Vlookup関数の実行ブロックです。

Vlookup関数の引数である検索範囲の行数として利用するための変数”ro”を宣言します。

インプットボックスに入力された文字列を代入する変数”key”を宣言します。

処理完了時のメッセージボックスに表示する文字列を代入する変数”name”を宣言します。ユーザーに処理の完了したこと通知するメッセージを作るために使います。

サンプルコードでは、InputBoxメソッドでユーザーにIDの入力を促します。Type引数をつかって入力を数値に制限し、意図しない文字列などが入力がされる可能性への対処をしています。

検索範囲の行数を取得して変数”ro”に代入しているコードです。

処理完了時のメッセージを表示するために、文字列を変数”name”に代入しているコードです。

InputBoxメソッドやType引数について詳しく知りたい人はこちら

続いて、変数roですが、最終行の取得で行数が代入されます。
こちらの表の最終行は58ですので、変数roには58が代入されます。

VBAで表の最終行・列を取得する方法についてはこちら

さいごに変数nameは、Vlookup関数の実行でP列に入力された氏名を代入しています。

VLookup関数をつかって1列目(ID)の値を取り出して、結果を出力するセルに指定した(O2)に入力します。このコード以降、VLookup関数のコードが複数行ありますが、基本的には同様の処理となります。

赤色の部分がそれぞれ異なる値を指定していますが、これにより検索値との比較で該当したデータの何列目の値を抽出して、どこのセルに入力するかを指定する部分を指定しています。

VLookup関数の検索範囲と列番号のイメージ

VLookup関数の引数にある列数は、検索範囲の1番を1列目として数えてね。

実行結果

Vlookup関数をつかったVBAマクロの実行結果画面

ワークシート関数のサンプルと同じ、IDが22の生徒のデータを取り出せました。しかし、このコードでは、Vlookup関数でエラーが発生する可能性がひそんでいます。また、そのエラーは意図してエラーを発生させるつもりでなくても、日常の業務において当然のように起こることが想定されるケースと言えます。
つまり、ここで書いたコードだけではエラー対処が不十分であると言えるので、少しアップデートしておかなればなりません。

それでは、あえてエラーを発生させて、どんなエラーなのか確認してみます。

実行時エラーが発生しましたが、こちらは検索値が見つからなかったことが原因で発生します。さきほどこのサンプルコードは、エラー対処が不十分と書いたのは、このプログラムを利用する人が、検索値として指定する数値が表の中に存在するIDか、存在しないIDかを事前に知った上でプログラムを利用することが前提となっている点です。

これを誰もが一度は経験のある場面で例えるなら、何かのサービスやアプリで、新しくアカウントを作成するときです。ユーザー(あなた)は登録処理をしたときに「既に登録されているので、他のアカウント名で登録してください」などの通知を受け取ることで、はじめて希望のアカウント名が既に登録されていることを知ることになるので、登録処理を実行する前は、アカウント名が存在する、しないかはわかりません。

プログラムを作成する人にとっては、当然の内容であっても、利用する人(ユーザー)はアプリ開発やプログラムの知識を保有していなくても安心してプログラム(機能やサービス)を利用できるようにすることが大切です。

検索値が見つからなかった場合のエラーを含め、Vlookup関数をつかった場合に起きやすいエラーについての紹介と対処方法については次の項にて紹介します。

独学の学習効率でお悩みの人必見!
<動画学習見放題サービス>

初心者にやさしいチューターなら今すぐにはじめられる

オンラインで学習したい
プログラミングで副業をはじめたい
パソコン・エクセルの学習をしたい

VBAのVlookupマクロで発生するエラーと対処方法

ここでは、VBAでVlookup関数をあつかうときに発生しやすい2つのエラーと、その対処方法について紹介していきます。発生しやすいエラーの原因は「検索値が見つからなかった」場合と「検索値がdate(日付)型」の場合です。
それぞれについて対処方法とあわせてみていきましょう。

検索値が存在しない場合のエラーと対処方法

[On Error Resume Next]による対処方法

On Error Resume Nextは、エラーが発生したとしても中断せずに、エラーが発生した処理の次の処理から実行を継続する文です。簡単に言えばエラーになってもプログラムをとめない命令のことです。

On Error Resume Nextの使いかたは以下のように書きます。

On Error Resume Next

 .Range(“O2”) = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 12)), 1, False)

On Error GoTo 0

On Error Resume Next (エラーを無視するスイッチを入れる)から On Error GoTo 0 (エラーを無視するスイッチを切る)までの間の処理でエラーが発生しても、その次の処理を実行します。注意しなければならないのは、エラーを無視するスイッチをONにしたら、その後OFFにすることをおぼえておきましょう。

検索値が日付(date)型の場合のエラーと対処方法

検索値にdate(日付)型を指定した場合にも、実行時エラーが発生します。具体的なケースとしては以下の表から、日付を検索値としてデータを取り出すときなどです。

VBAのVlookup関数で検索値を日付に指定してデータを抽出する例を解説する上で使用しているデータリスト

日付を検索値としてE2・F2・G2セルを入力してみましょう。
コードは以下のとおり。

入力内容

Option Explicit

Sub vba_VLookup_sample_date()

Dim ro As Long
Dim key As Date

key = Application.InputBox("検索値となる日付を入力してください", "検索値を入力")

With ThisWorkbook.Worksheets("3月カレンダー")
    ro = .Cells(Rows.count, 1).End(xlUp).row
    .Range("E2:G2").ClearContents
    
   
    .Range("E2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 1, False)
    .Range("F2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 2, False)
    .Range("G2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 3, False)
    
    
    MsgBox "日付により、抽出が完了しました。", vbOKOnly
    
End With

End Sub

変数Keydate(日付)型だとエラーが発生するため、以下のようにString(文字列)型に書きかえておきます。あわせて検索値が検索する範囲でみつからなかった場合は、別の原因でエラーが発生するので、こちらの対処もかねてOn Error Resume Nextも書き加えておきます。

入力内容(更新)

Option Explicit

Sub vba_VLookup_sample_date()

Dim ro As Long
Dim key As String

key = Application.InputBox("検索値となる日付を入力してください", "検索値を入力")

With ThisWorkbook.Worksheets("3月カレンダー")
    ro = .Cells(Rows.count, 1).End(xlUp).row
    .Range("E2:G2").ClearContents
    
    On Error Resume Next
    .Range("E2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 1, False)
    .Range("F2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 2, False)
    .Range("G2") = WorksheetFunction.VLookup(key, .Range(Cells(2, 1), Cells(ro, 3)), 3, False)
    On Error GoTo 0
    
   If .Range("E2") <> "" Or .Range("F2") <> "" Or .Range("G2") <> "" Then
       MsgBox "日付により、抽出が完了しました。", vbOKOnly
   Else
       MsgBox "入力された検索値によるデータはみつかりませんでした。", vbOKOnly
   End If
    
End With

End Sub

これで、InputBoxメソッドでユーザーから入力された値をString(文字列)型に代入して、Vlookup関数の引数の検索値にしています。この状態で実行した結果を確認してみましょう。

実行結果

画像のとおり、InputBoxメソッドで「2023/3/14」を検索値に入力した結果、E2セルからG2セルに実行結果が正しくかえってきたことが確認できました。

VBAでVlookup関数をつかわずに同じ処理を実行する方法

前項までで、VBAでVlookup関数をつかう方法を紹介していきました。しかし、これまでの内容とサンプルコードでもおわかりのとおり、Vlookup関数はデータリストから検索値をつかってデータを取り出すことをしてくれる機能となっていますが、この機能を実現するために、必ずしもVlookup関数をつかう必要はありません。

ワークシート関数の場合でも同じですが、VBAで同等の機能を実装するのであれば、他の選択肢でも同じ機能の実装が可能なので、そちらを選択するほうが無難と言えます。

ここでは、Vlookup関数をつかわずにVlookup関数と同じ機能を実装する方法を紹介していきます。

For文とIf文をつかってVlookup関数と同じ機能を実現するマクロ

VBAでVlookup関数と同じ機能を実現するためのマクロを、For文とIf文をつかって書いてみます。

入力内容

Option Explicit

Sub vba_like_a_Vlookup_sample()

Dim ro As Long, i As Long
Dim key As String

key = Application.InputBox("検索値となる日付を入力してください", "検索値を入力")

With ThisWorkbook.Worksheets("3月カレンダー")
    ro = .Cells(Rows.count, 1).End(xlUp).row
    .Range("E2:G2").ClearContents
    
    For i = 2 To ro
        If .Cells(i, 1).text = key Then
            .Range("E2") = .Cells(i, 1).text
            .Range("F2") = .Cells(i, 2).text
            .Range("G2") = .Cells(i, 3).text
        End If
    Next i
       
    If .Range("E2") <> "" Or .Range("F2") <> "" Or .Range("G2") <> "" Then
        MsgBox "日付により、抽出が完了しました。", vbOKOnly
    Else
        MsgBox "入力された検索値によるデータはみつかりませんでした。", vbOKOnly
    End If
End With

End Sub

大きく変わっているのは、データリスト内を検索値で検索する処理をVlookup関数ではなく、繰り返し処理(For Next)と条件判定(If)の文におきかえた部分です。繰り返し処理をいれるために変数iを新たに追加で宣言していますが、その他はVlookup関数を呼び出すコードと比較してみてもほぼ同じで、むしろVlookup関数をつかわないほうがコードがすっきりして読みやすくなっています。
なお、このコードなら変数keydate(日付)型でも、検索値がリスト上に存在しなくてもエラーは発生しません。

繰り返し処理はこちらの記事で、条件判定の処理の書き方についてはこちらの記事で紹介していますので、詳しくはそちらをご覧になってください。

データリスト(表)を配列にしてVlookup関数同等の機能を実現するコード

前項の繰り返し処理と条件判定をつかったサンプルコードをさらにアップデートして、同じ機能を実現することも可能です。このコードでは、ワークシート上のデータリスト(表)を2次元配列に変える方法です。配列とは、複数の変数をまとめてあつかうことができる便利なもので、大量のデータを検索・処理するなどの場面では、セルの範囲上で処理をするよりも、プログラムの処理速度が速くなるなどのメリットもあります。

ただし、コードを実装する難易度が高くなるので、詳しくは別の記事で紹介したいと思います。ここではそういった手段もあることを知っておく程度で問題ないと思います。

入力内容

Option Explicit

Sub vba_like_a_Vlookup_sample2()

Dim ro As Long, i As Long
Dim key As String
Dim table As Variant

key = Application.InputBox("検索値となる日付を入力してください", "検索値を入力")

With ThisWorkbook.Worksheets("3月カレンダー")
    ro = .Cells(Rows.count, 1).End(xlUp).row
    table = .Range(Cells(2, 1), Cells(ro, 3))
    .Range("E2:G2").ClearContents
        
    For i = 1 To UBound(table, 1)
        If table(i, 1) = key Then
            .Range("E2") = table(i, 1)
            .Range("F2") = table(i, 2)
            .Range("G2") = table(i, 3)
        End If
    Next i
       
    If .Range("E2") <> "" Or .Range("F2") <> "" Or .Range("G2") <> "" Then
        MsgBox "日付により、抽出が完了しました。", vbOKOnly
    Else
        MsgBox "入力された検索値によるデータはみつかりませんでした。", vbOKOnly
    End If
End With

End Sub

セル範囲を2次元配列に変えるための変数tableをVariant(万能)型であらたに宣言しています。その後、table = .Range(Cells(2, 1), Cells(ro, 3))のコードで、データリスト(表)の最終行・列まで2次元配列に変える処理をしています。

そして、変数keyに代入された検索値をつかって、2次元配列tableの1列目の値(日付)に対して、繰り返し判定していきます。検索値と同じ値が入っている行まで到達すると、tableの1列目、2列目、3列目の値をワークシートセルのE2、F2、G2セルに入力します。

ブログをはじめてアフィリエイトをはじめるならASP選びが大事!
まずは、あんしんして利用ができる老舗のA8への登録がおすすめ

まとめ

今回はVBAでVlookup関数のあつかうときの基本的な書き方と、発生しやすいエラーとその対処方法について紹介してきました。Vlookup関数はエクセルユーザーの中でも人気がある関数ですが、それは膨大なデータから検索値を指定してデータリストを検索し、関連するデータを抽出するといった実務上の作業でとても便利な機能だからだと言えます。

しかし、この便利な機能を実現する手段は一つではなく、複数の手段で検索からデータを抽出することが実現でき、Microsoftサポート内でも、Vlookup関数に関するページにおいて、下記の書き込みがあるくらいです。

ヒント: 新しい XLOOKUP 関数を使用してみてください。これは、VLOOKUP の改良版であり、任意の方向で機能し、既定で完全一致を返します。これにより、以前の関数よりも使いやすく便利になります。

Microsoftサポート

また、VBAにおいてもワークシート関数をつかったマクロをつくることは可能ですが、VBAであつかう場合は、VBAならではの実装方法があることも確かです。
エクセル関数を扱える人は、その便利さや有能さを理解しているので、VBAでマクロをつくるに場面においても、ワークシート関数を呼び出して実現する方法をさきに思いつくのだと思われます。
ですが、今回のVlookup関数のように、VBAで実現した場合、コードの冗長化による可読性の低下や、エラー発生のリスクが上がるなどのマイナス要素が多い場合は、同等の機能を実現できる他の書き方を検討してみることで、状況によっての使いわけが可能となり、さまざまな場面に対応するVBAに関する幅広い知識やスキルが身につきます。

他にも、本記事の最後にすこしだけ紹介した「配列」を用いた検索であったり、ここで紹介していない「辞書(連想配列)」と言われるものを利用する方法もありますが、スキルの習得や向上が目的ではなく、期限のある目下の課題をクリアする上では、今回紹介した実現難易度が比較的低いものを選択する方法もあるかと考えます。


今回はここまで
さいごまで読んでいただきありがとうございました。

コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。

コメント

タイトルとURLをコピーしました