えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

トップ > Excel > なぜExcelでテーブルを使うのか。なぜテーブルを使えないのか

なぜExcelでテーブルを使うのか。なぜテーブルを使えないのか

Excel

普段、私はテーブルを好んで使用しています。テーブルを使う理由、テーブルを使わない理由について、個人的に思いつく限りのことを書いてみました。

個人的な解釈に基づいた表現をしており、見方によっては評価が逆転することもある点にご注意ください。

テーブル化するメリット

良い感じに着色とオートフィルタが設定される（好みによる）

ご覧の通り、行を交互に着色して周りに罫線が入ってデータ範囲が明確になります。※縞模様（行）縞模様（列）のチェックボタンでON/OFFできます。

加えて、並べ替えや絞り込みに便利なオートフィルタが自動で設定されます。※後で消してもOK

なお、テーブルの縞模様はセルの書式設定の塗りつぶしより背景に描画されるため、普通の塗りつぶしで上書き可能です。

ウィンドウ枠の固定が不要（好みによる）

テーブル内にカーソルがある状態でヘッダ部分（先頭行）が見えないところまでスクロールすると、上部の列番号（A,B,C,D）が自動的にテーブルの列名に変化します。

そのため、わざわざウィンドウ枠の固定をしなくても列名がいつでも確認できます。

スライサーが設定できる

テーブル内にカーソルがある状態で、リボンのテーブルデザインからスライサーの挿入を選ぶことで、ワンクリックでテーブルの絞り込みが実行できるユーザーインターフェース＝スライサー（特殊な図形）を作成し、シート上の好きなところに設置することができます。

下図のように、テーブルの上の余白に設置した使い方をすると便利です。

スライサーを選択した状態でリボンに表示されるスライサーには、スライサーのボタンの並びや大きさを変える項目があるので実用化するときは必ず確認しましょう。

スライサーを使いこなすと、簡単な台帳ならVBAを使わずにサクッとグラフィカルなツールが開発できるようになります。

詳しくは、次のツイートのツリーをご覧下さい。

※スライサーが実装されたのはExcel2010ですが、当時はピボットテーブル専用の機能で普通のテーブルには使えませんでした。Excel2013から普通のテーブルにも使用可能となりました。そのファイルをExcel2010で開くとスライサーの図形は表示はされず使用できませんが、保存されても壊れないので2013で開き直せば動作します。

範囲選択が簡単になる（マウス：矢印選択、キーボード：Ctrl+Space / Shift+Space / Ctrl+Shift+Space / Ctrl+A)

マウスポインタをテーブルの端っこにカーソルを持って行ったとき黒矢印に変化するようになり、テーブル範囲内の行選択や列選択をすることができるようになります。

キーボード操作にも対応しており、セルの範囲選択を行うショートカットキーの操作が、テーブル内の行列選択→シート全体の行列選択という二段階に変化します。

  • 行選択： Shift+Space 　※IME半角時。又はIMEのオプション変更
  • 列選択： Ctrl+Space
  • すべて選択： Ctrl+A
  • すべて選択： Ctrl+Shift+Space 　※数式組み立て中の「参照モード」に対応

セル結合が絶対に使われていない表にできる

テーブル定義された範囲には、セル結合を行うことができません。

セル結合されている範囲をテーブルへ変換すると、セル結合が解除されます。

解除されたセルは左上以外は空欄となるため、全てのセルに直接「値」を書かなければならない状態となります。

結果として、関数やVBAで処理する際に、データが埋まっていることを信頼できるようになりロジックが簡単になります。

※もちろん「々」や「〃」を使われる危険性は残りますが、少なくともセル結合が出来なくなります。オートフィルタが存在することで、ユーザーが並べ替えや絞り込みを使うために自発的に「本来の値」を入力するようになることが期待できます。

ユーザーが「（セル結合された）人が見やすい表がいい」より「（セル結合されていない）データを使いやすい表がいい」という心境へ変化させるのが狙いです。

列の数式が自動入力される

新しい数式を組んだときや数式を変更したとき、自動で全ての行に数式がフィル（複製）されます。

筆者は事故リスクの高いオートコレクトを無効化する質なので自動入力はされないのですが、案内が表示されワンクリックで列全体に数式を複製できるので便利に使っています。

www.excel-chunchun.com

データを追加したときに領域が自動拡張される。

テーブルの末尾にデータを入力したとき、勝手にテーブル範囲が拡大されます。

テーブル右下端でTABキーを押したとき、勝手に新規レコードが増加します。

このとき、書式設定や入力規則も拡大され、列の数式は自動入力されます。

集計行が増やせる

表の末尾に集計行を増やすことができます。

プルダウン選択で目的の集計方法を選べば、適切な数式が自動入力されます。

レコードが増加したら、集計範囲が自動で拡張されます。

なお、合計なら Ctrl+Shift+= で、ワンタッチで増やせます。

集計行のON/OFFは Ctrl+Shift+T です。

数式を組むときに列名による構造化参照が使用できる＝数式が読みやすくなる

テーブル内のセルを参照しようとすると、参照先の表記が構造化参照という書式になります。

たとえば、同じ行の「列名」列のデータを参照するときは [@列名] 、他のテーブルのデータ範囲全体を参照するときは テーブル名 となります。

一般的なA1参照形式 $A1 や Sheet1!$A2:$B6 では何がしたいか読み解くには参照先のデータを見る必要がありますが、構造化参照では列名が書いてあるため数式のみから意図が読み取れるようになります。

列名の重複を禁止し具体的な名付けを強制できる

テーブルのヘッダ（列名＝表の横方向の項目名）には、同じ名前が使えません。

テーブル化する時に重複した項目名には、末尾に2,3,4と数字が付与されます。

このままではいけないので、必然的に一意な名前をつけさせることができます。

これにより、MATCH関数で狙った列を確実にヒットさせることができます。

従って、VLOOKUPやINDEX-MATCHの数式で、任意の行の任意の列を抽出するような数式を組むときに重宝します。

VBAからListObjectでアクセスできる

VBAから表を読み書きする場合に、テーブル（ListObject）を通したRangeアクセスが可能となります。

例えば、 Worksheets("Sheet1").Range("A2:X100") のような式は、 ListObjects("TBL名簿").DataBodyRange と書くことが出来ます。

実務においては「最終行を求める式」や、「データの開始行を表すマジックナンバーの記載」が不要となり、断然コードが簡単になります。

Rangeは相対アクセスとなるため、普段からシート範囲をRangeに入れてから操作することに慣れていないと、このメリットは理解しづらいかもしれません。

CurrentRegionやUsedRangeは検出するデータ範囲に信頼性がありませんが、ListObjectのDataBodyRangeで取得されるのは必ずデータ範囲のみです。

ListObject.ListColumns("列名").DataBodyRangeを使って、列名を使ったデータアクセスができます。

Sub Test_国語の列を選択する()
    Dim lo As ListObject
    Set lo = ActiveSheet.ListObjects(1)
    lo.ListColumns("国語").DataBodyRange.Select
End Sub

ListObject.HeaderRowRange. を使って、列名の一覧を知ることができます。

Sub Test_ヘッダの一覧を出力する()
    Dim lo As ListObject
    Set lo = ActiveSheet.ListObjects(1)
    
    Dim rng As Range
    For Each rng In lo.HeaderRowRange
        Debug.Print rng.Value
    Next
End Sub

このほか多数の使い道があります。

ピボットテーブル / パワークエリの内部データソースとして使える

「テーブルまたは範囲から」でセル範囲のテーブル化が強制され、PowerQueryのソースにできます。

外部データソースのインポートで必要（SharePoint、Access、CSV、その他）

外部のデータソースを取り込んだ時、必然的にテーブル化された状態で取り込まれます。

SharePoint リストへエクスポートできる

テーブルの内容をExcel上の操作でSharePointリストとしてアップロードできます。

※後日この方法で作成したリストは少し特殊なリストになってしまうことが発覚したため最近では使用していません。それでもWEBブラウザのSharePointの画面でExcelをインポートする時にテーブルの定義範囲をデータ範囲として選択するために必要なので、テーブル定義が無駄になることはありません。

残念なところ（使用上の注意とも言う）

テーブル知らない人は操作性の変化に戸惑う

教育不足です。

テーブル内では、挿入削除とか貼り付けのコマンドの表現が変わります。

他にも様々な操作制限や、テーブルの定義範囲など操作にあたって意識すべきことがあるため、基礎知識を周知しておく必要があります。

列の数式を壊す人が現れる（列の保護が貧弱･･･）

教育不足です。

テーブルでは数式を書くと決めた列に値を上書きするようなイレギュラーを認めるのは危険です。

数式が保護されないので、あっさり破壊されます。

値が無い行を削除せずそのままにする人が現れる

教育不足です。

一度入力したものの、何らかの事情で「やっぱなし」した時に、テーブルの定義範囲にも関わらずデータが入っていない状態にされるときがあります。 網目模様を拡大したいという思惑で、定義範囲だけ拡大する人もいます。

教育で対処するか、諦めましょう。最初から空欄を想定したコーディングするのはさほど苦じゃないと思います。

テーブルの範囲外にデータを追記する人が現れる

教育不足です。 末尾の行に追記するとテーブル範囲は自動で拡張されますが、1行飛ばしで追記されると拡張されなくなります。

テーブルの存在認知とともに、定義範囲の確認方法と、拡張方法を周知しておく必要があるでしょう。

インプットデータには適しているが、アウトプットには適していない

テーブル内ではスピルしないので、SEQENCE関数やUNIQUE関数でキー列を生成して、必要な情報と連結させるといったことができない・・・

アウトプットのテーブルでは、ピボットテーブルや、FILTER関数、UNIQUE関数を使ってスピルさせるのがオススメ。

計算の中間表としては使いづらい（列のMATCHと構造化参照の反発など）

テーブルの列の数式機能が便利なので途中計算の中間シートとして使いたくなるときがありますが、様々な制約があり不便になります。

昨今の配列を返す関数を使用したスピルも動作できません。

繰り返しになりますが、テーブルは元データを保存するための機能です。 中間表は本来想定されている使い方ではないので避けたほうが良いでしょう。

※PowerQueryでテーブルが出力されるのは例外です。

DBには必ずあるオートナンバーの列を作れないため行のユニークIDを付与できない

Excelのテーブルを使うくらいならDBを使えと言われる原因の一つです。

Excelでは「絶対に重複させられない」ID列を作ることができません。

あの手この手で対策を講じないと、ユーザーの手やプログラムのバグで容易に空欄や重複を作ることができてしまいますし、変更されてしまう可能性もあります。

永久的に同一の行を参照したい運用等においては、ある程度の妥協が必要です。

Excel Onlineでの操作性がデスクトップアプリと違って、イマイチ使いづらい

WEBブラウザ版のExcelは日々改善されています。

2021年執筆時はテーブル機能を使うと不便になりましたが、2024年現在は特に困ることはなくなっています。（体感）

GoogleSpreadsheetや他の互換表計算ソフトにはテーブルと構造化参照が無い

テーブルを使用したxlsxファイルを他の表計算ソフトで開くと、テーブル情報が破損するか消失し、デザインや数式が元の状態を維持できない場合があります。

コピーするとテーブルの書式が付いてきてしまう

背景色はもちろん、既定のヘッダの文字色が白なので、たまにヘッダが見えなくて消えたかのような状態になっていることがある

列の入れ替えをすると、そこから右の列の幅がズレる

コツは、列の挿入→切り取り→貼り付け→削除の順で入れ替えること

テーブルをコピーして、別のシートに形式を選択して貼り付け→列幅を複製しておくこと

テーブルの列の挿入をすると、そこから右の列の幅がズレる

シートの列として挿入すればズレが発生しなくて良いが、テーブル左端に列を増やしたい場合はどうにもならないので頭の痛い課題です。

先の通り、列幅のコピー等による回りくどい操作が必要です。

テーブルを横に2つ並べて置くと、シートの行の挿入などができなくなる

そもそもの問題として、1つのシートに複数のテーブルを横に並べるのはおすすめできません。

テーブルのデータ範囲に対する行の挿入はできるので実害は高くありませんが、テーブルの下にテーブルがあるといずれ衝突してしまうため、やはり並べるのは危険です。

テーブルを横に2つ並べて置くと、片方で絞り込みをしたとき、もう片方の行も非表示になってしまう

同上です。

テーブルを横に2つ並べて置くと、左のテーブルの列の入れ替えをしたとき、右の列のデータがずれるというあってはならない挙動をする

テーブルの行が切り取り挿入貼り付けで入れ替えられなくなる（シートの行として入れ替えることはできる）

ドラッグ＆ドロップで簡単に行の入れ替えが出来ると良いのですが、ExcelにはそのようなUIが用意されていないのが玉に瑕です。

テーブルの左端に「ID」や「並び順」列を作って、ソート用の連番を降っておくと幸せになれます。

構造化参照をA1参照に、A1参照を構造化参照に変える正当な方法が用意されていない

A1参照形式にする方法は、無いことは無いけども・・・（後述）

テーブルを含むと2つ以上のシートを一度にコピーできない

2シート間で数式の参照がある場合に、2シート同時にコピーすることで関係性を維持できますが、1シートづつコピーすると参照が途切れるという問題が生じるのが致命的です。

  • ブックをまるごとコピーして、不要なシートを取り除き、必要なシートを取り込む とか
  • コピーしたいシートセットを「移動」して、コピー元ブックは保存せずに閉じる といった強引な手法が必要となります。

テーブルにも名前を付けないと数式が読みづらくなるのだが、（シート名と違って）運用上は必要ないため名付けが重視されない。

テーブル名を設定するのは癖つけた方が良い。本当に。

クイックアクセスツールバーへ追加すると忘れにくいのでおすすめです。

ヘッダを空欄にできない。重複も出来ないので、スペースで埋めて誤魔化す

半分使い捨ての表を作りたい時に、デザインだけ欲しくてテーブル定義をすると列を重複できないので 、 、 、 みたいな列がいっぱい増えていく。

早めに「範囲に変換」でテーブル状態は解除しましょう。

集計行をヘッダの上にできないため、合計値などの集計結果をウィンドウ枠の固定により常時見ることができない

数式で引っ張ればなんとでもなりますが、どうしても欲しければ「分割」機能を使う方法もあります。

ヘッダが階層化されている表に使用しづらい（列名が冗長になりがち）

テーブルでは列名の重複が許されないという決まりの影響もあり、大きさ > 高さ、幅、奥行き のようなヘッダを2段書きにしたいような場面で列名が横に伸びがちになります。

列名に内部改行（Alt+Enter）があると参照する数式中の構造化参照が読みづらくなる

いっそ、ヘッダ行は縦長にして、折り返して全体表示にしてしまうのも吉かと。

並び順に依存する積み上げ型の集計（累計を求める式）と相性が悪い

これは数式の工夫によりある程度は回避が可能です。

どうしても計算量が重くなるので、相性が悪いのは否定できません。

ヘッダ部に数式が使用できない

たとえば、日付を羅列したいとき 4/1 4/2 4/3 ・・・

たとえば、直上の社員番号から社員名をつけたいとき・・・

こういうときは、行と列を入れ替えたり、テーブルは入力用にして、クロス集計シートを作成したら改善しないか検討してみましょう。

それでも自動化したい時が結構あるためテーブルの利用を断念することがあります。

誤って列全体/行全体に貼り付けしてテーブルが拡張されるときExcelがフリーズして死ぬ

Excelの初期設定では、貼り付けによる大量セルの書き換えで警告が表示されるのですが、（たぶん誰もが）無意識のうちに警告をOFFにしてしまうため、警告されることもなくフリーズして泣きます。

パソコンのスペックや表の計算量にもよりますが、軽いものなら10分も待てば終わると思います。ただ、Ctrl+Zで戻すにしても同じくらい時間がかかります（泣）

その他のTips

表が横長になりがち

横長の表は、横スクロール Ctrl+Shift+Scroll を覚えれば、ある程度許容できるようになる。

列の非表示ではなく、アウトラインによる列の折りたたみを使うと良い。

あとはマクロなど。

テーブルの右端、下端に書いたコメントが、テーブルに取り込まれてしまった

Ctrl+Z で自動拡張を取り消すことができる。

なお、テーブルより右か下にデータを置くのは、基本的に使い方を間違えている。

行の入れ替え機能が無いことから、左に書くのも使い勝手を阻害する。余分なデータは上に書いたほうがいい。

それでもやるなら、1列開けて、拡張されないようにすると良いかもしれない。

構造化参照をA1参照形式に変換したい：範囲に変換

「範囲に変換」で構造化参照をA1形式に戻すことができる。

構造化参照を使いたくない：xlsに変換

xls形式で保存していると、GUI操作でも構造化参照とならなくなり、A1形式で参照式を組むことができる。

構造化参照の列を横に変化させたい：横方向フィルの特殊な挙動

コピーだと構造化参照の列は変更しないが、マウス操作でフィルハンドルをドラッグしてフィルした場合は、構造化参照の項目名の部分が変化する。

特に、集計行の数式を複製するときは手間がかかるので、この操作の出番が多い。

入力規則のリストにテーブルを使いたい

まとめ

テーブルの機能大半は、Excelの単なる表を、リスト形式のデータベースとして使えるようにするためにあるものばかりです。

代わりに、Excelの操作性が若干落ちる傾向にあります。

本気で使い始めると、たまに目的を阻害するケースがあります。

テーブルの癖を知っていれば便利なものですが、何も知らない状態の人は戸惑うかもしれません。

テーブルを採用するかは、各自、各現場の事情に合わせて判断してください。

個人的な意見

さて、最後の最後に身も蓋もない理由で閉めさせてください。

私がテーブル化する最大の理由は、

入力データがテーブル化されていないと気持ち悪いから

です。もちろん「構造化参照を使いたい」「行と列の選択を楽にしたい」「スライサー使いたい」という思いがありますが、究極的にはこの一言に収束します。

なぜテーブル化するのか？

それは、

そこにテーブル化できそうな表があるから

です！

というわけで、今回は以上です。

Ctrl+T を押して、どんどんテーブル化していきましょう♪

2021/12/15時点で本書は未完成なので、後日加筆修正を行います。

後日談

何も考えずに全部テーブルにすると、こうなります。

あるいは、こうなります。