\今日から出社せず退社/ 退職代行ガーディアン

エクセルのテーブルを参照!構造化参照で数式を入力する方法

紬ちゃん

エクセルはテーブルで参照したほうが便利だって聞いたんですけど、テーブルと表の違いもよくわからないので、わかりやすく教えてもらえませんか。

ライクさん

表をテーブルに変換すると、表にはない便利な機能が使えるようになるんだ。テーブルで参照する「構造化参照」も便利な機能の1つなんだよ。

エクセルで表を作成することが多い人でも、テーブルはあまり使ったことがない方もいるかもしれません。

表をテーブルに変換してから参照する「構造化参照」には、通常のセル参照にはない便利な機能があります。

エクセルでテーブルを参照する簡単ステップ
  1. 作成した表をテーブルに変換する
  2. 変換したテーブルと計算に使用するセルに名前をつけておく
  3. セル参照と同じ手順で計算式を入力

テーブルを使って参照する方法には、「構造化参照」という少し難しい名前がついています。

しかし、使い方を覚えるとデータ入力の効率アップに役立ちます。

構造化参照のしくみと使い方を覚えて、ぜひ活用してみてください。

テーブルで参照する「構造化参照」以外にも、エクセルの参照について解説した記事があります。

こちらの記事もぜひ参考にしてみてください。

目次

エクセルのテーブルで参照する「構造化参照」参照の手順

紬ちゃん

「表とテーブルの違い」と「セル参照と構造化参照の違い」をわかりやすく教えてください。

ライクさん

「テーブル」と「表」の違いと「セル参照」と「構造化参照」の違いを解説していくね。

セル参照で数式を入力する場合は、表に入力したデータのセル番地を使って参照しますが、構造化参照を使う場合は、行または列を1つのまとまりとして参照します。

「表とテーブル」、「セル参照と構造化参照」の違いをさらに詳しく解説していきます。

そもそも構造化参照とは?

「構造化参照」とは、表をテーブルに変換し、テーブル内に入力されているデータをテーブル名や列名を使って参照することです。

セル参照で計算したデータと構造化参照で計算したデータで比較して、違いを確認してみましょう。

セル参照と構造化参照の比較
セル参照と構造化参照の比較

この2つのデータは、「B4」の定価に「C1」の掛け率をかけた販売価格の計算式を、「セル参照」と「構造化参照」で入力したものです。

左側が「セル参照」で、右側が「構造化参照」です。

表とテーブルの違いはありますが、データ内の内容は同じものを使用しています。

セル参照で計算した数式はセル番地で表示されていますが、テーブルで参照した構造化参照の数式は「[@定価]*掛け率」のように文字で表示されています。

構造化参照は、計算式が文字で表示されているので、計算の内容が見やすくなっていますね。

紬ちゃん

セル参照で計算したものは、先頭のセルしか計算結果が入っていないのに、右側の構造化参照で計算したものには全部の答えが表示されているのはなぜですか?

ライクさん

いいところに気がついたね!構造化参照を使うと、計算式をコピーしなくても自動ですべてのセルに計算結果が表示されるんだ。

テーブルを参照をする計算は、「計算式がわかりやすくなる」、「オートフィルでコピーをしなくても、計算結果が表示される」といったメリットだけではありません。

データを追加するとセルに挿入した計算式も自動で追加されるなど、セル参照にはない機能が使えるようになります。

ただし、表とテーブルは見た目は似ていますが、機能には以下のような違いがあります。

テーブルを使って参照するときは、表とテーブルの違いを理解しておきましょう。

テーブル
セル単位で操作ができるので、特定のセルを削除するなどの操作がしやすい行または列単位で操作するので、特定のセルだけを削除することができない
削除は、列または行単位で操作する必要がある
書式設定をしたい場合は、セル単位で操作する必要がある表の範囲を選択してテーブルに変換するだけで一瞬で書式設定ができる
セル参照で計算式を入力した場合、データを追加したときはデータ範囲を修正する必要がある構造化参照で計算式を入力すると、データを追加すると計算範囲が自動で拡張される
自由に書式設定ができるセルの結合ができないなど、書式設定に制限がかかる機能がある
ライクさん

テーブルに変換すると、セル単位での操作ができなくなってしまうから、注意しよう。

エクセルで構造化参照するための準備

エクセルでテーブルを使った構造化参照をするために、以下の内容を準備しておきましょう。

  • 表をテーブルに変換する
  • 計算に使用する範囲とセルに名前をつけておく

それぞれの操作方法を詳しく解説します。

表をテーブルに変換する

作成した表をテーブルに変換します。

STEP
表の範囲を選択して「テーブル」をクリックする
表の範囲を選択して「テーブル」をクリックする
表の範囲を選択して「テーブル」をクリックする
  • 表の範囲を選択
  • 「挿入」タブをクリック
  • 「テーブル」をクリックする
STEP
「テーブルの作成」の画面の「OK」を押す
「テーブルの作成」のダイアログボックス
「テーブルの作成」のダイアログボックス
  • 「先頭行をテーブルの見出しとして使用する」のチェックが入っていることを確認
  • 「OK」をクリックする
STEP
表をテーブルに変換できた
テーブルに変換できた
テーブルに変換できた

表をテーブルに変換することができました。

参考

テーブルは、以下の手順で表に戻すことができます。

テーブルを表に戻す
テーブルを表に戻す
  • テーブル内の任意の場所をクリック
  • 「テーブルデザインタブ」を選択
  • 「範囲に変換」をクリックする

「範囲に変換」は表に変換するという意味です。

計算に使用する範囲とセルに名前をつける

テーブルに変換した範囲と、計算に使用するセルに名前をつけておきましょう。

今回のデータでは、「定価」が入力されているB列と、「掛け率」が入力されている「C1」が必要なので、この2つ場所に名前をつけておきます。

STEP
定価の列を選択して名前ボックスに「定価」と入力
定価の列を選択して「定価」と名前をつける
定価の列を選択して「定価」と名前をつける
  • 「定価」の列の範囲を選択
  • 名前ボックスに「定価」と入力
  • Enterで確定する
STEP
掛け率が入力されているセルに名前をつける
掛け率が入力されているセルに名前をつける
掛け率が入力されているセルに名前をつける
  • 掛け率が入力されている「C1」をクリック
  • 名前ボックスに「掛け率」と入力する
  • Enterで確定
STEP
名前ボックスをクリックして確認する
名前が表示される
名前が表示される

名前ボックスをクリックすると、登録した名前が表示されます。

「定価」と「掛け率」という名前が表示されるか確認しておきましょう。

構造化参照で計算式を入力する手順

構造化参照をするための準備が完了したあとは、セル参照と同じ手順で計算式を入力していきます。

STEP
計算に必要なセルの場所をクリックして計算式を入力
セル参照と同じ手順で数式を入力
セル参照と同じ手順で数式を入力
  • 「C4」をクリックして「=」を入力
  • 「B4」をクリック
  • 「*」を入力
  • 「C1」をクリックする
  • Enterで確定

名前をつけた範囲のセルをクリックすると、[@定価]、掛け率のようにセル番地ではなく名前で表示されます。

STEP
すべてのセルに販売価格が表示された
すべてのセルに販売価格が表示された
すべてのセルに販売価格が表示された

すべてのセルに販売価格を表示することができました。

構造化参照を使うと絶対参照を使う必要がなくなる理由

構造化参照を使うと絶対参照を使う必要がなくなります。

紬ちゃん

掛け率のようにすべてのセルに同じセルを使って入力して計算式をコピーするときは、絶対参照を使わないといけないって聞いたんですけど、どうして絶対参照を使う必要がないんですか?

ライクさん

構造化参照を使うと、セル単位ではなくて名前をつけた場所や列、行単位で参照するから、セルの位置が「ずれる」いう考え方がなくなるんだ。

商品名と掛け率が入力されている先ほどのデータを使って、「相対参照」と「構造化参照」の計算式とセルのずれ方を比較してみましょう。

掛け率を相対参照の状態で挿入した計算結果
掛け率を相対参照の状態で挿入した計算結果

これは、「C4」に「=B4*C1」という計算式を挿入して、残りのセルに計算式をコピーした画像です。

計算結果を見ると、手入力したセル以外の計算結果がすべて間違っています。

数式で表示した計算式
数式で表示した計算式

これは、先ほどの計算結果を「数式の表示」に変更した画像です。

2番目以降のC列のセル番地をみると、1つずつ下にずれてしまっていることがわかります。

表を使用して計算式を挿入する場合、掛け率のように毎回同じセルを使って計算しなければいけない計算式をコピーするときは、絶対参照にしないとセルの位置がずれてしまいます。

そのため、セル参照の計算式をコピーする場合、固定したいセルに対して絶対参照の操作が必要です。

構造化参照で入力した計算式
構造化参照で入力した計算式

これは、「構造化参照」で計算式を入力し、数式で表示したものです。

計算式を見るとすべてのセルに同じ文字が入力されていて、「定価」と名前をつけた列と「掛け率」と名前がつけたセルを参照していることがわかります。

定価の列にはすべて同じ[@定価]と表示されていますが、列を一つのまとまりとして参照しているため、実際に計算で使用しているのは「B列の4からB列の16」の範囲です。

構造化参照では、「列名」または「あらかじめ設定した名前の範囲」で指定しているため、セルがずれることがなくなります。

つまり、計算式をコピーしてもセルがずれることがないので、絶対参照を使う必要がないということです。

別シートのテーブルを参照する方法

構造化参照は、同じシート内のテーブルだけではなく、別のシートにあるテーブルを参照することも可能です。

「参照したいテーブルが別のシートにある」という点以外は、同じシート内のテーブルを参照するときと手順は同じです。

別シートのテーブルを参照する手順

別のシートにあるテーブルを使って、構造化参照をする手順を解説します。

STEP
シートを分けて「定価表」と「販売価格」の表を作成する
シートをわけて「販売価格」の表と「定価表」を作成
シートをわけて「販売価格」の表と「定価表」を作成

シートを分けて、「販売価格」の表と「定価表」を作成し、必要な項目を入力しておきます。

STEP
作成した表をテーブルに変換
「定価表」をテーブルに変換
「定価表」をテーブルに変換
  • 「定価表のシート」を選択
  • 「挿入」タブをクリック
  • 表の範囲を選択
  • 「テーブル」をクリックする
STEP
「テーブルの作成」のダイアログボックスの画面の「OK」を押す
テーブルの作成のダイアログボックス
テーブルの作成のダイアログボックス

上記のような画面が表示されたら、「OK」をクリックしてください。

STEP
表がテーブルに変換された
「定価表」がテーブルに変換された
「定価表」がテーブルに変換された

「定価表」をテーブルに変換することができました。

STEP
「販売価格」の表もテーブルに変換する
テーブルに変換した「販売価格」の表
テーブルに変換した「販売価格」の表

同様の手順で、「販売価格シート」の表の範囲もテーブルに変換しておきましょう。

STEP
「定価表のシート」のテーブルに名前をつける
「定価表」のテーブルに名前をつける
「定価表」のテーブルに名前をつける
  1. 「定価表のシート」の金額が入力されている範囲を選択
  2. 名前ボックスに「定価」と入力してEnterで確定
STEP
「販売価格のシート」の掛け率の値が入力されているセルに「掛け率」と名前をつける
「販売価格」シートの掛け率が入力されているセルに名前をつける
「販売価格」シートの掛け率が入力されているセルに名前をつける
  • 「販売価格のシート」の「B1」をクリック
  • 名前ボックスに「掛け率」と入力してEnterで確定
STEP
「販売価格のシート」に「=」を入力
「販売価格」のシートに「=」を入力
「販売価格」のシートに「=」を入力
  • 「販売価格のシート」の「B3」をクリックして「=」を入力
  • 「定価表のシート」を選択
STEP
「定価表のシート」の「B3」をクリックする
「定価表」のシートの「B3」をクリックする
「定価表」のシートの「B3」をクリックする
  • 「定価表のシート」の「B3」をクリック
  • 「*」を入力
STEP
「販売価格のシート」を選択
「販売価格」のシートを選択
「販売価格」のシートを選択

「販売価格のシート」をクリックしてください。

STEP
「B1」をクリックする
「B1」をクリックする
「B1」をクリックする
  • 「B1」をクリックする
  • Enterで確定
STEP
別シートの「定価表」を使って「販売価格」を計算できた
別シートの「定価表」を使って販売価格を計算できた
別シートの「定価表」を使って販売価格を計算できた

別シートに作成した「定価表」を使って、販売価格を計算することができました。

別シートのテーブルを参照したデータを修正する方法

別シートのテーブルを参照したデータの修正は、別シートの表をセル参照したときと同じ手順で修正することができます。

定価表と販売価格のテーブル
定価表と販売価格のテーブル

「販売価格のシート」の販売価格は、「定価表のシート」のB列の定価と「販売価格のシート」の「B1」の掛け率を掛けて計算しています。

販売価格を修正したいときは、「定価表のシート」のB列の金額を変更すると販売価格に反映されます。

別のシートの表を参照する方法を紹介した記事もありますので、こちらも参考にしてみてください。

テーブルで参照する構造化参照のメリット・デメリット

テーブルで参照する「構造化参照」はメリットもありますが、表と同じ操作ができなくなるものがあります。

テーブルで参照する場合は、表とテーブルの違いを理解しておきましょう。

構造化参照のメリット

構造化参照の主なメリットは、以下の2つです。

  • 数式を見ただけで計算式の内容が把握できる
  • データを追加すると、参照範囲が自動で認識されて拡張される

それぞれの内容を詳しく解説していきます。

数式を見ただけで計算式の内容が把握できる

テーブルで参照する「構造化参照」を使うと、計算式がセル番地ではなくあらかじめ設定した名前や列名などで表示されます。

会社内の書類は、他の人が作成した書類を共有で使うということがありますね。

紬ちゃん

そうなんです。他の人が作ったデータに計算式や関数が使われていると、どこのセルを使って計算しているのかがよくわからなくて、間違って削除しちゃったらどうしようって不安になることがあるんです。

ライクさん

セル参照の場合は、セル番地を見て計算内容を判断しないといけないから、計算式の意味がすぐにわからないこともあるよね。

しかし、テーブルで参照しておくと、計算式を「定価」や「掛け率」のように文字で表示することができるので、計算式の内容を把握しやすくなります。

特に他の人と共有するデータを作成する場合は、「自分だけではなく、他の人も使いやすいか」という点を意識することも必要です。

計算式を複雑にしすぎてしまうと逆にわかりにくくなってしまうケースもあるので注意する必要はありますが、構造化参照は計算式を文字で表示できるので、計算内容を確認する手間を最小限に抑えることができます。

データを追加すると自動で認識されるようになる

テーブルで参照するもう一つのメリットは、データを追加すると追加したデータが自動で認識されるようになることです。

紬ちゃん

追加したデータが自動で認識されるってどういう意味ですか?

ライクさん

実際のデータを使って、確認してみよう!

構造化参照で入力した計算式の表
構造化参照で入力した計算式の表

これは、構造化参照を使って販売価格を計算しているテーブルです。

「A17」の行にデータを追加する
「A17」の行にデータを追加する

「A17」の行に新しくデータを追加してみましょう。

テーブルが拡張され、自動的に計算結果が表示された
テーブルが拡張され、自動的に計算結果が表示された

テーブルが拡張され、計算結果が自動で表示されました。

セル参照で計算式を入力した場合は、データを追加したときはデータ範囲を修正する必要があります。

しかし、構造化参照で入力しておくとテーブル範囲が自動で拡張されるので、範囲の修正は必要ありません。

追加が多いデータを作成する場合は、構造化参照を活用してみてください。

構造化参照のデメリット

便利な機能が多い構造化参照ですが、以下のようなデメリットもあります。

  • セルの結合ができなくなる
  • 書式設定や貼り付けなどの機能で機能制限がかかることがある

セルの結合ができなくなる

表をテーブルに変換すると、セルの結合ができなくなります。

セルの結合ができない
セルの結合ができない

テーブル内のセルを複数選択してみると、「セルを結合して中央揃え」のアイコンがグレーになってしまい、使えない状態になっています。

テーブルに変換するとセルの結合ができなくなってしまうので、注意してください。

書式設定などで機能制限がかかることがある

表をテーブルに変換するとセル単位での操作ができなくなるため、書式設定などの操作で機能制限がかかることがあります。

例えば、表の場合はセル1つ分だけを挿入することができますが、テーブルの場合は行または列単位でしか追加することができません。

表はセル単位で挿入が可能
表はセル単位で挿入が可能

表の場合は、上記の画像のようにセル1つ分だけを挿入するといった操作が可能です。

テーブルは列または行単位でしか挿入できない
テーブルは列または行単位でしか挿入できない

しかし、テーブルに変換してしまうと、列単位または行単位でしか追加することができません。

それ以外にも、表ではできてもテーブルだとできないという機能があるので注意してください。

構造化参照ができない主な原因と対処法

紬ちゃん

構造化参照で計算式を入力してみたんですけど、うまくできないんです。どうしたらいいですか?

ライクさん

構造化参照ができない主な原因と対処法を教えるね。

構造化参照がうまくできないときは、以下の点を確認してみましょう。

  • セル番地で指定している
  • 行や列が追加されたあとにデータの更新がうまくできていない
  • エクセルのバージョンによる影響

セル番地で指定している

セル番地を直接入力すると、セル参照と同じ形で計算式が入力されてしまいます。

セルをクリックして指定すると列で参照される
セルをクリックして指定すると列で参照される

参照するセルをクリックして計算式を入力すると、こちらの画像のように列を参照する形で表示されます。

セル番地を使って直接入力した数式
セル番地を使って直接入力した数式

しかし、数式バーに「=A3:$B$1」のように直接入力してしまうと、セル参照と同じ形で表示されてしまいます。

列番号や名前で表示されない場合は、数式の入力方法を確認してみてください。

行や列が追加されたあとにデータの更新がうまくできていない

テーブルに変換したデータは行や列を追加すると、テーブル範囲が自動で拡張されるので、基本的には参照している範囲を修正する必要がありません。

しかしデータの更新がうまくできていないと、正しい計算結果が表示されないことがあります。

データの更新
データの更新

正しい結果が表示されなかったときは、「データ」タブの中にある「すべて更新」をクリックしてみてください。

エクセルの拡張子による影響

セルをクリックして入力しても列単位やあらかじめつけておいた名前で表示されないときは、エクセルの「拡張子」が影響している可能性があります。

紬ちゃん

「拡張子」って何ですか?

ライクさん

拡張子は、ファイルの種類を表す文字列のことだよ。

例えば、エクセルで作成したファイルを開くと、自動的にエクセルが立ち上がりますよね。

これは、パソコンが「拡張子によって、エクセルで作成された書類であることを判断してファイルを表示する」というしくみになっているためです。

現在のエクセルファイルの拡張子は「.xlsx」です。

エクセルで作成したファイルは、種類を変更せずに保存すると自動的に「.xlsx」という拡張子がつきます。

しかし、10年以上前のエクセルでは「.xls」という拡張子が使われていました。

そのため、現在のエクセルでも古いバージョンのエクセルとの互換性が考慮されており、「.xls」で保存することが可能です。

「xls」で保存することができる
「xls」で保存することができる

古い拡張子が使われていたのは10年以上前なので、現在は「.xls」で保存するケースはほとんどありませんが、ファイル形式が変更されたファイルを使うと構造化参照がうまくできないことがあります。

特定のファイルだけ構造化参照が使えないという場合は、使用しているエクセルファイルの拡張子を確認してみてください。

参考

エクセルを初期設定のまま使用している場合や会社で使用しているパソコンでは、拡張子が表示されない設定になっていることがあります。

エクスプローラーを起動して、表示の設定を変更すると「拡張子」を表示することができます。

エクスプローラーの画面
エクスプローラーの画面

エクスプローラーの画面を起動して、画面上部にある「表示」の下向き矢印をクリックします。

表示にマウスポインタを合わせて右側にスライドすると「ファイル名拡張子」が表示されるので、この部分をクリックしてください。

この状態で通常通りに操作を行うと、ファイル名や画像ファイルに拡張子が表示されるようになります。

ただし、拡張子の設定を変更すると、拡張子が表示されたままの状態に変更されます。

普段、拡張子を表示させずにパソコンを使用している場合は、作業が完了したら設定を元に戻しておきましょう。

エクセルのテーブルの参照に関するQ&A

エクセルのテーブルで絶対参照ができないのはなぜですか?

セルをクリックして計算式を入力せずに、「=B1*$A$1」のように数式バーに手入力するとテーブルでも絶対参照にすることは可能です。

ただし、絶対参照にしたいセルにあらかじめ名前をつけておき、その名前を使って計算式を入力すると、絶対参照にする必要がなくなります。

絶対参照を使わずに計算式を入力する方法と絶対参照にする必要がない理由は、本文内で詳しく解説していますので、参考にしてみてください。

エクセルのテーブルの構造化参照とは何ですか?

作成した表をテーブルに変換して、列番号や行番号を使って参照することを構造化参照といいます。

構造化参照で計算式を挿入する手順や、構造化参照とセル参照の違いは本文で紹介していますので参考にしてください。

表をテーブルに変換するにはどうしたらいいですか?

作成した表の範囲を選択して、「挿入」タブの中にある「テーブル」をクリックします。

テーブルを表に戻したいときは、テーブルの中の任意のセルをクリックしたあとに「テーブルデザイン」のタブをクリックしてください。

表示されたメニューの中にある「範囲に変換」を選択すると、表に戻すことができます。

表をテーブルに変換して構造化参照を活用してみよう!

表とテーブルは扱い方が違うので、慣れるまでは少し難しいと感じてしまう方もいらっしゃるかもしれません。

しかし、テーブルで参照する構造化参照には、「データを追加しても範囲を修正する必要がない」、「計算式を文字で表示できる」、「絶対参照の必要がなくなる」など便利な機能がたくさんあります。

日常業務の効率アップが期待できるので、ぜひ挑戦してみてください。

最後に、表をテーブルに変換して参照する方法をおさらいしておきましょう。

おさらい
  1. 作成した表をテーブルに変換
  2. 名前ボックスを使って、変換したテーブルと計算に使用するセルに名前をつけておく
  3. セル参照と同じように、セルをクリックして計算式を入力する

テーブルで参照する方法には、「構造化参照」という少し難しい名前がついていますが、正しい手順さえ覚えてしまえば、操作自体はそれほど難しくはありません。

構造化参照を活用して、効率よく入力ができる資料を作成してみてください。

構造化参照以外にも、エクセルの参照について解説した記事があります。

こちらもぜひ参考にしてみてください。

よかったらシェアしてね!
  • URLをコピーしました!
目次