Excel VBAを使用してVlookupを自動化する方法

Vlookupは Excel の必須関数であり、データ処理の重要な部分になっています。包括的なデータベースと組み合わせることができる多くの機能を提供します。

ただし、この機能を自動化したい場合は、特に Excel や VBA で使用できる自動化言語の使用方法を知っていれば可能です。Excel VBA で vlookup 関数を自動化する方法は次のとおりです。

ExcelのVlookup関数の数式

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

式の引数の意味:

• lookup_value: 配列内で検索する基になる値。
• lookup_array: ソース データ、lookup_value が参照として使用されます。
• column_index: 列は値を返します。
• exact_match/partial_match: 組み合わせの種類を 0 または 1 で指定します。

ExcelのVBA Vlookup関数

Vlookup 関数の使用方法は、Excel で直接使用する場合でも、VBA 経由で使用する場合でも、大きな違いはありません。データ アナリストが情報の行を処理している場合でも、倉庫管理者が常に新製品を扱っている場合でも、vlookup 関数は便利です。

動的参照範囲を操作する場合、Excel で同じ数式を何度も操作する必要を避けるために、数式を自動化することが最善です。VBA で Vlookup 関数を自動化すると、複数列の計算をワンクリックで実行できます。

図

このデータセットには、ルックアップ テーブルとターゲット データ ポイントの 2 つの部分があります。ルックアップ テーブルには 3 つの列が含まれており、サブカテゴリと製品名の 2 つのフィールドにデータが含まれています。

ターゲット テーブルには適切な列があり、サブフォルダーや製品名データはありません。[注文 ID]列の値は、ルックアップ テーブルの同じ列にも表示されることに注意してください。

1. 必要な設定を行う

まず、新しい Excel ワークブックでコード エディターを開き ( Alt + F11を押すか、 「開発」タブに移動)、モジュールを追加してコーディングを開始します。コード エディターで、プログラミング ウィンドウの上部に次の行を追加してサブルーチンを作成します。

Sub vlookup_fn1()End Sub

サブルーチンはVBA コードのコンテナであり、正常に実行するために必要です。もう 1 つの方法は、VBA ユーザーフォームを作成して UI をよりインタラクティブにすることです。

2. 変数を宣言し、参照スコープを作成する

まず、Dim コマンドを使用して変数のデータ型を宣言する必要があります。

Dim i as integer, lastrow as long

次に、変数lastrowを作成して、検索範囲内の最後に設定された行の値を保存します。lastrow関数は、 end(xldown)関数を使用して、特定の範囲内の最後の行参照を計算します。

この例では、lastrow変数には、ルックアップ範囲内の最後に入力された行の値、具体的には 17 が含まれています。

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

上の例では、宛先テーブルは A2:C17 から拡張され、ソース テーブルは F2:H17 から拡張されます。vlookup 式は、列 A の各値をループしてソース テーブル内で検索し、結合されたエントリを列 B と列 C に貼り付けます。

ただし、ループに入る前に、次のように範囲値を新しい変数 ( my_range ) に保存する必要があります。

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

開始セル参照 (F3) と終了列参照 (H) を明示的に指定する必要があります。VBA は行の値を自動的に追加して、検索配列を完成させます。

3. 各ルックアップ値を循環するループを作成します。

ループを記述する前に、開始行の値 2 を定義します。動的コンポーネントを扱う場合は、ループの開始値の定義が必要です。宛先テーブルでは、行 2 がデータの最初の行になります。データが別の行で始まる場合は、この値を変更します。

i = 2

do-whileループを使用して、行 2 から開始して行 17 で終了する各行を処理できます。コードの残りの部分と同様、この側面は柔軟です。ループは条件が false になるまで実行されます。この条件を使用して、現在の行の最初のセルに空でない値があるかどうかをテストします。

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

ループ内で Vlookup 関数を呼び出してセルを埋めることができます。

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

これらのコマンドは、現在の行のセルの値を列 2 と列 3 にそれぞれ設定し、WorksheetFunction オブジェクトを使用して Vlookup 関数を呼び出し、列 1 からの対応する値を検索に渡します。また、最初に定義した範囲と、最終値を取得するために関連する列のインデックスも渡します。

vlookup コードは準備ができていますが、ループ内で毎回行変数をインクリメントする必要があります。

i = i + 1

ループが実行されるたびに、値i が1ずつ増加します。開始行の値は 2 であり、ループが実行されるたびに増分が発生することに注意してください。このコード ブロックの終わりを設定するには、loopキーワードを使用します。

コード全体が実行されると、ソース テーブルの値に基づいて、両方の列に結果が設定されます。

以下は参照コード全体です。

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. コードを実行するボタンを作成します。

Excel テーブルにボタンを作成して、ワンクリックで実行できます。空の Excel シートに目的の図形を挿入し、それを右クリックして、[マクロの割り当て]オプション をクリックします。

ダイアログ ボックスでサブルーチンの名前を選択し、[OK]をクリックします。

コードを実行する場合は、ボタンをクリックすると、データがどのように入力されるかをすぐに確認できます。

上記は、Excel VBA を使用して検索関数を自動化する方法です。この記事がお役に立てば幸いです。