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

Contents

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

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

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

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

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

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

マクロでもVLookup関数は使えるけど、もっと良い方法があるわけだね。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか？

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか？
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「１日速習講座」がおすすめです。

詳しくはこちら

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

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

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

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

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

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

VLookup関数と同じはたらきをするマクロの処理時間を短くする方法を紹介するよ。

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

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

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

20,000行の商品データが入力されたエクセルシート

VBAで商品リストからデータを抽出するプログラムを紹介するためのサンプルデータリスト

たくさんのデータが書かれた表での処理を想定しているよ。

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

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

マクロで実現したいことのイメージは以下の画像のとおりです。
A列の商品IDから、紐づいたデータを取得する(サンプルでは2,000個分のデータを取得する)

20,000行のデータから2,000行のデータを取り出した時間をはかってみるよ。

実行後のVBE画面

プログラムの実行完了までにかかった時間をVBEのイミディエイトウィンドウに表示した画像 — イミディエイトウィンドウにプログラム実行完了までの時間が表示されました。(単位は秒)

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

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

※計測はそれぞれのマクロを5回実施した平均
	VLookup関数をつかったマクロ	VLookup関数相当のマクロ
処理完了までの時間	247.851563秒(4分以上)	1.8秒強

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

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

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

VLookup関数にこだわらず、VBAならではの書きかたをした方が良さそうだね。

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が一致すれば、商品カテゴリや商品名などの情報を取り出しています。

検索値をつかって必要なデータを取り出すマクロだね。

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

エクセルVBA｜配列を利用したプログラミングで多くのデータを処理する | Meowのオフィスノート

VBAにおける配列は、複数の値を一つの変数に格納することができる便利な機能です。配列を使用するメリットは、大量のデータを効率的に処理ができることとコードの簡略化ができる点です。特にExcelのセル範囲との相性がよく、ワークシート上のデー

ここからもっと詳しくみていくよ。

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

変数名	宣言した目的
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)シートに出力しています。

スクール選びにお悩みですか？

動画編集、ロボット、AIとテクノロジーの進化が目まぐるしい時代だからこそ身につけるなら即戦力のスキル。

豊富なコースが選べて、独自開発されたカリキュラムの採用で業界有数の学習継続率！

さらにスキル習得だけじゃない。卒業後の転職や副業まで激アツなサポートが無料！！

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

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

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

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

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

Application.ScreenUpdatingで画面更新を停止する

Application.ScreenUpdatingプロパティとは、画面更新の実行・停止の設定ができます。マクロの実行速度を向上させることができます。

マイクロソフトのページでも以下のように解説されています。

解説

マクロの速度を向上させるため、画面を更新しないようにします。この場合、マクロの処理過程は見ることができませんが、実行速度が速くなります。

マクロが終了した後は、ScreenUpdating プロパティの値を True に戻してください。
Microsoft

このコードを書くことで画面の更新を停止させる効果があり、データを繰り返しセルに書き込むときなどは、ひとつのセルごとの画面更新しないように設定することでマクロの処理速度を上げることができます。

Application.ScreenUpdatingプロパティのつかいかたは以下です。

画面更新を停止するコード

Application.ScreenUpdating = False

画面更新を再開するコード

Application.ScreenUpdating = True

Application.ScreenUpdatingプロパティをつかったサンプルコード

Sub 画面更新なし()
    Dim i As Long
    Dim st As Double, et As Double
    
    st = Timer
    Application.ScreenUpdating = False
    
    For i = 2 To 301
        ThisWorkbook.Worksheets("画面更新").Cells(i, 2) = i - 1
    Next i
    
    Application.ScreenUpdating = True
    et = Timer
    Debug.Print et - st
End Sub

Application.ScreenUpdating＝False にしたところから、Trueにするコードまでの間は画面更新の停止ができます。

このサンプルマクロは、ワークシートに1行ずつ変数”i”に代入された数値をに入力します。この処理を300回繰り返すといったシンプルなマクロですが、このマクロを実行したときに画面更新の停止処理をしないときと、停止から復帰したときの動きのちがいを以下の動画で見てみましょう。

画面更新を停止しないでセルに文字出力を実行した場合

Application.ScreenUpdatingがTrue(初期値)で、画面更新セルに文字を入力する処理をした場合の動画です。処理が速いのでひとめではわかりにくいですが、次の画面更新を停止した動画と比較すると、A2セルから3行目、4行目と順番に数値を入力されていく様子がわかります。

1行ずつ文字が入力されていることがわかるね。

画面更新を停止してセルに文字出力を実行した場合

Application.ScreenUpdatingをFalseで、セルに文字を入力する処理をした場合の動画です。入力している間は画面更新はしないで、全ての入力が完了した時点で更新をしていることがわかります。

画面更新が復帰した時点で一気に出力する感じだね。

取り出したデータを配列に代入する

つづいて、結果出力用の配列をつかう点ですが、商品リストを配列としてrngに代入していますが、検索値(商品ID)で商品リスト内を検索して、一致したデータを取り出して配列outrngに代入しています。

以下、処理のながれのイメージ図です。

画像の[1]→[2]→[3]の順で処理をしていきます。[2]の比較で一致する値でなかった場合は、商品リストの次の行の商品IDとの比較を繰り返していく。変数keyに代入された値と一致する行が見つかると、商品ID以外のデータを配列outrngに代入していきます。

配列に代入したデータをまとめて出力する

3つめのポイントである配列outrngの値を結果出力 (2)シートに出力する処理ですが、恐らくこのポイントが、もっとも処理速度をあげる効果として大きな役割をもっていると言えます。配列outrngに代入された値を、まとめてシートに出力する点です。配列ourngの中身は、2,000行×7列分の値がありますが、これを丸ごと範囲選択して結果出力 (2)シートにペタッと貼り付けているイメージです。

しかし、この結果を出力する方法を繰り返し処理などにしてしまうと、処理速度が落ちてしまうので、処理速度を向上させる目指すのであれば、今回のサンプルコードのように、一度、配列に結果一覧を出力したあとで、丸ごとシートに出力する方法がおすすめです。

技術評論社

パーフェクトExcel VBA (PERFECT SERIES)

Amazonで見る楽天市場で見る

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

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

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