Excelテーブル（ListObject）化のススメと 良く使うListObject メンバ一覧

はじめに

最近のExcelのモダンな機能はテーブル化されていることが前提なものが多いです。このため表組みされたデータはテーブル化して保管した方が良いかと思います。
この記事ではテーブル化のメリットとVBAのListobjectクラスの使い方についてまとめました。

▲詳細な内容は上の記事にまとめています。

テーブルとは

データベース用語の「テーブル」と同じです。Excelでテーブルを作成する場合も、データベースのテーブルを意識して作成した方が良いです。
ExcelではテーブルをListObjectとも呼びます。

• １つの列には１つのデータ型を持つ。（数値型の列に文字列型×）
• 1データ（1レコード）につき1行
• 空白行は含めない
• 列名は重複しない
• テーブル名は１ブック内（1ファイル内）で重複しない

テーブル化のメリット

端的にいうと、データ範囲をテーブル化することによって「このセル範囲にデータが入ってますよ。」と、Excelと人間の間で共通認識ができるのでいろいろ便利になる、ということです。
それと最近のExcelはテーブル化していないと使えない機能も増えています。
具体的なメリットについて下に紹介していきます。

1. 関数に列名を使うことができる。

下図の表において、金額列にB列ｘC列の結果を表示したい場合、=[@単価]*[@数量]と計算式や関数に列名を指定することが出来ます。

@は入力されたセルの行を表します。

2. 列の増減時に関数を修正する必要がない

上記のように計算式や関数に列名を指定しておくと、列の増減によって参照列が左右にシフトしてしまった場合でも関数の計算に影響を与えることがありません。
関数を修正する必要がありませんので楽です。

3. 関数の作成・修正が楽

1つに列に関数を入力すると、同列のすべてのセルに関数が適用されます。関数をコピペする必要がありませんので楽です。またコピペ漏れも防げます。
例として、下図M3セルに関数を入力すると、テーブルとして認識されているM列のすべてのセルに数式が適用されます。ただし、もともと手打ちしてある数字が紛れていたりすると動作しません。

4. 簡単に見た目がゴージャスな表を作れる

自動で華やかなテーブルスタイル（デザインのセット（セル色・フォント色・罫線の組み合わせ）のこと）を適用してくれます。
既存のいくつかのスタイルを使っても良いですし、テーブルスタイルはカスタマイズできるので、モノクロ印刷用とか、デザインを抑えたやつとか、自己流の設定をいくつか保存しておくと、罫線やフォント等を個別に設定することなく簡単にケース合わせた設定が適用できるので便利です。

5. 自動でユニークな列名を割り当てしてくれる

テーブル化した時に列タイトルが空白の場合は、列名が付与されます。列タイトルに重複がある場合は自動でインデックス番号が不可されて列名の重複を回避します。列を挿入した場合も同様です。
列名を付けるときにユニークチェックしてくれますのでうっかり重複した列名を付けることがありません。

###6. VBAからデータを扱うのに便利なメソッドが使える
VBAでテーブル化していない表を扱う際には、end(xldown).rowあるいはend(xlup).rowメソッドで何行目までがデータの入っている行か特定してrowmax変数作って行番号を入れて、列がどこまでで・・・といった処理を書くことが一般的ですが、
テーブル化しておけば、どこまでが表の範囲で見出し行が何行目かなどは専用のメソッドが用意されていますので便利です。テーブルのVBAについてはこの記事の中で後ほど紹介したいと思います。

###7. データを追加したら自動的にテーブル範囲を拡張してくれる。

上図の場合、378行目がテーブル範囲となっております。
ここで379行目のテーブル範囲外にデータ入力した場合、379行目までテーブル範囲が拡張されます。
コピペによるデータ追加も同様です。375行目に25行分のデータを張り付けた場合は、400行目までテーブル範囲が拡張されます。
※集計行を使用している場合は、一部手入力による自動拡張は動作しません。

手動によるテーブル範囲の拡張は下図の赤枠のように、表の右下の▲マークをドラッグすることにより可能です。

###8. 勝手にセルの結合できないようにしてくれる。
セルの結合は特にVBAで自動化する際にいろいろと不都合を起こすことがありますので、人に配布して使ってもらい、自分で集計などする必要がある場合などにはメリットかなと思います。（個人的には結合セルの入ったエクセルが嫌いです。）

###9. 集計行を使える

最終行にsumやcountなどの集計行を簡単に挿入することが出来ます。

###10. "ウインドウ枠の固定"をしなくても、列見出しが見切れるまで下方向にスクロールしても列見出しが表示されたままになる。

※図内のパラメータは乱数で意味のない数値です。

###11. 下記のショートカットにて、セル移動が便利になる
これらのショートカットはテーブル化せずとも利用できるものですがテーブル化することによって挙動が少し変化します。より便利に操作可能になるかと思います。

• Ctrl+A

  • テーブル内で使うと列見出しを含めない表全体を選択できる！
    • (DataBodyRange.select)
  • さらにもう一度押すと！列見出しを含めた表全体を選択できる！！
    • (Listobject.Range.select)

• Ctrl+End

  • 表内の終端セルに移動できる！

• Ctrl+矢印キー

  • テーブル内の終端セルに移動できる！（下図参照）

※図内のパラメータは乱数で意味のない数値です。

上図において現在のカーソル位置（緑枠）からCtrl+→すると、テーブル化してあればテーブルの右端列（赤枠部）に移動できます。これ、便利じゃないですか？？？
テーブル化していないと、赤枠部にデータがない場合Excelのほんとの末端列までいっちゃいます。（XFD列とかだったかな？）

テーブル操作のVBA

VBAでテーブルのオブジェクトを操作するにはWorksheetオブジェクトメンバのListobjectオブジェクトにアクセスすることになります。
下記によく使うメソッドとプロパティを例文を含めて紹介していきます。

テーブル化する

ActiveSheet.ListObjects.Add xlSrcRange, Selection.CurrentRegion

見出し行(ヘッダー行)を取得する

Dim HeaderRowNum As Long
Dim HeaderRow As Range
HeaderRowNum = ActiveSheet.ListObjects(1).HeaderRowRange.Row '行番号
Set HeaderRow = ActiveSheet.ListObjects(1).HeaderRowRange 'Rangeオブジェクト

カラム数を取得する

Dim ColsCnt As Long
ColsCnt = ActiveSheet.ListObjects(1).Listcolumns.Count

レコード数を取得する

ActiveSheet.ListObjects(1).Listrows.Count

テーブル範囲を全選択する（見出し行を除いて）

ActiveSheet.ListObjects(1).DataBodyRange.Select

テーブル範囲を全選択する（見出し行を含めて）

ActiveSheet.ListObjects(1).Range.Select

1件目のレコードの1列目のアドレスを取得する

例1

'1列目
ActiveSheet.ListObjects(1).ListRows(1).Range(1).Address
'3列目
ActiveSheet.ListObjects(1).ListRows(1).Range(3).Address

例2

Dim MyAddress as String
MyAddress = ActiveSheet.ListObjects(1).DataBodyRange(1,1).Address

例3（行番号と列番号も同時に取得する）

Dim MyRow , MyCol as Long
Dim MyAddress as String
MyRow = ActiveSheet.ListObjects(1).DataBodyRange.Row
MyCol = ActiveSheet.ListObjects(1).DataBodyRange.Column
MyAddress = ActiveSheet.Cells(MyRow , MyCol).Address

レコード（行）の選択

'3行目を選択する。
ActiveSheet.ListObjects(1).ListRows(1).Select

最終行番号を取得する（テーブルとして定義している領域の最終行）

下記のように求めます。

• 見出し行番号（テーブルの開始行番号）+ レコード数（テーブルの行数）

Dim StartRow , RowsCnt , EndRow As Long
'見出し行番号（テーブルの開始行番号）
StartRow = ActiveSheet.ListObjects(1).HeaderRowRange.Row
'レコード数（テーブルの行数）
RowsCnt = ActiveSheet.ListObjects(1).ListRows.Count
'最終行番号
EndRow = StartRow + RowsCnt

最終行番号を取得する（実際にデータが入っている最終行）

ListRows.Countではテーブルとして定義されている領域の最終行が取得される。
つまり、実際には3行目までしかデータが入っていなくても、10行目までテーブルが定義してあると、RowsCnt=10となってしまうのです。RowsCnt=3を取得したい場合はこちらです。

Dim EndRow As Long
'最終行番号
EndRow = st.Cells(1, 1).ListObject.Range.Columns(1).Cells.Find("*", SearchDirection:=xlPrevious).Row

最終列番号を取得する

下記のように求めます。

• １列目の列番号（テーブルの開始列番号） + カラム数（テーブルの列数） - 1

Dim StartCol , ColsCnt , EndCol As Long
'１列目の列番号（テーブルの開始列番号）
StartCol = ActiveSheet.ListObjects(1).HeaderRowRange.Column
'カラム数（テーブルの列数）
ColsCnt = ActiveSheet.ListObjects(1).ListColumns.Count
'最終列番号
EndCol = StartCol + ColsCnt - 1

シート内にテーブルが存在するかを判定する

上記のようにListobjectオブジェクトを使ってコードを書く場合は、テーブルが存在しない場合も考慮する必要があるかもしれません。シート内のテーブルの有無をチェックして、テーブルが存在しない場合にプログラムを終了する例を紹介します。

If ActiveSheet.ListObjects.Count = 0 Then Exit Sub

テーブルに名前を付ける

シート内で複数のテーブルを作成することはあまりお勧めできませんが、複数のテーブルを作成する場合は名前を付けると便利です。

ActiveSheet.ListObjects(1).Name = "MyFirstTable"
Msgbox ("ぼくのはじめてのテーブルの名前は " & ActiveSheet.ListObjects(1).Name & "だよ。")

テーブル範囲のリサイズ

カレントリージョンの場合

ActiveSheet.ListObjects(1).Resize Selection.CurrentRegion

テーブルに行・列を追加

'行末に行の追加　
ActiveSheet.range("A1").ListObject.Listrows.add
'2列目に列の追加 Positionオプションで場所指定
ActiveSheet.range("A1").ListObject.ListColumns.add Position:=2

列名の取得と列名の変更

with ActiveSheet.range("A3").ListObject
    '最後列の列名を取得する
    msgbox .ListColumns(.ListColumns.count).name
    '最後列の列名を変更する
    .ListColumns(.ListColumns.count).name = "Product Name"
End with

集計行の表示

追加ではなく表示・非表示という表現をします。

ActiveSheet.ListObjects(1).ShowTotals = True

集計行の取得

Dim Rng as Range
Set Rng = ActiveSheet.ListObjects(1).TotalsRowRange

テーブルスタイルを変更する

'テーブルスタイル名は文字列で指定します。
ActiveSheet.ListObjects(1).TableStyle = "TableStyleLight3"

'テーブルスタイルを削除する（装飾をすべてなくす）には空の文字列を渡します。
ActiveSheet.ListObjects(1).TableStyle = ""

テーブルを範囲に変換（テーブルを解除）

いきなり変換すると書式が残ってしまうので、テーブルスタイルを削除してから変換すべし。

ActiveSheet.ListObjects(1).ListObject.TableStyle = ""
ActiveSheet.ListObjects(1).ListObject.Unlist

行の挿入（レコードの追加）

'最終行に行挿入
ActiveSheet.ListObjects(1).ListRows.Add
'テーブルの3行目に行挿入
ActiveSheet.ListObjects(1).ListRows.Add(3)

ちなみにAddメソッドにはAlwaysInsertというオプショナルな引数もありますが、私は使ったことがないのでここでの説明は省略します。

行・列単位のデータクリア

with ActiveSheet
   3列目のデータをまるごと空にする
   .Cells(1, 1).ListObject.ListColumns(3).DataBodyRange.ClearContents
end with

行の全件削除（レコード全件削除）

下記のいづれか

ActiveSheet.ListObjects(1).DataBodyRange.Delete
ActiveSheet.ListObjects(1).Range.Delete

テーブル範囲が列見出し行とデータボディレンジ1行の2行だけになります。（集計行を使っている場合は集計行も表示されます）中のデータは空っぽになります。

おわり

テーブルについて紹介しました。