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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

表をテーブルに変換する

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

参考

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

構造化参照のメリット

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

構造化参照のデメリット

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

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

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

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

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

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

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

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

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

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

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

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

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

セル番地で指定している

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

参考

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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