Office TANAKA - Excel VBA Tips[VLOOKUP関数を使う]

VBAからWorksheetFunctionを使って「VLOOKUP関数」を活用しようという話です。な～んだ、それほど難しくないっしょ…と思いきや、ここには思わぬ"落とし穴"が存在しますので、お楽しみに♪

まずは簡単なやつからいきましょう。ここでは下図のような表でやってみます。

セルの中にVLOOKUP関数を入力するのなら、こんな感じですか。

簡単ですね。これをVBAでやると

Sub Macro1()
    Range("D2") = WorksheetFunction.VLookup(Range("D1"), Range("A2:B6"), 2, False)
End Sub

こうなります。検索する値(ここでは"桜井")がセルに入っているのではなく、マクロ実行時に受け取るのでしたら、たとえばこんな感じでしょうか。

Sub Macro2()
    Dim A As String
    A = InputBox("名前は？")
    MsgBox WorksheetFunction.VLookup(A, Range("A2:B6"), 2, False)
End Sub

InputBoxを使うときは、必ず「キャンセルされたとき」のことを想定しなければなりません。あと、VLOOKUP関数がエラーになる(検索値が見つからない)可能性もありますから、そのへんも手を打ちましょう。

Sub Macro3()
    Dim A As String
    A = InputBox("名前は？")
    If A = "" Then Exit Sub
    With WorksheetFunction
        If .SumIf(Range("A:A"), A) = 0 Then Exit Sub
        MsgBox .VLookup(A, Range("A2:B6"), 2, False)
    End With
End Sub

それほど難しくはありません。そう、ここまではｗ問題は次です。

検索値に日付を指定するケース

ここからは、下図のような表を使います。A列にはシリアル値が入力されています。

じゃ、まずはさっきと同じように、検索値がセルに入力されているとします。

これも、さっきと同じ

Sub Macro4()
    Range("D2") = WorksheetFunction.VLookup(Range("D1"), Range("A2:B6"), 2, False)
End Sub

で、うまくいきます。では、次のようになっていたらどうでしょう。

実務ではよくある話です。A列に入力されているのは、すべて2020年の日付なのですから「毎回、年を入力するのは面倒くさい！月と日だけ指定すれば分かるっしょ」みたいに。ユーザーは、わがままです。まぁ、こういうときは一般的に、セルD1やセルE1に入力規則のリストを設定したりします。とにかく、与えられた「9」と「4」を使って「2020/9/4」という日付を作らなければなりません。こんなとき、絶対にしてはいけないのは、文字列で「2020/9/4」を作るという発想です。やってみましょう。

もちろんマクロでも失敗します。

日付と文字列は、まったく違います。人間は頭が良いですから「"2020/9/4"」という文字列を日付と判断できますが、Excelは人間ほど頭が良くありません。こういうときは、検索値として、正しく日付(シリアル値)を指定しなければなりません。ワークシート上でしたら、こんな感じです。

なるほど、関数でシリアル値を作ればいいんですね。では、マクロでも同じようにやってみましょう。ちなみに、年月日を表す3つの数値からシリアル値を作成するのに、ワークシート上ではDATE関数ですが、VBAではDateSerial関数です。

Sub Macro6()
    Range("D2") = WorksheetFunction.VLookup(DateSerial(2020, Range("D1"), Range("E1")), Range("A2:B6"), 2, False)
End Sub

エラーです・・・なぜでしょう？まったく同じ発想なのにエラーです。DateSerial関数を、VLOOKUP関数の引数として、直接指定したのがいけなかったのでしょうか。そんなはずはありませんが、一応試してみましょう。DateSerial関数の結果を変数に入れてみます。

Sub Macro7()
    Dim A As Date
    A = DateSerial(2020, Range("D1"), Range("E1"))
    Range("D2") = WorksheetFunction.VLookup(A, Range("A2:B6"), 2, False)
End Sub

しかし、これでもエラーです。こうなると、もう日本国民全員が「わけわかんないｗ」ってなると思います。でも、これ惜しいんです。次のように、ほんのちょっと変えると動作するんです。

Sub Macro8()
    Dim A As Long
    A = DateSerial(2020, Range("D1"), Range("E1"))
    Range("D2") = WorksheetFunction.VLookup(A, Range("A2:B6"), 2, False)
End Sub

違い分かりますか？変数の型を、Date(日付型)からLong(長整数型)に変えました。こっちなら成功します。これ、ずっと昔、私もハマりました。最初は納得できなかったです。いや、正直いまでも納得はしていません。でも「VLOOKUP関数ってのは、そういうふうに作られているんだ、そうなんだ、うん」と考えるのが得策です。それ以上の考察をしたところで、できないものはできないですし、VLOOKUP関数の仕様だと飲み込むのがいいと思いますよ。ちなみに、これCOUNTIFなど別の関数でしたら、何も問題は起こりません。次のコードは、すべて成功します。

Sub Macro9()
    MsgBox WorksheetFunction.CountIf(Range("A:A"), "2020/" & Range("D1") & "/" & Range("E1"))
End Sub

Sub Macro10()
    MsgBox WorksheetFunction.CountIf(Range("A:A"), DateSerial(2020, Range("D1"), Range("E1")))
End Sub

Sub Macro11()
    Dim A As Date
    A = DateSerial(2020, Range("D1"), Range("E1"))
    MsgBox WorksheetFunction.CountIf(Range("A:A"), A)
End Sub

Sub Macro12()
    Dim A As Long
    A = DateSerial(2020, Range("D1"), Range("E1"))
    MsgBox WorksheetFunction.CountIf(Range("A:A"), A)
End Sub

もちろん、ワークシート上でも可能です。

COUNTIF関数などは、検索値を文字列結合で作成してもうまくいきます。てゆーか、ほかにもMATCH関数とか、あれこれ試したんですが、どうやらSUMIF関数やCOUNTIF関数など"○○IF関数"だけ検索の仕組みが異なっているようです。一言で言えば"緩い"ですｗどんな形式でも検索してくれます。対して、VLOOKUP関数とかMATCH関数とか、新しく追加されたXLOOKUP関数などで日付を検索値に指定するときは、思わぬところでエラーになります。そうした、"○○IF関数"たちの挙動に関しては、また別のコンテンツでご紹介します。いずれにしても、VBAで、VLOOKUP関数の検索値に日付を指定するとき。そして、その日付を何らかの方法で作らなければならないときは、作成したシリアル値(ここでは、DateSerial関数の結果)を、一度Long型の変数に入れると覚えておきましょう。ちなみにですけど、どうしても変数を使いたくないのなら、次のようにトリッキーな方法もあります。

Sub Macro13()
    Range("D2") = WorksheetFunction.VLookup(DateSerial(2020, Range("D1"), Range("E1")) * 1, Range("A2:B6"), 2, False)
End Sub

これも、仕様的には納得できないんですが、DateSerial関数の結果に1を乗算したり、0を加算するなど、何らかの演算を行うと、VLOOKUP関数の検索値として使えます。演算の結果、内部でLong型に変換されるからでしょうね。でも、何もこうして1行で書かなければいけない必要はないし、この「* 1」の意味は難解です。可読性を考えたら、一度Long型に入れる方法をお勧めします。もちろん、マクロ内には「なぜLong型にしたのか、Date型にしてはいけない理由」を、しっかりコメントで残すようにしてくださいね。後任者のためにも。