【結論】配列でVLookup関数と同じ機能のマクロをつくる
結論をかんたんに言えば、以下のとおりです。
出力したいデータが少量の場合は、マクロでVLookup関数をつかっても処理時間は許容範囲と言える。
しかし、数千行以上のデータを取り出すなど、出力部分が多くなればなるほど、処理にかかる時間が長くなり、品質が下がる傾向にある。
これを考えた場合、マクロでは繰り返しと配列をつかって、VLookup関数と同じ機能のプログラムを実現した方が品質が高いものが作れる。
または、キーと値が対となってデータが登録できる、連想配列(Dictionaryオブジェクト)をつかうことで高品質なものが作れる。
マクロでもVLookup関数は使えるけど、もっと良い方法があるよ。
VLookup関数と同じ機能のエクセルVBAマクロ
VLookup関数と同じ機能のマクロについて
以前の記事でエクセルVBAマクロでVLookup関数をつかう方法と、繰り返し文や条件判定を利用してVLookup関数と同じ機能を実装する方法を紹介しました。
今回はそんなVLookup関数と同じ機能のマクロの処理速度を早くするための方法を紹介していきます。
まずは、サンプルデータの準備ですが、以下の画像のとおり、エクセルのワークシートに20,000行のデータが登録されている表があります。この表から、商品IDを検索値として他のシートに商品データを取り出す処理をしてみます。
以下は、マクロ実行前の結果出力用のワークシートです。A列の商品IDは2,000行記入されており、これが検索値です。この値を変数に代入して、さきほど商品リストから2,000行のデータを取り出します。
A列の商品IDから、紐づいたデータを取得する(サンプルでは2,000個分のデータを取得する)
実行後のVBE画面
20,000行の商品リストから、2,000行のデータを取り出す処理を実行するのにかかった時間は1.8秒強となっています。ちなみに同じ処理を『VLookup関数』を使ったマクロで実行した場合にかかる時間は、計測回数は5回で平均時間、247.851563秒(4分以上)でした。
同じ結果がかえってくるのであれば、処理が速く済むものを採用したほうが良いでしょう。もっと言えば処理にかかった時間が近似値ならば、どちらでも良いといった結論もゆるされそうですが、これだけ圧倒的な差であれば、マクロの品質から考えても、VLookup関数を無理矢理つかったマクロのメリットは小さいと考えます。
連想配列(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 LongVBEの標準モジュールの宣言セクション内にこちらを記載しています。
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に取込んで配列としてつかえるようにしています。
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に商品リストを配列として代入するところまで完了したら、戻ってきてそれ以降の処理を続けていきます。
このサンプルコードでは、セル範囲から配列つくっています。セル範囲以外から配列にする方法など、配列の基本的な使いかたについて詳しく説明している記事はこちらです。
こちらのコードで宣言している変数は以下のとおりです。
| 変数名 | 宣言した目的 |
|---|---|
| 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点を紹介します。
- 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 SubApplication.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)シートにペタッと貼り付けているイメージです。
しかし、この結果を出力する方法を繰り返し処理などにしてしまうと、処理速度が落ちてしまうので、処理速度を向上させる目指すのであれば、今回のサンプルコードのように、一度、配列に結果一覧を出力したあとで、丸ごとシートに出力する方法がおすすめです。
エクセルのマクロでVLookup関数の処理速度をあげる方法まとめ
今回は、エクセルVBAでVLookup関数の機能を実行するときのパフォーマンス(処理速度)に目を向けてみました。VLookup関数にかぎらず、ワークシート関数は、大量のデータを処理するときに時間がかかってしまう傾向があります。必要なデータを検索し、取り出すといったとても便利な機能(プログラム)でも、処理に時間がかかりすぎると使い勝手がわるい印象となってしまいます。
VLookup関数と同じ機能はつかいたい。でも、処理が遅くなることを可能なかぎり回避したい。
これをエクセルVBAマクロで実現するための方法は以下のとおりです。
●配列をつかったエクセルVBAマクロを検討する
●Application.ScreenUpdatingで画面更新を停止する
●処理速度アップのカギは結果をまとめて出力する
今回はここまで。
VLookup関数が好きなエクセルユーザーさんは多いと思いますので、VBAマクロでもVLookup関数のような機能を作りたいと考えている人は、ぜひこちらの記事を参考に開発してみることをおすすめします。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント