【Excel】揮発性関数(NOW・INDIRECT等)がExcelを遅くする!再計算頻度を抑える設計のコツ

Excelのブックが重くなり、動作が遅延する原因の一つに、揮発性関数が挙げられます。NOW関数やINDIRECT関数などは、ブックを開いたときだけでなく、あらゆる操作で再計算されるため、計算負荷が高まりやすい関数です。特に、これらの関数を多用している場合に、Excelのパフォーマンス低下を招きます。本記事では、揮発性関数がExcelを遅くする原因と、再計算頻度を抑えるための設計のコツを解説します。

この記事を読むことで、Excelの動作が遅くなる原因を特定し、揮発性関数の再計算を効率化するための具体的な設計方法を習得できます。これにより、ブックのパフォーマンスを改善し、より快適にExcelを利用できるようになります。

揮発性関数がExcelのパフォーマンスを低下させる仕組み

Excelの揮発性関数とは、ブック内のどこかのセルが変更されるたびに、その関数が使用されているセルだけでなく、依存関係のない他のセルも含めて再計算される性質を持つ関数のことです。代表的な揮発性関数には、NOW()、TODAY()、RAND()、RANDBETWEEN()、INDIRECT()、OFFSET()、CELL()、INFO()などがあります。これらの関数は、ブックの更新や操作のたびに自動的に再計算されるため、計算量が増大しやすい特性を持っています。

たとえば、NOW()関数は現在の日時を返しますが、これはExcelが起動している間、あるいは何らかの変更が行われるたびに更新される必要があります。INDIRECT()関数は、指定したセル参照を文字列として解釈して参照するため、参照元のセルが変更されると、その参照先のセルも再計算の対象となります。このように、揮発性関数はブックの変更に敏感に反応し、意図しない再計算を誘発するため、ブック全体の計算負荷を高める要因となります。

揮発性関数の再計算頻度を抑える設計のコツ

揮発性関数によるパフォーマンス低下を防ぐには、その使用方法を工夫することが重要です。再計算の頻度を抑え、必要な場合にのみ計算されるように設計することで、Excelの動作を軽快に保てます。

1. 揮発性関数の使用を最小限にする
   まず、本当に揮発性関数が必要かどうかを検討します。代替手段として、数式で直接参照したり、補助列を使用したりできないか検討しましょう。例えば、INDIRECT関数で参照する範囲が固定できる場合は、直接セル参照に置き換えることで、不要な再計算を防げます。
2. 参照範囲を限定する
   INDIRECT関数やOFFSET関数を使用する場合、参照するセル範囲をできるだけ限定します。広範囲を参照すると、その範囲内のわずかな変更でも再計算が発生しやすくなります。必要最小限のセルのみを参照するように数式を設計しましょう。
3. INDIRECT関数でのシート名参照に注意する
   INDIRECT関数でシート名を指定して参照する場合、シート名が変更されると数式がエラーになるだけでなく、再計算のトリガーとなります。シート名を固定して参照するか、VBAなどで動的にシート名を管理する方が安全な場合があります。
4. 数式の計算方法を「手動」に設定する
   Excelのオプションで、数式の計算方法を「自動」から「手動」に変更できます。これにより、ブックの変更時に自動で再計算されなくなります。手動計算に設定した場合、再計算したいタイミングで「数式」タブの「今すぐ計算」(F9キー)または「シートの計算」(Shift+F9キー)を実行する必要があります。この設定は、ブック全体に適用されるため、揮発性関数が多いブックで一時的に作業したい場合に有効です。ただし、常に手動計算にしていると、最新のデータが反映されないため注意が必要です。
5. VBAを利用して再計算を制御する
   VBAを使用すると、特定のイベント発生時にのみ揮発性関数を再計算させたり、計算を一時停止したりすることが可能です。たとえば、Worksheet_Changeイベントプロシージャ内で、変更されたセルが特定の範囲にある場合のみ揮発性関数を再計算させる、といった制御ができます。また、Application.CalculationプロパティをApplication.Calculation = xlCalculationManualに設定し、必要なタイミングでxlCalculationAutomaticに戻すことで、集中的な計算負荷を避けることができます。

揮発性関数によるよくある失敗パターンと対処法

揮発性関数を不適切に使用した場合に発生しやすい問題と、その対処法を解説します。

ブックを開くのに時間がかかる、またはフリーズする

原因: ブックの起動時に、大量の揮発性関数が再計算を試みているためです。特に、INDIRECT関数が多数のシートやセル範囲を参照している場合や、OFFSET関数で動的に作成される参照範囲が広すぎる場合に発生しやすくなります。

対処法:

1. 手動計算への一時的な切り替え
   ブックを開いた後、「数式」タブの「計算オプション」で「手動」を選択します。これにより、ブックの起動が速くなります。その後、必要なデータが更新されたタイミングで「今すぐ計算」を実行します。
2. 揮発性関数の使用箇所の特定と修正
   ブックのパフォーマンスが著しく低下している場合、どのセルで揮発性関数が使用されているかを特定する必要があります。Excelの「数式」タブにある「数式の検証」機能や、「名前の管理」機能を使って、数式内に揮発性関数が含まれていないか確認します。特定できた揮発性関数は、前述の設計のコツに従って、参照範囲を狭めたり、代替数式に変更したりします。
3. VBAによる計算制御
   ブックを開く際の自動計算を抑制するために、VBAのWorkbook_OpenイベントプロシージャでApplication.CalculationをxlCalculationManualに設定し、ブックを保存します。ユーザーがブックを開いた後、手動で計算を実行するよう促すメッセージを表示するなどの工夫も有効です。

特定のセルを更新すると、関係のないセルまで再計算される

原因: 揮発性関数が、直接的な依存関係がないセルにも影響を与えているためです。例えば、ブックのどこかのセルが変更されると、NOW()関数が最新の日時に更新され、そのNOW()関数に依存する別の数式も再計算される、といった連鎖が発生します。

対処法:

1. 揮発性関数の数式を見直す
   影響を受けているセルを含む数式を確認し、揮発性関数が不要な箇所で使用されていないか確認します。もし、その揮発性関数が特定の目的(例:常に最新の日時を表示したい)のために使用されているのであれば、その目的を達成するためのより効率的な方法がないか検討します。
2. 補助列や定数としての利用
   最新の日時などを取得したい場合、NOW()関数を直接多数のセルで使用するのではなく、一つのセルにNOW()関数を入力し、そのセルの値を他の数式で参照するようにします。さらに、その参照値が頻繁に変わる必要がない場合は、そのセルの値をコピーし、「値」として貼り付けることで、数式を定数に置き換えることができます。
3. VBAによる条件付き再計算
   Worksheet_Changeイベントプロシージャを利用して、変更されたセルのアドレスや値に基づいて、再計算が必要な揮発性関数のみを更新するように記述します。これにより、無駄な再計算を大幅に削減できます。

INDIRECT関数で参照シート名が変更できない

原因: INDIRECT関数で参照しているシート名が、ブックの構造変更やシート名の変更によって更新されなくなった場合です。シート名が数式内に直接記述されている場合、シート名が変更されると数式はエラーになります。

対処法:

1. 名前の定義を利用する
   シート名やセル範囲を「名前の定義」で登録し、INDIRECT関数からその名前を参照するようにします。シート名が変更された場合でも、「名前の管理」ダイアログで登録されている名前の参照先を修正するだけで済みます。
2. VBAでシート名を管理する
   シート名の変更を検知するVBAコードを記述し、自動的にINDIRECT関数内のシート名を更新するようにします。または、ブック内のシート名をリスト化し、INDIRECT関数がそのリストを参照するように設計することも可能です。
3. 代替関数への移行
   可能であれば、INDIRECT関数を使用せずに済むように、数式やブックの構造を見直します。例えば、XLOOKUP関数やINDEX/MATCH関数を組み合わせることで、INDIRECT関数を使わずに動的な参照を実現できる場合があります。

機能	揮発性関数 (例: NOW, INDIRECT)	非揮発性関数 (例: SUM, AVERAGE)
再計算のトリガー	ブックの変更、またはExcelの起動時に自動再計算される	依存するセルが変更された場合にのみ再計算される
パフォーマンスへの影響	多用するとブックが遅くなる原因となる	通常、パフォーマンスへの影響は小さい
主な使用目的	動的な参照、現在の日時取得、条件に応じた参照範囲の変更	データの集計、統計処理、論理演算
注意点	再計算頻度が高いため、設計に工夫が必要	直接的なパフォーマンス低下の原因にはなりにくい

揮発性関数はExcelで動的な機能を実現するために非常に役立ちますが、その特性を理解せずに多用すると、ブックのパフォーマンスを著しく低下させる可能性があります。本記事では、揮発性関数がExcelを遅くする原因と、再計算頻度を抑えるための設計のコツ、そしてよくある失敗パターンとその対処法について解説しました。

これらの設計のコツを実践することで、NOW関数やINDIRECT関数などを効率的に活用し、Excelブックの動作速度を改善できます。今後は、ブックを設計する際に、揮発性関数の使用箇所を意識し、代替案や制御方法を検討することをお勧めします。これにより、より大規模で複雑なExcelファイルでも、快適に作業を進めることが可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】