Excel のパフォーマンス: 計算パフォーマンスの強化
適用対象: Excel | Excel 2013 | Excel 2016 | VBA
Office Excel 2016 では、100 万行と 16,000 の列から成る "大きなグリッド" に加え、他にも多くの点で上限が引き上げられており、以前のバージョンの Excel と比較して構築できるワークシートのサイズが大幅に向上しています。 Excel の 1 つのワークシートには、以前のバージョンと比べて 1,000 倍の数のセルを含めることができるようになりました。
以前のバージョンの Excel で作成するワークシートは、どれも計算が遅く、ワークシートのサイズが大きくなると、さらに計算が遅くなるのが常でした。 Excel 2007 で "大きなグリッド" が導入されたことがきっかけとなり、パフォーマンスが問題視されるようになりました。 計算が遅いだけでなく、並べ替えやフィルター処理などのデータ操作タスクにも時間がかかるため、ユーザーは目の前の作業に集中できなくなり、エラーが頻発するようになりました。
最近の Excel のバージョンでは、このキャパシティの増大に対応するための機能が導入されました。たとえば、計算や一般的なデータ セット操作 (ブックを更新、並べ替え、開くなど) で一度に複数のプロセッサを使用できる機能などです。 計算をマルチスレッドで実行することにより、ワークシートの計算時間を大幅に短縮できます。 ただし、Excel の計算速度に影響する最も重要な要因は、やはり、ワークシートの設計および作成方法です。
計算速度の遅いワークシートはほとんどの場合、数十倍、数百倍、さらには数千倍も速く計算されるように変更することができます。 ワークシートの計算上の問題点を特定し、測定し、改善することによって、計算速度を上げることができます。
計算速度の重要性
計算速度が遅いと、生産性に影響し、ユーザー エラーが増加します。 ユーザーの生産性と作業への集中力は、応答時間が長くなるにつれて低下します。
Excel には、主に、次の 2 つの計算モードがあり、計算をどのタイミングで行うかを制御できます。
自動計算 - 変更を加えたときに、数式が自動で再計算されます。
手動計算 - 数式の再計算は、要求したときにのみ実行されます (たとえば、F9 キーを押したとき)。
計算時間が約 0.1 秒未満の場合、ユーザーはシステムが一瞬で応答していると感じます。 自動計算は、ユーザーがデータを入力するときにも使用できます。
計算時間が 0.1 ～ 1 秒の場合、ユーザーの思考は途切れませんが、応答時間の遅れを意識するようになります。
計算時間が長くなるにつれて (通常は 1 秒から 10 秒の間)、データを入力するときに、手動計算に切り替える必要があります。 ユーザーのエラーが増え始め、苛立ちの度合いが高くなります。特に、その傾向は反復的な作業で顕著に見られ、思考の流れを保つのが困難になります。
計算時間が 10 秒を超えると、ユーザーは待ちきれないので、応答が返るまで別の作業を行うようになります。 この段階になると、計算は一連のさまざまな作業の一部に取り込まれてしまい、ユーザーがその流れを誤ると、問題が発生することがあります。
Excel での計算方法について
Excel の計算パフォーマンスを強化するには、使用できる計算方法と、その計算方法の制御方法について理解する必要があります。
全計算と再計算との依存関係
Excel の高機能再計算エンジンは、できるだけ短時間で計算できるように、各数式の参照元と依存関係 (数式から参照されるセルのこと) の両方と、前回の計算以降に加えられた変更をすべて継続的に追跡します。 次回の再計算時には、次の再計算のみが行われます。
変更された、または要再計算としてフラグが設定されたセル、数式、値、または名前。
再計算が必要なセル、数式、名前、または値に依存しているセル。
揮発性関数と、表示される条件付き書式。
既に計算済みのセルに依存するセルは、依存元のセルの値が変更されていなくても、引き続き計算されます。
前回の計算以降、何か変更があるとしても、それは入力データの一部であったり、ごく少数の数式であったりするのがほとんどなので、再計算にかかる時間は、すべての数式の全計算にかかる時間と比べると、ほんのわずかです。
手動計算モードでは、F9 キーを押してこのスマート再計算をトリガーできます。 Ctrl + Alt + F9 キーを押すと、すべての数式の全計算を強制的に実行できます。また、Shift + Ctrl + Alt + F9 キーを押すと、依存関係の完全な再構築と全計算を強制的に実行できます。
計算プロセス
他のセルを参照する Excel の数式を、参照先セルの前または後に配置できます (前方参照または後方参照)。 このような参照方法を Excel で使用できるのは、セルの計算順序が一定でない、つまり、セルは行または列ごとに計算されないからです。 Excel での計算順序は、静的ではなく、計算するすべての数式のリスト (計算チェーン) と各数式の依存情報に基づいて、動的に決まります。
Excel の計算フェーズは独自です。
初期の計算チェーンを構築し、計算を開始する位置を決定します。 このフェーズは、ブックがメモリに読み込まれたときに発生します。
依存関係を追跡し、未計算のセルにフラグを設定し、計算チェーンを更新します。 このフェーズは、セルに入力または変更があるたびに発生します (手動計算モードの場合も含む)。 これは通常、気付かないほど高速で実行されますが、複雑なケースでは応答が低速になることがあります。
すべての数式を計算します。 計算プロセスの一部として、計算チェーンの順序を変更して再構築し、今後の再計算を最適化します。
Excel ウィンドウの表示されている部分を更新します。
3 番目のフェーズは、計算または再計算の度に実行されます。 Excel は、計算チェーンの各数式を順に計算しようとしますが、1 つ以上の未計算の数式に依存する数式があると、その数式を後回しにして後で再度計算します。 つまり、数式は再計算の度に複数回計算されることがあります。
通常、ブックの計算速度は 1 回目よりも 2 回目の方が大幅に向上します。 それにはいくつかの理由があります。
通常は、変更されたセルとその依存関係のみ再計算されます。
最新の計算順序を保管して再使用するので、計算順序を決める時間を大幅に節約できます。
複数のコア コンポーネントを備えているため、Excel は、以前の計算結果に基づいて計算をコア全体に拡散する方法の最適化を試行します。
Excel のセッションでは、Windows と Excel の両方が、より迅速にアクセスできるように、最近使用したデータとプログラムをキャッシュします。
ブック、ワークシート、および範囲の計算
別の Excel の計算方法を使用して、計算対象を制御することができます。
開いているすべてのブックを計算する
再計算と全計算を行うたびに、現在開いているすべてのブックを計算し、ブックとワークシート内の依存関係と、ブックとワークシートの間の依存関係を解決し、以前に計算されていなかった (ダーティ) セルを計算済みとして再設定します。
選択しているワークシートを計算する
Shift + F9 キーを押して、選択したワークシートのみを再計算することもできます。 これにより、ワークシートの依存関係が解決され、計算に従って、以前に計算されていない (ダーティ) セルがすべてリセットされます。
以前のバージョンの Excel では、動作が異なり、計算が完了した後ダーティセルは計算どおりに設定されませんでした。 ユーザー定義関数がこの動作に依存している場合は、この記事の「Volatile 関数」セクションで説明されているように、これらの 関数を代わりに揮発性 にする必要があります。
セルの範囲を計算する
Excel では、Visual Basic for Applications (VBA) のメソッド Range.CalculateRowMajorOrder と Range.Calculate を使用して、セルの範囲を計算できます。
Range.CalculateRowMajorOrder は左から右へ、上から下へ範囲を計算し、依存関係をすべて無視します。
Range.Calculate は範囲内のすべての依存関係を解決しながら、範囲を計算します。
CalculateRowMajorOrder は計算範囲内にある依存関係をいずれも解決しないので、通常、Range.Calculate より大幅に高速になります。 ただし、Range.Calculate とは同じ結果が得られない場合があるため、使用には注意が必要です。
Range.Calculate は、Excel の中で最も便利なパフォーマンス最適化ツールの 1 つで、さまざまな数式の計算速度を記録して比較できます。
詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」を参照してください。
揮発性関数
揮発性関数は、参照元が変更されていないように思われる場合でも、再計算のたびに必ず再計算されます。 揮発性関数を多用すると、再計算のたびに計算速度が低下しますが、全計算には影響しません。 Application.Volatile を関数コードに含めることによって、ユーザー定義の関数を揮発性にすることができます。
Excel の組み込み関数の中でも、RAND()、NOW()、TODAY() は、明らかに揮発性関数です。 揮発性関数である度合いが低い他の関数には、OFFSET()、CELL()、INDIRECT()、INFO() があります。
以前に揮発性関数として文書化されていたいくつかの関数、INDEX()、ROWS()、COLUMNS()、AREAS() は実際には揮発性ではありません。
揮発性操作
揮発性操作とは、再計算を呼び出す操作であり、次のものが含まれます。
- 自動モードで、行または列の分割線をクリックする。
- シートに、行、列、またはセルを挿入または削除する。
- 定義名を追加、変更、または削除する。
- 自動モードで、ワークシートの名前を変更、またはワークシートの位置を変更する。
- 行をフィルタリングする、非表示にする、再表示する。
- 自動モードになっているときにブックを開く。 前回、ブックが別のバージョンの Excel で計算されている場合は、通常、ブックを開くと全計算が実行されます。
- [保存前に計算] オプションが選択されている場合は、手動モードでブックを保存します。
数式と名前が評価される場合
次のいずれかの操作を行うと、数式または数式の一部がすぐに評価 (計算) されます (手動計算モードの場合を含む)。
- 数式を入力または編集する。
- 関数ウィザードを使用して数式を入力または編集する。
- 関数ウィザードで、数式を引数として入力する。
- 数式バーから数式を選択して F9 キーを押すか (操作を元に戻して数式に戻るには Esc キーを押す)、[数式の検証] をクリックする。
数式の参照先 (依存先) セルまたは数式が、次のいずれかの条件を満たしていると、参照元の数式には未計算のフラグが設定されます。
- セルまたは数式が入力された。
- セルまたは数式が変更された。
- セルまたは数式がオートフィルター リストに含まれ、条件ドロップダウン リストが有効になっている。
- 未計算のフラグが設定されている。
未計算のフラグが設定された数式は、その未計算の数式を含むワークシート、ブック、または Excel インスタンスの計算時または再計算時に評価されます。
定義名の評価が発生する条件は、セル内の数式の評価が発生する条件とは異なります。
- 定義名の評価は、その定義名を参照する数式が評価されるたびに行われます。したがって、1 つの名前を複数の数式で使用していると、その名前が何回も評価されることがあります。
- どの数式からも参照されていない名前は、全計算の対象からも外れます。
データ テーブル
Excel データ テーブル ([データ] タブ [データ ツール] > グループ>の [What-If AnalysisData Table]\(What-If Analysis >Data Table\) は、テーブル機能 ([ホーム] タブの [スタイル] グループ>の [テーブルとして書式設定] > または [テーブルの挿入] タブ>の [>テーブル] グループ) と混同しないでください。 Excel のデータ テーブルは、ブックについて複数の再計算を実行するもので、それぞれの計算はテーブル内のさまざまな値によって駆動されます。 Excel は最初に、ブックを通常どおり計算します。 次に、行と列の値のペアごとに、値を置き換え、シングル スレッドの再計算を実行し、結果をデータ テーブルに保存します。
データ テーブルの再計算では、常に 1 つのプロセッサのみが使用されます。
データ テーブルは、複数のバリエーションを計算し、バリエーションの結果を表示して比較するための便利な方法を提供します。 [テーブル以外自動] の計算オプションを使用すると、各計算で複数の計算が自動でトリガーされないように、ただしテーブルを除くすべての依存している数式は計算するように Excel を設定できます。
計算オプションの制御
Excel には、計算方法を制御する一連のオプションがあります。 Excel で使用頻度が最も高いオプションは、リボンの [数式] タブの [計算] グループで変更できます。
図 1. [数式] タブの [計算方法]
![[数式] タブの計算オプション](../../../images/ocd_xl2010_ta_improvingcalculationperf_fig01.jpg)
Excel の計算オプションをさらに確認するには、[ファイル] タブで [オプション] をクリックします。 [Excel のオプション] ダイアログ ボックスで [数式] タブをクリックします。
図 2. [Excel のオプション] の [数式] タブにある計算オプション
多数の計算オプションがあります ([自動]、[データ テーブル以外自動]、[手動]、[ブックの保存前に再計算を行う])。反復設定 ([反復計算を行う]、[最大反復回数]、[変化の最大値]) もあります。これらの計算オプションと反復設定は、ブック レベルではなく、アプリケーション レベルで機能します (これらは、開いているブックにすべて同様に適用されます)。
高度な計算オプションを見つけるには、[ファイル] タブで [オプション] をクリックします。 [Excel のオプション] ダイアログ ボックスで、[詳細設定] をクリックします。 [数式] セクションで計算オプションを設定します。
図 3. 詳細な計算オプション
Excel を起動するとき、またはどのブックも開いていない状態で Excel が実行されているときには、最初の計算モードと反復計算の設定は、最初に開く非テンプレート、非アドインのブックにより決まります。 つまり、後で開かれたブックの計算設定は無視されます。もちろん、いつでも Excel の設定は手動で変更できます。 ブックを保存すると、現在の計算設定がブックに保存されます。
自動計算
自動計算モードでは、開いているブックで変更がある度に、および、ブックを開いたときに、Excel によって自動的に再計算が行われます。 通常、自動モードでブックを開き、Excel によって再計算が行われるときには、再計算が見えることはありません。ブックが保存されてから何も変更されていないためです。
ブックが前回最後に計算されたときに使用したバージョンより新しいバージョンの Excel でブックを開いた場合に、この計算に気づく可能性があります (例: Excel 2016 と Excel 2013)。 Excel の計算エンジンが異なるため、Excel は以前のバージョンを使用して保存されたブックを開くときに、フル計算を実行します。
手動計算
手動計算モードでは、F9 キーまたは Ctrl + Alt + F9 キーを押すことで要求したとき、あるいはブックを保存するときに、開いているすべてのブックが Excel によって再計算されます。 再計算が一瞬では完了しないブックの場合は、変更を加えたときの遅延を避けるために、計算を手動モードに設定する必要があります。
手動モードのブックを再計算する必要がある場合には、ステータス バーに [計算] と表示されます。 ブックに循環参照が含まれていて、反復オプションが選択されている場合にも、ステータス バーに [計算] と表示されます。
反復設定
ブックに循環参照を意図的に含めている場合は、反復設定を使用して、ブックの最大再計算 (反復) 回数と収束条件 (変化の最大値: 停止するタイミング) を制御できます。 反復ボックスをオフにしておくと、循環参照が誤って含まれている場合に、そのことが警告されるだけで、解決は試行されません。
ブックの ForceFullCalculation プロパティ
このブックのプロパティを True に設定すると、Excel のスマート再計算がオフになり、再計算の度に、開いているすべてのブック内のすべての数式が再計算されるようになります。 一部の複雑なブックでは、スマート再計算に必要な依存関係ツリーの構築と管理にかかる時間のほうが、スマート再計算によって節約される時間よりも長くなります。
手動モードにしても、ブックを開くのに非常に長い時間がかかる、または、小さな変更を加えるだけでも長い時間がかかる場合は、ForceFullCalculation を試してみるとよいでしょう。
ブックの ForceFullCalculation プロパティが True に設定されている場合、ステータス バーに [計算] と表示されます。
この設定は、VBE を使用し (Alt + F11)、ThisWorkbook を プロジェクト エクスプローラー で選択し (Ctrl+R)、プロパティ ウィンドウ を表示する (F4) ことで制御できます。
図 4. Workbook.ForceFullCalculation プロパティの設定
ブックの計算の高速化
ブックの計算を高速化するには、次の手順と方法を使用します。
プロセッサの速度と複数のコア
ほとんどのバージョンの Excel で、プロセッサが高速になれば、それだけ Excel の計算も高速になります。 Excel 2007 で導入されたマルチスレッド計算エンジンによって、Excel でマルチプロセッサ システムを存分に利用できるようになり、ほとんどのブックで大幅なパフォーマンスの向上を期待できます。
ほとんどの大きなブックでは、計算のパフォーマンスは、物理プロセッサの数とほぼ比例して複数のプロセッサ スケールにより向上します。 しかしながら、物理プロセッサのハイパースレッドによるパフォーマンス向上はわずかなものです。
詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」を参照してください。
RAM
仮想メモリ ページング ファイルへのページングは、低速です。 オペレーティング システム、Excel、およびブックのために使用できる物理 RAM を十分に確保する必要があります。 計算中も、ある程度定期的にハード ディスク アクティビティがあり、ディスク アクティビティを呼び出すユーザー定義関数を実行しない場合は、より多くの RAM が必要です。
前にも説明したように、最近のバージョンの Excel は、大量のメモリを効果的に使用するようになっており、Excel 2007 と Excel 2010 の 32 ビット版では、単一のブックや複数のブックの組み合わせの処理に、最大 2 GB のメモリを使用することがあります。
32 ビット バージョンの Excel 2013 および L ADDRESS Aware (LAA) 機能を使用するExcel 2016は、インストールされている Windows のバージョンに応じて、最大 3 GB または 4 GB のメモリを使用できます。 64 ビット版の Excel は、より大きなブックを処理できます。 詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」のセクション「大規模データ セット、LAA、64 ビット版の Excel」を参照してください。
計算を効率化するためのガイドラインとして、同時に開く必要があるブックの最大セットに対応できるだけの十分な RAM が必要です。また、Excel とオペレーティング システム用に 1 GB または 2 GB の RAM と、その他の実行アプリケーション用の RAM も必要です。
計算時間の測定
ブックの計算を速くするためには、計算時間を正確に測定できる必要があります。 VBA の Time 関数よりも高速で正確なタイマーが必要になります。 次のコード例に示す MICROTIMER() 関数では、システムの高精度タイマーへの Windows API 呼び出しを使用しています。 このタイマーはミリ秒単位で時間を測定できます。 ただし、Windows はマルチタスク オペレーティング システムであること、および 1 回目よりも 2 回目の計算時間の方が短くなる可能性があるという理由から、正確な時間を繰り返し取得できるとは限りません。 できるだけ厳密に時間を把握したい場合は、計算時間を複数回測定して平均値を求めます。
Visual Basic エディターが VBA ユーザー定義関数のパフォーマンスに大幅に影響する可能性があることについての詳細は、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」のセクション「より高速な VBA ユーザー定義関数」を参照してください。
#If VBA7 Then
Private Declare PtrSafe Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#Else
Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
#End If
Function MicroTimer() As Double
'
' Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
' Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
' Get ticks.
getTickCount cyTicks1
' Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
計算時間を測定するには、適切な計算方法を呼び出す必要があります。 次のサブルーチンでは、範囲の計算時間、ワークシートまたは開いているすべてのブックの再計算時間、開いているすべてのブックの全計算時間を測定できます。
これらのサブルーチンと関数を標準 VBA モジュールにすべてコピーしてください。 VBA エディターを開くには、Alt + F11 キーを押します。 [挿入] メニューで、[モジュール] を選択し、モジュールにコードをコピーします。
Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
DoCalcTimer 4
End Sub
Sub DoCalcTimer(jMethod As Long)
Dim dTime As Double
Dim dOvhd As Double
Dim oRng As Range
Dim oCell As Range
Dim oArrRange As Range
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
'
On Error GoTo Errhandl
' Initialize
dTime = MicroTimer
' Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1
' Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End if
' Max is used range.
If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If
' Include array cells outside selection.
For Each oCell In oRng
If oCell.HasArray Then
If oArrRange Is Nothing Then
Set oArrRange = oCell.CurrentArray
End If
If Intersect(oCell, oArrRange) Is Nothing Then
Set oArrRange = oCell.CurrentArray
Set oRng = Union(oRng, oArrRange)
End If
End If
Next oCell
sCalcType = "Calculate " & CStr(oRng.Count) & _
" Cell(s) in Selected Range: "
Case 2
sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
Case 3
sCalcType = "Recalculate open workbooks: "
Case 4
sCalcType = "Full Calculate open workbooks: "
End Select
' Get start time.
dTime = MicroTimer
Select Case jMethod
Case 1
If Val(Application.Version) >= 12 Then
oRng.CalculateRowMajorOrder
Else
oRng.Calculate
End If
Case 2
ActiveSheet.Calculate
Case 3
Application.Calculate
Case 4
Application.CalculateFull
End Select
' Calculate duration.
dTime = MicroTimer - dTime
On Error GoTo 0
dTime = Round(dTime, 5)
MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
vbOKOnly + vbInformation, "CalcTimer"
Finish:
' Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Iteration = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox "Unable to Calculate " & sCalcType, _
vbOKOnly + vbCritical, "CalcTimer"
GoTo Finish
End Sub
Excel でサブルーチンを実行するには、Alt + F8 キーを押します。 目的のサブルーチンを選択し、[実行] をクリックします。
図 5. 計算時間を示す Excel マクロ ウィンドウ
計算上の問題点の特定と優先順位付け
計算がどんなに遅くても、計算時間の大半を費やしている問題や障害が存在するのは 2 - 3 箇所にすぎません。 問題点が特定されていない場合は、このセクションで説明するドリルダウン アプローチを使用して特定します。 既に特定している場合は、それぞれの問題点にどれだけの計算時間が費やされているかを個別に測定し、問題解決のために行う作業に優先順位を付けます。
問題点を特定するドリルダウン アプローチ
ドリルダウン アプローチは、ブックの計算時間を測定することから始めます。次に、各ワークシートの計算時間を測定し、計算が遅いワークシートがあれば、その数式ブロックの計算時間を測定します。 各手順を順序どおりに行って、計算時間を記録します。
ドリルダウン アプローチによって問題点を特定するには
ブックを 1 つだけ開き、他のタスクは一切実行されていないことを確認します。
計算を手動に設定します。
ブックのバックアップ コピーを作成します。
計算時間マクロを含むブックを開くか、そのマクロをブックに追加します。
各ワークシートで、順に Ctrl + End キーを押して、使用範囲を調べます。
この操作によって、最後に使用したセルの場所がわかります。 予想範囲を越えた場所に、最後に使用したセルがある場合は、余分な列と行を削除してからブックを保存することを検討します。 詳細については、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」のセクション「使用されている範囲の最小化」を参照してください。
FullCalcTimer マクロを実行します。
ブック内のすべての数式を計算する時間が、通常は最も長くかかるケースとなります。
RecalcTimer マクロを実行します。
全計算の直後に再計算すると、通常は、ベストケースの計算時間になります。
ブックの揮発性を、再計算時間対全計算時間の比として計算します。
この比は、数式の揮発性を表し、計算チェーンの評価が問題になっていないかどうかを判定します。
各シートをアクティブにして、順番に SheetTimer マクロを実行します。
ブックを再計算した直後なので、各ワークシートの再計算時間が測定されます。 これで、どのワークシートに問題があるかを特定できる必要があります。
選択された数式のブロックに対して RangeTimer マクロを実行します。
問題のあるそれぞれのワークシートについて、行または列を分けていくつかの小さいブロックにします。
各ブロックを順番に選択して、ブロックに対して RangeTimer マクロを実行します。
必要に応じて、各ブロックをさらに小さく分割して、より深くドリルダウンします。
問題点に優先順位を付けます。
計算速度の向上と問題の軽減
計算時間が長くなる要因は、数式の数やブックのサイズではありません。 関係するのは、セル参照と計算操作の数、および使用されている関数の効率性です。
ほとんどのワークシートは、絶対参照と相対参照を含む数式をコピーして構成されているため、一般的には、反復または重複する計算と参照を含む大量の数式がワークシートには含まれています。
複雑な大量の数式や配列数式を使用しないでください。 一般的には、行と列を増やして、複雑な計算を減らすようにします。 この方針に従うと、Excel の高機能再計算とマルチスレッド計算を必要に応じてより適切に最適化できます。 また、理解しやすいのでデバッグも簡単です。 次に、ブックの計算速度を改善するいくつかの規則を示します。
規則その 1: 重複、反復、および不要な計算をなくす
重複、反復、および不要な計算がないかどうかを調べ、そうした問題を抱えた状態で結果を計算するために、現在 Excel で必要としているセルの参照数と計算回数を概算します。 それよりも少ない参照数と計算回数で同じ結果を得る方法を考えます。
通常、この作業には、次の 1 つ以上の手順が必要です。
各数式での参照数を減らします。
反復する計算を 1 つ以上の補助的なセルに移動し、そのセルを元の数式から参照します。
計算の中間結果を一時的に格納する行や列を追加して、それらの結果を別の数式で再使用できるようにします。
規則その 2: できるだけ効率的な関数を使用する
問題に関数や配列数式が関係している場合は、より効率的な方法で同じ結果を得ることができないかどうかを考えます。 次に例を示します。
ルックアップの前にデータを並べ替えておくと、並べ替えていないデータをルックアップする場合よりも、効率が数十倍または数百倍向上することがあります。
VBA ユーザー定義関数は、通常、Excel の組み込み関数よりも低速です (ただし、慎重に記述されていれば高速です)。
SUM と SUMIF などの関数で使用されているセルの数を最小限に抑えます。 計算時間はセルの使用数に比例します (未使用のセルは無視されます)。
低速の配列数式をユーザー定義関数に置き換えることを検討します。
規則その 3: スマート再計算とマルチスレッド計算を効果的に使用する
Excel のスマート再計算とマルチスレッド計算をより適切に利用すれば、それだけ少ない処理で毎回の再計算を実行できます。そこで、次のことを実践します。
他の関数よりも大幅に効率的でない限り、INDIRECT や OFFSET などの揮発性関数は使用しないでください。 (適切に設計されていれば OFFSET は一般的に高速です。)
配列数式や関数で使用する範囲のサイズを最小にします。
配列数式やメガ関数は、別の補助的な列や行に分割します。
シングルスレッドの関数を使用しないでください。
- PHONETIC
- CELL ("format" or "address" のどちらかの引数が使用された場合)
- INDIRECT
- GETPIVOTDATA
- CUBEMEMBER
- CUBEVALUE
- CUBEMEMBERPROPERTY
- CUBESET
- CUBERANKEDMEMBER
- CUBEKPIMEMBER
- CUBESETCOUNT
- ADDRESS (5 番目のパラメーター "sheet_name" が指定されている場合)
- ピボットテーブルを参照するデータベース関数 (DSUM、DAVERAGE など)
- ERROR.TYPE
- HYPERLINK
- VBA および COM アドインのユーザー定義関数
データ テーブルと循環参照の反復的な使用を避ける: これらはどちらも、常にシングルスレッドで計算されます。
規則その 4: 変更を行うたびに時間の測定とテストを行う
自分が行った変更によって、思いどおりの答えが得られなかったり、計算が予想以上に低速になったりすると、そのことに自分自身が驚いてしまうことがあります。 したがって、次に示すように、変更を行うたびに時間を測定し、変更結果をテストする必要があります。
変更したい数式を、RangeTimer マクロを使用して時間計測します。
変更を加えます。
変更した数式を、RangeTimer マクロを使用して時間計測します。
変更された数式の結果が引き続き正しいことを確認します。
規則例
以下のセクションでは、規則を使用して計算を速くする方法の例を示します。
期間の初めから当日までの合計
たとえば、2,000 個の数字を含む列で、期間の初めから当日までの合計を求める必要があるとします。 列 A に数値が含まれていて、列 B と列 C に期間の初めから当日までの合計を含める必要があるとします。
効率的な関数である SUM を使用して、数式を作成できます。
B1=SUM($A$1:$A1)
B2=SUM($A$1:$A2)
図 6. 期間対日付の SUM 数式の例
この数式を B2000 までコピーします。
合計でいくつのセル参照が SUM によって追加されているでしょうか。 B1 が 1 つのセルを参照し、B2000 が 2,000 個のセルを参照します。 平均は、セルごとに 1,000 個の参照となるので、参照の合計数は 2 百万となります。 2,000 の数式を選択して RangeTimer マクロを使用すると、列 B の 2,000 の数式が 80 ミリ秒で計算されるということが示されます。 このような計算のほとんどは、何度も重複しています。つまり、SUM はB2:B2000 にあるそれぞれの数式で A1 を A2 に加算しています。
この重複は、数式を次のように作成することでなくすことができます。
C1=A1
C2=C1+A1
この数式を C2000 までコピーします。
この場合、合計でいくつのセル参照が加算されているでしょうか。 最初の数式を除き、各数式では、2 つのセル参照を使用しています。 したがって、合計は 1999*2+1=3999 となります。 これで、セル参照の数は 500 分の 1 になります。
RangeTimer の結果、列 C の 2,000 個の数式の計算時間は 3.7 ミリ秒でした (列 B の計算時間は 80 ミリ秒でした)。この変更により、1 数式あたりのオーバーヘッドが小さくなるので、パフォーマンス向上係数は、500 ではなく、80/3.7=22 になります。
エラー処理
計算処理を大量に含む数式で、エラー発生時 (完全一致ルックアップの実行時に頻繁に発生) の結果をゼロとして表示したい場合は、エラー処理を次のいくつかの方法で記述できます。
単一の数式で記述できます。ただし、処理は低速です。
B1=IF(ISERROR(time expensive formula),0,time expensive formula)2 つの数式で記述できます。処理は高速です。
A1=time expensive formulaB1=IF(ISERROR(A1),0,A1)または、IFERROR 関数を使用できます。この関数は高速かつシンプルで、単一の数式です。
B1=IFERROR(time expensive formula,0)
[一意にカウント] の動的な実行
図 7. [一意にカウント] のデータのリストの例
![[一意にカウント] のデータの例](../../../images/ocd_xl2010_ta_improvingcalculationperf_fig07.jpg)
たとえば、列 A に 11,000 行から成るデータのリストがあり、頻繁に変更が発生し、リスト内にある一意の項目 (空白は除く) の項目数を動的に計算する数式が必要であるとします。この場合、次のようないくつかの方法が考えられます。
配列数式 (Ctrl + Shift + Enter を使用)。RangeTimer では、この場合に 13.8 秒かかることが示されています。
{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}SUMPRODUCT は通常、同等の配列数式よりも高速で計算されます。 この数式の計算には 10.0 秒かかります。したがって、パフォーマンス向上率は 13.8/10.0=1.38 で、パフォーマンスは向上していますが、これではまだ不十分です。
=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&""))ユーザー定義関数。 次のコード例では、コレクションへのインデックスが一意でなければならないことを利用した VBA ユーザー定義関数を示しています。 使用されているいくつかの手法の詳細については、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」でユーザー定義関数について説明しているセクション「関数を効率的に使用する」を参照してください。 この数式
=COUNTU(A2:A11000)では、かかるのはわずか 0.061 秒です。 この場合の向上率は 13.8/0.061=226 となります。Public Function COUNTU(theRange As Range) As Variant Dim colUniques As New Collection Dim vArr As Variant Dim vCell As Variant Dim vLcell As Variant Dim oRng As Range Set oRng = Intersect(theRange, theRange.Parent.UsedRange) vArr = oRng On Error Resume Next For Each vCell In vArr If vCell <> vLcell Then If Len(CStr(vCell)) > 0 Then colUniques.Add vCell, CStr(vCell) End If End If vLcell = vCell Next vCell COUNTU = colUniques.Count End Function数式の列の追加。 前のサンプル データを見ると、並べ替えられていることがわかります (Excel では、11,000 行を並べ替えるには 0.5 秒かかります)。 このことを利用して、この行のデータが前の行のデータと同じかどうかをチェックする数式の列を追加することができます。 異なる場合は、数式は 1 を返します。 それ以外の場合は、0 を返します。
この数式をセル B2 に追加します。
=IF(AND(A2<>"",A2<>A1),1,0)この数式をコピーします。次に、列 B を加算する数式を追加します。
=SUM(B2:B11000)これらすべての数式の全計算にかかる時間は 0.027 秒です。 このパフォーマンス向上係数は 13.8/0.027=511 です。
まとめ
Excel では、より大きなワークシートを効果的に管理できるようになり、以前のバージョンと比較して計算速度が大幅に向上しています。 大きなワークシートを作成すると、計算が遅くなることがよくあります。 計算が遅いワークシートを使用していると、計算中にユーザーの集中力が途切れがちで、エラーが増加します。
直接的なテクニックを使用することにより、計算がどんなに遅いワークシートでも、計算速度を 10 ～ 100 倍上げることができます。 また、これらのテクニックをワークシートの設計および作成時に適用すれば、ワークシートがすばやく計算されることを保証できます。
関連項目
サポートとフィードバック
Office VBA またはこの説明書に関するご質問やフィードバックがありますか？ サポートの受け方およびフィードバックをお寄せいただく方法のガイダンスについては、Office VBA のサポートおよびフィードバックを参照してください。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示