Excel VBAエキスパート体験記23(構造化参照)

前回はExcel VBAエキスパートスタンダードのTable内の操作について勉強しました。
前回までの記事はこちら

今回は8章のテーブル操作の構造化参照についてレポートしていきます。今回で5回目です。

「構造化参照って何？」、「VBAでの新しい操作？」と思われる方もおられると思うのでわかりやすく説明したいと思います。

なお理解が間違っているところもあるかもしれませんがわからない人と同じ視点に立てるのは今しかないので細かいところはご容赦ください。
間違えに気づいたら修正していきます（笑）。

＜今回の目的＞

Table操作の構造化参照について理解する

今回の勉強方法

8章を精読する
構造化参照をわかりやすく説明する

結論
今回躓いた部分
構造化参照とは？
列指定子とは？
特殊指定子とは？
Excelでの使用例
VBAでの使用例
ここでのまとめ

結論

いきなりですが結論です。

構造化参照が分からない場合はまずはエクセルで理解しなおすとよい
構造化参照の記載方法は以下。独特なので形を理解する必要がある

特殊項目指定子と列指定子をまとめると以下になる

次から詳しく説明します。

今回躓いた部分

構造化参照についてはテーブル操作の数式で少し扱った程度だったよくわかっていませんでした。

その為VBA以前に構造化参照をもう一度理解しなおす必要がありました。

その為今回は構造化参照の基本からレポートします。

構造化参照とは？

テーブルと列の組み合わせを構造化参照といいます。テーブル機能が実装された際に作られた機能です。

テーブル名[列指定子]でテーブル内の列データを取得できます。

また[特殊指定子]を使うことでテーブルのデータ以外の取得も可能になります。

列指定子とは？

見出し行と集計行をのぞく列を参照するものです。あくまでデータ処理に特化しているので見出しなどは除外されたものが標準となっています。

見出し行や集計行についての説明はこちら

列指定子=[列名]と列の名称をかぎかっこで囲んだ形で使用します。

テーブル名[列指定子]で列データを取得します。

特殊指定子とは？

見出し行や集計行、テーブル全体を参照するものです。
簡単に言うと列指定子が列に対して、特殊指定子は行やテーブルを指定するものになります。

特殊指定子は列指定子同様鍵括弧で囲んだ形で使用することと、列指定子との区別の為に最初に#を付ける必要があります。以下はExcelでの記載方法です。

特殊指定子	参照先
#すべて	テーブル全体
#データ	データ部分
#見出し	見出し部分
#集計	集計部分
@	数式と同じ行

テーブル名[[#特殊指定子]でテーブルや行のデータを取得できます。

また特殊指定子と列指定子の組み合わせが可能です。
この場合は特殊指定子と列指定子の間をカンマで区切り、全体をさらに鍵括弧でおおいます。

具体亭にはテーブル名[[#特殊指定子],[列指定子]]という形になります。

Excelでの使用例

以下の表に対して各指定子で取得してみます。

なお実際にエクセルにテーブルを作成してみることをお勧めします。理解が早まると思います。

列指定子を使った例（テーブル名[列指定子])

セルでの指定	構造参照での指定
=B4:B12	=テーブル1[名前]
=C4:C12	=テーブル1[日付]
=E4:F12	=テーブル1[[金額]:[個数]]
=E4:G12	=テーブル1[[金額]:[合計]]

特殊指定子を使った例（テーブル名[特殊指定子]）

セルでの指定	構造参照での指定
=B3:G13	=テーブル1[#すべて]
=B4:B12	=テーブル1[#データ]
=B3:G10	=テーブル1[#見出し]
=D2:D10	=テーブル1[#集計]

特殊指定子と列指定子を使った例（テーブル名[[特殊指定子],[列指定子]])

セルでの指定	構造参照での指定
=B3:G12	=テーブル1[[#見出し],[#データ]]
=B3:B13	=テーブル1[[#すべて],[名前]]
=B3:B12	=テーブル1[[#見出し],[#データ],[名前]]
=B3:C12	=テーブル1[[#見出し],[#データ],[名前]:[日付]]

データ以外の部分を取得する場合は特殊指定子が必要です。

データ部分の取得だけなら[#データ]は省略できます。

VBAでの使用例

同様に上の表に対して各指定子で取得します。

VBAでの基本は先ほどの構造化参照をRange(” “)で囲えばOKです。

列指定子を使った例(Range(“テーブル名[列指定子]”))

セルでの指定	構造参照での指定
=Range(“B4:B12”)	=Range(“テーブル1[名前]”)
=Range(“C4:C12”)	=Range(“テーブル1[日付]”)
=Range(“E4:F12”)	=Range(“テーブル1[[金額]:[個数]]”)
=Range(“E4:G12”)	=Range(“テーブル1[[金額]:[合計]]”)

特殊指定子を使った例(Range(“テーブル名[#特殊項目指定子]”))

VBAでの特殊指定子は英語表記になっているので以下のようになります。

また理解を深めるうえでListObjectとの対応表も載せておきます。

エクセルでの特殊指定子	VBAでの特殊指定子	ListObjectでの表記	参照先
#すべて	#All	Range	テーブル全体
#データ	#Data	DataBodyRange	データ部分
#見出し	#Header	HeaderRowRange	見出し部分
#集計	#Totals	TotalsRowRange	集計部分
@	@	ListRows()	数式と同じ行

セルでの指定	構造参照での指定
=Range(“B3:G13”)	=Range(“テーブル1[#All]”)
=Range(“B4:B12”)	=Range(“テーブル1[#Data]”)
=Range(“B3:G10”)	=Range(“テーブル1[#Header]”)
=Range(“D2:D10”)	=Range(“テーブル1[#Totals]”)

特殊指定子と列指定子を使った例（Range(“テーブル名[[特殊指定子],[列指定子]]”))

セルでの指定	構造参照での指定
=Range(“B3:G12”)	=Range(“テーブル1[[#Header],[#Data]]”)
=Range(“B3:B13”)	=Range(“テーブル1[[#すべて],[名前]]”)
=Range(“B3:B12”)	=Range(“テーブル1[[#Header],[#Data],[名前]]”)
=Range(“B3:C12”)	=Range(“テーブル1[[#Header],[#Data],[名前]:[日付]]”)