Excel使いこなしの基礎と応用

目で見てわかりやすい数式になる「構造化参照」

テーブルでは「構造化参照」という参照方法を用います。セル番地の代わりにテーブル名・フィールド名が参照されるので、数式の構造が理解しやすくなります。

目次


構造化参照を使った構文の規則

▶データ表にはテーブルを使うべき! その理由と設定方法
テーブル内のセルを参照してみましょう。
任意のセルに「=」と入力して、テーブル内のF2セルをクリックすると、テーブル名「テーブル1」に続けてF列のフィールド名「料金」が入力されますね。
構造化参照の基本的な表記
=テーブル1[@料金] 角括弧「[]」で括ったこの形が構造化参照におけるフィールド名表記の基本です。

数式バーに「=テーブル名[」と入力すると指定子の候補が一覧表示されます。上の方に並ぶフィールド名は用語では列指定子といいます。下方に並ぶのは特殊項目指定子といって、テーブルを構成する特定のエリアの指定に使います。

構造化参照の指定子の一覧

#が付く特殊項目指定子とは?

テーブルを構成する特定のエリアを端的に表記する特殊項目指定子は以下の通りです。

スポンサーリンク

見出しまで含めてテーブル全体を選択すると、[#すべて]と表示されます。
特殊項目指定子の[#すべて]
見出し行全体を選択すると、[#見出し]と表示され、
特殊項目指定子の[#見出し]
集計行全体を選択すると、[#集計]と表示されます。
特殊項目指定子の[#集計]
また、見出しと集計行を除いたテーブルを[#データ]として呼び出すこともできます。
特殊項目指定子の[#データ]

テーブル名が付く、付かないの違いは?

上記の構文にはフィールド名の右横にテーブル名が表示されていますが、このテーブル名が付かないこともあります。これは、数式が入力されるセルの位置によります。

スポンサーリンク

テーブル名が付くのは、参照元(参照される側)のテーブルの外側に参照先(参照する側)のセルがある時で、
参照先がテーブルの外だとテーブル名が付く
テーブルの内側に参照先(参照する側)のセルがある時はテーブル名は省略されます。
参照先がテーブルの内だとテーブル名が付かない

「@」付きと無しの違いは?

フィールド名の左横に付くアットマーク@は、「数式を入れる行と、このフィールドが交差するセルを参照します」という印です。前述した特殊項目指定子の一種で、Excelの下位バージョンでは[#この行]と表記されていました。参照元と参照先のセルが同じ行にある時に付くということですね。異なる行から参照すると、ただのセル番地が返されます。
参照先と参照元が同じ行にあると@が付く
@付き[フィールド名]の範囲は1セル分ですが、
@付きの列指定子の範囲
このフィールド名からアットマークを外すと、下図のようにフィールドに属す、見出し以外の全セルが選択範囲になります。後述するVLOOKUPやSUMIF関数での範囲指定などには、この@無しを使います。
@が付かない列指定子の範囲
@無しの範囲を選択する時は、列見出しにポイントして、下向きの小さな黒い矢印が出たところでクリック。
@無しの列指定子の範囲選択

数式に組み入れてフィルコピーした場合、@付き@無しのどちらも相対参照的に可変します。
ただ、無しの方は文字列を関数で連結するときなどに列範囲が丸々つながって表示されちゃうようなこともあるので油断は禁物。
文字列をCONCAT関数で連結すると@なしでは正常な結果が返らない

@付きも無しも相対参照のように可変してしまうのであれば、絶対参照にするにはどうすればいいのでしょう?

構造化参照で絶対参照にするには?

VLOOKUP関数の検索範囲を絶対参照にするといったケースなら、最も手軽で確実なのはテーブル名を指定することです。
VLOOKUPでの範囲指定はテーブル名で
検索値が左端になく、表の特定の範囲を指定する必要がある場合は、テーブル名[[@無しのフィールド名]:[@無しのフィールド名]]とします。
VLOOKUPで特定の範囲を参照する
VLOOKUPでは[[商品名]:[単価]]のように、2つ以上のフィールドが範囲指定されることに注目して下さい。

複数の列(フィールド)範囲指定で絶対参照になる

SUMIF関数で施術ごとの売上を一覧表示させるケースで検証してみましょう。
引数の「範囲」と「合計範囲」にそれぞれ一つの列範囲を指定しています。
SUMIF関数で施術ごとの売上を一覧表示
この数式を横にフィルコピーすると、0が並んでしまいます。参照するフィールドが一つずつ右に移動しているせいですね。「範囲」と「合計範囲」を絶対参照にする必要があります。
SUMIF関数をフィルコピーでエラー
ここで前章のVLOOKUPで2つ以上のフィールドが範囲指定されていたことを思い出して下さい。どうやら構造化参照では、まとまった列範囲は可変せず、絶対参照と同じに固定されるようです。
そこで、「=SUMIF(テーブル1[施術メニュー],J2,テーブル1[料金])」を「=SUMIF(テーブル1[[施術メニュー]:[施術メニュー]],J2,テーブル1[[料金]:[料金]])」のように記述します。「単位としての列ではなく、まとまった列範囲を指定しましたよ」という宣言ですね。
フィルコピーしたセルで参照範囲に変化がないことが確認できました。
SUMIF関数の構造化参照を修正

実は、この例で「施術ごとの売上一覧」の項目が横並びであることは重要なポイントです。

行(縦)側に項目が並ぶ表なら1列指定でOK

上記の表の並びを縦方向(行側に項目がある)に替えてみると、「範囲」と「合計範囲」に一つのフィールド名を指定しても正常な値が返されます。
表の項目を行側に並べる
「施術メニュー」「料金」というフィールドの範囲内だけで完結される数式なので、このままでOKということですね。

そもそも、横並びの一覧表を表示したいならピボットテーブル↓を作成するのが一番確実で手っ取り早いわけですが、構造化参照を知る上では覚えておいても邪魔にならない情報かな?と思って書いてみました。
ピボットテーブル


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。