データ範囲をテーブルに設定する方法
1行目が見出し行で2行目以降にデータが入力されているデータは、テーブルとして認識させることができます。データ範囲をテーブルとして認識させると、集計行を表示して各列のデータ件数や合計、平均などの集計値を簡単に表示できます。また、
テーブルに名前をつけるとデータ範囲をその名前で管理できるため、操作が楽になります。
ListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティ
ワークシート内のテーブルの集まりを表すListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションを取得するにはWorksheet【ﾜｰｸｼｰﾄ】オブジェクトListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティを使用します。
ListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティの書式と設定値の説明
オブジェクト.ListObjects
- オブジェクト（必須）
Worksheet【ﾜｰｸｼｰﾄ】オブジェクトを指定します。
Add【ｱﾄﾞ】メソッド
データ範囲をテーブルに設定するにはListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションのAdd【ｱﾄﾞ】メソッドを使用します。データ範囲をテーブルに設定すると、そのデータ範囲は一つの「リストオブジェクト」として操作できるようになります。ワークシートでは複数のテーブルを管理できます。複数のテーブルをまとめたものがListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションです。
Add【ｱﾄﾞ】メソッドの書式と設定値の説明
[]内は省略可能です。
オブジェクト.Add[(SourceType,Source,LinkSource,
XlListObjectHasHeaders,Destination)]
- オブジェクト（必須）
Worksheet【ﾜｰｸｼｰﾄ】オブジェクトのListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティで取得したListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションを指定します。 - SourceType【ｿｰｽﾀｲﾌﾟ】（省略可）
テーブルの元データの種類をXlListObjectSourceType列挙型の定数または、値で指定します。XlListObjectSourceType列挙型 定数 値 内容 xlSrcExternal
【ｴｯｸｽｴﾙｿｰｽｴｸｽﾀｰﾅﾙ】0 外部データ ソース
SharePoint Foundation サイトxlSrcRange
【ｴｯｸｽｴﾙｿｰｽﾚﾝｼﾞ】1 セル範囲【既定値】 xlSrcXml
【ｴｯｸｽｴﾙｿｰｽｴｯｸｽｴﾑｴﾙ】2 XML xlSrcQuery
【ｴｯｸｽｴﾙｿｰｽｸｴﾘ】3 クエリ xlSrcModel
【ｴｯｸｽｴﾙｿｰｽﾓﾃﾞﾙ】4 PowerPivot モデル - Source【ｿｰｽ】（省略可）
元データを指定します。引数SourceType【ｿｰｽﾀｲﾌﾟ】 がxlSrcRange【ｴｯｸｽｴﾙｿｰｽﾚﾝｼﾞ】 の場合は、省略可能です。データ ソースを示す Range 【ﾚﾝｼﾞ】オブジェクトの値を使用します。この引数を省略すると、Source【ｿｰｽ】 にはリスト範囲検出コードが返す既定の範囲が指定されます。引数SourceType【ｿｰｽﾀｲﾌﾟ】がxlSrcExternal 【ｴｯｸｽｴﾙｿｰｽｴｸｽﾀｰﾅﾙ】の場合は、データ ソースへの接続を示す 1 つの配列を指定します。配列の各要素には、文字列型 の値を次のように格納します。
0 – SharePoint サイトの URL
1 – ListName
2 – ViewGUID - LinkSource【ﾘﾝｸｿｰｽ】（省略可）
外部データソースをListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】にリンクするかどうかをブール型の値で指定します。設定値 内容 True リンクする False リンクしない 引数SourceType【ｿｰｽﾀｲﾌﾟ】 が xlSrcExternal【ｴｯｸｽｴﾙｿｰｽｴｸｽﾀｰﾅﾙ】 の場合は、既定値は True です。引数SourceType【ｿｰｽﾀｲﾌﾟ】 が xlSrcRange【ｴｯｸｽｴﾙｿｰｽﾚﾝｼﾞ】 の場合は無効となり、省略しないとエラーが返されます。
- XlListObjectHasHeaders【ｴｯｸｽｴﾙﾘｽﾄｵﾌﾞｼﾞｪｸﾄﾊｽﾞﾍｯﾀﾞｰｽﾞ】（省略可）
先頭行が見出しかどうかをXlYesNoGuess列挙型の定数または、値を指定します。見出しがない場合は自動的に生成されます。XlYesNoGuess列挙型 定数 値 内容 xlGuess 0 見出しがある場合はその場所を Excel が特定します。 xlYes 1 範囲全体が並べ替えられません。 xlNo 2 範囲全体が並べ替えの対象になります。【既定値】 - Destination【ﾃﾞｨｽﾃｨﾈｰｼｮﾝ】（省略可）
新規作成するリストオブジェクトの左上端となる単一のセルを参照する Range【ﾚﾝｼﾞ】 オブジェクトを指定します。 引数SourceType【ｿｰｽﾀｲﾌﾟ】 が xlSrcExternal【ｴｯｸｽｴﾙｿｰｽｴｸｽﾀｰﾅﾙ】 の場合は、必ず指定します。引数SourceType【ｿｰｽﾀｲﾌﾟ】 が xlSrcRange【ｴｯｸｽｴﾙﾚﾝｼﾞ】 の場合は、指定しません。
※テーブルに設定済みの範囲に対してListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションのAdd【ｱﾄﾞ】メソッドを使用するとエラーにります。
A1セルから始まるデータ範囲をテーブル設定のコードと解説
Sub テーブル設定()
On Error GoTo エラー処理
Range("A1").Select
ActiveSheet.ListObjects.Add
Exit Sub
エラー処理:
MsgBox "テーブルは作成済みです。"
End Sub
On Error【ｵﾝｴﾗｰ】ステートメントと GoTo【ｺﾞｳﾄｩ】ステートメントを使用してエラーが発生したら（テーブル設定済みの範囲にテーブル設定をした場合）6行目の「エラー処理」ラベルまで処理をジャンプします。
3行目【Range(“A1”).Select】
見出しを含む、データの始まりのセルを選択します。A1セルから始まるアクティブセル領域を参照するので、データ範囲全体を選択する必要はありません。
4行目【ActiveSheet.ListObjects.Add】
Worksheet【ﾜｰｸｼｰﾄ】オブジェクトのListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティで取得したListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】コレクションのAdd【ｱﾄﾞ】メソッドを使用して、データ範囲にテーブルを作成します。
5行目【Exit Sub】
Exit【ｴｸﾞｼﾞｯﾄ】ステートメントでSub【ｻﾌﾞ】プロシージャを途中で終了します。
6～7行目
【エラー処理:
MsgBox “テーブルは作成済みです。”】
エラーが発生した場合、1行目のステートメントで6行目の「エラー処理」ラベルまで処理がジャンプし、7行目からの処理を実行します。
実行結果
テーブル設定を解除する方法
テーブル設定を解除するには、まずテーブルに設定されている色やフォントなどのスタイルをListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】オブジェクトのTableStyle【ﾃｰﾌﾞﾙｽﾀｲﾙ】プロパティで解除します。テーブルの設定解除はListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】オブジェクトのUnlist【ｱﾝﾘｽﾄ】メソッドを使用します。テーブルが設定されていない範囲にUnlist【ｱﾝﾘｽﾄ】メソッドを使用すると
エラーになります。
テーブル設定解除のコードと解説
Sub テーブル解除()
On Error Resume Next
With ActiveSheet.ListObjects(1)
.TableStyle = ""
.Unlist
End With
End Sub
テーブル範囲を表すListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】オブジェクトが存在しない場合、つまり範囲がテーブル設定されていない場合、エラーが発生するので、On Error【ｵﾝｴﾗｰ】ステートメントとResume Next【ﾘｽﾞｰﾑﾈｸｽﾄ】ステートメントを使用して
エラーが発生してもデバックモードで停止させないで処理を続行させます。
3行目【With ActiveSheet.ListObjects(1)】
Worksheet【ﾜｰｸｼｰﾄ】オブジェクトのListObjects【ﾘｽﾄｵﾌﾞｼﾞｪｸﾂ】プロパティで参照したインデックス番号1のテーブルをWith【ｳｨｽﾞ】ステートメントで指定します。
4行目【.TableStyle = “”】
With【ｳｨｽﾞ】ステートメントで指定したテーブルを表すListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】オブジェクトのTableStyle【ﾃｰﾌﾞﾙｽﾀｲﾙ】プロパティに「””」長さ0の文字列(空白)を指定して既定のスタイルが適用されているテーブルのスタイルを解除します。
5行目【.Unlist】
With【ｳｨｽﾞ】ステートメントで指定したテーブルを表すListObject【ﾘｽﾄｵﾌﾞｼﾞｪｸﾄ】オブジェクトのUnlist【ｱﾝﾘｽﾄ】メソッドを使用してテーブルを解除します。
テーブルに名前とスタイルを設定する
売上を管理しているテーブルに「売上データ」とテーブル名をつけておけば、その後のVBAのコード内で「売上データ」としてそのデータ範囲が扱え管理が楽です。テーブルに設定したデータ範囲には、自動的にスタイルが適用されます。これも自分が見やすいスタイルを適用すればデータの視認性が高まります。
コードと解説
Sub テーブル設定()
Range("A1").Select
with ActiveSheet.ListObjects.Add
.Name("売上データ")
.TableStyle = "TableStyleLight6"
End With
End Sub
データ範囲の始まりのA1セルを選択します。
3行目
Addメソッドでテーブルを追加してwith ステートメントで
指定します。
4行目
Nameプロパティでテーブルに「売上データ」と
名前をつけます。
5行目
TableStyleプロパティでスタイルを設定します。
TableStyleLight6は「淡色」の6番を指定しています。
既定のテーブルスタイル一覧
Light(ﾗｲﾄ)は「淡色」の一覧からの選択になります。一覧の最初はスタイル無しで、横方向に2番目を1としてインデックス番号で指定します。
Medium(ﾐﾃﾞｨｱﾑ)「中間」左上スミを1として横方向にインデックス番号が増加します。
Dark(ﾀﾞｰｸ)「濃色」左上スミを1として横方向にインデックス番号が増加します。
スタイルのオプションの設定
スタイルを適用した場合は、スタイルにオプションを設定することができます。プロパティの値にTrue（オン）/False（オフ）を指定します
スタイルオプションプロパティ一覧
| プロパティ | 既定値 | 内容 |
|---|---|---|
| ShowHeaders | True | 見出し行 |
| ShowTableStyleColumnStripes | False | 列のストライプ |
| ShowTableStyleFirstColumn | True | 1列目用の書式 |
| ShowTableStyleLastColumn | True | 最終列用の書式 |
| ShowTableStyleRowStripes | True | 行ごとのストライプ |
| ShowTotals | False | 合計行 |
| ShowAutoFilterDropDown | True | フィルターの矢印 |
設定したテーブル全体を選択する方法
テーブルに設定した範囲を選択するには、ListObjectオブジェクトに対してRangeプロパティを使った上でSelectメソッドを使用します。
ActiveSheet.ListObjects(“売上データ”).Selectでテーブル範囲を選択できそうですがSelectメソッドを使用するときはListObject.Range プロパティを使用します。
テーブル全体を選択するコード例
Sub テーブル全体選択()
ActiveSheet.ListObjects("売上データ").Range.Select
End Sub
テーブルの見出しとデータ部分をわけて選択する方法
ListObjectのHeaderRowRange【ﾍｯﾀﾞｰﾛｳﾚﾝｼﾞ】プロパティを使うとテーブルの見出し行を取得できます。DataBodyRange【ﾃﾞｰﾀﾎﾞﾃﾞｨﾚﾝｼﾞ】プロパティを使用するとテーブルのデータ部分の範囲を取得できます。
テーブルの見出しを選択するコード例
ActiveSheet.ListObjects("売上データ").HeaderRowRange.Select
テーブルのデータ部分を選択するコード
ActiveSheet.ListObjects("売上データ").DataBodyRange.Select
テーブルに新規データ用の行を追加する方法
テーブルの最終行の一つ下に行を追加するときはListObjecctのListRows【ﾘｽﾄﾛｳｽﾞ】コレクションに対してAddメソッドを使います。
テーブルの最終行の下に行を追加するコート例
Sub 行追加()
Range("A1").Select
Selection.ListObject.ListRows.Add
End Sub
テーブルの中に行を挿入する方法
ListRows.Addメソッドに引数を指定するとその位置に行を挿入することができます。
テーブルの3行目に新たな行を挿入するコード例
ActivSheet.ListObject("売上データ").ListRows.Add 3
テーブル範囲を拡張する方法
テーブル範囲の下の行にデータが追加された場合、その追加された行もテーブル範囲に収める場合はResize【ﾘｻｲｽﾞ】メソッドの引数に更新後のセル範囲を指定して実行します。
テーブル範囲を拡張更新するコード例
Sub 範囲拡張()
With ActiveSheet.ListObject("売上データ")
.Resize.Range.CurrentRegion
End With
End Sub
以上で、データ範囲をテーブルに設定する方法についての解説を終了します。ありがとうございました。