えくせるちゅんちゅん

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

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

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

Excel

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

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

テーブル化するメリット

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

ご存じの通り、セルの書式設定とは別次元で、行が交互に着色されデータ範囲が明確になります。

加えて、オートフィルタが自動で設定されます。※後で消してもOK

f:id:Kotori-ChunChun:20220318201717p:plain

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

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

そのため、ヘッダより上に常駐させたいデータの無いシートなら、ウィンドウ枠の固定が不要です。

f:id:Kotori-ChunChun:20211216004616p:plain

スライサーが設定できる

ワンクリックで絞り込みが実行できるユーザーインターフェース（スライサー）を設置することができます。

f:id:Kotori-ChunChun:20211216010010p:plain

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

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

※スライサーはExcel2010でピボットテーブル専用で搭載され、Excel2013から普通のテーブルにも使用可能となりました。

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

マウス操作の場合は、テーブルの端っこにカーソルを持って行ったとき、行選択や列選択をするための領域が増えます。

f:id:Kotori-ChunChun:20220318202731p:plain

キーボード操作の場合は、範囲選択を行うショートカットキーの操作が、テーブル内の選択→シート全体の選択という二段階に変化します。

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

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

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

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

解除されたセルは左上以外は空欄となるため、全てのセルに直接「値」を欠かざるを得なくなります。

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

もちろん「々」や「〃」を使われる可能性は否定できませんが、少なくともセル結合が出来なくなります。オートフィルタが設定されることで、ユーザー並び替えや絞り込みを使うために、自然と「本来の値」を入力するように学習されることが期待できます。

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

f:id:Kotori-ChunChun:20211216010025p:plain

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

新しい数式を組んだとき、全ての行に数式が自動でフィルされます。

数式を変更したとき、全ての行に数式を反映させる案内が表示されます。

f:id:Kotori-ChunChun:20211216010052p:plain

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

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

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

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

f:id:Kotori-ChunChun:20211216010107p:plain

集計行が増やせる

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

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

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

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

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

f:id:Kotori-ChunChun:20211216010121p:plain

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

テーブル内のセルを参照しようとすると、勝手に構造化参照という形式になります。

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

一般的なA1参照形式 $A1 や Sheet1!$A2:$B6 のような、ぱっと見で分からないセル座標から意味を読み解く必要がなくなります。

f:id:Kotori-ChunChun:20211216010135p:plain

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

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

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

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

f:id:Kotori-ChunChun:20211216010154p:plain

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

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

f:id:Kotori-ChunChun:20211216010205p:plain

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

f:id:Kotori-ChunChun:20211216010219p:plain

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のソースにできます。

f:id:Kotori-ChunChun:20211216010232p:plain

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

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

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

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

f:id:Kotori-ChunChun:20220318214128p:plain

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

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

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

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

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

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

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

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

中間データでは使いづらい場合がある（列のMATCHと構造化参照の反発）

DBのように行へユニークIDを付与できない　→　なぜなのか･･･

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

GoogleSpreadsheetには構造化参照が無い

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

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

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

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

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

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

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

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

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

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

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

Google Spreadsheet等のように、ドラッグで行の入れ替えが出来ると良いのだが･･･

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

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

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

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

2シート間で数式の参照がある場合に、1シートづつコピーすると、参照が途切れるという問題が生じるのが致命的。

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

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

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

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

、 、 、 みたいな列がいっぱい増えていく。

集計行をヘッダの上にできないため、集計結果を常時見ることができない

どうしても、欲しければこれ。

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

列名に改行Alt+Enterがあると構造化参照にも影響して式が読みづらくなる

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

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

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

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

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

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

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

その他のTips

表が横長になりがち

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

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

あとはマクロなど。

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

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

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

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

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

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

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

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

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

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

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

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

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

f:id:Kotori-ChunChun:20211216130955p:plain

まとめ

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

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

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

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

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

個人的な意見

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

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

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

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

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

それは、

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

です！

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

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

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

後日談

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

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

プライバシーポリシー