VBAで関数VLOOKUPの弱点を克服【 関数VLOOKUPの使い方も紹介 】

エクセル業務で関数VLOOKUPはメジャーで最もよく使われる関数の一つです。

特定の値を検索して、それに対応する値を転記するような作業に関数VLOOKUPは最大限効果を発揮し、私の職場でも関数VLOOKUPがよく使われています。

しかし

とっても便利な関数VLOOKUPにも弱点がある

そんな弱点をVBAを使って克服してみようと思います。

 

関数VLOOKUPとは

指定した範囲の一番左の列を上から検索し、検索値と一致または近い最大値の列番号にある値を取り出す。

 

関数VLOOKUPのセルへの入力方法

 

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が作れます。

 

VBA初心者が効率化マクロを導入する方法

 

VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！

このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。

 

こんな問題を解決する方法がこちら

• マクロの開発を外注に依頼する
• マクロが設定されたエクセルファイルをダウンロードする
• 参考書やネットでVBAスキルを身に付ける

 

VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。

 

 

 

 

VBAは

他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

という特徴があります。

 

毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。

 

マクロの開発を外注に依頼する

マクロの開発を外注に依頼する導入方法は

VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法

です。

 

「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」

こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。

 

 

 

デメリットは、費用がかかることだけ。

開発内容、依頼先によってかかる費用は大きく異なります。

私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。

 

 

ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。

開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、

費用が安い！スピード納期！！

でやらせてもらっています。

 

ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。

 

ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。

 

 

マクロが設定されたエクセルファイルをダウンロードする

VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。

 

たとえば、

A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、

『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。

 

実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら

 

 

 

 

『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、

VBA初心者でもマクロを導入できる、費用が安い、

というメリットがあります。

 

しかし

VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい

というデメリットもあります。

 

 

個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。

 

今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。

 

 

 

参考書やネットでVBAスキルを身に付ける

先程も紹介しましたが、

VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい

プログラミングです。

 

言い換えると、

情報量が豊富なので自分で勉強できちゃう

ということです。

 

私のVBAスキルはほとんど独学で身に付けたもので、

考えた処理のほとんどをVBAで表現できるレベルにまで到達した！

と思っています。

 

私の作ったマクロをプロのプログラマーが見れば、

お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！

と感じるはずです。

 

でも、

私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？

 

独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、

業務内容に合ったマクロを開発できるようになれます。

 

独学でVBAスキルを身に付けるコツは、

エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる

です。

 

とは言っても、全くの知識0の方は何をすればいいかわからないですよね。

 

VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。

 

VBAの知識0の方でもすいすい読み進められる内容になっています。

やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。

 

実際に私はこの参考書を読んでからVBAの勉強を始めました。

 

 

 

VBAの魅力を発見した後は、

ひたすら自動化したい処理を実現するコードを書くだけ

です。

 

【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。

 

実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると

自然といろんなマクロが作れるようになっているはずです。

 

VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。