大規模なワークブックのパフォーマンスを低下させる 7 つの Excel の隠された設定

 

Excel ワークブックがフリーズしたり、永遠に再計算されたり、スクロールするたびに遅れが生じたりした場合、ほとんどの人は複雑な数式やデータが多すぎることが原因だと考えます。時々それは真実です。しかし、多くの場合、本当の原因は、バックグラウンドで機能する Excel の設定の一部にあります。大規模な Excel ワークブックは、リソースを不必要に消費する非表示の設定や機能が原因で動作が遅くなる可能性があります。大規模なデータセット、複雑な数式、または複数のシートを扱っている場合、これらの設定を微調整するとパフォーマンスが大幅に向上する可能性があります。

このチュートリアルでは、大規模なブックの速度を密かに遅くする可能性がある 7 つの Excel 設定と、それらを特定して修正するための実践的な手順を紹介します。計算モード、アドイン、条件付き書式設定などについて説明します。

1.自動計算モード – 常にすべてを再計算します

デフォルトでは、Excel は通常、自動計算モードで実行されます。つまり、値を編集するたびに、Excel は依存するすべての数式をすぐに再計算します。小さなブックではこれが便利です。大規模なワークブックでは、すべてのキーストロークが高価に感じられる可能性があります。

ファイルに何千もの数式、ルックアップ、動的配列、ピボットベースの集計、または揮発性関数が含まれている場合、わずかな編集でも自動再計算によって連鎖反応が引き起こされる可能性があります。入力セルを 1 つ変更すると、Excel がワークブックの半分の再計算を開始する場合があります。

動作が遅くなる理由:

大規模なモデルには、多くの場合、深い数式依存関係があります。自動計算により、編集中であっても Excel は常に更新を強制します。大きなファイルでは、わずかな変更でも完全な再計算がトリガーされ、CPU 使用率が急増する可能性があります。

修正:

• ファイルに移動します タブ>> オプションを選択します>> 数式を選択します
• 計算オプションの下 、自動から切り替えます。 マニュアルへ>> [OK] をクリックします。

• またはリボンから:
  • 数式に移動します タブ>> 計算オプションを選択します>>手動を選択します

• 必要に応じて手動で再計算するには:
  • F9 開いているすべてのワークブックを再計算します
  • Shift + F9 アクティブなワークシートを再計算します
  • Ctrl + Alt + F9 完全な再計算を強制します

この変更だけでも、数式が多いファイルの遅延を 50% ～ 80% 削減できます。

注意: 他の人がファイルを使用する場合は、重大なエラーが発生する可能性があるため、手動モードを永続的にオンのままにしないでください。目立つメモを追加するか、ファイルを他の人に渡す前に忘れずに自動に切り替えてください。そうしないと、古い結果が表示される可能性があります。

プロのヒント: より選択的な制御を行うには、データ テーブルを除く自動を使用します。 継続的な更新を必要としないデータ テーブルがある場合。

2.不要なアドイン – 決して使用しないバックグラウンドで実行されている

Excel アドインは、パフォーマンス低下の最も見落とされている原因の 1 つです。アドインはアプリケーションとともにロードされ、ブックを開く、セルの変更、再計算、保存などのイベントにフックできます。忘れていたアドインであっても、データを操作するたびにこれらのイベントをインターセプトし、バックグラウンドで静かに動作する可能性があります。

一般的なアドインには、従来の企業ツール、Power Pivot COM アドイン、分析ツールパック (未使用の場合)、ソルバー、サードパーティのグラフ作成ライブラリ、PDF コンバータなどのサードパーティ ツールが含まれます。

動作が遅くなる理由:

アドインを積極的に使用していない場合でも、アドインはメモリを消費し、ブックのパフォーマンスを妨げる可能性があります。 Excel の起動時間が長くなったり、バックグラウンドで RAM が使用されたり、ブック イベントにフックされたり、数式、データ接続、またはリボンとやり取りしたりするため、アイドル状態であっても遅延が発生する可能性があります。

軽量のワークブックでは、気付かないかもしれません。重いワークブックでは、余分なオーバーヘッドが明らかになります。

修正:

• ファイルを開きます>>オプションを選択します>> アドインを選択します
• 管理を展開します。 ドロップダウン>> COM アドイン を選択します またはExcel アドイン>> [移動] をクリックします。

• すべてのアドインを検査し、積極的に使用していないアドインのチェックを外します
  • 分析ツールパックを無効にする 統計ツールを使用していない場合

• 後でいつでも再度有効にすることができます。チェックを外してもアンインストールされません
• Excel を再起動してパフォーマンスをテストする
• アドインが重要な場合は、アップデートを確認してください
  • ファイルを開きます>> アカウントを選択します>> [更新オプション] を選択します。 古いバージョンにはバグが多いため

肥大化したアドインを 1 つだけ無効にすると、ロード時間を数秒短縮できます。

3.過度に使用された条件付き書式 – 列全体に適用されるルール

条件付き書式設定は Excel の優れた機能の 1 つですが、ブックの速度を低下させる最も一般的な原因の 1 つでもあります。いくつかのルールを作成し、シートをコピーし、それらのルールを数回複製すると、最終的に範囲全体にわたって書式設定ロジックを常に再評価するワークブックが完成するのは簡単です。問題は書式設定ルールそのものではありません。それが適用される場所です。

動作が遅くなる理由:

Excel では、データが変更されたり、シートが再描画されたり、数式が再計算されたりするたびに、書式設定ルールを再確認する必要がある場合があります。列全体にルールを適用すると、Excel は、ほとんどのセルが空であっても、シートが再計算されるたびに 100 万を超えるセルに対してそのルールを評価します。条件付き書式設定では、数式自体が特に複雑でない場合でも、スクロール、フィルタリング、編集中に遅延が発生することがよくあります。

修正:

• 書式設定を使用して範囲を選択します
• ホームに移動します タブ>> 条件付き書式を選択します>> [ルールの管理] を選択します。

• 冗長なルールを確認して削除するか、類似したルールを結合する
• ルールをシート全体または列全体ではなく特定の範囲に制限する
  • [適用対象] にチェックを入れます。 各ルールの列
  • =$A:$M のような列全体の範囲を =$A$2:$M$5000 のような制限された範囲に置き換えます

• データが動的に増加する場合は、Excel テーブルを使用します。テーブルの列に適用される条件付き書式設定は、自動的にテーブルの実際のサイズにスコープされたままになります
• 数式ベースのルールを避ける 可能な場合。 より大きいなどの組み込みオプションを使用します または重複 処理を高速化するため
  • たとえば、=COUNTIF($B:$B,A1)>1 のようなルールはセルごとに評価されるため、値ベースのルールよりも大幅に重くなる可能性があります

• すべてのルールをクリアするには:
  • ホームに移動します タブ>> 条件付き書式を選択します>> [ルールをクリア] を選択します。>> [シート全体からルールをクリア] を選択します。

よくある罠: すでに条件付き書式が設定されているセルをコピーして貼り付けることは、ルールが誤って増加する最も一般的な方法の 1 つです。時間の経過とともに、ワークブックには、わずかに異なる範囲に適用されるほぼ重複したルールが数十個蓄積されることがあります。それらを定期的に監査して統合します。成熟したワークブックで 200 以上の冗長なルールが見つかることは珍しいことではありません。

また、ルール ロジックを簡素化するか、複雑なロジックをヘルパー列に移動し、その結果に基づいて書式設定することも検討してください。監査してルールの数を減らすと、ワークブックの応答性が大幅に向上します。

4.数式内の揮発性関数 – 編集のたびに完全な再計算をトリガーします

特定の Excel 関数は揮発性として分類されます。つまり、入力が変更されたかどうかに関係なく、ブック内の何かが変更されるたびに再計算されます。 TODAY()、NOW()、RAND()、OFFSET()、INDIRECT() などの関数はすべてこのカテゴリに分類されます。

最も一般的な犯罪者:

• NOW() および TODAY():再計算ごとに更新
• RAND() および RANDBETWEEN():新しい値を常に生成します
• OFFSET():ダイナミック レンジの式で一般的
• INDIRECT():柔軟性はありますが、規模が非常に遅い
• CELL() と INFO():ほとんど必要とされず、多くの場合コストがかかります

動作が遅くなる理由:

これらの関数は、無関係に見える小さな編集であっても、繰り返し再計算を強制する可能性があります。そのため、すでに大規模な数式チェーンからの圧力を受けているワークブックでは特に危険です。 10,000 行の範囲を参照する 1 つの INDIRECT() 式が 50 列にわたってコピーされると、それだけでワークブックがひどく遅く感じる可能性があります。

修正:

• 数式に移動します タブ>> 数式の表示 (Ctrl + `) を選択します。

• Ctrl + F を押します。 数式内の揮発性関数を検索する
• 可能な場合は置き換えてください:
  • TODAY() の代わりに静的な日付を使用する
  • OFFSET() ベースのダイナミック レンジを Table1[Column] などの構造化テーブル参照に置き換えます
  • 範囲をテキスト駆動にする必要がない場合は、INDIRECT() を直接参照または INDEX() に置き換えます

• それらが避けられない場合は、それらを単一のセルに分離し、そのセルを他の場所で参照します
• RAND() の場合は、値を一度生成し、静的な数値として貼り付けることを検討してください。
  • コピー>> 特殊な貼り付け>> 価値観

揮発性の数式を最小限に抑えると、定期的な再計算が防止され、ワークブックの操作性が向上します。

5.ワークシート全体で使用されていない書式設定

Excel はワークシート全体の書式を追跡します。フォント、色、枠線などの書式設定を行、列、シート全体に適用すると、それらのセルが使用されていない場合でも、ファイル サイズが肥大化し、レンダリングが遅くなる可能性があります。フォーマットが実際のデータセットをはるかに超えて拡張される場合、ファイル サイズと再計算のオーバーヘッドの両方が増加する可能性があります。

動作が遅くなる理由:

Excel では数百万のセルの書式設定を追跡することになる可能性があり、これによりメモリ使用量が増加し、保存と読み込みの時間が遅くなります。

修正:

• フォーマットされた領域を特定します:
  • Ctrl + End を押します。 最後に使用したセルにジャンプします。 Excel には、空であっても書式設定されたセルが含まれます
• データ以外の未使用の行と列を選択します
  • 最後のデータ行の下の行を右クリックし、削除を選択します。
• 書式設定のクリア:
  • 未使用の範囲を選択します
  • ホームに移動します タブ>> 編集を選択します>>クリアを選択します>>フォーマットをクリアを選択します

• シートの使用範囲をリセットするには、余分な行と列を削除した後、ワークブックを保存して閉じ、再度開きます
• 複数のシートの場合は、慣れている場合は VBA を使用してください。
  • Alt + F11 を押します。 、モジュールを挿入し、未使用領域から書式設定を削除するコードを実行します

ActiveSheet.usedRange.ClearFormats

未使用のフォーマットを削除すると、ファイル サイズが大幅に縮小され、開いたり保存したりする時間が短縮されます。

6.外部リンクとデータ接続

他のワークブックやデータベースなどの外部ソースへのリンクにより、データが自動的に更新され、バックグラウンドで情報がクエリされます。

動作が遅くなる理由:

他のファイルにリンクされたブックにより、Excel は外部参照を確認し、接続を更新し、依存関係を再計算します。 ='[Sales2024.xlsx]Sheet1'!A2 のような外部数式は、リンクされたファイルが大きい場合、またはネットワーク ドライブに保存されている場合、パフォーマンスを大幅に低下させる可能性があります。壊れたリンクや遅いリンクもタイムアウトを引き起こす可能性があり、自動更新により大きなファイルの作業が中断される可能性があります。

修正:

• データに移動します。 タブ>> クエリと接続を選択します>> [リンクの編集] を選択します。
• [すべて解除] をクリックして不要なリンクを解除します。

• データ接続の場合:
  • データに移動します。 タブ>> クエリと接続を選択します
  • クエリを右クリックし、[バックグラウンド更新を有効にする] チェックボックスをオフにします。

• 外部データを静的な値に変換します:
  • 一度更新してからコピーします>> 特殊な貼り付け>> 価値観
• 可能な限りネットワーク ドライブへのリンクを避けてください。代わりにソース データをローカルにコピーします

不要なリンクを解除すると、特に共有環境でのランダムなフリーズを排除できます。

7.設定を保存して開く – 大きなファイルを実際よりも重く感じさせる

編集中にすべての速度低下が発生するわけではありません。場合によっては、ブックを開いたり、保存したり、閉じたりするときに、本当にイライラすることがあります。これは通常、数式ロジックではなくファイル処理設定を指します。

共通の犯人: 多くの場合、いくつかのことが原因となります。

• 自動回復による保存が頻繁すぎる
• 外部リンクは開くと更新されます
• 古い名前付き範囲と非表示オブジェクト
• レガシー .xls ファイルの互換モード
• 非効率的なファイル形式の選択

動作が遅くなる理由:

大きなワークブックはディスクへの書き込みに時間がかかります。 Excel が頻繁に回復スナップショットを作成したり、外部リンクをチェックしたり、古い互換性動作を維持したりしている場合、保存時間と開く時間が非常に長くなる可能性があります。

修正:

• 自動回復の頻度を確認します:
  • ファイルに移動します タブ>> オプションを選択します>> [保存] を選択します。
  • 自動回復の設定が強すぎると、非常に大きなファイルで作業しているときに Excel が必要以上に頻繁に一時停止する可能性があります
• 外部リンクを確認します。ワークブックにリンクされたファイルが含まれている場合、それらのリンクを自動的に更新する必要があるかどうかを決定します
• ファイル形式を確認してください。まだ .xls で作業している場合 、ほぼ確実に Excel の動作を必要以上に難しくしている
• 大規模な内部ワークブックの場合は、.xlsb として保存することを検討してください。 .xlsx の代わりに 。多くの場合、バイナリ形式は次のとおりです:
  • 開く速度が速くなります
  • 保存が速くなります
  • 使用するディスク容量が少なくなります
  • 大規模なワークブックをより効率的に処理する

すべてのワークフローにとって常に正しい選択であるとは限りませんが、パフォーマンスが重要な内部ファイルの場合は、大きな違いを生む可能性があります。バイナリ ワークブックは、多くの場合、使用するディスク領域が少なくなり、より高速に開いたり保存したりできます。主なトレードオフは、一部の Microsoft 以外のツールを使用すると移植性が若干低下することです。

最高のパフォーマンスを実現するための最後のヒント

• [数式]> [計算]> [今すぐ計算] で再計算時間を監視します。 変更前と変更後
• [ファイル]> [情報]> [問題のチェック]> [ドキュメントの検査] を使用します 個人情報や未使用のスタイルなどの非表示データを削除する
• 非常に大きなファイルの場合は、ワークブックを複数のファイルに分割するか、分析に Power BI を使用することを検討してください
• データの損失を避けるために、最初にワークブックのコピーで変更をテストしてください

結論

これらは、大規模なブックの速度を密かに遅くする可能性がある 7 つの Excel 設定です。 Excel の速度が低下すると、多くの人はすぐに数式を書き直し始めます。それが必ずしも最初の最善策であるとは限りません。大規模なワークブックのパフォーマンスは、多くの場合、計算動作、アドイン、条件付き書式設定スコープ、外部リンク、保存オプションなど、バックグラウンドで静かに存在する設定によって決まります。小さなファイルでは、これらのことが重要になることはほとんどありません。大きなものでは、それらは非常に重要です。そのため、Excel でのパフォーマンス チューニングは、数式をよりスマートにするだけではありません。それはまた、静かなオーバーヘッドを取り除くことでもあります。これらの設定に対処することで、動作が遅いスプレッドシートをより効率的なツールに変えることができます。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!