最近はVBAを使って色々やってる貴姫です。このエントリーは備忘録ですよー？
セルへの値の書き込みが猛烈に遅いスクリプト
シートが2つあり、それぞれのシートに記載された値を元に、星取表に星を入れる必要があります。
あなたはどんなスクリプトを書きますか？
私の場合はこんな感じでした。
- Sheet1（下記1）にある指定の値をArray1に（一次元配列）
- Sheet2（下記2）にある指定の値をArray2に（一次元配列）
- それぞれの値を比較
- 一致したらSheet2（下記2）にある、Array2の値に対応する星取表の値をSheet1（下記1）に書き込む
- Array1 には 重複あり で 2000 くらいの値
- Array2 には 重複なし で 100 くらいの値
Original
For i = 0 To UBound(Array1)
For ii = 0 To UBound(Array2)
If Array1(i) = Array2(ii) Then
Worksheets("1").Cells(i + 1, 1).Value = Worksheets("2").Cells(ii + 1, 1).Value
Worksheets("1").Cells(i + 1, 2).Value = Worksheets("2").Cells(ii + 1, 2).Value
Worksheets("1").Cells(i + 1, 3).Value = Worksheets("2").Cells(ii + 1, 3).Value
Worksheets("1").Cells(i + 1, 4).Value = Worksheets("2").Cells(ii + 1, 4).Value
Worksheets("1").Cells(i + 1, 5).Value = Worksheets("2").Cells(ii + 1, 5).Value
Worksheets("1").Cells(i + 1, 6).Value = Worksheets("2").Cells(ii + 1, 6).Value
End If
Next
Next
結構べたな書き方ですよね。
ただしArray内の値の数によっては激しく処理に時間が掛かってしまうわけです。当然ですよね！ だって、1行に6個の値があって、それが2000行続くんですから。
「さすがに時間かかりすぎ」だし「仕事があんまり忙しくない」って事もあり、高速な処理を色々調べてみました！ と言っても、全部書いたりはしないです。
最適化されたよ！
さて、答えにすぐ辿り着いてしまった訳ですが・・・ それは以下のような感じでした。
Amelioration
' セルから配列に入れる
For i = 0 To UBound(Array1)
For ii = 0 To UBound(Array2)
If Array1(i) = Array2(ii) Then
' 配列に書き込む
With Worksheets("2")
Array3(i, 0) = .Cells(ii + 3, 2).Value
Array3(i, 1) = .Cells(ii + 3, 3).Value
Array3(i, 2) = .Cells(ii + 3, 4).Value
Array3(i, 3) = .Cells(ii + 3, 5).Value
Array3(i, 4) = .Cells(ii + 3, 6).Value
Array3(i, 5) = .Cells(ii + 3, 7).Value
End With
Exit For
End If
Next
Next
' 配列をセルに戻す
Worksheets("1").Range(Cells(1, 1), Cells(LastRow, 5)) = Array3
話はすごい単純。
セルから読んだ該当する星取表の値を直に目的のセルに入れるのではなく一旦配列（Array3）に入れます。
そうしたらその配列をセル範囲を指定して一気に書きこむだけです。
ちなみに貴姫が実際にテストした範囲だと、前者のスクリプトでは途中でExcelが応答なしになったりしつつ、2450行のデータ処理に約8分くらいかかりました。まあ正確な時間は計ってなくて、完全な体感ではありますが（2回も3回もやりたくなかった）。
そして後者の配列を一気に書きこむ方法だとなんとびっくり数秒で終わりました。
配列万歳！