スプレッドシートが重い。原因はたいていこの5つです

前回、自動化する前に決めておくことを書きました。今回はもう少し手前の話です。

「スプレッドシートが重くて使い物にならない」。これ、データ量が多いからだと思われがちですが、実際には数千行程度で重くなっていることがほとんどです。原因は行数ではなく、書き方のほうにあります。

自分の帳簿や売上の管理表も、最初は待ち時間が長くて困っていました。次の5つを順に直したら、作り直さずに普通に使えるようになりました。

■1. 列全体を指定している数式

一番多い原因です。

　＝SUMIF(A:A, …)

　＝VLOOKUP(…, Sheet2!A:Z, …)

　＝COUNTIF(B:B, …)

この書き方は楽なのでつい使ってしまいますが、スプレッドシートは1列あたり最大100万行を持っているので、空白を含めて毎回計算しています。これが10個20個あると、それだけで数秒止まります。

直し方は2つです。必要な範囲だけ指定する（A2:A5000）か、元データの範囲をテーブル形式や名前付き範囲にしておくか。前者だけでも十分効きます。

■2. 揃発性関数を使っている

下の関数は、シートのどこを触っても全部が再計算されます。

　NOW、TODAY、RAND、RANDBETWEEN

　INDIRECT、OFFSET

特に INDIRECT と OFFSET は、便利なので集計シートに散らばりがちです。セルを一つ書き換えただけで全体が計算し直されるので、数が増えると一気に重くなります。

TODAY は、日付を手入力のセル1つにまとめてそこを参照する形にする。INDIRECT は、シート名を動的に組み立てるのをやめて QUERY や FILTER に置き換える。この2つでかなり軽くなります。

■3. 条件付き書式を広い範囲にかけている

見落とされがちですが、条件付き書式はルール1つにつき範囲全体を毎回評価します。

・「シート全体」にかけている

・ルールが10個以上ある

・カスタム数式で INDIRECT を使っている

このどれかに当てはまったら、まず範囲を実データのある行数までに縮めてください。似たようなルールが複数ある場合は、1つのルールにまとめられないかも見てみる価値があります。

■4. 他のファイルをたくさん参照している

＝IMPORTRANGE は便利ですが、外部のファイルを取りにいく通信が発生します。散らばすほど遅くなり、参照先が更新中だとエラー表示にもなります。

対策は「窓口を一つにする」ことです。IMPORTRANGE は取込専用シートに1回だけ書いて、他のシートはそのシートを参照する。これだけで通信回数が激減します。

■5. 使っていない行・列と、残っているもの

最後は地味な話です。

・データは500行なのに、1万行分の空行が残っている

・横にも使っていない列が延々とある

・一度貼ってそのままの画像や、消し忘れたメモ・フィルタ

・非表示にしただけの古いシート

使っていない行と列は、非表示ではなく削除してください。元に戻すのはいつでもできます。

■直す順番

全部一度にやると、どれが効いたのか分からなくなります。この順で、毎回重さを確かめながら進めるのがおすすめです。

　1. 使っていない行・列を削除する（一番安全で効果が見えやすい）

　2. 列全体指定の数式を、範囲指定に直す

　3. 条件付き書式の範囲を縮める

　4. INDIRECT・OFFSET を減らす

　5. IMPORTRANGE を取込シートに集約する

■それでも重いとき

ここまでやってまだ重い場合は、数式ではなく構造の問題です。1枚のシートに「元データ」「計算」「見せる表」が混ざっていることが多いので、この3つをシートとして分けます。

元データは追記するだけで編集しない。計算はそこを参照する。見せる表は計算シートを参照する。この形にすると、重さだけでなく、あとから項目を追加するときの手間も減ります。

■まとめ

重い原因は、データ量よりも「必要ないところまで計算させていること」です。列全体指定、揃発性関数、広い条件付き書式、IMPORTRANGEの散らばり、使っていない行列。この5つを見るだけで、作り直さずに済むことがほとんどです。

Webの公開情報を調べて一覧表にまとめる仕事をお受けしています。渡すときは、元データと集計を分けて、あとから項目を追加しても崩れない形でお渡ししています。表の作り方から相談したい、という段階でも構いません。