Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

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

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

www.excellovers.com

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

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

Transpose関数で行列並び替え

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

Transpose関数を使用します。

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

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

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

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

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

元データは前回と同じです。
f:id:ExcelLover:20181021120705j:plain

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

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

End Sub

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

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

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

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

並び替え前後で配列はこのように変わっています。 f:id:ExcelLover:20181025191318j:plain

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

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

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

並び替え前

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

並び替え後

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

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

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

これまた元データは前回と同じです。
f:id:ExcelLover:20181021115844j:plain

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

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

並び替え前後で配列はこのように変わっています。
f:id:ExcelLover:20181025204359j:plain

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

イメージはこんな感じでしょうか。
f:id:ExcelLover:20181025192042j:plain

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

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

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

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

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

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関数を2回使用しています。

1回目の使用後は配列はこのような状態になっています。
f:id:ExcelLover:20181025203605j:plain

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

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

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

多段階配列に対して2回Transpose関数を使用した後の状態はこの様になっています。
f:id:ExcelLover:20181025203741j:plain

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

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

イメージはこんな感じでしょうか。 f:id:ExcelLover:20181025204127j:plain

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

まとめ

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

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

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

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

という制限があります。

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

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

おまけ

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

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

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