配列のセルへの一括転記(3.転記の方法と大量データの並び替え)

前々回で配列の並び方、前回で行列の並び替えについて調べてみました。

www.excellovers.com

www.excellovers.com

今回は配列のセルへの一括転記とTranspose関数の限界を超える大量データの並び替えについて調べてみました。

Transpose関数の限界と対応

配列の並び替えにこれまでTransposeを使用していましたが、前回記事の最後で記載した通り、要素数が65,536までしか正しく扱えないという限界があります。

Sub TransposeBigdataError()
     Dim Var(65536) As Variant
     Dim myRng As Range
     Dim i As Long
     Dim myArray(1) As Variant
     
     For Each myRng In Range("A1:A65537")
          myArray(0) = myRng.Value
          myArray(1) = myRng.Offset(, 1).Value
          Var(i) = myArray
          i = i + 1
     Next
     Dim VarTranspose As Variant
     VarTranspose = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Var))
End Sub

上記コードを実行すると下記のようになります。

並び替え後は、なんか１行目しか取得できていません。

取得するデータ範囲をRange("A1:A65536")という風に65,536以内にすれば正常に動作します。

では65,537以上の要素数の配列の行列並び替えを行いたい時はどうするのか。

自力で並び替えるしかありません。

一例としてはこんな感じでしょうか

Sub TransposeBigdata()
     Dim Var(65536) As Variant
     Dim myRng As Range
     Dim i As Long
     Dim myArray(1) As Variant
     
     For Each myRng In Range("A1:A65537")
          myArray(0) = myRng.Value
          myArray(1) = myRng.Offset(, 1).Value
          Var(i) = myArray
          i = i + 1
     Next

'①---自力で多段階配列を2次元配列へ--------------------
     Dim VarTranspose(65536, 1) As Variant
     
     For i = 0 To 65536
          VarTranspose(i, 0) = Var(i)(0)
          VarTranspose(i, 1) = Var(i)(1)
     Next i
     
'②---2次元配列をセルへ一括転記-------------------------
     Range("D1:E65537").Value = VarTranspose
End Sub
    

このように要素数が多くなった場合でも簡単なループ処理で並び替えることが出来ます。

配列のセルへの一括転記

配列をセルへ一括転記する方法は

配列のサイズとセル範囲を一致させることで＝(イコール)で代入できる、というものでした。

上記の例でもそうですが、これまで配列の転記先のセル範囲を直接入力してきました。

しかし配列の大きさが実際にマクロを起動させてみるまでわかないことも多いのではないでしょうか。

そんな時は配列の大きさを取得してセル範囲へ流用する方法があります。

上記コードを書き換えてみるとこのようになります。

'②---2次元配列をセルへ一括転記-------------------------
Range("D1").Resize(UBound(VarTranspose) + 1, UBound(VarTranspose, 2) + 1).Value = VarTranspose
'Range("D1:E65537").Value = VarTranspose

ここでキモになるのはResizeプロパティとUBound関数です。

Resize (RowSize,ColumnSize)

Resizeはセル範囲を動的に変化させる際に使用します。

例えばRange("A1").Resize(4,2)としたときのイメージは以下のようになります。

UBound ( arrayname [ , dimension ] )

UBoundは引数arraynameで指定した配列の、引数dimensionで指定した次元のインデックスの最大値を返します。

Resize(UBound(VarTranspose) + 1, UBound(VarTranspose, 2) + 1)

UBound(VarTranspose)で１次元目のインデックスの最大値を返します。※引数dimensionを省略した時は１次元目が対象。

UBoundの返り値に対して１を加算しているのは、UBoundの返り値が要素数そのものを返していないからです。

今回の例では配列の要素数が１からではなく０から始まっているため、実際の要素数とインデックスの最大値が異なっているため１を加算する処理を追加することで差異を調整しています。

UBound関数で取得した配列のサイズに、Resizeプロパティを使用してセル範囲の大きさを合わせることで、セルへの一括転記を可能にしています。

まとめ

配列をセルへの一括転記出来る条件

①配列が２次元配列であること

②配列のサイズとセル範囲が同じ大きさであること

①を実現するために要素数の少ない時のTranspose関数、データが多い場合のループ処理、それぞれの行列並び替えを利用することで色々なタイプの配列を２次元配列へと変換する方法。

②を実現するためにResizeプロパティとUBound関数の使用方法

3回に渡って調べてみました。

一括転記をすることで処理の高速化が図れますので積極的に使っていきたいです。

おまけ

前回のおまけで一次元配列の向きについてモヤモヤと書きましたが、メモも取らずしばらく間が空くと、何悩んでいたかも忘れてしまった。

思い出したら書くかもしれません。(だめだこりゃ