別シートでもOK！テーブル参照の数式と特徴を解説【Excel構造化参照】
はじめに
本記事では、Excel（エクセル）の 構造化参照（テーブル参照） の数式の使い方と特徴を解説します。
【構造化参照（テーブル参照）】
-
数式の基本形： テーブル名[ [特殊項目指定子], [列指定子] ]
-
よく使う形： テーブル名[列指定子]
例) テーブル１[X]： 列名"X"の全列データを取得 -
「自動拡張する範囲指定」をしたい場合に便利。
-
ただし、無理にセル参照から置き換える必要はない。
構造化参照とは
構造化参照 は、数式内で テーブル内のデータを参照 する方法の一つです。
- セル参照： セル番地 を指定。
（例：A1:A5） - 構造化参照： テーブル名 と 列名 を指定。
（例：テーブル1[A列]）
構造化参照の使用手順
テーブルを準備
構造化参照を利用するには、参照元となるデータがテーブル形式である必要があります。
- テーブルの作成：
テーブルにしたいセル範囲を選択し、「挿入」タブの「テーブル」ボタンを選択後、ダイアログでOKを押します。
- テーブル名の設定（任意）：
「テーブルデザイン」タブの「テーブル名」欄に、識別しやすいテーブル名を入力します（例：商品リスト）。
- 必要に応じて集計行を表示設定（任意）：
テーブルの最終行に「集計行」を追加する場合は、「テーブルデザイン」タブで「集計行」にチェックを入れます。
構造化参照の数式の詳細
数式の構文規則
構造化参照の数式は、以下の要素で構成されます。
-
テーブル名:
参照するテーブルの名前を指定。 参照先と参照元が同じテーブルの場合、省略可能。 -
テーブル指定子 :
テーブル内の特定の範囲を指定。 特殊項目指定子 と 列指定子 から構成され、どちらか一方のみの指定も可能 （その場合、角括弧とカンマを省ける）。 「特殊項目指定子が#データ」で「列指定子なし」の場合、テーブル指定子自体を省略可能。 - 特殊項目指定子:
テーブル内の特定の行（#見出し、#データ、#集計など）を指定（詳細は後述）。 - 列指定子:
テーブル内の列名を指定。
- 特殊項目指定子:
数式の特殊項目指定子
テーブル内で参照する 行 を指定する際に使用する指定子です。
- #すべて: テーブル全体を指す。
- #データ: データ行を指す（特殊項目指定子を省略した場合と同等で、使用頻度は低め）。
- #見出し: 見出し行を指す。
- #集計: 集計行を指す（集計行が非表示の場合、#REF!エラーとなる）。
- @ : 自身と同じ行を指す。
- テーブル指定子内の角括弧とカンマは不要。
"@" を使った構造化参照式の例
- 「自身と同じ 行全体 」を指定する例：
テーブル名[ @ ] - 「自身と同じ 行の特定の列 （列名に特殊文字を 含まない含まない ）」を指定する例：
テーブル名[ @見出し名 ] - 「自身と同じ 行の特定の列 （列名に特殊文字を 含む含む ）」を指定する例：
テーブル名[ @[見出し 名] ]（角括弧で見出し名を囲む）
別シート参照時の数式
別のシートにあるテーブルを参照する場合、
- 同一ブックの場合 :
数式は変化しない。
同じブック内であれば、テーブル名を指定するだけでどのシートからでも参照可能。 - 別ブックの場合 :
数式の先頭に'ブック名.xlsx'!を追加する
（例：='ブック名.xlsx'!テーブル名[見出し名]）。
外部参照での数式の使用は非推奨
外部参照（別のブックの参照）を行う場合、
セル参照 か 構造化参照 かに関わらず、数式の使用はあまりオススメしません。
参照元のブックを開かずにデータを更新でき、リンク切れも起こりにくい Power Query によるテーブル参照の利用が良いでしょう。
構造化参照の特徴
✅ メリット
-
参照範囲が自動で拡張される :
参照元のテーブルにデータを追加しても、参照側の範囲が自動で広がるため、数式を修正する手間が省けます。 セル位置に依存しない数式 を作成するときに役立ちます。 -
可読性が向上する :
「テーブル名」と「見出し名」を記述して参照場所を指定するため、数式の可読性が向上します。 セル参照と比較して、数式を見ただけで何を参照しているか判別しやすくなります。
外部参照（別ブックの参照） に限って ※、
（※ 外部参照でない場合は、
ただし前述の通り、数式の使用には欠点があります。
❌ デメリット
-
マウス操作で参照場所を変更できない :
セル参照では数式編集中に参照位置や範囲をマウスのドラッグで変更できますが、構造化参照ではできません。 -
参照場所の固定・移動の切り替えが苦手 :
数式を別のセルに複製する際、参照位置を「（ 絶対参照 のように）固定するか」「（ 相対参照 のように）移動するか」を細かく設定するのが苦手です。
参照場所の固定 / 移動の使い分け方法
構造化参照で絶対参照 / 相対参照 のような挙動を切り替える方法です。
列と行で、それぞれ対応が異なります。
【 列 の固定 / 移動】
数式の複製方法によって挙動が変わります。
-
固定：
コピペ（コピー＆ペースト） を使う。 -
移動：
オートフィル を使う。- ※ 単一列を 範囲指定 （A:Aなど）すると、 オートフィル でも 固定 可能。
【 行 の固定 / 移動】
関数を使用して対応できます。
場所指定の基準を変更することで、固定と移動を使い分けます。
- 固定：
INDEX関数で、 先頭行からの位置 を指定する。- 例) INDEX([X],1) ： X列の 1行目 のセル。
- 移動：
OFFSET関数で、 自身の行を基準とした位置 を指定する。- 例) OFFSET([@X],-1,) ： X列の 「自身の行」から1つ上 のセル。
構造化参照は、細かな参照位置の指定が煩雑です。
使いどころ
構造化参照は、
例:
=SUM(テーブル1[X]) → 列Xにデータを追加しても、自動で参照範囲が拡張される）
「自動拡張」機能が不要な場合は、 無理に構造化参照を使用する必要はなく 、
基本的には、セル選択（カーソル移動やクリック）した際に
関数内での使用例
関数の中で構造化参照を使用する例をいくつか紹介します。
- VLOOKUP : テーブル内を検索し、該当するデータを取得。
- データ追加時に検索範囲が自動で拡張される。
- 例） =VLOOKUP( 検索値, テーブル1, 2, FALSE )
- ただし、以下のデメリットがある。
- テーブルの列を入れ替えると、取得列がずれる
- 検索列はテーブルの左端に限定される
列の入れ替えや検索列の制限に対応する方法
テーブルの列の入れ替えに対応し、検索列がテーブルの左端に限定されないようにするには、
XLOOKUP または MATCH + INDEX 関数を使用します。
- 例） =XLOOKUP( 検索値, テーブル1[A], テーブル1[B] )
- 例） =INDEX( テーブル1[B], MATCH( 検索値, テーブル1[A], 0 ) )
XLOOKUPはExcel 2019以降で利用可能です。
- SUM, AVERAGE, MAX, MINなど : 集計値の算出を行う。
- データ追加時に計算範囲が自動で拡張される。
- 例） =SUM( テーブル1[A] )
- INDEX : 指定範囲 内の内の n番目のセルを取得する。
- （詳細は前述の「参照場所の固定 / 移動の使い分け方法」を参照）
- 例) =INDEX( [X], 1 ) → 列Xの1行目のセルを参照
- OFFSET : 指定範囲 からのからの ±n番目のセルを取得する。
- （詳細は前述の「参照場所の固定 / 移動の使い分け方法」を参照）
- 例) =OFFSET( [@X], -1, ) → 列Xの「自身の行」から1つ上のセルを参照。
- INDIRECT : ドロップダウンリストや条件付き書式などで、構造化参照を使用可能にする。
- 構造化参照の数式を直接使用できない場面 で対応する方法。
- 例) =INDIRECT( "テーブル1[X]" ) → テーブル1の列Xのリストを参照。
ドロップダウンリスト（入力規則） や 条件付き書式 では、
特に、別シートのテーブルを参照する際にINDIRECT関数を使用しないと、参照範囲が自動で拡張されない点に注意が必要です。
まとめ
本記事では、Excelの構造化参照について、基本的な使い方から応用、メリット・デメリットまでを解説しました。
構造化参照のポイントは以下の通りです。
- 基本的な数式 :
テーブル名[ [特殊項目指定子], [列指定子] ] の形式で記述。
最もよく使う形は テーブル名[列指定子] で、指定した列（例: テーブル１[X] で列 X）のデータが取得可能。 - 自動拡張の利便性 :
構造化参照は、テーブルにデータが追加されても参照範囲が自動的に拡張されるため、
手動で数式を修正する手間を省ける。データ更新が多い場面で特に有用。 - セル参照との使い分け :
既存のセル参照を 無理に構造化参照に置き換える必要はない 。
自動拡張の機能が不要な場合や、細かな参照位置の調整が必要な場合は、セル参照も有効な選択肢。
構造化参照が使えるようになると、テーブル内のデータをより柔軟かつ効率的に扱えるようになります。