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

動画でExcel なぜ配列をつかうのか、爆速で終わらせる配列コード編
配列を使って高速処理を手に入れる
動画版「マクロ講座106回」です。
わざわざなぜ配列を使うのか?配列を使うと処理が爆速化、今までの1/100の所要時間で終了します。そのコード例を紹介しています。
この記事で学べる内容
- 配列を使うとExcelマクロの実行速度が向上する理由
- Excel VBAコードで配列を宣言して使う方法
- 条件を用いて配列から特定のデータを抽出する技術
- Timer関数を使って配列処理にかかる時間を測定する方法
- 配列ベースのコードと従来の方法の効率性を比較する
(サンプルファイルは、こちらです。 なぜ配列をつかうのか、爆速で終わらせる配列コード編、サンプル106回)
マクロ動画 なぜ配列をつかうのか、爆速で終わらせる配列コード編
タイマー関数で配列の処理の速さを計測する
-
プロシージャに名前をつけて変数を設定しました。タイマーで使う変数を2つ作っています。
Dim starttime As Single, stoptime As Single -
タイマー関数を使って、タイマーをつけています。
タイマー関数は、午前 0 時からの経過秒数を表す単精度浮動小数点数型 (Single) の値を返します。
実行された時にスタートタイムで時間を取得します。
starttime = Timer -
次に配列を宣言します。
allmydataArr as Variant ということで 配列を Variant で宣言しました。allmydataArrは、どこから取得するのか、それは何かっていうわけで、それは、
allmydataArr = Range("A1").CurrentRegion.Value だと記述しています。 -
シートにデータがあるので1個ずつデータを入れていくわけではありません。
Range("A1").CurrentRegion
これでも配列ができてしまいました。入れて作るのって簡単ですよね。ここが素晴らしいです。 ですよっていうことです。 -
そしてこの配列はデータの抽出元ですから、そこから条件に該当するデータを見つけます。みつけたデータ
を別の配列に入れなければいけません。
そこでpickedDataArrを作成します。
Dim pickedDataArr(1 To 20000, 1 To 8) As Variant
pickedDataArrは、まだ要素数がわかりませんが、今回は1次元の要素数を2万件、2次元の要素数を8件としました。抽出元と同じサイズです。動的配列にしないで、要素は空でもOkですから、そこに代入していくのです。
元の配列から取り出す条件をIF文で指定する
-
生年月日を条件の1つにするために、変数を作ります。
Dim Specifieddate As Date: Specifieddate = "1990/1/1"
データ抽出の作業は、For文を使って行いますので、カウンタ変数として I と J を宣言しました。
Dim i As Long, j As Long
それとは別に見つかったデータを hitdataという名前をつけて番号をつけてカウントします。 -
For文を使って該当するデータを探しますが、カウンタ変数iの値は allmydataArrという配列元の配列のエルバウンドからユーバウンドまでの間です。
For i = LBound(allmydataArr, 1) To UBound(allmydataArr, 1)
Next i -
最初の条件は、配列の
allmydataArr(i, 4) = "女" が女であること、 If allmydataArr(i, 4) = "女"
もう一つは、配列の allmydataArr(i, 8) >= Specifieddate が指定日よりも大なりイコールであることです。
And allmydataArr(i, 8) >= Specifieddate -
それを満たすデータがあったときは、hitdataというカウント用の変数に+1してやります。
そして見つかったデータの方はもうひとつの配列 pickedDataArr に入れてやるわけです。この pickedDataArrに入れてやる作業は一つ見つかる事にしてやります。
-
後はもうこのFor文の中身を繰り返すだけです。 最後のデータまで終了したら、指定したワークシート上にデータを転記してやる。 その後タイマーを止めるという流れになります。
配列で高速処理の実行結果
-
こちらが配列で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
-
実行結果は、配列で処理した結果所要時間は0.2秒でした。
-
一方配列を使わない通常の処理方法で作業したところ100倍以上時間がかかってしまいました。この場合34.1秒でした。
同じ処理を配列を使った場合と配列を使わなかった場合の処理時間が、大幅に違ったと言う結果をご覧いただいただけでも、いかに配列が高速かということがお分かり頂けたと思います。その理由を考察して下記に記載しました。
配列処理が高速になる理由を考察
メモリ効率: 配列を使用すると、データがメモリ上に連続的に配置されるため、コンピュータがデータにアクセスしやすくなります。これにより、データの読み取りや書き込みが高速になります。
セル操作の最小限化: 配列を使うことで、Excelシート上のセル操作が最小限になります。セルへのアクセスは比較的遅いため、セルの読み書き回数を減らすことで、全体的な処理速度が向上します。
一括処理: 配列を用いると、一度に多くのデータを操作できるため、ループ処理などのオーバーヘッドが削減されます。これにより、処理が高速化されます。