配列のセルへの一括転記(2.配列の行列並び替え)

前回は配列のデータの並び方について調べました。

www.excellovers.com

今回は前回解説した３種類の配列について、それぞれ行列並び替えをした際に

どのようにデータ並び替わるのかを調べてみたいと思います。

Transpose関数で行列並び替え

VBAで配列の行列並び替えを行う際には

Transpose関数を使用します。

Transpose関数の引数に配列を指定すると、配列の行列並び替えが出来ます。

早速、それぞれの配列でTranspose関数を使用した際に、

どのように配列が並び替わるのかを調べていきます。

２次元配列(多次元配列)の行列並び替え

まずはそのままExcelへ一括転記が可能な２次元配列を調べてみます。

元データは前回と同じです。

以下のマクロで配列への取り込みと行列並び替えを行います。

Sub ArrayTest2()
     Dim Var As Variant
     Dim VarTranspose As Variant
     
     Var = Range("A1:B4").Value
     VarTranspose = WorksheetFunction.Transpose(Var)

End Sub

２次元配列として配列に取り込む場合は、セル範囲をそのままVariant型変数に入力すればOKです。

取り込んだ下の行で行列並び替えを行っています。

Transpose関数はVBAではなくExcel側の関数ですので

使用する際はWorksheetFunction.Transposeと記載します。

並び替え前後で配列はこのように変わっています。

２次元配列の場合はイメージ通りというか、素直に行列が並び替わっています。

並び替え前後どちらの場合もセル範囲と配列の並びを合わせてやれば一括転記が可能です。

今回の例で言えばこんなかんじです。

並び替え前

Range("A6:B9").Value = Var

並び替え後

Range("A12:D13").Value = VarTranspose

一次元配列の行列並び替え

同じように一次元配列の場合を調べてみます。

これまた元データは前回と同じです。

以下のマクロで配列への取り込みと行列並び替えを行います。

Sub ArrayTest1()
     Dim Var(3) As Variant
     Dim myRng As Range
     Dim n As Long
     
     For Each myRng In Range("A1:A4")
          Var(n) = myRng.Value
          n = n + 1
     Next
     
     Dim VarTranspose As Variant
     VarTranspose = WorksheetFunction.Transpose(Var)
End Sub

並び替え前後で配列はこのように変わっています。

これはちょっとよくわからないですね。

イメージはこんな感じでしょうか。

並び替え後は二次元配列になっていますので、先程と同じ要領でセルへの一括転記が可能です。

ちなみに並び替え後の配列をもう一度Transpose関数で並び替えると

一次元配列に戻ります。(添字の最小値が１になっていますが)

多段階配列(ジャグ配列)の並び替え

これまた同じように多段階配列の場合を調べてみます。

Sub ArrayTest3()
     Dim Var(3) As Variant
     Dim myRng As Range
     Dim n As Long
     
     For Each myRng In Range("A1:A4")
          Var(n) = Array(myRng.Value, myRng.Offset(, 1).Value)
          n = n + 1
     Next
     Dim Var2 As Variant
     Var2 = WorksheetFunction.Transpose(Var)
     Var2 = WorksheetFunction.Transpose(Var2)
End Sub

多段階配列ではTranspose関数を２回使用しています。

１回目の使用後は配列はこのような状態になっています。

１回目使用時点で多段階配列から多次元配列(二次元)に変わっています。

しかしこの状態では元データと行列の向きが異なっています。

そこでもう一度Transpose関数を使用します。

多段階配列に対して２回Transpose関数を使用した後の状態はこの様になっています。

この状態であれば元のデータと向きがあっている多次元配列になっていますので

セルへの一括転記が可能です。

イメージはこんな感じでしょうか。

ちなみにTranspose関数を3回使用した場合、 多段階配列には戻らず、1回使用したのと同じ状態になります。

まとめ

配列の状態を正しく認識し、適切な回数のTranspose関数を使用すれば、 簡単に一括貼り付けに必要な二次元に配列に変換できることがわかりました。

しかしここまで配列の行列並び替えにはTranspose関数を使用してきましたが、

実はこの関数を使用できない場合があります。

要素数が65,536までしか正しく扱えない

という制限があります。

そのようなデータ量を扱う場合は自力で並べ替える必要があります。

次回はそのような場合の並び替えの方法と、一括転記の方法を調べようと思います。

おまけ

今回のネタを調査中に、前回のおまけで書いた配列の向きについてかなり悩んでしまった。

あれはやっぱり一次元配列の時のみ一次元目は横向きなんじゃないかと思っている。

なぜそう思ったのかも次回のネタの中に少し盛り込みます。