Excel テーブルでの構造化参照の使い方

適用先
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel Mobile

Excel テーブルを作成すると、テーブル、またテーブルの各列ヘッダーに名前が割り当てられます。 Excel テーブルに数式を追加する場合、それらの名前を手動で入力しなくても、テーブルに数式を入力してセル参照を選ぶと、名前が自動的に表示されます。 次にその操作の例を紹介します。

明示的にセル参照を使用せずに テーブルや列の名前を使用する
=Sum(C2:C7) =SUM(DeptSales[Sales Amount])

このテーブル名と列名の組み合わせは、構造化参照と呼ばれます。 構造化参照内の名前は、テーブルにデータを追加またはテーブルから削除するたびに調整されます。

構造化参照は、Excel テーブルの外にテーブルのデータを作成する数式を作成した場合も表示されます。 構造化参照により、膨大な情報量のブック内のテーブルを簡単に見つけることができます。

構造化参照を数式に含める場合は、セル参照を数式に入力する代わりに、参照するテーブル セルを選択します。 次のサンプル データを使用して、構造化参照を使用して販売手数料の金額を計算する数式を入力しましょう。

営業担当者 地域 Sales Amount 手数料 (%) 手数料金額
内田 北部 260 10%
松本 南部 660 15%
金子 東部 940 15%
岡本 西部 410 12%
村上 北部 800 15%
原田 南部 900 15%
  1. 上記のテーブルのサンプル データを見出しも含めてコピーして、新しい Excel ワークシートのセル A1 に貼り付けます。
  2. テーブルを作成するには、データ範囲内の任意のセルを選択し、Ctrl+T を押します。
  3. [ 見出しがあるテーブル ] ボックスがオンになっていることを確認し、[ OK] を選択します。
  4. セル E2 で等号 (=) を入力し、セル C2 を選びます。
    数式バーで、等号の後に構造化参照 [@[Sales Amount]] が表示されます。
  5. 右角かっこの直後にアスタリスク (*) を入力して、セル D2 を選びます。
    数式バーで、アスタリスクの後に構造化参照 [@[手数料 (%)]] が表示されます。
  6. Enter キーを押します。
    Excel では、計算列が自動的に作成され、列全体に数式がコピーされ、行ごとに値が調整されます。

明示的にセル参照を使用した場合

集計列に明示的にセル参照を入力した場合、数式による計算内容の確認が難しくなる可能性があります。

  1. サンプル ワークシートでセル E2 を選択します
  2. 数式バーに「=C2*D2」と入力して、Enter キーを押します。

数式が列にコピーされている間、構造化参照は使用されませんので注意してください。 たとえば、既存の列の C 列と D 列の間に列を追加する場合は、数式を修正する必要があります。

テーブル名の変更方法

Excel テーブルを作成すると、既定のテーブル名 (テーブル 1、テーブル 2 など) が付けられますが、わかりやすいテーブル名に変更できます。

  1. テーブルの任意のセルを選択して、リボンに [テーブル デザイン] タブを表示します。
  2. [テーブル名] ボックスに名前を入力して、Enter キーを押します。

このサンプル データでは、DeptSales という名前を使用しました。

次のルールに従ってテーブル名を付けます。

  • 有効な文字を使う 名前は必ず文字、アンダースコア文字 (_)、またはバックスラッシュ (\) で始めてください。 先頭文字以外には、文字、数字、ピリオド、アンダースコアを使います。 "C"、"c"、"R"、"r" は、[名前] または [ジャンプ] ボックスに入力したときにアクティブ セルの列または行を選ぶショートカットとして既に指定されているため、名前に使用することはできません。
  • セル参照を使用しない 名前は、Z$100 や R1C1 など、セル参照と同じにすることはできません。
  • 単語の区切りにスペースを使わない 名前にスペースは使用できません。 単語の区切り記号としてアンダースコア文字 (_) とピリオド (.) を使用できます。 たとえば、DeptSales、Sales_Tax、First.Quarter のように入力します。
  • 255 文字以内で指定する テーブル名は、255 文字以内で指定します。
  • 一意のテーブル名を使用する 名前で大文字と小文字が区別されません。 Excel では名前で大文字と小文字が区別されないため、「Sales」と入力しても、同じブックに「SALES」という名前の別の名前が既にある場合は、一意の名前を選択するように求められます。
  • オブジェクト識別子を使用する テーブル、ピボットテーブル、グラフを混在する予定の場合は、名前の前にオブジェクト タイプを付けることをお勧めします。 たとえば、売上テーブルの場合は tbl_Sales、売上ピボットテーブルの場合は pt_Sales、売上グラフの場合は cult_Sales、売上ピボットグラフの場合は ptchrt_Sales となります。 これにより、すべての名前が名前の管理の順序付きリストに保持されます。

構造化参照の構文規則

数式では、構造化参照を手動で入力または変更できます。そのために、構造化参照の構文を理解しておくと便利です。 次の数式の例を見てみましょう。

=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])

この数式には、以下の構造化参照コンポーネントが含まれています。

  • **テーブル名:**DeptSales はカスタム テーブル名です。 この名前は、見出しまたは合計行を含まない、テーブル データを参照しています。 既定のテーブル名 (テーブル 1 など) を使うか、別の名前に変更できます。
  • 列指定子:[Sales Amount]と[Commission Amount]は、それらが表す列の名前を使用する列指定子です。 これらの指定子は、列見出しまたは合計行を含まない、列データを参照しています。 ここで示すように指定子は常にかっこで囲みます。
  • 項目指定子:[#Totals] や [#Data] は、合計行などのテーブルの特別な部分を参照する項目指定子です。
  • テーブル指定子:[[#Totals],[Sales Amount]] や [[#Data],[Commission Amount]] は、構造化参照の外側の部分を示すテーブル指定子です。 テーブル名の後に外部参照が続き、外部参照を角かっこで囲みます。
  • 構造化参照:DeptSales[[#Totals],[Sales Amount]] および DeptSales[[#Data],[Commission Amount]] は構造化参照で、先頭がテーブル名で最後に列指定子がある文字列で表されます。

構造化参照を手動で作成または編集するには、以下の構文規則を使用します。

  • 指定子の囲みかっこを使用 テーブル指定子、列指定子、特殊項目指定子はすべて、前後を角かっこ ([ ]) で囲む必要があります。 ある指定子が他の指定子を含む場合、外側の角かっこの組が、他の指定子を囲む内側の角かっこの組を包含している必要があります。 例: =DeptSales[[Sales Person]:[Region]]
  • すべての列見出しが文字列 ただし、構造化参照で使用される場合は引用符は必要ありません。 2014 または 2014/1/1 など、数字や日付もテキスト文字列と見なされます。 列見出しを含む式は使えません。 たとえば、式 DeptSalesFYSummary[[2014]:[2012]] は機能しません。

列見出しを囲む角かっこを特殊文字で囲むつまり、列指定子の場合は、2 重の角かっこが必要になります。 例: =DeptSalesFYSummary[[Total $ Amount]]

数式で余分な角かっこが必要な特殊文字の一覧を次に示します。

  • タブ
  • 改行
  • 復帰
  • コンマ (,)
  • コロン (:)
  • ピリオド (.)
  • 左大かっこ ([)
  • 右大かっこ (])
  • シャープ記号 (#)
  • 単一引用符 (')
  • 二重引用符 (")
  • 左中かっこ ({)
  • 右中かっこ (})
  • ドル記号 ($)
  • キャレット (^)
  • アンパサンド (&)
  • アスタリスク (*)
  • 正符号 (+)
  • 等号 (=)
  • 負符号 (-)
  • 大なり記号 (>)
  • 小なり記号 (<)
  • 除算記号 (/)
  • アット マーク (@)
  • バックスラッシュ (\)
  • 感嘆符 (!)
  • 左かっこ (()
  • 右かっこ ())
  • パーセント記号 (%)
  • 疑問符 (?)
  • バックティック (')
  • セミコロン (;)
  • チルダ (~)
  • アンダースコア (_)
  • 列見出しの一部の特殊文字にはエスケープ文字を使う 一部の文字には特別な意味があるため、エスケープ文字として単一引用符 (') を使う必要があります。 例: =DeptSalesFYSummary['#OfItems]

数式にエスケープ文字 (') が必要な特殊文字の一覧を次に示します。

  • 左大かっこ ([)
  • 右大かっこ (])
  • シャープ記号 (#)
  • 単一引用符 (')
  • アット マーク (@)

構造化参照を読みやすくする目的での空白文字の使用 空白文字を使用すると、構造化参照の読みやすさを向上させることができます。 例: =DeptSales[ [Sales Person]:[Region] ] or =DeptSales[[#Headers], [#Data], [% Commission]]

次の場所にはスペースを 1 つ入力することをお勧めします。

  • 最初の左角かっこ ([) の後
  • 最後の右角かっこ (]) の前
  • コンマの後

参照演算子

セル範囲をさらに柔軟に指定するために、次の参照演算子を使って列指定子を組み合わせることができます。

構造化参照の例 参照先 使用する演算子 参照されるセル範囲
=DeptSales[[Sales Person]:[Region]] 隣接する複数の列に含まれるすべてのセル : (コロン) 範囲演算子 A2:B7
=DeptSales[Sales Amount],DeptSales[Commission Amount] 複数の列に含まれるすべてのセル , (カンマ) 論理和演算子 C2:C7, E2:E7
=DeptSales[[Sales Person]:[Sales Amount]] DeptSales[[Region]:[% Commission]] 複数の列の共通部分 (空白) 論理積演算子 B2:C7

特殊項目指定子

合計行のみなど、テーブルの特定の部分を参照するには、構造化参照で次の特殊項目指定子を使います。

特殊項目指定子の例 参照先
#All 列見出し、データ、および合計を含む、テーブル全体
#Data データ行のみ
#Headers 見出し行のみ
#Totals 合計行のみ。 何もない場合は、Null を返します。
#This Row
または
@
または
@[Column Name]		 数式と同じ行のセルのみ。 これらの指定子は、他の特殊項目指定子と組み合わせることはできません。 参照に対して強制的に暗黙の論理積演算を行う場合、または暗黙の論理積演算の結果を使わず、ある列の単一の値を参照する場合に使います。
Excel では、1 つ以上の行データが含まれたテーブルの #This Row 指定子は省略形の @ 指定子に自動的に変更されます。 ただし、行が 1 行しかないテーブルの場合、Excel では "#This 行" 指定子が置き換えられないため、行を追加すると、予期しない計算結果が生まれる可能性があります。 このような計算の問題を避けるには、構造化参照の数式を入力する前に、複数の行をテーブルに入力してください。

集計列における構造化参照の修飾

集計列を作成する場合、一般に構造化参照を使って数式を作成します。 この構造化参照は、非修飾でも、完全修飾でも使えます。 たとえば、Commission Amount という集計列を作成して手数料の金額を計算する場合、次の数式を使えます。

構造化参照の種類 使用例 コメント
非修飾 =[Sales Amount]*[% Commission] 現在の行で対応する 2 つの列の値を乗算します。
完全修飾 =DeptSales[Sales Amount]*DeptSales[% Commission] 各行について、対応する 2 つの列の値を乗算します。

一般的な規則: 集計列を作成する際など、テーブル内で使う場合は非修飾の構造化参照を使うことができますが、テーブル外で使う場合は完全修飾の構造化参照を使う必要があります。

構造化参照の使用例

次に構造化参照の使用方法をいくつか示します。

構造化参照の例 参照先 参照されるセル範囲
=DeptSales[[#All],[Sales Amount]] 売上金額列のすべてのセル C1:C8
=DeptSales[[#Headers],[% Commission]] 手数料 (%) 列の見出し D1
=DeptSales[[#Totals],[Region]] Region 列の合計。 集計行がない場合は、Null を返します。 B8
=DeptSales[[#All],[Sales Amount]:[% Commission]] 売上金額列と手数料 (%) 列のすべてのセル C1:D8
=DeptSales[[#Data],[% Commission]:[Commission Amount]] 手数料 (%) 列と手数料金額列のデータのみ D2:E7
=DeptSales[[#Headers],[Region]:[Commission Amount]] 地域列から手数料金額列までの列見出しのみ B1:E1
=DeptSales[[#Totals],[Sales Amount]:[Commission Amount]] 売上金額列から手数料金額列までの合計。 集計行がない場合は、Null を返します。 C8:E8
=DeptSales[[#Headers],[#Data],[% Commission]] 手数料 (%) 列の見出しとデータのみ D1:D7
=DeptSales[[#This Row], [Commission Amount]]
または
=DeptSales[@Commission Amount]		 現在の行と手数料金額列が交差する位置にあるセル。 見出しや合計行と同じ行にある場合は、#VALUE! エラーが返されます。
複数の行データが含まれたテーブルで、この構造化参照 (#This Row) を長い形式で入力すると、Excel では省略形 (@) に自動的に変換されます。 どちらも動作は同じです。		 E5 (現在の行が 5 の場合)

構造化参照を活用するヒント

構造化参照を活用する際には、次の点を考慮してください。

  • 数式オートコンプリートを使用する構造化参照を入力する際に正しい構文を確実に使うためには、数式オートコンプリートを使うととても便利です。 詳細については、「数式オートコンプリートを使用する」を参照してください。

  • 半選択によるテーブルの構造化参照を生成するかどうかを決定する 既定では、数式を作成するときに、テーブル内のセル範囲を選択すると、セルが半選択され、数式のセル範囲ではなく構造化参照が自動的に入力されます。 この半選択の動作により、構造化参照を容易に入力できます。 この動作をオンまたはオフにするには、[ファイル>オプション]>数式>数式の操作] ダイアログで、[数式でテーブル名を使用する チェック] ボックスをオンまたはオフにします。

  • 他のワークブック内の Excel テーブルへの外部リンクを含むワークブックを使用する ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合、リンクを含む宛先ブックで #REF! エラーが発生しないように、リンクされたソース ブックを Excel で開く必要があります。 最初に宛先ブックを開いたときに #REF! エラーが表示された場合は、その後ソース ブックを開くと解決されます。 リンク元ブックを最初に開くと、エラー コードは表示されないはずです。

  • 範囲からテーブルへの変換と、テーブルから範囲への変換 テーブルを範囲に変換すると、すべてのセル参照は同等の絶対 A1 スタイルの参照に変更されます。 範囲をテーブルに変換する場合、Excel はこの範囲のセル参照を同等の構造化参照に自動的に変更しません。

  • 列見出しを非表示にする [ 表のデザイン ] タブ >見出し行) からテーブルの列ヘッダーのオンとオフを切り替えることができます。 テーブルの列見出しをオフにしても、列名を使用する構造化参照は影響を受けず、数式内で引き続き使用できます。 テーブルヘッダーを直接参照する構造化参照 (例: =DeptSales[[#Headers],[%Commission]]) は #REF になります。

  • テーブルに列と行を追加または削除する テーブルのデータ範囲は頻繁に変更されるため、構造化参照のセル参照は自動的に調整されます。 たとえば、数式でテーブル名を使用してテーブル内のすべてのデータ セルをカウントし、その後データ行を追加すると、セル参照が自動的に調整されます。

  • テーブル名または列名を変更するテーブル名や列名を変更すると、ブック内で使用されているすべての構造化参照で、テーブルや列見出しの部分が自動的に変更されます。

  • 構造化参照を移動、コピー、オートフィルする 構造化参照を使う数式をコピーまたは移動すると、すべての構造化参照は同じままになります。

    注

    構造化参照をコピーすることと、構造化参照のオートフィルを実行することは、同じではありません。 数式をコピーすると、すべての構造化参照は同じままになりますが、数式のオートフィルを実行すると、完全修飾された構造化参照によって、列指定子は次の表にある連続したデータのように調整されます。

オートフィルの方向 そして、充填中に押します。 結果
上下 なし 列指定子は調整されません。
上下 Ctrl キー 列指定子は連続データとして調整されます。
左右 なし 列指定子は連続データとして調整されます。
上下左右 Shift キー 現在のセルの値が上書きされる代わりに、現在のセルの値が移動され、列指定子が挿入されます。

補足説明

Excel 技術コミュニティの専門家にいつでも質問するか、コミュニティでサポートを受けることができます。

Excel のテーブルの概要
表を作成し、書式設定する
Excel のテーブルのデータを集計する
Excel のテーブルの書式を設定する
行や列を追加または削除してテーブルのサイズを変更する
範囲または表のデータにフィルターを適用する
テーブルを範囲に変換する
Excel テーブルに関する互換性の問題
Excel テーブルを SharePoint にエクスポートする
Excel の数式の概要