2019.10.15スポンサーリンクこんにちは、VBAエンジニアのやすこれです。みなさんは、VBAでVLOOKUP関数を使ったことがありますか?VBAでも検索キーを使ってセル範囲から対応する値を取得したいワークシート関数VLOOUPなら使えるけどVBAではやり方がわからないVBAでもワークシート関数VLOOKUPを使う方法が知りたい!という方もいるのではないでしょうか。そこで今回は、VBA上で動作するVLOOKUP関数をご紹介します。カエレバAmazon楽天市場Yahooショッピングスポンサーリンク目次VBAでのVLOOKUP関数の使い方VLOOKUP関数の基本的な使い方VLOOKUP関数の「検索方法」についてVLOOKUP関数のエラー値と回避方法VLOOKUP関数のエラー値VLOOKUP関数エラー回避方法INDEX関数とMATCH関数を組み合わせてVLOOKUP関数のように使うVBAでのVLOOKUP関数まとめVBAでのVLOOKUP関数の使い方VLOOKUP関数の基本的な使い方VLOOKUP関数は、任意の検索キーで指定範囲1列目を検索し、値が一致した行の指定した列数に対応する値を返す関数です。0)アクティブシート上「A1:B10」1列目のデータから、整数「5」で完全一致した行の「2」列目を返しています。検索方法の「0」は完全一致で検索することを意味しています。画像のように上から5番目のA5セルに「5」があり、列番号で「2」を指定しているので、コード例の数式は「E」を返します。検索値はセルの値で指定することもできます。その場合は次のように「.Value」は記載せず直接「Range」を指定するか、「.Value2」を指定するようにしましょう。「.Value」を指定した場合、日付型などで正常に検索できないことがあるので覚えておいてください。0)VLOOKUP関数はこのように、指定した範囲で1列目が検索キーと一致する行の指定列の値を返すことができます。検索方法には完全一致の他に近似一致(二分探索)があるので、詳しく解説していきす。VLOOKUP関数の「検索方法」について検索方法には2つの値を指定できます。それぞれの意味は下記の通りです。言葉だけでは伝わりづらいと思うので、範囲を「A1:B10」セルとした場合の具体的な例を順に紹介します。検索方法「0」(完全一致)、検索値は「5」の場合まず「完全一致で検索する場合」について解説します。0)「完全一致」は範囲1列目の内、検索値と完全に一致する値の行から、指定した列番号の値を返します。範囲のデータは、昇順や降順で並んでいなくても問題ありません。検索値「5」と完全に一致する「A5」セルは範囲1列目の5行目のセルで、列番号は「2」を指定しているので返り値は「E」となります。次に「近似一致で検索する場合」について解説していきます。1)「近似一致」は範囲1列目の内、検索値に近似一致する値の行から、指定した列番号の値を返します。範囲のデータは、昇順で並んでいる必要があります。検索方法に「近似一致」を指定した場合、対象の範囲1列目を「二分探索」で検索します。二分探索とは検索値が検索範囲の上にあるか下にあるか(左にあるか右にあるか)を繰り返し検索して目的の値を探す方式データが昇順に並んでいる必要があるデータが昇順でない場合は、正しい結果が求められない検索値「3」は範囲1列目の中央、A5セルの「5」より小さいので、A5セルより上にあると判定されます。A1~A4セルの中央、A2セルの「2」より大きくA2セルより下にあると判定されます。最後にA3~A4セルの中央、A3セル「3」と比較した際に検索値と一致します。指定した列番号は「2」なので範囲3行目の2列目の値「C」を返します。便利なVLOOKUP関数ですが、気を付けなければいけないこともあります。それはエラーが起き、マクロ処理が中断されることです。いう時にエラーがでるのか、例を挙げます。検索方法「0」・検索値と一致する値が範囲にない検索方法「1」・データ降順どちらも「VLookupプロパティを取得できません。」というエラーとなります。エラーはつきものですので、次に解説する回避方法も覚えてしまいましょう!VLOOKUP関数エラー回避方法エラーが起きた際は、検索方法によって次の2点を確認すれば原因がわかります。検索方法「0」なら、検索値に一致するデータがあるか検索方法「1」なら、データが昇順かしかし実務では、検索値がデータにあった場合のみ、対応する値を求めたいことも多いでしょう。検索結果の有無で処理を分けるには、次のようなコードで対応できます。Next」でエラーが起きても次の処理へ進むよう設定しているのがポイントです。0」を指定しています。0」)ことを忘れないようにしましょう。INDEX関数とMATCH関数を組み合わせてVLOOKUP関数のように使う検索範囲から任意の値でセルを探せるVLOOKUP関数ですが、VLOOKUP関数で検索できるのは、範囲の左端のみなので使い勝手が悪い面もあります。そこで今回は、範囲のどの行・列からでも検索ができ、より自由度の高いINDEX関数とMATCH関数を組み合わせた方法を紹介します。この方法は、VLOOKUP関数よりも処理が早いので、積極的に使ってみてくださいね。INDEX関数から解説していきます。1)アクティブシート上のセル範囲「A1:B5」4行目・1列目の値を返します。画像のようにセル範囲にデータがある場合、4行目・1列目の「いちご」を返すコードとなります。つづいて、MATCH関数について解説します。0)アクティブシート上「A1:A10」の範囲から、整数「5」で完全一致した値を返しています。検索方法の「0」は完全一致で検索することを意味しています。画像のように上から5番目のA5セルに「5」があるので、コード例の数式は「5」を返します。【ExcelVBA】MATCH関数の使い方とは?よくあるエラーの対策も解説!こんにちは、VBAエンジニアのやすこれです。ワークシート関数MATCHなら使えるけどVBAではやり方がわからないyasucore.com2020.02.01ここまでの内容をおさえた上でVLOOKUP関数のように使う方法を確認していきましょう。VLOOKUP関数は、指定した検索値が範囲1列目の値と一致した行から指定した列の値を返す関数です。INDEX関数の行指定をMATCH関数で行い、列を指定することで、VLOOKUP関数のように使えます。次のコード例では「いちご」の「価格」を求めています。2)少しコードが複雑になってきたので、順を追って解説します。ActiveSheet.Range("A1:A5")まず、変数「matchRng」にMATCH関数で検索したい範囲「A1:A5」を定義しています。0)次に、MATCH関数を使って「いちご」が入力されているセルを検索し、変数「matchRow」に値を入れています。この時、MATCH関数の検索範囲「A1:A5」は、先ほど定義した変数「matchRng」を使って設定しています。ActiveSheet.Range("A1:B5")変数「indexRng」にINDEX関数で検索したい範囲「A1:B5」を定義しています。2)変数「trgtPrice」に求めたい「いちご」の「価格」を定義しています。INDEX関数の値にはそれぞれ次の値を設定しています。範囲:「indexRng」(=「A1:B5」)行:MATCH関数で求めた値を定義した「matchRow」(「いちご」の行=4)列:「価格」列を表す「2」これでVLOOKUP関数のように、値を求めることができます。INDEX関数、MATCH関数でも検索結果がない場合は、VLOOKUP関数と同様に「Index(MATCH)プロパティを取得できません。」というエラーが発生します。VLOOKUP関数の時と同じ方法で、エラー時の処理も対応しておきましょう。VBAでのVLOOKUP関数まとめ今回はVBAでのVLOOKUP関数の使い方をご紹介いたしました。VLOOKUP関数を使えば、任意のセル範囲の中で検索キーに対応する値を調べられるので、使いこなせば非常に便利です。今回紹介したエラー回避方法や、応用として解説したINDEX関数とMATCH関数の組み合わせや二分探索も理解しておくと、応用が効くのでおすすめです。ExcelVBA】Len関数の使い方とは?実務で使うテクニックも解説こんにちは、VBAエンジニアのやすこれです。「条件付き書式」でセル値によって書式を変えている。セル値じゃなくて、決まった時間がきたら書式が変わるようにしたい。「条件付き書式」は数式を使い色々な条件を指定できます。【ExcelVBA】Left関数の使い方とは?実務で使うテクニックも解説こんにちは、VBAエンジニアのやすこれです。Excelをお使いのあなたにも、支払い締切日、給与締日など常に末日日付を表示させておきたいシーンがありますよね。【ExcelVBA】ファイルコピーのやり方とよくあるエラーを解説!こんにちは、VBAエンジニアのやすこれです。感じることも多いのではないでしょうか。【ExcelVBA】Mid関数の使い方とは?実務で使うテクニックも解説こんにちは、VBAエンジニアのやすこれです。【ExcelVBA】Dir関数の使い方とは?実務で使うテクニックも解説こんにちは、VBAエンジニアのやすこれです。2017年12月やすこれ働きたくない。いう思いから投資をはじめ、ブログを書いてます。働かないために業務自動化を推し進め、幅広くExcel(VBA含む)の知識を身に付けました。その後、事務職からVBAエンジニアへの転職を果たしています。VBA開発・Excel指導依頼受け付けてます。Twitterやcoconala等でお気軽にDMください。2017年12月やすこれ働きたくない。いう思いから投資をはじめ、ブログを書いてます。働かないために業務自動化を推し進め、幅広くExcel(VBA含む)の知識を身に付けました。その後、事務職からVBAエンジニアへの転職を果たしています。VBA開発・Excel指導依頼受け付けてます。Twitterやcoconala等でお気軽にDMください。カテゴリーを選択ライフハック。