関数などで重いスプレッドシートを軽くする

仕事で良くGoogle spreadsheetを使います。
複数人で使っていて便利ですよね。

しかし、サービスの運用などで長年使っておりデータ量も日増しに増えていき、徐々に重くなって気づいたら開くだけでも数分かかるみたいなことが発生します。

今回はそんなシートたちを軽くした方法のメモとこのブログを見てくれる誰かのための共有です。

まずはデータ量の確認

1000行2000行ではまず遅くなりませんが、5000行や1万行を超えると場合によっては重くなってきます。
まずは、そんな行数の多いシートから目星を付けてやっつけていきましょう。
非表示にしているシートでも裏では処理がされているので、非表示にしているシートも確認しましょう。

使っている関数の確認

1万行を超えているシートでもただの値だけが入っている場合は、そこまで遅くなりません。
やばいのはRANKやLOOKUPなど他のデータを範囲で見て処理する系の関数になります。
そのシートに範囲選択をしている関数がないか確認しましょう。
そういった関数があった場合、一度関数を使って結果を出してしていればいいデータはコピーして右クリックから値のみ貼り付けをし関数から値に変更するか、毎回処理をしないといけない場合ARRAYFORMULA関数を使い計算回数を減らします。

他のシートに依存していないか確認

IMPORTRANGEで別のワークブックのシートを参照している時は、その読み込みが終わるまで処理が続きます。
軽いシートを参照している時は大した処理ではないのですが、大量のデータがあったり、IMPORTRANGEの参照先で大量の処理をしていた場合やIMPORTRANGEの参照先が更にIMPORTRANGEをしていて別のシートを参照している場合はかなり時間がかかります。
VLOOKUPでも別シートを参照していて、そのシートで大量の処理をしていた場合はそれが終わるまではVLOOKUPの処理が開始できないので、それが何シートか跨っているとかなりの時間がかかる場合があります。
先述の通り、実際の値にするかARRAYFORMULA関数にして回避しましょう。

条件付き書式を確認する

他のブログにはあまり書かれていなかったのですが、条件付き書式でもかなりの処理が発生する場合があります。
特定の文字に色を付けたかったり、特定の行に色を付けたかったりするのに便利ですが、これも何千行とかあるシートの場合時間がかかります。
潔く条件付き書式をやめるか、処理回数が減らすような方法を取りましょう。
e.g.) シート内でOKの文字がある場合青背景色にNGの文字がある場合赤背景色にしたい場合、OKとNGの2つの条件付き書式を設定するのではなく元々の背景色を青にしてNGの文字がある場合のみ背景色付けるように条件付き設定するなど

諦めてExcelに移す

ブラウザ経由のスプレッドシートよりExcelのほうが軽いです。
特定の人しか使わないような関数がいっぱいの分析用データなどはExcelの方が適しています。
諦めましょう。

以上です！
それではシートの重さにイライラせず素敵なGoogle spreadsheetライフを！