🧈
Excel VBA 高速化するならセルに代入する回数を減らすと効果的
とりあえずおまじない。ではなく、セルに代入する回数をいかに減らすかを意識すると良いかも
たまたま見つけて読んだ記事で、単純にセルに書き込む回数を減らしたら良さそう。と思ったので自分でも書いてみました。
記事のコードでは30000回セルに代入していました。
説明
- やること
- A列に縦に並んでいるテキストを10行単位でB列から横に並べ替えます。
- 1～10
- 11～20
- 21～30
- A列に縦に並んでいるテキストを10行単位でB列から横に並べ替えます。
こんな感じ。
準備
-
A1セルに
=SEQUENCE(30000)と入力します。
-
VBAで書く前に実際にシートを使ってイメージを掴んでおきます。
- B1セルに
=TRANSPOSE(A1:A10)と入力すると、1から10まで横に入ります。
- B2セルはA列の参照先が10下がって11から20になるので、数式は
=TRANSPOSE(A11:A20)になります。
- B1セルに
この数式を3000行配列に入れ、セル範囲に1回で代入します。
コード
Option Explicit
Sub 計測()
Dim i As Integer
For i = 1 To 10
Dim Start As Double
Start = Timer
Call 転記
Debug.Print Timer - Start; "秒"
Columns("B:K").Delete
Next i
End Sub
Sub 転記()
Dim Rows As Long, Col As Long, RC As Long
Rows = Range("A1").CurrentRegion.Rows.Count
Col = 10
RC = Rows / Col
Dim B() As Variant
ReDim B(RC - 1, 0)
Dim i As Long, j As Long, Row As Long
i = 0
j = Col
For Row = 1 To Rows Step Col
B(i, 0) = "=TRANSPOSE(A" & Row & ":A" & j & ")"
i = i + 1
j = j + Col
Next Row
Range(Cells(1, 2), Cells(RC, 2)).Formula2 = B
End Sub
数式をそのままにせず値にしたい場合は以下のようにします。
- Range(Cells(1, 2), Cells(RC, 2)).Formula2 = B
+ With Range(Cells(1, 2), Cells(RC, 2))
+ .Formula2 = B
+ .Resize(, Col).Value = .Resize(, Col).Value
+ End With
元のコードに合わせて、転記する行単位を変数に入れています。
配列を使っても、For文の中でセルに代入していたら意味がなくなるので注意です。
ループの中は配列にいれるだけ。
セルを1つずつ操作するのは遅いので、セルに3000回代入するより
ループを抜けてからその配列を1回だけ代入する方が速い。
実行結果
| 回 | スピル | スピル→値 |
|---|---|---|
| 1 | 0.0390625 秒 | 0.08203125 秒 |
| 2 | 0.02734375 秒 | 0.07421875 秒 |
| 3 | 0.03125 秒 | 0.0703125 秒 |
| 4 | 0.03125 秒 | 0.0703125 秒 |
| 5 | 0.02734375 秒 | 0.07421875 秒 |
| 6 | 0.03125 秒 | 0.0703125 秒 |
| 7 | 0.02734375 秒 | 0.0703125 秒 |
| 8 | 0.02734375 秒 | 0.0703125 秒 |
| 9 | 0.0234375 秒 | 0.0703125 秒 |
| 10 | 0.02734375 秒 | 0.07421875 秒 |
おまじないはなくても十分に思うのですがどうでしょう。
数式から値に貼り付けなおしても0.1秒未満で出来ました。
マクロを劇的に速くする方法はおまじないではなく、セルに代入する回数を減らすことです。
ということを言いたいがために今回の記事を書きました。
そしてオチがあります。
↓
スピルだけで出来る
B1セルに=INDEX(A1#,SEQUENCE(3000,10))と入力すると一発です。
VBA使う必要なかったですね。
追記
=WRAPROWS(A1#,10)を使うと関数のネストなしで出来ますね。
おわりに
ということで、ぜひスピルを持って帰ってください。
Discussion