【Excel基本】数式の参照セルを分かりやすくできる「名前」と「構造化参照」とは

Aさん

数式の参照セルが何のデータを示しているか分からない場合があります。。
この場合、何か役立つ機能はありますかね？

森田

その場合、参照するデータ名を設定できる「名前」や「構造化参照」を活用すると良いですね！
では、それぞれ詳細を解説していきますね。

はじめに

この記事は関数に指定するデータ型「参照」や参照演算子を把握していることが前提です。

数式の参照セルにデータ名があると分かりやすくなる

通常、数式で参照するセル範囲は「G2:G11」といった表記です。

慣れれば、こうした表記で十分に理解できますが、この参照範囲にデータ名を付けることで、より分かりやすくなります。

こうした参照範囲をデータ名で表す機能はExcelでは2種類あります。

それぞれ順番に解説していきます。

「名前」とは

参照範囲をデータ名で表す機能の1つ目は「名前」です。

名前とは、指定した単一セルまたはセル範囲に任意のデータ名を設定できる機能です。

つまり、名前を設定後は、その名前を数式で指定することで、名前の参照範囲を指定したことと同じ意味になります。

一例として、G2～G11のセル範囲に「金額」と名前を付け、SUMで参照した数式が以下です。

なお、名前の参照範囲は「固定」です。

そのため、表の特定の列等に名前を付けた場合、追加レコードが名前の参照範囲に含まれず、集計漏れ等のリスクがあります。
上記の例であれば、12行目にレコード追加された場合、G12セルは集計されない。

よって、名前を設定する場合、固定のままで問題ない参照範囲を指定しましょう。

【参考】名前の構文

名前に使える文字等の規則が決まっています。

詳細は、名前の構文が正しくない場合に表示されるエラーメッセージで確認することが可能です。

この名前の構文が正しくありません。

名前が次の規則を満たしていることをご確認ください：
- 先頭が英文字、ひらがな、カタカナ、漢字、またはアンダースコア（_）である。
- 空白または他の無効な文字が含まれていない。
- ブック内の既存の名前と競合していない。

後述する構造化参照の一部となる「テーブル名」も上記構文と同様。

この規則の中で名前を設定してください。

名前の設定手順

名前の設定手順は、次の3種類あります。

【手順①】名前ボックスへ直接入力

1つ目は、名前ボックス経由で設定する手順です。

詳細の手順は以下の通りです。

手順②は「Alt」+「F3」で名前ボックスをアクティブ状態にすることが可能。

【手順②】右クリックメニューの「名前の定義」コマンド

2つ目は、右クリックメニュー経由で設定する手順です。

詳細の手順は以下の通りです。

手順①の内容によって手順④の時点で「名前」ボックスへ候補の名前がプリセットされている場合あり（列名等）。

【手順③】リボン「数式」タブの「名前の定義」コマンド

3つ目は、リボン「数式」タブ経由で設定する手順です。

詳細の手順は以下の通りです。

手順①の内容によって手順④の時点で「名前」ボックスへ候補の名前がプリセットされている場合あり（列名等）。

名前の使用手順

設定した名前を数式で使用する場合の手順は以下の通りです。

上記手順は「名前の貼り付け」ダイアログ経由の方法（「F3」キー→任意の名前を選択→「OK」）で時短が可能。

上記以外に、名前を数式へ直接入力する、名前の参照範囲を指定する（名前「金額」であれば参照範囲のG2~G11セルを指定）といった方法でも名前を使用できます。
名前を設定する前に、名前の参照範囲を指定していた数式は「G2:G11」等のセル範囲表示のまま。

なお、名前は文字列ではないため、ダブルクォーテーション（”）で囲う必要はありません。
逆に、ダブルクォーテーション（”）で名前を囲ってしまうと、数式の戻り値にエラー値「#VALUE!」が表示されてしまう。

名前の編集手順

設定した名前に対し、後から名前や参照範囲を編集したい場合の手順は以下の通りです。

手順③の名前をダブルクリックで手順④を省略可能。
「名前の管理」ダイアログの「値」は配列定数で表現されている。

名前の削除手順

設定した名前を削除したい場合の手順は以下の通りです。

「構造化参照」とは

参照範囲をデータ名で表す機能の2つ目は「構造化参照」です。

構造化参照とは、参照先の表を「テーブル」に設定した場合に適用される参照の種類です。
テーブルとは、任意のセル範囲を「データベースに最適化した表」にする機能のこと。

一例として、受注テーブルの「金額」列をSUMで参照した数式が以下です。

「受注テーブル[金額]」が構造化参照です。

なお、構造化参照の範囲はテーブル範囲の拡張/縮小に「連動」します。
上記の例であれば、12行目にレコード追加された場合、G12セルも集計される。

そのため、名前と違い、数式を構造化参照にした場合、追加レコードも集計対象に含まれ、集計漏れを防ぐことが可能です。

よって、表に対してデータ名を設定したい場合、その表をテーブルにして構造化参照を活用することを基本路線にしましょう。

ちなみに、テーブルは任意の名前（テーブル名）を設定できるため、分かりやすいものを設定すると良いです。

構造化参照の種類

構造化参照は、テーブルの参照範囲に応じて、テーブル名と列名等を組み合わせたデータ名が自動的に付加されます。
テーブル名も「名前の管理」ダイアログの管理対象。

この構造化参照は全部で8種類あります。

以下はI4セルを基準にした場合のものです。

構造化参照のうち、テーブル名と列名は可変。
テーブルの上記以外を参照した場合、構造化参照にならない（通常のセル番地やセル範囲の表記）。

それぞれの具体例を順番に解説していきます。

【構造化参照①】テーブルすべて

テーブル全体を参照した場合、構造化参照は「テーブル名[#すべて]」でした。

一例として、受注テーブルの全範囲を参照した場合、構造化参照は「受注テーブル[#すべて]」となります。

実務での使用頻度は低い。

【構造化参照②】特定の列すべて

テーブルの特定の列全体を参照した場合、構造化参照は「テーブル名[[#すべて],[列名]]」でした。

一例として、受注テーブルの「金額」列全体を参照した場合、構造化参照は「受注テーブル[[#すべて],[金額]]」と表示されます。

実務での使用頻度は低い。

なお、連続する複数列を参照した場合、構造化参照の列名部分に参照演算子のコロン（:）が追加されます。

たとえば、受注テーブルの「単価」列から「金額」列までの全体を参照した場合、「受注テーブル[[#すべて],[単価]:[金額]]」となるイメージです。

【構造化参照③】見出し行すべて

テーブルの見出し行全体を参照した場合、構造化参照は「テーブル名[#見出し]」でした。

一例として、受注テーブルの見出し行全体を参照した場合、構造化参照は「受注テーブル[#見出し]」と表示されます。

実務ではMATCHで任意のテーブルの列番号を自動計算する場合に使うケースあり。

【構造化参照④】特定の列名

テーブルの特定の列名（見出し）を参照した場合、構造化参照は「テーブル名[[#見出し],[列名]]」でした。

一例として、受注テーブルの「金額」列の列名を参照した場合、構造化参照は「受注テーブル[[#見出し],[金額]]」と表示されます。

実務での使用頻度は低い。

なお、連続する複数列を参照した場合、構造化参照の列名部分に参照演算子のコロン（:）が追加されます。

たとえば、受注テーブルの「単価」列から「金額」列までの列名を参照した場合、「受注テーブル[[#見出し],[単価]:[金額]]」となるイメージです。

【構造化参照⑤】レコードすべて

テーブルのレコード全体を参照した場合、構造化参照は「テーブル名」でした。

一例として、受注テーブルのレコード全体を参照した場合、構造化参照は「受注テーブル」と表示されます。

実務ではVLOOKUPで任意のテーブルから転記する場合に使うケースが多い。

【構造化参照⑥】特定の列のレコードすべて

テーブルの特定列のレコード全体を参照した場合、構造化参照は「テーブル名[列名]」でした。

一例として、受注テーブルの「金額」列のレコード全体を参照した場合、構造化参照は「受注テーブル[金額]」と表示されます。

実務ではSUM等で集計する場合に使うケースが多い。

なお、連続する複数列を参照した場合、構造化参照の列名部分に参照演算子のコロン（:）が追加されます。

たとえば、受注テーブルの「単価」列から「金額」列までのレコード全体を参照した場合、「受注テーブル[[単価]:[金額]]」となるイメージです。

【構造化参照⑦】同じ行すべて

テーブルの同じ行全体（レコード）を参照した場合、構造化参照は「テーブル名[@]」でした。

一例として、受注テーブルの同じ行全体を参照した場合、構造化参照は「受注テーブル[@]」と表示されます。

実務での使用頻度は低い。

【構造化参照⑧】同じ行の特定の列

テーブルの同じ行の特定列を参照した場合、構造化参照は「テーブル名[@列名]」でした。

一例として、受注テーブルの同じ行の「単価」列を参照した場合、構造化参照は「受注テーブル[@単価]」と表示されます。

実務では四則演算の計算列を追加する場合等に使うケースが多い。

なお、連続する複数列を参照した場合、構造化参照の列名部分に参照演算子のコロン（:）が追加されます。

たとえば、受注テーブルの同じ行の「単価」列から「金額」列までを参照した場合、「受注テーブル[@[単価]:[数量]]」となるイメージです。

構造化参照のコピペ後の挙動

構造化参照の数式をコピぺ後の挙動は、絶対参照か複合参照（行：相対参照、列：絶対参照）のいずれかと同じになります。

数式をコピペで再利用していく際、構造化参照だと不都合であれば、コピペ後にスライドするよう任意の参照の種類（相対参照等）を直接数式で入力しましょう。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に「名前の定義」の操作練習をしてみてください。

サンプルファイル_名前の定義.xlsx

サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。

ブックを開いたら、次の手順を実施してください（名前の設定手順②）。

本記事の解説と同じ結果になればOKです！

さいごに

いかがでしたでしょうか？

「名前」と「構造化参照」は数式の参照セルをデータ名で可読性を高めることができる機能です。

特に、構造化参照は任意の表をテーブルにするだけなので活用のハードルが低いのに、保守性は高いため、積極的に活用していきましょう！

なお、数式や関数、テーブルを拙著で体系的に解説していますので、こちらも参考にしてみてください。

Excel関数をフルに使って データの整形・集計・可視化の自動化を極める本

created by Rinker

ソシム

¥2,640 (2024/04/19 00:31:11時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

ご参考になれば幸いですm(_ _)m

森田

テーブルが登場以降、私は実務では構造化参照の方が圧倒的に使う頻度は高いです。
前述の通り、数式の可読性を上げる以外にも、「データの入力規則」のドロップダウンリストを階層化する際にも構造化参照は役立つので、しっかりと理解しておくことをおすすめします。