配列を使うと作業の高速化を実現できる
経験者の皆様よりエクセルマクロの2次元配列について問い合わせをいただく機会が多くなりました。
皆様興味はあるのですが「難しいので分からない」と言われる方が多いです。
2次元配列はうまく使えると作業の高速化が実現出来ます。
具体的には「10万行×10列 = 100万セル程度の比較的大きいデータ」でも軽々扱う事が出来る様になります。
配列自体が今までの作業を高速化するという事ではないです。
今までとは異なるデータの持ち方をする事で作業効率を上げた結果高速化が実現されます。
配列は他の言語でも出てくる便利な機能です。考え方を習得しておくと他の言語を勉強する際にご自身の助けになります。
出来る事が格段に増えて世界が広がります。おすすめ機能ですので是非解説を読んでください。
特典
後半にはVBEに貼り付けるだけで使えるコードを用意しました。
配列を使わないコードと配列を使ったコードの比較が出来る様にしてあります。速度差を体感、体験出来ます。
関連記事
1次元配列の記事へのリンクを用意しておきます。以下記事をご覧ください。
上の記事で考え方を解説し下の記事でコードを掲載しています。
2つの記事には関連があります。考え方を勉強してからコード＆解説記事を見ていただくのがおすすめです。
記事の構成
まずは配列について簡単に解説します。続いて2次元配列について説明していきます。
2次元配列は1次元配列と比べてたくさん情報を持つことが出来ます。
しかしその分取り扱いが少し難しいです。分かりやすい様に出来るだけ小さいモデルを用意して解説します。
配列とは
簡単に言うと値の集合を取り扱う事が出来る機能です。詳細が気になる方はWikipediaをご覧ください。
（引用＋リンクを用意しておきました）
複数の要素（値）の集合を格納・管理するのに用いられるデータ構造
Wikipedia:配列より抜粋
格納された情報を任意のタイミングで出し入れすることで効率良くコードを書く事ができるようになります。
効果
分かりやすいものを２つ挙げます。
作業スピードが上がる
配列を使う理由はほとんどこれです。
スピードが上がる理由はセル（オブジェクト）へのアクセスを減らすことが出来るからです。
Rangeオブジェクトを例に具体的に考えてみます。
基本的に「Rangeオブジェクトへのアプローチ回数が多いコード」は作業スピードが落ちます。
一般的には「マクロが遅い」という使われ方をします。
普通に考えると「データ量＝Rangeオブジェクトへのアプローチ回数」になります。
よって扱うデータ量が多いとマクロが遅くなります。
この状態を解決させる為に配列を使います。具体的に書くと「データ量に関係なく作業スピードを上げる」となります。
配列を使ってRangeオブジェクトへのアプローチ回数を減らす事で作業は格段に速くなります。
データをストックできる
エクセルで作業する際にシート間を繋ぐために情報の「一次置きシート」を作る事があります。
例えばシートAの情報をシートCに展開する際に計算や文字列を整える為に用意するシートBの様な存在です。
配列はメモリ上にワークシートのような規則的な空間を用意できます。そこに情報をストックします。
ストックされた情報は加工も可能です。よってシートBの様な一次置きシートをつくる必要も無くなります。
効果はあるがデメリットもある
単純に「難しい」です。
ここは慣れしかないのですが慣れるにも考え方自体が難しいので挫折する人も多い様です。
今回は本当に簡単な事例を使って解説をするのでここでしっかり配列をマスターしましょう。
分からない時はこちらから問い合わせください。
「作業スピードが上がる」を深堀りする
先程「マクロが遅い」というキーワードが出てきました。
ではこの「マクロが遅い」という状況から配列を用いる事で作業スピードが上がるのか検証しましょう。
「マクロが遅い」というのは一般的には「実行時間が長い」という事です。
つまり処理に手間取っているという事です。
傾向としてはRangeオブジェクトなどのオブジェクトを掴む回数が多ければ多いほど処理速度が落ちます。
- 「扱うデータの対象となるオブジェクトの量が多い時」
- 「オブジェクトへのアプローチ回数が多くなるコード」
2つの要素が重なるコードを書くとマクロが遅くなる傾向があります。
実行時間を減らすにはオブジェクトへのアプローチを減らす必要があります。
具体的な事例をもとに作業速度を体感する
2つのコードを用意しました。両方とも約100万個のセルに「おはようございます」を記入するコードです。
同じ作業をしていますがコードの書き方が違います。
最初のコードはB、C列に情報が入ります。配列未使用です。
2番目のコードはE、F列に情報が入ります。配列を使用しています。
この段階でコードは分からなくても良いのでこのままVBEに貼って実行してください。
配列を使用したコードの方が実行速度がはるかに速い事が分かります。
Sub 配列未使用()
Dim r As Long
Application.ScreenUpdating = False
For r = 2 To 500001
If Not IsEmpty(Range(Cells(r, 2), Cells(r, 3)).Value) Then
Cells(r, 2) = "おはようございます"
Cells(r, 3) = "おはようございます"
End If
Next
Application.ScreenUpdating = True
End Sub実行後はすぐに結果が出ません。少し気長に待ってください。
Sub 配列使用()
Dim ary(1 To 500000, 1 To 2) As Variant
Dim r As Long
For r = LBound(ary) To UBound(ary)
ary(r, 1) = "おはようございます"
ary(r, 2) = "おはようございます"
Next
If Not IsEmpty(Range(Cells(2, 5), Cells(r, 6)).Value) Then
Range(Cells(2, 5), Cells(UBound(ary) + 1, 6)) = ary
End If
End Sub数秒で結果が表示されます。
2つ目の配列を使用したコードはRangeオブジェクトへのアプローチは２回です。
Ifステートメントで1回、IfステートメントのTrueの処理で1回。これで終わりです。
1つ目の配列を使用しないコードのRangeオブジェクトへのアプローチは・・・沢山です。
「配列を使うと作業が速い」を体感出来たでしょうか。
参考：コピペはさらに遅い
今回の処理はセルにイコールを使って値を代入するコードと配列を使ったコードを紹介しています。
この2つの作業の他にもコピーメソッドを使ってセルに値を用意する方法があります。
PasteSpecialメソッドを使った「値貼り付け」は配列やイコールを使うコードに比べダントツに遅い作業になります。
少ないセルへの作業であればどの作業でも良いのですが沢山のセルへの仕事はコピペはやめて配列でコードを組みましょう。
PasteSpecialメソッドの使い方VBA×コピペ｜値のみ貼り付け＆簡単高速にコピーできる方法を公開
配列をイメージでとらえる
配列を使うとコードの書き方も変わります。
すごく難しくなるのですが難しい配列をより簡単に考える為にキーワードを用意しました。
配列のポイントはこれだけです。
キーワード
これだけです。これで配列はマスター出来ます。
- 取って入れて出す
- 作って入れて出す
キーワード自体はどこかで聞いたことありますよね。
使われている方は配列を意識して使ってないのですがこのキーワードは本当に配列にピッタリです。
- 取って：配列に格納するためセルの情報を取り出す
- 入れて：取り出した情報を配列に格納する
- 出す ：配列に格納された情報を効率良く出力する
- 作って：配列に格納するためのデータを用意する
- 入れて：用意した情報を配列に格納する
- 出す ：配列に格納された情報を効率良く出力する
※冒頭のリンク記事と先程紹介したコードは「作って入れて出す」の様式でデータを用意しています。
「作って入れて出す」とこの後紹介する「取って入れて出す」が出来れば2次元配列は覚えたも同然です。
あとは取り込んだ情報を好きなように取り回すことが出来ればセルの値は全て握ったようなものです。
2次元配列を使ってみる
早速具体的にコードを用意してみましょう。
作って入れて出す
以下コードは「作って入れて出す」を実行しています。変数はあえて日本語を使っています。
Sub 作って入れて出す()
'*****************************************************
Dim 配列(1 To 2, 1 To 3) As String '配列の大きさと型を宣言する 今回は文字を入れるのでString
Dim あ As String '1つ目のキーワード あ に意味は無い
Dim い As String '2つ目のキーワード い に意味は無い
Dim う As String '3つ目のキーワード う に意味は無い
'*****************************************************
'作って入れて出すの「作って」に当たるコード
あ = "作って"
い = "入れて"
う = "出す"
'作って入れて出すの「入れて」に当たるコード
配列(1, 1) = あ
配列(1, 2) = い
配列(1, 3) = う
配列(2, 1) = あ
配列(2, 2) = い
配列(2, 3) = う
'作って入れて出すの「出す」に当たるコード
Cells(3, 1) = 配列(1, 1)
Cells(3, 2) = 配列(1, 2)
Cells(3, 3) = 配列(1, 3)
Cells(4, 1) = 配列(2, 1)
Cells(4, 2) = 配列(2, 2)
Cells(4, 3) = 配列(2, 3)
End Subコードの説明をします。まずは配列で情報を格納するスペースと型を宣言します。
例えば以下のような感じです。
Dim 配列(1 To 2, 1 To 3) As String
2次元配列ですので字の通り2軸分の要素を定義する必要があります。
ここでは1次元目は1～2、2次元目は1～3という事で計6つの情報を入れる場所を定義しました。
合わせて格納するデータの型を宣言します。
この後文字を格納する予定ですので今回はStringを使用しています。
次は配列に格納する為にコード内で作った情報を準備します。
（変数 あ～う）これがキーワードの「作って」に該当します。
続いて作った情報を用意した配列の箱に番地を決めて格納します。
これがキーワードの「入れて」に該当します。
最後にセルに出力するコードを用意します。
これがキーワードの「出す」に該当します。
分かりやすい様にあえてループは使わず1つ1つの作業を書いてみました。
取って入れて出す
続いて「取って入れて出す」の説明です。
Sub 取って入れて出す()
'*****************************************************
Dim 配列(1 To 2, 1 To 3) As String '配列の大きさと型を宣言する 今回は文字を入れるのでString
Dim あ As String '1つ目のキーワード あ に意味は無い
Dim い As String '2つ目のキーワード い に意味は無い
Dim う As String '3つ目のキーワード う に意味は無い
'*****************************************************
'取って入れて出すの「取って」に当たるコード
あ = Range("A3").Value
い = Range("B3").Value
う = Range("C3").Value
'最初のキーワードだけ「取って」に書き換えます
あ = "取って"
'取って入れて出すの「入れて」に当たるコード
配列(1, 1) = あ
配列(1, 2) = い
配列(1, 3) = う
配列(2, 1) = あ
配列(2, 2) = い
配列(2, 3) = う
'取って入れて出すの「出す」に当たるコード
Cells(5, 1) = 配列(1, 1)
Cells(5, 2) = 配列(1, 2)
Cells(5, 3) = 配列(1, 3)
Cells(6, 1) = 配列(2, 1)
Cells(6, 2) = 配列(2, 2)
Cells(6, 3) = 配列(2, 3)
End Subちょっと見た事ないコードあるけど・・・と考えるかもしれませんが一旦流して先に進みます。
やってることは「作って入れて出す」とほぼ同じです。
「作って入れて出す」と「取って入れて出す」を紹介しました。
これらの処理が出来る様になれば配列はもう覚えたも同然です。
ただし先程紹介したコードだけだと転記と一部情報の書替えは出来ますけど演算が出来ません。
取得した情報を使って情報を加工したいですよね。
次は配列と相性の良い繰り返し処理を紹介します。
繰り返し処理
繰り返し処理についてはLBoundと UBoundを使います。本記事でも何回か使っていましたね。
直近では「取って入れて出す」のコードの中で使っています。
LBound と UBound は非常に使いやすいです。
配列の要素数（添字）の最小値から最大値までをループしてくれます。
配列内を周回する事が出来ます。覚えておいてください。
ループの中でも御馴染みの機能がつかえる
データの出力
「作って入れて出す」のコードでは1セル毎に配列の値を出力しました。
「取って入れて出す」ではセル範囲に対して配列全てを出力しています。
範囲を決めて出力する事で一括でセルに配列の値を展開する事ができます。
ここでも左辺に右辺を代入というセルに値を入力する際の基本的な考え方は踏襲されています。
「配列の大きさと同様の範囲を用意して配列を置く」というイメージで作業をしましょう。
関連記事：配列内の検索やデータの貼り付けについて
関連記事を2つ用意しました。
Like演算子を使って配列内を検索
配列内も検索が可能です。検索はFindメソッドを使わずLike演算子を使うことをおすすめします。
出力時のデータの貼り付けは注意が必要です
出力時のデータの貼付けについてはデータの持ち方によっては転置が必要になります。
1次元配列の記事で転置（Transpose）についての解説をあげています。
「配列の値をセルに出力する際行列の方向が合わずうまくデータを出力出来ない」という方は必見です。
転置を視覚で確認できる様な動画（1分程度）も用意しています。
まとめ
これで配列も扱う事が出来そうです。・・・？ 本当に出来るのでしょうか？
確かに「取って入れて出す」は出来るし情報量が少なければ問題なく作業をすることが出来そうです。
不安なのは情報量が多いデータに対して検索をかけた時など「より複雑な作業」の時です。
「作業スピードが落ちないか？」など分からない事がまだ沢山あります。
例えば検索値が50万行でDBは10万×10列程の量のデータを扱うとします。
DBを配列にしても50万行の検索値はセルに残ったままです。
という事は出力値も最低で50万件前後になります。セルへのアプローチ回数は100万回・・・。
「やはり配列を使ってもデータが大きいと限界があるのか」と考えてしまいます。
このような仕様であっても解決策はあります。以下記事で説明します。
