Excelのテーブル機能を使おう

Modern Excelの土台となるExcelのテーブル機能について説明します。機能自体はExcel 2007からあるのですが、まだまだ普及しきっていない感じですので。機能だけではなく、なぜそうするのかについても説明します。

Excelのテーブルとは

Excel 2007から導入されたデータ格納のための機能です。見出し+データとなっているセル範囲をテーブルとして設定すると、後述のさまざまな機能が使えるようになります。

この節ではなぜデータをテーブルとして格納するのかについて説明します。

Excelでのデータ処理

Excelのシートはかなり柔軟に使えますが、そのために本来分けるべきものまでぐちゃぐちゃに混ぜ込まれてしまいがちです。一度立ち止まって、**作業内容を入力、計算、格納、帳票に分けてみましょう。**きちんと分けた上でそれぞれに最適な機能で対応すれば、シンプルでわかりやすく、効率的なワークブックになります。

この中で、テーブル機能はリスト形式によるデータ格納を担当します。

行列覚えられない

Excelの行と列について、どっちが縦でどっちが横か覚えていない人はこの際覚えてください。横方向が行、縦方向が列です。覚え方は、漢字で水平または垂直に二本棒がある方向です。
⇒横方向が行
⇒縦方向が列
https://office-hack.com/excel/line-column-difference/

「行列なんて常識でしょ。そんなの知らない人がいるの？？」と小馬鹿にしたそこのあなた！あなたは手羽先の左右がどっちかわかっていますか？私にはわかります。太い方を上にしたときに右キーの形になる方が右手羽、左キーの形になる方が左手羽です。こんなの常識です（「丸鶏」で画像検索）。なお、味の違いはありませんし、手羽元の左右の見分け方は研究中です。

リスト形式とは

リスト形式とはデータの持ち方の一つです。他にはクロス集計などがあります。具体的な例で見てみましょう:

クロス集計の表

地点	12/3	12/4	12/5
札幌	☁	☁	☃
東京	☂	☀	☁
福岡	☀	☁	☀

リスト形式の表

地点	時点	天気
札幌	12/3	☁
札幌	12/4	☁
札幌	12/5	☃
東京	12/3	☂
東京	12/4	☀
東京	12/5	☁
福岡	12/3	☀
福岡	12/4	☁
福岡	12/5	☀

リスト形式は、以下の特徴をもつデータ保持方法です（これといった定義が無さそうなのでひとまずこのようにします）:

• 行が観測、列が変数になっている
• 1行目は変数名、2行目以降にデータ本体
• 同じ列には同じ型のデータ
• 1つのセルに1つのデータ

リスト形式は、それを元に様々な加工を行うことを目的としたデータの持ち方です。ここでいう加工とは、新しいデータを計算したり、レポーティングのために見栄えの良い表やグラフを作る事を意味しています。

クロス集計はレポーティング用の表で、データ格納には適していません。データとしてはリスト形式で保持して、それを元にクロス集計を作成します。

こちらにもいろいろとためになる事が書かれていますので、ご参考まで。

Excelのテーブル機能

改めて説明すると、Excelのテーブル機能とは、見出し+データとなっているセル範囲をリスト形式で保持するものです（リスト形式の定義の3点目は必ずしも守られませんが）。そして、データ処理のうち、データの格納を担当します。このテーブル機能を元に、データ入出力を担うPower Queryや集計や図表作成を担うPower Pivotが動くように作られています。次の機能への橋渡しをしているということです。

テーブルでは、整然データとなるようにデータを持たせます（行=レコードが観測、列が変数）。リスト形式の定義がほぼ整然データの定義なので、あまり意識する必要はありませんが、その言葉や考え方は覚えておくと、ワークブックの設計の段階で役に立ちます。参考:整然データとは何か

テーブルは後続の処理で見やすく加工されることを前提としているため、テーブル自体を最終的なアウトプットである帳票として使用することはありません。

また、ワークブック間でのデータの授受はテーブルで行ってください。帳票では行いません。帳票は見た目はきれいですが、再利用しにくいことがほとんどです。うまい例えではないですが、帳票は見た目きれいに過剰包装されたお菓子、テーブルはお菓子本体です。食べる側はお菓子本体があれば十分です（きれいな包装が気に入ったらそれも欲しくなりますが）。

テーブルにする例/しない例

いくつかの例でテーブル化するかどうか考えてみましょう。▶（答え）をクリックして答えを確認してください。

お天気のデータ

地点	12/3	12/4	12/5
札幌	☁	☁	☃
東京	☂	☀	☁
福岡	☀	☁	☀

（答え）

これはテーブル化しません。クロス集計になっているためです。

ガンダムのデータ

名称	ガンダム
型式番号	RX-78-2
頭頂高	18.0m
本体重量	43.4t
全備重量	60.0t
装甲材質	ルナチタニウム合金
出力	1,380kW
推力	55,500kg
センサー有効半径	5,700m
最高速度	165km/h

（答え）

これはテーブル化しません。列が観測なっているためです。

地域別支店別売上

地域	支店	売上
関東	新宿	1,000
	渋谷	900
	横浜	1,200
関西	難波	800
	神戸	800
中国	広島	700

（答え）

これはテーブル化しません。セルが連結されているからです。

（表の連結はMarkdownではなくHTMLで書くんですね🙃）

数学の行列

\left(
\begin{matrix}
11 & 12 & 13 \\
21 & 22 & 23 \\
31 & 32 & 33 \\
\end{matrix}
\right)

（答え）

これはテーブル化しません。行が観測、列が変数という形ではなく、この行列自体で一つの値になっているためです。

タスク一覧

#	タスク	担当	困難度
1	父ちゃんと同じトレーニング	堀口元気	★
2	アッパーストレート習得		★★
3	関拳児に勝つ		★★★
4		シャーク堀口	★
5	堀口君に会う	石田ともこ	★
6	アイドルになる		★★
7	プロで一勝する	山谷勝三	★★

（答え）

これはテーブル化しません。セルが連結されているからです。
「ただ殴るのではなく、自分自身に向き合い、かつ相手を殴らないといけない。深い」

プリキュア一覧

名前	色	シリーズ	年
キュアミラクル	ピンク	魔法使いプリキュア!	2016
キュアホイップ	ピンク	キラキラ☆プリキュアアラモード」	2017
キュアエール	ピンク	HUGっと!プリキュア	2018
キュアマシェリ	赤	HUGっと!プリキュア	2018
キュアアムール	紫	HUGっと!プリキュア	2018
キュアスター	ピンク	スター☆トゥインクルプリキュア	2019
キュアミルキー	青緑	スター☆トゥインクルプリキュア	2019
キュアグレース	ピンク	ヒーリングっど♥プリキュア	2020
キュアサマー	白＋虹？	トロピカル～ジュ!プリキュア	2021
キュアコーラル	紫	トロピカル～ジュ!プリキュア	2021
キュアプレシャス	ピンク	デリシャスパーティ♡プリキュア	2022
キュアフィナーレ	金	デリシャスパーティ♡プリキュア	2022

（答え）

どう見てもテーブルです。本当にありがとうございました。

機能説明

以下、テーブルの機能を説明します。

• テーブルの作成
• 名前付け
• 見出しフィルター
• しましまスタイル
• オートフィル
  • 範囲
  • 数式
  • 書式、条件付き書式、データの入力規則
• 数式が読みやすくなる（構造化参照）
• セル移動/選択
• Power Queryから読み込めるようになる
• VBAではListObjectとして利用可

テーブルの作成

テーブルにしたい範囲を選択した状態で、リボンのホームタブ -> テーブルとして書式設定（ショートカット キーはCtrl + T）で、テーブルを作成します。

名前付け

テーブル内の任意のセルを選択すると、リボンにテーブルツール デザインタブが追加されます。リボンのテーブルツール デザインタブ -> テーブル名: テキスト ボックスから、テーブル名を変更できます。
わかりやすい名前にしておくと、テーブル外から参照する場合やPower Queryで取り込む際に便利です。セル式に付けた名前と同様に名前の定義（リボンの数式タブ -> 名前の管理）で管理されますので、ひと目でテーブルと分かるよう「T_」等をプレフィックスとして付けることをお勧めします（セル式のプレフィックスは「N_」）。

見出しフィルター

見出し行に自動的にフィルターが付きます。リボンのテーブルツール デザインタブ -> フィルターボタン チェックボックスから、フィルターのON/OFFを切り替えられます。

しましまスタイル

テーブルにするとだいたいシマシマになって、おしゃれな感じになります。このしましまは着せ替えが可能で、リボンのホームタブ -> テーブルとして書式設定 または リボンのテーブルツール デザインタブ -> テーブル スタイルから好きなものを選べます。基本しましまですが、しましまではないものもあります。
関係ないですが、男の子の幼児服はだいたいしましま、車、星で、女の子に比べておしゃれのバリエーションが少ないと誰かが言っていました。

オートフィル

以下のように、テーブルの拡張に合わせていろいろなものも自動的に拡張されます。

範囲（行・列）

テーブルの最下行にデータを入力すると、テーブルの範囲が拡張され、その行もテーブルに含まれます。右端（見出し行の横でもデータ行の横でも）にデータを入力した場合も同様にテーブルの範囲が拡張されます。
ピボット テーブルのデータ元にする場合にも便利です。

数式

テーブル内で数式を入力すると、自動的に同じ列にコピーされます。上記の新しい行の追加があれば、その行にも数式がコピーされます。これは便利。もしも特定の行だけ別の数式にしたい場合には、オートフィルされた直後にCtrl+Zで取り消します（多用はおすすめできませんが）。逆に、オートフィルが効いていない時には、Ctrl+Spaceで列全体を選択し、再度数式を入力します。

書式、条件付き書式、データの入力規則

列全体に対して書式、条件付き書式、データの入力規則を設定すれば、数式と同様増えた行にも適用されます。

数式が読みやすくなる（構造化参照）

数式でテーブルの範囲やセルを利用すると、構造化参照機能でとても見やすくしてくれます。

例えば、以下の表に正規分布の確率密度関数の列を追加する場合:

表

μ	σ	x	確率密度関数
0	1	-1.0	追
0	1	-0.5	加
0	1	0.0	す
0	1	0.5	る
0	1	1.0	よ

正規分布の確率密度関数

確率密度関数

f(x)=\frac{1}{\sqrt{2 \pi\sigma}}e^{-\frac{1}{2}\left(\frac{x-\mu}{\sigma}\right)^2}

データをテーブル化せずに数式を入力するとこんな感じですが

# セル参照
= 1/SQRT(2*PI()*B2)*EXP(-1/2*((C2-A2)/B2)^2)

データをテーブル化してから数式を入力するとこうなります

# 構造化参照
= 1/SQRT(2*PI()*[@σ])*EXP(-1/2*(([@x]-[@μ])/[@σ])^2)

単独のテーブルだと同じ画面内に参照元があるので見やすくなる効果は薄いかもしれません。ですが、別シートにあるテーブルのように同じ画面内に参照元が無い場合には、かなり違います。別のテーブルからXLOOKUP関数やINDEX関数で値を取ってくる例を見てみましょう。

「T_夏秋キュウリ」テーブル

市町村	年	出荷量（t）
伊達市	2016	5,900
須賀川市	2016	5,740
深谷市	2016	4,600
二本松市	2016	3,540
山形市	2016	2,870
…	…	…

「T_都道府県」テーブル（「都道府県」シートに作成）

市町村	都道府県
伊達市	福島県
須賀川市	福島県
深谷市	埼玉県
二本松市	福島県
山形市	山形県
…	…

「T_夏秋キュウリ」テーブルに「都道府県」列を追加します。テーブル機能を使わない場合は

# セル参照
# XLOOKUP関数
= XLOOKUP(A2, 都道府県!A:A, 都道府県!G:G)

# INDEX関数
=INDEX(都道府県!G:G, MATCH(A2, 都道府県!A:A, 0))

テーブル機能を使う場合は

# 構造化参照
# XLOOKUP関数
= XLOOKUP([@市町村], T_都道府県[市町村], T_都道府県[都道府県])

# INDEX関数
=INDEX(T_都道府県[都道府県], MATCH([@市町村], T_都道府県[市町村], 0))

おわかりいただけただろうか。

また、数式入力時にインテリセンスが働くのもいちいち画面を切り替えずに済むので地味に便利です。

セル移動/選択

Ctrl+Down（カーソルを最下行へ移動）がテーブルの範囲で効くので使いやすくなります。値が無くてもテーブルの最下行で止まるので、新しい列を追加した後の操作で便利です。
また、セル選択も同様にテーブルの範囲で効きます。Ctrl+A（範囲選択）は一度押しでテーブルの見出し以外、二度押しでテーブル全体、三度押しでシート全体を選択します。Ctrl/Shift+Space（列/行選択）は一度押しでテーブルの列/行を選択、もう一度押してシートの列/行を選択します。

Power Query/Power Pivotから読み込めるようになる

テーブル化することでPower Query/Power Pivotから読み込めるようになります。Power Queryの場合はシートのままでも可能ですが、読み込み時に余分な手間がかかるため、テーブル化しておきましょう。

VBAではListObjectとして利用可

（あまりVBAを使いたくないので）ここでは深入りはしませんが、テーブル化しておくとVBAでも構造的に扱うことができます:

'先の「T_夏秋キュウリ」テーブルから出荷量の合計を計算
'セル範囲や列の番号ではなく、テーブル名や列名で書くことができる
Sub sumKyuri()
    Dim lo as ListObject, lrow as ListRow, total as Double

    Set lo = Activesheet.ListObjects("T_夏秋キュウリ")
    For Each lrow in lo.ListRows
        total = total + Intersect(lrow.Range, lo.ListColumns("出荷量（t）").Range)
    next
    debug.print total
End Sub

参考

Excelテーブル（ListObject）化のススメと 良く使うListObject メンバ一覧