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

2021.08.21
スポンサーリンク

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

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

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

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

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

スポンサーリンク

ListObject（リストオブジェクト）

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

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

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

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

※VBAからテーブルを作成する方法についてはこちら

【EXCEL VBA】シートの範囲をテーブル形式に変換する ListObject
ワークシートでもVBAでもテーブルは便利(^o^)ワースシート上でもお馴染みのテーブル形式の表をVBAで作成する方法を紹介します！テーブルテーブルってこんなやつです。ワークシート上にある表の中に、アクティブセルを置き、「挿入タブ＞テーブル」...
hirachin.com
2021.11.21

スポンサーリンク

テーブルを捕まえる

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

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

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

です。

１つずつ見ていきましょう！

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

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

Range(“A1”).ListObject

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

こんな感じです。

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

テーブルがあるシートから指定する

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

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

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

シート.ListObjects(1)

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

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

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

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

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

シート.ListObjects(“テーブル1”)

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

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

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

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

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

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

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

テーブル全体（見出し・データ・行・列）の取得

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

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

見出しの取得

見出し（タイトル部分）の取得

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

データ部分の取得（見出し含まない）

見出し（タイトル部分）の取得

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

列の取得

”見出し含む”列の取得

複数ある列を集めた、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

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

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

行の取得

”見出し含む”行の取得

複数ある行を集めた、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
    
    '３行目のデータを取得する
    Dim rng As Range
    Set rng = row_data(3).Range
    
End Sub

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

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

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

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

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

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でのテーブル操作の基本となる、テーブル全体や各部位の指定方法を紹介しました！

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

スポンサーリンク

コメント

タイトルとURLをコピーしました