Googleスプレッドシートにテーブル機能が来たよ
Google Cloud Next '24では地味な扱いというか、殆ど何処のサイトも注目機能みたいに扱っていなかったテーブル機能。もともとMicrosoft Excelではその原型が2000年から搭載されてる機能です。これがようやっとGoogleスプレッドシートに搭載されて、自分のテナントにも展開されました。
自分自身も社内研修などではこのテーブル機能とPower Queryは事務職の必須のスキルとして推していましたが、Excelのテーブルとどう違うのか？などを早速検証してみようと思います。
※Google Workspace Blogでも紹介されています。窓の杜でも5月9日には紹介されていたものになります。
目次
今回利用する機能
Excelでのテーブルの活用やこれを応用したPower Queryについては研修素材として作ってよくレクチャーしていました。それらは以下のエントリーから参照することができます。合わせて見ていただけたらと思います。
Google Workspaceだけじゃなく通常のGoogleアカウントでも利用でき、早期リリースだと5/8から5/30までには展開済みとなっています。
図：サイドバー機能までついてきた
そもそもテーブルとは何なのか？
Excelで搭載されてもうかれこれ経つ機能で、それまでのExcelの縦横の範囲で扱うものではなくAccessのテーブルと同様に１つの塊として扱うことのできる機能で、主な機能としては
- 範囲指定ではないので他の機能やセルからは１個の伸縮する塊としてデータを扱える
- Accessのテーブルと同様に、列という塊に対して数式を記述することが可能（列内に１個ずつ数式を書いたり、またその範囲がずれたりといったことがなくなる）
- Power Queryなどから出力する場合はテーブルで出力される
- VBAやVBSなどからも範囲ではなく塊でアクセスできるため、範囲（特に行数）を意識して記述する必要がない。
- テーブル領域内はフィルタやきれいなフォーマットで整形してくれる
- 関数で指定する際に非常にわかりやすい表記になる
- 同一のタイトル名は設定できなくなるのでプログラム的には非常に扱いやすくなる
- 列や行を増やすと自動的にテーブルは拡張される
事務職であれば経験したであろう、vlookupで数式の範囲がずれたであったり、何個目の列みたいなことを意識しなくて良くなる、また数式の入れ忘れなどがなくなるなど、非常にメリットが大きいため、欠かせない機能でした。
これが今回Googleスプレッドシートに搭載されたということなので色々と期待するものになります（正直遅すぎだとは思いますが）。
テーブル機能を調査してみた
テーブル化と基本設定
テーブル化の実行
Excel同様にテーブル化するのはとっても簡単。
- テーブル化する領域を範囲指定
- メニューから表示形式⇒テーブルに変換をクリック
これだけです。ここから、テーブルのいろいろな設定をしたり、機能を試すことになります。もとに戻したい場合は、テーブル名横の▼をクリックして、表形式ではないデータに戻すを選択すると通常のデータ形式に戻ります。
図：テーブル化してみた
テーブルの名前をつけておく
デフォルトのままだと「表_1」という味気ないタイトルで何の表なのかわからないため、以下の手順で表の名前を必ずセットしましょう。
- 表の左上の名前の隣の▼をクリックする
- テーブルの名前を変更をクリックする
- 名前変更ができるので、わかりやすい名前を入力する
図：テーブル名を変更
テーブルの交互色を変更する
デフォルトのテーブルの行ごとの色違いである交互色は薄い灰色でなんだかわかりずらい。これを指定の交互色にするには以下の手順で設定変更します。なぜかテーブル上では項目がなく、メニューから辿る必要がある。
- メニューより、表示形式⇒交互の背景色をクリック
- サイドバーが出でくる
- 色1や色2を変更すると色が変わる
- フッターを含めると3色指定可能
- 完了をクリックする
図：フッターは最終行の色になります
スプレッドシートのテーブル機能検証
サンプルのテーブルスタイル
Google側で用意してる出来合いのテンプレート（型指定やプレースホルダ、スマートチップとかまで設定済み）が用意されています。これらを使うことで１からテーブルの構造を自分で考えたりセットする手間が省ける・・というものですが、正直人が利用するテーブルってのは多種多様であるため、本当にサンプル程度のものとして捉えたほうが良いかも。正直結局は手直しが必ず入ると思うので。
- 空っぽのセルの上で右クリック⇒表をクリック
- サイドバーが出てくるので、例えばイベントタスクをクリックする
- 選択したテーブルスタイルが自動で挿入される
図：テンプレートが用意されている
プレースホルダを指定可能
プレースホルダとは、そのセルに対して「何を入力するべきなのか？」の例示表示のことで、日付の形式であったり値の形式であったり入力事例をセットしておけるものです。値ではないので参照しても値が入ってるわけじゃありません。これは後述の型指定が必要になります。
自分で好きなプレースホルダ表示が出来るわけじゃないのですが、入力のアシストにはなるかと思います。プルダウンの場合はプレースホルダ表示はないので注意。
- 対象の列の▼をクリックする
- 列の型を編集するをクリック
- プレースホルダを表示をチェックオンする
図：プレースホルダ表示のオンオフ
テーブルに型を指定可能
Excelのテーブルにはない機能なのですが、本来テーブルにあるべき「列の型」を指定することが可能です。例えば数値型を指定しておくと、文字列が同じ列にあった場合エラーとして警告が出るようになっています。
数値型を指定した後に、その列に文字列を入力するとエラー表記が出て修正を促されます。これのお陰でGASなどのプログラムなどからも対象の列が数値であることがある程度保証できるため、コードが書きやすくなりますね。
型指定はそれぞれの列の「∨」をクリックして、列の型を編集する⇒指定したい型を選ぶことでセットされます。列タイトルには型指定した様子がアイコン表示されるのでわかりやすいです。日付型の場合はカレンダーが出てくるようになるので、手入力で変な日付形式入れられるといったことを防げます。
※ただし警告無視で入力自体は出来てしまうのでそこは要注意です。
図：列の型を指定する
図：型に一致しないデータのエラー警告
表示形式は列全体に統一される
通常のシートの場合はセル単位で表示形式を指定することが可能です。しかし、テーブルの場合はその特性上特定の行だけ違う表示みたいなことにはならず、表示形式は列単位で統一されます。特に日付の表記揺れであったり、通貨単位、小数点表示などが人によってあったりなかったりといったことが防げるようになってるのがテーブルの特徴の１つです。
故に、特定の列内のセル（日時形式）を選択状態にして、メニュー⇒表示形式⇒カスタム日時を開いた時に、xxxx年xx月xx日という形式を選ぶとデフォルトのxxxx/xx/xxという表記でなくなるだけじゃなく、列全体の表示形式も変更されます。ただ、プレースホルダが対応していないようで、プレースホルダ表示が変わりません・・・・
図：カスタム日時を指定してみた
図：表示形式は列全体に反映される
列名重複検知
テーブルのルールとして、同一の列名は指定出来ません。実際に同じ列名をしてみると赤い▲で表示され修正を促されます。これはもともとテーブルというのはAccessでもそうですが同一のタイトル名は使えないようになっています。そうすることでプログラムなどから参照した場合に列の特定が出来ないということがなくなるため、非常に重要な機能です。
※こちらも警告を無視はできてしまうのでユーザが注意を払う必要があります。
図：重複して同じ名前は使えません
数式の扱い
数式で対象の列の全部を指定したり、テーブル領域全部を指定するといつもの範囲指定ではなく「テーブル名」や「テーブル名[列名]」といったようテーブル構造で参照されるようになります。よって、vlookupなどで数式をコピーしても範囲ではないため、すべての行の数式が同じ数式となり、範囲固定をする必要性がなくなります（当然ずれたりしない）。
また、数式外の値を一番したに行に追加して値を貼り付けると自動で数式が反映されるようになっています（自動でテーブルが拡張されるため）。必ず範囲指定する場合は列全部やテーブル全部などを指定しないと通常の範囲指定となってしまうので注意です。
また、Index・Matchみたいな数式組み合わせを使った場合も、テーブル構造参照となるため、テーブルの列名で数式が構築されることからA列の・・・みたいな分かりにくい状況が解消されます。
図：数式がテーブル構造で参照されるようになる
配列関数を使える
Excelのテーブルにはない機能というか、Excelでは仕様上出来ない「テーブル内で配列で返す関数」を利用することが可能です。例えばFilter関数などは代表的な事例ですね。Excelだとテーブル内で関数自体は使えますが、配列で返す関数はエラーとなって使えないのです。
これができることで、自動フィルタした内容でもってテーブルとして扱えるので、かなりの手間が省くことができる地味に良い機能です。
UniqueやArrayformulaなども使えることになるので夢が広がります。ただし配列関数を使った場合、オートフィルタでの並べかえをすると壊れるので、数式自体でソートするように構築してあげる必要があります。
※またimportrange関数で他のファイルのデータを呼び出す場合には、タイトル列は含めないように注意しましょう。テーブル化が出来なくなります。
図：Excelでこれ出来なくて辛かった
テーブルリスト
作成したテーブルは各々ユニークな名前がついています。スプレッドシートの範囲の入力欄をクリックすると、作成したテーブルのリストが現れて、対象のテーブル名をクリックすると、そのシートにジャンプすることが可能ですので、どこに作ったのか？などを把握することが可能になります。
図：テーブルに直接ジャンプ
グループビュー
テーブル内の特定の列の値をもってグルーピングして表示する機能です。小計機能みたいなものは無いのですが、簡単にグルーピングして表示することが出来るため、小規模な用途では便利かもしれません。
この設定は保存しておくことが可能で、グルーピング解除後であっても、再度保存したグルーピングの名前を選べば、同じことが一発で実現できるようになっています。
図：ランクでグルーピングを実行中
図：グルーピングされた様子
フィルタービュー
前述のグループビューと同様にフィルタリングした内容を表示する機能も備わっています。オートフィルタとは違い、自分にだけ反映するカスタムビューであるため、他人の作業を邪魔しないのが特徴です。
グループビュー同様に名前をつけておけるので、複数のフィルタリング項目をあらかじめ保存しておけば手軽にビューを切り替えることが可能です。
図：設定してる様子
図：フィルタビューでフィルタしてみた
ドロップダウンリスト
Excelと違ってGoogleスプレッドシートはもともと「A2:A」みたいなA列全部指定でドロップダウンリストを作れてしまうので旨味はあまりないのですが、ドロップダウンリストでもテーブルの構造参照が使えます。すると、行が増えると自動的にドロップダウンリストの中身も増えます。Excelの場合これを実現するには名前付き範囲というものを併用しないと出来ないでの非常に厄介です。
図：ドロップダウン設定
図：きちんと行が増えてもリストが更新されてる
集計行機能を擬似的に実現
Excelのテーブルにある集計行機能が装備されていないのであれば、Query関数でつけてあげればいいじゃないか？ということで、やってみました。空のテーブルを作成して数式を一つ追加。別のテーブルの特定の列の合計を取ってみました。
|
1 |
={query(医薬品,"select *");{"総合計：","","","","",sum(医薬品[単位])}} |
すると、一番最終行にこれで集計行が入るので、擬似的に集計行機能を実現することが可能です。同様に、Query関数を使うことで2つのテーブルデータの連結が可能になるので擬似的にリレーションシップ機能も実現可能です。
※ただしImportrangeでタイトル行を含めて呼び出したものはテーブル化が出来ません
図：擬似的に集計行を実現できました
GASで行データを追加
Google Apps Scriptからテーブルのあるシートに対して、行データを追加したらどうなるのか？ということで以下のようなコードで追加してみました。無事にデータは追加されました。appendRowや２次元配列で最終行にまとめ書きならば追加出来そうです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
//appendRowで追加 function kinoko_insert() { //プロパでオートナンバー let prop = PropertiesService.getScriptProperties(); let uid = Number(prop.getProperty("uid")) + 1; prop.setProperty("uid",uid); //スプシを取得 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GAS"); //配列を用意 let array = [uid,"エリンギ","中級","350"]; //１行追加する ss.appendRow(array); } //２次元配列でインサート function kinoko_batch(){ //スプシを取得 let ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GAS"); //最終行を取得 let endrow = Number(ss.getLastRow()) + 1; //書き込み用2次元配列 let array = [ [6,"やまぶしたけ","中級","400"], [7,"ウスキキヌガサタケ","高級","10000"], ] //一括書き出し let lastColumn = array[0].length; //カラムの数を取得する let lastRow = array.length; //行の数を取得する ss.getRange(endrow,1,lastRow,lastColumn).setValues(array); } |
図：きちんとテーブルが拡張されました
Google Formの回答シートもテーブル化しておいた
Google Formは標準で回答内容をスプレッドシートにリンクして書き出しすることが可能です。その書き出し対象のスプレッドシートを開いてあらかじめテーブル化しておいたらどうなるか？
無事に書き込まれました。ただ型指定は無視されて書き込まれますので、きっちり型どおりにしたいということであれば、Google FormにGoogle Apps Scriptを記述して前述のように自力で整形してあげてスプレッドシートに書き込むようにすると良いでしょう。
図：フォームを作ってみた
図：無事に書き込まれました
注意点
勝手に横拡張
配列関数をつかってテーブルに表示をしていたときに、参照してる別のテーブルを1列拡張したら、参照元のテーブル（filter関数で拾ってる）側の列が勝手に拡張されてColumn7とかいった具合に5列ほど空っぽの列が勝手に拡張されました。バグだとは思いますが、消せば問題有りません。参照元テーブルに行を追加してもこの現象は起きませんでした。
図：Filter関数などを使ってる場合は要注意
Query関数でおかしな挙動
54000行ものデータをQuery関数に食わせて単純に出力をしてみたところ、4500行程度拡張しきれなくて、手動で追加するとエラーが解消しました。これなのでこれをテーブル化する時は注意しましょう。
指定した数式は以下のようなものです。故に、挿入してください的なエラーが出てる場合には、空行を指定行数分以上に足して上げると表示されます。
|
1 |
=query(医薬品,"select *") |
図：4500行程度拡張しきれなかったらしい
Importrange関数では参照出来ない
これまたよく利用する「他のスプレッドシートの範囲を召喚する関数」であるImportrangeですが、テーブルを参照できるか？とやってみましたが、それは流石に出来ませんでした。外部からはテーブルを参照することは難しいもよう。ただし、コレまで通り、以下の数式のように範囲指定であるならば問題なく呼び出すことは可能です。
タイトル行つけてimportrangeで呼び出してあげたものをテーブル化すれば、事実上のimportrangeでテーブル化した形にはなります。元データのタイトル行は持ってこないように範囲指定しましょう。
|
1 |
=importrange("スプシのURL","医薬品!A2:F") |
図：テーブル内で通常通り呼び出せばテーブル化となる
今現在は未対応の機能
現状で出来ないことやイマイチなことを列挙してみたいと思います。これらは将来的には対応してくれるであろうという期待事項になります。
- Google Apps Scriptにて現在はまだテーブルという塊でデータの参照は出来ません（getDataRangeを使う必要がある）
- Excelのテーブルを直接読み込ませましたが、テーブルとして認識されず。変換しても同じく。互換性が取れていないようです。（ただしテーブル有りのスプレッドシートをエクスポートするときちんとExcelではテーブルとして認識されてる）
- Excelテーブルにはある「集計行」機能がまだ存在しない
- 型は表からテーブル変換時には自動判定して設定してくれない
- 列にてvlookupで数式を一番上に入れても他の行まで自動で数式反映してくれない（Excelはこれを自動でやってくれる）。ただし自動入力の候補としては出てくるのでこれで代用可能。
- Excelのテーブルにある「リレーションシップによるピボットテーブル」は使えません。
- [#ALL]は使えるものの、テーブルのタイトル行まで含めて数式で指定して呼び出すことは出来ません。
- テーブルドラッグしてテーブルを拡張することが出来ません。
ExcelにはあってGoogleスプレッドシートには無い機能が結構ありますが、一方でExcelにはなくGoogleスプレッドシートにあるといった点もあるため、現状ではどっこいな感じです。
図：テーブルに互換性がないようだ
