すぐに役立つエクセルVBAマクロ集 FAQ VLOOKUPで検索

　すぐマク　YNxv9591

VLOOKUPで検索可能にするには？

Ｑuestion 50.6　値の操作

・エクセルで作った顧客名簿と、
・同じくエクセルで作られた都道府県別の資料が入力されているデータと、
・顧客に送信するメール文書のフォーマットがあります。

顧客名簿の顧客の住所（都道府県）に合わせて、都道府県別の資料の中から必要なデータを検索し、それを顧客に送信するメール文書のフォーマットの規定の所に書き出せるように、顧客名簿にボタンを作りたいのです。

ただし、顧客名簿の住所が千葉だったとすると、都道府県別の資料からは関東の資料を検索してくるようにしたいのです。顧客名簿の住所に入っているのは○○県○○市までです。でもたまに○○市しか入っていないものもあります。
都道府県別の資料には都道府県名は入っておらず、地方名ととってくる値のみです。

VLOOKUPだと同じ値なら検索できますが違うと検索できないので、マクロをくまないといけないかなと思うのですが・・・。

Ａnswer　　Excel 97以上

質問文からワークシートを再現してみましょう。

　顧客名簿　　　　　　　　　　　　　　　　　都道府県別資料
顧客名簿シート

左図のシートの住所の中の都道府県名を基にして、右図のシートから対応する値（りんご、みかん、バナナ）を取り出したいという課題ですね。

例えば「千葉」だったら「りんご」を取り出すことになりますか。

この二つのシートを人間が見れば、例えば静岡県は東海地方だから値としては「みかん」を取り出してくれるでしょうが・・・

情報テーブル

人間が処理する場合にも言えることですが、関東、信越、北陸、東海、中部、関西などと、地方の分類方法には色々あります。コンピュータ処理する場合には、各都道府県がどの地方に属するのかを明確にした情報テーブルが不可欠です。

上図の都道府県別資料シートを改良して、右図のとおり「情報」シートを作ってみました。

VLOOKUP関数の書式

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

引数の指定方法

・検索値…　検索したい値が入っているセル番号を指定する
・範　囲…　情報テーブルのセル範囲を指定
・列番号…　情報テーブルの目的の値が入っている列を指定
・検索の型…　完全に一致する値だけを検索するか、近似値を含めるかを指定
　 TRUE を指定するか省略すると、検索値が見つからない場合に、検索値未満で最も大きい値が使用される。
　 FALSE を指定すると検索値と完全に一致する値だけが検索され、見つからない場合はエラー値 #N/A が返される。

顧客名簿と情報シートのデータで引数を指定してみます。
　検索値…　顧客名簿シートのB2セルの値の左から2文字つまり、千葉
　範　囲…　情報シートのA2:C13セル
　列番号…　3
　検索の型…　FALSE

問題点

VLOOKUP関数がなんとなく使えそうですね。しかし、困ったことがあります。それは、「たまに○○市しか入っていないものもあります」ということです。
府県庁所在地で府県名と同一の市に限って、府県名が省略されているだけならば支障はありませんが、それ以外の市でもあるならば、データの入力ルールを改良するか、情報テーブルに市名も設定するか、マクロでエラー処理など救済対策するかしなければなりません。

VLOOKUP関数をマクロで使用する方法

VLOOKUP関数は一般にワークシートのセルに数式入力（マクロでも可能）して使います。その方法は省略しますが必要ならば、「指定された値から別表を検索して目的の値を取り出すには」のページを参照してください。
ワークシート関数の中でVBAで使用できるものは沢山あり、VLOOKUPも使用可能です。上の例をマクロに組むと下記のようになるかと考えます。

'-----------------------------------------------------------------------------------------
Sub 都道府県名で情報テーブルを検索して値を取得する()
Dim 範囲 As Range
    Set 範囲 = Worksheets("情報").Range("A2:C13")
    列番号 = 3
    検索値 = Left(Worksheets("顧客名簿").Range("B2"), 2)
    値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
    MsgBox 検索値 & "は " & 値
End Sub
'-----------------------------------------------------------------------------------------

このマクロは顧客名簿シートのB2セルの値だけを対象にしていますが、実務では B列の下端セルまでのデータを対象にする必要があるでしょう。そのための改造方法がわからなければ、「シートの下端と右端を調べて範囲選択する」のページを参考にして考えてください。

また、VLOOKUP関数の「検索の型」に FALSEを指定しているので、検索結果としてエラー値 #N/Aが返される場合があり得ます。エラー対策の方法を知りたい場合は、「Search」のページのテキストボックスに「エラー」と入力して検索し、該当ページを参考にしながら研究してみてください。

蛇足かも知れませんが、情報シートの都道府県名は頭2文字で十分です。3文字名（神奈川・和歌山・鹿児島）を正確に作成すると、上例サンプルマクロの5行目の検索値を設定するコードが、ややこしくなるだけで実益がありません。また、既にお気づきかと思いますが、情報シートの「地方名」は不要かも知れません。

この他に、「メール文書のフォーマットの規定の所に書き出す」とか「顧客名簿にボタンを作りたい」と、いろいろ工夫すべき事項があります。すべてをクリアして完成させると、きっと、実力が向上するでしょう。期待してます。

http://www.geocities.jp/happy_ngi/