Excel(エクセル）で表を取り扱う時に、最下行にSUM関数を挿入したり、フィルターを設定したり、色を調整したり・・・いろいろな手間がかかります。
そんな時に、「テーブル」機能を使うと様々な作業が効率化出来る可能性があります。
たとえば、「最下行に自動でＳＵＭ関数を挿入」「表のデザインを自在に変更」「列の数式が自動で挿入」などです。
この記事では「テーブル」の設定方法と1０のメリットを紹介します。
エクセルの表に「テーブル」を設定する方法
エクセルの表に「テーブル」を設定する方法を紹介します。
手順１．表を準備する
「テーブル」を設定するためには、まずは元データとなる表を用意する必要があります。
つまり、売上明細データや支払明細データや資産明細データなどの集計や分析したいデータのことです。
「テーブル」を正しく使うためには、元データに対して以下の4つの条件を整える必要があります。
【テーブルの元データの４つ条件】
①列項目に空白セルが無い
➁書式に統一性がある(全角、半角、カタカナなど）
③セルが結合されていない
④表に隣接したセルにデータが入っていない
準備する表の良い例としては、以下のような表です。
逆に元データの悪い例として、以下のような表です。
①～④の項目をすべて守っていないと「テーブル」を設定出来ないわけではありませんが、「テーブル」の様々な便利機能を利用するために、極力１セルに１データを挿入した整えられた表を準備しましょう。
手順２．「テーブル」を設定
元データを整え終えたら、「テーブル」を設定します。
元データの中であればどのセルでもいいのでクリックして選択してから(図①)、「挿入」タブの「テーブル」をクリック(図➁)します。
そうすると、下のような「テーブルの作成」画面が表示されますので、元データのデータ範囲(図③)が正しければ、「OK」をクリック(図④)します。この操作で表に「テーブル」が設定されます。
手順３．「テーブル」の設定の完了
表に「テーブル」が設定されたか確認しましょう。
表の任意のセルを選択すると「テーブルデザイン」タブが表示されていれば設定が完了しています。
また、表には「フィルター」が設定され、１行おきに色塗りされデザインも変わります。
「テーブル」を設定するショートカットキー
「テーブル」の設定にはショートカットキーが２つ存在しています。
表の任意のセルを選択した状態で、以下のどちらかのショートカットキーを利用することでテーブルを簡単に設定することが出来ます。
「Ctrl」キー＋「T」キー
「Ctrl」キー＋「L」キー
「T」がテーブル(table)の「T」と覚えましょう。
「テーブル」を使うメリットを紹介
上記で説明した通り「テーブル」の設定は簡単です。では、どうして表にわざわざ「テーブル」を設定するのでしょうか。
それには以下の見出しで紹介した１０のメリット(特徴）があるからです。
皆さんが個人で使っている表の使い方を思い出しながら、「テーブル」のメリットを確認してみてください。
範囲が自動で拡張される
「テーブル」を設置した表の最下行に１行追加するために文字を入力します。
文字を入力して「Enter」キーで確定すると、入力した行に罫線やセルの塗りつぶしの色などの書式設定が自動で行われます。
列も同様に、表に隣接したセルに文字を入力すると、自動で「テーブル」の表として反映され書式設定は拡張されます。
合計行を簡単に挿入出来る
「テーブル」を使うと、最下行に合計値を簡単に表示させることが出来ます。
「テーブルデザイン」タブの「テーブルスタイルのオプション」グループの「集計行」のチェックボックスにチェック(✓）を入れると、テーブルの右下に一番右の列の合計値が表示されます。
合計値が表示されたセルの数式は、「=SUBTOTAL(109,[販売数量6月])」とSUBTOTAL関数が挿入され、引数には[販売数量6月]という普段見慣れない引数の設定がされています。
これは、以降の見出しで紹介する「名前」という機能がテーブルを設定されたことによるものです。そのため、[販売数量6月]というのは、セル範囲(G3:G12)のことを指します。
左側のセルにも合計値を挿入したい場合は、SUBTOTAL関数が挿入されたセル(G23セル）の右下にカーソルを合わせて、マウスのポインターを十字マークに変えてください。
このポインターをマウスを使って、左側にドラッグすることで関数をコピーします。(オートフィルの利用）
オートフィルを使わずにG23 セルをコピーして左横のセルに貼り付けると、数式がすべて同じ引数になってしまいます。つまり、すべて同じ合計値が表示されてしまいますので注意してください。
集計方法をドロップダウンで指定できる
上の見出しで紹介した方法で「集計行」のチェックボックスにチェック(✓）を入れることで、集計方法は「合計」だけでなく、「平均」や「個数」「最大」など様々な集計方法を選択することが可能です。
変更可能な集計方法は以下のとおりです。
- なし(非表示）
- 平均
- 個数
- 数値の個数
- 最大
- 最小
- 合計
- 標本標準偏差
- 標本分散
- その他の関数(SUM関数やSUMIF関数など自由に選択可能）
最下行を「平均」で集計した場合は、表の左下のセル(B23セル）に、直接「平均」と入力します。
プルダウンを使って「平均」を選択すると、表全体に対して平均が算出され、下の図の様に文字列が挿入された表だと「♯DIV/0！」とエラーが表示されます。
また、「集計行」のチェックボックスにチェック(✓）を外して集計を非表示にした後に、再度チェックを入れると、集計方法は「平均」で表示されます。
テーブルで利用した最下行の「集計方法」は記憶されるということです。
列を追加すると自動でオートフィルがかかる
上の見出しで紹介したとおり表の右側に新しい列を追加すると、行と同じく書式が自動で拡張されます。
その機能に加えて、列に挿入した関数や数式にはオートフィルが自動でかかります。
どういうことか図解で説明します。
まず一番右の列に「合計」列を挿入するため、列タイトルを追加します。
そうすると、新しい列に書式(セルの色)が自動で拡張されます。
次に「販売数量４月」「販売数量５月」「販売数量６月」を合計するために、見出しの下の列に「SUM関数」を挿入します。
「Enter」キーでSUM関数を確定すると、同じ列の下のセルにもSUM関数が自動で挿入されます。
「テーブル」機能によりオートフィルが自動でかかるので、わざわざ数式をコピペする必要がないので時短に繋がります。
SUM関数以外の別の関数や数式を利用しても同様に数式が自動で拡張されます。
オートフィルについて使い方をおさらいしたい方は、以下の記事を参考にしてみてください。
「名前」機能が自動で設定(構造化参照)
「テーブル」を設定すると、表全体とテーブル内の各列に「名前」が割り当てられます。
「テーブル」に数式を追加する際、引数にセル参照を選ぶと、「名前」が関数の引数に自動的に表示され、どのセルを参照しているか分かりやすくなります。
どういうことか、 SUM関数を使って具体例を紹介します。
まずテーブルの下に「販売数量６月」列の合計値を表示するためSUM関数を挿入します。
＝SUM（
次に、合計するセル範囲を選択するため、B3セルからB9セルまでをドラッグして選択します。(図２）
そうすると、SUM関数の引数に自動で以下の名前が挿入されます。
=SUM(テーブル1[販売数量6月]
この状態で「Enter」キーで関数を確定すると、通常どおり合計値が算出されます。
このように、テーブルのセルを参照すると、「販売数量６月」列が参照されていることが分かりやすくなります。
表全体の「名前」と列の「名前」の組み合わせは、構造化参照と呼ばれます。 構造化参照の名前は、テーブルにデータを追加や削除、変更するたびに自動で調整されます。
構造化参照により、ブック内のテーブルを簡単に見つけやすくなるというメリットがあります。
ただ、このテーブルに使い慣れていないユーザーが、表の一部のセル範囲を参照して関数を挿入すると、関数の内容が分かりづらくなる場合もあるのでその例を紹介します。
テーブルの「栗」の「販売数量５月」の販売数量を抽出するため、J6セルにVLOOKUP関数を挿入します。
そうすると、関数は以下のようになります。
=VLOOKUP(I6,テーブル2[[#すべて],[商品名]:[販売数量6月]],3,FALSE)
第２引数は『テーブル2[[#すべて],[商品名]:[販売数量6月]]』となっており、構造化参照の構文規則に慣れていない方にとっては分かりづらい表示となっています。
『テーブル2[[#すべて],[商品名]:[販売数量6月]]』のそれぞれの構文の意味は以下のとおりです。
テーブル2⇒設定した表のテーブル名
[#すべて]⇒テーブルの特定の部分 (合計行など) を参照する特別な指定方法です。
[商品名]:[販売数量6月]⇒テーブルの「商品名」列から「販売数量６月」列までを４列のセル範囲を指定しています。
「フィルター」が有効化される
「テーブル」を設定すると自動で「フィルター」が設定されます。
『フィルター』機能を使うことで、表のデータを昇順や降順に並べ替えたり、任意の値を抽出することが出来ます。
今回はフィルターで「大阪店」のデータだけを抽出する方法を紹介します。
まず、「フィルター」各見出しのセルの「▼」をクリックします。
フィルターのメニューリストが表示されるので、「大阪店」だけにチェックを入れて「OK」を押します。
そうすると、フィルターのマークが変わり「大阪店」の２行のみが抽出されます。
「フィルター」を利用すると昇順や降順にも並べ替えすることが可能です。以下の記事でフィルターの詳しい使い方を紹介していますので、合わせて参考にしてみて下さい。
スライサー機能で集計が楽に
テーブルを設定した表には「スライサー」機能が利用出来るようになります。
「スライサー」機能とは、テーブルのデータを簡単に一瞬でフィルタリングできる機能です。
この「スライサー」の使い方を紹介します。
まずテーブル内の任意のセルをクリックします。すると、「テーブルデザイン」タブが表示されるので、「スライサーの挿入」をクリックします。
「スライサーの挿入」画面が表示され、テーブルの列項目の一覧が表示されます。
今回の説明では「販売店舗」と「商品名」の売上高を詳しく確認するため、「販売店舗」と「商品名」にチェックを入れて「OK」を押します。
そうすると、「販売店舗」と「商品名」の２つのスライサーが表示され、各店舗のスライサーボタンと各商品名のスライサーボタンが表示されます。
このボタンが薄い青色の時は、対象のデータがテーブルに表示されている状態です。
この２つスライサーを使って、「大阪店」の「バナナ」の売上高を抽出してみます。
まず、「販売店舗」のスライサーから「大阪店」をクリックします。そうすると、それ以外のスライサーボタンは白色に変わり選択OFFの状態に変わります。
「商品名」のスライサーも同時に変化し、「大阪店」で存在する「バナナ」と「リンゴ」のボタンだけが押せる状態となります。
最後に「商品名」のスライサーの「バナナ」をクリックすると、「大阪店」の「バナナ」だけをテーブルに表示することが出来ます。
このように「スライサー」機能を利用すると、「フィルター」より簡単にデータを抽出することが可能となります。ピボットテーブルでも同様にスライサーを利用出来るので、合わせて参考にしてみて下さい。
テーブルのデザインを自在に変更
「テーブル」を設定すると、デフォルトのデザインが適用され、表は１行置きに色塗りされたデザインになります。
このデザインは消したり、変更したり、ユーザー設定して自分の好みに調整したりすることが可能です。
デザインを変更する
テーブルのデザインを変更する方法を紹介します。
「テーブルデザイン」タブの「テーブルスタイル」グループの下矢印をクリックしてください。(図１)
テーブルデザインの一覧が表示されるので、変更したいデザインを選択します。(図２)
そうすると、下の図のようにデザインを簡単に変更することが出来ます。
デザインを消す
デフォルトで設定される１行おきの色塗りを消したい場合は、「デザイン」を消すことが出来ます。
「テーブルデザイン」タブの「テーブルスタイル」グループの下矢印をクリックし、メニュー一覧の最下行にある『クリア』をクリックしてください。
そうすると、「テーブル」機能は表に残したまま、デザインだけを削除(クリア）することが出来ます。
「ユーザー設定」したデザインを使う
「テーブルデザイン」を自分の好みで合わせてユーザー設定することが出来ます。
但し、ユーザー設定したブックと異なるExcelブックでは利用出来ないので登録には注意が必要です。
「テーブルデザイン」タブの「テーブルスタイル」グループの下矢印をクリックし、メニュー一覧の最下行にある『新しいテーブルスタイル』をクリックしてください。
「新しいテーブルスタイル」画面が表示されますので、設定するデザインの「名前」を設定します。(図２)
次に、設定したいテーブル要素を選択(図３)してから、「書式」をクリック(図４)します。
「セルの書式設定」画面の「塗りつぶし」タブの「その他の色」をクリックし、設定したい色を選択します。
「セルの書式設定」画面に戻るので「OK」を押します。「新しいテーブルスタイル」画面に戻るのでこちらも「OK」で書式設定を確定させます。
「テーブルデザイン」タブの「テーブルスタイル」グループの下矢印をクリックすると、メニューの一番上に「ユーザー設定」したデザインが表示されるので、選択すれば利用が可能となります。
「重複」データの削除が可能
「テーブル」に重複データが存在している場合、重複行を簡単に削除することが可能です。
重複データとは、下の図の８行目と９行目のように各列のデータが同じ値となってる行のことを言います。
まず「テーブルデザイン」タブの「重複の削除」をクリックします。
「重複の削除」画面が表示されるので、すべての列項目にチェックが入っている状態で「ＯＫ」を押します。
「１個の重複する値が見つかり、削除されました」とポップアップ画面で表示され、重複行が削除されます。
「重複の削除」画面のチェックする項目を変えることで重複の条件を変更することが出来ますが、この機能は重複行の削除前の事前確認が行えず、いきなり行が削除されてしまいます。
そのため、重複データを抽出して事前確認したい場合は、以下の記事で方法を紹介していますので参考にしてみて下さい。
「Ctrl＋A」キーで見出し行以外を選択
「テーブル」のセル範囲の中で「Ctrl＋A」キーの「すべてを選択するショートカットキー」を利用すると、見出し行を除いたセル範囲を選択することが出来ます。
「Ctrl＋A」キーをもう一度押すと、見出し行も含んだセル範囲を選択することが出来ます。
テーブルを解除する方法
「テーブル」を解除する方法を紹介します。
「テーブルデザイン」タブの「範囲に変換」をクリックするとテーブルを解除することが出来ます。
テーブルを解除してもテーブルのデザイン(セルの塗りつぶしの色など）は残ったままです。
色を消したい場合は、解除後にセルの塗りつぶしの色で色を削除するか、テーブルを解除する前に「テーブルスタイル」グループの「クリア」を使って書式を削除してください。