「ピボットテーブルの作成」で、ソースデータは「テーブルとして書式設定」しておくと良いと解説しました。ここではExcelのテーブル機能について詳しく解説します。
Excelは表計算ソフトの代表的なものです。表計算ソフトとは、文字通り、事柄や数値の関係を簡潔に理解できるように列を揃えて並べた「表」を作成して、データの集計、分析などを行なうためのソフトウェアで、英語では「Spread Sheet(集計表の意味)」と呼ばれているソフトウェアの総称です。
英語では表をTableと言いますが、Excelでいうテーブルは、ただの表のことではありません。
Excelでは、ワークシート内に作成した表を、関連するデータのまとまりとして扱いやすくしたものを「テーブル」と呼びます。別に表をテーブルに変換しなくても計算などは普通に行なえ、関数やフィルターなどを使えば、自動集計やデータの選別なども自由に行うことができますが、「テーブル」として扱うと、以下に解説するように、さらに便利なことが多いのです。
設定方法は簡単ですので、リスト形式の一覧表として管理する場合には、できるだけ「テーブル」に設定しておくことをお勧めします。
テーブルの設定は、通常、ある程度データ入力がされている表をもとに、テーブル形式に変換するという方法で行います。
つまり、先頭行には各列に項目名が、また2行目以下には、少なくとも1行分は、文字か数値のデータが入力されている行が続いている表が作成済みであることが前提です。データ行の追加や項目名(列)の追加は後でもできますので、最小限の表でも構いません。
テーブル形式への変換には2通りの操作方法があります。
Excelには「テーブルとして書式設定」という機能が用意されています。範囲を指定しておいて、一覧の中から好みの表イメージを選ぶと、自動的に罫線やセルに色付けしたスタイルにしてくれるというものです。
具体的には、どこでも構いませんので、表内のひとつのセルを選択しておいて、「ホーム」タブの「テーブルとして書式設定」アイコンをクリックすると、自動的に入力済みのデータ全体が選択されて、テーブルのレイアウト一覧が表示されます。
好みの罫線や色の取り合わせのスタイルのものを選んでクリックすれば、表全体がひとつのテーブルとして設定されます。
【便利知識】
ひとつのセルでなく、表の一部あるいは全体の複数セルを選択して、「ホーム」タブの「テーブルとして書式設定」アイコンをクリックした時は、その選択した範囲でテーブル設定されます。
表内の任意のセルを選択しておいて、「挿入」タブの「テーブル」アイコンをクリックする方法でも、表全体をテーブル形式に変換できます。
この場合は先にセル範囲を選択すればその範囲でのみテーブル化されます。
【便利知識】
通常は先にテーブルの見出し行(項目名が並んだ行)を作成しておきますが、データ行しかない表の場合は、「テービルの作成」ダイアログボックスで「先頭行をテーブルの見出しとして使用する」のチェックを外せば、自動的に「列1」「列2」・・などの項目名の見出し行が挿入されてテーブル化されます。
テーブル内のセルを選択すると「テーブルデザイン」タブが出現します。
「テーブルデザイン」タブでは、テーブル名の設定、スタイルの変更、スタイルオプションの選択などが行います。
設定されたテーブルには自動的に「テーブル名1」などのテーブル名が登録されます。
「テーブルデザイン」タブの左端にある「テーブル名」欄で任意のテーブル名に変更できます。
【便利知識】
テービル内のセルを選択して、「名前ボックス」欄右の▼マークをクリックすることでも、テーブル名が確認できます。
「テーブルデザイン」タブの右側には、「テーブルスタイル」のボックスがあり、罫線や色使いのスタイルを後から変更することができます。
「テーブルデザイン」タブの中央付近には「テーブルスタイルのオプション」グループ欄があり、細かな設定ができるようになっています。
テーブル設定された時点では「見出し行」「縞模様(行)」「フィルターボタン」の3ヵ所にチェックが入っています。
「見出し行」のチェックを外すと、見出し行が非表示になります。
「縞模様(行)」のチェックを外すと行に縞模様のあるスタイルの縞模様がなくなります。行ごとに横罫線のあるスタイルの場合は横罫線が消えます。(縞模様と横罫線ともある場合は縞模様が消え、横罫線は残ります。)
「フィルターボタン」のチェックを外すと「フィルターボタン」が消えて、「フィルター」機能が利かなくなります。
「集計行」にチェックを入れると最下行に集計行が表示されます。
「縞模様(列)」にチェックを入れると、1列置きごとに列に縞模様が付きます。
「最初の列」「最後の列」にチェックを入れると、それぞれの列が強調表示(太字化)されます。
右図は、すべてのオプションにチェックを入れてみた例です・
テーブルの設定を解除したい場合は、テーブルの中のセルを選択した状態で、「テーブルデザイン」タブの 「範囲に変換」をクリックします。デザインはそのままですが、テーブルとしての設定は解除されます。
【便利知識】
表のデザインも戻したい場合は、表全体を選択して、「ホーム」タブの「クリア」アイコンをクリックして、「書式のクリア」を選択します。
テーブルを設定するとテーブル機能が有効になり、様々なメリットが生じます。大別すると、書式設定や表の範囲などの「表の管理が楽になる」という点と、列の項目名(列名)を使用した計算式や行追加の際の計算式のコピー設定など「計算式の入力が便利になる」という点です。
フィルター機能が有効になる
既存の表をテーブルに変換すると、自動的に見出し行の各セルにフィルターボタン(▼)が追加され、フィルター機能が働くようになります。
表のデザイン(書式)が素早く設定できる
見出し行のセルの色やフォントの色、1行ごとのデータ行の色分け、罫線の有無、表全体の色合いなど、テーブルデザイン欄にリストされているスタイルを選ぶだけで表のデザインが決まります。また、スタイルオプションの選択をすることで細かなデザイン調整も簡単に行なえます。
見出し行の項目名がきちんと埋まる
見出し行に項目名の無いセルがあれば【列1】のような項目名が付加され、ピボットテービルで扱える形になります。
テーブルの範囲が自動的に拡張・縮小される
行や列の追加・削除に応じて、テーブルの範囲は自動的に拡張・縮小されます。
フィルター範囲もいちいち再設定する必要がありません。
追加行の書式は自動設定される
最終行の次の行にデータを入力すると、上方の行に入っている書式を参照して書式が自動で設定されます。
右図は、セルA24に番号だけ入力した例です。縞模様になるかどうかも自動判断されて設定されます。
追加行にはデータの入力規則も自動設定される
テーブルでは、データの入力規則も自動的に引き継がれます。
右図の例では、摘要(D)列には、「摘要リスト」シートにあるリストから選ぶような入力規則が設定されています。行を追加した時には、この入力規則が新しい行にも自動設定されます。
列の追加でもテーブル範囲が拡張される
右図は、セルG1に新たな項目名を入力した例です。
テーブルの範囲が拡大され、新たな項目名にもフィルターボタン(▼)が付いていることがお分かりと思います。
各セルの表示形式と横配置はともに「標準」で設定されます。
オプションで集計行を設定した場合、集計はSUM関数ではなく、SUBTOTAL関数の集計(109)が用いられます。
これにより、フィルターを使って一部データに絞り込んだ時に、絞られたデータのみで集計されたものが表示されます。
計算式が見やすくなる(構造化参照)
通常は「=F2」というように、セル番号でセル参照しますが、テーブル内で、同じ行のセルをマウスで選択して参照すると、「=[@金額]」というように、その列の列名(項目名)によるセル参照になります。これは同じ行のその項目の値を参照するという意味ですので、行番号は付きません。
このような列名を用いた参照方式を「構造化参照」(詳細後述)と呼びます。
よく見られる例として、単価、数量、金額という列があって、金額=単価x数量の場合、金額の列には、「=[@単価]*[@数量]」という形で、計算式をわかりやすく示すことができます。
同じ行でないセルを選択して参照した場合は、普通のセル参照と同じ「=F3」のようにセル番号での表示になります。
なお、マウスによる選択ではなく、セル番号を手入力した場合には、「=F2」のように通常の表示がされます。
計算式を一気に入力できる
いずれかのセルに計算式を入力すると、すべてのデータ行のその列のセルに計算式がコピーされます。いちいちドラッグしてコピーしなくても良いので、特に大量データの表では便利です。
【便利知識】
計算式の入力は、必ずしも先頭のデータ行でのセルでなくても構いません。テーブル内のデータ行であれば、どのセルで行っても、先頭行から末尾行まで計算式がコピーされます。
計算式の変更も一気に入力できる
あるセルの入力済みの計算式を変更した時も、すべてのデータ行のその列のセルに変更後の計算式がコピーされます。
右図は、「金額+前の行の残高」で新たな残高を算出する目的で、G3のセルにその計算式(=[@金額]+G2)を入力しているところです。
「Enter」すると、計算式(「=[@金額]+G3」など)が残高(G)の列にコピー入力されましたが、先頭データのG2のセルも「=[@金額]+G1」の計算式に置き換わり、結果としてエラー(#VALUE)となってしまいました。
先頭行のG2セルの計算式を別の計算式に変えると、その列の式がまたすべて変わってしまいます。
右図の例では、2番目以降のデータ行の計算式を生かしたまま、「#VALUE」のエラーを解消するために、G2のセルは、数値データに置き換えました。
【便利知識】
すべてのデータ行のその列のセルに計算式が入力されていることが条件です。計算式ではなく空白であったり、数値だけのセルが混じっている場合には、コピー入力はされません。
ちなみに、残高の列のデータが最初は空白で、まず、G2に数値データを入力して、G3に「=[@金額]+G2」を入力した時は、G4以降は空白のままです。また、右図のようにG2に数値データ、以降のG列のセルには計算式が入力済みの場合、例えばG3のセルの計算式を変更しても、G4以降の計算式は元のままです。
追加の行には、計算式が自動反映される
右図は最終行の下に番号だけを入力した状態の例で、書式とともに、計算式も自動的に反映されています。
表をテーブル化すると上述のように数々のメリットがありますが、何と言っても一番便利なのは、ピボットテーブルや、VLOOKUP関数などを使う際に、テーブル全体あるいはテーブルの一部のデータなどを参照する際に、テーブル名を含む構造化参照で呼び出すことができるということです。
前提として、「数式でテーブル名を使用する」ことができなければなりません。
まずは、「ファイル」タブの「その他のオプション」⇒「オプション」で、「Excelのオプション」ダイアログボックスを出します。サイドメニューの「数式」をクリックし、「数式の処理」の中にある「数式でテーブル名を指定する」にチェックが入っていることを確かめましょう。
テーブル全体を呼び出す
別のシートで、テーブル全体を呼び出してみましょう。
別シート上のいずれかのセルで「=」を入力してから、テーブルのあるシートに戻り、テービル全体を選択して、「Enter」キーを押します。
そうすると、右図のように、色や罫線などの書式設定はされていないものの、テーブル全体のデータがコピー表示されます。
コピーされたテーブル内のどのセルを選択しても、
「=収入2021[#すべて]」
となっています。ここでの「収入2021」はテーブル名です。
[便利知識]
別ブックのシートから、テーブルを呼び出すこともできます。この場合は、
「=ピボットテーブル.xlsx!収入2021[#すべて]」
のように、テーブルのあるブックの「ファイル名!」が前に付きます。
テーブルのデータのみを呼び出す
テーブル名だけで参照すると、見出し行や集計行を除いた(つまり、データ行だけの)テーブルデータがコピー表示されます。
「=収入2021」
テーブルの一部の列データのみを呼び出す
テーブル名の後ろに列名(このケースでは連続する複数の列名)を付けて参照すると、指定した列のデータがコピー表示されます。
「=収入2021[[摘要]:[金額]]」
特殊項目指定子を用いて、見出し行や集計行のデータも参照できます。
テーブルの見出し行の項目名を呼び出す
「=2021収入[#見出し]」
テーブルの集計行の列「金額」のデータを呼び出す
「=2021収入[[#集計],[金額]]」
【便利知識】
テーブル外のセルで、テーブル内のデータ行内のセルを個別に参照するときは、通常のセル参照になります。
別シートのセルで参照を行うと、セル番号の前に「シート名!」が付きます。
データ行の連続するセルを選択して参照することもできますが、個別の編集がしづらいので、ひとつのセルのみを選択して参照し、それをドラッグで広げることをお勧めします。
合計や平均などの関数でテーブルデータを参照する
=SUM(収入2021[金額])
=AVERAGE(収入2021[個数])
IF関数やSUMIF関数でテーブルデータを参照する
=SUMIF(収入2021[摘要],"ホール 賃貸料",
収入2021[金額])
列の項目名(列名)を用いた参照方式を構造化参照と呼ぶことを前述しました。上述の具体例で記述した参照方式もすべて構造化参照の形式です。もう少し詳しく構造化参照について解説します。
右図は、J3のセルを選択しておいて、「Σ」ボタンをクリックしてから、F2からF24まで選択した結果の図です。つまりF2からF24までの合計を求める計算式をJ3のセルに入力する操作を行ったということです。
表がテーブル化されていなければ、
=SUM(F2:F24)
のように表されますね。
この図のように、表がテーブル化されていると、
=SUM(収入2021[金額])
となっていることがお分かりと思います。
ここで、「収入2021」はテーブル名です。「金額」は列名(フィールド名とも言います)です。列名は鍵括弧 [ ] で囲われています。
このようにテーブル名と列名で表す参照形式を構造化参照と呼びます。
【便利知識】
テーブル化されていると、列上の見出しを除くセル全体(つまりデータのセルのみ)を、1タッチで選択することができます。
右図のように、項目名のセルの上の枠にマウスオーバーして、黒の下矢印が現れたら、クリックすれば良いのです。
ドラッグするなどで選択する方法に比べて、間違いも無く、操作が簡便なので、おすすめです。
テーブル名
構造化参照を使うときは、最初にテーブル名を記述します。テーブル名がないとどのテーブルについて記述しているかわからなくなってしまうためです。
テーブル内のセルで、同じテーブル内の別セルを参照する時は、テーブル名を省略することができます。
列指定子
見出し行と集計行を除く(つまりデータ行の)特定の列のセルを参照するもので、[金額]のように列名を鍵括弧 [ ] で囲む形で記述します。
特殊項目指定子
見出し行や集計行など、テーブルの特定の部分を参照するためのものです。特殊項目指定子には以下のものがあります。特殊項目指定子も鍵括弧 [ ] で囲みます。
項目指定子 (日本語表記) |
項目指定子 (英語表記) |
参照先 | 例 |
参照する セルの範囲 |
#すべて | #All |
テーブル全体 (見出し行、集計行を含む) |
=収入2021[[#すべて],[金額]] | =H1:H25 |
#データ | #Data |
データ部分のみ (見出し、集計以外) |
=収入2021[#データ] | =A2:I24 |
#見出し | #Headers | 見出し行のみ |
=収入2021[[#見出し],[摘要]:[金額]] |
=D1:H1 |
#集計 | #Totals |
集計行のみ (集計行が無いときは null が返される) |
=収入2021[[#集計],[金額]] | =H25 |
@ または #この行 |
@ または #This Row |
数式と同じ行のセルのみ | =[@単価]*[@個数] | =F2*G2 など |
【便利知識】
「@」付きと無しの違い
列名の左横に付くアットマーク「@」は、参照元と参照先のセルが同じ行にある「ひとつのセル」を選択する時に付きます。列名の前のアットマーク「@」を付けない時は、見出し行と集計行以外(つまりデータ行)の全セルが選択範囲になります。
そのほか、ここで詳しくは触れませんが、テーブルには次のような機能があります。