なぜ配列をつかうのか、爆速で終わらせる配列コード編

配列を使って高速処理を手に入れる、効率的な配列コード技術で作業を加速 動画編 106回

動画でExcel　なぜ配列をつかうのか、爆速で終わらせる配列コード編

配列を使って高速処理を手に入れる

動画版「マクロ講座１０６回」です。
わざわざなぜ配列を使うのか？配列を使うと処理が爆速化、今までの1/100の所要時間で終了します。そのコード例を紹介しています。

この記事で学べる内容

• 配列を使うとExcelマクロの実行速度が向上する理由
• Excel VBAコードで配列を宣言して使う方法
• 条件を用いて配列から特定のデータを抽出する技術
• Timer関数を使って配列処理にかかる時間を測定する方法
• 配列ベースのコードと従来の方法の効率性を比較する

（サンプルファイルは、こちらです。 なぜ配列をつかうのか、爆速で終わらせる配列コード編、サンプル106回）

マクロ動画　なぜ配列をつかうのか、爆速で終わらせる配列コード編

タイマー関数で配列の処理の速さを計測する

1. プロシージャに名前をつけて変数を設定しました。タイマーで使う変数を2つ作っています。
   Dim starttime As Single, stoptime As Single

   

2. タイマー関数を使って、タイマーをつけています。
   タイマー関数は、午前 0 時からの経過秒数を表す単精度浮動小数点数型 (Single) の値を返します。
   実行された時にスタートタイムで時間を取得します。
   starttime = Timer

   

3. 次に配列を宣言します。
   allmydataArr as Variant ということで 配列を Variant で宣言しました。

   

   allmydataArrは、どこから取得するのか、それは何かっていうわけで、それは、
   allmydataArr = Range("A1").CurrentRegion.Value　だと記述しています。

4. シートにデータがあるので1個ずつデータを入れていくわけではありません。
   Range("A1").CurrentRegion　
   これでも配列ができてしまいました。入れて作るのって簡単ですよね。ここが素晴らしいです。 ですよっていうことです。

   

5. そしてこの配列はデータの抽出元ですから、そこから条件に該当するデータを見つけます。みつけたデータ
   を別の配列に入れなければいけません。
   そこでpickedDataArrを作成します。
   Dim pickedDataArr(1 To 20000, 1 To 8) As Variant
   pickedDataArrは、まだ要素数がわかりませんが、今回は1次元の要素数を2万件、2次元の要素数を8件としました。

   抽出元と同じサイズです。動的配列にしないで、要素は空でもOkですから、そこに代入していくのです。

   

元の配列から取り出す条件をIF文で指定する

6. 生年月日を条件の１つにするために、変数を作ります。
   Dim Specifieddate As Date: Specifieddate = "1990/1/1"
   

   

   データ抽出の作業は、For文を使って行いますので、カウンタ変数として I と J を宣言しました。
   Dim i As Long, j As Long
   それとは別に見つかったデータを　hitdataという名前をつけて番号をつけてカウントします。

7. For文を使って該当するデータを探しますが、カウンタ変数iの値は allmydataArrという配列元の配列のエルバウンドからユーバウンドまでの間です。
   For i = LBound(allmydataArr, 1) To UBound(allmydataArr, 1)
   
   Next i

   

8. 最初の条件は、配列の
   allmydataArr(i, 4) = "女"　が女であること、 If allmydataArr(i, 4) = "女"
   もう一つは、配列の allmydataArr(i, 8) >= Specifieddate　が指定日よりも大なりイコールであることです。
   And allmydataArr(i, 8) >= Specifieddate

   

9. それを満たすデータがあったときは、hitdataというカウント用の変数に+1してやります。
   そして見つかったデータの方はもうひとつの配列 pickedDataArr に入れてやるわけです。

   この pickedDataArrに入れてやる作業は一つ見つかる事にしてやります。

   

10. 後はもうこのFor文の中身を繰り返すだけです。 最後のデータまで終了したら、指定したワークシート上にデータを転記してやる。 その後タイマーを止めるという流れになります。

    

配列で高速処理の実行結果

11. こちらが配列で2万件のデータを取り込んで、そして条件に合うデータをもうひとつの配列に登録します。
    作業が終了した時点でワークシートにそのデータを転記して、ストップタイマーを止めるという流れのコードです。

    Sub タイム測定Arrayでフィルタ()
        Dim starttime As Single, stoptime As Single
        starttime = Timer
        Dim allmydataArr As Variant
        allmydataArr = Range("A1").CurrentRegion.Value
        
        Dim pickedDataArr(1 To 20000, 1 To 8) As Variant
        Dim Specifieddate As Date: Specifieddate = "1990/1/1"
        Dim i As Long, j As Long
        Dim hitdata As Long: hitdata = 0
            For i = LBound(allmydataArr, 1) To UBound(allmydataArr, 1)
                 If allmydataArr(i, 4) = "女" And allmydataArr(i, 8) >= Specifieddate Then
                 hitdata = hitdata + 1
                    For j = 1 To 8
                        pickedDataArr(hitdata, j) = allmydataArr(i, j)
                    Next j
                End If
            Next
        Range("M2:T" & hitdata + 1) = pickedDataArr
        stoptime = Timer
        MsgBox hitdata & "件該当ありました。" & vbCrLf & Round(stoptime - starttime, 2)
        
    End Sub

12. 実行結果は、配列で処理した結果所要時間は0.2秒でした。

    

13. 一方配列を使わない通常の処理方法で作業したところ100倍以上時間がかかってしまいました。この場合34.1秒でした。

    

配列処理が高速になる理由を考察

14. 同じ処理を配列を使った場合と配列を使わなかった場合の処理時間が、大幅に違ったと言う結果をご覧いただいただけでも、いかに配列が高速かということがお分かり頂けたと思います。その理由を考察して下記に記載しました。