Microsoft Learn Challenge

Excelのパフォーマンス – 計算パフォーマンスの向上

Excel 2016では、1,000,000行と16,000列の「ビッググリッド」により、大容量のワークシートが可能になり、計算速度が向上しました。計算が遅いと生産性が低下し、エラーが増えます。Excelには自動計算と手動計算の2つのモードがあり、効率的な設計が計算速度に影響します。シンプルなルールで重複計算を削除し、効率的な関数を使用することで、計算を最大100倍速くできます。これにより、大規模なデータを扱う際の集中力を高めることが可能です。

Excelの計算性能を最適化する方法

適用対象

Excel | Excel 2013 | Excel 2016 | VBA

Office Excel 2016では、100万行と16,000列の「ビッググリッド」が導入され、多くの制限が増加されたため、以前のバージョンと比較して作成できるワークシートのサイズが大幅に向上しました。これにより、Excelの単一のワークシートは、以前のバージョンの1,000倍以上のセルを含むことが可能になっています。

以前のバージョンのExcelでは、多くのユーザーが計算速度の遅いワークシートを作成しており、通常、大きなワークシートは小さなワークシートよりも計算が遅くなります。2007年に「ビッググリッド」が導入されて以来、パフォーマンスが非常に重要です。遅い計算やデータの操作（ソートやフィルタリングなど）は、ユーザーが現在のタスクに集中するのを難しくし、その結果、エラーが増加します。

計算速度の重要性

計算速度の悪化は生産性に悪影響を及ぼし、ユーザーエラーを増加させます。応答時間が長くなるにつれて、ユーザーの生産性や集中力は低下します。

Excelには、計算の発生時期をコントロールできる2つの主要な計算モードがあります。

  • 自動計算: 変更を加えたときに数式が自動的に再計算されます。
  • 手動計算: 要求したとき（例えば、F9を押したとき）にのみ再計算が行われます。

計算時間が約0.1秒未満の場合、ユーザーはシステムが即座に応答していると感じることができますが、これは自動計算を利用してデータを入力することが可能です。

計算時間が0.1秒から1秒の間では、ユーザーは思考を続けることができますが、応答時間の遅延には気付くでしょう。その後、計算時間が1秒から10秒に増加すると、ユーザーはデータを入力する際に手動計算に切り替えなければならなくなります。この時点で、特に繰り返し作業では、エラーの発生や不満が増加し、思考を維持するのが難しくなります。

10秒以上の計算時間の場合、ユーザーは苛立ち、通常は待っている間に他のタスクに切り替えます。これにより、計算が一連のタスクの一部であり、ユーザーが流れを失う原因となることがあります。

Excelの計算方法を理解する

Excelでの計算性能を改善するためには、利用可能な計算方法とそれをコントロールする方法を理解する必要があります。

完全計算と再計算の依存関係

Excelのスマート再計算エンジンは、計算時間を最小限に抑えるために、各数式の前提条件と依存関係、および前回の計算からの変更を追跡します。次の再計算時、Excelは次のものだけを再計算します。

  • 変更されたり、再計算が必要とされるフラグが立てられたセル、数式、値、または名前。
  • 再計算が必要な他のセル、数式、名前、または値に依存するセル。
  • ボラタイル関数や表示される条件付き書式。

Excelは、以前に計算されたセルに依存するセルを続けて計算します。

通常のケースでは、計算の間に入力データの一部または数式の一部のみを変更するため、このスマート再計算は、すべての数式の完全な計算を行う場合にはかかる時間のほんの一部で済みます。

手動計算モードでは、F9を押すことでこのスマート再計算をトリガーできます。Ctrl+Alt+F9を押すことで、すべての数式の完全計算を強制することができます。また、Shift+Ctrl+Alt+F9を押すことで、依存関係を完全に再構築し、全体的な計算を行うことができます。

計算プロセス

Excelの数式は、参照されるセルの前または後に置くことができます（先行参照または後方参照）。これは、Excelがセルを固定の順序で計算したり、行や列ごとに計算したりしないためです。代わりに、Excelは計算するすべての数式（計算チェーン）のリストと、各数式に関する依存情報に基づいて計算の順序を動的に決定します。

Excelは次の4つの計算フェーズを持っています。

  1. 初期計算チェーンを構築し、どこから計算を開始するかを決定します。このフェーズは、ワークブックがメモリに読み込まれるときに発生します。
  2. 依存関係を追跡し、セルを未計算としてフラグ付けし、計算チェーンを更新します。このフェーズは、各セルの入力や変更のたびに実行されます。
  3. すべての数式を計算します。計算プロセスの一部として、Excelは次回以降の再計算を最適化するために計算チェーンの順序を変更し、再構築します。
  4. Excelウィンドウの表示部分を更新します。

この後、Excelは計算チェーン内の各数式を順番に計算しようとします。もし数式が計算されていない他の数式に依存している場合、その数式は再度計算されるようにチェーンに送られます。これは、ある数式が再計算ごとに複数回計算される可能性があることを意味します。

Excelの計算時間を改善するために、次のステップを実行できます。

計算時間を測定する

計算の速度を正確に測定するためには、計算時間を測定するコードを追加します。このコードを使用して計算の速度を測定し、どの数式が計算時間を消費しているかを評価することができます。

計算性能を向上させるためのヒント

  1. 重複した計算を削除する: 重複した計算野や、関数の非効率な使い方を見直して、計算の効率化を図る。
  2. 最も効率的な関数を使用する: Excelの内蔵関数を可能な限り活用し、ユーザー定義関数は必要に応じて利用する。
  3. スマート再計算とマルチスレッド計算の活用: 計算をより効果的に行うために、Excelの機能を活かす。
  4. テストと測定: 各変更をテストし、速度を測定して改善効果を確認する。

これにより、ワークブックの計算を最大限に効率化し、ユーザーが直面する遅延を大幅に軽減できます。

結論

Excelは、新たに導入された機能によって、より大きなワークシートを効率的に管理できるように設計されています。大規模なワークシートを作成する際には、計算が遅くなる原因を考慮しながら設計することが重要です。手順を踏むことで、ワークシートの計算速度を10倍以上向上させることが可能です。

より詳しく

Excelの計算性能の最適化についてさらに知りたい方は、Microsoftの公式ドキュメントを参照してください。

————-

Excel performance – Improving calculation performance

Source link

The information provided covers improvements and features in Excel 2016 (and earlier versions, including Excel 2013) that enhance users’ ability to manage large worksheets, specifically the "Big Grid" capacity which allows over 1 million rows and 16,000 columns. This capability has significantly increased the number of cells available, creating opportunities for larger data analysis but also making the management of calculation speed crucial.

Key Points:

  1. Increased Capacity: Excel now supports significantly larger worksheets than previous versions, allowing over a million rows and thousands of columns, enabling more extensive data analysis while requiring careful design to avoid calculation delays.

  2. Performance Optimization:

    • Excel introduces multithreaded processing, which allows calculations to utilize multiple processors, speeding up performance dramatically.
    • Users can optimize their worksheets by identifying inefficiencies in design, such as:
      • Removing duplicated and unnecessary calculations.
      • Ensuring efficient use of functions, as some can slow performance significantly.
      • Using helper columns to streamline calculations.

  3. Calculation Modes:

    • Automatic Calculation: Formulas are recalculated whenever changes are made.
    • Manual Calculation: Allows users to control when recalculations occur, which can be beneficial for large workbooks to avoid delays during data entry.

  4. Understanding Recalculation: Excel uses smart recalculation techniques to minimize processing time by only recalculating cells that have changed. Users can trigger recalculations selectively using shortcuts (F9 for manual updates, etc.).

  5. Volatile Functions: These functions, which recalculate every time a calculation occurs, can slow down performance if overused. Identifying and managing these functions is vital for optimizing speed.

  6. Improving Calculation Speed:

    • Users can measure calculation time using VBA tools, allowing them to identify bottlenecks effectively.
    • Strategies like combining continuous calculations into fewer formulas can drastically reduce the number of references and boost performance.
  7. Conclusion: Overall, while modern Excel versions allow for expanded data handling, careful worksheet design is essential to ensure efficient calculations. By implementing established strategies, users can significantly enhance the speed of their worksheets, reducing errors and improving productivity.

This summary encapsulates the benefits and methodologies associated with managing and optimizing worksheet performance in Excel, providing a foundation for users to enhance their productivity while leveraging the advanced capabilities of the software.

Power BI 埋め込みアナリティクスアクセラレータープログラムのアップデート | Microsoft Power BI ブログ 前の記事 サノフィ Power BI データビジュアライゼーションコンペティション：パリオリンピックに焦点を当てたインスピレーションあふれる取り組み | Microsoft Power ... 次の記事

関連記事