Microsoft Learn Challenge

Excelのパフォーマンス – パフォーマンス障害を最適化するためのヒント

Excelのパフォーマンス最適化には、参照やリンクの改善、循環参照の最小化、ワークブック間のリンク回避が重要です。前方および後方参照を避け、循環参照は代数で解消し、複数のワークシートにまたがると計算が遅くなるため、同一シートに集約しましょう。また、動的範囲や構造化テーブル参照を利用することで、計算を効率化できます。VBAコードの実行時には、画面の更新や計算を手動にし、一括でデータを読み書きすることも効果的です。

Excelの性能最適化：よくある障害の克服

Excelは、データの管理や分析に非常に強力なツールですが、パフォーマンスの問題に直面することもあります。この記事では、Excel 2013、Office 2016、VBAを使用した場合に、よくあるパフォーマンスの障害を最適化するためのヒントを紹介します。

参照とリンクの最適化

前方参照と後方参照を避ける

計算の明確さを高め、エラーを回避するために、数式を設計する際には、他の数式やセルへの前方参照を避けるようにします。前方参照は、計算性能に影響を与えないことが多いですが、初回のワークブック計算において、多数の数式が計算を保留する必要がある場合は、計算順序の確立に時間がかかることがあります。

循環参照の使用を最小限に

循環参照を持つ数式は、反復計算が必要なため計算が遅くなることがあります。このような場合、代数を使って循環参照を「展開」し、反復計算を不要にすることができる場合があります。例えば、キャッシュフローと利息計算においては、まず利息を考慮しないキャッシュフローを計算し、次に利息を計算してから利息を含むキャッシュフローを求めることが推奨されます。

ワークブック間のリンクを避ける

できるだけ相互リンクの使用は避けるようにしましょう。これらは遅く、壊れやすく、見つけたり修正したりするのが難しいことがあります。多くの場合、少ないが大きなワークブックを使用する方が、小さなワークブックを多数使用するよりも良いです。データの最適化のために、リンクされているワークブックを開いたままにしておくことも一つの方法です。

シート間のリンクを最小限に

多くのシートを使用することは、ワークブックの利便性を高めることができますが、シート間の参照を計算するのは通常遅くなります。同じシート内の参照よりも計算が遅くなることがほとんどです。

使用範囲の最適化

メモリを節約し、ファイルサイズを削減するために、Excelはワークシートで使用された範囲の情報のみを保存しようとします。これは「使用範囲」と呼ばれます。時折、編集や書式設定の操作によって、使用範囲が実際の使用として考えている範囲を超えて大きく広がることがあります。これがパフォーマンス障害やファイルサイズの障害を引き起こす原因となります。

構造化テーブル参照を利用する（推奨）

Excel 2007以降では、構造化テーブル参照を利用することができ、参照されるテーブルのサイズが増加または減少する際に自動的に拡張または収縮します。これは計算の効果を向上させるための優れた方法です。

VLOOKUPやHLOOKUPの最適化

Office 365のバージョン1809以降、ExcelのVLOOKUP、HLOOKUP、MATCH関数を使用して未ソートデータからの正確な一致を高速化することが可能になりました。もし過去のバージョンを使用している場合、VLOOKUPの性能向上が重要です。これにより多くの計算を効率化し、パフォーマンスの改善が期待できます。

VBAコードの最適化

VBAマクロを作成する際のパフォーマンス改善には、実行中に必要ない機能をオフにすること、セルの読み書きを一括で行うことが推奨されます。また、オブジェクトを選択やアクティブ化するのではなく、直接参照して使用することもパフォーマンスを改善します。

結論

このように、Excelの機能を最適化し、リンクや参照、数式、VBAコードを工夫することで、パフォーマンスを向上させることができます。これは一時的な障害を克服し、Excelの利便性を最大限に引き出すために非常に重要です。これらのテクニックを活用して、よりスムーズにデータ管理や分析を行いましょう。

————-

Excel performance – Tips for optimizing performance obstructions

Source link

Here’s a summary of performance optimization tips for Excel, especially relevant for Excel 2013, Office 2016, and VBA:

Optimizing References and Links

  1. Avoid Forward and Backward Referencing: Design formulas to avoid referring to other cells or formulas in a way that complicates calculation sequences.

  2. Minimize Circular References: Use circular references judiciously, as they are time-consuming. Instead, try to simplify calculations to avoid the need for iterations.

  3. Avoid Workbook Links: Inter-workbook links can slow performance and can be difficult to manage. If necessary, keep linked workbooks open when working with them.

  4. Minimize Worksheet Links: Calculating across multiple worksheets is generally slower than calculations within a single worksheet.

  5. Control the Used Range: Limit the used range on worksheets to prevent excessive memory usage. Clear out unused rows and columns to reduce file size.

  6. Use Structured Table References: Leverage Excel tables for dynamic sizing and easier formula management.

Improving Lookup Calculation Time

  1. Optimize Lookups: Use exact match lookups judiciously and prefer approximate matches on sorted data where possible. Consider using INDEX and MATCH as alternatives to VLOOKUP or HLOOKUP.

  2. Limit Lookup Ranges: Restrict the range of cells to optimize lookup speeds, especially for larger datasets.

  3. Utilize Helper Columns: Store intermediate results (like the result of a MATCH function) in separate cells to streamline further lookups.

Optimizing Array Formulas and Functions

  1. Minimize Array Formula References: Use specific ranges instead of entire columns to avoid unnecessary calculations.

  2. Consider Using SUMIFS and Related Functions: Use built-in functions like SUMIFS, COUNTIFS, and AVERAGEIFS instead of array formulas for better performance.

  3. Optimize with SUMPRODUCT: For multiple conditions, use SUMPRODUCT, as it’s typically faster than equivalent array formulas.

VBA Performance Optimizations

  1. Turn Off Unnecessary Features: Temporarily disable features like screen updating, automatic calculation, and event handling while running VBA code for performance gains.

  2. Batch Data Operations: Read and write large ranges of data in one go rather than cell by cell, which saves on performance due to reduced interaction with the Excel interface.

  3. Avoid Selecting Objects: Reference objects directly without selecting them to streamline your code execution.

File Format Considerations

  • Choose Appropriate File Formats: Use .XLSB for better performance with larger files compared to .XLSX. While .XLS maintains compatibility, it hinders performance and features.

Best Practices for Opening, Closing, and Saving Workbooks

  • Clear temporary files, manage workbook structures wisely, avoid excessive links, and ensure virus scanner settings do not disrupt performance.

Conclusion

Implementing these various strategies will help optimize performance in Excel, reducing calculation time, enhancing efficiency, and ensuring a smoother user experience.

サノフィ Power BI データビジュアライゼーションコンペティション：パリオリンピックに焦点を当てたインスピレーションあふれる取り組み | Microsoft Power ... 前の記事 Excelのパフォーマンス - パフォーマンスと制限の改善 次の記事

関連記事