VBA講座:配列の活用による処理のスピードアップ策




たった1秒で仕事が片づく Excel自動化の教科書

『たった1秒で仕事が片づくExcel自動化の教科書』(技術評論社)にて、
紙面の都合で書けなかった詳細をこちらで解説します。




■配列の活用による処理のスピードアップ策

数万行を超えるデータを処理していく場合は、そのデータを一旦配列に入れて、配列の中で処理してからシート上に戻す…というテクニックが極めて有効です。

 

■データを一旦配列に入れるとは

例えば、シートのA列とB列に数字が2行目から50万行目まで入っているとします。リスティング広告のログデータなどではこのような大きなデータはざらにあります。2行目から順に、A列とB列の数字を掛け算した値をC列に入れる場合、通常であれば次のようなプロシージャになりますね。

 Sub sample()

   Application.ScreenUpdating = False

   Dim i As Long

   For i = 2 To 50000

     Cells(i, 3) = Cells(i, 1) * Cells(i, 2)

   Next

 End Sub


この処理の所要時間は、状況によって変わりますが8秒ほどかかります。

 

これを次のように、シート上のデータを一旦配列に入れて、その中で処理してシートに戻すことでその所要時間は1秒足らずに短縮されます。

 Sub sample()

   Application.ScreenUpdating = False

   Dim i As Long

   Dim Table As Variant

   Table = Range("A1:C50000")

   For i = 2 To 50000

     Table(i, 3) = Table(i, 1) * Table(i, 2)

   Next

   Range("A1:C50000") = Table

 End Sub


このプロシージャを理解する最大のポイントは5行目、


 Table = Range("A1:C50000")      

 この処理です。

一つ前の行でTableという変数をでVariant型、つまり何でも入る型で宣言しています。

この変数にRange("A1:C50000")というセル範囲を入れると、変数Tableはタテ5万行、ヨコ3列という配列になるのです。

目には見えませんからイメージしずらいですが、シート上で見えてるセル、データの状態そのままをイメージします。

 そして、配列の中の各部屋(シート上で言うセルに該当)はそれぞれ、Table(1,2)などのように指定することができます。つまり[Table = Range("A1:C50000")]という処理をした場合、A2セルに該当する配列内の部屋はTable(2,1)となります。

 配列の中でForNext構文を使って配列一列目の値と配列2列目の値を掛け算した値を配列3列目に入れているのが次の部分です。

 For i = 2 To 50000

   Table(i, 3) = Table(i, 1) * Table(i, 2)

 Next


 そして配列の中で2行目から50000行目まで処理が終わったら、この配列の値をまるごとシートに戻します。それが


 Range("A1:C50000") = Table      

 という処理です。

 

 これが、大量のデータ処理で重くて時間がかかる時は、一旦配列に入れてその中で処理して、またシートに戻すと早くなるという方法の基本です。



『たった1秒で仕事が片づく Excel自動化の教科書』執筆陣が自ら直接指導。
実務直結・一日集中・受講後無期限サポート付きのマクロVBAセミナー



記事一覧に戻る



  • Excel学習法診断
  • 100の極意マスター講座
  • Excelショートカットマスター
  • おすすめショートカット一覧・壁紙ダウンロード
  • オリジナルExcelグッズ
  • Twitter:吉田 拳@Excelすごい改善社長
  • Excelで業務効率化を実現!
  • Excelで業務効率化を実現!
  • 約1万人の業務効率を劇的に改善!Excel速技BEST100
  • 資金繰り予報士 こがねむしクラブ