VBAでのテーブル操作について、VBA100本ノック53本目の問題を題材にして解説したいと思います。
主に以下の２つについて解説します。
- テーブル内の各部分の選択方法
- テーブル内のデータを検索・抽出する方法
いずれもVBAでテーブルを扱う場合の基本的な内容ですので、これからVBAでテーブルを扱いたいという方にはおすすめの内容です。
問題(VBA100本ノック53本目）
上記のテーブルの中で
「東京都」の「男」で「35歳以上」の行の備考欄に「対象」と入れる。
模範解答↓
Sub VBA100_53_01()
Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject 'テーブル変数「tbl」の宣言
Set tbl = ws.Range("A1").ListObject 'テーブル変数「tbl」にテーブルを格納
tbl.DataBodyRange.Columns(getCol(tbl, "備考")).ClearContents'「tbl」の「備考」列のデータを削除
tbl.ShowAutoFilter = False
With tbl.DataBodyRange 'DataBodyRangeはタイトル行と集計行をの除くデータ
.Columns(getCol(tbl, "備考")).ClearContents'「tbl」の「備考」列のデータを削除
.AutoFilter Field:=getCol(tbl, "都道府県"), Criteria1:="東京都"'「tbl」の「都道府県」列の東京データを抽出
.AutoFilter Field:=getCol(tbl, "性別"), Criteria1:="男"'「tbl」の「性別」列の男データを抽出
.AutoFilter Field:=getCol(tbl, "誕生日"), Criteria1:="<" & DateSerial(Year(Date) - 35, 12, 31)
'「tbl」の「誕生日」列の35歳以上データを抽出
On Error Resume Next
.Columns(getCol(tbl, "備考")).SpecialCells(xlCellTypeVisible) = "対象" '備考列の可視セルに「対象」と入れる
End With
ws.ShowAllData
End Sub
'列番号を返すファンクションプロシージャ
Function getCol(ByVal aTbl As ListObject, aColName As String) As Long
getCol = aTbl.ListColumns(aColName).Index
End Functionでは、模範解答に沿ってテーブルの基本的なことを解説します。
テーブルとは？
表の任意の場所を選択してリボンの「挿入」→テーブルグループ：「テーブル」ボタンで通常の表をテーブル化することができます。
テーブル化することで表のデータの追加（行・列の追加）や集計が容易になり、機能的に表を管理できます。
以下の過去記事で詳しく解説しています↓↓
範囲からテーブルへの変換方法
前述にもありますが、範囲のテーブル化は、ワークシート上でのボタン操作は、表の任意の場所を選択してリボンの「挿入」→テーブルグループ：「テーブル」ボタンで通常の表をテーブル化することができます。
VBAでのテーブルの変換方法は、ListObjectsコレクションのAddメソッドを実行します。
.ListObjects.Add（SorceType,Sorce,LinkSorce,xllistthasheaders,Destination,TableStyleName）
Sub テーブルに変換()
ActiveSheet.ListObjects.Add SourceType:=xlSrcRange,Source:=ActiveSheet.Range("A1").CurrentRegion
End Subまたは
Sub テーブルに変換()
ActiveSheet.ListObjects.Add Source:=ActiveSheet.Range("A1").CurrentRegion
End Sub主要引数の説明
SorceType（省略可能）・・・ワークシート上の範囲でしたら、SorceTyoe:=xlSrcRange
Sorce・・・ワークシート上の範囲でしたら、Sorce:=Rangeオブジェクト
XlListObjectHasHeaders（省略可能）・・・指定した範囲の1行目が列名かどうか指定します。1行目が列名の場合：xlYse、列名ではない場合：xlNO、自動判別の場合：xlGuess。規定値はxlGuess
Addメソッドの引数の詳細はリンクを貼っておきます↓↓
テーブルの選択方法-ListObject-
VBAでテーブルを表すオブジェクトがListObjectです。
シート内のListObjectsコレクションのListObjectオブジェクトです。
テーブル内のセルから選択する場合は、Rangeオブジェクトの後にLIstObjectを続けます。
ワークシートオブジェクト.Range(“A1”).ListObject
※テーブル化しているセルであればどこでもOKです。（例えばRange(“B1”)やRange(“C2”)でも大丈夫です。）
Dim ws As Worksheet: Set ws = ActiveSheet
Dim tbl As ListObject 'テーブル変数「tbl」の宣言
Set tbl = ws.Range("A1").ListObject 'テーブル変数「tbl」にテーブルを格納その他の選択方法
ワークシートオブジェクト.ListObjects(1)
ListObjectsコレクションに（インデックス）で指定します。インデックスは作られた順番に振られます。（最初に作ったテーブルが１，次に作ったテーブルが２、次が３・・・）
ワークシートオブジェクト.ListObjects(”テーブル名”)
テーブルを作成するとテーブル名が付けられます。こちらでテーブルを選択します。
テーブル内のそれぞれの部分の指定方法
ListObjectのプロパティでテーブル内の各部分を選択できます。
Range（テーブル範囲の全選択）
ListObject.Range
HeaderRowRange（見出し行部分）
ListObject.HeaderRowRange
DataBodyRange（見出し行と集計行を除いたデータ部分）
ListObject.DataBodyRange
ListColumns（テーブル内の列を表すコレクション）
ListObject.listcolumns(インデックス)
または
ListObject.listcolumns("列名")上記の記述は列そのものを表すので、列のデータを取得したい場合は.Rangaを付けます。
ListObject.listcolumns(インデックス).Range
または
ListObject.listcolumns("列名").Range見出しを抜いた列データの取得
先のlistColumns(インデックスまたは見出し).Rangeが取得したデータは、見出し行と集計行の入った列のデータを取得します。
先のDataBodyRangeを使って（ListColumnオブジェクトのDataBodyRangeプロパティを使って）見出し行と集計行のデータ無しの列データを取得します。
ListObject.ListColumns(インデックスまたは見出し).DataBodyRange（解答例※一部抜粋）
With tbl.DataBodyRange 'DataBodyRangeはタイトル行と集計行をの除くデータ
.Columns(getCol(tbl, "備考")).ClearContents '「tbl」の「備考」列のデータを削除解答例は少し書き方が違います。
こちらについては、LIstObjectオブジェクトが持つ、DataBodyRangeプロパティのColumnsプロパティの指定インデックスを取得しているので、書き方は違いますが、結果は一緒で見出し行と集計行のデータ無しの列データを取得します。
DataBodyRange.columns(インデックス）・・見出し行と集計行を除いた列データ部分の取得
DataBodyRange.Rows(インデックス)・・見出し行と集計行を除いた行データ部分の取得
ListRows（テーブル内の行を表すコレクション）※見出し行・集計行を除く
ListObject.listRows(インデックス)上記の記述は行そのものを表すので、行のデータを取得したい場合は.Rangeを付けます。
ListObject.listRows(インデックス).Range※インデックスは見出しを含まずに上から行数を数えます。
例えば、インデックスに「２」とすると下のように取得できます。
ActiveSheet.Range("A1").ListObject.ListRows(2).Range
テーブル内の行・列を選択抽出する方法
これらを踏まえて、今回の設問に戻ります。
「東京都」の「男」で「35歳以上」の行の備考欄に「対象」と入れる。
上記のような問題の場合、テーブル行を１行ずつ選択して、その行の特定のセルを条件分岐して抽出していくやり方もできます。
その場合には、サイトにも掲載されていますが、For Each 文とForNext文が使えます。
For Each 文の場合
Dim tbl As ListObject
Set tbl = ws.Range("A1").ListObject
Dim tRow As ListRow
For Each tRow In tbl.ListRows
tRow.Range(インデックス）
Next
（模範解答パターン２ ※一部抜粋）
Dim tRow As ListRow
For Each tRow In tbl.ListRows
Select Case True
Case tRow.Range(getCol(tbl, "都道府県")) <> "東京都", _
tRow.Range(getCol(tbl, "性別")) <> "男", _
Year(Date) - Year(tRow.Range(getCol(tbl, "誕生日"))) < 35
Case Else
tRow.Range(getCol(tbl, "備考")) = "対象"
End Select
Next
End Sub
Function getCol(ByVal aTbl As ListObject, aColName As String) As Long
getCol = aTbl.ListColumns(aColName).Index
End FunctionFor Each文を使うことで、1行ずつループするので、特定の行の特定のセルを選択できます。
（例）tRow.Range(２）とすれば、特定行の左から２番目のセル（性別列）を選択できます。
※（前述）ListRowsは見出し行と集計行を除くコレクションです。
For Next 文の場合
Dim tbl As ListObject
Set tbl = ws.Range("A1").ListObject
Dim i As Long
For i = 2 To tbl.Range.Rows.Count
tbl.ListColumns("都道府県").Range(i)
tbl.ListColumns("性別").Range(i)
Next
（模範解答パターン３ ※一部抜粋）
Dim tbl As ListObject
Set tbl = ws.Range("A1").ListObject
Intersect(tbl.DataBodyRange, tbl.ListColumns("備考").Range()).ClearContents
Dim i As Long
For i = 2 To tbl.Range.Rows.Count
Select Case True
Case tbl.ListColumns("都道府県").Range(i) <> "東京都", _
tbl.ListColumns("性別").Range(i) <> "男", _
Year(Date) - Year(tbl.ListColumns("誕生日").Range(i)) < 35
Case Else
tbl.ListColumns("備考").Range(i) = "対象"
End Select
NextFor Next文を使うことで、特定の列の特定のセルを選択できます。
（例）tbl.ListColumns(“都道府県”).Range(２)とすれば、都道府県列の上から数えて２番目のセルを選択できます。
テーブルのオブジェクトとプロパティを使って、自在に特定列や特定行のセルを指定できるやり方を理解すれば、テーブル内の特定条件を抽出することは容易になると思います。
管理人インスタグラムはこちら（フォローお願いします！！）↓
まとめ
今回はテーブルの操作の基本とテーブルにおけるデータ抽出の基本について解説しました。
このサイトはVBAを中心にExcelに関する情報を多数掲載しています。次回もお楽しみに！！
- FileSystemObjectで自動連番付与設定
- フォルダ自動作成とファイル保存（コピーを保存）
- ユーザーフォームを使ったレコード抽出方法－基本－
- ユーザーフォームとコンボボックスの値の取得
- VBAユーザーフォームとコンボボックス
コメント