個人的にExcelで使いこなせると便利だなと感じる機能の一つとしてテーブルがあります。
Excelで指定した範囲をテーブルに変換すると、Excel側でいろいろといい感じにしてくれる機能となります。
そんなテーブルのデータ参照方法として、構造化参照と呼ばれる書き方があります。
テーブルへのアクセス方法として、いままで数式の入力時にオートコンプリートされる内容から、なんとなく理解して利用していましたが。
ふとした事から、これが構造化参照という呼び名だった事をしり。
そこからMSドキュメントを読んで、理解した内容を自分用のまとめとして投稿にします。
（2022年11月現在、ドキュメントは日本語への翻訳の関係からか、そのままだとできない箇所があるので補足しつつ）
ドキュメント
以下は今回の話で前提となるExcelのテーブルについて。
今回参照するドキュメントについて
本記事では、上記ドキュメントに書いている内容を実施します。
下記テーブルを作成、操作して参照化構造を理解していきます。
テーブルの作成
まずはドキュメントに記載されている内容で表を作成します。
入力したデータ範囲内の任意のセルを選択して CTRL+Tを入力
テーブルの作成ポップアップがでてきます。
今回作成した表の範囲（$A$1:$E$7）が自動で選択されている事がわかります。
先頭行をテーブルの見出しとして使用する にチェックをいれてOKを選択
テーブルが作成され、指定した範囲にスタイルが適用されて色がつきました。
また作成したテーブル上にカーソル選択がされている時は、リボンにテーブルデザインという項目が追加されます。
E2セルに=を入力してからC2セルをクリックすると=[@売上金額]という数式が入力されます。
=C2ではなく=[@売上金額]と記載されていますが、これが構造化参照と呼ばれるものになります。
続けて * を入力したあとに、D2セルを選択すると
=[@売上金額]*[@[手数料 (%)]]と数式が入力され、売上金額 ＊ 手数料（%) という数式ができあがります。
Enterで入力を確定すると列全体に数式がコピーされて計算されます。
構造化参照では通常のA1といった形式とは異なり、記法の詳細については後述します。
テーブル名の変更
テーブルを作成すると自動でテーブル名が割り振られます。（テーブル1、テーブル2、テーブル3、、、）
テーブル名は、テーブルデザインのテーブル名から確認できます。
またここでテーブル名を書き換えることで、テーブル名を変更できます。
今回はドキュメントと揃えて、テーブル1からDeptSalesに変更します。
他の変更方法としてはCTRL+F3で表示できる名前の管理からも変更する事ができます。
個人的にはこちらから変更する方が多いです。（好みの問題）
名前の管理でテーブルを選択して、編集からテーブル名を変更します。
特殊項目指定子を確認してみる
構造化参照ではテーブルの見出し、データ部、集計業にアクセスするために特殊項目指定子と呼ばれる要素を利用します。
ドキュメントだと#All,#Data,#Headers,##Totalsとなっていますが、日本語環境のエクセルだとこのままだと利用できないので下記のように読み替えます。
| 特殊項目指定子 | 例 | 参照先 |
|---|---|---|
| #すべて | =DeptSales[#すべて] |
見出し、データ、および集計を含む、テーブル全体 |
| #データ | =DeptSales[#データ] |
データ行のみ |
| #見出し | =DeptSales[#見出し] |
見出し行のみ |
| #集計 | =DeptSales[#集計] |
集計行のみ |
| @ | =DeptSales[@] |
現在の行 |
ここではセルに上記例となる構造化参照を入力してテーブルにアクセスしてみます。
#すべて
A11セルに=DeptSales[#すべて]
#データ
A11セルに=DeptSales[#データ]
#見出し
A11セルに=DeptSales[#見出し]
#集計
A11セルに=DeptSales[#集計]
集計だけは別の要素とちがい、#REFとなりました。
これは対象テーブルで集計が有効になっていないため、テーブルの集計を有効にする必要があります。
テーブルデザインから集計を有効にします。
有効になると#REFとならずに参照できます。
@（現在の行）
G2セルに=DeptSales[@]とする事で数式が入力されている現在の行にに対応するテーブルの行が取得できます。
画像ではG2セルに数式を入力しているので2行明のテーブル内容が取得されています。
なお現在の行の項目を取得したい場合は下記のように記述します。
=DeptSales[@地域]
=DeptSales[@[営業 担当者]]
地域を指定するときと、営業 担当者を指定する場合、[]の個数が違いますが。
特殊文字を含む列見出しの場合は[]で囲む必要があり、営業 担当者のケースではスペースが含まれるため外側を[]で囲む必要があります。
参照演算子を確認する
範囲を参照する場合は構造化参照では下記のようになっています。
| 記述 | 例 | 説明 |
|---|---|---|
テーブル名[[項目A]:[項目D]] |
=DeptSales[[営業 担当者]:[手数料 (%)]] |
項目Aから項目Bの範囲を指定 |
| またテーブル名 | ||
| `テーブル名[項目A],テーブル名[項目B] | =DeptSales[売上金額],DeptSales[手数料金額] |
項目A,項目Bを選択 |
=DeptSales[[営業 担当者]:[手数料 (%)]]
G2セルに入力すると営業 担当者から 手数料 (%)までの範囲が選択されている事がわかります。
=DeptSales[売上金額],DeptSales[手数料金額]
G2セルに=DeptSales[売上金額],DeptSales[手数料金額] と入力すると、売上金額（C2:C7） と 手数料金額（E2:E7） の範囲が選択されている事がわかります。
（Enterで確定してしまうと計算できなくて#VALUEになります。）
構造化参照の構文規則を確認してみる
ドキュメントでは下記の数式が紹介されていますが、これは利用できません。
=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])
このドキュメントが元々英語で翻訳されて日本語になっているからかと思いますが。
項目名は下記のように翻訳されているので、数式もドキュメントで記載されている内容からは変更してあげる必要があります。
Sales Person <==> 営業 担当者
Region <==> 地域
Sales Amount <==> 売上金額
% Commission <==> 手数料 (%)
Commission Amount <==> 手数料金額
また構造化参照の特殊項目指定子と呼ばれる、合計行やデータ全体などを参照するための識別子が日本語環境だと翻訳した形で入力する必要があるのでこの部分もドキュメントから変更する必要があります。
-
[#Totals]は[#すべて] -
[#Data]は[#データ]
全部加味すると下記のようになります。
=SUM(DeptSales[[#すべて],[売上金額]],DeptSales[[#データ],[手数料金額]])
H1セルに入力して計算してみると 4540.2になることがわかります。
分解してみてみると これは売上金額の合計 3970 と 手数料金額を 570.2 を SUMで集計して 4540.2 になっています。
=DeptSales[[#すべて],[売上金額]]=DeptSales[[#データ],[手数料金額]]
をそれぞれ別のセルに入力して確認すると理解しやすいかと思います。
H3セルに=DeptSales[[#すべて],[売上金額]] を入力
I3セルに=DeptSales[[#データ],[手数料金額]]
ちなみにドキュメントでは売上金額 と手数料金額の合計を算出するために、該当項目を足し合わせていますが。
なぜか特殊項目指定子を売上金額側では#すべて、手数料金額側は#データを指定しています。
#すべては集計行も含みますが、#データは集計業を含まないといった違いがあるため、意識して使わないと集計ミスにつながるので気をつけたいポイントとなります。
この状態の何が気をつけるポイントかというとC8セルで売上金額の集計もするように設定すると
売上金額は #すべて としているため、集計行の3970まで含むようになり,H1セルの集計結果も 4540.2から 8510.2 に変化します。
総評
いままで オートコンプリート で表示される内容からなんとなくで理解して利用していましたが。
ドキュメントを読んで、色々と再確認になりました。