大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAを使ってテーブルからデータを取得する方法を“ListObjects”と“構造化参照”で比較しながらまとめました。「テーブル」でデータを管理するとExcel VBAで操作しやすくなって、かなり便利ですのでテーブルの操作をマスターしていきましょう。

はじめに

この記事では、テーブルの値を取得する方法について、ご紹介します。

テーブルの値を取得するには、「ListObjects」を使う方法と「構造化参照」を使う方法があります。

「構造化参照」の方が、VBAコードが短くなるので、覚えるのが簡単です。

ただ、列の名前から、「列番号」を取得するには、「ListObjects」を使う必要があります。

場面に応じて、「ListObjects」と「構造化参照」を使えるようにしましょう。

では、テーブルの値を取得する方法について、解説していきます。

この記事を読むメリット

  • テーブルの値を取得する方法がわかります。

本記事の内容を動画でまとめています

目次から見たい項目へ移動すると便利ですよ。

目次

VBAコードまとめ

テーブルの値を取得するVBAコードについて、まとめています。

VBAコードだけ確認したい、という場合にご活用ください。

'■ListObjectsを使う

'すべての値
With ActiveSheet.ListObjects("テーブル1")
    .DataBodyRange.Select 'すべての値
    .Range.Select '見出しを含めたすべての値
End With

'行、列の値
With ActiveSheet.ListObjects("テーブル1")
    '行
    .Range.Rows(1).Select '見出し
    .Range.Rows(2).Select  '1行目の値
    .ListRows(1).Range.Select '1行目の値
    '列
    .ListColumns(1).DataBodyRange.Select '1つ目の列
    .ListColumns("名前").DataBodyRange.Select '「名前」の列の値
    .ListColumns(1).Range.Select '見出しを含めた1つ目の列
    .ListColumns("名前").Range.Select '見出しを含めた「名前」の列の値
End With

'行数、列数
With ActiveSheet.ListObjects("テーブル1")
    '行数
    Debug.Print .ListRows.Count '行数
    Debug.Print .DataBodyRange.Rows.Count '行数
    Debug.Print .Range.Rows.Count '見出しを含めた行数
    '列数
    Debug.Print .ListColumns.Count '列数
    Debug.Print .Range.Columns.Count '列数
End With

'特定の値
With ActiveSheet.ListObjects("テーブル1").Range
    .Cells(2, 1).Select '1行目の1列目を取得
    .Cells(2, .Columns.Count).Select '1行目の最終列
    .Cells(.Rows.Count, 1).Select '最終行の1列目
    .Cells(.Rows.Count, .Columns.Count).Select '最終行の最終列
    .Cells(.Count).Select '最終行の最終列
End With

'列の番号
Debug.Print ActiveSheet.ListObjects("テーブル1").ListColumns("身長").Index 'テーブルの列の番号を取得


'■構造化参照を使う

'すべての値
Range("テーブル1").Select 'すべての値
Range("テーブル1[#ALL]").Select '見出しを含めたすべての値

'行、列の値
Range("テーブル1[#Headers]").Select
Range("テーブル1").Rows(0).Select '見出し
Range("テーブル1").Rows(1).Select '1行目の値
Range("テーブル1").Columns(1).Select '列の値
Range("テーブル1[名前]").Select '列の値
Range("テーブル1[[#ALL],[名前]]").Select '見出しを含めた列の値
Range("テーブル1[#ALL]").Columns(1).Select '見出しを含めた列の値

'行数、列数
With Range("テーブル1")
    Debug.Print .Rows.Count '行数
    Debug.Print .Columns.Count '列数
End With

'行数
With Range("テーブル1[#ALL]")
    Debug.Print .Rows.Count '見出しを含めた行数
End With


'特定の値
With Range("テーブル1")
    .Cells(1, 1).Select '1行目の1列目を取得
    .Cells(.Rows.Count, 1).Select '最終行の1列目
    .Cells(1, .Columns.Count).Select '1行目の最終列
    .Cells(.Rows.Count, .Columns.Count).Select '最終行の最終列
    .Cells(.Count).Select '最終行の最終列
End With

では、解説していきます。

テーブルのすべての値を取得

テーブルの「すべての値」と「見出しを含むすべての値」を取得してみます。

すべての値

まずは、「すべての値」を取得してみます。

すべての値

ListObjectsで「すべての値」を取得するVBAコードです。

Sub TEST1()

    'すべての値
    ActiveSheet.ListObjects("テーブル1").DataBodyRange.Select
     
End Sub

実行すると、「すべての値」を取得できます。

ListObjectsで「すべての値」を取得します

ListObjectsで「すべての値」を取得できました。

すべての値(構造化参照)

構造化参照で「すべての値」を取得するVBAコードです。

Sub TEST2()

    Range("テーブル1").Select 'すべての値
    
End Sub

実行すると、「すべての値」を取得します。

構造化参照で「すべての値」を取得します

構造化参照で「すべての値」を取得できました。

見出しを含むすべての値

次は、「見出しを含むすべての値」を取得してみます。

見出しを含むすべての値

ListObjectsで「見出しを含むすべての値」を取得するVBAコードです。

Sub TEST3()
    
    '見出しを含めたすべての値
    ActiveSheet.ListObjects("テーブル1").Range.Select
    
End Sub

実行すると、「見出しを含むすべての値」を取得できます。

ListObjectsで「見出しを含むすべての値」を取得します

ListObjectsで「見出しを含むすべての値」を取得できました。

見出しを含むすべての値(構造化参照)

構造化参照で「見出しを含むすべての値」を取得するVBAコードはです。

Sub TEST4()
    
    '見出しを含めたすべての値
    Range("テーブル1[#ALL]").Select
    
End Sub

では、実行してみます。

構造化参照で「見出しを含むすべての値」を取得します

構造化参照で「見出しを含むすべての値」を取得できました。

テーブルの行・列の値を取得

テーブルの行・列の次の値を取得してみます。

  • 見出しの値
  • 行の値
  • 列の値
  • 見出しを含む列の値
  • 行数
  • 列数

では、やってみます。

見出しの値

まずは、「見出しの値」を取得してみます。

見出しの値

ListObjectsで「見出しの値」を取得するVBAコードです。

Sub TEST5()
    
    '見出し
    ActiveSheet.ListObjects("テーブル1").Range.Rows(1).Select
    
End Sub

では、実行します。

ListObjectsで「見出しの値」を取得します

ListObjectsで「見出しの値」を取得できました。

見出しの値(構造化参照)

構造化参照で「見出しの値」を取得するVBAコードです。

Sub TEST6()
    
    '見出し
    Range("テーブル1[#Headers]").Select
    'または
    Range("テーブル1").Rows(0).Select
    
End Sub

では、実行してみます。

構造化参照で「見出しの値」を取得します

構造化参照で「見出しの値」を取得できました。

行の値

次は、「行の値」を取得してみます。

行の値

ListObjectsで「行の値」を取得するVBAコードです。

Sub TEST7()
    
    '1行目の値
    ActiveSheet.ListObjects("テーブル1").ListRows(1).Range.Select
    'もしくは
    ActiveSheet.ListObjects("テーブル1").Range.Rows(2).Select

End Sub

では、実行してみます。

ListObjectsで「行の値」を取得します

ListObjectsで「行の値」を取得できました。

行の値(構造化参照)

構造化参照で「行の値」を取得するVBAコードです。

Sub TEST8()
    
    '1行目の値
    Range("テーブル1").Rows(1).Select
    
End Sub

では、実行してみます。

構造化参照で「行の値」を取得します

構造化参照で「行の値」を取得できました。

列の値

次は、「列の値」を取得してみます。

列の値

ListObjectsで「列の値」を取得するVBAコードです。

Sub TEST9()
    
    '列の値
    ActiveSheet.ListObjects("テーブル1").ListColumns(1).DataBodyRange.Select
    'もしくは
    ActiveSheet.ListObjects("テーブル1").ListColumns("名前").DataBodyRange.Select
    
End Sub

では、実行してみます。

ListObjectsで「列の値」を取得します

ListObjectsで「列の値」を取得できました。

列の値(構造化参照)

構造化参照で「列の値」を取得するVBAコードです。

Sub TEST10()
    
    '1つ目の列の値
    Range("テーブル1").Columns(1).Select
    'もしくは
    Range("テーブル1[名前]").Select
    
End Sub

では、実行してみます。

構造化参照で「列の値」を取得します

構造化参照で「列の値」を取得できました。

見出しを含む列の値

見出しを含む列の値

ListObjectsで「見出しを含む列の値」を取得するVBAコードです。

Sub TEST11()
    
    '見出しを含めた列の値
    ActiveSheet.ListObjects("テーブル1").ListColumns(1).Range.Select
    'もしくは
    ActiveSheet.ListObjects("テーブル1").ListColumns("名前").Range.Select
    
End Sub

では、実行してみます。

ListObjectsで「見出しを含む列の値」を取得します

ListObjectsで「見出しを含む列の値」を取得できました。

見出しを含む列の値(構造化参照)

構造化参照で「見出しを含む列の値」を取得するVBAコードです。

Sub TEST12()
    
    '見出しを含めた列の値
    Range("テーブル1[#ALL]").Columns(1).Select
    'もしくは
    Range("テーブル1[[#ALL],[名前]]").Select
    
End Sub

では、実行してみます。

構造化参照で「見出しを含む列の値」を取得します

構造化参照で「見出しを含む列の値」を取得できました。

行数

次は、「行数」を取得してみます。

行数

ListObjectsで「行数」を取得するVBAコードです。

Sub TEST13()
    
    'テーブルの行数
    Debug.Print ActiveSheet.ListObjects("テーブル1").ListRows.Count
    'もしくは
    Debug.Print ActiveSheet.ListObjects("テーブル1").DataBodyRange.Rows.Count
    
End Sub

データの部分の行数を取得します。

ListObjectsで「行数」を取得します

では、実行します。

ListObjectsで「行数」を取得できました

ListObjectsで「行数」を取得できました。

行数(構造化参照)

構造化参照で「行数」を取得するVBAコードです。

Sub TEST14()
    
    'テーブルの行数
    Debug.Print Range("テーブル1").Rows.Count
    
End Sub

データの部分の行数を取得します。

構造化参照で「行数」を取得します

では、実行します。

構造化参照で「行数」を取得できました

構造化参照で「行数」を取得できました。

見出しを含めた行数

ListObjectsで、「見出しを含めた行数」を取得するVBAコードです。

Sub TEST15()
    
    '見出しを含めた行数
    Debug.Print ActiveSheet.ListObjects("テーブル1").Range.Rows.Count
    
End Sub

見出しを含めた行数を取得します。

ListObjectsで「見出しを含めた行数」を取得してみます

では、実行します。

ListObjectsで「見出しを含めた行数」を取得できました

「見出しを含めた行数」を取得できました。

見出しを含めた行数(構造化参照)

構造化参照で、「見出しを含めた行数」を取得するVBAコードです。

Sub TEST16()
    
    '見出しを含めた行数
    Debug.Print Range("テーブル1[#ALL]").Rows.Count
    
End Sub

見出しを含めた行数を取得します。

構造化参照で「見出しを含めた行数」を取得してみます

では、実行します。

構造化参照で「見出しを含めた行数」を取得できました

「見出しを含めた行数」を取得できました。

列数

次は、「列数」を取得してみます。

列数

ListObjectsで「列数」を取得するVBAコードです。

Sub TEST17()
    
    'テーブルの列数
    Debug.Print ActiveSheet.ListObjects("テーブル1").ListColumns.Count
    'もしくは
    Debug.Print ActiveSheet.ListObjects("テーブル1").Range.Columns.Count

End Sub

列数を取得します。

ListObjectsで「列数」を取得します

では、実行してみます。

ListObjectsで「列数」を取得できました

ListObjectsで「列数」を取得できました。

列数(構造化参照)

構造化参照で「列数」を取得するVBAコードです。

Sub TEST18()
    
    'テーブルの列数
    Debug.Print Range("テーブル1").Columns.Count
    
End Sub

テーブルの「列数」を取得します。

構造化参照で「列数」を取得します

では、実行します。

構造化参照で「列数」を取得できました

構造化参照で「列数」を取得できました。

テーブルの特定の値を取得

テーブルの次の「特定の値」を取得してみます。

  • 1行目の1列目の値
  • 1行目の最終列の値
  • 最終行の1列目の値
  • 最終行の最終列の値

では、やってみます。

1行目の1列目

まずは、「1行目の1列目の値」を取得してみます。

1行目の1列目の値

ListObjectsで「1行目の1列目の値」を取得するVBAコードです。

Sub TEST19()
    
    '1行目の1列目
    With ActiveSheet.ListObjects("テーブル1").Range
        .Cells(2, 1).Select
    End With
    
    'もしくは
    With ActiveSheet.ListObjects("テーブル1")
        .Range(2, 1).Select
    End With
    
End Sub

では、実行してみます。

ListObjectsで「1行目の1列目の値」を取得します

ListObjectsで「1行目の1列目の値」を取得できました。

1行目の1列目の値(構造化参照)

構造化参照で「1行目の1列目の値」を取得するVBAコードです。

Sub TEST20()
    
    '1行目の1列目
    With Range("テーブル1")
        .Cells(1, 1).Select
    End With
    
End Sub

では、実行してみます。

構造化参照で「1行目の1列目の値」を取得します

構造化参照で「1行目の1列目の値」を取得できました。

1行目の最終列

次は、「1行目の最終列の値」を取得してみます。

1行目の最終列の値

ListObjectsで「最終列の値」を取得するVBAコードです。

Sub TEST21()
    
    '1行目の最終列
    With ActiveSheet.ListObjects("テーブル1").Range
        .Cells(2, .Columns.Count).Select
    End With
    
    'もしくは
    With ActiveSheet.ListObjects("テーブル1")
        .Range(2, .Range.Columns.Count).Select
    End With
    
End Sub

では、実行してみます。

ListObjectsで「最終列の値」を取得します

ListObjectsで「最終列の値」を取得できました。

1行目の最終列の値(構造化参照)

構造化参照で「最終列の値」を取得するVBAコードです。

Sub TEST22()
    
    '1行目の最終列
    With Range("テーブル1")
        .Cells(1, .Columns.Count).Select
    End With
    
End Sub

では、実行してみます。

構造化参照で「最終列の値」を取得します

構造化参照で「最終列の値」を取得できました。

最終行の1列目

次は、「最終行の1列目の値」を取得してみます。

最終行の1列目の値

ListObjectsで「最終行の1列目の値」を取得するVBAコードです。

Sub TEST23()
    
    '最終行の1列目
    With ActiveSheet.ListObjects("テーブル1").Range
        .Cells(.Rows.Count, 1).Select
    End With
    
    'もしくは
    With ActiveSheet.ListObjects("テーブル1")
        .Range(.Range.Rows.Count, 1).Select
    End With
    
End Sub

では、実行してみます。

ListObjectsで「最終行の1列目の値」を取得します

ListObjectsで「最終行の1列目の値」を取得できました。

最終行の1列目の値(構造化参照)

構造化参照で「最終行の1列目の値」を取得するVBAコードです。

Sub TEST24()
    
    '最終行の1列目
    With Range("テーブル1")
        .Cells(.Rows.Count, 1).Select
    End With
    
End Sub

では、実行してみます。

構造化参照で「最終行の1列目の値」を取得します

構造化参照で「最終行の1列目の値」を取得できました。

最終行の最終列

次は、「最終行の最終列の値」を取得してみます。

最終行の最終列の値

ListObjectsで「最終行の最終列の値」を取得するVBAコードです。

Sub TEST25()
    
    '最終行の最終列
    With ActiveSheet.ListObjects("テーブル1").Range
        .Cells(.Rows.Count, .Columns.Count).Select
        'もしくは
        .Cells(.Count).Select
    End With
    
    'もしくは
    With ActiveSheet.ListObjects("テーブル1")
        .Range(.Range.Rows.Count, .Range.Columns.Count).Select
    End With
    
End Sub

では、実行してみます。

ListObjectsで「最終行の最終列の値」を取得します

ListObjectsで「最終行の最終列の値」を取得できました。

最終行の最終列の値(構造化参照)

構造化参照で「最終行の最終列の値」を取得するVBAコードです。

Sub TEST26()
    
    '最終行の最終列
    With Range("テーブル1")
        .Cells(.Rows.Count, .Columns.Count).Select
        'もしくは
        .Cells(.Count).Select
    End With
    
End Sub

では、実行してみます。

構造化参照で「最終行の最終列の値」を取得します

構造化参照で「最終行の最終列の値」を取得できました。

テーブルの列番号を取得

テーブルの「列番号」を取得してみます。

「列番号」は、「構造化参照」では取得できなくて、「ListObjects」のみで取得できます。

指定した列の番号

指定した列の番号を取得してみます。

指定した列の番号

ListObjectsで「指定した列の番号」を取得するVBAコードです。

Sub TEST27()
    
    '列の番号を取得
    Debug.Print ActiveSheet.ListObjects("テーブル1").ListColumns("身長").Index
    
End Sub

ListObjectsで「指定した列の番号」を取得します。

ListObjectsで「指定した列の番号」を取得します

では、実行してみます。

ListObjectsで「指定した列の番号」を取得できました

ListObjectsで「指定した列の番号」を取得できました。

構造化参照はなし

構造化参照で、「列番号」は取得できないので、「列番号」は、「ListObjects」を使って、取得しましょう。

おわりに

この記事では、テーブルの値を取得する方法について、ご紹介しました。

テーブルの値を取得するには、「ListObjects」を使う方法と「構造化参照」を使う方法があります。

「構造化参照」の方が、VBAコードが短くなるので、覚えるのが簡単です。

ただ、列の名前から、「列番号」を取得するには、「ListObjects」を使う必要があります。

場面に応じて、「ListObjects」と「構造化参照」を使えるようにしましょう。

最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す