EXCEL VBA  2次元配列・セル範囲の内容を配列に格納・配列の内容をセルに代入(一括・高速化・セルの指定範囲)

 

 

EXCEL VBA  2次元配列・セル範囲の内容を配列に格納・配列の内容をセルに代入(一括・高速化・セルの指定範囲)

 

 

 ●はじめに
今回、説明するサンプルプログラムは、2次元配列を利用して、指定したセルの範囲内容を一括に配列へ格納して、格納した配列内容を一括にセルへ代入(貼り付け)するサンプルプログラムです。また、2次元配列に格納したデータを配列内で変更・入替・計算結果を格納するなど、2次元配列をワークシートの様に扱う方法も併せて説明いたします。

●【配列の基礎については、下記を参照して下さい】

EXCEL VBA 配列の使い方(Array LBound UBound)

●【連想配列を利用したクロス集計については、下記の参照して下さい】

EXCEL VBA 連想配列でクロス集計・項目別集計・グループ集計 (Scripting.Dictionary) テクニック

 

 

 

 ●書式の説明 (2次元配列)

●2次元配列を利用する場合は、下記の通りに宣言します。(宣言例)

宣言説明
Dim 配列名配列名のみ設定します。
Dim 配列名() As  VariantVariant形式で宣言します。
Dim 配列名(1 To 10,1 To 3)2次元配列を縦10行・横3列に指定
Dim 配列名(10,3)2次元配列を縦0~10行・横0~3列に指定※注意・配列の番号が0~10となるので、11行になります。

【使用例(サンプルプログラム)】
下記の2つの使用例(サンプルプログラム)は、Aパターン・Bパターン共に結果は同じ処理を行います。
Aパターンの場合は、セル範囲を選択して、一括に配列に読み取り(格納)別のセルに転記しています。
Bパターンの場合は、セル範囲から2次元配列へデータをひとつずつ登録(格納)して、登録された配列からセルにひとつずつセルに転記します。処理の方法は、Aパターンの方がプログラムも短く簡単ですが、2次元配列を覚えるには、Bパターンの方が重要です。

【パターンA】

【パターンB】

●【パターンA】・【パターンB】のプログラム実行中の2次元配列の中身は下図の通りです。

【配列の中身を表示するには、ローカルウィンドを選択します】

 

 

 

 

2次元配列に取り込んだ格納データを計算して、格納された計算結果をセルに転記します。(配列内の計算)

 

 

 ●プログラム説明 (サンプル①)2次元配列内の計算

下記のサンプルプログラムは、2次元配列に取り込んだ格納データを計算して、計算結果を配列に格納され、配列に格納された計算結果をセルに転記するサンプルプログラムです。


●実際に配列(TempData)の中身を見てみましょう。
【解説】TempData・・・は、2次元データ全体を表しています。
TempData(1,1)  100 が格納されています。
TempData(1,2)    200が格納されています。
TempData(1,1)+TempData(1,2) =TempData(1,3) 計算結果が「TempData(1,3)」に格納されている事が解ります。

 

 

 

 

●実行前~実行後 ※プログラム実行後、セル(A1:C10)の値を2次元配列へ格納して、1列目と2列目の配列を計算(3列目:足し算)します。計算結果を含めてセル(D1:F10)へ転記されました。
(画面クリックして拡大)

 

 

2つの2次元配列を利用してデータの受け渡し及び、判定結果・計算結果を別の2次元配列に格納する。

 

 

 ●プログラム説明 (サンプル②)2次元配列内の計算及び条件判定・複数の2次元配列 

下記のサンプルプログラムは、2つの2次元配列を利用してデータを別の配列へ受け渡しを行い、配列の計算を行い計算結果を元に判定を行い結果を配列に格納します。最後に、指定したセル範囲に結果を転記します。

【プログラムの説明・処理手順】
①セル(A2:C11)のセル内容をTempData01(2次元配列)へ格納します。(縦10×横3)
②TempData02(2次元配列)を再定義します。(縦10×横5)
③TempData01 ⇒ TempData02 へ配列内データを転記します。
④配列内のデータを転記する際に、国語・数学の点を合計してTempData02へ4列目に転記します。
⑤TempData02へ4列目の合計点を元に、110以上で合格・110未満で不合格とTempData02へ5列目に判定します。
⑥最後に、TempData02の配列データをセルE2よりデータを貼り付けます。

 

 

 

 

 ●実行前~実行後 ※プログラム実行後、2つの2次元配列を利用してデータを転記・配列内の計算・計算結果による判定を行い、指定したセル位置に、2次元配列のデータが転記されました。
(画面クリックして拡大)

 

 

2つの2次元配列の利用して、データの受け渡し及びデータ抽出を行い、結果をシートに転記する。

 

 

●プログラム説明 (サンプル③)2次元配列間でのデータ抽出・別シートに転記

下記のサンプルプログラムは、2つの2次元配列を利用して、データの受け渡し及び選択したデータの抽出を行い、データの抽出結果を別シートに転記するサンプルプログラムです。

【プログラムの説明・処理手順】
①ワークシート【人事データ】のセル(A2:H21)のセル内容をTempData01(2次元配列)へ格納します。(縦20×横8)
②TempData02(2次元配列)を再定義します。(縦20×横8)
③TempData01 ⇒ TempData02 へ配列内データを転記します。④の条件で転記
④配列内のデータを転記する際に、性別【男】かつ年齢【45歳未満】に該当するデータをTempData02へ転記します。
最後に、TempData02の配列データをワークシート【】のセルA2よりデータを貼り付けます。

【プログラム実行条件】
ワークシート名を【人事データ】
ワークシート名を【抽出リスト】

 

 

 

  ●実行前~実行後 ※ワークシート「人事データ」から2次元配列へ格納して、格納したデータから指定した条件(性別:男かつ45歳未満)のデータを別の2次元配列へ転記して、結果を別ワークシート「抽出リスト」へ転記されました。
(画面クリックして拡大)

 

 

最後まで、ご覧いただきまして誠に有難うございました。
また、VBAに関するテクニックや便利な手法などをこのサイトに掲載していきますので、定期的に参照していただけると幸いです。