スプレッドシートが重いを「設計」で直す｜消すべき数式と、残していい手入力

「スプレッドシート 重い」で調べると、不要な行と列を削除する、画像を減らす、条件付き書式を整理する、キャッシュを消す、といった対処が並びます。これらは間違っていませんし、実際に効くこともあります。

ただ、これらで直るのは「1回きりの汚れ」です。掃除したのに数週間後にまた重くなるなら、掃除の対象ではなく、汚れが溜まる作りのほうを疑ったほうが早いです。

切り分けは簡単で、症状の出方を見ます。特定の1シートだけ遅いのか、ファイル全体が開かないのか、他の人が編集している時だけ遅いのか。ここで原因の当たりがかなり絞れます。

重いシートを開くと、たいてい1枚のシートの中に、手入力する列と、その隣に計算列と、右のほうに集計表とグラフが同居しています。作った時は便利なのですが、これは構造的に重くなります。

理由は、入力するたびに同じシート内の計算が走るからです。しかも集計側は範囲を広めに取っていることが多いので、1セル直すだけで広い範囲の再計算が発生します。人が増えるほど、この再計算が重なります。

分け方は、入力（ローデータ）・計算・表示の3枚にするのが基本です。入力シートには数式を置かず、ひたすら1行1件で貯める。計算シートで必要な形に整える。表示シートは見るだけにする。これだけで、入力時の体感がかなり変わります。

副次的な効果として、入力シートに数式が無いと、コピー&ペーストで数式を壊す事故が減ります。現場が壊すのではなく、壊せる作りになっていただけ、という場合が多いです。

営業の案件管理シートは、この入力側にあたります。列を絞って毎日触れる形にする組み方は営業の案件管理シートを1枚で回すに書きました。

別ファイルの数字を持ってきたいとき、IMPORTRANGEは手軽です。ただ、これが増えると重さの原因になりやすい関数でもあります。

問題になるのは数そのものより、つながり方です。AがBを取り込み、BがCを取り込み、CはさらにDから取り込んでいる、という多段構成になると、末端の1ファイルが更新されるたびに上流が順番に待たされます。どこが遅いのかも分かりにくくなります。

現実的な直し方は3つです。1つ目は、取り込みを1段に揃えること。取り込み元はすべて同じ階層に置き、Aから直接引く。2つ目は、必要な列だけを取り込むこと。シート全体を持ってきて手元で絞るのではなく、取り込み元にあらかじめ「渡す用」の範囲を作ります。3つ目は、そもそも別ファイルに分ける必要があるかを見直すことです。権限を分けたいという理由が無いなら、1ファイル内のシート分けで済みます。

更新頻度の低いデータ、たとえば月次で確定する数字や、年に数回しか変わらないマスタは、取り込みを常時つなぎっぱなしにせず、確定したタイミングで値として貼り付けるほうが軽く、事故も減ります。

出典・一次情報：データの参照を最適化してスプレッドシートのパフォーマンスを上げる（Google ドキュメント エディタ ヘルプ）。IMPORTRANGEはスプレッドシート間の参照にインターネットを使うため、同じファイル内での参照よりも遅くなること、参照チェーンはシートの動作を遅くすること、SUM(A:A)のような非限定範囲の参照は空白セルを含めて読み込まれることが、いずれも公式ヘルプに記載されています。仕様は変更されます。最新の情報は公表元でご確認ください。

重さの相談で意外と多いのが、自動化しすぎているケースです。すべての列を数式で埋めた結果、月に1回しか変わらない値のために、毎回シート全体が計算されている。この状態は、手で入れたほうが軽くて速いです。

判断の目安は、更新頻度と、間違えたときの気づきやすさです。頻繁に変わって人が追えないものは自動化する価値があります。逆に、めったに変わらないもの、変わるときに必ず人が判断するもの、間違っても誰も気づけないものは、手入力のほうが安全な場合があります。

とくに、確定した過去のデータを数式のままにしておく理由はほとんどありません。月次で締めたら、その月の集計結果は値に変換して固定する。これだけで、月を追うごとに重くなる現象は止まります。

「自動化＝善」ではなく、毎日変わるものを自動化して、変わらないものは固定する。この線引きが、結果として一番軽くなります。

一度に全部やろうとすると、途中で数字が合わなくなって戻せなくなります。順番があります。

最初にファイルの複製を取ります。次に、掃除で直る分（使っていない行・列、広すぎる条件付き書式、貼りっぱなしの画像）を片づけて、どこまで軽くなったかを見ます。ここで十分なら、設計に手を入れる必要はありません。

それでも重いなら、入力・計算・表示の3枚に分けます。ここが一番効きますが、参照の張り替えが発生するので、分けた直後に必ず主要な数字を旧ファイルと突き合わせてください。合っていることを確認してから、IMPORTRANGEの整理と、過去データの値固定に進みます。

最後に、次に重くなる条件を決めておきます。行数がどこまで増えたら年度で分けるのか、履歴をどこまで残すのか。ここを決めていないシートは、直しても半年後に同じ状態になります。