2次元動的配列の実務での利用法 エクセルVBA

vbahairetu3eyecatch

エクセルVBAのプログラムでは、配列を使うことが結構多くあります。

その中でも、二次元動的配列を使い熟せれば強力な武器となると思います。

結構深い部分ではありますが、その入り口論を説明していきます。

エクセルVBAで変数を利用するとき、二次元配列を知っていれば何かと便利なことがあります。

今回はそんな配列をより実際に近い例題で説明していきます。

こんにちは、じゅんぱ店長（@junpa33）です。

2次元動的配列を実例的利用法で最速理解

先の記事

vbahairetueyecatch 1次元配列とは。静的配列と動的配列 エクセルVBA

で一次元配列の説明をさせていただきましたが、

今回は二次元配列についてのテーマで記事を進めさせていただきます。

二次元配列は、「変数名（1次元インデックス番号 ，2次元インデクス番号）」という様に記述します。

これは、「Cells（ 行，列 ）」と記述方法において値の配置の考え方と共通しています。

例えば、「変数名 = 飲み物」であった場合

ABC
あ緑茶ウーロン茶
いコーヒーカフェオレ
う紅茶アップルティ

二次元配列のシート上のセルとの位置関係は完全に一致します。

配列の宣言は・・・Dim　飲み物(３，３)　As　String

Cells(１，１)　=　飲み物（１，１）　＜A＞

Cells(１，２)　=　飲み物（１，２）　＜B＞

Cells(１，３)　=　飲み物（１，３）　＜C＞

Cells(２，１)　=　飲み物（２，１）　＜あ＞

Cells(２，２)　=　飲み物（２，２）　＜緑茶＞

Cells(２，３)　=　飲み物（２，３）　＜ウーロン茶＞

Cells(３，１)　=　飲み物（３，１）　＜い＞

Cells(３，２)　=　飲み物（３，２）　＜コーヒー＞

Cells(３，３)　=　飲み物（３，３）　＜カフェオレ＞

Cells(４，１)　=　飲み物（４，１）　＜う＞

Cells(４，２)　=　飲み物（４，２）　＜緑茶＞

Cells(４，３)　=　飲み物（４，３）　＜アップルティ＞

二次元配列のインデックス番号とシートのセル番号は完全連携していることは覚えておきましょう。

それでは例題で二次元配列を説明していきます。

一次元配列についての記事はこちらになります。
vbahairetueyecatch 1次元配列とは。静的配列と動的配列 エクセルVBA
配列に関しての関数・ステートメントについての記事はこちらになります。
vbahairetu2eyecatch Array・LBound・RBoundの配列関数と各ステートメントを最速理解

例題の紹介をします

今回は、一次元配列と比較対象出来るように、

先の記事で使った例題を利用します。

vbahairetup3001

先回は、これを一次元配列を使ってコード化して最安情報の欄に表示をさせました。

今回の例題のテーマ

二次元配列を利用して最安情報の空欄に値を表示させていきたいと思います。
配列の種別的には動的配列によって、「購入品リスト」や「価格表」のデータ量がその時々に増減しても配列で対応できるようにしたいと思います。

技術的な部分も含めて先の記事を参考にしていただき、VBAコードのコード提示とその説明という方法で進めていきます。

例題のコードはこれです

二次元配列変数

今回は二次元配列ですので、ReDimの再宣言は１つです。

VBAコード組み立ての流れとしては、

  1. 価格表のデータ量を調べます。
  2. データ量が判明した時点で、二次元配列の再宣言をします。
  3. 価格表のデータを二次元配列に格納していきます。
  4. 購入品リスト側の品数を調べます。価格表側の野菜種類のセル範囲をオブジェクト変数として格納します。
  5. 目的の買い出し品が価格表のどの行番号にあるかを調べ、どのスーパーが一番安いかを選び出します。
  6. 選び出した価格と、その販売スーパー名を最安情報リストに表示していきます。

コード 
Sub 二次元配列()

Dim vege() As Variant
Dim FR As Long, R2C As Long
Dim myData As Variant
Dim s As Long, t As Long, n As Long
Dim AR As Long, MtRow As Long, MinVal As Variant
Dim DaRange As Range

Worksheets("Sheet1").Select

'配列に格納するデータ範囲を考慮します。
FR = Cells(Rows.Count, 6).End(xlUp).Row - 1
R2C = Cells(2, Columns.Count).End(xlToLeft).Column - 5

'インデックス数確定により配列の再宣言をします。
ReDim vege(FR, R2C)

'価格表のデータを二次元配列に格納します。
For s = 1 To FR
    For t = 1 To R2C
        vege(s, t) = Cells(s + 1, t + 5)
    Next t
Next s

'買い出し品の品数を数えます。
AR = Cells(Rows.Count, 2).End(xlUp).Row - 2

'価格表野菜種をRangeオブジェクトに格納します。
Set DaRange = Range(Cells(2, 6), Cells(FR, 6))

For n = 1 To AR

'買い出し品を価格表の野菜リストから探します。
    On Error Resume Next

    'MtRowは配列格納範囲の先頭行からの行数です。
    MtRow = WorksheetFunction.Match(Cells(n + 2, 2), DaRange, 0)

'探し出した野菜の最低価格を調べます。
    MinVal = WorksheetFunction.Min(Range(Cells(MtRow + 1, 7), Cells(MtRow + 1, R2C + 5)))

'最低価格の販売元を確認して最終、最安情報リストに表示します。
    For t = 1 To R2C
        If vege(MtRow, t) = MinVal Then
        Worksheets("Sheet1").Range("C" & n + 2) = MinVal
        Worksheets("Sheet1").Range("D" & n + 2) = vege(1, t)
        End If
    Next t
Next n

End Sub

実行結果

vbahairetup3004

MEMO

For ～ Nextステートメントnituitehaこちらを参考にしてください。

fornextirekoeyecatch エクセルVBA！For～Nextのループと入れ子構造をVBA最速理解

入力セルの最終行を取得する方法についてはこちらを参考にしてください。

vbalastcelleyecatch データ入力済セルの最終行番号を取得する

Match関数についてはこちらが参考になります。

vbamatcheyecatch001 エクセルVBAで使うMatch関数 活用度アップでテッパン関数に！

Min関数についてはこちらが参考になります。

vbamaxminaveeyecatch Max・Min・Averageワークシート関数で最大値・最小値・平均を求める

価格表のデータ量を調べます。

価格表の最終行と最終列を調べます。

価格表にあるデータ自体は行と列の隅にあるわけではないので、その分を加味してデータセルの大きさ計ります。

FR = Cells(Rows.Count, 6).End(xlUp).Row - 1 
R2C = Cells(2, Columns.Count).End(xlToLeft).Column - 5

青枠囲みのセルデータを配列に格納します。

vbahairetup3002

二次元配列の再宣言をします。

インデックス番号として「FR」価格表のデータ行番号の最大値、「R2C」価格表のデータ列番号の最大値で再宣言します。

その都度、データの最大行と最大列が変化するものとして、この値も変数として扱います。（動的配列）

ReDim vege(FR, R2C)

価格表のデータを二次元配列に格納していきます。

For～Nextの入れ子構造で配列に格納していきます。

これだけのコード行で格納完了です。

For s = 1 To FR
    For t = 1 To R2C
        vege(s, t) = Cells(s + 1, t + 5)
    Next t
Next s

格納したデータはこのようになります。

vbahairetup3003

項目行と項目列の配列インデックス番号は、黄色と青色の部分になります。

購入品リスト側の品数を調べます。

購入品リストにある買い出し品の行数を調べます。

買い出し品を価格表と対照する前に、価格表の野菜種類のセル部分をオブジェクト変数として格納します。

'買い出し品の品数を数えます。
AR = Cells(Rows.Count, 2).End(xlUp).Row - 2

'価格表野菜種をRangeオブジェクトに格納します。
Set DaRange = Range(Cells(2, 6), Cells(FR, 6))

目的の買い出し品の価格表行番号と、一番安い価格を選び出します。

Match関数を使って買い出し品に適合する野菜がある行番号を調べます。

この行番号はオブジェクト変数で格納したセル範囲の最上部から数えた行数になります。

最低価格はMin関数で調べますが、範囲指定をセル範囲で指定しています。

'買い出し品を価格表の野菜リストから探します。
    On Error Resume Next
    'MtRowは配列格納範囲の先頭行からの行数です。
    MtRow = WorksheetFunction.Match(Cells(n + 2, 2), DaRange, 0)
'探し出した野菜の最低価格を調べます。
    MinVal = WorksheetFunction.Min(Range(Cells(MtRow + 1, 7), Cells(MtRow + 1, R2C + 5)))

最安価格と、販売スーパー名を最安情報リストに表示していきます。

最低価格になった値を持つ配列のインデックス番号を取り出します。

その番号は、スーパーの個別番号になっているので、スーパー名を取り出すことが出来ます。

'最低価格の販売元を確認して最終、最安情報リストに表示します。
    For t = 1 To R2C
        If vege(MtRow, t) = MinVal Then
        Worksheets("Sheet1").Range("C" & n + 2) = MinVal
        Worksheets("Sheet1").Range("D" & n + 2) = vege(1, t)
        End If
    Next t
目次へ戻る

2次元動的配列の実例的利用法 まとめ

今回は二次元配列で作成いたしました。

実際の場面では、配列に格納するデータ表においては、

インデックス番号と行・列番号がずれているのが普通だと思います。

二次元配列を扱うに当たってはまず最初に、この番号のズレを修正してマッチさせることが必要です。

コード組み立ても、作業手順をリスト化しておくとスムーズに進めて行くことが出来ると思います。

目次へ戻る

最初からエクセルVBAを復習する

エクセルVBAを勉強しだすと、「これをしたい、あれをしたいと」やりたいことが激増します。

そのたびに、本を見たり、ネット検索したり、と

「あーでもない、こーでもない」と、もがかれているのではないでしょうか。

VBAの基礎ベースが不確実で、途中からレベルの高いことを習得しようとしても、

時間がかかりますし、なかなか身に付きにくいものです。

まずは、焦らず、エクセルVBAの基礎ベースを復習してみましょう。

そうすることで、「調べているやりたいこと」を説明している文章の「読解力」も必ず深まります。

目次へ戻る

短期間でエクセルVBAの独学習得を目指したいなら

エクセルVBAを独学する独習方法は、学習者それぞれ十人十色、多種多様と思われます。

けれども、

出来るだけ効率よく学習するためには、いくつかの大切なポイントがあります。

独学でもVBA習得の中級クラスに達するのはそんなに難しいことではありません。

先人が行った勉強方法をあなたがそのまま利用すればよいということです。

vbastudyeyecatch002
エクセルVBAを独学で習得する！ために大切な7つのポイントを解説します

独習のための大切な7つのポイントは、上記記事にて解説しています。

重要ワード

独習によるVBA習得のキーワードは、

出来るだけ多くの実例に触れること！

です。

正直、VBAの学習について自分の周りの仕事（業務）からだけ実例を得るのでは効率良い習熟は無理です。

ハッキリ言って、

本当に短い期間でVBA習得を成功させたいなら、今使っている参考書が良書かどうかを判断し、新ツールとしてオンライン学習も取り入れて行うことが、

手っ取り早く短期間習得できるというのは間違いないでしょう。

このサイトでよく使うVBAのコードのトピックを纏めています。

「エクセルVBA最速理解」の記事一覧を開く

今回の記事はここまでです。   最後までご覧いただき有難うございました。

＜記事内容についての告知＞VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。
目次へ戻る

前の記事

vbahairetu2eyecatch
Array・LBound・RBoundの配列関数と各ステートメ…

次の記事

エクセルVBAで使うMatch関数 活用度アップでテッパン関数…
vbamatcheyecatch001