Excelが落ちる原因が、行数の多さそのものであることはめったにありません。原因は、その行の中身です。
値だけが入った90万行のシートは、2015年のノートPCでも問題なく開きます。ところが、列全体に条件付き書式を設定し、VLOOKUPを数千個使い、ピボットキャッシュを抱えた4万行のシートは、同じPCを完全にフリーズさせます。目につくのは行数ですが、原因であることはほとんどありません。
そこで確認したいのは、5つの原因のうちどれに当たるかです。対処法はそれぞれ違い、そのうち4つは1分もかかりません。
5つの原因(可能性の高い順)
1. メモリ不足(32ビット版かもしれません)
Excelはブック全体をRAMに読み込みます。そこに計算チェーン、元に戻す履歴、ピボットテーブルごとのピボットキャッシュが加わり、ディスク上のファイルサイズの数倍になることもよくあります。
まず自分のバージョンを確認します。「ファイル」→「アカウント」→「Excelのバージョン情報」です。 先頭の行に32ビットとあれば、マシンにどれだけメモリがあっても、Excelが使えるのは約2GBまでです。これが答えです。Microsoftは長年32ビット版を標準で配布していたため、そのままの環境は少なくありません。該当する人にとって、64ビット版への入れ替えはこのページで最も効果の大きい対処です。
メモリ問題のサイン:「使用できるリソースが足りないため、このタスクを完了できません」というメッセージ、または保存時や2つ目のブックを開いたときに限って落ちる症状です。
2. 揮発性関数が常に全体を再計算している
一部の関数は、入力が変わったときだけでなく、ブックのどこかを変更するたびに毎回再計算されます。揮発性なのはNOW()、TODAY()、RAND()、OFFSET()、INDIRECT()、INFO()、CELL()で、どれもそれに依存するセルをすべて巻き込みます。
動作が遅いブックで犯人になりやすいのはINDIRECT()、使い物にならなくなったブックで多いのはOFFSET()です。大きなシートにどちらかが数百個あるだけで、キーを1回押すたびに全体の再計算が走ります。
確認は10秒です。「数式」→「計算方法の設定」→「手動」 にします。ブックがすぐに軽くなれば、原因はサイズではなく再計算です。
対処は、優先順に次のとおりです。OFFSET/INDIRECTはINDEXに置き換えます。列全体を対象にしたVLOOKUPは、範囲を限定したINDEX/MATCHかXLOOKUPに置き換えます。A1:A50000で足りるならA:Aは参照しないでください。列全体の参照は、数式1つにつき100万セルのスキャンです。
3. 列全体に設定された書式
A列を選択して条件付き書式や塗りつぶしを設定すると、Excelは100万セル分の書式情報を保存することがあります。それを十数列でやれば、ファイルは膨れ上がります。データが5,000行しかないのに40MBあるブックはよくあり、開くときは100万行あるかのように重くなります。
2つのサイン:
- データは5,000行目で終わっているのに、Ctrl+Endで途方もない位置(900,000行目、BZ列など)に飛びます。それが使用済み範囲で、Excelはそれを基準にメモリを確保します。
- 中身に比べてファイルが極端に大きい。
対処:データの下にある行をすべて最下部まで選択して、(内容ではなく)行ごと削除します。データの右側の列も同様にします。保存して、閉じて、開き直してください。Ctrl+Endが本当の最終セルに着くはずです。そのあと、条件付き書式は列全体ではなく実際の範囲に設定し直します。
4. ピボットテーブルごとのピボットキャッシュ
ピボットテーブルは、他のピボットテーブルとキャッシュを共有すると明示しない限り、元データのコピーを自分で保持します。20万行のデータに5つのピボットテーブルを作ると、ファイルの中に100万行分の隠れた重複ができます。
対処:ピボットテーブルを追加するときは、元データから作り直すのではなく、既存のピボットテーブルをコピーします。コピーならキャッシュを共有できます。さらに、可能なら「ピボットテーブル オプション」で「元のデータをファイルと共に保存する」をオフにします。
5. やはり行数が原因
数百万行のCSVファイルなら、上のどれも当てはまりません。Excelは1シートに1,048,576行までしか入らず、これは構造上の上限です。正確には落ちません。入る分だけ読み込んで止まります。そのほうがたちが悪いのです。 Excelで開けない大きなCSVファイルを開く方法がこのケース専用のガイドで、 1GBのCSVファイルを開く方法では、代わりに扱えるツールを比べています。
どれに当たるかを切り分ける
2分で済みます。この順に確認してください。
- 「ファイル」→「アカウント」→「Excelのバージョン情報」。 32ビットですか?ここで終わりです。それが対処になります。
- Ctrl+End。 データよりずっと先に飛びますか?原因3です。
- 「数式」→「計算方法の設定」→「手動」。 軽くなりましたか?原因2です。
- ファイルサイズと中身を比べます。 8,000行で30MBなら、原因3か4です。
- どれも違い、ファイルが数百万行ですか? 原因5です。この場合、Excelは壊れているのではなく、道具が合っていません。
アドインは別に切り分けます。excel /safeでExcelを起動してください。ブックが普通に動くなら原因はアドインで、上の話は関係ありません。
代わりにできること
文書ではなくデータファイルの場合。 行き詰まる人に多いのは、表計算ソフトをデータベースの代わりにするパターンです。何十万行もあり、全行にわたって計算する数式の列があり、その上にピボットがある。この処理にはクエリエンジンが向いています。
duckdb -c "SELECT region, SUM(amount) FROM 'export.csv' GROUP BY region"
Excelなら2分とフリーズが必要な処理を、DuckDBは約1秒で返します。構文も、入れ子のSUMIFSより簡単です。詳しい手順はデータベースなしでCSVにSQLクエリを実行する方法にあります。
Excelから離れられない場合。 Power Query(「データ」→「データの取得」)はファイル全体を処理し、集計結果だけをシートに出力します。数百万行がワークシートを占めることはありません。データモデルはシートに収まらない行数を扱え、その上で直接ピボットテーブルを動かせます。
中身を読みたいだけの場合。 データファイルを、文書ではなくデータとして扱うツールで開けば、5つの原因をまとめて回避できます。
ファイルをインポートせずに読み込みます。再計算も書式情報もピボットキャッシュもありません。書かれたままの値に対して、SQLや普通の言葉で質問できます。
できること大半を防ぐ2つの習慣
値と見た目を分けます。 書式も数式もない生データのシートを1枚、それを参照する分析のシートを1枚。悲惨なブックの多くは、1枚のシートが3つの役目を全部担っています。
列ではなく範囲を参照します。 どこでもA:AではなくA1:A50000にします。ブックが大きくなっても使い続けられるかどうかに、最も大きく効く習慣です。
