別シートでもOK！テーブル参照の数式と特徴を解説【Excel構造化参照】

本記事では、Excel（エクセル）の 構造化参照（テーブル参照） の数式の使い方と特徴を解説します。具体的には、構造化参照の数式の書き方や、どのような場面で活用すべきかについて述べます。

構造化参照 は、数式内で テーブル内のデータを参照 する方法の一つです。通常の セル参照 とは異なる記述をします。

構造化参照の数式は、以下の要素で構成されます。

• テーブル名 ：参照するテーブルの名前を指定。参照先と参照元が同じテーブルの場合、省略可能。

• テーブル指定子 ：テーブル内の特定の範囲を指定。特殊項目指定子 と 列指定子 から構成され、どちらか一方のみの指定も可能（その場合、角括弧とカンマを省ける）。「特殊項目指定子が#データ」で「列指定子なし」の場合、テーブル指定子自体を省略可能。

  • 特殊項目指定子 ：テーブル内の特定の行（#見出し、#データ、#集計など）を指定（詳細は後述）。
  • 列指定子 ：テーブル内の列名を指定。

テーブル内で参照する 行 を指定するための指定子です。

別のシートにあるテーブルを参照する場合、参照先のシートが別のブック（ファイル）にあるときは、数式にファイル名を記述する必要があります。同じ同じ ブック内であれば、参照するテーブルが どのシートにあっても数式は変わりません。

構造化参照には、セル参照にはない独自のメリットがある一方、苦手な操作もあります。用途に応じて使い分けることが重要です。

• 参照範囲が自動で拡張される ：参照元のテーブルにデータを追加しても、参照側の範囲が自動で広がるため、数式を修正する手間が省けます。セル位置に依存しない数式を作成するときに役立ちます。

• 可読性が向上する ：「テーブル名」と「見出し名」を記述して参照場所を指定するため、数式の可読性が向上します。セル参照と比較して、数式を見ただけで何を参照しているか判別しやすくなります。

• マウス操作で参照場所を変更できない ：セル参照では数式編集中に 参照位置や範囲をマウスのドラッグで変更 できますが、構造化参照ではできません。

• 参照場所の固定・移動の切り替えが苦手 ：数式を別のセルに複製する際、参照位置を「（ 絶対参照 のように）固定するか」「（ 相対参照 のように）移動するか」を細かく設定するのが苦手です。

構造化参照は、「データを追加しても、参照範囲を自動で拡張させたい」場合に 便利です（詳細は前述のメリットの項目を参照）。

「自動拡張」機能が不要な場合は、 無理に構造化参照を使用する必要はなく 、セル参照を使用しても問題となることは少ないでしょう。

構造化参照では「テーブルの何行目か」を直接数字で指定することはできません。関数を組み合わせて、目的別に以下の方法を使い分けます。

列の固定と行の固定では、それぞれ方法が異なります。

列列 を固定する方法には以下の2つがあります。

行行 を固定する場合は、目的に応じて以下の関数・記法を使い分けます。

条件付き書式 や 入力規則 では 仕様上、構造化参照を直接使用できません。しかし、名前の定義 か INDIRECT関数 を経由することで使用可能になります。

対処法は以下の2つです。

条件付き書式や入力規則で構造化参照を使う方法の1つが、「名前の定義」の機能を経由することです。数式タブの 名前の定義 で、テーブル列に名前を付けて列の範囲を定義し、使用します。

条件付き書式や入力規則で構造化参照を使う2つ目の方法は、「INDIRECT関数」を使う ことです。

名前の定義とINDIRECT関数のどちらを使っても、条件付き書式や入力規則で構造化参照を使えるようになりますが、数式の可読性やパフォーマンスの面から、基本は 名前の定義 がオススメです。参照する列が多い場合や手軽に試したい場合は INDIRECT関数 を使いましょう。

関数の中で構造化参照を使用する例をいくつか紹介します。

• VLOOKUP ： テーブル内を検索し、該当するデータを取得。
  • データ追加時に検索範囲が自動で拡張される。
  • 例） =VLOOKUP( 検索値, テーブル1, 2, FALSE )
  • ただし、以下のデメリットがある。
    • テーブルの列を入れ替えると、取得列がずれる
    • 検索列はテーブルの左端に限定される

• SUM, AVERAGE, MAX, MINなど ： 集計値の算出を行う。
  • データ追加時に計算範囲が自動で拡張される。
  • 例） =SUM( テーブル1[A] )
• INDEX ： 指定範囲 内の内の n番目のセルを取得する。
  • （詳細は前述の「行の固定 / 移動」を参照）
  • 例) =INDEX( [列X], 1 ) → 列Xの1行目のセルを参照
• OFFSET ： 指定範囲 からのからの ±n番目のセルを取得する。
  • （詳細は前述の「行の固定 / 移動」を参照）
  • 例) =OFFSET( [@列X], -1, ) → 列Xの「自身の行」から1つ上のセルを参照。
• INDIRECT ： ドロップダウンリストや条件付き書式などで、構造化参照を使用可能にする。
  • 構造化参照の数式を直接使用できない場面 で対応する方法（詳細は応用1を参照）。
  • 例) =INDIRECT( "テーブル1[列X]" ) → テーブル1の列Xのリストを参照。

構造化参照を使う際に、つまずきやすいポイントをまとめました。構造化参照（テーブル参照）がうまく機能しない、またはエラーが出る場合は、以下の表から症状に合うものを確認してください。

Excelの設定で「数式でテーブル名を使用する」が無効になっていると、セル選択やオートコンプリートによる構造化参照の自動補完が効きません。

セル範囲が「テーブル」として設定されていない（標準の範囲のまま）場合、構造化参照は使えません。

「データの入力規則」のドロップダウンリスト設定欄や「条件付き書式」の数式欄では、構造化参照（テーブル1[列名]）を 直接入力することはできません。

列名にカンマや大括弧などの特殊文字が含まれる場合、角括弧（[ ]）が必要になるケースがあります。列名に含まれる大括弧の前には、シングルクォーテーション（'）でのエスケープも必要です。

上記の例のように、同テーブル内での参照では「テーブル名」は省略可能です

構造化参照をマウスのオートフィルで横方向にコピーすると、セル参照の「相対参照」のように参照列が右へずれてしまいます。

本記事では、Excelの構造化参照について、基本的な使い方から応用、メリット・デメリットまでを解説しました。

構造化参照のポイントは以下の通りです。

構造化参照が使えるようになると、テーブル内のデータをより柔軟かつ効率的に扱えるようになります。特に、テーブル と Power Query を組み合わせることで、データベース運用の自動化やデータ転記の効率化が可能となり、データ活用の基盤を構築し、全体的な業務効率化が図れます。

» Excelでデータ活用する方法はコチラ