大量VlookupをVBAで高速に処理する方法について
大量データ同士のVlookup処理は、非常に時間のかかる処理となります、
シートに関数を入れる場合は、以下を参照してください。
【奥義】大量データでの高速VLOOKUP
・高速VLOOKUPに使用するサンプルデータ ・高速VLOOKUPの数式 ・高速VLOOKUPの数式解説 ・高速VLOOKUPの補足
以下の表で検証します。
一致したSheet2のB列を、Sheet1のB列に入れます。
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
これを10万行いれると、とても時間がかかるのでVBAで何とかしようという事です。
Sub test()
Dim rng検索値 As Range
Dim rng検索範囲 As Range
Dim rng出力範囲 As Range
Set rng検索値 = Worksheets("Sheet1").Range("A2:A100001")
Set rng検索範囲 = Worksheets("Sheet2").Range("A2:B100001")
Set rng出力範囲 = Worksheets("Sheet1").Range("B2:B100001")
Application.ScreenUpdating = False
Debug.Print Timer
Call sample○(rng検索値, rng検索範囲, 2, rng出力範囲)
Debug.Print Timer
Application.ScreenUpdating = True
End Sub
sample○
この部分を取り換えて、複数のプロシージャーを検証します。
Sub sample1(ByVal rng検索値 As Range, _
ByVal rng検索範囲 As Range, _
ByVal 列位置 As Integer, _
ByVal rng出力範囲 As Range)
Dim i As Long
For i = 1 To rng検索値.Rows.Count
rng出力範囲(i, 1) = WorksheetFunction.VLookup( _
rng検索値(i, 1), _
rng検索範囲, _
列位置, _
0)
Next
End Sub
普通に、
WorksheetFunction.VLookup
これで取得しています。
これは、シート関数そのものですので、速いはずがありません。
シートに関数を入れての再計算と同じ、むしろそれよりも時間がかかってしまいます。
それでも、ブックが重くなってしまうという事は避けられます。
たまに受ける相談で、
「シートに関数を入れたら再計算に時間がかかるのでマクロでやりたい。」
シート関数をマクロにしただけではあまり意味がないという事です。
速度対策における考え方については、以下を参考にしてください。
大量データで処理時間がかかる関数の対処方法（WorksheetFunction）
・大量データで処理時間がかかるサンプルデータ ・普通にマクロVBAコ－ドを書いた場合 ・指定範囲を絞ってみる ・配列を使って書いてみる ・アルゴリズムを考えてみる ・Dictionary（連想配列）を使う ・大量データで処理時間がかかる関数の対処方法の最後に
大量データにおける処理方法の速度王決定戦VBAで自動化したが、大量データ処理に時間がかかってしまう… そんな悩みが非常に多いようです、そこで、各種処理方法の速度比較を行い、どの処理方法が最も速いかを検証します。つまり、処理方法の速度王決定戦です。検証する題材としては、最も一般的な集計で行います。
Sub sample2(ByVal rng検索値 As Range, _
ByVal rng検索範囲 As Range, _
ByVal 列位置 As Integer, _
ByVal rng出力範囲 As Range)
Dim i As Long
Dim ary()
Dim myDic As New Dictionary
For i = 1 To rng検索範囲.Rows.Count
If Not myDic.Exists(rng検索範囲(i, 1).Value) Then
myDic.Add rng検索範囲(i, 1).Value, rng検索範囲(i, 1).Offset(, 列位置 - 1).Value
End If
Next
ReDim Preserve ary(1 To rng出力範囲.Rows.Count, 1 To 2)
For i = 1 To rng検索値.Rows.Count
ary(i, 1) = myDic.Item(rng検索値(i, 1).Value)
Next
rng出力範囲.Value = ary
End Sub
Dim myDic As New Dictionary
「ツール」→「参照設定」で、
Microsoft Scripting Runtime
これを参照設定しています。
参照設定しない場合は、
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
Microsoft Scripting Runtime
これを参照設定しています。
参照設定しない場合は、
Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")
Dictionary全般については、以下を参考にしてください。
Dictionary（ディクショナリー）連想配列の使い方について
・Dictionaryを使って重複を除く ・Dictionaryの使い方その2 ・Dictionaryの使い方その3 ・Dictionaryの使い方サンプル ・サイト内のDictionary関連記事
Dictionary（ディクショナリー）のパフォーマンスについて・Dictionaryの検証に使うシート ・ユニーク化（重複削除）の方法について ・Dictionaryでユニーク化1 ・Dictionaryでユニーク化2 ・Dictionaryでユニーク化3 ・Dictionaryでユニーク化4 ・最初のVBAはなぜ遅かったのか ・Dictionaryを使わない方法 ・サイト内の関連ページ
最初に検索範囲を、Dictionaryに全て入れてしまい、
検索値の全行に対して、検索値でDictionaryから値を取得しています。
このマクロVBAで、2.5秒～2.8秒程度で完了します。
十分に速いものとなっています。
検索値の全行に対して、検索値でDictionaryから値を取得しています。
十分に速いものとなっています。
Sub sample4(ByVal rng検索値 As Range, _
ByVal rng検索範囲 As Range, _
ByVal 列位置 As Integer, _
ByVal rng出力範囲 As Range)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i1 As Long
Dim i2 As Long
Dim rMax1 As Long
Dim rMax2 As Long
Dim ary1
Dim ary2
Dim ary3
Set wb = Workbooks.Add
Set ws1 = wb.Worksheets(1)
Set ws2 = wb.Worksheets.Add
rMax1 = rng検索値.Rows.Count
rMax2 = rng検索範囲.Rows.Count
ary1 = rng検索値
ReDim Preserve ary1(1 To rMax1, 1 To 2)
For i1 = 1 To UBound(ary1, 1)
ary1(i1, 2) = i1
Next
ws1.Range("A1").Resize(rMax1, 2).Value = ary1
ws2.Range("A1").Resize(rMax2).Value = rng検索範囲.Columns(1).Value
ws2.Range("B1").Resize(rMax2).Value = rng検索範囲.Columns(列位置).Value
With ws1
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A1"), Order:=xlAscending
.Sort.SetRange .Range("A1").CurrentRegion
.Sort.Header = xlNo
.Sort.Apply
ary1 = .Range("A1").CurrentRegion
End With
With ws2
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A1"), Order:=xlAscending
.Sort.SetRange .Range("A1").CurrentRegion
.Sort.Header = xlNo
.Sort.Apply
ary2 = .Range("A1").CurrentRegion
End With
wb.Close SaveChanges:=False
ReDim ary3(1 To rMax1, 1 To 1)
i1 = 1
i2 = 1
Do
Select Case True
Case ary1(i1, 1) = ary2(i2, 1)
ary3(ary1(i1, 2), 1) = ary2(i2, 2)
i1 = i1 + 1
Case ary1(i1, 1) > ary2(i2, 1)
i2 = i2 + 1
Case ary1(i1, 1) < ary2(i2, 1)
i1 = i1 + 1
End Select
If i1 > rMax1 Or i2 > rMax2 Then
Exit Do
End If
Loop
rng出力範囲.Value = ary3
End Sub
新規ブックを追加し、そこに検索値と検索範囲を入れています。
並べ替えした後、それぞれを配列に入れます。
検索値と検索範囲の配列を順次比較しながら、一致していれば値を取得しています。
Dictionaryとほぼ同様の処理速度となっています。
ほぼ同程度の処理速度の結果となりました。
マクロVBAコードの簡潔さでいえば、Dictionaryに分がありそうです。
処理速度が同程度なので、どちらを使うかは好みになるかと思います。
場面によって使い分けすれば良いでしょう。
同じテーマ「マクロVBA技術解説」の記事
エクセルVBAのパフォーマンス・処理速度に関するレポート
VBAのFindメソッドの使い方には注意が必要です
マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
遅い文字列結合を最速処理する方法について
大量VlookupをVBAで高速に処理する方法について
Withステートメントの実行速度と注意点
IfステートメントとIIF関数とMax関数の速度比較
スピルって速いの?スピルの速度について
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数（セル範囲をトリム：端の空白セルを除外）｜エクセル入門(2024-08-30)
正規表現関数（REGEXTEST,REGEXREPLACE,REGEXEXTRACT）｜エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない｜エクセル雑感(2024-04-11)
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 大量VlookupをVBAで高速に処理する方法について
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。