「Excelでテーブルのある部分を参照して数式を作ってみたら、いつもと何かが違う…」
こんな経験、ありませんか？
通常の表の場合、「B3:B15」などのように、セルのアドレスが参照されるのが一般的な表示方法です。
しかし、テーブル内のデータを参照した場合、数式がセルアドレスでは表示されません。
このようなテーブルの列が表記された式は「構造化参照」と呼ばれます。
もくじ
本記事では、その構造化参照について分かりやすく説明します。
慣れないととっつきづらく感じてしまう機能ですが、日々進化するExcelの機能を使いこなすためには、テーブルや構造化参照の使い方を知っておくのが断然おすすめです。
今まで構造化参照の使い方を知らなかった人は、これを機会に、本記事を通して学習してくださいね！
テーブルの構造化参照とは
Excelでデータを扱う時、普通は「A1」や「B2」のように、アルファベットと数字を組み合わせてセルの位置を示します。
これを「セル参照」と呼びます。
でも、データをテーブルという特別な形式で管理する場合、セル参照とは違う方法で表示されます。
これを「構造化参照」と言います。
構造化参照では、「テーブル名[列名]」のように、テーブルの名前と列の名前を使います。
時には「@」記号や「[#見出し]」、「[#すべて]」といった特殊な表記も使われます。
これらの表記方法に慣れるのは最初は大変かもしれません。
でも、慣れてしまえばとても便利です。
例えば、普通のExcel表だと、新しいデータを下に追加するたびに数式の中の範囲を手動で修正しなければいけません。
構造化参照をうまく使えば、手動での調整や修正が不要になります。
つまり、構造化参照を使いこなせると、数式の管理にかかる時間を大幅に減らせます。
また、範囲を間違えて指定してしまうミスも防げるので、計算の間違いも減らせます。
構造化参照の設定方法とルール
構造化参照を設定する一番かんたんな方法は、従来のExcelのように、マウスで範囲を選択して式を作るだけです。
ただし、ちょっとした注意点があります。
参照元と参照先のセルの行が違うと構造化参照にならない、ということです。
具体的に見てみましょう。
例えば、テーブル「入力1」内の参照元B2セルをテーブル外の参照先J2セルから参照するとします。
J2セルに数式を入れると、「=入力1[@店舗]」のように構造化参照が設定されます。
しかし、意図的に行をズラして、J2セルからB3セルを参照すると「=B3」という普通のセル参照が設定されてしまいます。
これはテーブル内でも同じです。
例えば、H列で個数と単価を掛けて金額を計算する時も、同じ方法で設定可能です。
ここで、式に含まれている「＠」は何を示しているか気になりませんか？
試しに「@」を排除してみましょう。
「対象列のデータが全て表示される」という、従来のExcelとは異なる結果が示されました。
これは、新しいバージョンのExcelに装備された、スピルという革新的な機能です。
スピル関数とテーブル機能とうまく組み合わせることで、表の更新やデータ集計にかける時間を大きく減らせます。
スピル機能は、最初は少し難しく感じるかもしれません。
でも、使い方に慣れると「これは便利だ！」ってきっと感じるはずです。
また、構造化参照はマウスを使わず数式バーで直接入力することも可能です。
式にテーブル名と大かっこ「 [ 」を直接入力すると、入力候補がリストとして表示されます。
列名を入力したら、大かっこ「 [ 」で締めてあげましょう。
ここまで覚えれば、構造化参照の基礎をマスターしたと言ってもよいでしょう。
構造化参照をよく使う事例5選
Excelテーブルの構造化参照はいろんな場面で活用できますが、中でもよく使うもの５つに絞って紹介します。
本記事では「こんな風に使えるんだ」というイメージをつかんでもらうことが目的なので、あえて関数の詳しい使い方に触れずに解説していきます。
事例1-シンプルな数式(四則演算)
四則演算などシンプルな数式で構造化参照を使うことが、学習のハードルを下げるのに有効な方法です。
普段のExcel式と同じように操作できるので、特別な設定は必要ありません。
ただし、「@」を消去すると、スピルによって対象列のデータが全て表示される、ということだけは覚えておきましょう。
事例2-検索関数で使う(VLOOKUP、MATCH、INDEX)
検索関数でも構造化参照が活躍します。
検索でよく使われる、VLOOKUPやMATCH、INDEXでも構造化参照は使用可能です。
下は、検索関数のMATCHINDEXの組み合わせで構造化参照を使ったパターンです。
範囲の部分には構造化参照が設定されていて、テーブル名と列名の表示が分かりやすいですよね。
一方で、下は従来のセル参照で作った数式です。
セル参照はテーブル名や列名が含まれない冗長な数式なので、構造化参照と比べ見づらく感じてしまうのではないでしょうか？
事例3-条件付き集計で使う
SUMIFS関数のような条件付き集計関数でも、構造化参照は便利です。
品名別の集計表なども、数式を２つのセルに入力するだけで作成可能です。
ここでは、品名別で個数を集計してみました。
まずは、重複を除外したリストを表示するため、UNIQUE関数を使います。
次に、品名別の個数を計算するために、SUMIFS関数を使います。
SUMIFS関数の設定条件部分にはスピルが設定されています。
いずれも構造化参照が用いられていて、数式が解読しやすいですよね。
事例4-入力規則のリストで使う
構造化参照は入力規則のドロップダウンリスト設定でも使えます。
この場合、INDIRECT関数と組み合わせることがポイントです。
ちょっと特殊な書き方ですが、手直し不要のドロップダウンリストを作れる、という大きなメリットがあるので、ぜひマスターしておきたいテクニックですね。
事例5-スピル関数で使う(FILTER、GROUPBY、PIVOTBY)
他の新しめのExcel関数とも構造化参照の相性は抜群です。
サラッと３つの関数の事例を紹介していきます。
まずは、データを抽出するFITLER関数を使ったパターンです。
「にんじん」でフィルターをかけてみました。
項目別の集計結果を計算するGROUPBY関数を使っています。
クロス集計を行うPIVOTBY関数を使っています。
いずれの関数も１つのセルにしか数式を設定していませんが、高度な集計表が作成可能。
急ぎで集計したい時には本当に役立ちます。
まとめ
今回は、テーブルの構造化参照について解説しました。
テーブル機能自体は新しめの機能で、さらに構造化参照を日頃から意識して使っている人は確実に少数派です。
しかし、本記事で紹介したようにうまく関数に組み合わせられれば、メンテ不要で見やすい数式を作れますので、ぜひマスターしておきましょう。