VBAで関数VLOOKUPの弱点を克服【 関数VLOOKUPの使い方も紹介 】
エクセル業務で関数VLOOKUPはメジャーで最もよく使われる関数の一つです。
特定の値を検索して、それに対応する値を転記するような作業に関数VLOOKUPは最大限効果を発揮し、私の職場でも関数VLOOKUPがよく使われています。
しかし
とっても便利な関数VLOOKUPにも弱点がある
そんな弱点をVBAを使って克服してみようと思います。
目次
関数VLOOKUPとは
指定した範囲の一番左の列を上から検索し、検索値と一致または近い最大値の列番号にある値を取り出す。
関数VLOOKUPのセルへの入力方法
＝VLOOKUP（検索値,範囲,列番号,検索の型）
検索値・・・探したい値
範囲・・・検索する範囲
列番号・・・範囲の中で検索値の列から何列の値を取り出すか
検索の型・・・「True」か「False」
Trueは検索した結果、一致する値がないとき、検索値を超えない最大値を取り出す。
Falseは完全に一致する値を検索するときに使います。完全に一致するものがないときはエラーとして表示される。
私の職場で関数VLOOKUPを使うときは完全一致となるデータを探すことがほとんどなので、Falseが設定されています。
関数VLOOKUPのサンプル
上記のような例を関数VLOOKUPを使って入力してみます。
H4～K4のセルに関数VLOOKUPの数式を入力します。
#N/Aが表示されていますが、今回は気にしないことにします。
設備IDを入力すると、他のセルにちゃんと値が入力されました。
入力の間違いがなくなり、作業の効率化に繋がるとても便利な関数です。
今回のようなシンプルな例のときは問題ないのですが、もう少し複雑な操作が要求されると関数VLOOKUPでは対応できなくなることがます。
関数VLOOKUPの弱点
検索値が取り出したい値よりも右側にあると、関数VLOOKUPでは対応できない
関数VLOOKUPとは検索値の列から右に何列目の値を取り出すのかというもの。なので検索値より左側にある値は取り出せない。
扱うデータが大量にあったり、複雑なデータを扱う場合、取り出したい値が検索値より右側にあるとは限りません。
実際、私の職場でも関数VLOOKUPでは対応できない場面が多々あります。
関数VLOOKUPでは対応できないサンプル
上記の例で検索値よりも左側にある値を関数VLOOKUPで取り出せるか試してみます。
上記のように検査日の取り出しで、3パターンの範囲と列番号の組み合わせを試しましたが、取り出すことが出来ませんでした。
他にも検索値が一番左になるように範囲を指定しなければエラーになることがわかりました。
全く同じ検索値が2つ以上あるとき関数VLOOKUPでは対応できない
指定した範囲を上から検索して検索値と一致する値を取り出すというのが関数VLOOKUPなので、同じ検索値が2つ以上あるとき上の行にある検索値で値を取り出してしまいます。
2つ目以降の値は取り出すことができません。
仕事では大量のデータを扱うため、1つの検索値に対して取り出したい値が複数になることってありますよね。
例えば、1つの検索値に対して、2019年の値及び2018年の値を取り出したいという場合！
関数VLOOKUPでは対応できない例を紹介します。
上記は設備IDに「1006」と「1011」をいれて、4種類の値を取り出したい。という例です
関数VLOOKUPを駆使して作ってみます。
設備IDに「1006」を入力した場合、上の行にある値しか取り出せません。
設備IDに「1011」を入力した場合、異なった2つの値をそれぞれ取り出すことができました。
関数VLOOKUPの範囲の指定を変更すれば検索値が全く同じでも異なる値を取り出すことは可能ということがわかりました。
しかし大量にあるデータのなかで、範囲の指定をそれぞれ変更して作る、というのはとても手間のかかる作業なので本末転倒な気がします。
この弱点に気づかず、なぜできないのだろう、、と長いこと迷った時期がありました、、、
VBAを使って関数VLOOKUPの弱点を克服
1つ目の弱点（検索値よりも右側にある値しか取り出せない）を解消することはとても簡単です。
先ほど紹介した例でEXcel VBAを作ってみます。
関数VLOOKUPでは検索値である設備IDよりも左側にある検査日を取り出すことはできない。という例です
これをFor～Nextと関数IFを組み合わせたプログラムで関数VLOOKUPの弱点を解消します。
上記のようにプログラムを作ってみます。
実行すると、、
1つ目の弱点はこのFor～Nextと関数IFの組み合わせたプログラムで解消できました。
VBAを使えば関数VLOOKUPを使わなくても、簡単に好きな箇所の値を取り出すことができます。
2つ目の弱点（同じ検索値が2つ以上ある場合、上の行にある検索値に対応する値しか取り出せない）を解消するプログラムはちょっと複雑になります。
先ほど紹介した例で弱点を解消できるか試してみます。
ちなみに設備ID「1006」を入力して、1つ目の弱点を解消したプログラムを実行すると、次のような結果になります。
関数VLOOKUPでは1つ目の検索値に対応する値しか取り出せなかったのに対し、このプログラムでは2つ目(最後の検索値）に対応する値しか取り出せない。
「F8」でVBAの実行を行うと、なぜこのような結果になるのかがわかります。
検索値が1つ目の値を取り出した後、検索値が2つ目の値を取り出して上書きしている。
「F8」を使ったVBAの実行については下記の記事で詳しく紹介しています。
この問題点を解消するVBAを考えていきます。
これで実行してみると
検索値が1つ目の値を取り出すことができました。
次に検索値が2つ目の値を取り出すプログラムを付け加えていきます。
上記のプログラムを追加して実行すると
1つ目と2つ目の検索値に対応する値を取り出すことができました。
2つ目の設備IDの欄が空欄で見栄えが悪い、と感じる場合は次のようにプログラムを1行追加すると問題解決です。
これで同じ検索値が2つ以上ある場合、上にある検索値に対する値しか取り出せない。という弱点を解消することができました。
今回は検索値が2つだけ、ということを想定して
Cells(4+1,〇)＝・・・
というちょっと乱暴なプログラムにしましたが、ここでもFor～Nextを使えば同じ検索値がいくつあっても対応可能なVBAが作れます。
関数VLOOKUPの限界を感じている方へ
今回は関数VLOOKUPの弱点をExcel VBAを使って解消する方法を紹介しました。
関数VLOOKUPはとても便利なもので、私の職場でも多くのエクセルファイルで使用されていますが、私はVBAでプログラムを作る方が簡単かなぁと個人的に思います。
Excel VBAの導入や関数VLOOKUPの弱点でお悩みの方は今回紹介した解消法を是非参考にしてみて下さい。