Excel のアレ がキター! 【超速報】Googleスプレッドシート テーブル機能まとめ
先日発表された Googleスプレッドシートのテーブル機能。
mirの環境でも 2024年5月25日 に使えるようになったので、急ぎ検証してみました。
速報検証ということで、まずはざっくりとした所感 や 使い方、 元祖 Excelのテーブル機能との違いなどをまとめています。誤りや漏れがあるかもしれませんので、その際はコメントや X(Twitter)でお知らせください。
※2024年8月までの追加アップデート情報も反映させました
先週までは UNIQUE関数シリーズの noteを書いてました。
Googleスプレッドシートの テーブル って どうなのよ?
とりあえず 数時間 テーブル機能を いじってみた感想としては
「さすがに Excelほど充実した機能ではないけど 悪くはないし、今後を期待させるものがある」
でした。
Googleスプレッドシート テーブル機能を使ってみた 感想まとめ
テーブル機能を 使って感じたポイントをまとめると
こんなところでしょうか。
これら以外にも細かい検証をしているので、最後まで読んでみてください!
そもそも テーブル機能ってなに?
上に登場する「構造化参照」やら「集計行」がピンとこない方は、たぶんExcelのテーブル機能を使ったことがない人だと思います。
さすがに、これを説明するのは大変なんで Excelのテーブル機能を紹介したサイトをご覧ください。
Microsoftの公式
窓の杜さん
他にもいろいろあります。
当然、使えるようになったばかりの Googleスプレッドシートの テーブル機能の解説サイトは まだ見当たらない・・・。(書いてる人はいるかもですが)
一応Googleの公式ページはあったので掲載
Googleスプレッドシート テーブル機能を使うには?
もしかすると note公開日 2024年5月25日の時点では、まだテーブル機能が使えないという人もいるかもしれませんが、順次ロールアウトされているとのことで、2024年5月中には個人ユーザーは全員使えるようになると思われます。
今回のアップデートは有料の GoogleWorkspace限定ではなく、全Googleアカウントのユーザーが誰でも使えるってのが良いですね!
テーブル機能が使えるようになったか?はどこで確認するの
スプレッドシートを新規作成してみるのが一番簡単です。
サイドバーにこのような 「事前構築済みの表から始める」というガイドが表示されたら、テーブル機能が実装されています。
Googleにしては珍しく、かなり多くのテーブルの テンプレートを用意してくれています。
サクッとデザイン性の高い表が作れる。複雑な数式や仕組みは不要って人は、この為だけにテーブルを使っても良いですね。
もともとあった機能「交互の背景色」では変化のなかった文字の色なども含めて表のデザインを自動設定してくれるのが テーブル機能の魅力の一つです。
ちなみにこのテーブルのテンプレートですが、既存のスプレッドシートで利用したい場合は
メニューから 挿入 > 表 で利用できます。
また、他のスマートチップと同じように @からも呼び出せます。
微妙な日本語訳で「表」って書き方の箇所もあってわかりにくいんですよね。。「テーブル」で統一して欲しいもんです。
もし既に作成済みの表でテーブル機能を試したい場合は
メニューから 表示形式 > テーブルに変換
が表示される
または 表の範囲を選択して右クリックで
このように「テーブルに変換」が表示されていれば、「テーブル機能」が使える状態です!
早速テーブルを使ってましょう。
新規テーブル作成時にはガイドが表示されるので安心
ためしに 「イベントのスケジュール」というテーブルテンプレートを使って新規テーブルを作ってみましょう。
右のサイドバーで使いたい テンプレートの右に表示される「挿入」ボタンを選択すると
このように作成ガイドが始まります。
まず「列の型を変更する」・・・これはつまり「列単位でデータの型(形式)を指定できる」ってことですね!
次が「カスタムビューを作成する」これは、フィルタビュー(旧 フィルタ表示)機能が テーブルにも適用できて、他の共有メンバーの表示に影響させることなく、自分だけの画面で表示をカスタムできるってことですね。
チームでの共同作業を前提としている Googleらしい設計思想が、テーブル機能にも反映されています。
最後に「表のカスタマイズ」ということで、色や名前、デザインを変更できるようになっています。
このようにテーブル作成時の初期設定をガイドしてくれるので、不慣れな人でも安心です。
ちなみに ココ、1行増えているようで実際は行ではありません。あくまでも見出し行が1行目で、その上のテーブル名の行? は行番号のない特殊な行となります。(2行目以降にテーブル見出しがくる場合は、これは生成されない)
後で他の機能を使った時にも再度登場しますが、テーブルを使うと 行番号にカウントされない(行番号の無い)特殊な行が発生するようになります。
まずは適当なテーブルを一つ作ってみるがおススメです。
テーブル用サンプルデータ
日付 商品 単価 数量 販売額
04/15 りんご ¥100 2
04/15 ばなな ¥150 3
05/02 めろん ¥900 5
05/10 りんご ¥150 4
05/12 ぶどう ¥200 5
テンプレートは スマートチップを多用したものが多いので、もうちょっと普通の(シンプルな)テーブルをまずは試したいんだけど・・・って人のためにサンプルデータを用意しました。
このデータを右上に表示されるボタンで コピーして、適当なスプレッドシートの 新しいシートの A1セルに貼り付けすると、ちょうど範囲が選択された状態になってるんで、メニューから
表示形式 > テーブルに変換
これで見出し行が固定され テーブルになりました。
※販売額の列は後で式をいれます。
テーブルに出来ない表もある
どんな表でもテーブルに変換できるわけではありません。
まず 表の中に一つでも結合セルがある場合は、
このようなメッセージが出てテーブル化できません。
また、他のスプレッドシートにある表を
このような IMPORTRANGE関数の式で 見出し行を含め出力した場合は
このようにエラーになります。
見出し行を数式で生成していると テーブルに出来ないってことですね。
とりあえずこの2つは見つかりましたが、もしかすると他にも テーブルに変換できない表があるかもしれません。
Googleスプレッドシートの テーブル機能で出来ること
次に Googleスプレッドシートのテーブル機能では、何が出来るのかを見ていきましょう。
Googleスプレッドシートのテーブルは 列の型指定が出来る
先ほどのスタート時のガイドにも登場しましたが、列単位でデータの型を指定できます。
指定できる型ですが
このような選択肢になっています。
意外だったのは既存の表をテーブル化した時に 自動で型を判別してくれないという点。
既存の表をテーブル化した場合は、いずれの列も型は「なし」となります。いやいや、ここでGeminiさん登場せんのかぃ。
現状では手動で型を選択する必要があるってことです。
【追記】2024年8月のアップデートで、表をテーブルに変換した際の列の型の自動判別が出来るようになりました。
2.範囲をテーブルに変換するときに列の種類を自動的に設定するようになった
型を選択すると 見出し名の左側に 型に応じたアイコンが表示されるので、慣れれば見ただけで型指定がわかりそうです。
型指定した列は プレースホルダー(入力例)が表示できる
あらかじめ入力予定の範囲のカラの行も含めてテーブル化した場合、日付や日時、数値、テキストなどの型を選択すると、入力例(プレースホルダー)を表示させることが出来ます。
列の型を編集するのメニュー一番下に「プレースホルダーを表示」という選択肢が表示されるので、これにチェックを付けることで、入力セルが
このような表示入り(実際にはセルの中身は空白状態)となります。
わかりやすいですね!
もちろん日付や日時の列なら、ダブルクリックで カレンダーからの選択(日付ピッカー)となるので便利。
ちなみに「通貨」は ロケーションを日本にしていれば自動で 円(¥)になりますが
初期値がGoogleスプレッドシートではお馴染みに 小数点2桁表示なので、表内のセルを選択して 小数点以下の桁数を減らすボタンを2回押す必要があります。
テーブル内では 一つセルを選択して 表示形式を設定すると、その表示形式は 列全体に適用されます。
逆に言えば 表内の特定のセルだけ表示形式を変更することは出来ないってことです。
ここで指定した型にマッチしない内容を入力をすると
無効といいつつ、入力は出来ちゃいます。
入力出来ないという強い制限も設定できたら良かったかも。
それでも、テーブル以外のデータを値貼付けではなくそのままコピペやドラック&ドロップといった 書式を壊すような操作 をされても
このようにセルの色などの装飾は影響しても 列で指定した データの型は壊れることはありません。
なかなか便利です。
本当は 数値が指定されている列への入力時は 自動で日本語入力がオフになってくれると便利なんですが、さすがにブラウザアプリでそこまでは無理か・・・。
フィルタと同じように 並べ替えや 絞り込み、スライサーの利用ができる
テーブルの見出し行には 各セルの右に Ⅴ(フィルタボタン) が表示され、フィルタ機能と同じように 並べ替えや 絞り込みが出来るようになっています。
なぜかここは(現時点では)英語のままで
Sort column(並べ替え)
Filter column(絞り込み)
となっています。これらの機能は フィルタ機能と一緒ですね。
Filter columnを選択すると、いつもの フィルタの操作画面が登場します。
ちなみにGoogleスプレッドシートのスライサーは微妙って声も多いんですが、スライサーもテーブルに対して使えます。
テーブルは見た目をカスタマイズできる
左上の テーブル名の V から メニューを開くと テーブルの名称や見た目を変更できます。
これらが可能です。ちなみに交互の背景色の色を変えたい って時は
テーブル内のセルを選択した状態で メニューの
表示 > 交互の背景色 で変更可能です。
また、表形式でないデータに戻すで テーブルの解除(データは残る)
表を削除で データごと丸ごと表の削除が出来ます。(どちらも実行してよいか?という確認のダイアログが出る)
テーブルは範囲が自動拡張され設定している数式も自動追加される
スピルしない時代の Excelでは 非常に重宝されていた テーブルの自動拡張と数式の自動追加ですが、 Googleスプレッドシートのテーブル機能でも実装されています!
上のテーブルは、販売額の列(E列)の 各セルに
という式が入っています。テーブルの範囲は5行目までとなっています。
ここで 新しいデータとして テーブルの下 6行目に日付を入力したことで テーブルが1行拡張され、それに合わせて E6セルの販売額にも数式が自動補完されているのがわかりますね。
E6セルには自動で =C6*D6 という数式が追加されました。
スピルや配列式に不慣れな人が待ち望んでた機能じゃないでしょうか?
テーブルを使うと構造化参照ができる
そしてもう一つ、こちらも 待望の機能でしょう。Googleスプレッドシートの テーブルでも 構造化参照が使えるようになりました!
こちらは構造ではなく孝蔵
構造化参照とは簡単に言うと、スプレッドシートで通常使う セル位置を表す A1参照ではなく、テーブルとテーブルの列名(カラム名)を使って セル(範囲)を参照する方式です。
構造化参照を使う場合は、テーブル名をわかりやすいものに変更しておきましょう。
サンプルデータの表は「販売表」という名前にしています。
このような記述で、指定したテーブルの列データをまるっと参照できます。
例えば
この式だと、販売表というテーブルの 販売額 の列の 全ての 金額(数値)を 合計しているわけです。
従来の書き方 =SUM(E3:E6) でもよいのですが、上の方が処理内容がわかりやすいのと、テーブルの拡張に伴い 範囲が自動拡張されるという利点があります。
構造化参照の記述は、全て正しく手入力する必要はなく
このように対象の列のデータ範囲を選択する(見出しを除きテーブルの最後まで選択する必要あり)
または、
このようにセルに入力することで参照したい候補を呼び出すことが出来ます。(関数は後から入れる)
構造化参照に歓喜しているユーザーも多いかと思いますが、Googleスプレッドシートの 構造化参照は Excelのテーブルのものと少し勝手が違います。
Excelと違って @による 現在の行 という指定や #見出し、#集計 などがありません。
※後で触れますが、そもそも 集計行機能が無いので #集計 が使えないのは当然ですが
Excelの [#すべて] に該当するものはあります。 [#ALL] です。
こちら で 見出しを含めたテーブルのデータ全てを取得できます。
Excelの #データ に該当する データ部分だけの取得は
このようにテーブル名だけの指定で可能です。
ただし、どちらもそのままセル出力する場合は Googleスプレッドシートだと 自動ではスピらないので、
このようにするか
このように記述する必要があります。
Excelのテーブルの 式を入れた行の データを参照する際の@をつける書き方、 販売表[@販売額]
これは Googleスプレッドシートにはありませんが、
これがそのまま代用できます。
どういうことか?
このように 販売額[販売表] という参照の結果が
記述の仕方や記述するセル位置で変わってくるわけです。
さらに テーブル内での参照の場合は テーブル名は不要なので
販売額の式を このように記述できるってことです。もちろんテーブルの拡張に合わせて式も自動補完されていきます。
ただし既に他の列が埋まっているテーブルの新しい列に式を入れる場合は、データの1行目に 式を入れて確定させた後に表示される「自動入力の候補を表示」の ✓ を選択(または Ctrl + Enter)とする必要があります。
ここは Excelと少し違いますね。
テーブルの見出し行の取得は、どうやら用意されていないようなので、
とりあえずこんな感じで対応ですかね。
Excelのテーブルで使われる 見出し含めて列データを取得する
このような記述も「すべて」の部分を「ALL」にしても Googleスプレッドシートでは使えません。
あと、テーブル化すると左上の名前付きボックスから選択して 指定のテーブルに ジャンプできるようになります。これも便利ですね。
Googleスプレッドシート ならでは!のテーブル機能
せっかく後発での実装なんだから、Excelのテーブル機能とは一味違った魅力が欲しいところ。
Googleスプレッドシートならでは!といえるテーブルの機能はあるのか?こちらを見ていきましょう。
テーブル内でARRAYFORMULAが使える!
テーブルの機能で 行が追加されテーブルが拡張されると 数式も追随して自度補完される。
これはいい機能なんですが、Googleスプレッドシートは式が多いと重くなるのが気になるんですよね。
出来れば 一つの式(ARRAYFORMULA)で配列を返したい(スピらせたい)!
でも、Excelだと テーブル内でのスピルは 出来なかったはず・・・
じゃあ Googleスプレッドシートのテーブルはどうなのか?
うひょー!出来ました~。これは素晴らしい。
こんな感じで Googleスプレッドシートのテーブルは、
テーブル内で構造化参照 + ARRAYFORMULA
が使えるってことです。
これを使うと A2:Aのような指定でシートの最後まで計算させたり、関数を使って最終行までの範囲を取得することなく、拡張性を備えた 適したサイズの式を簡単に書くことができますね。
ただし、並べ替えには弱いので注意が必要です。
これに対して よくやる対策ですが、並べ替えに強い見出し行に式を入れる
こんな方法は出来ませんでした。
そういや IMPORTRANGE取得データのテーブル化で、見出し行に式を入れちゃ駄目って学びましたねw
また、さすがに自分自身の列を構造化参照させる式は無理でしたが・・・
ARRAYFORMULA関数 + LET関数で 複数列の計算を 一つの式で
これは出来ました。
まぁここまでして一つの式にする必要があるかは微妙ですがw
グループ化したビューが生成できる
Googleスプレッドシートのテーブルには「グループ化したビューを作成」という機能があります。
これは列毎の見出しの フィルタボタンから 「グループ化の列」を選択することで使えます。
たとえば 「アクティビティの種類」でこれを選んでみると
このように アクティビティの種類毎でまとめられ、それぞれに区切り行(行番号なしの特殊行)が追加されます。
ちなみにこれは フィルタビュー状態(旧フィルタ表示)となっているので、自分だけの表示です。
同時に他の人がシートを見ていても影響しません。
さらに、先ほどテーブル内で ARRAYFORMULAを使った式は 日付を降順で並び替えした際に表示がおかしくなりましたが。
グループビュー 状態での並び替えであれば、並び替えをしても ARRAYFORMULAの数式の結果が崩れることがありません。
なかなか便利ですね。
※ただし ビュー表示後に追加されたデータを反映するには 手動で「ビューの更新」をしてあげる必要があります。
グループ化したビューは 名前を付けて保存、いつでも呼び出せ、ユニークなリンクも生成される
この グループ化のビュー画面は「フィルタビュー」と同じ状態なので、名前を付けビューを保存できます。
これは テーブル機能実装に合わせて、旧 フィルタ表示が フィルタビューに名称変更し、いままで自動保存だったものが 必要な時に保存する方式に改善された 為です。(次回こちらも noteまとめます)
右上の「ビューを保存」を押すと、「このビューに名前を付けますか?」と表示されるので、ビューの名前を付けて保存とすれば
テーブル名の 右の 電卓っぽいアイコンから いつでも呼び出せ、他の共有メンバーも利用することができますし
フィルタビュー(フィルタ表示)同様に、閲覧のみのユーザーでも利用できるので非常に便利です。
他にも、列の型指定+プレースホルダー や スマートチップが使えるといった点もGoogleスプレッドシートならでは!といえる、テーブル機能ですね。
今回の検証では思いつきませんでしたが、他にもまだあるかもしれません。
Googleスプレッドシートのテーブル機能で出来ないこと
最後に Googleスプレッドシートのテーブル機能で 出来ないことを見ていきましょう。
テーブルに 集計行がない
一番大きい Excelのテーブル機能との違い(不満)はこれですね。
Excelの場合は、無料の Web版 Excel でも現在は テーブル機能が使えて「集計行」を追加する機能があります。
こちらを使うことで合計や個数、平均など テーブル内の指定した列のデータを集計できるんですが・・・
Googleスプレッドシートのテーブル機能には、集計行機能がありません。
そもそも、先ほど登場した グループ化したビューも
せっかくなら、グループ毎の集計(集計関数の利用)が出来たらいいのにって思いますよね。
残念ながら現状はこれらは出来ないようです。
グループ化 の集計 で日付グループのオプションがない
グループ化に関連してもう一つ不満が。
たとえば Googleスプレッドシートの ピボットテーブルでは
このように日付データを 月単位や 曜日、年といった 単位でグループ化して集計表示を切り替える機能があります。
残念ながら 新たに実装された Googleスプレッドシートのテーブルでは、グループ化という機能はあっても、このような日付データを 月単位や年単位のグループに変更するオプションがありません。
先ほどのグループ毎の集計関数機能と合わせて、この辺りは今後のアップデートに期待したいところです。
テーブルを フィルで拡張できない
Excelのテーブルは、右下をマウスで選択して フィルすることで、下や右にテーブルを拡張することができます。
残念ながら Googleスプレッドシートのテーブルは 直観的なこの操作が出来ません。
もちろん 表の範囲を調整で セル範囲を変更すれば テーブルの拡張は出来るんですが、より簡単で直観的なフィルによる 拡張が出来ると便利ですよね。
【追記】2024年8月のアップデートで、テーブルのフィル拡張が出来るようになりました。
3.下、横にドラッグ(フィル)でテーブルが拡張するようになった
INDIRECT関数やIMPORTRANGE関数では テーブルを参照できない
Googleスプレッドシートの テーブルは、名前付き範囲とは別モノのようです。
名前付き範囲として「あいうえお表」と登録した 範囲 'シート1'!G2:G6 は
で参照できますが、テーブル名 「販売表」は
で取得ができません。無効なセル / 範囲の参照です と#REF!エラーになってしまいます。
INDIRECTで参照できないということは・・・
IMPORTRANGE関数でも 名前付き範囲のような参照ができないということです。これは残念。
Excelのテーブルとの互換性が中途半端
ExcelとGoogleスプレッドシートの両方を使うユーザーは、双方のテーブルに互換性はあるのか?が気になるところですよね。
まず Googleスプレッドシート → Excel ですが、
Googleスプレッドシートのテーブルは xlsx形式でダウンロードした場合、Excelでも テーブルとして機能します。
ただし注意点が幾つかあって
当然ですが Excelには 無い スマートチップはそのまま継承は出来ません。ただし、地図チップや人物などは ハイパーリンク化したテキストに変換されます。
また、先ほど登場した Googleスプレッドシートならではの テーブル内での ARRAYFORMULAですが、こちらを使っていると Excelにダウンロードした際にテーブルになりません。
Excelとして扱う可能性がある場合は、テーブル内ARRAYFORMULAは使わない方が良さそうですね。
では逆の Excel → Googleスプレッドシート はどうでしょうか?
こちらは テーブルとして引き継がれません!
テーブルを設定したシートがあるExcelファイルを Googleドライブにアップロードして スプレッドシートの互換表示で開いた際、テーブル機能は失われてしまいました。
単なるキツめの色合いの交互の背景色の表になるだけで、これはテーブルではなく構造化参照もされていません。
ちなみに スプレッドシートで テーブル入りの Excelファイルをインポートする方法でも同様でした。
というわけで、互換性をまとめると
このような結果でした。うーん、もう少しGoogleスプレッドシート側が 先駆者のExcelに歩み寄っても良かったんでは??
テーブル機能の 列の型指定でも Googleフォームの回答の 表示形式は整えられない
Googleフォームは 回答をスプレッドシートに出力できて便利なんですが、回答の入った 真っ新な(表示形式設定のない)行が挿入される挙動である為、書式クラッシャーというか、どうしても新しい回答の表示形式が揃わないという問題がありました。
で、強力な列の型指定が出来る テーブル機能をフォームの回答シートに適用しておいたらどうなるか期待したんですが・・・
残念ながら 指定した型にならずです。
このように B列には A列の タイムスタンプを INT関数で 日付のみのデータにして 日付形式で表示する設定をしていたのですが。
シリアル値のまま回答が追加され、テーブル上では 悲しく 無効という表示になっています。
ちなみに D列の 希望日の列も yyyy年mm月dd日 指定なんですが、揃わずに yyyy/mm/dd になっちゃってますね。こちらは日付という型は合ってるので無効の表示はでませんが・・・。
テーブル機能では Googleフォームの回答の表示形式を制御は出来ませんでした。
フォームの回答の 書式クラッシャーっぷりは、どんだけ強力なんだ~!!
テーブル機能:その他の検証・確認
最後に 上記以外で今回 テーブル機能で検証・確認したことを書きます。
本来は無い 集計行機能を 数式で作る
Googleスプレッドシートのテーブルには無い 集計行機能。これを 無理やり集計行機能を数式で作っちゃおうというネタです。
わかりましたでしょうか?
テーブル 販売額の下に合計があり、テーブルの最終行にデータを追加しても自動で下にズレて再計算されますが
・合計行といったラベルがつけれない
・行が追加されると 罫線や色、文字の装飾は失われる
とやや微妙かなといったところですよね。
数式で無理やりくっつけてるだけなんで、仕方ありません。
こんな式で無理やり 販売額と SUM(販売額) を縦に連結して対応しています。
ちゃんとした集計行機能が実装されるまでの繋ぎくらいにはなるかなとw
Googleスプレッドシート「テーブル機能」アプリ版の対応
スマホ アプリ版ですが、iOS版、Android版、どちらも 作成済みのテーブルを扱うことが出来ます。
既存の表をテーブルに変換する
テンプレートから新規のテーブルを挿入する
テーブルのデザイン変更
列の型の指定(変更)
グループ化したビューの作成、切り替え
これらは スマホアプリからは出来ませんでした。
ただし、
テーブルへのデータ追加
データ追加に伴うテーブル範囲の自動拡張(数式の補完含め)
構造化参照
この辺りは普通に利用することが出来ました。
今後のさらなるアップデートに期待ですね!
Googleスプレッドシートのテーブル機能 可能性は感じた!
取り急ぎ 数時間ほど Googleスプレッドシートのテーブル機能を触ってみた検証結果を書きました。
個人的には 新しく追加されたテーブル機能は、今後のGoogleスプレッドシートの可能性の広がりを期待させるものでした。
欲を言えばもう少しここが・・・という点は多々ありますが、スマートチップの活用促進にもつながりそうですし、Excelユーザーの乗り換えにも貢献するんじゃないでしょうか?
今回は 十分な検証とは言えないので、誤ったことが書かれている、もしくは重要なこれについて書かれていない、といったご指摘があれば 是非コメントか X(Twitter)でお知らせください。
新たな発見や さらなる検証が出来た際には 続きを書きたいと思います。
【追記】2024年8月のアップデートを別 noteにまとめています。
次回こそは予定していた、今回のテーブル機能にも関連する フィルタ表示から フィルタビューに変更になった件を書きたいと思います。
この記事が気に入ったらサポートをしてみませんか?