Excelのテーブルに構造化参照でアクセスする

個人的にExcelで使いこなせると便利だなと感じる機能の一つとしてテーブルがあります。

Excelで指定した範囲をテーブルに変換すると、Excel側でいろいろといい感じにしてくれる機能となります。

そんなテーブルのデータ参照方法として、構造化参照と呼ばれる書き方があります。

テーブルへのアクセス方法として、いままで数式の入力時にオートコンプリートされる内容から、なんとなく理解して利用していましたが。

ふとした事から、これが構造化参照という呼び名だった事をしり。
そこからMSドキュメントを読んで、理解した内容を自分用のまとめとして投稿にします。
（2022年11月現在、ドキュメントは日本語への翻訳の関係からか、そのままだとできない箇所があるので補足しつつ）

## ドキュメント

[Excel テーブルでの構造化参照の使い方](https://support.microsoft.com/ja-jp/office/excel-%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%A7%E3%81%AE%E6%A7%8B%E9%80%A0%E5%8C%96%E5%8F%82%E7%85%A7%E3%81%AE%E4%BD%BF%E3%81%84%E6%96%B9-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)

以下は今回の話で前提となるExcelのテーブルについて。

[Excel のテーブルの概要](https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E6%A6%82%E8%A6%81-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c)

[表を作成する](https://support.microsoft.com/ja-jp/office/excel-%E3%81%A7%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-bf0ce08b-d012-42ec-8ecf-a2259c9faf3f)

## 今回参照するドキュメントについて

[Excel テーブルでの構造化参照の使い方](https://support.microsoft.com/ja-jp/office/excel-%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%A7%E3%81%AE%E6%A7%8B%E9%80%A0%E5%8C%96%E5%8F%82%E7%85%A7%E3%81%AE%E4%BD%BF%E3%81%84%E6%96%B9-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)

本記事では、上記ドキュメントに書いている内容を実施します。

下記テーブルを作成、操作して参照化構造を理解していきます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/0f74f8b3-a4b9-bde2-a8d9-1cde3c4b9668.png)

## テーブルの作成

まずはドキュメントに記載されている内容で表を作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/a1a35d31-93ce-e757-03a8-6e5da351af3b.png)

入力したデータ範囲内の任意のセルを選択して `CTRL+T`を入力

テーブルの作成ポップアップがでてきます。
今回作成した表の範囲（`$A$1:$E$7`）が自動で選択されている事がわかります。

`先頭行をテーブルの見出しとして使用する`　にチェックをいれてOKを選択

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/1c3638fd-33e6-7316-90e8-c6c222f56450.png)

テーブルが作成され、指定した範囲にスタイルが適用されて色がつきました。

また作成したテーブル上にカーソル選択がされている時は、リボンにテーブルデザインという項目が追加されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/05ff291e-a525-d251-28fc-e73d8ce8a2d5.png)

`E2`セルに`=`を入力してから`C2`セルをクリックすると`=[@売上金額]`という数式が入力されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/d1dc46e5-1d66-3fcc-deb7-05180a252237.png)

`=C2`ではなく`=[@売上金額]`と記載されていますが、これが構造化参照と呼ばれるものになります。

続けて `*`　を入力したあとに、`D2`セルを選択すると

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/f7777f80-86f2-72a5-dbbd-11798654c845.png)

`=[@売上金額]*[@[手数料 (%)]]`と数式が入力され、`売上金額`　＊ `手数料（%)` という数式ができあがります。

`Enter`で入力を確定すると列全体に数式がコピーされて計算されます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/b55e2e30-cf9d-21c9-f544-02c1d01087f5.png)

後続化参照では通常の`A1`といった形式とは異なり、記法の詳細については後述します。

## テーブル名の変更

テーブルを作成すると自動でテーブル名が割り振られます。（テーブル1、テーブル2、テーブル3、、、）

テーブル名は、テーブルデザインのテーブル名から確認できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/66da46fd-4b3e-b919-8635-28867c1464ec.png)

またここでテーブル名を書き換えることで、テーブル名を変更できます。
今回はドキュメントと揃えて、`テーブル1`から`DeptSales`に変更します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/e44795f1-6348-df8e-aa67-b756719470bf.png)

他の変更方法としては`CTRL+F3`で表示できる`名前の管理`からも変更する事ができます。
個人的にはこちらから変更する方が多いです。（好みの問題）

`名前の管理`でテーブルを選択して、編集からテーブル名を変更します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/d596fca7-aef1-0815-1618-bae98c3a70dc.png)

## 特殊項目指定子を確認してみる

後続化参照ではテーブルの見出し、データ部、集計業にアクセスするために特殊項目指定子と呼ばれる要素を利用します。

ドキュメントだと`#All`,`#Data`,`#Headers`,`##Totals`となっていますが、日本語環境のエクセルだとこのままだと利用できないので下記のように読み替えます。

|特殊項目指定子|例|参照先|
|---|---|---|
|#すべて|`=DeptSales[#すべて]`|見出し、データ、および集計を含む、テーブル全体|
|#データ|`=DeptSales[#データ]`|データ行のみ|
|#見出し|`=DeptSales[#見出し]`|見出し行のみ|
|#集計|`=DeptSales[#集計]`|集計行のみ|
|@|`=DeptSales[@]`|現在の行|

ここではセルに上記例となる構造化参照を入力してテーブルにアクセスしてみます。

### #すべて

`A11`セルに`=DeptSales[#すべて]`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/3fa0dcea-5671-7728-5410-aef4a9494d04.png)

### #データ

`A11`セルに`=DeptSales[#データ]`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/ff5cf6f6-2d94-98d2-d2e6-8a4078271c7a.png)

### #見出し

`A11`セルに`=DeptSales[#見出し]`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/04e0a77f-2653-04bd-7f0a-6122f4a0bb32.png)

### #集計

`A11`セルに`=DeptSales[#集計]`

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/ebf4ac88-8fa7-7c2e-5fc0-24b60885630e.png)

集計だけは別の要素とちがい、`#REF`となりました。
これは対象テーブルで集計が有効になっていないため、テーブルの集計を有効にする必要があります。

`テーブルデザイン`から集計を有効にします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/695baf2b-5841-8dda-9dd0-0447cbfdd0f1.png)

有効になると`#REF`とならずに参照できます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/ed4ec0f0-2c7c-5f99-6e8b-03efc2f1dbec.png)

### @（現在の行）

`G2`セルに`=DeptSales[@]`とする事で数式が入力されている現在の行にに対応するテーブルの行が取得できます。

画像ではG2セルに数式を入力しているので2行明のテーブル内容が取得されています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/6386f480-07a0-3c41-8252-95578b59701f.png)

なお現在の行の項目を取得したい場合は下記のように記述します。

`=DeptSales[@地域]`
`=DeptSales[@[営業 担当者]]`

地域を指定するときと、`営業 担当者`を指定する場合、[]の個数が違いますが。
特殊文字を含む列見出しの場合は`[]`で囲む必要があり、`営業 担当者`のケースではスペースが含まれるため外側を`[]`で囲む必要があります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/08cf9e89-e231-2dfb-3e71-45d3c6a4a6cf.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/2a02c759-a502-7772-f7e2-be65997d7b82.png)

## 参照演算子を確認する

範囲を参照する場合は構造化参照では下記のようになっています。

|記述|例|説明|
|---|---|---|
|`テーブル名[[項目A]:[項目D]]`|`=DeptSales[[営業 担当者]:[手数料 (%)]]`| 項目Aから項目Bの範囲を指定|
またテーブル名
|`テーブル名[項目A],テーブル名[項目B] |`=DeptSales[売上金額],DeptSales[手数料金額]`|項目A,項目Bを選択|

### =DeptSales[[営業 担当者]:[手数料 (%)]]

`G2セル`に入力すると`営業 担当者`から `手数料 (%)`までの範囲が選択されている事がわかります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/62696b0c-5ab4-ea88-b430-202e46e225d3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/847b5639-b996-7bbc-19e5-94b2d2e19876.png)

### =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`になることがわかります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/ed59bfab-850a-3629-b806-036cce38a3ff.png)

分解してみてみると　これは売上金額の合計 `3970` と 手数料金額を `570.2` を　SUMで集計して `4540.2` になっています。

 - `=DeptSales[[#すべて],[売上金額]]` 
 - `=DeptSales[[#データ],[手数料金額]]` 

をそれぞれ別のセルに入力して確認すると理解しやすいかと思います。

`H3セル`に`=DeptSales[[#すべて],[売上金額]]` を入力

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/020c69ac-a418-f10f-577c-ca9178903dc3.png)

`I3`セルに`=DeptSales[[#データ],[手数料金額]]` 

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/2a893917-6b23-3b68-4900-d0d3c8baf597.png)

ちなみにドキュメントでは`売上金額` と`手数料金額`の合計を算出するために、該当項目を足し合わせていますが。

なぜか特殊項目指定子を売上金額側では`#すべて`、手数料金額側は`#データ`を指定しています。

`#すべて`は集計行も含みますが、`#データ`は集計業を含まないといった違いがあるため、意識して使わないと集計ミスにつながるので気をつけたいポイントとなります。

この状態の何が気をつけるポイントかというと`C8`セルで売上金額の集計もするように設定すると

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/33894a20-e52c-6f97-ec08-324721e60ffa.png)

売上金額は `#すべて` としているため、集計行の`3970`まで含むようになり,`H1`セルの集計結果も `4540.2`から `8510.2` に変化します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1670622/90b4f2de-1e76-6111-82d6-769053a1f9fa.png)

## 総評

いままで　オートコンプリート　で表示される内容からなんとなくで理解して利用していましたが。
ドキュメントを読んで、色々と再確認になりました。