☰

Excel ドキュメントの詳細ステータスが遅い

tag-icon • tag-icon microsoft-excel performance logging debug
Excel ドキュメントの詳細ステータスが遅い

私は、特注の MATLAB シミュレータからの出力を後処理するために使用する、最適化が不十分な大規模な Excel ドキュメント セットを継承しましたが、非常に遅いです。開く、再計算、保存に時間がかかり、使用するとさらに遅くなります (開く/保存に最大 45 分)。最適化して動作を良くすることは許可されていますが、完全に削除してやり直すことは許可されていません。私はすでに、簡単に実行できる方法を試し、書式設定を改善し、冗長な数式を削除し、エラーを修正、削除、または検出しました。

何らかのデバッグまたは詳細なログ記録を有効にして、プロセスに時間がかかっている場所を確認する方法はありますか。そうすれば、最小限の利益を追い求めるのではなく、努力に集中できます。

答え1

ご質問にお答えすると、Excel にはパフォーマンス ログやデバッグ出力はありません。VBA コードでタイマー関数を実行して、さまざまな VBA サブ、関数、またはマクロの実行時間を計測できます (ただし、Excel の再計算を直接行うことはできません)。

Excel ブックの速度を最適化するための私のトップヒント:

  1. ワークブックのデータサイズを比較する(列、行、シート) とファイル サイズ。不釣り合いに大きいファイルには、大量のメモリを消費する隠れた図や描画が含まれている可能性があります。.xlsx の名前を .zip に変更し、フォルダーに解凍して、最も大きいファイルを探すとヒントが得られます。
  2. 異なるファイル形式で保存たとえば、古い .xls を新しい .xlsx に保存し、新しい .xlsx をバイナリ .xlsb に保存します。これは、大規模なデータ セットの読み込みと保存に時間がかかる場合に特に役立ちます。
  3. CPUとメモリの使用状況を確認する. CPU の動きがあまりないのにメモリ使用量が多い場合は、主にデータ量が多いだけで、処理負荷はそれほど高くないことを示しています。バイナリ形式 (.xlsb) と RAM (RAM を 2 倍にする) およびディスク (SSD) のアップグレードを組み合わせると効果的です。また、自動保存/バックアップ設定をチェックして、作業中にファイルが保存/バックアップされる回数を減らしてください。
  4. もしCPUとメモリの使用状況高い場合は、最適化するために数式を精査する必要がある可能性があります。集中的な数式計算のもう 1 つの兆候は、たとえば最初の入力セルの 1 つを編集した後、突然大幅に速度が低下することです。ただし、表示中は比較的高速です (最初の読み込み時間を除く)。
  5. VOLATILE関数(OFFSET、INDIRECT、NOW、RANDなど)は、どんな大きなスプレッドシートでも致命的です。一番下の空白で参照されていない孤立したセルを編集することで、これらを確認できます。この編集によっていくつかの遅延が発生する場合は、揮発性関数が原因である可能性があります。ああ、忘れないでください条件付き書式不安定でもあります。
  6. VOLATILEトリガー自動フィルターの変更、セルの幅と高さの調整、ゴールシーク、セルとシートの非表示/表示/挿入/削除/移動/名前の変更、CSV ファイルや DB などへの接続の更新、名前付きセルと範囲の変更など、(スプレッドシート) シート全体の再計算を引き起こすアクションが含まれます。これらのいずれかが VBA マクロでも実行される場合は、これで完了です。
  7. 再計算設定パフォーマンスに大きな影響を与える可能性があります。可能であれば自動再計算をオフにして、更新/編集後に手動で再計算してください。反復計算は避けてください。
  8. 従属関係/前例をトレースする数式リボンのオプションを使用するか、CTRL+[ と CTRL+] を使用します。後者は、CTRL+[ を複数回押すと、従属関係の従属関係のさらにその ...
  9. 問題のある数式を表示または検索します。数式リボンを切り替えることができます >数式を表示（CTRL+~）を使用すると、問題のある数式を簡単に見つけることができます。また、ワークブック全体で探す揮発性関数の場合は、たとえば OFFSET を検索します。
  10. 名前付き範囲を忘れないでください。名前マネージャーで、揮発性関数を含む名前付き範囲を確認します。
  11. 行く不揮発性INDIRECT を INDEX(..MATCH(..)) に置き換え、OFFSET を相対的な名前付き範囲 (「CellAbove」名前付き範囲など) に置き換えます。
  12. データメモリの最適化。減らす中古品(使用範囲内に空白の行と列がないようにしてください)。他のワークブックや他のシートへの相互参照は避けてください。データをテキストとして保存しないでください。番号付きのデータであることを確認してください。虚数は実際にはテキストとして保存されるため、使用しないでください。Office 365 を使用している場合は、数式の同じ部分を何度も再計算しないように LET を使用してください。たとえば、代わりに を使用=IF(LOG(A1)>B1,LOG(A1),B1)できます=LET(x, LOG(A1), IF(x > B1, x, B1))。また、異なるセルで同じ方程式の部分を何度も計算する場合は、別のセルで計算するか、名前付きを定義してください。
  13. マクロ/VBAコード/JavaScriptを最適化各サブまたは関数呼び出しの開始時に、再計算/画面更新/イベントをオフにします。残りのコードを実行し、それらを再びオンにします。
  14. 使用ブロックセルの読み取りと書き込みこれは特に VBA/マクロ/JavaScript コードに当てはまります。個々のセルへのアクセスは遅く、特に画面更新がオンになっている場合は遅くなります。回答を配列に蓄積し、それをセルにブロック書き込みするようにしてください。これに関連して、配列数式 (および Excel 365 でのスピル) を使用して、Excel 数式のブロック r/w と同等のことを実行できます。これが実際に高速化するかどうかは確認していませんが、直感的にはそうだと思います。
  15. データ接続を確認してください。一部の接続は、ワークブックを開くと自動更新されるように設定されており、特にオンライン/ネットワーク データを更新するときに、全体的な読み込み時間が大幅に長くなる可能性があります。

答え2

ワークブックでは VBA が使用されていると仮定し、この仮定に基づいて回答を投稿します。

コストの大部分は、画面の更新や、ディスク/ネットワーク共有へのファイルの書き込みで発生します。

画面をリフレッシュするには、重い処理を行っているモジュールを

Application.ScreenUpdating = True
    'Do Something
Application.ScreenUpdating = False

これらの設定により、何も起こっていないように見えますが、コード ビハインドは引き続き実行されます。debug.print's をいくつか追加し、即時ウィンドウを監視して、どこまで進んでいるかを確認できます。

Application.EnableEventsを無効にして、絶対に必要な場合にのみ使用することも検討できますApplication.Calculation = xlAutomatic。

ディスク コストに関して、まだ行っていない場合は、ファイルをローカル マシンに配置し、可能であればソース MatLab データも配置することをお勧めします。ネットワーク共有の読み取り/書き込みは、ローカル ディスクに比べて本質的に低速です。

関連情報