イントロダクション
前回（第24回）は、For で表を1行ずつ処理して、条件に合う行を別のシートに転記するマクロを作った。サンプルは10行ほどの小さな表だったので、一瞬で終わったはずである。
しかし、実務の表は、数万行、数十万行になることもある。1行ずつセルを読み書きする方法は、行数が増えると、処理に時間がかかるようになる。セルを読み書きするたびに、VBAとExcel本体の間で、やり取りが発生するためである。
そこで今回は、表を配列に一括で取り込み、配列の上で処理して、結果を一括で書き戻す方法をご紹介しよう。表データの処理編の最終回として、実際に処理時間を測って、違いを確かめてみよう。
- 第23回：データの最終行を取得する
- 第24回：表を1行ずつ処理する（条件に合う行を抽出して転記する）
- 第25回（今回）：配列を使って、処理を高速化する
第15回で学んだ配列の知識が、ここで活きてくる。
準備
今回のマクロは、これまでに作った次の部品を使う。
同じ標準モジュールに入っていることを確認しておこう。
| 部品 | 作った回 | 役割 |
|---|---|---|
最終行を取得する（Function） | 第23回 | 最終行を求める |
シートを用意する（Function） | 第24回 | 指定した名前のシートを作る（あれば中身を消す） |
担当者つきサンプルデータを作る（Sub） | 第24回 | 「売上データ」シートに、10件のサンプルを作る |
最初の実験では、第24回で作った10件の「売上データ」シートを使う。
次の実験では、速度の違いが分かるように、10万行の表を用意する。
【注意】実行前・実行後
練習用のブックで試そう
今回のマクロは、次の3つのシートを、自動で作る。すでに同じ名前のシートがある場合は、内容を消してから使うので、必ず練習用のブックで試そう。
- 大量データ
- 抽出_1行ずつ
- 抽出_配列
また、以下で試す「1行ずつ」の処理は、時間がかかることがあり、その間は、Excelが応答しなくなったように見える。終わるまで待とう。
なお、他のブックで作業しているときは、先に保存・終了してから実行しよう。
処理に時間がかかる場合もあることから、パソコンへの負担がご心配なら、大量データを作る の 件数 を、50000 などに小さくして、試してください。
なお、途中で Esc キーなどで止めたときは、後始末が実行されず、画面の更新が止まったままになることがある。その場合は、イミディエイトウィンドウに、Application.ScreenUpdating = True と入力して、Enter を押そう。
また、実験が終わったら、「大量データ」「抽出_1行ずつ」「抽出_配列」の3つのシートを、削除しておこう。10万行のデータが入ったままだと、ブックのファイルの容量が大きくなり、開いたり保存したりするのに、時間がかかるようになる。
配列に一括で取り込む
まず、基本の動きを確かめよう。Range の Value を、Variant 型の変数に代入すると、セル範囲の値が、2次元の配列として一括で取り込まれる。
Sub 配列に取り込む()
Dim ws As Worksheet
Dim データ As Variant
Dim 最終行 As Long
Dim 行 As Long
Set ws = ThisWorkbook.Worksheets("売上データ")
最終行 = 最終行を取得する(ws, 1)
If 最終行 < 2 Then
MsgBox "データがありません。", vbExclamation
Exit Sub
End If
データ = ws.Range("A2:D" & 最終行).Value
Debug.Print "行数：" & UBound(データ, 1) & "、列数：" & UBound(データ, 2)
For 行 = 1 To UBound(データ, 1)
Debug.Print データ(行, 2) & "：" & データ(行, 3)
Next 行
End Sub
実行すると、イミディエイトウィンドウ（Ctrl + G）に、「行数：10、列数：4」に続いて、商品名と売上が10行ぶん表示される。
ポイントは、次の3つである。
データ = ws.Range("A2:D" & 最終行).Value
A2からD列の最終行までを、1回の代入で、配列に取り込んでいる。データ部分だけを取り込むので、見出しの1行目は含めていないデータ(行, 列)
配列の要素は、「何行目の、何列目か」で指定する。ws.Cells(行, 列)と同じ感覚で使えるUBound(データ, 1)とUBound(データ, 2)
配列の行数と列数を調べる。UBoundの2つ目の引数が、何番目の次元かを表す（1が行、2が列）
注意したいのは、セルから取り込んだ配列の番号は、1から始まることである。
第15回で扱った Array 関数で作った配列は0から始まるが、セルから取り込んだ配列は、1から始まる。また、最終行が1（データなし）のまま A2:D1 のような範囲を指定すると、意図しない範囲になってしまう。そのため、最初に 最終行 < 2 かどうかを確認している（第19回の事前チェックである）。
速度を比べるため10万行の表を作る
それでは、速度の違いが分かる、大きな表を用意しよう。
10万行ぶんのデータを、配列を使って、一括でシートに書き込むマクロである。
ここでも、配列の「一括で書き戻す」動きが使われている。
Sub 大量データを作る()
Const 件数 As Long = 100000
Dim ws As Worksheet
Dim データ() As Variant
Dim 商品 As Variant
Dim 担当 As Variant
Dim i As Long
Set ws = シートを用意する("大量データ")
商品 = Array("りんご", "みかん", "ぶどう", "バナナ", "メロン")
担当 = Array("田中", "佐藤", "鈴木")
' 配列の上で、10万行ぶんのデータを作る
ReDim データ(1 To 件数, 1 To 4)
For i = 1 To 件数
データ(i, 1) = DateSerial(2026, 1, 1) + (i Mod 300)
データ(i, 2) = 商品(i Mod 5)
データ(i, 3) = 500 + ((i * 37) Mod 2000)
データ(i, 4) = 担当(i Mod 3)
Next i
' 見出しと、データを一括で書き込む
ws.Range("A1:D1").Value = Array("日付", "商品", "売上", "担当")
ws.Range("A2").Resize(件数, 4).Value = データ
ws.Range("A2").Resize(件数, 1).NumberFormat = "m/d"
End Sub「大量データ」というシートが作られ、A1:D100001に、10万件のデータが書き込まれる。実行には、数秒かかることがある。
ReDim データ(1 To 件数, 1 To 4) は、配列の大きさを決める命令である。ws.Range("A2").Resize(件数, 4) は、A2を起点に、件数 行・4列の範囲を作る。書き込み先の範囲の大きさと、配列の大きさを、同じにするのが、一括で書き込むときの基本である。
このデータでは、売上が1,000以上の行は、ちょうど75,000件になる。
後ほどの実験で、確認に使える。
1行ずつ処理する方法
準備ができたので、同じ処理を、2つの方法で作って、時間を比べてみよう。「売上が1,000以上の行を、別シートに抽出する」という処理である。
今回作るのは、次の3つである。実行するのは、最後の 速度を比べる だけで、前の2つは、そこから呼び出される。
抽出_1行ずつ（Function）：1行ずつの方法で抽出して、かかった時間を返す抽出_配列（Function）：配列を使う方法で抽出して、かかった時間を返す速度を比べる（Sub）：2つの Function を呼び出して、時間を比べる。実行の入口になる
引数のある Function は、単独では実行できない。3つともコードを貼り付けてから、速度を比べる を実行しよう。
まず、第24回と同じ「1行ずつ」の方法である。処理にかかった時間を返す Function にしている。
Function 抽出_1行ずつ(ByRef 件数 As Long) As Double
Dim 元 As Worksheet
Dim 先 As Worksheet
Dim 最終行 As Long
Dim 行 As Long
Dim 書込行 As Long
Dim 開始 As Double
Set 元 = ThisWorkbook.Worksheets("大量データ")
Set 先 = シートを用意する("抽出_1行ずつ")
最終行 = 最終行を取得する(元, 1)
先.Range("A1:D1").Value = 元.Range("A1:D1").Value
書込行 = 2
開始 = Timer
For 行 = 2 To 最終行
If 元.Cells(行, 3).Value >= 1000 Then
先.Cells(書込行, 1).Value = 元.Cells(行, 1).Value
先.Cells(書込行, 2).Value = 元.Cells(行, 2).Value
先.Cells(書込行, 3).Value = 元.Cells(行, 3).Value
先.Cells(書込行, 4).Value = 元.Cells(行, 4).Value
書込行 = 書込行 + 1
End If
Next 行
件数 = 書込行 - 2
抽出_1行ずつ = Timer - 開始
End FunctionTimer は、午前0時からの経過秒数を返す関数である。処理の前後で Timer の値を取り、その差を求めれば、処理にかかった時間（秒）が分かる。
件数 は ByRef（参照渡し）で受け取っている。Function は、戻り値を1つしか返せないので、「処理時間」を戻り値にして、「抽出した件数」は、引数を通じて呼び出し元に返している。
配列を使う方法
次に、同じ処理を、配列を使って作る。
Function 抽出_配列(ByRef 件数 As Long) As Double
Dim 元 As Worksheet
Dim 先 As Worksheet
Dim 入力 As Variant
Dim 出力() As Variant
Dim 最終行 As Long
Dim 行 As Long
Dim 列 As Long
Dim 出力行 As Long
Dim 開始 As Double
Set 元 = ThisWorkbook.Worksheets("大量データ")
Set 先 = シートを用意する("抽出_配列")
最終行 = 最終行を取得する(元, 1)
先.Range("A1:D1").Value = 元.Range("A1:D1").Value
開始 = Timer
' 1. 表を、配列に一括で取り込む
入力 = 元.Range("A2:D" & 最終行).Value
' 2. 条件に合う件数を数えて、出力用の配列の大きさを決める
件数 = 0
For 行 = 1 To UBound(入力, 1)
If 入力(行, 3) >= 1000 Then 件数 = 件数 + 1
Next 行
If 件数 > 0 Then
ReDim 出力(1 To 件数, 1 To 4)
' 3. 条件に合う行を、出力用の配列に詰める
出力行 = 0
For 行 = 1 To UBound(入力, 1)
If 入力(行, 3) >= 1000 Then
出力行 = 出力行 + 1
For 列 = 1 To 4
出力(出力行, 列) = 入力(行, 列)
Next 列
End If
Next 行
' 4. 出力用の配列を、一括で書き戻す
先.Range("A2").Resize(件数, 4).Value = 出力
先.Range("A2").Resize(件数, 1).NumberFormat = "m/d"
End If
抽出_配列 = Timer - 開始
End Function流れは、次の4ステップである。
- 表全体を、
入力という配列に、1回で取り込む - 配列の上で、条件に合う件数を数える
- 件数が分かったので、ちょうどの大きさの
出力配列を用意して、条件に合う行を詰める 出力配列を、1回でシートに書き戻す
2番目で、先に件数を数えているのは、出力 配列の大きさを決めるためである。書き込み先の範囲と配列の大きさが一致していないと、意図しない結果になることがある。そのため、いったん件数を数えてから、ちょうどの大きさで用意している。
なお、この Function は、大量データ にデータが入っていることを前提にしている。実務で使うときは、先ほどの 配列に取り込む のように、最終行が2未満（データがない）かどうかを、Range("A2:D" & 最終行) の前に確認しておこう。
「1行ずつの方法」では、セルの読み書きが、10万行ぶん繰り返される。
配列を使う方法では、セルを読み書きするのは、取り込みと書き戻しの、たった2回である。それ以外の処理は、すべて配列の上（VBAの中）で行われる。
実行して時間を比べる
最後に、2つの Function を呼び出して、時間を比べるマクロを作る。画面の更新を止めるのは、どちらの方法でも使われる、定番の高速化である。第18回で学んだ後始末の形も、ここで使っている。
Sub 速度を比べる()
Dim 件数A As Long
Dim 件数B As Long
Dim 時間A As Double
Dim 時間B As Double
On Error GoTo エラー処理
Application.ScreenUpdating = False
時間A = 抽出_1行ずつ(件数A)
時間B = 抽出_配列(件数B)
MsgBox "【1行ずつ】" & 件数A & " 件 " & Format(時間A, "0.00") & " 秒" & vbCrLf & _
"【配列】 " & 件数B & " 件 " & Format(時間B, "0.00") & " 秒"
後始末:
Application.ScreenUpdating = True
Exit Sub
エラー処理:
MsgBox "処理中にエラーが発生しました。" & vbCrLf & _
Err.Number & "：" & Err.Description, vbCritical
Resume 後始末
End Sub必ず大量データを作る を実行してから、速度を比べる を実行しよう。
メッセージボックスに、次のように、件数と時間が表示される。
- 件数は、どちらも「75000 件」になる
- 時間は、パソコンの性能によって変わるが、「1行ずつ」より「配列」のほうが、はるかに短いことが分かる
配列を使うときの注意点
配列は便利だが、いつでも使えばよいわけではない。次の点に注意しよう。
- 取り込まれるのは、値だけ
数式のセルは計算結果の値になり、色や罫線などの書式は、配列には入らない。配列に取り込んだ表を、同じ場所に書き戻すと、数式が値に置き換わってしまうので注意しよう。書式も扱いたいときは、Copyなどのセル操作が必要である - 1つのセルだけの
Valueは、配列にならないRange("A1").Valueは、1つの値が返る。配列として扱えるのは、複数のセルの範囲である - 処理が単純なら、読みやすさを優先する
数百行程度の表なら、1行ずつの方法でも、一瞬で終わる。コードも短く、読みやすい。数万行以上の表で、処理に時間がかかるようになったら、配列の出番である
まとめ
今回は、配列を使った高速化を学んだ。
- 1行ずつセルを読み書きすると、行数が増えたときに、時間がかかる
RangeのValueをVariant型の変数に代入すると、セル範囲が2次元の配列として、一括で取り込める- セルから取り込んだ配列は、1から始まる。
UBound(配列, 1)が行数、UBound(配列, 2)が列数である - 配列の上で処理して、
Range(...).Resize(行数, 列数).Value = 配列で、一括で書き戻せる - 書き込み先の範囲と配列の大きさは、一致させる
Timerで、処理時間を測れる- 取り込まれるのは値だけである。数式や書式が必要なときは、セル操作を使う
表データの処理編では、3回かけて、次のことを学んだ。
| 回 | テーマ | 主な内容 |
|---|---|---|
| 第23回 | 最終行の取得 | End(xlUp)、CurrentRegion、UsedRange |
| 第24回 | 1行ずつ処理 | For、If、And / Or、別シートへの転記、IsNumeric |
| 第25回 | 高速化 | 配列による一括取り込み・一括書き戻し、Timer |
これで、行数が変わる表を、最後まで確実に、しかも速く処理できるようになった。ブック・ファイル操作編と組み合わせれば、「フォルダ内の複数のブックの表を、それぞれ配列で取り込んで、1つにまとめる」といったマクロも作れる。ぜひ、身近な作業で試してみてほしい。
次回からは、新しいテーマに進む予定である。
コメント