初心者でもわかるGoogle Apps Script活用のススメ

動作が重いスプレッドシートの原因とすぐに実践できる解決方法

サイト管理人

GoogleスプレッドシートはGoogleアカウントがあれば無料で利用ができる表計算ソフトでMicrosoft OfficeのExcelとほぼ同じように使えるとても便利なサービスです。

その便利さから、利用されている方は少なくないと思いますが、データ量や関数量が増加すると動作が極端に遅くなり作業スピードの著しい低下を引き起こしてしまうことがあります。

今回の記事では、動作が遅くなったスプレッドシートの改善をするための方法をご紹介します。

そもそもスプレッドシートの動作が遅いとは？

遅いスプレッドシートの特徴としては次の3つが考えられます。

動作が遅いスプレッドシートの特徴としては次の3つが考えられます。

  • 右上のプログレスバーが出たままなかなか消えない
  • 関数を入力しても結果が表示されない
  • スプレッドシートのタブ/ページが応答しない状態になる

スプレッドシートが遅いとは、一般的に上記のような状態になってしまうことを言います。

1つ目のプログレスバーは、ご存知でない方もいるかもしれませんので念の為補足を入れます。たくさんの関数が入っているスプレッドシートを開いたときやそのファイルを編集した場合に右上に表示される下記画像のようなバーのことをプログレスバーと呼びます。

処理が終了するとプログレスバーが自動的に消えるのですが、関数が多数入っているスプレッドシートを開いたり編集したりするとこのバーがなかなか消えない状態になってしまいます。そうなると他の入力した情報を再計算して結果を更新してくれなくなってしまうため作業性がかなり低下してしまいます。

２つ目の関数の結果が表示されないという症状については、関数を入力して確定しても入力したセルが空欄のままになってしまう状態です。大体の場合は時間が経てば結果が表示されますが、最悪のケースでは３つ目のページが応答しない状態になってしまいます。

このような状態で作業するのはかなりストレスが溜まりますので、できるだけ回避したほうがストレスを溜めずに作業をすることができると思います。

スプレッドシートが保持できる上限数はあるのか？

スプレッドシートには様々な上限が決まっています。段階的にその上限は緩和されていますが、今後も上限が撤廃されることはないと考えられます。本サイトではスプレッドシートの横方向のセルのことを列（Column）と呼び、縦方向のセルのことを行（Row）と呼びます。

  1. セルの上限数 = 5,000,000セル
  2. 列の上限数 = 18,278列
  3. セルの上限文字数 = 50,000文字
  4. 一度に追加できる上限行数 = 40,000行

このように上限が決まっていることから、上限を考慮してスプレッドシートを運用する必要があります。列数には少ない上限数が設定されていますので、縦方向にデータは追加していく方が良いと思います。上限を過ぎてしまった場合は警告ダイアログが表示されて入力や追加などが実行できなくなります。

動作が遅くなったスプレッドシートの見直すべきポイント

見直すべき項目はいくつかありますが、一つずつ見直すことでスプレッドシートの動作スピードが劇的に向上させることができる可能性があります。

使用していないタブやセルは削除する

例えば、
A列からD列までのみを使用しているすれば、E列以降のすべての列を削除してしまいましょう。
100行目までしか使っていなければ101行目以降をすべて削除してしまいましょう。
スプレッドシートには表示できるセルの上限数が決まっておりますので、極力必要なセルのみを表示するようにしましょう。タブについても必要のないタブは作成せずに、もし作成したとしても必要がなくなったら削除するようにします。

可能な限り関数を文字や数値に置き換える

関数である必要がない部分は可能な限り文字列や数値に置き換えるようにしましょう。
関数の量はスプレッドシートのパフォーマンスに一番影響を与える部分になります。
関数を文字列や数値に置き換える簡単な方法としては、セルをコピーして値のみ貼り付けを行うことで簡単に実施できます。
値のみ貼り付けをする方法はセル上で右クリックして『特殊貼り付け』→『値のみ貼り付け』を選択するか、Shift + Alt + Vで置き換えが可能です。
複数セルを一気に貼り付けることもできますので、簡単に関数から文字列や数値に置き換えることができます。

必要の無い条件付き書式や保護を削除する

スプレッドシートの高速化には基本的に無駄なものを削除することがおすすめです。使用していない必要のない条件付き書式やシート保護などは極力削除しましょう。

関数を最適化する

関数を指定するときは上限値を決める

関数の中には複数セルを指定できる関数があります。身近な例を上げれば、SUM関数です。=SUM(A1:A)というような形で記載すると思いますが、もし行数の上限(例えば1000行目までなど）が決まっているのであれば次のように記載したほうが結果的にパフォーマンスの低下を防ぐことができます。
=SUM(A1:A1000)
この理由としては、最初の関数（=SUM(A1:A)）の場合は計算の必要のないセルまで含めて値の有無を確認し、処理をしてしまうためデータ量が多くなった場合に動作が遅くなる可能性があるためです。

編集時に再計算される関数の乱用をしない

関数の中にはスプレッドシートを編集時に自動的に再計算されてしまう関数が存在します。TODAY関数、NOW関数、RAND関数、RANDBETWEEN関数がこのような関数になります。関係ないセルを編集したとしてもこの関数は毎回計算を行い、この関数が入ったセルを参照している他のセルの関数を含めてすべてのセルが再計算を始めてしまいます。そのため、このような関数の使用はできるだけ控えた方が良いと言えます。

ARRAYFORMULAを乱用しない

私が以前投稿した

あわせて読みたい
【検証】スプレッドシートの高速化にARRAYFORMULAは本当に有効か？

にも記載したように、ARRAYFORMULAは決してスプレッドシートを高速化してくれる関数ではありません。少ないデータ量を処理する場合はそれほど問題はないと思いますが、データベースのように膨大なデータが入ったスプレッドシートでは使用することをおすすめしません。できる限り使用頻度を抑える運用を考えてみましょう。

他のページやスプレッドシートからデータを取得することを抑える

次に挙げる関数は他のスプレッドシートやウェブサイトなどから情報を取得してスプレッドシートにデータを出力します。

  • IMPORTRANGE関数
  • IMPORTHTML関数
  • IMPORTFEED関数
  • IMPORTXML関数
  • IMPORTDATA関数

これらの関数は他の関数よりも処理に時間がかかる傾向があるため、どうしても必要な場合に限定して使用することをおすすめします。

IF関数を活用し必要時のみ実行されるように工夫する

IFを使わない状態で関数が大量に書かれているスプレッドシートの場合、その関数が必要な値を出力しない場合にも処理がされてしまい、結果として何も表示されないあるいは使わないデータを出力してしまうことがあります。そのような状況を回避するためには、必要な場合には処理がされ、必要でない場合には空欄あるいはデータがないというような出力がされるようにIF関数を使って工夫することをおすすめします。

具体的な関数形式で説明すると、
=IF(A1=””,””,VLOOKUP(A1,D1:F1000, 2,false))
このような形になります。A1セルにデータが入っている場合にはVLOOKUP関数を実行し、空欄の場合には空欄を出力するという手法です。こうすることでA1セルが空欄の場合に無駄にVLOOKUP関数を実行せずに済みます。

更新頻度の高いスプレッドシートはファイルをコピーして入れ替える

スプレッドシートの編集を行うと、自動的に更新履歴が作成されバージョン管理をしてくれます。とても便利な機能で、必要なときに以前のバージョンに戻すことができたり、誰が変更したのかを確認することができます。しかしながら、こちらのバージョンデータはスプレッドシートのファイルに保存されているため、更新頻度が高いファイルにはどんどんバージョンデータが保存されていきます。このバージョンデータが原因となってスプレッドシートの体感処理速度が遅くなる場合がありますので、その場合はファイルをコピーして新しいスプレッドシートを使用するようにしましょう。この場合、気をつけなければいけないことは、他のスプレッドシートからコピー元のスプレッドシートをIMPORTRANGE関数で参照していたり、GASなどでスプレッドシートIDをコードで使っている場合はファイルIDが変わりますので修正が必要になります。

関数をGoogle Apps Scriptに置き換えてみる

GASの処理はGoogleのサーバー上で行われるため、計算処理が原因でその他の動作が遅くなったりすることはありません。これを利用してGASを駆使してスプレッドシートの軽量化を目指しましょう。
GASでは、データが入力されたやスプレッドシートを開いたときに自動的に処理がスタートするトリガーが存在します。onEditやonOpenがこれに当たりますが、そのようなトリガーを使用して極力関数を減らすことでテキストデータのみ入力されたスプレッドシートを作ることができます。

上から順に実施しやすい順に列挙してみました。できることから実施して軽量化を目指してみましょう。一度動作がかなり遅くなってしまった状態になると同じファイルで軽量化をすることは難しいので、そのときにはファイルをコピーして置き換えることも検討してみましょう。関数のGAS化については、本サイトで使い方を学んでもらい、挑戦できるようになれば良いですね。

まとめ

今回は、スプレッドシートの動作が遅くなってしまった場合に検討するべき項目をご紹介しました。公式に発表されているわけではない情報もあり、その部分は私がスプレッドシートを使用してきた体感をベースにしていますので、情報が正しくない可能性があること予めご了承下さい。簡単に実装できるものから実装に検討やスキルが必要なものまでありますが、できることから実装してみましょう。快適なスプレッドシートライフをみなさんが送れることを願っています。

もし私にスプレッドシートを軽量化して欲しい！という方がいれば問い合わせからご依頼下さい！処理速度を劇的に向上させますよ😀

問い合わせ
Exit mobile version