こんにちは。Excel開発15年、セルネッツの竹本です。
今回は「エクセルが重い！遅い！開かない！残骸チェックで原因究明」というテーマで解説をしていきます。
まずエクセルファイルのサイズが重い原因ですが、基本的には情報量が多いからになります。
着目すべきポイントはいろんな情報がくっついて重くなっているのですが、必要な情報ではない不要な残骸が含まれているとどんどん重くなっていくので、その見極めをするスキルが重要になってくるというお話になります。
具体的にその情報とはなんなのかというお話です。
5つあります。
1つ目は物理的なデータの量になります。シートの枚数です。物理的に増えれば増えるほどサイズは増えていきます。
データ量は厳密には行数が多ければ重いというものでもなくて、たとえば商品マスターのような商品コード・商品名で構成されるような、2列で構成されるデータが1万行あっても大したデータ量ではないんです。
そして、逆に行数が少なくても列数が大量にうしろのほうまでびっしり使われていると、行数・件数は少ないけれどもサイズが重くなるので、考え方としては行数×列数で使われている値がセルです。値が入っているセルの個数という考え方です。
または値は入っていなくても、書式だったり設定がついているようなケースがデータが重くなります。実際にエクセルで開いてセルの書式設定を見ていただくと、これだけの標準の機能がついています。
書式を変えたり、寄せ、書体、罫線、塗りつぶしなどがありますが、多岐にわたる種類がいろんな使い方でどんどん増えていくと重くなっていきます。
というのが2番目の書式関係の影響です。
3番目はわかりやすいと思いますが、数式の数が多ければ多いほど重くなります。
たとえばたまに見かけるのが、エクセルのシートがあって、数量×単価で金額が求まるケース。数量・金額を入れたら自動的に求まるように、100万行まで数量×単価の数式を入れているケースです。
実際に使っているところは上のほうだけだったりするので、これらは本当に重くなります。数式がサイズを重くしているというのはわかりやすいと思います。
4番目、オブジェクトです。図形・画像・コメントのことです。
ここでの図形とは、挿入から図で矢印・四角・丸・罫線などの線です。色がなくなってしまえば見つけにくい残骸になってしまいます。
残骸とは、その名の通りなくても良いものです。目に見えないケースがたまにあります。不要な情報はバッサリ削除。そして目に見えない残骸に注意をしてくださいということです。
目に見えない残骸をどう見抜くのかを次にお話します。
まずファイルのサイズがどんどん増えていく、どこが大きな要因なのか。
なんでもない1万行のプレーンなデータがあったときに、罫線がついて、塗りがついて、数式がついて、数式の列数が増えて、さらに条件付き書式が加わって、だんだんと重くなっていきます。こちらのファイルはプレーンなデータ、528Kのものがだんだん重くなっていくと例です。
実際にやっていきます。
（以下デモ）
これがプレーンな状態です。1万行のデータ、100という値が入っています。これに対して罫線が加わると563K、ちょっと増えました。ここに塗りが加わると、数式が入ってくると632K、1列1万件入っています。
続いて、数式が5列増えました。数式の数がたくさん増えてるわけなので、632Kが898Kになりました。
最後、条件付き書式をびっしりと入れました。
黄色いところに入れたのですが、条件付き書式を見ると、ルールが今は1個しか入っていませんが、この条件付き書式は複数の条件付き書式を設定することができるので、複雑な書式に変えたり、大量に増やしていくと、そのぶんサイズは重くなっていきます。
どういう情報が加わるとどういう増え方をするのか。一般的には数式や条件付き書式がどんどん増えるという感覚で捉えていただいて大丈夫だと思います。
続いてオブジェクト残骸の削除でサイズ激減する事例をお見せします。
下のほうがBefore、114MB。こちらが残骸削除によって33.2MBに激減する例です。
（以下デモ）
こちらが重いほう、画像などが含まれているものです。この1ファイルに9枚のシートが入っています。係数も9948行目まで使っていて、データが入っています。
塗りで白くしているのでわかりませんが、シート9枚数で114MBって明らかにおかしいですよね、という気づきが重要です。
こういうケースでは、俯瞰してシートの全体を見ることが重要です。ズームを10%にして、一番下のデータの有効範囲、ここで行数が出ています。下のほうは空っぽです。ここから俯瞰して見ていく必要があります。
今は10%でほぼ全体が見えていますが、下のほうにどんどん降りていくと、色を塗っています。
Ctrl1で塗りつぶしをしている状態が発見されました。AD列が有効データ範囲である場合は、AEですから右側をバッサリ削除します。
やり方はCtrl＋Shift＋右矢印です。するとそれだけ情報量が入っているということがわかります。右クリックをしてもすぐに出てこないほど時間がかかっています。
いったん応答なしになって、ここで削除ができるようになったのですが、大量なデータが入っていて時間がかかるので、今回はこのファイルは閉じてしまいます。
バッサリ削除した状態がAfterのほうで、サイズが軽くなっています。AE列から右側はバッサリ削除したので、軽くサクッと動いたことがわかったと思います。
行も同じです。最下行9949行目からCtrl＋Shift＋下矢印で、ここもバッサリ削除した状態で閉じます。すると、サイズが軽くなりました。もともと114MBあったものが3.2MBに激減しています。
純粋に残骸を捨てたことで35分の1に軽くなっています。
これに気づかなければ、システム開発、マクロの開発の場合でも、お客様から提供いただいた在庫表にコマンドボタン・機能ボタンを実装して運用させてしまう可能性があるので、本当に十分な注意が必要になります。
大事なところは、114MBって異常ですよね。普通に考えてシート9枚なのにデータが大きすぎるのではないかという気づきがとても重要です。
大量に削除するケースでは、セルの個数が大量で時間がかかるので、エクセルがこういうメッセージを出してきます。
見えないデータ、残骸の削除についてお話をしました。
色がなかった場合、同じような目に見えない状態の残骸がある可能性が高いので、あまりに重い場合はぜひお試しください。
続いてセルのスタイルの残骸のお話です。
ホームタブからセルのスタイルでこのような状態です。
書式に対していろんなパターンがどんどん増えていくと、このブックに対して情報が保持され続けていくので、このあたりも気をつけて見るようにしてください。
（以下デモ）
健康な状態だったらCtrl＋Aで新規のブックを開きますけど、セルのスタイル、これが健康なブックの状態です。新規でブックを作ったときはこれが標準の状態になります。ここから書式をつけたりすると増えていきます。
もうひとつお話したかったのは、数式から名前の管理を選ぶ、名前の定義です。データの範囲に名前をつけるものです。
こちらはREFで、この程度の数は悪影響を及ぼす重さにではありませんが、大量にあるケースがあります。
基本的には残骸というか、使われていないものはバッサリ削除してください。あるファイルをコピーして、そこから作業を始めていくケースでよく起こるので注意をしてください。消せば軽くなっていきます。
次が今回のメイン的なお話です。非常にインパクトのあるお話なので実際にやってみます。
残骸の事例です。オブジェクトと言っているのは図形などです。今回は139Kしかありませんが、削除するとこのぐらいのサイズになったりします。
（以下デモ）
重いほうを開いています。このシートのどこに残骸があるのか、サイズからは見当がつかないと思います。
なぜなら1ブック1シートで139K。なんとなく妥当なファイルサイズなので、このまま使い続けていくことが多いと思います。
ホームタブから虫眼鏡で条件を検索してジャンプして、オブジェクト。選択オプション、オブジェクトを選んでOKをすると、オブジェクトが選択された状態になります。目視では見えなかった、これだけのオブジェクトが入っているということが見えるようになります。
こちらは四角い図形に背景が塗りなし、枠線もない状態です。つまり見えないんです。これをチェックするためには枠線を塗ると見えるようになります。たくさんのデータ・図形が入った状態ですが、先ほどのように俯瞰で見ると全体が見えます。なにが増殖していったのかを突き止める必要があります。
もう1回フォームから虫眼鏡で、オブジェクトの選択と表示。するとシート内のオブジェクトが全部見えます。このコメントは目のマーク、アイコンをクリック、オンオフするとコメントはこれで、コメントという名前だということがわかります。
このあたりも選択をすると、今も大量にあるので、下のほうだったりします。これが2でこれが3でこれが4でとわかるようになります。
ということでこれらをバッサリ削除する場合は、さっきの方法で選択条件・オブジェクトを指定して、1回全部つかんで、デリートで良いです。するとコメントだけが残りました。
この状態で上書き保存をしたものがこちらです。97K、あまり小さくなっていないように思われますが、重くないと気づかないことも問題なので、基本的にはすべてのブック、すべてのシートに対して、健康な状態であるのかどうかのチェックが必要です。
今回は削っている状態なのでオブジェクトはもうありません。削除し終わった状態になりました。
最後にまとめです。
1つ目が、ファイルサイズで妥当性をチェックする。
大きくは、このシート数でこのファイルサイズというような見方を、感覚的なものになりますが、あたりをつける、健康状態、なんとなくシート数からどうだろうと考えます。
2つ目が、データ量に対して重すぎないか。
データの量、あまりに重いと思ったら、有効データ範囲外、先ほどは列と行をバッサリ削除しましたが、それで激減する可能性がありますので、ぜひやっていただきたいと思います。
3つ目が、残骸オブジェクトは抱えない。
名前の定義、セルのスタイル、リンク切れ、クエリのデータ接続の残骸もたまにチェックする必要があります。開発の場合は必ずです。
ポイントとしては、ファイルサイズを常に意識すること、ブックの健康状態も意識することです。
エクセルのマクロ開発の場合は、VBAの技術が高かったとしても、これらが及ばないと大きな事故・不具合につながることがあります。
プログラムでやっつけるというより、健康状態をよく調べながら、軽量で運用できるような状態を設計していくことがなによりも重要です。
重くなっていく状態のまま使うと、ブックを開くときに時間が無駄ですよね。それに気づかなければそういうものだと思って運用することが多いです。
また、残骸によって画像が含まれる画像制御をするときにはまず致命的なエラーになる可能性があります。
マクロで実行時エラーが出たり、ファイルのサイズが重いことで改良・改善のご相談をいただくケースが多いですが、場合によっては手遅れなことがあります。
シートの数が多く、数式が複雑で、どこでなにをしたらどこになんの影響が出るのかわからない状態になっているケースでは怖くて手が出せず、作り直しになる可能性があります。
エクセルの運用ではサイズに関する視点が本当に重要ですので、特に開発のかたはプログラムだけでは品質の高い開発はできないということを理解いただく必要があるかと思います。
解説は以上となります。少しでも参考になったかたはぜひチャンネル登録・高評価をお願いします。
毎週金曜日夜9時に投稿しています。ご視聴ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
