VBA集計業務で二次元配列を使い処理を高速化させるサンプル

VBA集計業務に配列を積極的に取り入れることで、とくに膨大なデータ処理に掛かる時間を大幅に短縮できます。

本記事では、ベースとなる基本的な集計用VBAサンプルコードに対し、配列を段階的に取り入れ、処理が高速化されていく様子をご紹介します。また、配列を段階的に取り入れる様子を4つのサンプルコードで確認できますので、VBA集計業務で配列を使う具体的な方法が理解できます。

下の1分動画では、本記事で実際にご紹介する4つのサンプルコードを、配列を徐々に取り入れる順番で実行しています。わずか1000レコードだけですが、段々と実行速度が短くなる様子が見て取れますよ。

VBAでクラス別5教科の平均点を求める

生徒数1000人、8クラス、5教科の平均点を求めるサンプルファイル

本記事では、配列導入によりVBAの高速化を実感してもらうためのサンプルファイルを用意しました。

サンプルファイルの内容は、ある学年（生徒数1000人、8クラス）のある時期に実施した試験の生徒別各教科(国語、算数、理科、社会、英語）の得点データです。

さらに、同一シート上にクラス別各科目ごとの平均点を表示する集計表があります。（下図）

今回は集計結果についても先にご紹介します。今回は4つのVBAサンプルコードをご紹介しますが、いづれの集計結果も下図になります。

配列を使わずに集計させるVBAサンプルコード

まずは、配列を全く使わずにクラス別平均点を集計させるVBAコードをご紹介します。

配列を使わずに集計させるVBAサンプルコードのアルゴリズム

20～52行目： For r = 2 To lngERow ～ Next
集計対象レコードを繰り返し処理するVBAコードになります。lngERowは、VBAコード17行目で取得する集計対象レコードの最終行番号です。

次からは繰り返し集計処理の中身になります。

VBAコードで書込み行を決めるアルゴリズム

22～31行目： Select Case Cells(r, 2) ～ End Select
平均点を集計する表への書込み行番号を決めるVBAコードです。

VBAコード22行目のSelect文の条件に各レコードのクラス情報（Cells(r, 2))を指定します（赤文字）。集計対象レコード上では、赤枠のデータです。

そしてVBAコード23～30行目で、平均点表示する集計表のExcelシート行番号を指定します（青枠）。

なお Select文 により書込み行を決めるVBAコードについては、下記記事でもご紹介しております。

Tips： Select文 により表への書込み行を決めるVBAコード
VBAコードで部門別費目別予実別に月ごとの経費を集計する
VBAコードで部門別に月ごとの経費を集計するサンプル
VBAコードで部門別に各経費費目に分けて集計する

VBAコードで各教科点数とデータカウント数を転記させるアルゴリズム

VBAコード34～51行目では、2つのことを実行します。1つは、各レコードの各教科別点数を読み取り、平均点を表示する表（下図青枠）に加算集計します。

2つめは、平均点を表示する表の下にデータ数をカウントさせています (下図赤枠） 。データ数を同時にカウントする理由は、後で平均点を求めるために必要だからです。図を使って説明しますね。

まず1つ目の目的、平均点を表示させる範囲（青枠）に、クラス別教科別の点数を合算集計させます。VBAコードでは、34, 38, 42, 46, 50行目（青字）です。この段階では、「点数の合算集計」=合計点ですので平均点ではありません。

また表への書込み列については各教科によって決まります。緑字と緑枠の関係をご覧ください。

2つ目の目的、点数を集計したデータ数を同時にカウントするVBAコードは、35, 39, 43, 47, 51行目（赤字）です。カウント数の集計先書込み行は、lngWRowに+10することで、平均点を表示させる表の下行方向へ+10ずらして集計させるようにしています（赤枠）。

図の矢印は、例としてAクラスの集計先書込み行番号を示したものです。まずAクラスですから、VBAコード23行目によりlngWRow = 4になります。よって点数を合算する行は4行目。同時にデータ数をカウントする行は、lngWRowに+10しますので、14行目に+1ずつカウントアップされます。

VBAで平均点を計算・表示させるアルゴリズム

VBAコード55～59行目は、前項までに集計された点数とデータカウント数の合計から平均点を計算・表示させます。

For ～ Next文をネスティング（入れ子）させることにより、Aから1クラスずつ（青矢印方向）国語、数学、理科、社会、英語の順番（緑矢印方向）に、平均点を計算し表示させています。

具体的には、Aクラスの国語、数学、理科、社会、英語、Bクラスの国語、数学・・・、Hクラスの国語、数学、理科、社会、英語の順番で集計処理されます。

57行目： Cells(r, c) = Cells(r, c) / Cells(r + 10, c)
平均点を計算し、表示させるVBAコードです。右辺で点数の合計をデータカウント数で除して平均点を算出します。その計算結果を、Cells(r, c)へ再代入することで平均点が表示されます。

図では、Aクラスの国語の例を赤枠で示しています。このようにあるクラスの教科の平均点は、上の合計点の表と下のデータカウント数の表の相対位置が同じ2つのセルから計算されることが分かります。

62行目： Range(Cells(14, 10), Cells(21, 14)).ClearContents
平均点を計算・表示させたあとは、データカウントを集計した表は不要になりますので、このVBAコードでデータカウントを集計したセル範囲をクリアします。

集計データ数カウントに配列を使うVBAサンプルコード

前項のサンプルコードでは、配列を使わないで集計を行いました。ここでは、データカウント数の集計に配列を取り入れたサンプルコードをご紹介します。

データ数をカウントする箇所に配列を使うVBAサンプルコード

データ数をカウントする箇所に配列を取り入れたVBAコードです。ぜひ真似して書いてみてください。また実行結果は、全く同じになることも確認しておいてください。

データ数カウントを配列に格納させるVBAコードのアルゴリズム

では、VBAコードについてデータカウント数を配列化した箇所を中心に解説します。

集計先Excelシートの行列番号に合わせて配列要素を宣言する

7行目： Dim varCunt(4 To 11, 10 To 14) As Variant
配列を宣言するVBAコードです（下図）。

まずVBAで配列を使うために、他の変数と同様、変数の宣言をします。今回は配列用変数として変数名「varCunt」使います。

配列用変数の宣言方法ですが、ここでは配列変数の後にカッコ「()」をつけて要素を指定する方法を使います。VBAコードを確認しますと、要素の部分は「4 to 10」（赤文字）と「10 to 14」（青文字）を2つをカンマ「,」で分けて記述しています。

ところで通常の二次元配列であれば、varCunt(1 to 7, 1 to 5)あるいはvarCunt(6, 4)など、1次と2次の要素番号の開始を1もしくは0のように揃えて使いますが、ここでは集計先のExcelシート（緑色の範囲）の行範囲（赤枠）と列範囲（青枠）に合わせて宣言する方法を使います。

データカウント数を配列に代入するアルゴリズム

VBAコード36, 40, 44, 48, 52行目は、データカウント数を配列変数varCuntに代入しています。例えば、Aクラス（lngWRow=4)の国語のカウント数は、VBAコード36行目により変数varCunt(4, 10)に集計されます。

データカウント数用に配列変数を使う事で、前項のようにデータカウント集計用にExcelシートを使う必要がなくなります。また、Excelシートの各セルへのアクセスが減る分、VBAの実行時間も短くなります。

平均点を計算・表示させるアルゴリズム

最後はExcelシート上に計算された点数の合計点を、二次元配列変数varCuntに集計されたデータカウント数で除して平均点を算出し、Excelシートに表示するVBAコードの解説になります。

配列の宣言時に、集計先のExcelシートの範囲に合わせて配列要素を設定したため、合計点が格納されたセルと同じ要素番号の配列「varCunt」で除すことで簡単に各クラス各教科の平均値が求まります。

さらに配列要素の番号が一致しているため可読性が良くなりコーディングのミスが少なくなります。

集計結果表示範囲にも配列を使うVBAサンプルコード

前項では、データカウント数の集計に配列を取り入れました。本項では、平均値や点数の合計点の集計結果を表示するセル範囲にも同様のやり方で配列処理を追加します。

集計結果表示範囲にも配列を使うVBAサンプルコード

では早速ですが、集計結果表示範囲にも配列を取り入れたVBAサンプルコードをご紹介します。ぜひ真似て書いてみてくださいね。ここでも実行結果は同じになりますよ。

集計結果表示範囲に配列を使うVBAコードのアルゴリズム

8行目： Dim varData(4 To 11, 10 To 14) As Variant
前項でデータ数カウント集計用の二次元配列変数「varCunt」を宣言した時と同じく、今回は点数の合計値や平均値を格納するための二次元配列変数としてvarDataを追加で宣言します。

10行目： Dim rngData As Range
集計結果を表示する範囲をRangeオブジェクトで定義します。

20行目： Set rngData = Range(Cells(4, 10), Cells(11, 14))
21行目： rngData.ClearContents
前項までの2つのサンプルコードでは、集計結果を表示する範囲を「Range(Cells(4,10), Cells(11,14))」で記述していました。

今回は、集計結果範囲にも配列を取り入れる都合上、Rangeオブジェクトとして変数「rngData」を設定した上で、集計結果範囲を初期化しています。

点数の合計値を合算集計して二次元配列に格納するアルゴリズム

VBAコード40, 44, 48, 52, 56行目は、各レコード各教科の点数をセルではなく、VBAコード8行目で定義した二次元配列変数「varData」に合算集計します。前項のVBAコードと比較した図を用意しましたのでご確認ください。

点数の合算結果とデータカウント数から平均点を計算させるアルゴリズム

VBAコード61～65行目は、直前までに集計し終わった点数の合算値とデータカウント数から各クラス各教科別の平均点を算出しています。そして、求めた平均点は再び二次元配列変数「varData」に再代入されます。

ではこれらのVBAコードも前項の同じ個所のVBAコードと比較してみると、CellsとvarDateが違うだけになります。

二次元配列に格納された平均値を最後にセルへ表示させるアルゴリズム

ここまでで、クラス別教科別の平均値が二次元配列varDateに格納されました。ただこのままでは、配列に格納されたままですので、結果が目に見えるようにExcelシートに集計結果を表示させる必要があります。

それを実現するのがVBAコード68行目「 rngData = varData 」です。rngDataは、Excelシートのセル範囲です。varDataは、そのセル範囲を同じ要素の構成になるように宣言した配列であり、この時すでに平均値が入っています。

この右辺の配列データを左辺のセル範囲に代入することで、集計結果を表示するセル範囲に、平均値を表示させることができます。

このように、セル範囲の構成と対応する二次元配列の構成を同じくしておけば、コーディングをする上でも管理し易いため、あとで要素構成を変更する必要が生じた際にも効率よくコードの改修ができます。

集計対象レコードにも配列を使うVBAサンプルコード

本記事最後のVBAサンプルコードは、集計対象レコードにも配列を扱う場合についてです。

これまで、データカウント数と集計結果表示範囲の2か所に配列を取り入れてきました。本項ではさらに生徒別各科目得点が入力されている1000人分のレコードに対しても配列を導入していきます。

集計対象レコードにも配列を使うVBAサンプルコード

では早速ですが、レコード全体にも配列を導入したVBAサンプルコードをご紹介します。ぜひ真似して書いてみてくださいね。なお、ここでも実行結果はこれまでと同じになりますよ。

集計対象レコードに配列を使うVBAコードのアルゴリズム

ではVBAコードの解説になります。これまでと同様、新しい項目についての解説が中心になります。

集計対象レコードを配列で扱うために必要な配列とRangeオブジェクト変数の宣言

10行目： Dim varRcrd As Variant
集計対象レコードのデータを格納するための配列を宣言しています。これまでの配列の宣言方法と違い、配列の要素数については指定しません。

13行目： Dim rngRcrd As Range
Excelシート上における集計対象レコードのデータ範囲を設定します。VBAコード10行目で宣言した二次元配列へ格納する前に、ここで宣言したRangeオブジェクト「rngData」に集計対象レコードのデータを取得します。

ここで注意したいのが、rngRcrdに設定する範囲は、集計結果に関係のない項目名が記述された1行目を除いた範囲になります。

集計対象レコードのデータ範囲をRangeオブジェクトで取得するアルゴリズム

VBAコード25～27行目で、このRangeオブジェクト変数「rngRcrd」に、集計対象レコードのデータ範囲を設定しています。では、この3行について、順を追って詳しくお話ししますね。

まずVBAコード25行目で、Range("A1").CurrentRegionを指定しています。下図のように、1行目の項目を含めたレコード全体を「.CurrentRegion」プロパティで指定しています。

この段階ではまだ項目名が書かれた1行目が含まれていますので、さらに処理が必要になります。

そこでVBAコード26行目では、その項目名が書かれた1行目を取り除きながらrngRcrdを設定しているので、少々コードが複雑になっています。具体的には下記2点を行いながらrngRcrdを設定しています。

• Range("A1").CurrentRegionで取得した行数を1行減らしている
• さらに、その範囲を行方向へ1行移動させている

言葉ではわかりずらいので、下図を使って説明しますね。まず、これまで取得したRange("A1).CurrentRegionの「行数を1行減らす」目的ですが、集計結果に関係のない項目名の書かれた1行目を除くためです。

前述で指定したRange("A1).CurrentRegionの行数は、1001行（生徒1000人と項目行）ですので、これをまず生徒数分と同じ行数の1000行にすれば良さそうですよね。そのために、図の赤字で書かれている「Resize」プロパティを使います。

Resizeプロパティは、Rangeオブジェクトに対し範囲の行列サイズを変更する際に使います。一般的には、Resize(行数, 列数)のように、行と列を指定して使いますが、Resize(行数), Resize(,列数)のように行数だけとか列数だけを指定して使う方法もあります。

今回は、「.Resize(行数)」の使い方ですので行方向のみサイズ変更をすることになります。ではその行数をどのように指定しているかですが、Resizeの()内に相当するのは「.Rows.Count-1」です。

ここで「.Rows.Count」は、Withステートメントで指定された「Range("A1").CurrentRegion」に対する「Rows.Count」ですので、項目名が書かれた1行目を含んだ行数、すなわち1001行になります。

そこから1を引いてますので、「.Resize(.Rows.Count-1)」により「.Range("A1").CurrentRegion」の行サイズを1000行(1001-1)に変えることになります。

そして後半部分で1000行にResizeした範囲全体を、「Offset」プロパティで行方向へ+1ずらすことで目的のデータ範囲だけを指定できます。（下図）

ここで使用したRangeオブジェクトに対するプロパティ「Offset」ですが、使い方はResizeプロパティと同じで、Offset（行, 列)で指定したり、Offset（行), Offset(,列)のように行列を省略して使用したりします。

Excelデータ範囲を配列に格納するVBAコード

30行目： varRcrd = rngRcrd.Value
ここまででrngRcrdには、すべてのレコードのデータ範囲が格納されましたので、このVBAコードでデータを二次元配列に格納します。ちょうど下図のようなイメージになります。

ここで注意したいのが、配列の要素番号が1から始まることです。よって、配列のレコード数に対応する1次の要素番号は1から1000になります。また、2次の要素番号は、1から7になります。

配列内に格納した集計対象レコードを繰り返し処理するアルゴリズム

33～65行目： For r = 1 To UBound(varRcrd) ～ Next
配列に格納した集計対象レコードを、このFor ～ Next文で繰り返し処理します。33行目の「UBound」は、配列を引数にとり配列の最大要素番号を返す関数です。ここでは、UBound(varRcrd)により1次の最大要素番号1000を取得しています。

また引数の指定方法をUBound(varRcrd, 2)のように指定することで、配列の2次要素の最大要素番号を取得できます。例えば、UBound(varRcrd, 2)は7を返します。

逆に配列の最小要素番号を取得する関数は「LBound」です。使い方はUBound関数と同様ですので、例えば1次の最小要素番号であればLBound(varRcrd, 1)で1になります。LBound(varRcrd, 2)も1ですね。

配列の値を読み取り書込み行を決定するアルゴリズム

VBAコード35～44行目で、配列varRcrdに格納されたクラスデータを判定し、書込み行番号を決めています。前項までのサンプルコードに対し、図の赤文字の部分がセル上のデータ「Cells(r, 2)」から、配列データ「varRcrd(r, 2)」に変わっただけです。

配列データを使い合計点を合算するアルゴリズム

VBAコード47～64行目では、これまでと同様に点数の合算集計と、データ数をカウントしています。今回は、集計対象レコードのデータを配列化していますので、下図のように赤字部分だけが変わったことになります。

アルゴリズムについては、これまでと同様ですが、レコードの取り扱いが配列化されましたので前項のVBAコードに対し、赤文字部分が変更になります。

以上、レコードを配列化したことによる変更箇所を中心に解説してきました。67行目以降の平均点算出、Excelシートへ表示の部分は、前項と同じになりますのでここでは割愛させて頂きますね。

段階的に配列を取り入れた4つのVBAコードの実行速度比較

本記事では、4つのサンプルコードを使いながら、VBA集計業務への配列の取り入れ方をお話ししてきました。これら4つのサンプルコードですが、実行結果はすべて同じで、VBAコードの中に配列をどの程度取り入れるかだけの違いです。

ここで4つのサンプルコード別に、配列の取り入れる程度を整理しますと下の図表のようになります。

このようにVBA集計業務では、集計に使われているExcelシートの範囲を配列化して集計できることになります。でもなぜ配列の並びに等しいセル範囲を持つExcelシートを使わずに、わざわざ配列化して集計するのかが疑問になりますよね。

そこで、もう１つ表を用意しました。今回の4つのサンプルコードの実行速度を整理した表です（下表）。前図の〇番号と合わせてみてくださいね。

既に実行されてきた中でお気づきかと思いますが、配列を取り入れる割合を増やすことで実行速度が速くなっていますよね。同じことを実行させるのに、配列を使わない1番目のサンプルコードに対して、4番目のサンプルコードは約30倍速くなるということになります。（あくまで筆者の実行環境によるものですが、下の動画で4つのサンプルコードの実行速度の違いをイメージ頂けると思います。）

概算ですが、仮に今回のレコード数が100,000になった場合、配列を全く使わない1番目のサンプルコードでは約1分(0.60×100000/1000)かかりますが、4番目のサンプルコードでは、たったの2秒で済むことになります。

以上より、VBAコード内で配列による集計を行わない場合、必然的にExcelシートへのアクセスする回数が増えるのですが、このことがVBAの実行速度を遅くする原因になります。逆に、VBAコード内で配列処理を多く取り入れることにより、処理速度を改善することができます。

このように、VBA集計業務では集計対象のデータ数やExcelシート範囲の規模が大きくなる場合、配列を使う・使わないによって、処理速度の差が大きくなります。とくに毎日大量のデータに対し、同様な処理をするような場合には、ぜひ配列の導入をご検討されてもよいかもしれません。

まとめ

VBA集計業務に配列を取り入れる具体的な手法と、配列を取り入れるメリットを4つのサンプルコードを使いながらお話ししてきました。

VBAコードで配列を使う手法については、4つのサンプルコードに段階的に配列を取り入れる様子をご紹介することで、実務で高速化が必要な個所に絞って配列化できるようになると考えています。

また、実際に4つのサンプルコードを実行して頂くことにより、配列化の割合を増やせば処理速度がより高速になることを実感できたのではと思います。