Excel のパフォーマンス: 計算パフォーマンスの強化Excel performance: Improving calculation performance

適用対象: Excel | Excel 2013 | Excel 2016 | VBAApplies to: Excel | Excel 2013 | Excel 2016 | VBA

Office Excel 2016 では、100 万行と 16,000 の列から成る "大きなグリッド" に加え、他にも多くの点で上限が引き上げられており、以前のバージョンの Excel と比較して構築できるワークシートのサイズが大幅に向上しています。The "Big Grid" of 1 million rows and 16,000 columns in Office Excel 2016, together with many other limit increases, greatly increases the size of worksheets that you can build compared to earlier versions of Excel. Excel の 1 つのワークシートには、以前のバージョンと比べて 1,000 倍の数のセルを含めることができるようになりました。A single worksheet in Excel can now contain over 1,000 times as many cells as earlier versions.

以前のバージョンの Excel で作成するワークシートは、どれも計算が遅く、ワークシートのサイズが大きくなると、さらに計算が遅くなるのが常でした。In earlier versions of Excel, many people created slow-calculating worksheets, and larger worksheets usually calculate more slowly than smaller ones. Excel 2007 で "大きなグリッド" が導入されたことがきっかけとなり、パフォーマンスが問題視されるようになりました。With the introduction of the "Big Grid" in Excel 2007, performance really matters. 計算が遅いだけでなく、並べ替えやフィルター処理などのデータ操作タスクにも時間がかかるため、ユーザーは目の前の作業に集中できなくなり、エラーが頻発するようになりました。Slow calculation and data manipulation tasks such as sorting and filtering make it more difficult for users to concentrate on the task at hand, and lack of concentration increases errors.

最近の Excel のバージョンでは、このキャパシティの増大に対応するための機能が導入されました。たとえば、計算や一般的なデータ セット操作 (ブックを更新、並べ替え、開くなど) で一度に複数のプロセッサを使用できる機能などです。Recent Excel versions introduced several features to help you handle this capacity increase, such as the ability to use more than one processor at a time for calculations and common data set operations like refresh, sorting, and opening workbooks. 計算をマルチスレッドで実行することにより、ワークシートの計算時間を大幅に短縮できます。Multithreaded calculation can substantially reduce worksheet calculation time. ただし、Excel の計算速度に影響する最も重要な要因は、やはり、ワークシートの設計および作成方法です。However, the most important factor that influences Excel calculation speed is still the way your worksheet is designed and built.

計算速度の遅いワークシートはほとんどの場合、数十倍、数百倍、さらには数千倍も速く計算されるように変更することができます。You can modify most slow-calculating worksheets to calculate tens, hundreds, or even thousands of times faster. ワークシートの計算上の問題点を特定し、測定し、改善することによって、計算速度を上げることができます。By identifying, measuring, and then improving the calculation obstructions in your worksheets, you can speed up calculation.

計算速度の重要性The importance of calculation speed

計算速度が遅いと、生産性に影響し、ユーザー エラーが増加します。Poor calculation speed affects productivity and increases user error. ユーザーの生産性と作業への集中力は、応答時間が長くなるにつれて低下します。User productivity and the ability to focus on a task deteriorates as response time lengthens.

Excel には、主に、次の 2 つの計算モードがあり、計算をどのタイミングで行うかを制御できます。Excel has two main calculation modes that let you control when calculation occurs:

  • 自動計算 - 変更を加えたときに、数式が自動で再計算されます。Automatic calculation - Formulas are automatically recalculated when you make a change.

  • 手動計算 - 数式の再計算は、要求したときにのみ実行されます (たとえば、F9 キーを押したとき)。Manual calculation - Formulas are recalculated only when you request it (for example, by pressing F9).

計算時間が約 0.1 秒未満の場合、ユーザーはシステムが一瞬で応答していると感じます。For calculation times of less than about a tenth of a second, users feel that the system is responding instantly. 自動計算は、ユーザーがデータを入力するときにも使用できます。They can use automatic calculation even when they enter data.

計算時間が 0.1 ~ 1 秒の場合、ユーザーの思考は途切れませんが、応答時間の遅れを意識するようになります。Between a tenth of a second and one second, users can successfully keep a train of thought going, although they will notice the response time delay.

計算時間が長くなるにつれて (通常は 1 秒から 10 秒の間)、データを入力するときに、手動計算に切り替える必要があります。As calculation time increases (usually between 1 and 10 seconds), users must switch to manual calculation when they enter data. ユーザーのエラーが増え始め、苛立ちの度合いが高くなります。特に、その傾向は反復的な作業で顕著に見られ、思考の流れを保つのが困難になります。User errors and annoyance levels start to increase, especially for repetitive tasks, and it becomes difficult to maintain a train of thought.

計算時間が 10 秒を超えると、ユーザーは待ちきれないので、応答が返るまで別の作業を行うようになります。For calculation times greater than 10 seconds, users become impatient and usually switch to other tasks while they wait. この段階になると、計算は一連のさまざまな作業の一部に取り込まれてしまい、ユーザーがその流れを誤ると、問題が発生することがあります。This can cause problems when the calculation is one of a sequence of tasks and the user loses track.

Excel での計算方法についてUnderstanding calculation methods in Excel

Excel の計算パフォーマンスを強化するには、使用できる計算方法と、その計算方法の制御方法について理解する必要があります。To improve the calculation performance in Excel, you must understand both the available calculation methods and how to control them.

全計算と再計算との依存関係Full calculation and recalculation dependencies

Excel の高機能再計算エンジンは、できるだけ短時間で計算できるように、各数式の参照元と依存関係 (数式から参照されるセルのこと) の両方と、前回の計算以降に加えられた変更をすべて継続的に追跡します。The smart recalculation engine in Excel tries to minimize calculation time by continuously tracking both the precedents and dependencies for each formula (the cells referenced by the formula) and any changes that were made since the last calculation. 次回の再計算時には、次の再計算のみが行われます。At the next recalculation, Excel recalculates only the following:

  • 変更された、または要再計算としてフラグが設定されたセル、数式、値、または名前。Cells, formulas, values, or names that have changed or are flagged as needing recalculation.

  • 再計算が必要なセル、数式、名前、または値に依存しているセル。Cells dependent on other cells, formulas, names, or values that need recalculation.

  • 揮発性関数と、表示される条件付き書式。Volatile functions and visible conditional formats.

既に計算済みのセルに依存するセルは、依存元のセルの値が変更されていなくても、引き続き計算されます。Excel continues calculating cells that depend on previously calculated cells even if the value of the previously calculated cell does not change when it is calculated.

前回の計算以降、何か変更があるとしても、それは入力データの一部であったり、ごく少数の数式であったりするのがほとんどなので、再計算にかかる時間は、すべての数式の全計算にかかる時間と比べると、ほんのわずかです。Because you change only part of the input data or a few formulas between calculations in most cases, this smart recalculation usually takes only a fraction of the time that a full calculation of all the formulas would take.

手動計算モードでは、F9 キーを押してこのスマート再計算をトリガーできます。In manual calculation mode, you can trigger this smart recalculation by pressing F9. Ctrl + Alt + F9 キーを押すと、すべての数式の全計算を強制的に実行できます。また、Shift + Ctrl + Alt + F9 キーを押すと、依存関係の完全な再構築と全計算を強制的に実行できます。You can force a full calculation of all the formulas by pressing Ctrl+Alt+F9, or you can force a complete rebuild of the dependencies and a full calculation by pressing Shift+Ctrl+Alt+F9.

計算プロセスCalculation process

他のセルを参照する Excel の数式を、参照先セルの前または後に配置できます (前方参照または後方参照)。Excel formulas that reference other cells can be put before or after the referenced cells (forward referencing or backward referencing). このような参照方法を Excel で使用できるのは、セルの計算順序が一定でない、つまり、セルは行または列ごとに計算されないからです。This is because Excel does not calculate cells in a fixed order, or by row or column. Excel での計算順序は、静的ではなく、計算するすべての数式のリスト (計算チェーン) と各数式の依存情報に基づいて、動的に決まります。Instead, Excel dynamically determines the calculation sequence based on a list of all the formulas to calculate (the calculation chain) and the dependency information about each formula.

Excel の計算フェーズは独自です。Excel has distinct calculation phases:

  1. 初期の計算チェーンを構築し、計算を開始する位置を決定します。Build the initial calculation chain and determine where to begin calculating. このフェーズは、ブックがメモリに読み込まれたときに発生します。This phase occurs when the workbook is loaded into memory.

  2. 依存関係を追跡し、未計算のセルにフラグを設定し、計算チェーンを更新します。Track dependencies, flag cells as uncalculated, and update the calculation chain. このフェーズは、セルに入力または変更があるたびに発生します (手動計算モードの場合も含む)。This phase executes at each cell entry or change, even in manual calculation mode. これは通常、気付かないほど高速で実行されますが、複雑なケースでは応答が低速になることがあります。Ordinarily this executes so fast that you do not notice it, but in complex cases, response can be slow.

  3. すべての数式を計算します。Calculate all formulas. 計算プロセスの一部として、計算チェーンの順序を変更して再構築し、今後の再計算を最適化します。As a part of the calculation process, Excel reorders and restructures the calculation chain to optimize future recalculations.

  4. Excel ウィンドウの表示されている部分を更新します。Update the visible parts of the Excel windows.

3 番目のフェーズは、計算または再計算の度に実行されます。The third phase executes at each calculation or recalculation. Excel は、計算チェーンの各数式を順に計算しようとしますが、1 つ以上の未計算の数式に依存する数式があると、その数式を後回しにして後で再度計算します。Excel tries to calculate each formula in the calculation chain in turn, but if a formula depends on one or more formulas that have not yet been calculated, the formula is sent down the chain to be calculated again later. つまり、数式は再計算の度に複数回計算されることがあります。This means that a formula can be calculated multiple times per recalculation.

通常、ブックの計算速度は 1 回目よりも 2 回目の方が大幅に向上します。The second time that you calculate a workbook is often significantly faster than the first time. それにはいくつかの理由があります。This occurs for several reasons:

  • 通常は、変更されたセルとその依存関係のみ再計算されます。Excel usually recalculates only cells that have changed, and their dependents.

  • 最新の計算順序を保管して再使用するので、計算順序を決める時間を大幅に節約できます。Excel stores and reuses the most recent calculation sequence so that it can save most of the time used to determine the calculation sequence.

  • 複数のコア コンポーネントを備えているため、Excel は、以前の計算結果に基づいて計算をコア全体に拡散する方法の最適化を試行します。With multiple core computers, Excel tries to optimize the way the calculations are spread across the cores based on the results of the previous calculation.

  • Excel のセッションでは、Windows と Excel の両方が、より迅速にアクセスできるように、最近使用したデータとプログラムをキャッシュします。In an Excel session, both Windows and Excel cache recently used data and programs for faster access.

ブック、ワークシート、および範囲の計算Calculating workbooks, worksheets, and ranges

別の Excel の計算方法を使用して、計算対象を制御することができます。You can control what is calculated by using the different Excel calculation methods.

開いているすべてのブックを計算するCalculate all open workbooks

再計算と全計算を行うたびに、現在開いているすべてのブックを計算し、ブックとワークシート内の依存関係と、ブックとワークシートの間の依存関係を解決し、以前に計算されていなかった (ダーティ) セルを計算済みとして再設定します。Each recalculation and full calculation calculates all the workbooks that are currently open, resolves any dependencies within and between workbooks and worksheets, and resets all previously uncalculated (dirty) cells as calculated.

選択しているワークシートを計算するCalculate selected worksheets

Shift + F9 キーを使用すると、選択しているワークシートのみ再計算できます。You can also recalculate only the selected worksheets by using Shift+F9. このとき、ワークシート間の依存関係は解決されず、ダーティ セルは計算済みとして再設定されませんThis does not resolve any dependencies between worksheets, and does not reset dirty cells as calculated.

セルの範囲を計算するCalculate a range of cells

Excel では、Visual Basic for Applications (VBA) のメソッド Range.CalculateRowMajorOrderRange.Calculate を使用して、セルの範囲を計算できます。Excel also allows for the calculation of a range of cells by using the Visual Basic for Applications (VBA) methods Range.CalculateRowMajorOrder and Range.Calculate:

  • Range.CalculateRowMajorOrder は左から右へ、上から下へ範囲を計算し、依存関係をすべて無視します。Range.CalculateRowMajorOrder calculates the range left to right and top to bottom, ignoring all dependencies.

  • Range.Calculate は範囲内のすべての依存関係を解決しながら、範囲を計算します。Range.Calculate calculates the range resolving all dependencies within the range.

CalculateRowMajorOrder は計算範囲内にある依存関係をいずれも解決しないので、通常、Range.Calculate より大幅に高速になります。Because CalculateRowMajorOrder does not resolve any dependencies within the range that is being calculated, it is usually significantly faster than Range.Calculate. ただし、Range.Calculate とは同じ結果が得られない場合があるため、使用には注意が必要です。However, it should be used with care because it may not give the same results as Range.Calculate.

Range.Calculate は、Excel の中で最も便利なパフォーマンス最適化ツールの 1 つで、さまざまな数式の計算速度を記録して比較できます。Range.Calculate is one of the most useful tools in Excel for performance optimization because you can use it to time and compare the calculation speed of different formulas.

詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」を参照してください。For more information, see Excel performance: Performance and limit improvements.

揮発性関数Volatile functions

揮発性関数は、参照元が変更されていないように思われる場合でも、再計算のたびに必ず再計算されます。A volatile function is always recalculated at each recalculation even if it does not seem to have any changed precedents. 揮発性関数を多用すると、再計算のたびに計算速度が低下しますが、全計算には影響しません。Using many volatile functions slows down each recalculation, but it makes no difference to a full calculation. Application.Volatile を関数コードに含めることによって、ユーザー定義の関数を揮発性にすることができます。You can make a user-defined function volatile by including Application.Volatile in the function code.

Excel の組み込み関数の中でも、RAND()NOW()TODAY() は、明らかに揮発性関数です。Some of the built-in functions in Excel are obviously volatile: RAND(), NOW(), TODAY(). 揮発性関数である度合いが低い他の関数には、OFFSET()CELL()INDIRECT()INFO() があります。Others are less obviously volatile: OFFSET(), CELL(), INDIRECT(), INFO().

以前に揮発性関数として文書化されていたいくつかの関数、INDEX()ROWS()COLUMNS()AREAS() は実際には揮発性ではありません。Some functions that have previously been documented as volatile are not in fact volatile: INDEX(), ROWS(), COLUMNS(), AREAS().

揮発性操作Volatile actions

揮発性操作とは、再計算を呼び出す操作であり、次のものが含まれます。Volatile actions are actions that trigger a recalculation, and include the following:

  • 自動モードで、行または列の分割線をクリックする。Clicking a row or column divider when in automatic mode.
  • シートに、行、列、またはセルを挿入または削除する。Inserting or deleting rows, columns, or cells on a sheet.
  • 定義名を追加、変更、または削除する。Adding, changing, or deleting defined names.
  • 自動モードで、ワークシートの名前を変更、またはワークシートの位置を変更する。Renaming worksheets or changing worksheet position when in automatic mode.
  • 行をフィルタリングする、非表示にする、再表示する。Filtering, hiding, or un-hiding rows.
  • 自動モードになっているときにブックを開く。Opening a workbook when in automatic mode. 前回、ブックが別のバージョンの Excel で計算されている場合は、通常、ブックを開くと全計算が実行されます。If the workbook was last calculated by a different version of Excel, opening the workbook usually results in a full calculation.
  • [保存前に計算] オプションが選択されている場合は、手動モードでブックを保存します。Saving a workbook in manual mode if the Calculate before Save option is selected.

数式と名前が評価される場合Formula and name evaluation circumstances

次のいずれかの操作を行うと、数式または数式の一部がすぐに評価 (計算) されます (手動計算モードの場合を含む)。A formula or part of a formula is immediately evaluated (calculated), even in manual calculation mode, when you do one of the following:

  • 数式を入力または編集する。Enter or edit the formula.
  • 関数ウィザードを使用して数式を入力または編集する。Enter or edit the formula by using the Function Wizard.
  • 関数ウィザードで、数式を引数として入力する。Enter the formula as an argument in the Function Wizard.
  • 数式バーから数式を選択して F9 キーを押すか (操作を元に戻して数式に戻るには Esc キーを押す)、[数式の検証] をクリックする。Select the formula in the formula bar and press F9 (press Esc to undo and revert to the formula), or click Evaluate Formula.

数式の参照先 (依存先) セルまたは数式が、次のいずれかの条件を満たしていると、参照元の数式には未計算のフラグが設定されます。A formula is flagged as uncalculated when it refers to (depends on) a cell or formula that has one of these conditions:

  • セルまたは数式が入力された。It was entered.
  • セルまたは数式が変更された。It was changed.
  • セルまたは数式がオートフィルター リストに含まれ、条件ドロップダウン リストが有効になっている。It is in an AutoFilter list, and the criteria drop-down list was enabled.
  • 未計算のフラグが設定されている。It is flagged as uncalculated.

未計算のフラグが設定された数式は、その未計算の数式を含むワークシート、ブック、または Excel インスタンスの計算時または再計算時に評価されます。A formula that is flagged as uncalculated is evaluated when the worksheet, workbook, or Excel instance that contains it is calculated or recalculated.

定義名の評価が発生する条件は、セル内の数式の評価が発生する条件とは異なります。The circumstances that cause a defined name to be evaluated differ from those for a formula in a cell:

  • 定義名の評価は、その定義名を参照する数式が評価されるたびに行われます。したがって、1 つの名前を複数の数式で使用していると、その名前が何回も評価されることがあります。A defined name is evaluated every time that a formula that refers to it is evaluated so that using a name in multiple formulas can cause the name to be evaluated multiple times.
  • どの数式からも参照されていない名前は、全計算の対象からも外れます。Names that are not referred to by any formula are not calculated even by a full calculation.

データ テーブルData tables

Excel のデータ テーブル ([データ] タブ > [データ ツール] グループ > [What-If 分析] > [データ テーブル]) は、テーブル機能 ([ホーム] タブ > [スタイル] グループ > [テーブルとして書式設定]、または [挿入] タブ > [テーブル] グループ > [テーブル]) と混同すべきではありません。Excel data tables (Data tab > Data Tools group > What-If Analysis > Data Table) should not be confused with the table feature (Home tab > Styles group > Format as Table, or, Insert tab > Tables group > Table). Excel のデータ テーブルは、ブックについて複数の再計算を実行するもので、それぞれの計算はテーブル内のさまざまな値によって駆動されます。Excel data tables do multiple recalculations of the workbook, each driven by the different values in the table. Excel は最初に、ブックを通常どおり計算します。Excel first calculates the workbook normally. 次に、行と列の値のペアごとに、値を置き換え、シングル スレッドの再計算を実行し、結果をデータ テーブルに保存します。For each pair of row and column values, it then substitutes the values, does a single-threaded recalculation, and stores the results in the data table.

データ テーブルの再計算では、常に 1 つのプロセッサのみが使用されます。Data table recalculation always uses only a single processor.

データ テーブルは、複数のバリエーションを計算し、バリエーションの結果を表示して比較するための便利な方法を提供します。Data tables give you a convenient way to calculate multiple variations and view and compare the results of the variations. [テーブル以外自動] の計算オプションを使用すると、各計算で複数の計算が自動でトリガーされないように、ただしテーブルを除くすべての依存している数式は計算するように Excel を設定できます。Use the Automatic except Tables calculation option to stop Excel from automatically triggering the multiple calculations at each calculation, but still calculate all dependent formulas except tables.

計算オプションの制御Controlling calculation options

Excel には、計算方法を制御する一連のオプションがあります。Excel has a range of options that enable you to control the way it calculates. Excel で使用頻度が最も高いオプションは、リボンの [数式] タブの [計算] グループで変更できます。You can change the most frequently used options in Excel by using the Calculation group on the Formulas tab on the Ribbon.

図 1. [数式] タブの [計算] グループFigure 1. Calculation group on the Formulas tab

[数式] タブの計算オプション

Excel の計算オプションをさらに確認するには、[ファイル] タブで [オプション] をクリックします。To see more Excel calculation options, on the File tab, click Options. [Excel のオプション] ダイアログ ボックスで [数式] タブをクリックします。In the Excel Options dialog box, click the Formulas tab.

図 2. Excel の意オプションの [数式] タブにある計算オプションFigure 2. Calculation options on the Formulas tab in Excel Options

Backstage ビューの計算オプション

多数の計算オプションがあります ([自動]、[データ テーブル以外自動]、[手動]、[ブックの保存前に再計算を行う])。反復設定 ([反復計算を行う]、[最大反復回数]、[変化の最大値]) もあります。これらの計算オプションと反復設定は、ブック レベルではなく、アプリケーション レベルで機能します (これらは、開いているブックにすべて同様に適用されます)。Many calculation options (Automatic, Automatic except for data tables, Manual, Recalculate workbook before saving) and the iteration settings (Enable iterative calculation, Maximum Iterations, Maximum Change) operate at the application level instead of at the workbook level (they are the same for all open workbooks).

高度な計算オプションを見つけるには、[ファイル] タブで [オプション] をクリックします。To find advanced calculation options, on the File tab, click Options. [Excel のオプション] ダイアログ ボックスで、[詳細設定] をクリックします。In the Excel Options dialog box, click Advanced. [数式] セクションで計算オプションを設定します。Under the Formulas section, set calculation options.

図 3. 高度な計算オプションFigure 3. Advanced calculation options

Backstage ビューの高度な計算オプション

Excel を起動するとき、またはどのブックも開いていない状態で Excel が実行されているときには、最初の計算モードと反復計算の設定は、最初に開く非テンプレート、非アドインのブックにより決まります。When you start Excel, or when it is running without any workbooks open, the initial calculation mode and iteration settings are set from the first non-template, non-add-in workbook that you open. つまり、後で開かれたブックの計算設定は無視されます。もちろん、いつでも Excel の設定は手動で変更できます。This means that the calculation settings in workbooks opened later are ignored, although, of course, you can manually change the settings in Excel at any time. ブックを保存すると、現在の計算設定がブックに保存されます。When you save a workbook, the current calculation settings are stored in the workbook.

自動計算Automatic calculation

自動計算モードでは、開いているブックで変更がある度に、および、ブックを開いたときに、Excel によって自動的に再計算が行われます。Automatic calculation mode means that Excel automatically recalculates all open workbooks at every change and when you open a workbook. 通常、自動モードでブックを開き、Excel によって再計算が行われるときには、再計算が見えることはありません。ブックが保存されてから何も変更されていないためです。Usually when you open a workbook in automatic mode and Excel recalculates, you do not see the recalculation because nothing has changed since the workbook was saved.

ブックが前回最後に計算されたときに使用したバージョンより新しいバージョンの Excel でブックを開いた場合に、この計算に気づく可能性があります (例: Excel 2016 と Excel 2013)。You might notice this calculation when you open a workbook in a later version of Excel than you used the last time that the workbook was calculated (for example, Excel 2016 versus Excel 2013). Excel の計算エンジンが異なるため、Excel は以前のバージョンを使用して保存されたブックを開くときに、フル計算を実行します。Because the Excel calculation engines are different, Excel performs a full calculation when it opens a workbook that was saved using an earlier version of Excel.

手動計算Manual calculation

手動計算モードでは、F9 キーまたは Ctrl + Alt + F9 キーを押すことで要求したとき、あるいはブックを保存するときに、開いているすべてのブックが Excel によって再計算されます。Manual calculation mode means that Excel recalculates all open workbooks only when you request it by pressing F9 or Ctrl+Alt+F9, or when you save a workbook. 再計算が一瞬では完了しないブックの場合は、変更を加えたときの遅延を避けるために、計算を手動モードに設定する必要があります。For workbooks that take more than a fraction of a second to recalculate, you must set calculation to manual mode to avoid a delay when you make changes.

手動モードのブックを再計算する必要がある場合には、ステータス バーに [計算] と表示されます。Excel tells you when a workbook in manual mode needs recalculation by displaying Calculate in the status bar. ブックに循環参照が含まれていて、反復オプションが選択されている場合にも、ステータス バーに [計算] と表示されます。The status bar also displays Calculate if your workbook contains circular references and the iteration option is selected.

反復設定Iteration settings

ブックに循環参照を意図的に含めている場合は、反復設定を使用して、ブックの最大再計算 (反復) 回数と収束条件 (変化の最大値: 停止するタイミング) を制御できます。If you have intentional circular references in your workbook, the iteration settings enable you to control the maximum number of times the workbook is recalculated (iterations) and the convergence criteria (maximum change: when to stop). 反復ボックスをオフにしておくと、循環参照が誤って含まれている場合に、そのことが警告されるだけで、解決は試行されません。Clear the iteration box so that if you have accidental circular references, Excel will warn you and will not try to solve them.

ブックの ForceFullCalculation プロパティWorkbook ForceFullCalculation property

このブックのプロパティを True に設定すると、Excel のスマート再計算がオフになり、再計算の度に、開いているすべてのブック内のすべての数式が再計算されるようになります。When you set this workbook property to True, Excel's Smart Recalculation is turned off and every recalculation recalculates all the formulas in all the open workbooks. 一部の複雑なブックでは、スマート再計算に必要な依存関係ツリーの構築と管理にかかる時間のほうが、スマート再計算によって節約される時間よりも長くなります。For some complex workbooks, the time taken to build and maintain the dependency trees needed for Smart Recalculation is larger than the time saved by Smart Recalculation.

手動モードにしても、ブックを開くのに非常に長い時間がかかる、または、小さな変更を加えるだけでも長い時間がかかる場合は、ForceFullCalculation を試してみるとよいでしょう。If your workbook takes an excessively long time to open, or making small changes takes a long time even in manual calculation mode, it may be worth trying ForceFullCalculation.

ブックの ForceFullCalculation プロパティが True に設定されている場合、ステータス バーに [計算] と表示されます。Calculate will appear in the status bar if the workbook ForceFullCalculation property has been set to True.

この設定は、VBE を使用し (Alt + F11)、ThisWorkbookプロジェクト エクスプローラー で選択し (Ctrl+R)、プロパティ ウィンドウ を表示する (F4) ことで制御できます。You can control this setting using the VBE (Alt+F11), selecting ThisWorkbook in the Project Explorer (Ctrl+R) and showing the Properties Window (F4).

図 4. Workbook.ForceFullCalculation プロパティの設定Figure 4. Setting the Workbook.ForceFullCalculation property

ForceFullCalculation の設定

ブックの計算の高速化Making workbooks calculate faster

ブックの計算を高速化するには、次の手順と方法を使用します。Use the following steps and methods to make your workbooks calculate faster.

プロセッサの速度と複数のコアProcessor speed and multiple cores

ほとんどのバージョンの Excel で、プロセッサが高速になれば、それだけ Excel の計算も高速になります。For most versions of Excel, a faster processor will, of course, enable faster Excel calculation. Excel 2007 で導入されたマルチスレッド計算エンジンによって、Excel でマルチプロセッサ システムを存分に利用できるようになり、ほとんどのブックで大幅なパフォーマンスの向上を期待できます。The multithreaded calculation engine introduced in Excel 2007 enables Excel to make excellent use of multiprocessor systems, and you can expect significant performance gains with most workbooks.

ほとんどの大きなブックでは、計算のパフォーマンスは、物理プロセッサの数とほぼ比例して複数のプロセッサ スケールにより向上します。For most large workbooks, the calculation performance gains from multiple processors scale almost linearly with the number of physical processors. しかしながら、物理プロセッサのハイパースレッドによるパフォーマンス向上はわずかなものです。However, hyper-threading of physical processors only produces a small performance gain.

詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」を参照してください。For more information, see Excel performance: Performance and limit improvements.

RAMRAM

仮想メモリ ページング ファイルへのページングは、低速です。Paging to a virtual-memory paging file is slow. オペレーティング システム、Excel、およびブックのために使用できる物理 RAM を十分に確保する必要があります。You must have enough physical RAM for the operating system, Excel, and your workbooks. 計算中も、ある程度定期的にハード ディスク アクティビティがあり、ディスク アクティビティを呼び出すユーザー定義関数を実行しない場合は、より多くの RAM が必要です。If you have more than occasional hard disk activity during calculation, and you are not running user-defined functions that trigger disk activity, you need more RAM.

前にも説明したように、最近のバージョンの Excel は、大量のメモリを効果的に使用するようになっており、Excel 2007 と Excel 2010 の 32 ビット版では、単一のブックや複数のブックの組み合わせの処理に、最大 2 GB のメモリを使用することがあります。As mentioned, recent versions of Excel can make effective use of large amounts of memory, and the 32-bit version of Excel 2007 and Excel 2010 can handle a single workbook or a combination of workbooks using up to 2 GB of memory.

Large Address Aware (LAA) 機能を使用する Excel 2013 と Excel 2016 の 32 ビット版は、インストールされている Windows のバージョンによっては、最大 3 または 4 GB のメモリを使用することがあります。The 32-bit versions of Excel 2013 and Excel 2016 that use the Large Address Aware (LAA) feature can use up to 3 or 4 GB of memory, depending on the version of Windows installed. 64 ビット版の Excel は、より大きなブックを処理できます。The 64-bit version of Excel can handle larger workbooks. 詳細については、「Excel のパフォーマンス: パフォーマンスの向上と制限の改善」のセクション「大規模データ セット、LAA、64 ビット版の Excel」を参照してください。For more information, see the "Large data sets, LAA, and 64-bit Excel" section in Excel performance: Performance and limit improvements.

計算を効率化するためのガイドラインとして、同時に開く必要があるブックの最大セットに対応できるだけの十分な RAM が必要です。また、Excel とオペレーティング システム用に 1 GB または 2 GB の RAM と、その他の実行アプリケーション用の RAM も必要です。A rough guideline for efficient calculation is to have enough RAM to hold the largest set of workbooks that you need to have open at the same time, plus 1 to 2 GB for Excel and the operating system, plus additional RAM for any other running applications.

計算時間の測定Measuring calculation time

ブックの計算を速くするためには、計算時間を正確に測定できる必要があります。To make workbooks calculate faster, you must be able to accurately measure calculation time. VBA の Time 関数よりも高速で正確なタイマーが必要になります。You need a timer that is faster and more accurate than the VBA Time function. 次のコード例に示す MICROTIMER() 関数では、システムの高精度タイマーへの Windows API 呼び出しを使用しています。The MICROTIMER() function shown in the following code example uses Windows API calls to the system high-resolution timer. このタイマーはミリ秒単位で時間を測定できます。It can measure time intervals down to small numbers of microseconds. ただし、Windows はマルチタスク オペレーティング システムであること、および 1 回目よりも 2 回目の計算時間の方が短くなる可能性があるという理由から、正確な時間を繰り返し取得できるとは限りません。Be aware that because Windows is a multitasking operating system, and because the second time that you calculate something, it may be faster than the first time, the times that you get usually do not repeat exactly. できるだけ厳密に時間を把握したい場合は、計算時間を複数回測定して平均値を求めます。To achieve the best accuracy, measure time calculation tasks several times and average the results.

Visual Basic エディターが VBA ユーザー定義関数のパフォーマンスに大幅に影響する可能性があることについての詳細は、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」のセクション「より高速な VBA ユーザー定義関数」を参照してください。For more information about how the Visual Basic Editor can significantly affect VBA user-defined function performance, see the "Faster VBA user-defined functions" section in Excel performance: Tips for optimizing performance obstructions.

#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

計算時間を測定するには、適切な計算方法を呼び出す必要があります。To measure calculation time, you must call the appropriate calculation method. 次のサブルーチンでは、範囲の計算時間、ワークシートまたは開いているすべてのブックの再計算時間、開いているすべてのブックの全計算時間を測定できます。These subroutines give you calculation time for a range, recalculation time for a sheet or all open workbooks, or full calculation time for all open workbooks.

これらのサブルーチンと関数を標準 VBA モジュールにすべてコピーしてください。Copy all these subroutines and functions into a standard VBA module. VBA エディターを開くには、Alt + F11 キーを押します。To open the VBA editor, press Alt+F11. [挿入] メニューで、[モジュール] を選択し、モジュールにコードをコピーします。On the Insert menu, select Module, and then copy the code into the module.

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 キーを押します。To run the subroutines in Excel, press Alt+F8. 目的のサブルーチンを選択し、[実行] をクリックします。Select the subroutine you want, and then click Run.

図 5. 計算タイマーを表示している Excel マクロ ウィンドウFigure 5. The Excel Macro window showing the calculation timers

Excel のマクロ ウィンドウ

計算上の問題点の特定と優先順位付けFinding and prioritizing calculation obstructions

計算がどんなに遅くても、計算時間の大半を費やしている問題や障害が存在するのは 2 - 3 箇所にすぎません。 Most slow-calculating workbooks have only a few problem areas or obstructions that consume most of the calculation time. 問題点を未特定の場合は、このセクションで説明するドリルダウン アプローチを使用して特定します。If you do not already know where they are, use the drill-down approach outlined in this section to find them. 既に特定している場合は、それぞれの問題点にどれだけの計算時間が費やされているかを個別に測定し、問題解決のために行う作業に優先順位を付けます。If you do know where they are, you must measure the calculation time that is used by each obstruction so that you can prioritize your work to remove them.

問題点を特定するドリルダウン アプローチDrill-down approach to finding obstructions

ドリルダウン アプローチは、ブックの計算時間を測定することから始めます。次に、各ワークシートの計算時間を測定し、計算が遅いワークシートがあれば、その数式ブロックの計算時間を測定します。The drill-down approach starts by timing the calculation of the workbook, the calculation of each worksheet, and the blocks of formulas on slow-calculating sheets. 各手順を順序どおりに行って、計算時間を記録します。Do each step in order and note the calculation times.

ドリルダウン アプローチによって問題点を特定するにはTo find obstructions using the drill-down approach

  1. ブックを 1 つだけ開き、他のタスクは一切実行されていないことを確認します。Ensure that you have only one workbook open and no other tasks are running.

  2. 計算を手動に設定します。Set calculation to manual.

  3. ブックのバックアップ コピーを作成します。Make a backup copy of the workbook.

  4. 計算時間マクロを含むブックを開くか、そのマクロをブックに追加します。Open the workbook that contains the Calculation Timers macros, or add them to the workbook.

  5. 各ワークシートで、順に Ctrl + End キーを押して、使用範囲を調べます。Check the used range by pressing Ctrl+End on each worksheet in turn.

    この操作によって、最後に使用したセルの場所がわかります。This shows where the last used cell is. 予想範囲を越えた場所に、最後に使用したセルがある場合は、余分な列と行を削除してからブックを保存することを検討します。If this is beyond where you expect it to be, consider deleting the excess columns and rows and saving the workbook. 詳細については、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」のセクション「使用されている範囲の最小化」を参照してください。For more information, see the "Minimizing the used range" section in Excel performance: Tips for optimizing performance obstructions.

  6. FullCalcTimer マクロを実行します。Run the FullCalcTimer macro.

    ブック内のすべての数式を計算する時間が、通常は最も長くかかるケースとなります。The time to calculate all the formulas in the workbook is usually the worst-case time.

  7. RecalcTimer マクロを実行します。Run the RecalcTimer macro.

    全計算の直後に再計算すると、通常は、ベストケースの計算時間になります。A recalculation immediately after a full calculation usually gives you the best-case time.

  8. ブックの揮発性を、再計算時間対全計算時間の比として計算します。Calculate workbook volatility as the ratio of recalculation time to full calculation time.

    この比は、数式の揮発性を表し、計算チェーンの評価が問題になっていないかどうかを判定します。This measures the extent to which volatile formulas and the evaluation of the calculation chain are obstructions.

  9. 各シートをアクティブにして、順番に SheetTimer マクロを実行します。Activate each sheet and run the SheetTimer macro in turn.

    ブックを再計算した直後なので、各ワークシートの再計算時間が測定されます。Because you just recalculated the workbook, this gives you the recalculate time for each worksheet. これで、どのワークシートに問題があるかを特定できる必要があります。This should enable you to determine which ones are the problem worksheets.

  10. 選択された数式のブロックに対して RangeTimer マクロを実行します。Run the RangeTimer macro on selected blocks of formulas.

  11. 問題のあるそれぞれのワークシートについて、行または列を分けていくつかの小さいブロックにします。For each problem worksheet, divide the columns or rows into a small number of blocks.

  12. 各ブロックを順番に選択して、ブロックに対して RangeTimer マクロを実行します。Select each block in turn, and then run the RangeTimer macro on the block.

  13. 必要に応じて、各ブロックをさらに小さく分割して、より深くドリルダウンします。If necessary, drill down further by subdividing each block into a smaller number of blocks.

  14. 問題点に優先順位を付けます。Prioritize the obstructions.


計算速度の向上と問題の軽減Speeding up calculations and reducing obstructions

計算時間が長くなる要因は、数式の数やブックのサイズではありません。It is not the number of formulas or the size of a workbook that consumes the calculation time. 関係するのは、セル参照と計算操作の数、および使用されている関数の効率性です。It is the number of cell references and calculation operations, and the efficiency of the functions being used.

ほとんどのワークシートは、絶対参照と相対参照を含む数式をコピーして構成されているため、一般的には、反復または重複する計算と参照を含む大量の数式がワークシートには含まれています。Because most worksheets are constructed by copying formulas that contain a mixture of absolute and relative references, they usually contain a large number of formulas that contain repeated or duplicated calculations and references.

複雑な大量の数式や配列数式を使用しないでください。Avoid complex mega-formulas and array formulas. 一般的には、行と列を増やして、複雑な計算を減らすようにします。In general, it is better to have more rows and columns and fewer complex calculations. この方針に従うと、Excel の高機能再計算とマルチスレッド計算を必要に応じてより適切に最適化できます。This gives both the smart recalculation and the multithreaded calculation in Excel a better opportunity to optimize the calculations. また、理解しやすいのでデバッグも簡単です。It is also easier to understand and debug. 次に、ブックの計算速度を改善するいくつかの規則を示します。The following are a few rules to help you speed up workbook calculations.

規則その 1: 重複、反復、および不要な計算をなくすFirst rule: Remove duplicated, repeated, and unnecessary calculations

重複、反復、および不要な計算がないかどうかを調べ、そうした問題を抱えた状態で結果を計算するために、現在 Excel で必要としているセルの参照数と計算回数を概算します。Look for duplicated, repeated, and unnecessary calculations, and figure out approximately how many cell references and calculations are required for Excel to calculate the result for this obstruction. それよりも少ない参照数と計算回数で同じ結果を得る方法を考えます。Think how you might obtain the same result with fewer references and calculations.

通常、この作業には、次の 1 つ以上の手順が必要です。Usually this involves one or more of the following steps:

  • 各数式での参照数を減らします。Reduce the number of references in each formula.

  • 反復する計算を 1 つ以上の補助的なセルに移動し、そのセルを元の数式から参照します。Move the repeated calculations to one or more helper cells, and then reference the helper cells from the original formulas.

  • 計算の中間結果を一時的に格納する行や列を追加して、それらの結果を別の数式で再使用できるようにします。Use additional rows and columns to calculate and store intermediate results once so that you can reuse them in other formulas.

規則その 2: できるだけ効率的な関数を使用するSecond rule: Use the most efficient function possible

問題に関数や配列数式が関係している場合は、より効率的な方法で同じ結果を得ることができないかどうかを考えます。When you find an obstruction that involves a function or array formulas, determine whether there is a more efficient way to achieve the same result. 次に例を示します。For example:

  • ルックアップの前にデータを並べ替えておくと、並べ替えていないデータをルックアップする場合よりも、効率が数十倍または数百倍向上することがあります。Lookups on sorted data can be tens or hundreds of times more efficient than lookups on unsorted data.

  • VBA ユーザー定義関数は、通常、Excel の組み込み関数よりも低速です (ただし、慎重に記述されていれば高速です)。VBA user-defined functions are usually slower than the built-in functions in Excel (although carefully written VBA functions can be fast).

  • SUMSUMIF などの関数で使用されているセルの数を最小限に抑えます。Minimize the number of used cells in functions like SUM and SUMIF. 計算時間はセルの使用数に比例します (未使用のセルは無視されます)。Calculation time is proportional to the number of used cells (unused cells are ignored).

  • 低速の配列数式をユーザー定義関数に置き換えることを検討します。Consider replacing slow array formulas with user-defined functions.

規則その 3: スマート再計算とマルチスレッド計算を効果的に使用するThird rule: Make good use of smart recalculation and multithreaded calculation

Excel のスマート再計算とマルチスレッド計算をより適切に利用すれば、それだけ少ない処理で毎回の再計算を実行できます。そこで、次のことを実践します。The better use you make of smart recalculation and multithreaded calculation in Excel, the less processing has to be done every time that Excel recalculates, so:

  • 他の関数よりも大幅に効率的でない限り、INDIRECTOFFSET などの揮発性関数は使用しないでください。Avoid volatile functions such as INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (適切に設計されていれば OFFSET は一般的に高速です。)(Well-designed use of OFFSET is often fast.)

  • 配列数式や関数で使用する範囲のサイズを最小にします。Minimize the size of the ranges that you are using in array formulas and functions.

  • 配列数式やメガ関数は、別の補助的な列や行に分割します。Break array formulas and mega-formulas out into separate helper columns and rows.

  • シングルスレッドの関数を使用しないでください。Avoid single-threaded functions:

    • PHONETICPHONETIC
    • CELL ("format" or "address" のどちらかの引数が使用された場合)CELL when either the "format" or "address" argument is used
    • INDIRECTINDIRECT
    • GETPIVOTDATAGETPIVOTDATA
    • CUBEMEMBERCUBEMEMBER
    • CUBEVALUECUBEVALUE
    • CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY
    • CUBESETCUBESET
    • CUBERANKEDMEMBERCUBERANKEDMEMBER
    • CUBEKPIMEMBERCUBEKPIMEMBER
    • CUBESETCOUNTCUBESETCOUNT
    • ADDRESS (5 番目のパラメーター "sheet_name" が指定されている場合)ADDRESS where the fifth parameter (the sheet_name) is given
    • ピボットテーブルを参照するデータベース関数 (DSUM、DAVERAGE など)Any database function (DSUM, DAVERAGE, and so on) that refers to a pivot table
    • ERROR.TYPEERROR.TYPE
    • HYPERLINKHYPERLINK
    • VBA および COM アドインのユーザー定義関数VBA and COM add-in user-defined functions
  • データ テーブルと循環参照の反復的な使用を避ける: これらはどちらも、常にシングルスレッドで計算されます。Avoid iterative use of data tables and circular references: both of these always calculate single-threaded.

規則その 4: 変更を行うたびに時間の測定とテストを行うFourth rule: Time and test each change

自分が行った変更によって、思いどおりの答えが得られなかったり、計算が予想以上に低速になったりすると、そのことに自分自身が驚いてしまうことがあります。Some of the changes that you make might surprise you, either by not giving the answer that you thought they would, or by calculating more slowly than you expected. したがって、次に示すように、変更を行うたびに時間を測定し、変更結果をテストする必要があります。Therefore, you should time and test each change, as follows:

  1. 変更したい数式を、RangeTimer マクロを使用して時間計測します。Time the formula that you want to change by using the RangeTimer macro.

  2. 変更を加えます。Make the change.

  3. 変更した数式を、RangeTimer マクロを使用して時間計測します。Time the changed formula by using the RangeTimer macro.

  4. 変更された数式の結果が引き続き正しいことを確認します。Check that the changed formula still gives the correct answer.

規則例Rule examples

以下のセクションでは、規則を使用して計算を速くする方法の例を示します。The following sections provide examples of how to use the rules to speed up calculation.

期間の初めから当日までの合計Period-to-date sums

たとえば、2,000 個の数字を含む列で、期間の初めから当日までの合計を求める必要があるとします。For example, you need to calculate the period-to-date sums of a column that contains 2,000 numbers. 列 A に数値が含まれていて、列 B と列 C に期間の初めから当日までの合計を含める必要があるとします。Assume that column A contains the numbers, and that column B and column C should contain the period-to-date totals.

効率的な関数である SUM を使用して、数式を作成できます。You could write the formula using SUM, which is an efficient function.

  B1=SUM($A$1:$A1)
  B2=SUM($A$1:$A2)

図 6. 期間の初めから当日までの SUM 数式の例Figure 6. Example of period-to-date SUM formulas

期間の初めから当日までの SUM 数式の例

この数式を B2000 までコピーします。Copy the formula down to B2000.

合計でいくつのセル参照が SUM によって追加されているでしょうか。How many cell references are added up by SUM in total? B1 が 1 つのセルを参照し、B2000 が 2,000 個のセルを参照します。B1 refers to one cell, and B2000 refers to 2,000 cells. 平均は、セルごとに 1,000 個の参照となるので、参照の合計数は 2 百万となります。The average is 1,000 references per cell, so the total number of references is 2 million. 2,000 の数式を選択して RangeTimer マクロを使用すると、列 B の 2,000 の数式が 80 ミリ秒で計算されるということが示されます。Selecting the 2,000 formulas and using the RangeTimer macro shows you that the 2,000 formulas in column B calculate in 80 milliseconds. このような計算のほとんどは、何度も重複しています。つまり、SUM はB2:B2000 にあるそれぞれの数式で A1 を A2 に加算しています。Most of these calculations are duplicated many times: SUM adds A1 to A2 in each formula from B2:B2000.

この重複は、数式を次のように作成することでなくすことができます。You can eliminate this duplication if you write the formulas as follows.

  C1=A1
  C2=C1+A1

この数式を C2000 までコピーします。Copy this formula down to C2000.

この場合、合計でいくつのセル参照が加算されているでしょうか。Now how many cell references are added up in total? 最初の数式を除き、各数式では、2 つのセル参照を使用しています。Each formula, except the first formula, uses two cell references. したがって、合計は 1999*2+1=3999 となります。Therefore, the total is 1999*2+1=3999. これで、セル参照の数は 500 分の 1 になります。This is a factor of 500 fewer cell references.

RangeTimer の結果、列 C の 2,000 個の数式の計算時間は 3.7 ミリ秒でした (列 B の計算時間は 80 ミリ秒でした)。この変更により、1 数式あたりのオーバーヘッドが小さくなるので、パフォーマンス向上係数は、500 ではなく、80/3.7=22 になります。RangeTimer indicates that the 2,000 formulas in column C calculate in 3.7 milliseconds compared to the 80 milliseconds for column B. This change has a performance improvement factor of only 80/3.7=22 instead of 500 because there is a small overhead per formula.

エラー処理Error handling

計算処理を大量に含む数式で、エラー発生時 (完全一致ルックアップの実行時に頻繁に発生) の結果をゼロとして表示したい場合は、エラー処理を次のいくつかの方法で記述できます。If you have a calculation-intensive formula where you want the result to be shown as zero if there is an error (this frequently occurs with exact match lookups), you can write this in several ways.

  • 単一の数式で記述できます。ただし、処理は低速です。You can write it as a single formula, which is slow:

    B1=IF(ISERROR(time expensive formula),0,time expensive formula)

  • 2 つの数式で記述できます。処理は高速です。You can write it as two formulas, which is fast:

    A1=time expensive formula

    B1=IF(ISERROR(A1),0,A1)

  • または、IFERROR 関数を使用できます。この関数は高速かつシンプルで、単一の数式です。Or you can use the IFERROR function, which is designed to be fast and simple, and is a single formula:

    B1=IFERROR(time expensive formula,0)


[一意にカウント] の動的な実行Dynamic count unique

図 7. [一意にカウント] のデータのリストの例Figure 7. Example list of data for count unique

[一意にカウント] のデータの例

たとえば、列 A に 11,000 行から成るデータのリストがあり、頻繁に変更が発生し、リスト内にある一意の項目 (空白は除く) の項目数を動的に計算する数式が必要であるとします。この場合、次のようないくつかの方法が考えられます。If you have a list of 11,000 rows of data in column A, which frequently changes, and you need a formula that dynamically calculates the number of unique items in the list, ignoring blanks, following are several possible solutions.

  • 配列数式 (Ctrl + Shift + Enter を使用)。RangeTimer では、この場合に 13.8 秒かかることが示されています。Array formulas (use Ctrl+Shift+Enter); RangeTimer indicates that this takes 13.8 seconds.

    {=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}
    
  • SUMPRODUCT は通常、同等の配列数式よりも高速で計算されます。SUMPRODUCT usually calculates faster than an equivalent array formula. この数式の計算には 10.0 秒かかります。したがって、パフォーマンス向上率は 13.8/10.0=1.38 で、パフォーマンスは向上していますが、これではまだ不十分です。This formula takes 10.0 seconds, and gives an improvement factor of 13.8/10.0=1.38, which is better, but not good enough.

    =SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&amp;""))
    
  • ユーザー定義関数User-defined functions. 次のコード例では、コレクションへのインデックスが一意でなければならないことを利用した VBA ユーザー定義関数を示しています。The following code example shows a VBA user-defined function that uses the fact that the index to a collection must be unique. 使用されているいくつかの手法の詳細については、「Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント」でユーザー定義関数について説明しているセクション「関数を効率的に使用する」を参照してください。For an explanation of some techniques that are used, see the section about user-defined functions in the "Using functions efficiently" section in Excel performance: Tips for optimizing performance obstructions. この数式 =COUNTU(A2:A11000) では、かかるのはわずか 0.061 秒です。This formula, =COUNTU(A2:A11000), takes only 0.061 seconds. この場合の向上率は 13.8/0.061=226 となります。This gives an improvement factor of 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
    
  • 数式の列の追加Adding a column of formulas. 前のサンプル データを見ると、並べ替えられていることがわかります (Excel では、11,000 行を並べ替えるには 0.5 秒かかります)。If you look at the previous sample of the data, you can see that it is sorted (Excel takes 0.5 seconds to sort the 11,000 rows). このことを利用して、この行のデータが前の行のデータと同じかどうかをチェックする数式の列を追加することができます。You can exploit this by adding a column of formulas that checks if the data in this row is the same as the data in the previous row. 異なる場合は、数式は 1 を返します。If it is different, the formula returns 1. それ以外の場合は、0 を返します。Otherwise, it returns 0.

    この数式をセル B2 に追加します。Add this formula to cell B2.

      =IF(AND(A2<>"",A2<>A1),1,0)
    

    この数式をコピーします。次に、列 B を加算する数式を追加します。Copy the formula, and then add a formula to add up column B.

      =SUM(B2:B11000)
    

    これらすべての数式の全計算にかかる時間は 0.027 秒です。A full calculation of all these formulas takes 0.027 seconds. このパフォーマンス向上係数は 13.8/0.027=511 です。This gives an improvement factor of 13.8/0.027=511.

まとめConclusion

Excel では、より大きなワークシートを効果的に管理できるようになり、以前のバージョンと比較して計算速度が大幅に向上しています。Excel enables you to effectively manage much larger worksheets, and it provides significant improvements in calculation speed compared with early versions. 大きなワークシートを作成すると、計算が遅くなることがよくあります。When you create large worksheets, it is easy to build them in a way that causes them to calculate slowly. 計算が遅いワークシートを使用していると、計算中にユーザーの集中力が途切れがちで、エラーが増加します。Slow-calculating worksheets increase errors because users find it difficult to maintain concentration while calculation is occurring.

直接的なテクニックを使用することにより、計算がどんなに遅いワークシートでも、計算速度を 10 ~ 100 倍上げることができます。By using a straightforward set of techniques, you can speed up most slow-calculating worksheets by a factor of 10 or 100. また、これらのテクニックをワークシートの設計および作成時に適用すれば、ワークシートがすばやく計算されることを保証できます。You can also apply these techniques as you design and create worksheets to ensure that they calculate quickly.

関連項目See also

サポートとフィードバックSupport and feedback

Office VBA またはこの説明書に関するご質問やフィードバックがありますか?Have questions or feedback about Office VBA or this documentation? サポートの受け方およびフィードバックをお寄せいただく方法のガイダンスについては、Office VBA のサポートおよびフィードバックを参照してください。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.