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

マクロ講座 動画編 106回

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

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

動画版「マクロ講座１０６回」です。
わざわざなぜ配列を使うのか？配列を使うと処理が爆速化、今までの1/100の所要時間で終了します。そのコード例を紹介しています。
（サンプルファイルは、こちらです。 なぜ配列をつかうのか、爆速で終わらせる配列コード編、サンプル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秒でした。