VBAでのテーブル操作の基本

Excel
スポンサーリンク

VBAでのテーブル操作について、VBA100本ノック53本目の問題を題材にして解説したいと思います。

主に以下の2つについて解説します。

  1. テーブル内の各部分の選択方法
  2. テーブル内のデータを検索・抽出する方法

いずれも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

では、模範解答に沿ってテーブルの基本的なことを解説します。

テーブルとは?

表の任意の場所を選択してリボンの「挿入」→テーブルグループ:「テーブル」ボタンで通常の表をテーブル化することができます。

テーブル化することで表のデータの追加(行・列の追加)や集計が容易になり、機能的に表を管理できます。

以下の過去記事で詳しく解説しています↓↓

Excel テーブル機能①~基本的な使い方~
Excelのテーブル機能の基本的な使い方についてまとめています。

テーブルの選択方法-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コレクションに(インデックス)で指定します。インデックスは作られた順番に振られます。(最初に作ったテーブルが1,次に作ったテーブルが2、次が3・・・)

ワークシートオブジェクト.ListObjects(”テーブル名”)

テーブル名

テーブルを作成するとテーブル名が付けられます。こちらでテーブルを選択します。

範囲からテーブルへの変換方法

前述にもありますが、範囲のテーブル化は、ワークシート上でのボタン操作は、表の任意の場所を選択してリボンの「挿入」→テーブルグループ:「テーブル」ボタンで通常の表をテーブル化することができます。

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入門
テーブルをVBAで操作する場合に使用するオブジェクトの概要説明です、テーブルは、セルの範囲を表(テーブル)に変換することで、関連するデータの管理と分析を容易にできるようになるエクセルの機能で、以前はリストと呼ばれていました。テーブルを作成して書式設定することで、データを視覚的に分析しやすくできます。

テーブル内のそれぞれの部分の指定方法

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

※インデックスは見出しを含まずに上から行数を数えます。

例えば、インデックスに「2」とすると下のように取得できます。

ActiveSheet.Range("A1").ListObject.ListRows(2).Range

テーブル内の行・列を選択抽出する方法

これらを踏まえて、今回の設問に戻ります。

「東京都」の「男」で「35歳以上」の行の備考欄に「対象」と入れる

上記のような問題の場合、テーブル行を1行ずつ選択して、その行の特定のセルを条件分岐して抽出していくやり方もできます。
その場合には、サイトにも掲載されていますが、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
(模範解答パターン2 ※一部抜粋)
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 Function

For Each文を使うことで、1行ずつループするので、特定の行の特定のセルを選択できます。

(例)tRow.Range(2)とすれば、特定行の左から2番目のセル(性別列)を選択できます。

※(前述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
(模範解答パターン3 ※一部抜粋)
  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
  Next

For Next文を使うことで、特定の列の特定のセルを選択できます。

(例)tbl.ListColumns(“都道府県”).Range(2)とすれば、都道府県列の上から数えて2番目のセルを選択できます。

テーブルのオブジェクトとプロパティを使って、自在に特定列や特定行のセルを指定できるやり方を理解すれば、テーブル内の特定条件を抽出することは容易になると思います。

まとめ

今回はテーブルの操作の基本とテーブルにおけるデータ抽出の基本について解説しました。

このサイトはVBAを中心にExcelに関する情報を多数掲載しています。次回もお楽しみに!!


コメント

タイトルとURLをコピーしました