動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

公開日：2015-05-30　最終更新日：2016-08-12

動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、
そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。

2次元配列の場合、
ReDim myArray(2, 10)
↓
ReDim myArray(2, 11)
これはOKですが、
ReDim myArray(2, 10)
↓
~~ReDim myArray(3, 10)~~
これはダメです。

Excelの表、つまりシート上では縦(行)方向にデータが増減するものです。
しかし、配列をシートに出力すると、
1次元目が縦(行)、2次元目が横(列)、になります。
つまり、2次元配列の1次元目を項目として、2次元目でデータを増減させるように作成すると、
縦横が逆になってしまいます。

そこで、縦横を入れ替えてシートに出力する必要が出てきます。

まずは自力で縦横を入れ替えます。
ある意味、基本ですし、いつでも書けるようにしておく必要があります。


  
    
      Dim i As Long

Dim j As Long

Dim myArray()

Dim myArray2()

ReDim myArray(2, 10)

・・・

ReDim Preserve myArray(2, 11)

・・・

ReDim myArray2(UBound(myArray, 2), UBound(myArray, 1))

For i = 0 To UBound(myArray, 1)

　　For j = 0 To UBound(myArray, 2)

　　　　myArray2(j, i) = myArray(i, j)

　　Next

Next

Range(Cells(1, 1), Cells(UBound(myArray2, 1) + 1, UBound(myArray2, 2) + 1)) = myArray2

上の例では、動的配列が0から始まっていますので、+1しているところに注意してください。

少し工夫して、ワークシート関数のTRANSPOSE関数を使ってみましょう。


  
    
      Dim myArray()

Dim myArray2()

ReDim myArray(2, 10)

・・・

ReDim Preserve myArray(2, 11)

・・・

      myArray2 = WorksheetFunction.Transpose(myArray)

      Range(Cells(1, 1), Cells(UBound(myArray2, 1), UBound(myArray2, 2))) = myArray22

Transposeの出力は、配列が1から始まるように作成されますので、先例の+1は不要となっている点に注意してください。
WorksheetFunction.Transpose
これで次元を入れ替えています。
TRANSPOSE関数はシートでは縦横を入れ替える配列関数です。

上では、縦横入れ替えて一旦別の配列に入れていますが、
Transposeの結果を直接シートに出力することもできます。


  
    
      Dim myArray()

Dim myArray2()

ReDim myArray(2, 10)

・・・

ReDim Preserve myArray(2, 11)

      ・・・

      Range(Cells(1, 1), Cells(UBound(myArray, 2) + 1, UBound(myArray, 1) + 1))
      = WorksheetFunction.Transpose(myArray)

どの方法が良いという事もありませんが、
いずれの方法も、一応は押さえておくと良いでしょう。

後日談

Transposeには制限事項があるようです。
MSの古い記事ですが、以下の記載がありました。

配列に 255 文字を超える要素を含めることはできません。
配列に Null 値を含めることはできません。
配列内の要素数が 5461 を超えることはできません。

しかし、実際にやってみましたが、20000*2の配列も処理できました。
上記は、Excel2003の記事なので、2007以降で制限緩和されたのだと思います。
しかし、10万では、結果が正しく出力されませんでした。
実際の処理では、要素数は変化しますので、
数万を超える配列の場合には、使用しない方が良いでしょう。

同じテーマ「マクロVBA技術解説」の記事

エクセルVBAのパフォーマンス・処理速度に関するレポート
 VBAのFindメソッドの使い方には注意が必要です
 マクロVBAの高速化・速度対策の具体的手順と検証
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）
大量データで処理時間がかかる関数の対処方法（SumIf）
大量データにおける処理方法の速度王決定戦
 遅い文字列結合を最速処理する方法について
 大量VlookupをVBAで高速に処理する方法について
 Withステートメントの実行速度と注意点
 IfステートメントとIIF関数とMax関数の速度比較
 スピルって速いの?スピルの速度について

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA技術解説
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）