なぜ配列を使うのか?配列を使うと100の速さで終了

マクロ講座 動画編 105回
マクロ講座

動画でExcel なぜ配列を使うのか?配列を使うと100の速さで終了

なぜ配列を使うのか?配列を使うと100の速さで終了

動画版「マクロ講座105回」です。
わざわざなぜ配列を使うのか?配列にするメリットは何と思うかもしれません。
配列は取得も簡単で、さらに利点として処理が爆速になると言うことです。
その例を今回お見せします。 こちらは私の動画によく登場する名簿形式のデータです。お名前があり住所氏名がありといった形になっています。
このデータから例えば、女性で生年月日が1985年以降の生まれの方を抽出して化粧品のダイレクトメールを送りたいなどと言うケースがあったとします。
その一覧を書き出す速度について調べてみましょう。処理時間を計算計測するためにVBAマクロのタイマー関数を使います。
タイマー関数は0時からの経過時間を平数で返してくれる関数です。処理の経過時間を計測するときに使うと便利です。
(サンプルファイルは、こちらです。 なぜ配列を使うのか?配列を使うと100の速さで終了、サンプル105回)

マクロ動画 なぜ配列を使うのか?配列を使うと100の速さで終了

なぜ配列を勉強するのかという疑問

1. わざわざ配列を使わなくても、マクロを使えば十分に高速で処理はできます。
配列を使わない場合と配列を使う場合とどこが違うかといったらそのスピードです。

namae1

2. 1万件2万件あるいはもっともっとデータ量が多いなどというケースでは、処理速度が圧倒的に変わってしまいます。
住所録のような、こういったデータベースがあったとします。このサンプルは2万件です。
2万件のリストの中から新商品の案内をお客様に送りたい。

namae2

3. 全部に送ったらダイレクトメールだったら費用がかかりすぎますよね。今回は2万件のデータの中から性別とそれから生年月日などで絞り込みます。


namae3

4. 処理の時間を計測するのでタイマー関数を中で使います。タイマー関数というのは引数はない関数です。 経過した秒数を取得するというもので、午前0時からその時までに経過した秒数を取得する関数です。


namae4

5. そしてこのデータベースを配列に入れてしまうので、配列は2次元配列です。
セル範囲ごっそり配列に入れます。A1セルのカレントリージョンでそっくり範囲を取得できます。


namae5

配列を使った場合の処理時間を計測する

6.変数を決める
スタートタイムとそれからストップタイム、
Dim starttime As Single, stoptime As Single
配列として、Dim allmydataArr As Variant
取得した配列として、
Dim pickedDataArr(1 To 20000, 1 To 8) As Variant

取得した配列のサイズは、元の配列のデータと同じです。
この考え方は箱が大きくても、ちいさいよりはマシということでおおらかに考えます。

7. コードは以下のようになります。

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)
            ' 条件:4列目が「女」 かつ 8列目の日付が 1990/1/1 以降
             If allmydataArr(i, 4) = "女" And allmydataArr(i, 8) >= Specifieddate Then
             hitdata = hitdata + 1
             ' 該当した行の1〜8列目を、受け皿配列にコピー
                For j = 1 To 8
                    pickedDataArr(hitdata, j) = allmydataArr(i, j)
                Next j
            End If
        Next 
    ' --- ⑤ 抽出結果をシートへ一括書き戻し ---
    If hitdata > 0 Then
        Range("M2:T" & hitdata + 1) = pickedDataArr
    End If

     ' --- ⑥ 測定ストップ&結果発表 ---
    stoptime = Timer
    MsgBox hitdata & "件該当ありました。" & vbCrLf & _
           "処理時間:" & Round(stoptime - starttime, 2) & "秒", vbInformation

End Sub

8. 抽出に要した時間は0.21秒でした。


namae8

【補足:おもてなしVBA版】絶対に壊れない防弾仕様コード

以下は、万が一予想外の使い方をされた場合を考えています。
データの入っていないシートでボタンを押したり、該当データが0件だったりしても、黄色いエラー画面を出さずに優しくウインクしてそっと終了する「完全防弾コード」です!

 Sub タイム測定Arrayでフィルタ_おもてなし防弾版()
    Dim starttime As Single, stoptime As Single
    starttime = Timer
    
    
    ' --- 【超スマート判定】1セルも、見出し1行も「行数 <= 1」で一網打尽にガード! ---
    If Range("A1").CurrentRegion.Rows.Count <= 1 Then
        MsgBox "データが入力されていないようです。" & vbCrLf & _
               "見出しの下(2行目以降)にデータを用意してから実行してくださいね!", _
               vbInformation, "おもてなし受付"
        Exit Sub
    End If
    
    ' ➔ ここを通過した時点で、必ず「2行以上の安全な2次元配列」になることが100%確定!
    Dim allmydataArr As Variant
    allmydataArr = Range("A1").CurrentRegion.Value 
    ' --- 抽出用の受け皿と検索条件のセット ---
    ' 元データの最大行数を上限にして配列を動的に確保(固定長20000行からの卒業)
    Dim maxRows As Long
    maxRows = UBound(allmydataArr, 1)
    
    Dim pickedDataArr() As Variant
    ReDim pickedDataArr(1 To maxRows, 1 To 8)
    
    Dim Specifieddate As Date: Specifieddate = #1/1/1990#
    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 i
    
    ' --- 防弾対策2:該当件数チェック&Resize一括書き戻し ---
    If hitdata = 0 Then
        MsgBox "条件に該当するデータは0件でした。", vbInformation, "おもてなし受付"
        Exit Sub
    End If
    
    ' M2セルを起点に、件数分(hitdata行×8列)のピッタリサイズで一括書き込み(おもてなし③)
    Range("M2").Resize(hitdata, 8).Value = pickedDataArr
    
    ' 仕上げ:書き出した列幅をキレイに揃える(おもてなし④)
    Range("M2").Resize(hitdata, 8).Columns.AutoFit
    
    stoptime = Timer
    
    MsgBox hitdata & "件の該当データを出力しました!" & vbCrLf & _
           "処理時間:" & Round(stoptime - starttime, 2) & "秒", vbInformation, "処理完了"
End Sub

9. では、配列を使わない場合には、処理時間はどのくらいかかるのでしょうか?
次回の106回は配列を使わない場合のコードと、今回のコードの解説をします。