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

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2013 Excel Mobile その他…表示数を減らす

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

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

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

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

構造化参照を数式に含める場合は、セル参照を数式に入力する代わりに、参照するテーブルセルをクリックします。以下の例のデータを利用して、自動的に構造化参照を使って、販売手数料の金額を計算する数式を入力してみましょう。

営業担当者	地域	売上金額	手数料 (%)
内田	北部	260	10%
松本	南部	660	15%
金子	東部	940	15%
岡本	西部	410	12%
村上	北部	800	15%
原田	南部	900	15%

上記のテーブルのサンプルデータを見出しも含めてコピーして、新しい Excel ワークシートのセル A1 に貼り付けます。
テーブルを作成するには、データ範囲内の任意のセルを選択し、Ctrl+T を押します。
[先頭行をテーブルの見出しとして使用する] ボックスがオンになっていることを確認して、[OK] をクリックします。
セル E2 に等号 (=) を入力して、セル C2 をクリックします。

数式バーで、等号の後に構造化参照 [@[Sales Amount]] が表示されます。

右角かっこの直後にアスタリスク (*****) を入力して、セル D2 をクリックします。

数式バーで、アスタリスクの後に構造化参照 [@[手数料 (%)]] が表示されます。

Enter キーを押します。

Excel では、計算列が自動的に作成され、列全体に数式がコピーされ、行ごとに値が調整されます。

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

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

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

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

テーブル名の変更方法

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

テーブル内の任意のセルを選んで、リボンの [テーブルツール] > [デザイン] タブを開きます。
[テーブル名] ボックスに名前を入力して、Enter キーを押します。

サンプルデータでは、「DeptSales 」という名前を使っています。

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

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

構造化参照の構文規則

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

=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]] は機能しません。

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

数式で追加の角かっこが必要になる特殊文字は次のとおりです。

タブ
改行
復帰
コンマ (,)
コロン (:)
ピリオド (.)
左大かっこ ([)
右大かっこ (])
シャープ記号 (#)
単一引用符 (‘)
二重引用符 (")
左中かっこ ({)
右中かっこ (})
ドル記号 ($)
キャレット (^)
アンパサンド (&)
アスタリスク (*)
正符号 (+)
等号 (=)
負符号 (-)
大なり記号 (>)
小なり記号 (<)
除算記号 (/)
アットマーク (@)
バックスラッシュ (\)
感嘆符 (!)
左かっこ (()
右かっこ ())
パーセント記号 (%)
疑問符 (?)
Backtick (‘)
セミコロン (;)
チルダ (~)
アンダースコア (_)
列見出しでは一部の特殊文字にエスケープ文字を使う 一部の文字には特別な意味があるため、エスケープ文字として単一引用符 (‘) を使う必要があります。例: =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 Row 指定子が変換されないため、行を追加すると、予期しない計算結果が生まれる可能性があります。このような計算の問題を避けるには、構造化参照の数式を入力する前に、複数の行をテーブルに入力してください。

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

集計列を作成する場合、一般に構造化参照を使って数式を作成します。この構造化参照は、非修飾でも、完全修飾でも使えます。たとえば、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]	現在の行と手数料金額列が交差する位置にあるセル。見出しや合計行と同じ行にある場合は、 target=”_blank” title=”#VALUE!” rel=”noopener”>#VALUE! エラーが返されます。複数の行データが含まれたテーブルで、この構造化参照 (#This Row) を長い形式で入力すると、Excel では省略形 (@) に自動的に変換されます。どちらも動作は同じです。	E5 (現在の行が 5 の場合)

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

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

数式オートコンプリートを使用する 構造化参照を入力する際に正しい構文を確実に使うためには、数式オートコンプリートを使うととても便利です。詳細については、「 target=”_blank” title=”数式オートコンプリートを使用する” rel=”noopener”>数式オートコンプリートを使用する」を参照してください。
半選択によるテーブルの構造化参照の生成 既定では、数式を作成する際にテーブル内のあるセル範囲をクリックするとセルが半選択状態になり、セル範囲ではなく構造化参照が自動的に数式に入力されます。この半選択の動作により、構造化参照を容易に入力できます。この動作をオンまたはオフにするには、[ファイル] > [オプション] > [数式] > [数式の操作] ダイアログの [数式でテーブル名を使用する] チェックボックスをオンまたはオフにします。
他のワークブック内の Excel テーブルへの外部リンクを含むワークブックを使用する ブックに別のブックの Excel テーブルへの外部リンクが含まれている場合、リンクを含む宛先ブックでの target=”_blank” title=”#REF!” rel=”noopener”>#REF! エラーを避けるために、リンクされたソースブックを Excel で開く必要があります。最初に宛先ブックを開いたときに target=”_blank” title=”#REF!” rel=”noopener”>#REF! エラーが表示された場合は、その後ソースブックを開くと解決されます。リンク元ブックを最初に開くと、エラーコードは表示されないはずです。
範囲からテーブルへの変換と、テーブルから範囲への変換 テーブルを範囲に変換すると、すべてのセル参照は同等の絶対 A1 スタイルの参照に変更されます。範囲をテーブルに変換する場合、Excel はこの範囲のセル参照を同等の構造化参照に自動的に変更しません。
列見出しの非表示 テーブルの [デザイン] タブ > [ヘッダー行] から、テーブルの列ヘッダーのオンとオフを切り替えることができます。テーブルの列ヘッダーをオフにしても、列名を使用する構造化参照は影響を受けず、引き続き数式で使用できます。テーブルヘッダーを直接参照する構造化参照 (例: =DeptSales[[#Headers],[%Commission]]) は target=”_blank” title=”#REF” rel=”noopener”>#REF になります。
テーブルに列と行を追加または削除する テーブルのデータ範囲はよく変更されますが、構造化参照によるセル参照は自動的に調整されます。たとえば、数式でテーブル名を使用してテーブル内のすべてのデータセルをカウントし、その後データ行を追加すると、セル参照が自動的に調整されます。
テーブル名または列名の変更 テーブル名や列名を変更すると、ブック内で使用されているすべての構造化参照で、テーブルや列見出しの部分が自動的に変更されます。
構造化参照を移動、コピー、オートフィルする 構造化参照を使う数式をコピーまたは移動すると、すべての構造化参照は同じままになります。

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

オートフィルの方向	オートフィルによる入力で使用するキー	結果
上下	なし	列指定子は調整されません。
上下	Ctrl キー	列指定子は連続データとして調整されます。
左右	なし	列指定子は連続データとして調整されます。
上下左右	Shift キー	現在のセルの値を上書きせずに、セルの値を移動して列指定子を挿入します。

補足説明

Excel Tech Communityで、いつでも専門家に質問できます。また、コミュニティでは、サポートを受けられます。