テレワークという名のテレワーク端末がないので事実上の自宅待機。今日で3回目。
ふらふらと外に出ず、自己研鑽してろとのことで、VBA・SQLの勉強をすることにしました。またまた自己満足、備忘録の記載です。
前回クラスを使ってVLookupてきなデータ流し込みをやってみたのですが、
やっぱり配列が早いんじゃないか・・・？と思ったので、無理くりにクラスを使うのをやめて今度は標準モジュールで配列を利用したデータ流し込み。
C：配列を使ったデータ流し込み
①配列にもとデータをがばっと入れる
②いれるシートの最初の行から最後の行まで
③いれるシートのKey値に対応するインデックスを配列から探す
④一致したkeyが配列にあればセルに出力
⑤ ②に戻り②～⑤繰り返し
配列にがばっと入れるのはクラスのdic登録、コレクション登録より早かった。（そんなに変わらないけど）
①のセル範囲を指定して、がばっと入れるコード。
Dim v As Variant
With Worksheets(“もとデータ”)
v = .Range(“A2:E100001”).Value
End With
③④が遅い！100行くらいだったらいいんだろうけど。
で、ディクショナリで配列のkeyに対するインデックスを登録することにした。
D：配列とディクショナリを使ったデータ流し込み
①－１配列にもとデータをがばっと入れる（ここはCと同じ）
①－２配列のkeyとインデックス（配列の何行目か）をディクショナリ登録
②いれるシートの最初の行から最後の行まで
③いれるシートのKey値に対応するインデックスをディクショナリのアイテムから求める
④配列の該当インデックスのデータをセルに出力
⑤ ②に戻り②～⑤繰り返し
①－２で、ディクショナリのキーに配列の1列目の値（key値)、アイテムに配列のインデックス(配列の何行目か)を代入
For idx = LBound(v, 1) To UBound(v, 1) 配列の最初から最後まで
配列vの1列目のkey値をkeyに、idx（何行目か）をitemに登録
dic.Add v(idx, 1), idx
Next idx
③でディクショナリで配列の何行目かをループ処理なく1回で取り出し
①－２の処理の分ちょっと余計な時間がかかるけど、こっちの方が断然早い～。
With Worksheets(“入れるシート”)
For i = 2 To 100001 最初の行から最後の行まで
「入れるシート」A列のkeyを変数vkeyに代入
vkey = .Cells(i, 1).Value
vkeyに対するアイテム（配列ｲﾝﾃﾞｯｸｽ：配列の何行目か） をidxに代入 idx = dic(vkey)
ヒットした位置の値をセルに打ち出し
（いったん配列に入れていっぺんに入力処理。ここはどうでもいい）
.Range(.Cells(i, 3), .Cells(i, 5)).Value = Array(v(idx, 3), v(idx, 4), v(idx, 5))
Next i
End With
どうせなら、出力セル範囲がつながっているなら、配列にストックしてがばっと入れる方法ならもっと早いかも。
Ｅ：配列（元データ）とディクショナリと配列（出力データ）を使ったデータ流し込み
①－１配列にもとデータをがばっと入れる（ここはCと同じ）
①－２配列のkeyとインデックス（配列の何行目か）をディクショナリ登録
②動的配列の宣言：配列（出力データ）
③入れるシート、最初の行からの最後の行まで
④入れるシート対象行のkey値取得
⑤対応する配列(もとデータ）インデックスをディクショナリから求める
⑥配列（出力データ）要素数を1増やす
⑦key値に対応する配列（元データ）の内容を配列（出力データ）に代入
④～⑦繰り返し。配列（出力データ）にもとデータの内容が出力したい順番に
ストックされていく
⑧配列（出力データ）に入れた値を一気に入れるシートに打ち出し
※配列の要素数の変更ができるのは最後の要素のみなので、要素数を増やしていくのは2番目の要素。出力時にWorksheetFunction.Transposeで縦横入れ替えて出力。
がばっと入れて、がばっと出すのが一番早い―。でもーVlookup的なVBAを使いたいときって、入れる行列が一つながりになっていないことが多いかも・・・。途中で余計な小計行・列があったりするからな・・・。
♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～♪～
以下、コードです。
（ディクショナリ利用の参照設定（microsoft scripting runtime)は設定済のコードです）
◆C：配列を使ったデータ流し込み
‘配列にセル範囲で一気にデータを入れる…①
Dim v As Variant
With Worksheets(“もとデータ”)
v = .Range(“A2:E100001”).Value
End With
‘「入れるシート」に打ち出し処理
Dim i As Long, j As Long, vkey As Long
With Worksheets(“入れるシート”)
For i = 2 To 100001 ‘最初の行から最後の行まで…②
vkey = .Cells(i, 1).Value ‘「入れるシート」A列のkeyを変数vkeyに代入
For j = LBound(v, 1) To UBound(v, 1) ‘配列の最初から最後までvkeyを探索③
If vkey = v(j, 1) Then ‘ヒットした位置の値をセルに打ち出し…④
.Cells(i, 3).Value = v(j, 3)
.Cells(i, 4).Value = v(j, 4)
.Cells(i, 5).Value = v(j, 5)
Exit For ‘jのループ抜ける。i次の行の探索
End If
Next j
Next i
End With
‘配列にセル範囲で一気にデータを入れる…①
Dim v As Variant
With Worksheets(“もとデータ”)
v = .Range(“A2:E100001”).Value
End With
‘ディクショナリのキーに配列の1列目の値（no)、アイテムに配列のインデックス(何行目か)を代入…①－２
Dim dic As Dictionary
Set dic = New Dictionary
Dim idx As Long
For idx = LBound(v, 1) To UBound(v, 1) ‘配列の最初から最後まで
dic.Add v(idx, 1), idx ‘ディクショナリ代入処理
Next idx
‘「入れるシート」に打ち出し処理
Dim i As Long, vkey As Long
With Worksheets(“入れるシート”)
For i = 2 To 100001 ‘最初の行から最後の行まで
vkey = .Cells(i, 1).Value ‘「入れるシート」A列のkeyを変数vkeyに代入
’vkeyに対するアイテム（配列インデックス）をidxに代入
‘ヒットした位置の値をセルに打ち出し
‘（いったん配列に入れていっぺんに入力処理）…どうでもいい変更箇所
.Range(.Cells(i, 3), .Cells(i, 5)).Value = Array(v(idx, 3), v(idx, 4), v(idx, 5))
Next i
End With
‘配列にセル範囲で一気にデータを入れる…①
Dim v As Variant
With Worksheets(“もとデータ”)
v = .Range(“A2:E100001”).Value
End With
‘ディクショナリのキーに配列の1列目の値（no)、アイテムに配列のインデックス(何行目か)を代入…①－２
Dim dic As Dictionary
Set dic = New Dictionary
Dim idx As Long
For idx = LBound(v, 1) To UBound(v, 1) ‘配列の最初から最後まで
dic.Add v(idx, 1), idx ‘ディクショナリ代入処理
Next idx
‘「入れるシート」に打ち出し処理
Dim i As Long, vkey As Long
Dim tmp() As Long ‘配列tmpを用意しておく…②
With Worksheets(“入れるシート”)
For i = 2 To 100001 ‘最初の行から最後の行まで
vkey = .Cells(i, 1).Value ‘「入れるシート」A列のkeyを変数vkeyに代入④
‘vkeyに対するアイテム（配列インデックス）をidxに代入…⑤
‘配列tmp再宣言。一つ行を増やす。※変更できるのは最後の要素のみ…⑥
ReDim Preserve tmp(1 To 3, 1 To i – 1)
‘配列tmpにいったん代入してストック。…⑦
tmp(1, i – 1) = v(idx, 3)
tmp(2, i – 1) = v(idx, 4)
tmp(3, i – 1) = v(idx, 5)
Next i
‘配列tmpに入れた値を一気に「入れるシート」に打ち出し…⑧
‘縦横が逆なので、 WorksheetFunction.Transposeで縦横変換
.Range(“C2:E100001”).Value = WorksheetFunction.Transpose(tmp)
End With
コメント