Office TANAKA - VBA高速化テクニック(セルを配列に入れる)

トップページ > Excel > VBA > 高速化テクニック > セルを配列に入れる

セルを配列に入れる

膨大なセルにデータが入力されていた場合、高速にデータを参照する究極の方法があります。それは、セルのデータを配列に格納するテクニックです。何はともあれ検証結果をご覧ください。
下の検証は、10,000行×100列＝1,000,000セルのデータにアクセスするテストです。セルにはランダムな数値が入力されています。

	Test1	Test2	%
1回目	00:19	00:02	10.5%
2回目	00:18	00:01	5.6%
3回目	00:18	00:02	11.1%
4回目	00:18	00:02	11.1%
5回目	00:18	00:01	5.6%
6回目	00:19	00:02	10.5%
7回目	00:18	00:02	11.1%
8回目	00:18	00:01	5.6%
9回目	00:18	00:02	11.1%
10回目	00:18	00:01	5.6%
平均	00:18	00:02	8.8%

Test1は、For Nextステートメントを使ってセルにアクセスしています。おそらく、これが一般的な方法のひとつでしょう。
対してTest2では、Range("A1:CV10000")をバリアント型変数Cに代入します。
Range("A1:CV10000")のデータは配列形式ですから、変数Cも配列になり、以降はC(1, 2)のような記述で操作できるようになります。

最初は、10,000行×10列＝100,000セルで速度を測ったのですが、それだとTest2は1秒未満で終わってしまうのでセルの数を増やしました。

Sub Test1()
    Dim i As Long, j As Long, buf As Long
    For i = 1 To 10000
        For j = 1 To 100
            buf = Cells(i, j)
        Next j
    Next i
End Sub

Sub Test2()
    Dim i As Long, j As Long, buf As Long, C As Variant
    C = Range("A1:CV10000")
    For i = 1 To 10000
        For j = 1 To 100
            buf = C(i, j)
        Next j
    Next i
End Sub

1,000,000個のデータにアクセスして1秒～2秒なのですから、1回のアクセスにかかる体感時間は、ほぼ0に近いでしょう。どちらもオンメモリの処理とはいえ、CellsプロパティではどうしてもExcelによる評価が発生します。メモリ上の配列に直接アクセスする方が高速なのは明かです。このテクニックは、セルにアクセスする回数が多いほど効果が大きいです。

もちろん、セルに書き込むケースでも配列方式は有効です。単純な例ですが、次のようにできます。

Sub Sample()
    Dim C As Variant
    C = Range("A1:A10000")
    Range("B1:B10000") = C
End Sub

「それは、Range("B1:B10000").Value = Range("A1:A10000").Valueと同じでは？」という問題ではありません。Rangeオブジェクトに配列を代入することで、複数のセルに複数のデータを一度で書き込めることがポイントなのです。
なお、セルに代入する配列は二次元配列でなければいけません。上記Sampleのように、たとえ1列のセル範囲に代入する場合でも、配列は二次元配列として作成します。

ただし、注意していただきたいのは、配列にはセルのデータだけしか格納されないという点です。セルのデータに高速アクセスは可能ですが、セルのプロパティなどは操作できません。扱えるのはあくまでデータだけです。
一般的なマクロでは、セルのデータをただ読み書きするだけというケースは希でしょう。もう少しだけ実用的なケースで検証してみます。

A列には名前、B列にはランダムな数字が入力されています。
データは全部で10,000件。セル範囲A1:B10000に入力されています。
この中から「田中」のデータを探してみます。
なお「田中」は10,000行目のセルA10000にだけ入力されています。

ここでは3通りの方法で速度を測ってみましょう。
Test1はFindステートメントで「田中」を検索します。
Test2はワークシート関数のVLOOKUPを使います。VBAで使うワークシート関数の高速性は「関数も使え！」をご覧ください。
そしてTest3が今回のテーマである配列方式です。

まずは、それぞれのコードをご覧ください。該当するB列を変数Ansに格納する処理を100回繰り返します。
また今回は趣向を変えて、ミリ秒単位で計測してみましょう。

Sub Test1()
    Dim Ans As Long, i As Long
    For i = 1 To 100
        Ans =  Range("A1:B10000").Find(What:="田中", After:=Range("A1")).Offset(0, 1)
    Next i
End Sub

Sub Test2()
    Dim Ans As Long, i As Long
    For i = 1 To 100
        Ans =  WorksheetFunction.VLookup("田中", Range("A1:B10000"), 2, False)
    Next i
End Sub

Sub Test3()
    Dim C As Variant, Ans As Long, i As Long, j As Long
    C = Range("A1:B10000")
    For i = 1 To 100
        For j = 1 To 10000
            If C(j, 1) = "田中" Then Ans = C(j, 2)
        Next j
    Next i
End Sub

	Find	VLOOKUP	配列
1回目	3795	701	981
2回目	3585	691	972
3回目	3636	701	981
4回目	3575	671	952
5回目	3575	711	971
6回目	3625	711	1032
7回目	3585	681	1001
8回目	3545	701	991
9回目	3556	731	982
10回目	3645	751	971
平均	3612.2	705	983.4

1位はVLOOKUP関数でしたが、配列方式の結果も驚きです。
セル検索の基本Findステートメントが大きく引き離されていますが、100回の処理で4秒もかからないのですから、決して悪い結果ではありません。他の2つが速すぎるのです。
VLOOKUPなどワークシート関数はとても高速です。ただし、ワークシート関数は用途が限られます。柔軟な処理を超高速に行いたいのでしたら、配列方式を検討するといいでしょう。
もっとも、使いこなすにはそれ相当のスキルが必要ですから、十分注意してくださいね。

[セルの指定方法]戻る← | →進む[文字列型関数を使う]