機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > VBA > VBA高速化テクニック

セルを配列に入れる



膨大なセルにデータが入力されていた場合、高速にデータを参照する究極の方法があります。それは、セルのデータを配列に格納するテクニックです。何はともあれ検証結果をご覧ください。

下の検証は、10,000行×100列=1,000,000セルのデータにアクセスするテストです。セルにはランダムな数値が入力されています。

 Test1Test2%
1回目00:1900:0210.5%
2回目00:1800:015.6%
3回目00:1800:0211.1%
4回目00:1800:0211.1%
5回目00:1800:015.6%
6回目00:1900:0210.5%
7回目00:1800:0211.1%
8回目00:1800:015.6%
9回目00:1800:0211.1%
10回目00:1800:015.6%
平均00:1800:028.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

 FindVLOOKUP配列
1回目3795701981
2回目3585691972
3回目3636701981
4回目3575671952
5回目3575711971
6回目36257111032
7回目35856811001
8回目3545701991
9回目3556731982
10回目3645751971
平均3612.2705983.4
1位はVLOOKUP関数でしたが、配列方式の結果も驚きです。
セル検索の基本Findステートメントが大きく引き離されていますが、100回の処理で4秒もかからないのですから、決して悪い結果ではありません。他の2つが速すぎるのです。
VLOOKUPなどワークシート関数はとても高速です。ただし、ワークシート関数は用途が限られます。柔軟な処理を超高速に行いたいのでしたら、配列方式を検討するといいでしょう。

もっとも、使いこなすにはそれ相当のスキルが必要ですから、十分注意してくださいね。



このエントリーをはてなブックマークに追加