Google スプレッドシートを快適に使うコツ(2019年版)

こむ

2019年5月4日 17:47

Google スプレッドシートの便利さに慣れてきて、脱Excelしてガッツリメインで使っていきたいけど、どうも細かい使い勝手で移行しきれずにいる。そんな方のためのnoteです。

対象としている読者

・ファイルをメールでやり取りするのを心からやめたい人
・PCだけでなくスマホでも快適にデータを確認したい人
・ローカルでファイルを管理することが苦痛になってきている人

対象としていない読者

・Microsoft Excel の無料の代替を探している人（代替ならLibreOfficeがオススメです〜）
・Googleのサービスに縛られるのは嫌だ！と思っている人（もはや無理に使わなくてもいいんじゃ、、）

使い方のコツ

まずサクッと結論です。

（１）余計なことをしない
（２）仕事をGoogle スプレッドシートの機能に合わせる

以上です。「えー」って感じかもしれませんが、海外サイト含めていろいろ調べた結果の結論です。残念ながら銀の弾丸はありませんでした、、。しかしこの2つは大原則になります。「Excelではこうやっていたんだけど」はご法度です。なぜならGoogle スプレッドシートは違う思想で作られているサービスで、Excelユーザーの乗り換えを狙ったサービスではありません。

Excelに長年慣れ親しんでしまった人が犯しがちな過ちの1つが、表計算ソフトを巨大な方眼紙のように使ってしまうことです。文字とか画像を貼り付けてキレイな見た目のものができるのでとても便利なのですが、そもそもこれは表計算ソフトとして使い方が間違っています。表計算ソフトには表の計算以外をさせてはダメで無駄な飾りは必要ありません。

このようなことを気にしながら新しいソフトを触る気持ちでGoogle スプレッドシートに向き合ってみると幸せになれるかもしれません。それではコツを紹介していきます。

コツその1〜行・列は有限に設定する

Excelは昔6万5千行までしか使えなかったんですよ〜という昔話は置いといて、Google スプレッドシートで空白のシートを開くと1000行のところで「行を追加」表示が現れると思います。ついExcelのほぼ無限シート的な感覚で「あとで面倒なのでとりあえず3000行ぐらい追加」とかやりたくなってしまうのですが、それはご法度です。行・列は困ったときのみ追加しましょう。この理由はベンチマーク結果を見ると一目瞭然です。ここでブックを開き切る（処理中表示が消え全てのUIがreadyとなる）までの秒数を測定してみると以下のようになります。

- デフォルトの1000行✕27列セル→5.5秒

- 1行✕1列セル→4.0秒

※ちなみに以後このような秒数を測ったベンチマーク結果が出てきますが、マシンはGoogle スプレッドシートを動かすにはわりと速めのマシン(Chromebook C302CA)を使用しています

このように表サイズはてきめんにパフォーマンスに影響しますので、常に最小の行・列のみ利用することを心がけてください。

コツその2〜式は最低限に

なんとなく凝った式を埋めると仕事したった感は出るのですが、じわじわ重くなっていきます。何種類か式を試してみましょう。

- デフォルトの1000行✕27列セル→5.5秒
- RAND()で埋める→9.5秒
- RAND()を起点にして横のセルを参照するIF文で埋める→13.5秒

これはつまり「Xの結果を利用しYを計算する」という伝播が続くようなシートになってしまっていると、どんどん重くなることを示しています。不要な計算は減らしましょう。

コツその3〜同じ式はオートコンプリートではなくarrayformulaを使う

ちょっと慣れが必要なのですが、効果があるTIPSです。Excelで数式を入れて右下の黒四角をダブルクリックして同じ数式を下まで埋めるオートコンプリートを使用することはよくあると思います。しかしGoogle スプレッドシートでは前記のように式の数が重さに比例してくるため、この操作を行うとシートの表示が遅くなってしまいます。それを解決するのがarrayformulaです。

arrayformulaは上記のように最初のセルに配列数式を入れることで、元配列で埋まっている行の空白部分に自動で計算結果を埋めてくれます。試しに下のセルにカーソルを合わせると自動で埋められた数字のみ入っているのが分かります。

それってセルに変な値が入っていたら危ないじゃん！と思われるかもしれませんが大丈夫、手入力があると以下のようにarrayformulaが無効になります。

ちなみに私はarrayformulaを適用したことを忘れないように私は適用した場合に特定の背景色をつけたりしてます。

コツその4〜グラフはタブに飛ばしてしまう

表からグラフを作ってシート上に並べる、というのはよく行う操作だと思いますが、Google スプレッドシートでそれをやってしまうとシートが結構重くなってしまいます。ここでいくつか最適化を試してみました。1000行のデータに7つのグラフがあるブックで以下の3種類作成してみました。

- シート上にグラフベタ乗せ（最適化なし）→8.5秒
- グラフだけを別シートにまとめる→8.3秒（あまり効果なし）
- グラフを個別にタブにする→6.3秒に短縮

グラフを個別タブ（シート）に移動するには、グラフの右上にあるメニューから「個別のシートに移動...」オプションを有効にします。

表とグラフを同時に見る用途では適用できませんが、そもそも表の行数が多くてグラフを見ることがメインの用途の場合はこの手法で高速化がはかれ、かつこのレイアウトにしておくことでスマホでの操作性を上げることができます。

コツその5〜Drive上でgsheetファイルを整理する

まず原則として、スプレッドシート作成時に作られるDrive上の.gsheetというファイルは単なるリンクです。データや数式その他はGoogleのDBの奥深くに格納されているため、この.gsheetファイル自体にはデータなどは含まれず、コピペして他の人のPCで開こうとしても権限が無く開きません。ただし、Drive上でこのリンクを消してしまうとデータも一緒に消えてしまうので作成された.gsheetファイルは大事に管理してください。

例えばCSVからスプレッドシートを起こした場合は、CSVとgsheetファイルをDrive内の同フォルダに整理しておくと後々便利です。単なるリンクなので、スプレッドシートの編集中に.gsheetを移動させても不具合は起こりません。もしくは.gsheetの整理を諦めてファイルを開くときはファイル名の検索で辿るというGoogle Driveの特性を活かした使用法も可能です（ただしワークブック名は検索しやすくしておく必要はあります）。

コツその6〜見るだけならスマホアプリが最強

これはベンチマークをしていて気づいたことです。ChromebookはPC版（Chrome上）のGoogle スプレッドシートとAndroidアプリのGoogle スプレッドシートの2つを同時にインストールすることができます。AndroidはChromeOS上ではコンテナ環境になるので基本的によりパフォーマンス出なくなる方向になるけど一応ベンチマークとっておくか〜、と測定してみたら…あれ、、倍ぐらい立ち上がりが速くて切り替えも速い…。

こちら推測になるのですが、リアルタイム編集を基本とするPC版と、閲覧のみに特化したAndroid版では、Android版はリアルタイム編集機能を切っている分速くなっているのではないかと思います。Android版では編集は一部の機能に限られるのと、編集を確定するためには✓をタップする必要があり、ここらへんがPCのフル機能版とは異なりそうです。例えばグラフだけ眺めるのにスマホを利用する、とか斬新な活用法もあるかもしれませんので、スマホアプリはどうせ遅いだろとタカをくくらず試してみることをオススメいたします。

コツその7〜マクロ / GAS(Google App Script)でタスクの自動化

反則技っぽいですがExcel VBAのノリで自動化できます。ここらへんは他に良記事がたくさんありますので個人的な感想だけ以下にまとめておきます。

メリット

- マクロでざっくり繰り返し処理ができる
- JavaScriptの最低限の知識があればスクリプトの編集ができる
- ライブラリとして公開して他の人に関数だけ簡単にシェアできる

デメリット

- 全てクラウド側のAPI間を経由する処理になるので動作はExcel VBAよりかなり遅い
- ES6記法に対応してない(他のJSのライブラリをコピペして動かないことがある)

例えば以下は私がよく使う、2列の比較グラフを作成して独立したシートに飛ばすスクリプトになります。1つのブックで10回ぐらい同じ操作をするのでライブラリにしてます。

//2カラム並べるLine-Chart、最後に独立シートに飛ばす
function plotCompare2(col1, col2, sn){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var chartrange = sheet.getRange("A:A").getLastRow();

  var chart = sheet.newChart()
  .asLineChart()
  .addRange(spreadsheet.getRange(col1 + '1:' + col1 + chartrange))
  .addRange(spreadsheet.getRange(col2 + '1:' + col2 + chartrange))
  .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
  .setTransposeRowsAndColumns(false)
  .setNumHeaders(-1)
  .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
  .setPosition(1, 1, 0, 5)
  .build();
  sheet.insertChart(chart);
  sheet = spreadsheet.moveChartToObjectSheet(chart);
  sheet.setName(sn);   
}


//メイン関数
function plotAll() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var chartrange = sheet.getRange("A:A").getLastRow();

  //先頭行の固定
  spreadsheet.getActiveSheet().setFrozenRows(1);
  //列幅の最適化
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveSheet().autoResizeColumns(1, 30);
  
  plotCompare2('D','M','new_chart');
  
  Browser.msgBox("Finished", Browser.Buttons.OK);
};

コツというか苦しい回避策〜散布図で複数系列を重ねる

以下は機能的にちょっと苦しい機能の回避ネタになります。

(X1,Y1)と(X2,Y2)のように異なる粒度の配列を1グラフ内で比較したい、ということがあると思うのですが、グラフの基本機能として2つのX系列を追加することができません。回避策として以下のように表をくっつけてX系列を1列にすることで1グラフ内で描画することができます。

最後に

本当は「こういう技を使うと劇的に良くなるよ！」的な記事にしたかったのですが、かなり地味な記事となってしまいました、、、。しかしこれらの実践で、少なくともこの半年で自分が業務上扱う表計算シートで「Excelじゃないと困る」というものはなくなって、オンラインツールのメリット・高い生産性を享受できるようになりました。ここで大事なのがやはり「Excelというレガシーソフトのアンラーニング」です。全てがオンラインツール化して仕事のスピードが上がっている中でローカルファイルに依存するレガシーソフトから離脱できると自然と非効率になっている日常を変えることができるのではないでしょうか？皆さんもぜひチャレンジしてみてください！

この記事が気に入ったらサポートをしてみませんか？