Excel VBA スタンダードの暗記項目まとめ【第八章テーブルの操作】

6月 22, 202012月 4, 2021Excel VBA スタンダード

この記事では、VBAエキスパートの「Excel VBA スタンダード」の受験用に、暗記すべき項目について、公式の試験範囲の第八章をまとめました。

とくに暗記すべきところは「太文字」や「表」でまとめてあります。とくにステートメントやメソッド、プロパティについては、記述できるレベルまで仕上げておくことをおすすめします。

この記事では暗記を優先するため、解説をできるだけ省いています。学習未経験の人は、Excel VBA スタンダードの公式テキストなどで試験範囲の内容に目を通してからの利用を推奨します。

また、公式テキストに掲載されていないなど、試験に出る可能性の低い内容の解説は、基本的に割愛しています。

Excel VBA スタンダード第八章テーブルの操作（2019年5月改訂で追加された範囲）

8.1 テーブルとは何か（2019年5月改訂で追加）

公式テキストでは、あまり深く解説されていませんが、第八章を理解するためにも、「テーブルとは何か」の概要を把握しておく必要はあります。

テーブルとは、簡単にいえばExcelが管理しているデータベースの領域であり、通常のワークシート領域とは別のものです（テーブルのセルを選択すると、Excelのリボンに「テーブルツール」が表示されます）。

Excelの「テーブルとして書式設定」を使いテーブル化すると、色分けやフィルターが自動で付与されたり、表の最下部に項目を追加した場合も自動で表が拡張されるなどのメリットがあります。

テーブルはVBAでは「ListObjectオブジェクト」として表されます。また、ListObjectのコレクションが、ListObjectsオブジェクトです。

8.2 テーブルの特定（2019年5月改訂で追加）

テーブルのセルから特定する方法

「Range(“テーブルのセル").ListObject」とすることで、テーブルを特定することが可能です。

シートから特定する方法

シートからテーブルを特定する場合は「テーブルのインデックス」か「テーブル名」を利用します。具体的には、以下のような記述になります。

シート.ListObjects(インデックス)
シート.ListObjects(テーブル名)

シートには複数のテーブルがある可能性があるので、オブジェクトは「ListObjects」を使います。

Rangeとテーブル名で特定する方法

「Range(“テーブル名")」でもテーブルを特定することが可能です。

8.3 テーブル部位の特定（2019年5月改訂で追加）

ListObject.Range（タイトルを含むテーブル全体を特定）

テーブル全体は「ListObject.Range」で表されます。

具体的には「Range(“A1").ListObject.Range」とすると、A1のセルを含むテーブル全体を特定することができます。

ListObject.DataBodyRange（タイトルを含まないテーブルを特定する）

テーブルの内、タイトル部分を含まない全体は「ListObject.DataBodyRange」で表します。

具体的には「Range(“A1").ListObject.DataBodyRange」とすると、A1のセルを含むテーブルのタイトルを含まない全体を特定することができます。

ListObject.HeaderRowRange(テーブルのタイトル部分を特定する)

テーブルのタイトル部分は「ListObject.HeaderRowRange」で表します。

具体的には「Range(“A1").ListObject.HeaderRowRange」とすると、A1のセルを含むテーブルのタイトル部分を特定することができます。

ListObject.ListColumn(s)(テーブルの列を特定する)

テーブル内の列は「ListObject.ListColumn」で表します。

具体的には「Range(“A1").ListObject.ListColumn」とすると、A1のセルを含むテーブルの列を選択することができます。

また「ListObject.ListColumn」の集合体は「ListObject.ListColumns」となります。集合体から指定した列を特定する場合は、以下のように記述します（例の記述のみでは実行はできません）。

Range("A1").ListObject.ListColumns(3).Range
Range("A1").ListObject.ListColumns(”年齢").Range

もし、列のタイトルを含まない場合は、「Range」部分を先に紹介した「DataBodyRange」に変えるだけでOKです。

ListObject.ListRow(テーブルの行を特定する)

テーブル内の行は「ListObject.ListRow」で表します。

具体的には「Range(“A1").ListObject.ListRow」とすると、A1のセルを含むテーブルの行を選択することができます。

列の特定と同様に、集合体は「ListObject.ListRows」となります。集合体から指定した行を特定する場合も、列の場合と同様、以下のように記述します。

Range("A1").ListObject.ListRows(3).Range

構造化参照を使った特定方法

Excelが管理するデータベースであるテーブルに対して、セルのアドレスではなく、データベース的に参照する方法が構造化参照です。

構造化参照では「テーブル名[[特殊項目指定子],[列指定子]]」の形で記述します。Excel VBA スタンダードで覚えておくべき内容をまとめると、以下のとおりです。

特殊項目指定子

@：数式と同じ行のセルを指定
[#All]：テーブル全体を指定。
[#Data]：データ行のみを指定（見出し、合計などは含まない）
[#Headers]：見出し行のみを指定
[#Totals]：合計行のみ指定。

特殊項目指定子は、セルに直接記述する場合は[#すべて][#データ][#見出し][#集計]のように日本語で記述する必要がありますが、VBAで利用する場合は英語で記述します。

また、列指定子は[列の名前]で利用します。例えば、下の画像で「年齢」列のデータの平均を求める場合「=AVERAGE(テーブル1[[#データ], [年齢]])」となります。

Excel VBA スタンダード試験対策用に、VBAで構造化参照を利用した特定方法の例を、以下にまとめます。

VBAで構造化参照を利用する特定方法まとめ

見出し含むテーブル全体：Range(“テーブル名[#All]“)
見出しなしのテーブルデータ全体：Range(“テーブル名“)またはRange(“テーブル名[#Data]“)
見出し含むテーブルの列：Range(“テーブル名[#All][列の名前]“)
見出しなしのテーブルの列：Range(“テーブル名[列の名前]“)　または Range(“テーブル名[#Data][列の名前]“)
行の指定：できません（AutoFilterの絞り込みと組み合わせるなどである程度可能）

8.4 テーブル操作の例（2019年5月改訂で追加）

テーブル操作の例については、基本的に7章までのVBAの操作とテーブルの組み合わせです。「実践でどのように利用するか？」を考えて、解答できるように知識を定着させるようにしましょう。

AutoFilterと組み合わせる

オートフィルターと組み合わせた場合、以下のようになります。

'ListObjectを利用した場合
Range("A1").ListObject.Range.AutoFilter 1, "マントヒヒ"
'構造化参照を利用した場合
Range("テーブル1[動物の種類]").AutoFilter 1, "マントヒヒ"

実行後の状態は以下のとおりです（1番目の列に"マントヒヒ"がある行のみ絞り込んでます）。

ListObjectを利用した場合、「Range」を「DataBodyRange」にしても、オートフィルターの絞り込みではタイトル部分が残るため、結果は同じになります。

テーブルの内容をコピーする

オートフィルターで絞り込んだ結果を、「Sheet2」にコピーする場合、以下のようになります。

ListObjectを利用した場合
Range("A1").ListObject.Range.AutoFilter 1, "マントヒヒ"
Range("A1").ListObject.Range.Copy Sheets("Sheet2").Range("A1")

'構造化参照を利用した場合
Range("テーブル1[動物の種類]").AutoFilter 1, "マントヒヒ"
Range("テーブル1[#All]").Copy Sheets("Sheet2").Range("A1")

2～3行目はListObjectを利用した例、6～7行目は構造化参照を利用した例です。

ListObjectの場合、参照先はテーブル全体ですが、オートフィルターの絞り込みで1列目を指定しています。

構造化参照では、最初の参照は「動物の種類」で絞り込んでいますが、コピーの際は「動物の種類」以外もコピーするため、タイトル含む全体を参照しています。

見出しを含まずコピーする

見出し行を含まずコピーしたい場合、ListObjectを利用するなら、先の例の「Range.Copy」を「DataBodyRange.Copy」に置き換え、構造化参照の場合は[#All]を[#Data]に置き換えることで実現可能です。

特定の列をコピーする

先の「テーブルの内容をコピーする」の内容を修正して「名前」列のみコピーした例は以下のとおりです。

'ListObjectを利用した場合
Range("A1").ListObject.Range.AutoFilter 1, "マントヒヒ"
Range("A1").ListObject.ListColumns(2).Range.Copy Sheets("Sheet2").Range("A1")

'構造化参照を利用した場合
Range("テーブル1[動物の種類]").AutoFilter 1, "マントヒヒ"
Range("テーブル1[[#Data],[名前]]").Copy Sheets("Sheet2").Range("A1")

ListObjectを利用した場合は、Rangeの前に「ListColumns(2)」を追加して、コピー元に「名前」列のみを選択しています。

また構造化参照を利用した場合は、コピー元を[#Data]から[[#Data],[名前]]に修正し、データの中から名前列のみをコピー元とするようにしています。

書式を設定する

書式設定は、RangeオブジェクトのFontプロパティで操作が可能です。

特定の列のみ文字を太くする書式を設定する場合は、以下のようになります。

Range("A1").ListObject.ListColumns(2).Range.Font.Bold = True
Range("テーブル1[[#Data],[名前]]").Font.Bold = True

テーブルの行を削除する

テーブルの行を削除する場合、任意のセルを含む行全体を表すEntireRowプロパティで位置を特定したうえで、Deleteメソッドを利用します。

オートフィルターで絞り込んだ行を削除する場合、ListObjectと構造化参照の2パターンは以下のようになります。

Range("A1").ListObject.Range.AutoFilter 1, "マントヒヒ"
Range("A1").ListObject.DataBodyRange.EntireRow.Delete

Range("テーブル1[動物の種類]").AutoFilter 1, "マントヒヒ"
Range("テーブル1[#Data]").EntireRow.Delete

テーブルに列を挿入し、編集する

テーブルに列を挿入する場合、ListColumnsオブジェクトのAddメソッドを利用します。

以下の画像の例に対して、テーブルの右端に列を挿入し、「年齢」の2倍の数値を入れる編集例を紹介します。

実際に編集で利用するコードは以下のとおりです（3パターン）。

'右端の列がわからない場合
Dim RowNum As Integer
Range("A1").ListObject.ListColumns.Add
RowNum = Range("A1").ListObject.ListColumns.Count
Range("A1").ListObject.ListColumns(RowNum).DataBodyRange = "=[@年齢]*2"

'右端の列の見出しが「住処」であることが分かっている場合
Range("A1").ListObject.ListColumns("住処").DataBodyRange.Offset(0, 1) = "=[@年齢]*2"

'右端の列の見出しが「住処」であることが分かっている場合（構造化参照）
Range("テーブル1[[#Data],[住処]]").Offset(0, 1) = "=[@年齢]*2"

実施後の結果は、以下のようになります。