EXCEL VBA 2次元配列から1次元配列へ変換・マトリックス表からリスト表へ変換(Array・Transpose・配列変換)
EXCEL VBA 2次元配列から1次元配列へ変換・マトリックス表からリスト表へ変換(Array・Transpose・配列変換)テクニック
今回、説明するサンプルプログラムは、マトリックスの表データを2次元配列に格納して1次元配列へ変換してリスト表へ転記するサンプルプログラムです。リスト表からマトリックス表へ変換(集計)する作業は、日常業務でも多く発生し、一般的に集計表などを作成する時に利用しますが、今回は、その逆にあたるマトリックス表からリスト表に変換する(戻す)方法を説明します。元となるリスト表が無くてもマトリックス表のみを利用して、リスト表を作成する事ができます。リスト表を作成する事で、データの追記や再度、マトリックス表(集計表)を作成する時には、便利だと思います。それでは、サンプルプログラムを交えて順番に説明いたします。
EXCEL VBA 2次元配列から指定した行データを1次元配列に格納しセルに出力します。
下記のサンプルプログラムは、マトリックス表を2次元配列に格納して、格納した2次元配列データから指定した配列データを1次元配列へ変換してリスト表としてセルに転記するサンプルプログラムです。2行目のデータをG列に出力します。
【プログラムの流れ】
① セル(A1:E9)の内容を2次元配列へ登録します。
② 2次元配列へ登録した指定行(2行目)のデータを一次元配列に格納する。
③ 1次元配列へ登録したデータをセルに出力します。
【プログラム実行条件】
・ セル(A1:E9)に上記のようなマトリックス表データを登録します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
' ' Sub TempArray01() '2次元配列から指定した行データを1次元配列に格納しセルに出力します。 Dim TempArray1D, TempArray2D As Variant TempArray2D = Range("A1:E9") TempArray1D = WorksheetFunction.Index(TempArray2D, 2) '2次元配列へ登録した指定行(2行目)のデータを一次元配列に格納する Range("G1", Cells(UBound(TempArray1D), "G")).Value = WorksheetFunction.Transpose(TempArray1D) '1次元配列へ登録したデータをセルに出力します。 End Sub ' |
●実行前~実行後 ※プログラム実行後、2次元配列に登録したデータから2行目のデータ(東京)が転記されました。
(画面クリックして拡大)
EXCEL VBA 2次元配列から指定した列データを1次元配列に格納しセルに出力します。
下記のサンプルプログラムは、マトリックス表を2次元配列に格納して、格納した2次元配列データから指定した配列データを1次元配列へ変換してリスト表としてセルに転記するサンプルプログラムです。今回は、5列目(E列)のデータをG列に出力します。
【プログラムの流れ】
① セル(A1:E9)の内容を2次元配列へ登録します。
② 2次元配列へ登録した指定行(5列目:7月)のデータを一次元配列に格納する。
③ 1次元配列へ登録したデータをセルに出力します。
【プログラム実行条件】
・ セル(A1:E9)に上記のようなマトリックス表データを登録します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
' ' Sub TempArray02() '2次元配列から指定した列データを1次元配列に格納しセルに出力します。 Dim TempArray1D, TempArray2D As Variant TempArray2D = Range("A1:E9") TempArray1D = WorksheetFunction.Index(WorksheetFunction.Transpose(TempArray2D), 5) '2次元配列へ登録したE列(E列:5列目)のデータを1次元配列へ格納します。 Range("G1", Cells(UBound(TempArray1D), "G")).Value = WorksheetFunction.Transpose(TempArray1D) '1次元配列へ登録したデータをセルに出力します。 End Sub ' |
(画面クリックして拡大)
EXCEL VBA 2次元配列から指定した列データを1次元配列に格納しセルに出力します。
(格納した1次元配列を変更:縮小)
下記のサンプルプログラムは、上記サンプルプログラム②の応用編になります。マトリックス表を2次元配列に格納して、格納した2次元配列データから指定した配列データを1次元配列へ変換してリスト表としてセルに転記するサンプルプログラムです。今回は、4列目(D列)のデータを1次元配列に格納されますが、1件目の「6月」を取り除き、2件目以降のデータのみG列に出力するサンプルプログラムです。
【プログラムの流れ】
① セル(A1:E9)の内容を2次元配列へ登録します。
② 2次元配列へ登録した指定行(5列目:7月)のデータを一次元配列に格納する。
③ 1次元配列に登録されている1件目を取り除き、配列の大きさを縮小します。
④ 縮小した1次元配列のデータをセルに出力します。
【プログラム実行条件】
・ セル(A1:E9)に上記のようなマトリックス表データを登録します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
' ' Sub TempArray03() '2次元配列から指定した行データを1次元配列に格納しセルに出力します。 Dim TempArray1D, TempArray2D As Variant Dim I, L As Long TempArray2D = Range("A1:E9") 'セル(A1:E9)の範囲を2次元配列として登録します。 TempArray1D = WorksheetFunction.Index(WorksheetFunction.Transpose(TempArray2D), 4) '2次元配列へ登録したE列(5列目)のデータを1次元配列へ格納します。 For I = 1 To UBound(TempArray1D) - 1 '全配列データ分繰り返します。 TempArray1D(I) = TempArray1D(I + 1) '配列のデータの1件目の”各月”データを消すために配列のデータを移動します。 Next I ReDim Preserve TempArray1D(UBound(TempArray1D) - 1) '配列データのサイズを変更(縮小します) Range("G1", Cells(UBound(TempArray1D), "G")).Value = WorksheetFunction.Transpose(TempArray1D) '1次元配列へ登録したデータをセルに出力します。 End Sub ' |
(画面クリックして拡大)
EXCEL VBA 2次元配列からデータ部分全てを1次元配列に格納しセルに出力します。
(別シートにリストデータを出力)
下記のサンプルプログラムは、上記サンプルプログラム③の応用編になります。マトリックス表を2次元配列に格納して、格納した2次元配列データからデータ部分全てを1次元配列へ格納してリスト表(一覧)として別シートのセルに転記するサンプルプログラムです。今回は、全てのデータを1次元配列に格納されますが、シート「DATA」のマトリックス表の1列目とA列のデータを取り除きDATA部分のみ別シート「LIST」のA列に出力するサンプルプログラムです。
【プログラムの流れ】
① ワークシート「DATA」に作成しているマトリックス表の内容を2次元配列へ登録します。(表の大きさは、自動取得します)
② 2次元配列へ登録したデータを一次元配列に全て格納する。
③ 1次元配列に登録されている1行名のデータとA列のデータを取り除き、配列の大きさを縮小します。
④ 縮小した1次元配列のデータをワークシート「LIST」のA列に出力します。
【プログラム実行条件】
・ ワークシート名「DATA」に上記のようなマトリックス表データを登録します。
※A1を起点に作成して下さい。
・ ワークシート名「LIST」を作成します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
' ' Sub TempArray04() '2次元配列から指定した行データを1次元配列に格納しセルに出力します。 Dim ws01, ws02 As Worksheet Dim TempArray1D, TempArray2D As Variant Dim I, L, CountCell As Long Set ws01 = Worksheets("DATA") Set ws02 = Worksheets("LIST") TempArray2D = ws01.Range("A1").CurrentRegion 'A1から始まる表の範囲を2次元配列へ登録します。 CountCell = 2 For L = 2 To ws01.Cells(1, ws01.Columns.Count).End(xlToLeft).Column 'ワークシートの1行目の最終列を取得します。 TempArray1D = WorksheetFunction.Index(WorksheetFunction.Transpose(TempArray2D), L) '2次元配列へ登録したデータを順番に1次元配列へ格納します。 For I = 1 To UBound(TempArray1D) - 1 '全配列データ分繰り返します。 TempArray1D(I) = TempArray1D(I + 1) '配列のデータの1件目の”各月”データを消すために配列のデータを移動します。 Next I ReDim Preserve TempArray1D(UBound(TempArray1D) - 2) '配列データのサイズを変更(縮小します) ws02.Range(ws02.Cells(CountCell, "A"), ws02.Cells(CountCell + UBound(TempArray1D), "A")).Value = WorksheetFunction.Transpose(TempArray1D) '1次元配列へ登録したデ-タをH列セルに金額を出力します。 CountCell = CountCell + UBound(TempArray1D) + 1 '転記先のセル番号を加算します。(次の開始番号) '1次元配列へ登録したデータをセルに出力します。 Next L End Sub ' |
(画面クリックして拡大)
マトリックス表の2次元配列データから年月・支店名・データを年月・支店名・金額を行単位のデータとして、別シートに転記します。
(別シートにリストデータを出力)
下記のサンプルプログラムは、マトリックス表を2次元配列にデータ部分を格納して、年月・支店名・金額を行単位にデータ化し、別シート「1次元」へ転記します。今回は、集計表(マトリックス表)を行データとして変換する(元データ)事になります。集計する際の元データが無い場合に、利用できるプログラムだと思います。
【プログラムの流れ】
① ワークシート「2次元」に作成しているマトリックス表の内容を2次元配列へ登録します。(表の大きさは、自動取得します)
② 2次元配列へ登録したデータ部分を一次元配列に全て格納する。
③ データの転記先、ワークシート「1次元」の文字列データを削除します。
④ 1次元配列に登録されている1行名のデータとA列のデータを取り除き、配列の大きさを縮小します。
⑤ ワークシート「1次元」に年月をAセルに転記します。
⑥ ワークシート「1次元」に支店名をBセルに転記します。
⑦ ワークシート「1次元」に縮小した1次元配列のデータを金額としてC列に出力します。
【プログラム実行条件】
・ ワークシート名「2次元」に上記のようなマトリックス表データを登録します。
※A1を起点に作成して下さい。
・ ワークシート名「1次元」を作成します。
※出力先のワークシートを作成します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
' ' Sub TempArray05() '2次元配列から指定した行データを1次元配列に格納しセルに出力します。 Dim ws01, ws02 As Worksheet Dim TempArray1D, TempArray2D As Variant Dim I, L, CountCell, lRow, lCol As Long Set ws02 = Worksheets("2次元") Set ws01 = Worksheets("1次元") With ws02 lRow = .Cells(.Rows.Count, "A").End(xlUp).Row 'ワークシート「2次元」のA列の最終行を取得します。 lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column 'ワークシート「2次元」の1行目の最終列を取得します。 TempArray2D = .Range("A1").CurrentRegion 'A1から始まる表の範囲を2次元配列へ登録します。 End With With ws01 .Cells.ClearContents 'ワークシート「1次元」の文字列を削除 .Range("A1") = "年月" 'ワークシート「1次元」セルA1に”年月”を代入 .Range("B1") = "支店名" 'ワークシート「1次元」セルB1に”支店名”を代入 .Range("C1") = "金額" 'ワークシート「1次元」セルC1に”金額”を代入 CountCell = 2 '1次元配列データの転記先のワークシート「1次元」の開始番号を指定します。(2行目から) For L = 2 To lCol 'ワークシート「2次元」の1行目の最終列まで繰り返します。(4月~ TempArray1D = WorksheetFunction.Index(WorksheetFunction.Transpose(TempArray2D), L) '2次元配列へ登録したデータを順番に1次元配列へ格納します。 For I = 1 To UBound(TempArray1D) - 1 '全配列データ分繰り返します。 TempArray1D(I) = TempArray1D(I + 1) '配列のデータの1件目の”各月”データを消すために配列のデータを移動します。 Next I ReDim Preserve TempArray1D(UBound(TempArray1D) - 2) '配列データのサイズを変更(縮小します) .Range(.Cells(CountCell, "A"), .Cells(CountCell + UBound(TempArray1D), "A")).Value = ws02.Cells(1, L).Value '年月をA列セルに出力します。 .Range(.Cells(CountCell, "B"), .Cells(CountCell + UBound(TempArray1D), "B")).Value = ws02.Range(ws02.Cells(2, "A"), ws02.Cells(lRow, "A")).Value '支店名をB列セルに出力します。 .Range(.Cells(CountCell, "C"), .Cells(CountCell + UBound(TempArray1D), "C")).Value = WorksheetFunction.Transpose(TempArray1D) '1次元配列へ登録したデ-タをC列セルに金額を出力します。 CountCell = CountCell + UBound(TempArray1D) + 1 '転記先のセル番号を加算します。(次の開始番号) Next L End With End Sub ' |
(画面クリックして拡大)
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。