エクセルVBA｜VLookup関数マクロの処理をはやくするときのポイント

【結論】配列でVLookup関数と同じ機能のマクロをつくる

結論をかんたんに言えば、以下のとおりです。

出力したいデータが少量の場合は、マクロでVLookup関数をつかっても処理時間は許容範囲と言える。

しかし、数千行以上のデータを取り出すなど、出力部分が多くなればなるほど、処理にかかる時間が長くなり、品質が下がる傾向にある。

これを考えた場合、マクロではくり返しと配列をつかって、VLookup関数と同じ機能をもつマクロを実現した方が品質が高いものが作れる。

または、キーと値が対となってデータが登録できる、連想配列(Dictionaryオブジェクト)をつかうことで高品質なものが作れる。

VLookup関数と同じ機能のエクセルVBAマクロ

VLookup関数と同じ機能のマクロについて

エクセルのワークシート関数では有名なVLookup関数ですが、こちらの記事ではこのVLookup関数をVBAコードで実装する方法について紹介しました。

結論として、マクロでVLookup関数と同じ機能をあつかうためにはワークシートをつかう方法と、くり返し文と条件判定を組み合わせた方法を利用して、VLookup関数と同じはたらきをするマクロの作り方がありました。

VLookup関数やVLookup関数相当マクロともに、データのボリュームによっては処理に時間がかかるようになってきます。これは処理をする対象が多くなれば仕方のない部分ではありますが、マクロをつくる上でその機能の使い勝手のよさを考えた場合、結果を得るための時間はなるべく短いことが望ましいことも事実です。

そこでこちらの記事では、VLookup関数やVLookup関数相当のマクロの処理速度を早くするための方法を紹介していきます。

検証に使用するサンプルデータの紹介

まずはサンプルデータの準備ですが、以下の画像のとおりエクセルのワークシートに20,000行のデータが登録されている表があるとします。

今回検証するマクロは、こちらのサンプルデータから指定した検索値と一致した商品データを取り出す処理をしてみます。

つづいて、マクロ実行前の結果出力用のワークシートです。

A列の商品IDは2,000行記入されており、これが検索値です。この値を変数に代入して、20,000行の商品リストから2,000行分のデータを取り出します。

実行後のVBE画面

2,000行のデータを取り出すのにかかた時間の結果

この処理につかったVBAコードの詳しい内容については後ほど紹介しますが、結論から言えば、VLookup関数をつかったマクロとくり返しと条件分岐を組み合わせたVLookup関数相当のマクロでは、圧倒的に後者を使ったほうが短い時間で処理ができました。

※計測はそれぞれのマクロを5回実施した平均

	VLookup関数をつかったマクロ	VLookup関数相当のマクロ
処理完了までの時間	247.851563秒(4分以上)	1.8秒強

こちらの結果でもわかるとおり、同じ機能をもったマクロでもVBAのコードの書きかた次第で処理を完了するまでにかかる時間がこんなにも違ってきます。

当然ですが同じ結果がかえってくるのであれば、処理が速く済むものを採用したほうが良いでしょう。

もっと言えば処理にかかった時間が近似値ならば、どちらでも良いといった結論でもゆるされそうですが、これだけ圧倒的な差であれば、マクロの品質から考えても、VLookup関数を無理矢理つかったマクロのメリットは小さいと考えます。

VLookup関数と同じ機能は、連想配列(Dictionary)でも実現できます。
連想配列(Dictionary)については以下の記事で紹介していますので、そちらが知りたい人におすすめです。

VLookup関数と同じ機能のマクロのコード

実際に作成したコードは以下のとおりです。
こちらのコード単体では完成版ではありませんので、そのあたりについて詳しく説明します。

入力内容

Option Explicit

'時間計測のための変数を2つ宣言する
Private startTime As Double, endTime As Double

'セル範囲を格納するための変数を宣言する
Private rng As Variant

'抽出データを格納するための配列を宣言する
Private outrng() As Variant

'検索値、行数、列数を格納するための変数を宣言する
Private key As Long, ro As Long, col As Long

VBEの標準モジュールの宣言セクション内にこちらを記載しています。
Privateをつけて宣言した変数は、同じモジュール内でつくったプロシージャで使用できます。

Function 範囲取得()

    ThisWorkbook.Worksheets("商品リスト").Activate
        With ThisWorkbook.Worksheets("商品リスト")
            '最終行の取得
            ro = .Cells(Rows.Count, 1).End(xlUp).Row
            
            '最終列の取得
            Col = .Cells(1, Columns.Count).End(xlToLeft).Column
            
            'ワークシートの指定範囲を取得
            rng = .Range(Cells(2, 1), Cells(ro, Col))
    
    End With

End Function

このファンクションでは、商品リストを配列に代入するための処理を実行しています。商品リストの表が全てrngに取込んで配列としてつかえるようにしています。

Option Explicit

Sub For_If_配列のコードB改()

Call 範囲取得

    Dim testtime As Double
    Dim i As Long, ii As Long, iii As Long, j As Long, k As Long

    '計測開始
    startTime = Timer
    
    '画面更新を停止
    Application.ScreenUpdating = False

    ThisWorkbook.Worksheets("結果出力 (2)").Activate
    
    With ThisWorkbook.Worksheets("結果出力 (2)")
        j = .Cells(Rows.Count, 1).End(xlUp).Row
        k = .Cells(1, Columns.Count).End(xlToLeft).Column

        For i = 1 To j
            Key = .Cells(i + 1, 1)
            For ii = 1 To UBound(Rng, 1)
                If Key = Rng(ii, 1) Then
                    ReDim Preserve outrng(1 To j - 1, 1 To k - 1)
                    If iii <= j Then
                        outrng(iii + 1, 1) = Rng(ii, 2)
                        outrng(iii + 1, 2) = Rng(ii, 3)
                        outrng(iii + 1, 3) = Rng(ii, 4)
                        outrng(iii + 1, 4) = Rng(ii, 5)
                        outrng(iii + 1, 5) = Rng(ii, 6)
                        outrng(iii + 1, 6) = Rng(ii, 7)
                        outrng(iii + 1, 7) = Rng(ii, 8)
                        iii = iii + 1
                    End If
                End If
            Next ii
        Next i
    
        .Range(Cells(2, 2), Cells(j, k)).Value = outrng
    
    End With

    '画面更新を再開
    Application.ScreenUpdating = True
    
    '計測終了
    endTime = Timer
    
    'マクロの作業時間を算出
    testtime = endTime - startTime
    
    'マクロの作業時間をイミディエイトウィンドウに表示する
    Debug.Print testtime

End Sub

コードの下線部分は、プログラムの実行速度を計測につかうものなので、VLookup関数と同じ機能を実装したいだけの場合はその部分のコードを削除しても動作します。

続いて、太字部分のCallステートメントではファンクションプロシージャである「範囲取得」を呼び出しています。範囲取得では、モジュール変数である”rng”に商品リストの最終行と最終列までの範囲を取得し、配列として代入しています。

ファンクションプロシージャでの処理が完了すると、再び呼び出し元のプロシージャに戻ります。ここからは結果出力用のワークシート(結果出力 (2))の作業範囲として最終行を変数”j“に、最終列を変数”k“に代入していきます。

配列データの代入と作業する範囲が取得できたため、実際にどういったデータを入力するかの部分を作成していきます。
サンプルコードでは、For文によるくり返しと、If文による条件分岐の組み合わせを使って検索値が同じもの検索します。具体的に言えば結果出力用のワークシートに記入された商品IDを使って、配列データに取込んだ表の商品IDが一致すれば、商品カテゴリや商品名などの情報を取り出しています。

このサンプルコードでは、商品リストシートのセル範囲を配列に代入しています。配列は大量のデータをまとめて扱うときに便利に使えるものです。配列についての記事は以下のリンクをクリックにしてください。

こちらのコードで宣言している変数は以下のとおりです。

変数名	宣言した目的
testtime	処理時間を計測して代入するため、VLookup関数と同じ機能だけが必要なら削除しても良い
i	繰り返し処理のループカウンタとワークシート上の検索値となる文字列の座標を指定
ii	繰り返し処理のループカウンタと配列rngの文字列の座標を指定
iii	検索値により取得したデータを配列outrngの何行目に代入するかを指定
j	結果出力に必要な配列の最終行数を代入する
k	結果出力に必要な配列の最終列数を代入する

変数の宣言のあとについて、「Application.ScreenUpdating = False」がありますが、これは画面更新を停止するコードです。こちらはマクロの実行速度を上げるためのポイントですが、これを実行するとどういった状態になるのかも含めてあとで詳しく説明します。

続いて、変数jと変数kに最終行・列の数値を代入しますが、結果出力(2)のシートには2,000行の商品IDが記入されていますので、行数となる変数jは、2001が代入され、列数となる変数kはH列の8が代入されます。
また、この結果によって、抽出結果の配列outrngは2,000行×7列の大きさが必要です。(※列数は商品IDの列は不要なので、8-1で7列となる。)

次に繰り返し処理ですが、最初のFor文で変数keyに検索値を代入します。
具体的には、結果出力(2)のシートのA列に記載されている値(商品ID)をkeyに代入する処理を繰り返していきます。

keyに代入された値と、商品リストが代入された配列rngの1列目の値をIf文で比較して、不一致であれば、商品リスト配列rngの次の行のデータとの比較へ移り、一致すれば結果用の配列outrngに商品名などの値を代入していきます。

要するに配列(rng)から、条件に一致したデータだけを抜き出し、別の配列(outrng)に代入していく処理をしています。

さいごに配列outrngを結果出力(2)シートに出力しています。

マクロの処理速度をあげるポイントは3つ

前項までで、エクセルのVBAマクロでVLookup関数の処理速度をはやくしたいのであれば、VLookup関数ではない方法をつかってVLookup関数と同じ機能を実装する方法を紹介しました。

ここでは、サンプルコードで処理速度をあげている以下のポイント3点を紹介します。

1. Application.ScreenUpdatingで画面更新を停止する
2. 取り出したデータを配列に代入する
3. 配列に代入したデータをまとめて出力する

マクロの処理速度に効果があるコード

エクセルのマクロでVLookup関数の処理速度をあげる方法まとめ

今回は、エクセルVBAでVLookup関数の機能を実行するときのパフォーマンス(処理速度)に目を向けてみました。VLookup関数にかぎらず、ワークシート関数は、大量のデータを処理するときに時間がかかってしまう傾向があります。必要なデータを検索し、取り出すといったとても便利な機能(プログラム)でも、処理に時間がかかりすぎると使い勝手がわるい印象となってしまいます。

VLookup関数と同じ機能はつかいたい。でも、処理が遅くなることを可能なかぎり回避したい。
これをエクセルVBAマクロで実現するための方法は以下のとおりです。

• 配列をつかったエクセルVBAマクロを検討する
• Application.ScreenUpdatingで画面更新を停止する
• 結果はなるべくまとめて出力する

今回はここまで。

VLookup関数が好きなエクセルユーザーさんは多いと思いますので、VBAマクロでもVLookup関数のような機能を作りたいと考えている人は、ぜひこちらの記事を参考に開発してみることをおすすめします。