- 構造化参照を使用すると、セル範囲の代わりにテーブル名や列名を使用できるため、数式の明瞭性と保守性が向上します。
- その構文は、テーブル名、要素指定子（#Data、#Totals、#Headers、@）、および角括弧で囲まれた列名を組み合わせたものです。
- 行、列、またはテーブルの追加、削除、または名前変更時に自動的に更新されるため、一般的なA1参照エラーを防ぐことができます。
- 名前、オートコンプリート、および参照演算子を適切に使用することで、Excelの表は高度な分析のための強力なツールとなります。
Excelで頻繁に表を扱う場合、構造化参照に出会う機会が必ずあります。最初は奇妙に感じたり、分かりにくく思えるかもしれませんが、一度使い方を覚えれば、明確で保守しやすい数式を作成するための非常に便利なツールとなります。
基本的な考え方は非常にシンプルです。Excelでは、C2:C7のような参照の代わりに、SalesDeptやSalaryDeptといったテーブル名や列名を使用できます。こうすることで、数式が何を計算しているのかが一目で分かり、テーブルに行や列を追加または削除しても参照が自動的に調整されます。それでは、豊富な実例を交えながら、手順を追って詳しく見ていきましょう。
Excelにおける構造化参照とは具体的に何ですか？
データ範囲をテーブルに変換すると、Excel はそのテーブルと各列ヘッダーに自動的に名前を作成します。それ以降は、テーブル内外を問わず、数式でこれらの名前を使用できます。
例えば、=SUM(C2:C7)のような数式を書く代わりに、 =SUM(SalesDept)のように書くことができます。ここで、「SalesDept」はテーブル名、「Sales Amount」は列名です。このようにテーブル名と列名を組み合わせたものを構造化参照と呼びます。
これらの参照には重要な利点があります。それは、テーブルを変更すると自動的に更新されることです。売上の行を追加したり、新しい列を挿入したりすると、構造化された参照は自動的に適応しますが、従来のC2:C7参照では必要な範囲全体をカバーできなくなり、数式を手動で修正する必要が生じる場合があります。
さらに、構造化参照はテーブルの外部からも使用できます。これは、複数のシートと多数のテーブルを含むワークブックを扱う場合に非常に便利です。ファイル全体に散在するA2:B7のような範囲を解読しようとするよりも、 SalesDeptのような明確な名前が表示される方がはるかに直感的だからです。
テーブルを作成し、基本的な構造化参照を使用する
構造化参照を使用するための最初のステップは、範囲をExcelテーブルに変換することです。売上データ、給与、その他の情報の種類に関わらず、手順は常に同じです。
営業担当者、地域、売上高、手数料率、手数料額という列を持つ表を想像してみてください。ヘッダーを含めてデータを新しいシートにコピーし、セルA1から貼り付けます。次に、その範囲内の任意のセルを選択し、Ctrl+Tを押すと、Excelが表を生成します。
表示されるダイアログボックスで、「テーブルにヘッダーがある」オプションが選択されていることを確認し、「OK」をクリックします。Excel がテーブルデザインを適用し、以降は、そのヘッダーに基づいた構造化参照にアクセスできるようになります。
「手数料額」列の手数料を、売上金額に手数料率を掛けて計算したいとします。セルE2に移動し、等号（=）を入力して、セルC2（売上金額）をクリックします。すると、数式バーにC2の代わりに]のようなものが表示されます。
次に、アスタリスク (*) を入力し、セル D2 (手数料率) をクリックします。Excel は数式に]を追加します。最終的な数式は次のようになります: =]*]。Enter キーを押すと、Excel は自動的に計算列を作成し、数式を下にコピーして、対応する行への参照を内部的に調整します。
従来の細胞参照との違い
上記の計算は、通常のセル参照を使用しても実行できます。たとえば、 E2セルに「=C2*D2」と入力します。Excelは数式を下方向にも複製しますが、この場合は構造化された参照ではなく、明示的な参照を使用することになります。
この方法には2つの問題点があります。1つ目は、数式の可読性が低下することです。1か月後にそのファイルを開くと、C2セルとD2セルに何が入っていたかを覚えておく必要があります。2つ目は、構造的な変更（新しい列の挿入、列の位置変更など）があると、C2*D2という参照が意味をなさなくなるため、数式を修正する必要が生じる可能性があることです。
構造化参照では、その逆が当てはまります。]*]のような式のおかげで、どの列が関係しているかが一目で分かり、列を間に挿入しても数式は有効のままです。つまり、変更に対する耐性が高く、単純なミスを防ぐことができます。
多くの場合、Excelは表内で数式を入力する際に構造化された参照候補を提示してくれるため、構文を覚える必要はほとんどありません。ChatGPT for Excelのような外部アシスタントも役立ちます。入力するだけで、数式のオートコンプリート機能が面倒な作業をすべて行ってくれます。
表の外で作業している場合でも、数式を入力しながら表のセルをクリックしてドラッグすると、Excel は A2:A100 のような範囲ではなく、構造化参照を直接挿入します。
Excelでテーブルに適切な名前を付けたり変更したりする方法
テーブルを作成するたびに、Excel はTable1、Table2などといった汎用的な名前を自動的に割り当てます。これらの名前でも機能しますが、データの内容がわかるような分かりやすい名前に変更する方が実用的です。
名前を変更するには、表内の任意のセルを選択すると、表のデザインタブ（バージョンによっては「表のデザイン」または「デザイン」）が表示されます。「表名」ボックスに、SalesDept、SalaryDeptなど、より分かりやすい名前を入力し、Enterキーを押します。以降、すべての構造化参照でその名前が使用されるようになります。
Excel では、テーブル名に関して一定のルールが設けられています。テーブル名は文字、アンダースコア (_)、またはバックスラッシュ (\)で始まる必要があり、その後は文字、数字、ピリオド、アンダースコアを使用できます。ただし、Z$100 や R1C1 のようにセル参照と一致する名前は使用できません。また、「C」、「c」、「R」、「r」といった文字も使用できません。これらは Excel が列や行を選択するためのショートカットとして予約しているためです。
名前にスペースを含めることはできません。代わりに、アンダースコアまたはピリオドを区切り文字として使用し、SalesDept、SalesTax、FirstQuarter、PurchaseBonus のような名前を作成できます。もう 1 つの制限は長さです。名前は255 文字を超えることはできませんが、実際にはほとんど問題になりません。
テーブル名はワークブック内で一意である必要があり、Excel では大文字と小文字は区別されません。つまり、すでに SALES という名前のテーブルがある場合、Sales という名前のテーブルを新たに作成することはできません。整理整頓のための一般的なコツは、オブジェクトの種類を示す接頭辞を使用することです。たとえば、通常のテーブルにはtbl_Sales 、ピボットテーブルにはpt_Sales、グラフにはchrt_Salesのように接頭辞を付けます。こうすることで、名前マネージャですべてがきちんと分類されます。
構造化参照の詳細な構文
単純な例にとどまらず、構造化参照には、テーブルの特定の部分（ヘッダー、データ行、合計、列全体、交差部分など）を指定できる強力な構文があります。これらの要素を習得することは、複雑なモデルを構築する際に非常に重要です。
一般的な数式は次のようになります。=SUM(SalesDept,],SalesDept,])。ここでは、売上金額列（合計行）の合計と手数料金額列のすべてのデータの合計の 2 つの値が加算されます。
この式では、いくつかの要素を区別できます。テーブル名（たとえば、SalesDept または SalaryDept）は、操作対象のテーブルを識別します。次に、要素指定子（たとえば、または）があり、テーブル全体、データのみ、ヘッダー、または合計行のいずれを参照しているかを示します。
次に、ヘッダー名を使用する列指定子があります。たとえば、`<header>`、`<header>`、`<total>`などです。これらの指定子は、列指定子と項目指定子を組み合わせない限り、ヘッダー行や合計行を自動的に含めることなく、その列のデータを参照します。
最後に、 `,]`や` ,]`のようなセットはテーブル指定子として機能し、テーブルのどの部分を合計、カウント、平均などするかを正確に指定します。テーブル名から始まり列指定子で終わるこの文字列全体が、完全な構造化参照を構成します。
この構文を使用する際は、すべての指定子を角括弧 () で囲む必要があることに注意してください。1 つの指定子の中に他の指定子を含めると、角括弧が入れ子になるため、セットを囲むために外側の角括弧が必要になります。たとえば、=DeptVentas:]は、「Comercial」列と「Región」列の間のすべてのセルを参照します。
列ヘッダー内の特殊文字とスペース
構造化参照内の列ヘッダーは、内部的にはテキスト文字列として扱われますが、引用符で囲む必要はありません。角括弧で囲むだけで済みます。ただし、ヘッダーに特定の特殊文字（句読点、スペース、記号など）が含まれている場合は、Excel ではヘッダー全体をさらに角括弧で囲む必要があります。
例えば、「合計金額」という名前の列がある場合、=SummaryDeptSalesYear]のように記述する必要があります。二重角括弧を使用することで、スペース、ドル記号、句読点などを含むテキスト全体が列名としてExcelに認識されます。
追加の括弧の使用が必要な文字には、タブ、改行、キャリッジリターン、コンマ、コロン、ピリオド、中括弧、ハッシュ記号（#）、シングルクォーテーションマークとダブルクォーテーションマーク、中括弧、ドル記号、キャレット、アンパサンド（&）、アスタリスク、プラス記号とマイナス記号、等号、大なり記号と小なり記号、スラッシュ、アットマーク（@）、バックスラッシュ（\）、感嘆符、丸括弧、パーセント記号、疑問符、グレイブアクセント、セミコロン、チルダ、アンダースコアなどがあります。
さらに、これらの文字の中には Excel で特別な意味を持つものがあり、参照内にエスケープ文字（通常はシングルクォーテーション（'））が必要になります。たとえば、ヘッダーに # 記号やシングルクォーテーションが含まれている場合は、 Excel が正しく解釈できるように、 =DeptBonusYearのように記述する必要があるかもしれません。
もう一つ重要な推奨事項は、構造化参照自体の中にスペースを入れることで可読性を向上させることができるということです。例えば、=DeptVentas:]や、,,] のように複数の指定子を列挙する数式などにスペースを入れることができます。参照内で複数の引数を区切るセミコロンの後、開き括弧の後、閉じ括弧の前、およびセミコロンの後にはスペースを入れるのが一般的です。
参照演算子：範囲、和集合、積集合
構造化参照は、従来のExcel 参照演算子もサポートしているため、A1 タイプの参照を使用せずに、範囲、列の組み合わせ、またはテーブル内の領域間の交差を作成できます。
範囲演算子（コロン：）を「DeptVentas:]」のように使用すると、隣接する複数の列のすべてのセルを参照することになります。セルで考えると、A2:B7のような範囲、またはそれに類する構造化された範囲になります。
連続していない列を組み合わせて選択したい場合は、和集合演算子（セミコロン😉）を使用できます。たとえば、DeptVentas;DeptVentas と入力すると、C2:C7;E2:E7 と入力した場合と同様に、2つの異なる列が選択されます。これは、複数の範囲を引数として受け取る関数で便利です。
最後に、数式ではスペースで表される交差演算子を使用すると、2 つの範囲が交差する領域を取得できます。例えば、DeptVentas:] DeptVentas:]のような参照は、これらの列ブロックの交差部分を指し示し、従来の参照における B2:C7 のような中間範囲に相当します。
これらの演算子のおかげで、非常に柔軟な参照を作成でき、行や列の挿入または削除時にExcelテーブルが提供する自動更新機能を引き続き利用できます。
特殊要素指定子（#All、#Data、#Headers、#Totals、@）
構造化参照の重要な要素の1つは、特殊要素指定子です。これは、数式で使用するテーブルの特定の部分を示します。これらの指定子は常に角括弧で囲まれ、ハッシュ記号（#）で始まります。ただし、現在の行参照は通常@で表されます。
この指定子は、ヘッダー、データ行、および合計行（存在する場合）を含むテーブル全体を参照します。これは、正しく動作するためにテーブル全体を考慮する必要がある関数で非常に役立ちます。
この指定子は、ヘッダー行と合計行を除くデータ行に限定されます。ヘッダー行のみを指し、合計行のみを参照します。テーブル内でその合計行がアクティブでない場合、参照はnull値を返します。
計算列で現在の行を参照するには、指定子、または省略形の「@」記号を使用します。たとえば、[SalesDept] は、数式と同じ行にある「手数料」列のセルを参照します。Excel は通常、テーブルに複数の行がある場合、数式を自動的に「@」に置き換えるため、通常は省略形が表示されます。
#ThisRow と @ は、他の特殊要素指定子と組み合わせることはできないことに注意してください。また、これらの参照をヘッダー行または合計行で使用すると、通常の意味での「現在のデータ行」が存在しないため、#VALUE! エラーが発生する可能性が高くなります。
資格のあるコラム参照と資格のないコラム参照
Excelでは、表内で作業する場合、表のコンテキスト自体が参照として機能するため、修飾されていない参照と呼ばれる短い構造化参照を使用できます。ただし、表の外で数式を作成する場合は、表名を含む完全な参照（修飾参照）を使用する必要があります。
例えば、「SalesDept」テーブルの計算列では、単に「=*」と入力するだけで済みます。Excelはこれらのヘッダーが現在のテーブルに属していることを認識するため、各列の前に「SalesDept」と入力する必要はありません。
ただし、テーブルの外から同じ計算を実行する場合は、完全な式「=DeptVentas*DeptVentas」を使用する必要があります。一般的なルールは非常にシンプルです。テーブル内では修飾されていない参照が受け入れられますが、テーブル外では完全なテーブル名を使用する必要があります。
これは、数式の可読性と保守性の両方を向上させ、同じ Excel ワークブック内でヘッダーが重複する可能性のある複数の表がある場合に発生する曖昧さを回避するのに役立ちます。
構造化参照の使用例
上記をより明確にするために、いくつかの典型的な例と、それに対応するセル参照を見てみましょう。「DeptSales」のような参照は、「売上金額」列のすべてのセルを参照します。これにはヘッダーセルと、存在する場合は合計行も含まれます。これは、標準範囲の「C1:C8」に相当します。
「% Commission」列のヘッダーのみが必要な場合は、DeptVentas,]を使用します。これは範囲で言うと、例えば D1 のような単一のセルに対応します。合計行の「Region」列の合計にアクセスするには、DeptVentas,]という参照を使用します。これは B8 のようなものに相当し、合計行が有効になっていない場合は null が返されます。
異なる指定子を組み合わせて使用することもできます。例えば、DeptSales,:]のような参照は、「売上金額」列と「手数料率」列の間のすべてのセル（ヘッダーを含む）を対象とします。標準的な A1 セル範囲では、C1:D8 のようになります。これらの列のデータのみ（ヘッダーや合計を除く）が必要な場合は、DeptSales,:]を使用し、D2:E7 のように指定します。
もう1つの興味深いケースは、ヘッダーとデータを単一の参照に結合する場合です。たとえば、DeptSales,,]のように指定します。これは、ヘッダーと「% Commission」列のすべてのデータを選択します。これは、D1:D7 に相当します。計算列の現在の行の特定のセルを指定するには、DeptSales]を使用できます。たとえば、現在の行が 5 行目の場合は、E5 になります。
複数行の表で#ThisRow指定子を入力すると、Excel が自動的に短縮形の@に変換してくれるのはよくあることです。計算列内ではどちらの式も同じように機能するため、Excel が数式を書き換えても心配する必要はありません。これは想定される動作です。
構造化された参照を扱う際の戦略とベストプラクティス
構造化参照を最大限に活用するには、 Excelの機能を活用し、範囲の変換、ブックのリンク、テーブル構造の変更を行う際の動作の詳細を念頭に置いておくことが重要です。
まず便利なツールとして、オートコンプリート機能があります。表に文字を入力すると、Excelが列名や指定子を提案してくれるので、構文エラーを最小限に抑えることができます。これは、長い見出しや特殊文字を含む見出しの場合に特に役立ちます。括弧の間違いで数式が台無しになってしまう可能性があるからです。
既定では、数式を作成する際に表内のセル範囲を選択すると、Excel は標準範囲ではなく構造化参照を自動的に生成します。以前の動作に戻したい場合は、[ファイル] > [オプション] > [数式] > [数式の操作] に移動し、[数式で表名を使用する] チェックボックスをオンまたはオフにしてください。
複数のリンクされたワークブックを操作する場合、あるファイルに別のワークブック内の Excel テーブルへの外部リンクが含まれている場合は、リンク先のワークブックで #REF! エラーが発生しないように、リンク元のワークブックを開いておく必要があることに注意してください。リンク先のワークブックを先に開いて #REF! エラーが多数表示される場合は、通常、テーブルを含むリンク元のファイルを開くことで解決します。
テーブルを標準範囲に変換すると、そのテーブルに関連付けられているすべての参照は、絶対形式のA1スタイルに変換されます。ただし、範囲をテーブルに変換しても、Excelは既存の参照を自動的に構造化参照に変更しません。スタイルを変更したい場合は、手動で調整する必要があります。
もう一つ重要な点として、テーブルデザインタブからヘッダー行の表示/非表示を切り替えることができます。ヘッダー行を非表示にした場合、列名を使用する構造化参照は引き続き機能しますが、ヘッダーを直接指す参照（例えば、SalesDept,] のようなもの）は #REF エラーを返します。
テーブルの大きな利点の1つは、行や列を追加または削除すると、構造化された参照が自動的に更新されることです。行数をカウントしたりデータを合計したりする関数でテーブル名を使用し、さらにレコードを追加すると、参照は自動的に更新されてそれらのレコードが含まれるようになります。
さらに、テーブルや列の名前を変更すると、Excel はブック内でその識別子を使用しているすべての数式にその変更を反映させます。これにより、明確で分かりやすい名前を使用することの重要性が改めて認識されます。なぜなら、ブック全体に散在する数式が壊れる心配なく、名前を変更できるからです。
最後に、構造化参照を含む数式をコピーまたは移動する場合、指定子は通常保持されます。上方向または下方向にフィルしても、既定では Excel は列名を調整しません。Ctrl キーを押しながらフィルすると、列がシリーズとして扱われる場合があります。左方向または右方向にフィルすると、列はシーケンスのように移動します。Shift キーを押しながらフィルすると、既存の値を上書きするのではなく、新しい列が挿入され、値が移動します。
よく考えられたテーブル名、正しい構文、指定子、参照演算子の使用、そして適切な編集方法といった要素をすべて習得することで、構造化参照はExcelモデルをより明確で柔軟性があり、変更にも強いものにするための非常に強力なリソースとなります。これは、ファイルサイズが大きくなり、複数のユーザー間で共有される場合に特に価値があります。