【EXCEL VBA | ListObject】ワークシートでもVBAでもテーブルは便利(^o^) その1

スポンサーリンク

ワークシートでもVBAでもテーブルは便利(^o^)

EXCELに慣れてくると、ワークシート上のデータを扱うときに”テーブル形式”を使うようになると思います。

数式が自動でコピーされたり色々便利ですよね(^o^)

もちろんVBAでもワークシートの”テーブル”を扱う機能が用意されています。

とっても便利なので紹介させていただきます!

ListObject(リストオブジェクト)

ワークシート上のテーブルは分かると思いますが、念の為。

ワークシート上にある表の中に、アクティブセルを置き、「挿入タブ>テーブル」で作れますね。

この”テーブル”ですが、VBAで言うとテーブルにした時点で、”ListObject(リストオブジェクト)”というオブジェクトが作られます。

VBAのプログラムから、テーブルを扱う場合は、このListObjectを捕まえることになります。

テーブルを捕まえる

まずは、このテーブルの捕まえ方です。

テーブルを捕まえるには、いくつか方法があります。

  1. テーブルのセルから指定する
  2. テーブルがあるシートから指定する
  3. テーブルの名前だけで指定する

です。

1つずつ見ていきましょう!

1.テーブルのセルから指定する

テーブル内のセルから、指定する場合は、テーブルがあるセル(テーブル内ならどこでもOK)に続けて、ListObjectを指定します。

Range("A1").ListObject

Sub sample()

    Dim テーブル As ListObject
    Set テーブル = Range("A1").ListObject

End Sub

こんな感じです。

Range("A1")は、Range("B4")でも、Range("H14")でも、テーブルの範囲内ならどこでもOKです。

2.テーブルがあるシーとから指定する

シートから指定するには、シートオブジェクトからListObjects(インデックス)か、 ListObjects(テーブル名)で指定しいます。

シートオブジェクトから指定する場合は、ListObjyec”s”と複数形になっていることに注意してください。

インデックスで指定する場合

シート.ListObjects(1)

Sub sample()

    Dim シート As Worksheet
    Set シート = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = シート.ListObjects(1) ’1つ目に作成したテーブルを指定(インデックスは1から)
    
End Sub

同じシート上にあるテールブルには、作られた順番にインデックスが振られています。ListObjectsに続いてインデックスをしてすることで指定のテーブルを取得出来ます。


テーブルのインデックスは、テーブルが消されたときに繰り上がりますので注意してください。

例えば、1つのシートにテーブルが3つあった場合は、インデックス2のテーブルを削除すると、インデックス3のテーブルが、インデックス2になります。


テーブル名で指定する場合

シート.ListObjects("テーブル1")

Sub sample()

    Dim シート As Worksheet
    Set シート = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = シート.ListObjects("テーブル1") ’テーブル名を指定する
    
End Sub

テーブルには1つ1つにテーブル名が付けられています。ワークシート上の「テーブルデザインタブ>テーブル名」で確認することが出来ます。テーブルデザインタブは、確認したいテーブルの範囲内にアクティブセルを置くことで表示されます。

テーブル名は変更出来るので、扱いやすい名前に変えておくと便利です。

3. テーブルの名前だけで指定する

こちらについては、構造化参照という方法とセットで後ほど説明します。

テーブル内のデータを取得する(見出し・データ・行・列)

テーブルは、「見出し」「データ」「行」「列」で構成されていますが、それぞれの部位を指定することが出来ます。

1.テーブル全体(見出し・データ・行・列)の取得

見出しとデータ、つまりテーブルそのものの取得

ListObject.Range

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    'テーブル全体のデータ
    Dim rng As Range
    Set rng = テーブル.Range ’ここ ListObject.Range
     
End Sub

2.見出しの取得

見出し(タイトル部分)の取得

ListObject.HeaderRowRange

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    'タイトル部分の取得(データ含まない)
    Dim rng As Range
    Set rng = テーブル.HeaderRowRange ’ここ ListObject.HeaderRowRange
    
End Sub

3.データ部分の取得(見出し含まない)

見出し(タイトル部分)の取得

ListObject.DataBodyRange

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    'データ部分の取得(見出し含まない)
    Dim rng As Range
    Set rng = テーブル.DataBodyRange ’ここ ListObject.DataBodyRange
    
End Sub

4.列の取得

”見出し含む”列の取得

複数ある列を集めた、ListColumnsコレクションから、特定の列をインデックスか列名で指定します。

ListColumnオブジェクトになります。

上記は、列そのものになりますので、中の実データを指定する場合は、更に.Rangeを続けます。

こちらは、Rangeオブジェクトです。

ListObject.ListColumns(インデックス).Range

ListObject.ListColumns(列名).Range

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    '列の取得
    Dim col_data As ListColumn
    Set col_data = テーブル.ListColumns(1)  'インデックスで取得(3)は性別列
    
    '列のデータを取得
    Dim rng As Range
    Set rng = col_data.Range '列の実データを取得
    
End Sub

インデックスで指定する場合は、左の列から1、2、3となります。

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    '列の取得
    Dim col_data As ListColumn
    Set col_data = テーブル.ListColumns("性別")  '列名で取得
    
    '列のデータを取得
    Dim rng As Range
    Set rng = col_data.Range '列の実データを取得
    
End Sub

列名で指定する場合の列名は、見出しの行に入っている項目名がそのまま列名になっています。

”列そのもの”を取得してから、”列の中の実データ”を取得するというのが分かりにくいので注意してください。

”見出しを抜いた”列データの取得

ListColumn.Rangeで取得したデータは、見出しの入った列のデータになっています。

ここから、見出し無しのデータを取得するには、ListColumnに対して、.Rangeではなく、.DataBodyRangeを使います。

テーブル全体に対して、データ部分のみ取得した時と同じ方法ですね。

ListObject.ListColumns(インデックス).DataBodyRange

ListObject.ListColumns(列名).DataBodyRange

※ListObject.ListColumns(列名).DataBodyRangeの方だけサンプル付けます。

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    '列の取得
    Dim col_data As ListColumn
    Set col_data = テーブル.ListColumns("性別")  '列名で取得
    
    '列のデータを取得
    Dim rng As Range
    Set rng = col_data.DataBodyRange '列の実データを取得(見出し無し)
    
End Sub

5.行の取得

”見出し含む”行の取得

複数ある行を集めた、ListRowsコレクションから、特定の列を行番号で指定します。

ListRowオブジェクトになります。

※ListRowsコレクションには、見出し行は含まれません。

ListObject.ListRows

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    '行データの取得
    Dim row_data As ListRows
    Set row_data = テーブル.ListRows
    
End Sub

上記のRowsコレクションにインデックスを指定して、それぞれの行を指定します。

列の時と同じように、これは行そのものなので、中のデータを取得する場合は、.Rangeを続けます。

ListObject.ListRows(行番号).Range

Sub sample()

    Dim sheet As Worksheet
    Set sheet = ActiveSheet

    Dim テーブル As ListObject
    Set テーブル = sheet.ListObjects("テーブル1")
    
    '行データの取得
    Dim row_data As ListRows
    Set row_data = テーブル.ListRows
    
    '3行目のデータを取得する
    Dim rng As Range
    Set rng = row_data(3).Range
    
End Sub

行番番号は、見出しを含まずに1から始まります。

構造化参照を使ってテーブルを指定する

先程飛ばした、テーブルを捕まえる3つ目の方法です。

ListObjectではなく、Rangeを使って捕まえます。

3.テーブルの名前だけで指定する

Range(テーブル名)の形で指定します。

Range("テーブル1")

Sub sample()

    Dim テーブル As Range
    Set テーブル = Range("テーブル1")
    
End Sub

この場合、型がRangeになりますので変数の型指定など注意してください。


また、ListObjectでは無いので、特定の列やデータを指定するときに、ListObjectと違いがあるので注意する必要があります。


構造化参照について

この方法で、テーブルの部位を指定する場合は、構造化参照を使用します。

構造化参照とは、

テーブル名[[特殊項目指定子], [列指定子]]

とう形です。

ワークシート上で、「=」に続けてテーブルの参照しようとすると数式バーに出てきたりするやつですね(^o^)

ワークシート上だと次のような感じです。

=テーブル1[@ふりがな]

「やすい けんいち」がセルに表示されます。

「@」は、数式を入れたセルと同じ行という意味になります。※VBAでは使えません

見出しの参照

=テーブル1[#見出し]

見出し(一部)の参照

=テーブル1[[#見出し],[ふりがな]:[年齢]]

列の参照(見出し含む)

=テーブル1[ [ #すべて ], [ 名前 ] ]

列の参照(データのみ)

=テーブル1[ 名前 ]

テーブル全て(見出し含む)

=テーブル1[ #全て ]

テーブル全て(データのみ)

=テーブル1

年齢列の平均値を出す場合

=AVERAGE(テーブル1[年齢])

確定すると、平均値が表示される

このように数式の中でも指定出来ます。

つまり、

Range("テーブル1")

で捕まえたテーブルは、

=テーブル1

と同じになりますので、見出しを含まないデータ全体であることが分かりますね。

ただし、ここまで紹介したようにワークシート上の構造化参照は

[#すべて] [#データ] [#見出し] [#集計] のように、日本語で表記されていましたが、VBAの場合は日本語は使えません。

[#すべて] → [#All]

[#データ]  → [#Data]

[#見出し]  → [#Headers]

[#集計]  → [#Totals]

といったように、全て英語表記になりますので注意してください。

構造化参照を使った、各部位の指定のサンプルです。

テーブル全体の取得(見出し業を含む)

Range(”テーブル名[#All]”)

Sub sample()
    
    'テーブル全体の取得(見出し行含む)
    Dim rng As Range
    Set rng = Range("テーブル1[#All]")
    
End Sub

テーブル全体の取得(データのみ)

Range(”テーブル名”)

または

Range(”テーブル名[#Data]”)

Sub sample()
    
    'テーブル全体の取得(見出し行含む)
    Dim rng As Range
    Set rng = Range("テーブル1")
    
End Sub

見出し行の取得

Range(”テーブル名[#Headers]”)

Sub sample()
    
    'テーブル全体の取得(見出し行含む)
    Dim rng As Range
    Set rng = Range("テーブル1[#Headers]")
    
End Sub

列の取得(見出し含む列全体)

Range(”テーブル名[[#ALL], [列名]]”)

Sub sample()
    
    'テーブル全体の取得(見出し行含む)
    Dim rng As Range
    Set rng = Range("テーブル1[[#All], [性別]]")
    
End Sub

列の取得(データのみ)

Range(”テーブル名[列名]”)

または

Range(”テーブル名[[#Data], [列名]]”)

Sub sample()
    
    'テーブル全体の取得(見出し行含む)
    Dim rng As Range
    Set rng = Range("テーブル1[[#Data], [性別]]")
    
End Sub

行の取得

構造化参照を使った、行の取得は出来ません。

まとめ

今回は、VBAでのテーブル操作の基本となる、テーブル全体や各部位の指定方法を紹介しました!

次回以降で、フィルターや行や列の挿入や削除について説明したいと思います!

追記しました!