農2017/8/152019/4/3Excelの表でコードや区分の横に対応する名称を出すときなど、VLOOKUP関数を使います。参照範囲を配列で置き換えるVLookupの高速化という応用的な内容までお伝えします。まとめVLOOKUP関数とは最初に、そもそもVLOOKUP関数とは何なのかを解説します。冒頭で触れたとおり、コードや区分から対応する値を取得するものです。伝票を例にとると、商品コードから商品名や単価を取得すると考えてください。その具体例は以下です。以下の例では、商品コードと数量のみ手入力で、商品名と単価はVLOOKUP関数を使用して取得しています。金額は単価と数量の掛け算、合計はSUM関数を使って自動で計算結果を表示しています。検索方法の指定)検索値は検索したい値、範囲は対応する名称がある範囲、列番号は何列目に欲しい値があるかの指定です。検索方法は完全一致かあいまい検索を指定しますが、通常は完全一致で使用するため、FALSEを指定すればよいでしょう。[使用例]商品名の取得を文章で説明しましょう。B3セルの商品コードに対応する商品名を、C3セルに表示する。商品名は範囲H3セルからJ8セルまでにある。範囲の中で商品名は範囲の2列目にある。あいまい検索はしないのでFALSE。結果、図のオレンジ色の枠にある式ができます。となりのD3セルに単価も取得して設定してみました。オレンジ色と青色の枠内の式を比較すると、動作イメージが明確になるでしょう。これが、Excelの表におけるVLOOKUP関数です。VBAでVLookupを使う方法ExcelVBAにおいて、コードや区分から値を取得する場合は以下のようにします。検索方法の指定)次の例では、B3セルに入力した商品コードに対応する商品名を、表から取得してC3セルに表示しています。Sub[実行結果]いかがでしょうか?キーとなる値からそれに対応する値を取り出す、といった関係データベース的な使い方ができますね。エラーへの対処法VBAでVLOOKUP関数を使ったコーディングをするのは、今まで解説してきたことで十分可能です。実は、それよりもエラーとその対処方法のほうが難しいのです。プロパティを取得できません。」このエラーは特によく起こります。ある理由を順に解説していきます。検索したい値のあるセルと、範囲で指定するセルの型が異なるときに起こります。以下の例では、B3セルを文字列にして型を合わせるとエラーは解消します。参照範囲に値が存在しない検索範囲に値が存在しないときにエラーが発生します。VBAの一般的なエラーの対処法を解説します。次のサンプルコードでは、実行時エラー1004が発生した場合に、C3セルに「値取得不可!」と入れています。err:」は、エラーが発生したら下の方の「err:」というラベルまでジャンプせよという意味です。エラーは1004以外にもありえるので、1004の場合のみ「値取得不可!」と返すようにしているのが「err:」の後のIf文です。実行時エラー1004だけでも「範囲に値が存在しない」以外にもさまざまな原因があるので、あえてザックリと「値取得不可!」としています。以下のコードを実行してみましょう。Sub1004エラーが発生します。(環境によっては、実行時にエラーが出るケースもあります。)検証方法を解説します。コード中のVLOOKUP関数の部分を、セルに式で記述してみましょう。商品名は「#REF!」と表示されており、値が取得できていません。これはVLOOKUP関数の第3引数である列番号が4となっているのが原因です。範囲はE3セルからG8セルまで3列しかないのに、4列目を指定してしまっています。ここでは商品名がほしいので、列番号を2と修正すれば問題は解消できます。想定する最終形で実際に式として記述してみる、これはとても役に立つデバッグ方法です。このデバッグ方法をもう一歩さらに進めてみましょう。ここまでは、ExcelシートのセルにVLOOKUP関数を直接入力していましたが、セルに入力せずにVBAからVLOOKUP関数をセルに入力することもできます。それはRangeオブジェクトのFormulaプロパティを使う方法です。“=数式”これで指定したRangeオブジェクトにVBAから関数式を入力することができます。Sub実行結果:これでExcelシートとVBAの開発環境(VBE)を行ったり来たりせずに、VBAの開発環境だけでデバッグをすることが可能です。Formulaプロパティを使ってVBAからセルに直接関数を入力する方法については、こちらで詳しく解説しています。別シート、別ブックを参照する方法ここまでは同じシート内の参照範囲を検索してきました。参照範囲が大きくなったり、勝手に変更されたくない場合などは検索結果と参照範囲を別シートや別ブックに分ける場合も考えられます。そんな場合は、参照範囲の指定を以下のように記述することで対応できます。Workbooks("ブック名.xlsx").Worksheets(“シート名”).Range((参照範囲)参照先が同じブック内の場合はWorkbooks("ブック名.xlsx").は省略可能です。これは参照先のブックが開いていることが前提になります。Sub実行結果:参照範囲を配列で置き換えるこれまでは参照範囲がExcelシート上のあるセル範囲でした。VBAを使っていると、Excelシート上のセル範囲ではなく、配列を参照範囲に指定したい場合も出てきます。検索する参照範囲を配列にした場合についてみてみましょう。Sub実行結果:まず、配列arrにセル範囲"E3:G8"の値を格納しています。VLookup関数の参照範囲に配列arrを指定しています。配列とセル範囲の値を共有する方法については、こちらで詳しく解説していますので、ぜひ参考にしてください。2019年9月26日VLookupの高速化これまでは、比較的小さいデータをVLookupで参照して検索してきました。1万件や10万件を超える大きなデータから検索を数千回繰り返したい場合もあります。そんな場合はVLookup関数の処理速度も考慮しなければ、時間がかかりすぎてしまうことになります。時間がかかりすぎてしまえば、代わりの手段も考えなければなりません。ExcelではVLOOKUP関数の代わりに、INDEX関数とMATCH関数を組み合わせて使わうこともよくあります。INDEX関数は=INDEX(範囲,縦位置)"縦位置"に"範囲"内の位置を数値で入力するとその値を返します。配列のインデックス番号を指定したら、要素の値が返ってくるのと使い方が似ていますね。MATCH関数は=MATCH(検索値,範囲,一致か近似値か)”検索値”に値を入力すると”範囲”の中の位置を数値で返します。MATCH関数で検索値の位置を出力して、その位置を使ってある範囲内の値を抽出します。これでVLOOKUP関数と同じ結果が得られるようになります。Sub実行結果:また、VBAでは検索にはFindメソッドをよく使います。これらで処理速度を比較してみましょう。使うデータは以下のVBAを使って作成しています。Sub1万個のデータリストから商品名を抽出する作業を1万回繰り返す時間で比較します。Sub実行結果:実行結果ではVLookup関数で約12.5秒Index関数とMatch関数で約2.7秒Findメソッドで約39.3秒という結果になりました。この結果からは、VLookup関数よりも代わりにIndex関数とMatch関数を使った方が処理時間を短縮できます。数千や数万個のデータリストから数千個のデータを検索して抽出する場合は、Index関数とMatch関数の組み合わせを使用することをオススメします。ちなみに、Findメソッドは処理時間がかなり遅い結果となりました。Findメソッドの使い方については、こちらのサイトで詳しく解説していますので、ぜひ参考にしてください。2019年9月26日まとめ本記事では、VLOOKUP関数について解説しました。セルに式で記述する、VBAでも実装できる、この両方をマスターするといろんなことができるようになります。頑張ってマスターしてくださいね!TweetPocket無料でSEからWebエンジニアへ転職しませんか?侍エンジニア塾では、完全未経験の方から現在SEだけどプログラミングはやっていないという経験者まで、幅広い方々の人生を好転させるプログラミング指導を行ってきました。SEの方とお話していくなかで、システムエンジニアという職業だけどコードが書けない事務作業が多くスキルがないため将来が不安スクールに通うと完全未経験者と同じスタートになるからレベルが合わないという、すでに知識があるSEならではのお悩みがあることに気づきました。弊社では、マンツーマンでレッスンを行いますので、現在お持ちの知識レベルからカリキュラムを作成いたします。こちらの転職コースは無料で受講を始められて転職成功でそのまま卒業できるというとてもお得なコースとなっています。知識のあるSEといっても転職は年齢が若いほど受かりやすいため、まずは無料体験レッスンで今の現状や理想の働き方について一緒に考えていきましょう。関西在住のITエンジニアです。普通の会社に勤務しながら、侍エンジニアのインストラクター、ライターとして活動しています。