大体でIT

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

大体でIT

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

はじめに

エクセルではテーブルという機能があります。このテーブルを使うとデータが扱いやすくなりますし、Excel VBAでの入出力もやりやすいのでかなりおすすめです。

テーブルを使うメリットをまとめました。

テーブルを使うメリット

  • 1つのシートに複数のテーブルを作成できる
  • データのセル移動が簡単
  • Excel VBAでのデータ取得が簡単
  • Excel VBAでのデータ入力も簡単

テーブルを扱う際のExcel VBAのコードをまとめたのがこちらになります。

VBAコードまとめ

  'テーブル名を“テーブル1”としてます。
  With ActiveSheet
      '全データ取得
      a = .Range("テーブル1")  
      a = .ListObjects("テーブル1").DataBodyRange  
      
      '見出し含む全データ取得
      a = .Range("テーブル1[#ALL]")  
      a = .ListObjects("テーブル1").Range  
      
      '列データ取得
      a = .Range("テーブル1[名前]")  
      a = .ListObjects("テーブル1").ListColumns("名前").DataBodyRange 
      
      '見出し含む列データ取得
      a = .Range("テーブル1[[#ALL]:[名前]]")  
      a = .ListObjects("テーブル1").ListColumns("名前").Range  
      
      '行データ取得
      a = .Range("テーブル1").Rows(1)  
      a = .ListObjects("テーブル1").Rows(1)  
      
      '見出しデータ取得
      a = .Range("テーブル1").Rows(0)  
      a = .ListObjects("テーブル1").Rows(0)  
      
      '特定セルデータ取得
      a = .Range("テーブル1").Cells(1, 1)  
      a = .ListObjects("テーブル1").DataBodyRange.Cells(1, 1)  
      
      '最終行取得
      a = .Range("テーブル1").Rows.Count  
      a = .ListObjects("テーブル1").DataBodyRange.Columns.Count  
      
      '最終行取得
      a = .Range("テーブル1").Columns.Count  
      a = .ListObjects("テーブル1").DataBodyRange.Rows.Count  
      
      '列の順番取得
      '構造化参照はありません。
      a = .ListObjects("テーブル1").ListColumns("身長").Index  
  End With

コードの入力を反対にするとテーブルへの入力になります。

テーブルを操作するためのExcel VBAコードは“構造化参照”と“ListObjects”の2種類があります。

どちらをつかっても同じようにテーブルを操作できます。ただ、列の順番取得だけは“ListObjects”の方法しかありませんので、“Listobjects”だけを覚えてもいいかもしれません。

ではそれぞれのコードがテーブルのどのデータを取得するのか説明していきます。

目次から使いそうな説明のところへ移動すると便利ですよ。

目次

テーブル名の変更方法

最初にテーブル名の変更方法について説明します。

テーブル名は作成した①テーブル内を選択した状態で②“デザイン”タブを選択し、③テーブル名の値を入力することで変更できます。

テーブル名の変更方法

それぞれのデータの取得について説明します。

全データを取得

テーブル内の全データの取得方法になります。

全データを取得

VBAコード:全データ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1")  
  End With

VBAコード:全データ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").DataBodyRange  
  End With

見出し含む全データを取得

テーブル内の見出しを含む全データの取得方法になります。

見出し含む全データを取得

VBAコード:見出し含む全データ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1[#ALL]")  
  End With

VBAコード:見出し含む全データ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").Range  
  End With

列データを取得

テーブル内の列データを取得する方法です。

列データを取得

VBAコード:列データ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1[名前]")  
  End With

VBAコード:列データ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").ListColumns("名前").DataBodyRange  
  End With

見出し含む列データを取得

見出しを含んだ列データを取得する方法になります。

見出し含む列データを取得

VBAコード:見出し含む全データ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1[[#ALL]:[名前]]")  
  End With

VBAコード:見出し含む全データ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").ListColumns("名前").Range  
  End With

行データ取得

テーブル内の行データを取得する方法です。

行データ取得

VBAコード:行データ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1").Rows(1)  
  End With

VBAコード:行データ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").Rows(1)  
  End With

見出しデータ取得

テーブルの見出しデータを取得する方法です。

見出しデータ取得

VBAコード:見出しデータ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1").Rows(0)  
  End With

VBAコード:見出しデータ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").Rows(0)  
  End With

特定セルデータ取得

テーブルデータ内の特定セルを取得する方法です。

特定セルデータ取得

VBAコード:特定セルデータ(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1").Cells(1, 1)  
  End With

VBAコード:特定セルデータ(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").DataBodyRange.Cells(1, 1)  
  End With

最終行・最終列取得

最終行と最終列を取得する方法です。

最終行・最終列取得

テーブルデータ内の最終行を取得するVBAコードはこちらになります。

VBAコード:最終行(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1").Rows.Count  
  End With

VBAコード:最終行(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").DataBodyRange.Rows.Count  
  End With

テーブルデータ内の最終列を取得するVBAコードがこちらになります。

VBAコード:最終列(構造化参照)

  With ActiveSheet
      a = .Range("テーブル1").Columns.Count  
  End With

VBAコード:最終列(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").DataBodyRange.Columns.Count  
  End With

列の順番を取得

“見出しの名前”から“列の順番”を取得する方法になります。

列の順番を取得

VBAコード:列の順番(ListObjects)

  With ActiveSheet
      a = .ListObjects("テーブル1").ListColumns("身長").Index  
  End With

“見出しの名前”から“列の順番”がわかるとテーブルのフィルタをExcel VBAでしたいときに見出しからフィルタする列を設定できるので覚えておいたほうがいいコードです。

おわりに

私は基本的に“構造化参照”を使います。理由は見出しを含めたデータ取得は基本的にしないのでその場合は構造化参照のほうがコードが短くて入力が楽だからです。

ただ、列の順番の取得だけは“ListObjects”を使って取得しています。列の順番はこの方法でしか取得できませんので。

自分の好みで“構造化参照”と“ListObjects”のどちらを使うかを決めるといいかと思います。

次回はExcel VBAでのテーブルのソートとフィルタする方法について説明します。また、テーブルへExcel VBAを使って入力する際の問題がありますのでその解決策についても次回以降にご紹介したいと思います。

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す