Excel のパフォーマンス: パフォーマンスの問題を最適化するヒントExcel performance: Tips for optimizing performance obstructions

適用対象: Excel |Excel 2013 |Office 2016 |示すApplies to: Excel | Excel 2013 | Office 2016 | VBA

Excel で頻繁に発生する多くのパフォーマンスの問題を最適化するには、以下のヒントを参考にしてください。Follow these tips for optimizing many frequently occurring performance obstructions in Excel.

参照とリンクの種類に関連するパフォーマンスを向上させる方法について説明します。Learn how to improve performance related to types of references and links.

前方参照と後方参照を使用しないでください。Do not use forward referencing and backward referencing

わかりやすい構造にしてエラーを回避するには、他の数式やセルを前方参照 (右側または上側への参照) しないように数式を設計します。To increase clarity and avoid errors, design your formulas so that they do not refer forward (to the right or below) to other formulas or cells. 通常、前方参照は計算のパフォーマンスに影響しませんが、ブックを初めて計算するという極端なケースで、計算を後回しにする必要がある数式が多数存在すると、適切な計算順序を確立するのに時間がかかる可能性があります。Forward referencing usually does not affect calculation performance, except in extreme cases for the first calculation of a workbook, where it might take longer to establish a sensible calculation sequence if there are many formulas that need to have their calculation deferred.

反復処理での循環参照の使用を最小限にするMinimize use of circular references with iteration

反復計算で循環参照を計算すると、複数の計算が必要になり、これらの計算がシングルスレッドになるので、処理が遅くなります。Calculating circular references with iterations is slow because multiple calculations are needed, and these calculations are single-threaded. 多くの場合、反復計算が不要になるように、代数を使用して循環参照を "アンロール" できます。Frequently you can "unroll" the circular references by using algebra so that iterative calculation is no longer needed. たとえば、キャッシュフローと利息計算では、利息の前にキャッシュフローを計算し、利息を計算してから、利息を含むキャッシュフローを計算します。For example, in cash flow and interest calculations, try to calculate the cash flow before interest, calculate the interest, and then calculate the cash flow including the interest.

Excel では、依存関係を考慮せずに、円グラフが計算されます。Excel calculates circular references sheet-by-sheet without considering dependencies. したがって、循環参照が複数のワークシートにまたがっていると、通常は計算が遅くなります。Therefore, you usually get slow calculation if your circular references span more than one worksheet. 不要な計算を実行しないように、循環計算を単一のワークシートに移すか、ワークシートの計算順序を最適化してみます。Try to move the circular calculations onto a single worksheet or optimize the worksheet calculation sequence to avoid unnecessary calculations.

反復計算を開始する前に、Excel ではブックを再計算して、循環参照とその依存関係をすべて識別する必要があります。Before the iterative calculations start, Excel must recalculate the workbook to identify all the circular references and their dependents. この処理には、計算を 2 ~ 3 回繰り返すのと同じだけの負荷がかかります。This process is equal to two or three iterations of the calculation.

循環参照とその依存関係が識別された後、Excel は、計算のたびに、循環参照内のすべてのセルだけでなく、循環参照チェーン内のセルに依存するセルと、揮発性セルとその依存関係も含めて計算する必要があります。After the circular references and their dependents are identified, each iteration requires Excel to calculate not only all the cells in the circular reference, but also any cells that depend on the cells in the circular reference chain, together with volatile cells and their dependents. 循環参照内のセルに依存する複雑な計算がある場合は、その計算を別の閉じているブックに切り離して、循環計算の収束後に開いて再計算を行うと、計算時間を短縮できます。If you have a complex calculation that depends on cells in the circular reference, it can be faster to isolate this into a separate closed workbook and open it for recalculation after the circular calculation has converged.

循環計算内のセル数を減らして、それらのセルの計算にかかる時間を短縮することが重要です。It is important to reduce the number of cells in the circular calculation and the calculation time that is taken by these cells.

可能な場合はブック間のリンクを避けてください。時間がかかり、破損していることがあり、簡単に見つけて修正できるわけではありません。Avoid inter-workbook links when it is possible; they can be slow, easily broken, and not always easy to find and fix.

通常は、よりサイズの大きいブックを使用するよりも、使用する方が効率的です。Using fewer larger workbooks is usually, but not always, better than using many smaller workbooks. このような場合、多くのフロントエンドの計算が、個別のブックに格納されている、または RAM が不足しているなどの理由で再計算が必要になることがあります。Some exceptions to this might be when you have many front-end calculations that are so rarely recalculated that it makes sense to put them in a separate workbook, or when you have insufficient RAM.

閉じられたブックで機能する簡単な直接のセル参照を使用してみてください。Try to use simple direct cell references that work on closed workbooks. これにより、ブックを再計算するときに、リンクされ** ているブックをすべて再計算しないようにすることができます。By doing this, you can avoid recalculating all your linked workbooks when you recalculate any workbook. また、Excel が閉じているブックから読み取った値を確認することもできます。これは、ブックをデバッグおよび監査する際によく重要です。Also, you can see the values Excel has read from the closed workbook, which is frequently important for debugging and auditing the workbook.

ブック間のリンクをどうしても使用する必要がある場合は、閉じているブックではなく、ブックをすべて開くようにします。また、ブックを開くときは、リンク先のブックを開いてから、リンク元のブックを開いてください。If you cannot avoid using linked workbooks, try to have them all open instead of closed, and open the workbooks that are linked to before you open the workbooks that are linked from.

多数のワークシートを使用すると、ブックは使いやすくなりますが、一般に、他のワークシートへの参照を計算する方が、ワークシート内の参照を計算するよりも低速になります。Using many worksheets can make your workbook easier to use, but generally it is slower to calculate references to other worksheets than references within worksheets.

使用範囲を最小限にするMinimize the used range

メモリを節約し、ファイルサイズを小さくするために、Excel は、使用されたワークシートの領域に関する情報のみを保存しようとします。To save memory and reduce file size, Excel tries to store information about only the area on a worksheet that was used. この領域は "使用範囲"** と呼ばれます。This is called the used range. 場合によっては、現在の使用範囲と見なしている範囲を大幅に越えた場所で、さまざまな編集操作や書式設定操作を行っていることがあります。Sometimes various editing and formatting operations extend the used range significantly beyond the range that you would currently consider used. こうしたことが原因で、パフォーマンスやファイルサイズ上の障害となることがあります。This can cause performance obstructions and file-size obstructions.

Ctrl + End を使用して、ワークシートの表示されている範囲を確認できます。You can check the visible used range on a worksheet by using Ctrl+End. これは、非常に長い場合は、最後に使用したセルの下および右側にあるすべての行と列を削除してから、ブックを保存することをお勧めします。Where this is excessive, you should consider deleting all the rows and columns below and to the right of your real last used cell, and then saving the workbook. 最初にバックアップ コピーを作成します。Create a backup copy first. このときに削除する領域が、数式に伴う範囲に含まれていたり、その範囲から参照されていたりすると、その範囲はサイズが縮小されるか、#N/A に変更されます。If you have formulas with ranges that extend into or refer to the deleted area, these ranges will be reduced in size or changed to #N/A.

その他のデータを許可するAllow for extra data

データの行や列をワークシートに頻繁に追加する場合は、データを追加するたびに数式を探して変更するのではなく、新しいデータ領域を自動参照するように数式を設定する方法が必要です。When you frequently add rows or columns of data to your worksheets, you need to find a way of having your Excel formulas automatically refer to the new data area, instead of trying to find and change your formulas every time.

これを行うには、数式に大きな範囲を使用して、現在のデータ境界を超えていることを確認します。You can do this by using a large range in your formulas that extends well beyond your current data boundaries. ただし、特定の状況では非効率的な計算が発生する可能性があります。行や列を削除すると、それに気付かれずに範囲が減少する可能性があるため、管理が困難になります。However, this can cause inefficient calculation under certain circumstances, and it is difficult to maintain because deleting rows and columns can decrease the range without you noticing.

Excel 2007 以降では、構造化されたテーブル参照を使用できるようになります。これは、参照テーブルのサイズが増加または減少すると自動的に展開および縮小されます。Starting in Excel 2007, you can use structured table references, which automatically expand and contract as the size of the referenced table increases or decreases.

これにはいくつかの利点があります。This solution has several advantages:

  • 列の参照と動的範囲の代替手段に比べて、パフォーマンス上の欠点が発生します。Fewer performance disadvantages exist than the alternatives of whole column referencing and dynamic ranges.

  • 単一のワークシート上で複数のデータ テーブルを容易に使用できます。It is easy to have multiple tables of data on a single worksheet.

  • テーブルに埋め込まれた数式も、データに合わせて拡張および縮小します。Formulas that are embedded in the table also expand and contract with the data.

または、列と行の参照全体を使用します。Alternatively, use whole column and row references

別の方法として、列の参照全体を使用することもできます。たとえば $A: $Aのようにします。An alternative approach is to use a whole column reference, for example $A:$A. このリファレンスは、列 A 内のすべての行を返します。そのため、必要なだけデータを追加することができ、その参照には常にそれが含まれます。This reference returns all the rows in Column A. Therefore, you can add as much data as you want, and the reference will always include it.

これには長所も短所もあります。This solution has both advantages and disadvantages:

  • Excel の多数の組み込み関数 (SUMSUMIF) は、列内の最後に使用された行を自動認識するので、全列の参照を効率的に計算します。Many Excel built-in functions (SUM, SUMIF) calculate whole column references efficiently because they automatically recognize the last used row in the column. 一方で、SUMPRODUCT などの配列計算関数は、全列の参照を処理することも、列内のすべてのセルを計算することもできません。However, array calculation functions like SUMPRODUCT either cannot handle whole column references or calculate all the cells in the column.

  • ユーザー定義関数は、列内の最後に使用された行を自動認識しないため、一般に、全列の参照は効率的に計算されません。User-defined functions do not automatically recognize the last-used row in the column and, therefore, frequently calculate whole column references inefficiently. ただし、最後に使用された行を認識するようにユーザー定義関数をプログラムするのは簡単です。However, it is easy to program user-defined functions so that they recognize the last-used row.

  • 単一のワークシート上に複数のデータ テーブルがある場合は、全列の参照を使用するのは困難です。It is difficult to use whole column references when you have multiple tables of data on a single worksheet.

  • Excel 2007 以降のバージョンでは、配列数式は列全体の参照を処理できますが、これにより、空のセルを含む、列内のすべてのセルが強制的に計算されます。In Excel 2007 and later versions, array formulas can handle whole-column references, but this forces calculation for all the cells in the column, including empty cells. 特に100万行の場合は、計算に時間がかかることがあります。This can be slow to calculate, especially for 1 million rows.

または、動的範囲を使用します。Alternatively, use dynamic ranges

名前付き範囲の定義でOFFSETまたはINDEXおよびCOUNTA関数を使用すると、名前付き範囲が参照する領域を、動的に拡大および縮小することができます。By using the OFFSET or INDEX and COUNTA functions in the definition of a named range, you can make the area that the named range refers to dynamically expand and contract. たとえば、次の数式のいずれかを使用して定義済みの名前を作成します。For example, create a defined name using one of the following formulas:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
  =Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A)+ROW(Sheet1!$A$1) - 1,1)

動的範囲の名前を数式で使用すると、新しい項目を含むように自動的に拡張されます。When you use the dynamic range name in a formula, it automatically expands to include new entries.

offset 式では、動的範囲にインデックス数式を使用する**** ことをお勧めします。オフセット式の場合は、通常は、計算のたびに計算される揮発性関数であるという短所があります。Using the INDEX formula for a dynamic range is generally preferable to the OFFSET formula because OFFSET has the disadvantage of being a volatile function that will be calculated at every recalculation.

動的範囲の数式内のCOUNTA関数では、多くの行を調べる必要があるため、パフォーマンスが低下します。Performance decreases because the COUNTA function inside the dynamic range formula must examine many rows. 数式のCOUNTA部分を別のセルまたは定義名に格納した後、動的範囲のセルまたは名前を参照することによって、このパフォーマンスの低下を最小限に抑えることができます。You can minimize this performance decrease by storing the COUNTA part of the formula in a separate cell or defined name, and then referring to the cell or name in the dynamic range:

 Counts!z1=COUNTA(Sheet1!$A:$A)
 OffsetDynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)
 IndexDynamicRange=Sheet1!$A$1:INDEX(Sheet1!$A:$A,Counts!$Z$1+ROW(Sheet1!$A$1) - 1,1)

indirectなどの関数を使用して動的範囲を構築することもできますが、 indirectは volatile で、常にシングルスレッドを計算します。You can also use functions such as INDIRECT to construct dynamic ranges, but INDIRECT is volatile and always calculates single-threaded.

動的範囲の長所と短所は次のとおりです。Dynamic ranges have the following advantages and disadvantages:

  • 動的範囲は、配列数式によって実行される計算数を適切に制限します。Dynamic ranges work well to limit the number of calculations performed by array formulas.

  • 単一の列内で複数の動的範囲を使用するには、特殊なカウント関数が必要です。Using multiple dynamic ranges within a single column requires special-purpose counting functions.

  • 動的範囲を多数使用すると、パフォーマンスが低下する可能性があります。Using many dynamic ranges can decrease performance.

参照計算時間を短縮するImprove lookup calculation time

Office 365 バージョン 1809 以降では、並べ替えなしのデータ上での完全一致について、同じテーブル範囲から複数の列 (または HLOOKUP を含む行) を検索するときの Excel の VLOOKUP、HLOOKUP、MATCH の速度が以前より大幅に改善されました。In Office 365 version 1809 and later, Excel's VLOOKUP, HLOOKUP, and MATCH for exact match on unsorted data is much faster than ever before when looking up multiple columns (or rows with HLOOKUP) from the same table range.

とはいえ、以前の Excel バージョンでは、検索は多くの場合、重要な計算上の問題になります。That said, for earlier Excel versions, Lookups continue to be frequently significant calculation obstructions. 幸い、ルックアップの計算時間はさまざまな方法によって短縮できます。Fortunately, there are many ways of improving lookup calculation time. 完全一致オプションを使用すると、関数の計算時間は、一致が見つかるまでにスキャンされるセルの数に比例します。If you use the exact match option, the calculation time for the function is proportional to the number of cells scanned before a match is found. ルックアップの範囲が拡大すると、計算時間が大幅に増加する可能性があります。For lookups over large ranges, this time can be significant.

並べ替えられたデータに対して VLOOKUPHLOOKUP、および MATCH の近似一致オプションを使用すると、ルックアップはすばやく行われ、ルックアップする範囲の長さによってルックアップ時間が大幅に増加することはありません。Lookup time using the approximate match options of VLOOKUP, HLOOKUP, and MATCH on sorted data is fast and is not significantly increased by the length of the range you are looking up. 特性はバイナリ検索と同じです。Characteristics are the same as binary search.

検索オプションを理解するUnderstand lookup options

一致、 VLOOKUP、およびHLOOKUPの照合型オプションと範囲参照オプション**** を理解していることを確認してください。Ensure that you understand the match-type and range-lookup options in MATCH, VLOOKUP, and HLOOKUP.

次のコード例は、MATCH 関数の構文です。The following code example shows the syntax for the MATCH function. 詳細については、WorksheetFunction オブジェクトの Match メソッドを参照してください。For more information, see the Match method of the WorksheetFunction object.

  MATCH(lookup value, lookup array, matchtype)
  • Matchtype = 1は、参照配列が昇順で並べ替えられている場合に、参照値と等しいか、それよりも大きい一致を返します (概数一致)。Matchtype=1 returns the largest match less than or equal to the lookup value when the lookup array is sorted ascending (approximate match). 参照配列が昇順で並べ替えられていない場合、MATCH は正しくない応答を返します。If the lookup array is not sorted ascending, MATCH will return an incorrect answer. 既定のオプションは、昇順で並べ替えられた近似値に一致します。The default option is approximate match sorted ascending.

  • Matchtype=0 は、完全一致を要求し、データは並べ替えられていないことを想定します。Matchtype=0 requests an exact match and assumes that the data is not sorted.

  • Matchtype=-1 は、ルックアップ配列が降順で並べ替えられている場合に、ルックアップ値以上の最小一致を返します (近似一致)。Matchtype=-1 returns the smallest match greater than or equal to the lookup value if the lookup array is sorted descending (approximate match).

次のコード例は、VLOOKUP および HLOOKUP 関数の構文です。The following code example shows the syntax for the VLOOKUP and HLOOKUP functions. 詳細については、WorksheetFunction オブジェクトの VLOOKUP および HLOOKUP メソッドを参照してください。For more information, see the VLOOKUP and HLOOKUP methods of the WorksheetFunction object.

  VLOOKUP(lookup value, table array, col index num, range-lookup)
  HLOOKUP(lookup value, table array, row index num, range-lookup)
  • Range-lookup=TRUE は、ルックアップ値以下の最長一致を返します (近似一致)。Range-lookup=TRUE returns the largest match less than or equal to the lookup value (approximate match). これは、既定のオプションです。This is the default option. テーブル配列は昇順で並べ替える必要があります。Table array must be sorted ascending.

  • Range-lookup=FALSE は、完全一致を要求し、データは並べ替えられていないことを想定します。Range-lookup=FALSE requests an exact match and assumes the data is not sorted.

並べ替えられていないデータのルックアップは低速なるので、できるだけルックアップを実行しないでください。Avoid performing lookups on unsorted data where possible because it is slow. データが並べ替えられていても正確に一致する必要がある場合は、「値が不足している並べ替えデータに2つの参照を使用する」を参照してください。If your data is sorted, but you want an exact match, see Use two lookups for sorted data with missing values.

VLOOKUP ではなく、INDEX と MATCH または OFFSET を使用するUse INDEX and MATCH or OFFSET instead of VLOOKUP

VLOOKUP ではなく、INDEX および MATCH 関数を使用するようにします。Try using the INDEX and MATCH functions instead of VLOOKUP. VLOOKUPは、 matchおよびindex、またはOFFSETを組み合わせた場合よりも少し速く (約 5%)、より少ないメモリを使用します。 **** **** 多くの場合、提供される時間を大幅に節約できます。Although VLOOKUP is slightly faster (approximately 5 percent faster), simpler, and uses less memory than a combination of MATCH and INDEX, or OFFSET, the additional flexibility that MATCH and INDEX offer often enables you to significantly save time. たとえば、完全一致を指定した MATCH の結果をセルに保管しておいて、複数の INDEX ステートメントで再使用できます。For example, you can store the result of an exact MATCH in a cell and reuse it in several INDEX statements.

INDEX関数は fast で、非揮発性関数なので、再計算を高速化できます。The INDEX function is fast and is a non-volatile function, which speeds up recalculation. OFFSET関数も高速です。ただし、これは揮発性関数で、計算チェーンの処理にかかる時間が大幅に長くなる場合があります。The OFFSET function is also fast; however, it is a volatile function, and it sometimes significantly increases the time taken to process the calculation chain.

VLOOKUP は、INDEXMATCH に簡単に変換できます。It is easy to convert VLOOKUP to INDEX and MATCH. 次の2つのステートメントは、同じ応答を返します。The following two statements return the same answer:

 VLOOKUP(A1, Data!$A$2:$F$1000,3,False)

 INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)

検索を高速化するSpeed up lookups

完全一致ルックアップは低速になることがあるので、次のオプションによってパフォーマンスを改善することを検討します。Because exact match lookups can be slow, consider the following options for improving performance:

  • ワークシートは 1 つだけ使用します。Use one worksheet. ルックアップとデータを同じワークシートで維持すると、処理速度が向上します。It is faster to keep lookups and data on the same sheet.

  • 可能であれば、最初に SORT によってデータを並べ替えてから (SORT は高速)、近似一致を使用します。When you can, SORT the data first (SORT is fast), and use approximate match.

  • 完全一致ルックアップを使用する必要がある場合は、スキャンするセルの範囲を最小にします。When you must use an exact match lookup, restrict the range of cells to be scanned to a minimum. 多数の行または列を参照するのではなく、テーブルと構造化参照または動的範囲名を使用します。Use tables and structured references or dynamic range names rather than referring to a large number of rows or columns. 場合によっては、ルックアップの範囲の下限と上限を事前に計算することもできます。Sometimes you can pre-calculate a lower-range limit and upper-range limit for the lookup.

値が欠落している並べ替えられたデータに対して2つの検索を使用するUse two lookups for sorted data with missing values

2つの近似一致は、少数の行を超える参照に対して、完全一致が1つの場合よりも大幅に高速です。Two approximate matches are significantly faster than one exact match for a lookup over more than a few rows. (breakeven ポイントは約10-20 行です。)(The breakeven point is about 10-20 rows.)

データを並べ替えることができても近似一致を使用できない場合は、参照している値が参照範囲に存在することを確認できないので、次の数式を使用できます。If you can sort your data but still cannot use approximate match because you cannot be sure that the value you are looking up exists in the lookup range, you can use this formula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
      VLOOKUP(lookup_val, lookup_array, column, True), "notexist")

この数式の最初の部分は、ルックアップ列自体を近似ルックアップします。The first part of the formula works by doing an approximate lookup on the lookup column itself.

  VLOOKUP(lookup_val ,lookup_array,1,True)

次の式を使用して、ルックアップ列からの応答が参照値と同じかどうかを確認できます (この場合、完全一致がある場合)。You can check if the answer from the lookup column is the same as the lookup value (in which case you have an exact match) by using the following formula:

  IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,

この数式が True を返す場合は、完全一致が見つかったので、今度は、必要な列から答えを返します。If this formula returns True, you have found an exact match, so you can do the approximate lookup again, but this time, return the answer from the column you want.

  VLOOKUP(lookup_val, lookup_array, column, True)

ルックアップ列からの回答がルックアップ値と一致しなかった場合は、値が存在しないので、式は "notexist" を返します。If the answer from the lookup column did not match the lookup value, you have a missing value, and the formula returns "notexist".

リスト内の最小値よりも小さな値をルックアップすると、エラーが発生します。Be aware that if you look up a value smaller than the smallest value in the list, you receive an error. このエラーの処理には、IFERROR を使用するか、小さなテスト値をリストに追加します。You can handle this error by using IFERROR, or by adding a small test value to the list.

IFERROR 関数を使用して、値が不足しているデータを並べ替えません。Use IFERROR function for unsorted data with missing values

並べ替えられていないデータに対して完全一致検索を使用する必要があり、参照値が存在するかどうかを確認できない場合は、一致が見つからない場合に返される #N/a を処理する必要があります。If you must use exact match lookup on unsorted data, and you cannot be sure whether the lookup value exists, you often must handle the #N/A that is returned if no match is found. Excel 2007 以降では、 IFERROR関数を使用することができます。これは単純なものと高速なものの両方です。Beginning with Excel 2007, you can use the IFERROR function, which is both simple and fast.

  IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)

Excel 2007 よりも前のバージョンでは、2 つのルックアップを含む IF 関数を使用します。この関数はシンプルですが、低速です。In earlier versions, a simple but slow way is to use an IF function that contains two lookups.

  IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
      VLOOKUP(lookupval,table,2,FALSE))

完全一致ルックアップを 2 回使用することを回避できます。そのためには、MATCH で完全一致ルックアップを 1 回使用し、その結果をセルに格納し、その結果をテストしてから INDEX を実行します。You can avoid the double exact lookup if you use exact MATCH once, store the result in a cell, and then test the result before doing an INDEX.

  In A1 =MATCH(lookupvalue,lookuparray,0)
  In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))

2 つのセルを使用できない場合は、COUNTIF を使用します。If you cannot use two cells, use COUNTIF. これは一般に完全一致ルックアップよりも高速です。It is generally faster than an exact match lookup.

  IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
      VLOOKUP(lookupval, table, 2 FALSE))

複数列の完全一致参照に対して match および INDEX を使用するUse MATCH and INDEX for exact match lookups on multiple columns

通常、完全一致を指定した MATCH を保管して何回でも再使用できます。You can often reuse a stored exact MATCH many times. たとえば、複数の結果列に対して完全一致ルックアップを実行する場合は、1 つの MATCH と多数の INDEX ステートメントを使用した方が、多数の VLOOKUP ステートメントを使用するよりも時間を節約できます。For example, if you are doing exact lookups on multiple result columns, you can save time by using one MATCH and many INDEX statements rather than many VLOOKUP statements.

結果を格納するための**** 追加の列 (stored_row) を追加し、各結果列に次のものを使用します。Add an extra column for the MATCH to store the result (stored_row), and for each result column use the following:

  INDEX(Lookup_Range,stored_row,column_number)

また、VLOOKUP を配列数式で使用することもできます。Alternatively, you can use VLOOKUP in an array formula. (配列数式は、Ctrl +-Shift + Enter キーを使用して入力する必要があります。(Array formulas must be entered by using Ctrl+-Shift+Enter. Excel は {and} を追加して、これが配列数式であることを示します。Excel will add the { and } to show you that this is an array formula).

  {VLOOKUP(lookupvalue,{4,2},FALSE)}

連続した行または列のセットにインデックスを使用するUse INDEX for a set of contiguous rows or columns

1 回のルックアップ操作で多数のセルを返すこともできます。You can also return many cells from one lookup operation. 連続する複数の列をルックアップするには、INDEX 関数を配列数式で使用して、一度に複数の列を返すことができます (0 を列番号として使用)。To look up several contiguous columns, you can use the INDEX function in an array formula to return multiple columns at once (use 0 as the column number). また、INDEX 関数を使用して、一度に複数の行を返すこともできます。You can also use the INDEX function to return multiple rows at one time.

  {INDEX($A$1:$J$1000,stored_row,0)}

このステートメントは、前の MATCH ステートメントで作成した stored_row から列 A ~ J を返します。This returns column A to column J from the stored row created by a previous MATCH statement.

MATCH を使用して、セルの長方形ブロックを返すUse MATCH to return a rectangular block of cells

MATCH および OFFSET 関数を使用して、セルの矩形ブロックを返すことができます。You can use the MATCH and OFFSET functions to return a rectangular block of cells.

2次元検索で MATCH および INDEX を使用するUse MATCH and INDEX for two-dimensional lookup

2次元のテーブル検索を効率的に実行するには、2つの埋め込み関数を使用して、テーブル**** の行と列に**** 対して個別のルックアップを使用します。You can efficiently do a two-dimensional table lookup by using separate lookups on the rows and columns of a table by using an INDEX function with two embedded MATCH functions, one for the row and one for the column.

複数インデックス検索でサブセット範囲を使用するUse a subset range for multiple-index lookup

大規模なワークシートでは、多くの場合、複数のインデックスを使用して検索する必要があります。たとえば、国の製品ボリュームを検索する場合などです。In large worksheets, you may frequently need to look up by using multiple indexes, such as looking up product volumes in a country. このようなルックアップを実行するには、インデックスを連結し、連結されたルックアップ値を使用してルックアップを実行します。To do this, you can concatenate the indexes and perform the lookup by using concatenated lookup values. ただし、次の 2 つの理由により、効率は低下します。However, this is inefficient for two reasons:

  • 文字列の連結には大量の計算処理が必要である。Concatenating strings is a calculation-intensive operation.

  • ルックアップの範囲が大きい。The lookup will cover a large range.

多くの場合、検索のサブセット範囲を計算する方が効率的です (たとえば、国の最初と最後の行を検索し、そのサブセットの範囲内で製品を検索した場合)。It is often more efficient to calculate a subset range for the lookup (for example, by finding the first and last row for the country, and then looking up the product within that subset range).

3次元参照のオプションを検討するConsider options for three-dimensional lookup

行と列だけでなく、テーブルもルックアップする場合は、次のテクニックを使用できます。これらは、特に、Excel でテーブルをルックアップまたは選択する方法です。To look up the table to use in addition to the row and the column, you can use the following techniques, focusing on how to make Excel look up or choose the table.

検索する各テーブル (3 次元) が、名前付きの構造テーブル、範囲名、または範囲を表すテキスト文字列の表として格納されている場合は、 CHOOSE関数またはINDIRECT関数を使用することができます。If each table that you want to look up (the third dimension) is stored as a set of named structured tables, range names, or as a table of text strings that represent ranges, you might be able to use the CHOOSE or INDIRECT functions.

  • CHOOSE と範囲名を使用すると効率的です。Using CHOOSE and range names can be an efficient method. CHOOSE は不揮発性ですが、比較的テーブル数が少ない場合に最適です。CHOOSE is not volatile, but it is best-suited to a relatively small number of tables. 次の使用例TableLookup_Valueは、参照テーブルで使用TableName1, TableName2, ...する範囲名 () を選択するために、を動的に使用します。This example dynamically uses TableLookup_Value to choose which range name (TableName1, TableName2, ...) to use for the lookup table.

      INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
      MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
    
  • 次の使用例で**** は、INDIRECT TableLookup_Value関数を使用して、参照テーブルに使用するシート名を動的に作成します。The following example uses the INDIRECT function and TableLookup_Value to dynamically create the sheet name to use for the lookup table. この方法の利点は、シンプルであること、および大量のテーブルを処理できることです。This method has the advantage of being simple and able to handle a large number of tables. INDIRECTは volatile なシングルスレッドの関数であるため、ルックアップはデータが変更されていない場合でも、すべての計算で1つのスレッドで計算されます。Because INDIRECT is a volatile single-threaded function, the lookup is single-thread calculated at every calculation even if no data has changed. このメソッドの使用には時間がかかります。Using this method is slow.

      INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _
      MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
    
  • また、VLOOKUP 関数によって、ワークシートの名前やテーブルで使用するテキスト文字列を見つけて、次に、INDIRECT 関数によって結果のテキストを範囲に変換することもできます。You could also use the VLOOKUP function to find the name of the sheet or the text string to use for the table, and then use the INDIRECT function to convert the resulting text into a range.

      INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
    

別のテクニックとして、個々のテーブルを識別する追加の列を持つ 1 つの巨大なテーブルに、すべてのテーブルを集約することもできます。Another technique is to aggregate all your tables into one giant table that has an additional column that identifies the individual tables. これらのテクニックは、前の例に示したマルチインデックス ルックアップで使用できます。You can then use the techniques for multiple-index lookup shown in the previous examples.

ワイルドカード検索を使用するUse wildcard lookup

MATCHVLOOKUP、およびHLOOKUP関数を使用すると、ワイルドカード文字を使用することができますか。The MATCH, VLOOKUP, and HLOOKUP functions allow you to use the wildcard characters ? (任意の1文字) * を指定し、完全一致の場合は (文字または任意の数の文字は不可)。(any single character) and * (no character or any number of characters) on alphabetical exact matches. 場合によっては、この方法によって複数の一致を回避することもできます。Sometimes you can use this method to avoid multiple matches.

配列数式と SUMPRODUCT を最適化するOptimize array formulas and SUMPRODUCT

配列数式と SUMPRODUCT 関数は強力ですが、扱いに注意が必要です。Array formulas and the SUMPRODUCT function are powerful, but you must handle them carefully. 1つの配列数式は、多くの計算を必要とする場合があります。A single array formula might require many calculations.

配列数式の計算速度を最適化するためのキーは、配列数式で評価されるセルと式の数が可能な限り小さくなるようにすることです。The key to optimizing the calculation speed of array formulas is to ensure that the number of cells and expressions that are evaluated in the array formula is as small as possible. 配列数式は揮発性の数式のようなものです。参照されているいずれかのセルが変更された場合、揮発性になっている場合、または再計算された場合、配列数式は数式内のすべてのセルを計算し、ca を実行するために必要なすべての仮想セルを評価します。lカリング。Remember that an array formula is a bit like a volatile formula: if any one of the cells that it references has changed, is volatile, or has been recalculated, the array formula calculates all the cells in the formula and evaluates all the virtual cells it needs to do the calculation.

配列数式の計算速度を最適化するにはTo optimize the calculation speed of array formulas:

  • 式や範囲の参照を配列数式から別の補助的な列や行に移動します。Take expressions and range references out of the array formulas into separate helper columns and rows. こうすると、Excel の高機能再計算プロセスをよりいっそう効果的に利用できます。This makes much better use of the smart recalculation process in Excel.

  • すべての行、または行や列を必要以上に参照しないでください。Do not reference complete rows, or more rows and columns than you need. 配列数式では、空白セルや使用されていないセルも含め、すべてのセル参照が強制的に計算されます。Array formulas are forced to calculate all the cell references in the formula even if the cells are empty or unused. Excel 2007 以降では 100 万行まで使用できるので、全列を参照する配列数式は計算速度が著しく低下します。With 1 million rows available starting in Excel 2007, an array formula that references a whole column is extremely slow to calculate.

  • Excel 2007 以降では、配列数式で評価するセルを最少数に維持できる構造化された参照を使用します。Starting in Excel 2007, use structured references where you can to keep the number of cells that are evaluated by the array formula to a minimum.

  • Excel 2007 より前のバージョンでは、可能な場合は動的範囲名を使用します。In versions earlier than Excel 2007, use dynamic range names where possible. 動的範囲名は揮発性ですが、範囲のサイズを最小にできるので有効です。Although they are volatile, it is worthwhile because they minimize the size of the ranges.

  • 行と列の両方を参照する配列数式は、矩形範囲を強制的に計算するので注意してください。Be careful with array formulas that reference both a row and a column: this forces the calculation of a rectangular range.

  • 可能であれば、SUMPRODUCT を使用します。これは、同等の配列数式よりも少し高速です。Use SUMPRODUCT if possible; it is slightly faster than the equivalent array formula.

複数条件配列数式で SUM を使用するためのオプションを検討するConsider options for using SUM for multiple-condition array formulas

可能であれば、配列数式ではなく、SUMIFSCOUNTIFS、および AVERAGEIFS 関数を常に使用する必要があります。計算速度が大幅に向上しています。You should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of array formulas where you can because they are much faster to calculate. Excel 2016 では、fast maxifsおよびminifs関数が導入されています。Excel 2016 introduces fast MAXIFS and MINIFS functions.

Excel 2007 より前のバージョンでは、複数の条件を使用して合計を計算するために、多くの場合、配列数式が使用されます。In versions earlier than Excel 2007, array formulas are often used to calculate a sum with multiple conditions. これは比較的簡単に実行できます。特に、Excel で条件付き合計ウィザードを使用する場合は、時間がかかる場合があります。This is relatively easy to do, especially if you use the Conditional Sum Wizard in Excel, but it is often slow. 通常、同じ結果を得るためのより高速な方法があります。Usually there are much faster ways of getting the same result. 複数の条件を合計するだけの場合は、 DSUM関数を使用して、同等の配列数式よりも処理速度を向上させることができます。If you have only a few multiple-condition SUMs, you may be able to use the DSUM function, which is much faster than the equivalent array formula.

配列数式を使用する必要がある場合は、次の方法で高速化できます。If you must use array formulas, some good methods of speeding them up are as follows:

  • 動的範囲名または構造化テーブル参照を使用して、セルの数を最小限にします。Use dynamic range names or structured table references to minimize the number of cells.

  • 各行に対してTrueまたはFalseを返すヘルパー式の列に複数の条件を分割し、 SUMIFまたは array 数式でヘルパー列を参照します。Split out the multiple conditions into a column of helper formulas that return True or False for each row, and then reference the helper column in a SUMIF or array formula. これは、1つの配列数式の計算の数を減らすようには表示されない場合があります。ただし、ほとんどの場合、スマート再計算プロセスでは、再計算が必要なヘルパー列の数式のみを再計算できます。This might not appear to reduce the number of calculations for a single array formula; however, most of the time it enables the smart recalculation process to recalculate only the formulas in the helper column that need to be recalculated.

  • すべての条件を単一の条件に連結し、SUMIF を使用することを検討します。Consider concatenating together all the conditions into a single condition, and then using SUMIF.

  • データを並べ替えることができる場合は、行のグループをカウントし、配列数式をサブセットグループを参照するように制限します。If the data can be sorted, count groups of rows and limit the array formulas to looking at the subset groups.

複数条件 SUMIFS、COUNTIFS、およびその他の IFS ファミリ関数に優先順位を設定するPrioritize multiple-condition SUMIFS, COUNTIFS, and other IFS family functions

これらの関数は、それぞれの条件を左から右へ順番に評価します。These functions evaluate each of the conditions from left to right in turn. そのため、最も制限の厳しい条件を最初に設定して、以降の条件で最小行数のみを検索する方が効率的です。Therefore, it is more efficient to put the most restrictive condition first, so that subsequent conditions only need to look at the smallest number of rows.

複数条件配列数式に対して SUMPRODUCT を使用するためのオプションを検討するConsider options for using SUMPRODUCT for multiple-condition array formulas

excel 2007 以降では、可能な場合は、 SUMIFSCOUNTIFS、およびAVERAGEIFS関数、および excel 2016 maxifsおよびminifs関数を使用する必要があります (可能な場合は、 SUMPRODUCTの数式ではなく)。Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions, and in Excel 2016 MAXIFS and MINIFS functions, instead of SUMPRODUCT formulas where possible.

Excel 2007 よりも前のバージョンでは、SUM 配列数式ではなく、SUMPRODUCT を使用した方がいくつかの点で便利です。In earlier versions, there are a few advantages to using SUMPRODUCT instead of SUM array formulas:

  • SUMPRODUCTは、Ctrl + Shift + Enter キーを使用して、配列で入力する必要はありません。SUMPRODUCT does not have to be array-entered by using Ctrl+Shift+Enter.

  • SUMPRODUCT は、通常、処理が少し高速です (5 ~ 10 パーセント)。SUMPRODUCT is usually slightly faster (5 to 10 percent).

SUMPRODUCTは、次のように複数条件の配列数式に対して使用できます。You can use SUMPRODUCT for multiple-condition array formulas as follows:

  SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)

この例ではCondition1Condition2となどの条件式$A$1:$A$10000<=$Z4があります。In this example, Condition1 and Condition2 are conditional expressions such as $A$1:$A$10000<=$Z4. 条件式の返す値は数字ではなく、True または False なので、これらを SUMPRODUCT 関数内で強制的に数字にする必要があります。Because conditional expressions return True or False instead of numbers, they must be coerced to numbers inside the SUMPRODUCT function. これを行うには、2つの負-- 符号 () を使用するか、0 (+ 0) を追加するか、または 1 (x1) を掛ける必要があります。You can do this by using two minus signs (--), or by adding 0 (+0), or by multiplying by 1 (x1). -- 使用すると、 + 0またはx1より少し速くなります。Using -- is slightly faster than +0 or x1.

範囲または配列のサイズと形状は、条件式で使用するものと、合計する範囲 (RangetoSum) で使用するものとが同じである必要があり、列全体を含むことはできません。Note that the size and shape of the ranges or arrays that are used in the conditional expressions and range to sum must be the same, and they cannot contain entire columns.

また、コンマで区切らずに、 SUMPRODUCT内の用語を直接乗算することもできます。You can also directly multiply the terms inside SUMPRODUCT rather than separate them by commas:

  SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)

通常、この値はコンマの構文を使用するよりも若干遅く、合計する範囲にテキスト値が含まれている場合はエラーになります。This is usually slightly slower than using the comma syntax, and it gives an error if the range to sum contains a text value. ただし、こちらの方が少し柔軟で、たとえば、条件に 1 列しか含まれていない場合でも、合計する範囲 (RangetoSum) には複数の列を含めることができます。However, it is slightly more flexible in that the range to sum may have, for example, multiple columns when the conditions have only one column.

SUMPRODUCT を使用して範囲と配列を乗算して追加するUse SUMPRODUCT to multiply and add ranges and arrays

たとえば、加重平均の計算では、数字の範囲を別の数字の範囲で乗算し、その結果を加算する必要がありますが、そのようなケースでは、SUMPRODUCT のコンマ構文を使用すると、配列入力される SUM を使用するよりも、処理速度が 20 ~ 25 パーセント向上します。In cases like weighted average calculations, where you need to multiply a range of numbers by another range of numbers and sum the results, using the comma syntax for SUMPRODUCT can be 20 to 25 percent faster than an array-entered SUM.

  {=SUM($D$2:$D$10301*$E$2:$E$10301)}
  =SUMPRODUCT($D$2:$D$10301*$E$2:$E$10301)
  =SUMPRODUCT($D$2:$D$10301,$E$2:$E$10301)

これらの3つの式はすべて同じ結果になりますが、 SUMPRODUCTにコンマの構文を使用する3番目の数式は、他の2つの式で必要とされる計算時間のうち、約 77% しかかかりません。These three formulas all produce the same result, but the third formula, which uses the comma syntax for SUMPRODUCT, takes only about 77 percent of the calculation time that the other two formulas need.

潜在的な配列と関数の計算上の問題に注意してください。Be aware of potential array and function calculation obstructions

Excel の計算エンジンは、配列数式と範囲を参照する関数を利用するように最適化されています。The calculation engine in Excel is optimized to exploit array formulas and functions that reference ranges. ただし、これらの数式と関数を通常とは異なる方法で使用すると、場合によっては、計算時間が著しく増加することがあります。However, some unusual arrangements of these formulas and functions can sometimes, but not always, cause significantly increased calculation time.

配列数式と範囲関数が関係する計算上の問題を見つけた場合は、次の確認を行う必要があります。If you find a calculation obstruction that involves array formulas and range functions, you should look for the following:

  • 参照が部分的に重複していないか。Partially overlapping references.

  • 配列数式と、範囲関数が参照するセルのブロックの一部が、別の配列数式や範囲関数の計算対象に含まれていないか。Array formulas and range functions that reference part of a block of cells that are calculated in another array formula or range function. 時系列分析ではこうした状況が頻繁に発生します。This situation can frequently occur in time series analysis.

  • 行によって参照する数式セットがあり、さらに、そのセットを列によって参照する別の数式セットがないか。One set of formulas referencing by row, and a second set of formulas referencing the first set by column.

  • 単一行の配列数式の大きなセットが対象としている列のブロックで、SUM 関数が各列の末尾で使用されていないか。A large set of single-row array formulas covering a block of columns, with SUM functions at the foot of each column.

関数を効率的に使用するUse functions efficiently

関数は Excel の機能を大幅に拡張していますが、使用方法によっては計算時間に影響することがあります。Functions significantly extend the power of Excel, but the way in which you use them can often affect calculation time.

シングルスレッド関数を回避するAvoid single-threaded functions

大部分のネイティブな Excel 関数は、マルチスレッド計算で適切に動作します。Most native Excel functions work well with multi-threaded calculation. ただし、可能な場合は、次のシングルスレッド関数の使用を避けてください。However, where possible, avoid using the following single-threaded functions:

  • VBA およびオートメーションのユーザー定義関数 (udf)。ただし、XLL ベースの udf はマルチスレッドにすることができます。VBA and Automation user-defined functions (UDFs), but XLL-based UDFs can be multi-threaded
  • PHONETICPHONETIC
  • CELL ("format" or "address" のどちらかの引数が使用された場合)CELL when either the "format" or "address" argument is used
  • INDIRECTINDIRECT
  • GETPIVOTDATAGETPIVOTDATA
  • CUBEMEMBERCUBEMEMBER
  • CUBEVALUECUBEVALUE
  • CUBEMEMBERPROPERTYCUBEMEMBERPROPERTY
  • CUBESETCUBESET
  • CUBERANKEDMEMBERCUBERANKEDMEMBER
  • CUBEKPIMEMBERCUBEKPIMEMBER
  • CUBESETCOUNTCUBESETCOUNT
  • 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 PivotTable
  • ERROR.TYPEERROR.TYPE
  • HYPERLINKHYPERLINK

テーブルを使用して範囲を処理する関数を使用するUse tables for functions that handle ranges

範囲を処理する SUMSUMIF、および SUMIFS などの関数の場合、計算時間は、合計またはカウントする使用中のセルの数に比例します。For functions like SUM, SUMIF, and SUMIFS that handle ranges, the calculation time is proportional to the number of used cells you are summing or counting. 使用されていないセルは調べられないので、列全体の参照は比較的効率的ですが、必要なセルを追加しないことをお勧めします。Unused cells are not examined, so whole column references are relatively efficient, but it is better to ensure that you do not include more used cells than you need. テーブルを使用するか、サブセット範囲または動的範囲を計算します。Use tables, or calculate subset ranges or dynamic ranges.

揮発性関数を減らすReduce volatile functions

揮発性関数を使用すると、計算のたびに再計算が必要な数式の数が増加するので、再計算が低速になる可能性があります。Volatile functions can slow recalculation because they increase the number of formulas that must be recalculated at each calculation.

一般に、揮発性関数の数を減らすには、OFFSET の代わりに INDEX を、INDIRECT の代わりに CHOOSE を使用します。You can often reduce the number of volatile functions by using INDEX instead of OFFSET, and CHOOSE instead of INDIRECT. ただし、 OFFSETは fast 関数で、多くの場合、高速計算を行うクリエイティブな方法で使用できます。However, OFFSET is a fast function and can often be used in creative ways that give fast calculation.

C または C++ のユーザー定義関数を使用するUse C or C++ user-defined functions

c または C++ でプログラミングされ、c API (XLL アドイン関数) を使用するユーザー定義関数は、通常、VBA または automation (.xla または automation アドイン) を使用して開発されたユーザー定義関数よりも高速に実行されます。User-defined functions that are programmed in C or C++ and that use the C API (XLL add-in functions) generally perform faster than user-defined functions that are developed by using VBA or Automation (XLA or Automation add-ins). 詳細については、「 Excel 2010 xlls の開発」を参照してください。For more information, see Developing Excel 2010 XLLs.

VBA のユーザー定義関数のパフォーマンスは、プログラミング方法や呼び出し方法によって左右されます。The performance of VBA user-defined functions is sensitive to how you program and call them.

より高速な VBA ユーザー定義関数を使用するUse faster VBA user-defined functions

通常、Excel の数式計算とワークシート関数を使用した方が、VBA ユーザー定義関数を使用するよりも高速です。It is usually faster to use the Excel formula calculations and worksheet functions than to use VBA user-defined functions. ユーザー定義関数は呼び出すたびに小さなオーバーヘッドが発生し、さらに、Excel からユーザー定義関数に情報を転送するときには大きなオーバーヘッドが発生します。This is because there is a small overhead for each user-defined function call and significant overhead transferring information from Excel to the user-defined function. ただし、ユーザー定義関数の設計と呼び出しが適切であれば、複雑な配列数式よりもはるかに高速になります。But well-designed and called user-defined functions can be much faster than complex array formulas.

ワークシート セルの参照をすべて、ユーザー定義関数の本体ではなく、ユーザー定義関数の入力パラメーターに設定します。このように設定すると、Application.Volatile を追加する無駄を省くことができます。Ensure that you have put all the references to worksheet cells in the user-defined function input parameters instead of in the body of the user-defined function, so that you can avoid adding Application.Volatile unnecessarily.

ユーザー定義関数を使用する多数の数式を指定する必要がある場合は、手動計算モードにしていること、および計算が VBA から開始されることを確認してください。If you must have many formulas that use user-defined functions, ensure that you are in manual calculation mode, and that the calculation is initiated from VBA. VBA ユーザー定義関数の計算は、計算が** VBA から呼び出されない場合 (自動モードや手動モードで F9 キーを押した場合など) は大幅に低速になります。VBA user-defined functions calculate much more slowly if the calculation is not called from VBA (for example, in automatic mode or when you press F9 in manual mode). これは、Visual Basic Editor (Alt + F11) が開いている場合、または現在の Excel セッションで開かれている場合に特に当てはまります。This is particularly true when the Visual Basic Editor (Alt+F11) is open or has been opened in the current Excel session.

次のように、F9 キーをトラップして VBA 計算サブルーチンにリダイレクトできます。You can trap F9 and redirect it to a VBA calculation subroutine as follows. このサブルーチンをThisworkbookモジュールに追加します。Add this subroutine to the Thisworkbook module.

  Private Sub Workbook_Open()
      Application.OnKey "{F9}", "Recalc"
  End Sub

このサブルーチンを標準モジュールに追加します。Add this subroutine to a standard module.

  Sub Recalc()
      Application.Calculate
      MsgBox "hello"
  End Sub

オートメーション アドイン (Excel 2002 以降のバージョン) のユーザー定義関数は統合エディターを使用しないので、Visual Basic Editor のオーバーヘッドは発生しません。User-defined functions in Automation add-ins (Excel 2002 and later versions) do not incur the Visual Basic Editor overhead because they do not use the integrated editor. オートメーション アドインの Visual Basic 6 ユーザー定義関数の他のパフォーマンス特性は VBA 関数と同様です。Other performance characteristics of Visual Basic 6 user-defined functions in Automation add-ins are similar to VBA functions.

ユーザー定義関数が範囲内の各セルを処理する場合は、入力を範囲として宣言し、配列を含む variant に代入して、それに対するループを行います。If your user-defined function processes each cell in a range, declare the input as a range, assign it to a variant that contains an array, and loop on that. 列全体の参照を効率的に処理するには、次の例に示すように、入力範囲のサブセットを作成し、使用する範囲と交差点に分割する必要があります。If you want to handle whole column references efficiently, you must make a subset of the input range, dividing it at its intersection with the used range, as in this example.

  Public Function DemoUDF(theInputRange as Range)
      Dim vArr as Variant
      Dim vCell as Variant
      Dim oRange as Range
      Set oRange=Union(theInputRange, theRange.Parent.UsedRange)
      vArr=oRange
      For Each vCell in vArr
          If IsNumeric(vCell) then DemoUDF=DemoUDF+vCell
      Next vCell
  End Function

ユーザー定義関数で、ワークシート関数または Excel オブジェクト モデル メソッドを使用して範囲を処理する場合は、一般に、範囲をオブジェクト変数として保持した方が、データを Excel からユーザー定義関数に転送するよりも効率的です。If your user-defined function is using worksheet functions or Excel object model methods to process a range, it is generally more efficient to keep the range as an object variable than to transfer all the data from Excel to the user-defined function.

  Function uLOOKUP(lookup_value As Variant, lookup_array As Range, _
                   col_num As Variant, sorted As Variant, _
                   NotFound As Variant)
      Dim vAnsa As Variant
      vAnsa = Application.VLookup(lookup_value, lookup_array, _
                                  col_num, sorted)
      If Not IsError(vAnsa) Then
          uLOOKUP = vAnsa
      Else
          uLOOKUP = NotFound
      End If
  End Function

ユーザー定義関数が計算チェーンの早い段階で呼び出された場合は、計算され引数として渡すことができます。If your user-defined function is called early in the calculation chain, it can be passed as uncalculated arguments. ユーザー定義関数の内部では、次の数式を含む空のセルに対して次のテストを使用して、計算されのセルを検出できます。Inside a user-defined function, you can detect uncalculated cells by using the following test for empty cells that contain a formula:

  If ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then

ユーザー定義関数を呼び出すたびに、および Excel から VBA へデータを転送するたびに、時間のオーバーヘッドが発生します。A time overhead exists for each call to a user-defined function and for each transfer of data from Excel to VBA. 場合によっては、1 つの複数セル配列数式のユーザー定義関数によって、複数の関数呼び出しを単一の関数 (結果の範囲を返す複数セル入力範囲を持つ) にまとめることで、これらのオーバーヘッドを最小限に抑えることができます。Sometimes one multi-cell array formula user-defined function can help you minimize these overheads by combining multiple function calls into a single function with a multi-cell input range that returns a range of answers.

合計および SUMIF 参照が含まれるセル範囲を最小化するMinimize range of cells that SUM and SUMIF reference

Excel の SUM および SUMIF 関数は、一般的に、大量のセルを対象として使用されます。The Excel SUM and SUMIF functions are frequently used over a large number of cells. これらの関数の計算時間は、対象となるセルの数に比例するので、関数で参照するセルの範囲は最小にします。Calculation time for these functions is proportionate to the number of cells covered, so try to minimize the range of cells that the functions are referencing.

ワイルドカードの SUMIF、COUNTIF、SUMIFS、COUNTIFS、およびその他の IFS 関数を使用するUse wildcard SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions

ワイルドカード文字を使用できますか。You can use the wildcard characters ? (任意の1文字) * 、または、 SUMIFSCOUNTIFS、およびその他のIFS関数の一部として****、アルファベット**** 範囲の条件に (文字または任意の数の文字を使用しない)。(any single character) and * (no character or any number of characters) in the criteria for alphabetical ranges as part of the SUMIF, COUNTIF, SUMIFS, COUNTIFS, and other IFS functions.

日付から日付までの合計および累積合計を指定するメソッドを選択するChoose method for period-to-date and cumulative SUMs

期間または累積合計を処理するには、2つの方法があります。There are two methods of doing period-to-date or cumulative SUMs. 累積的sumにする数値が列 A にあり、列 B に累積合計が格納されているとします。次のいずれかの操作を実行できます。Suppose the numbers that you want to cumulatively SUM are in column A, and you want column B to contain the cumulative sum; you can do either of the following:

  • 列 B に数式を作成=SUM($A$1:$A2)し、必要に応じてドラッグすることができます。You can create a formula in column B such as =SUM($A$1:$A2) and drag it down as far as you need. SUM の最初のセルは A1 に固定されていますが、最後のセルには行の相対参照が含まれるため、このセルの値は 1 行ずつ自動的に増加します。The beginning cell of the SUM is anchored in A1, but because the finishing cell has a relative row reference, it automatically increases for each row.

  • セル B1 および=$B1+$A2セル B2 に数式=$A1を作成し、必要に応じてドラッグすることができます。You can create a formula such as =$A1 in cell B1 and =$B1+$A2 in cell B2 and drag it down as far as you need. この計算では、行の番号を直前の累積 SUM に加算して累積セルを求めます。This calculates the cumulative cell by adding this row's number to the previous cumulative SUM.

たとえば、行数が 1,000 の場合、1 番目の方法では計算を約 500,000 回行いますが、2 番目の方法では約 2,000 回です。For 1,000 rows, the first method makes Excel do about 500,000 calculations, but the second method makes Excel do only about 2,000 calculations.

サブセットの合計を計算するCalculate subset sums

テーブルに複数の並べ替えられたインデックス (たとえば、[領域内のサイト]) がある場合は、 SUMまたはSUMIF関数で使用する行 (または列) のサブセット範囲のアドレスを動的に計算することで、多くの場合、計算時間を大幅に短縮できます。When you have multiple sorted indexes to a table (for example, Site within Area) you can often save significant calculation time by dynamically calculating the address of a subset range of rows (or columns) to use in the SUM or SUMIF function.

行または列のサブセット範囲のアドレスを計算するには、次のようにします。To calculate the address of a subset range of row or columns:

  1. 各サブセット ブロックの行数をカウントします。Count the number of rows for each subset block.

  2. 各ブロックの行数を累積的に加算して基準行を決定します。Add the counts cumulatively for each block to determine its start row.

  3. OFFSET に基準行と行数を指定して、行のサブセット ブロックのみを含む SUM または SUMIF にサブセット範囲を返します。Use OFFSET with the start row and count to return a subset range to the SUM or SUMIF that covers only the subset block of rows.

フィルター処理されたリストの小計を使用するUse SUBTOTAL for filtered lists

SUBTOTAL 関数を使用して、フィルター処理したリストに SUM を実行します。Use the SUBTOTAL function to SUM filtered lists. SUBTOTAL 関数は、SUM 関数とは異なり、次の要素が無視されるので便利です。The SUBTOTAL function is useful because, unlike SUM, it ignores the following:

  • リストのフィルター処理の結果、非表示になっている行。Hidden rows that result from filtering a list. Excel 2003 以降、SUBTOTAL はフィルター処理された行だけでなく、非表示の行もすべて無視します。Starting in Excel 2003, you can also make SUBTOTAL ignore all hidden rows, not just filtered rows.

  • 他の SUBTOTAL 関数。Other SUBTOTAL functions.

集計関数を使用するUse the AGGREGATE function

集計関数は、データを集計する19個の異なる方法 ( SUMMEDIAN百分位LARGEなど) を計算するための強力で効率的な方法です。The AGGREGATE function is a powerful and efficient way of calculating 19 different methods of aggregating data (such as SUM, MEDIAN, PERCENTILE and LARGE). 集計には、非表示またはフィルター処理された行、エラー値、およびネストされたSUBTOTAL関数とAGGREGATE関数を無視するオプションがあります。AGGREGATE has options for ignoring hidden or filtered rows, error values, and nested SUBTOTAL and AGGREGATE functions.

dfunctions の使用を避けるAvoid using DFunctions

D 関数の DSUMDCOUNTDAVERAGE などは、同等の配列数式よりもはるかに高速です。The DFunctions DSUM, DCOUNT, DAVERAGE, and so on are significantly faster than equivalent array formulas. ただし、D 関数の欠点として、条件を別の範囲に指定する必要があり、それが、多くの状況で D 関数を使用および保守することは現実的でないと判断される原因となっています。The disadvantage of the DFunctions is that the criteria must be in a separate range, which makes them impractical to use and maintain in many circumstances. Excel 2007 以降では、D 関数ではなく、SUMIFSCOUNTIFS、および AVERAGEIFS 関数を常に使用する必要があります。Starting in Excel 2007, you should use SUMIFS, COUNTIFS, and AVERAGEIFS functions instead of the DFunctions.

より高速な VBA マクロを作成するCreate faster VBA macros

次のヒントを使用して、より高速な VBA マクロを作成します。Use the following tips to create faster VBA macros.

コードが実行されている間は essentials 以外のすべての機能をオフにするTurn off everything but the essentials while code is running

VBA マクロのパフォーマンスを向上させるには、コードの実行中に必要でない機能を明示的に無効にします。To improve performance for VBA macros, explicitly turn off the functionality that is not required while your code executes. 多くの場合、コードを実行した後に再計算するか再描画するだけで、パフォーマンスを向上させることができます。Often, one recalculation or one redraw after your code runs is all that is necessary and can improve performance. コードを実行した後、機能を元の状態に復元します。After your code executes, restore the functionality to its original state.

次の機能は、通常、VBA マクロの実行中に無効にできます。The following functionality can usually be turned off while your VBA macro executes:

  • アプリケーション画面更新画面の更新をオフにします。Application.ScreenUpdating Turn off screen updating. Application. 表示の更新がFalseに設定されている場合、Excel で画面は再描画されません。If Application.ScreenUpdating is set to False, Excel does not redraw the screen. コードが実行されている間、画面はすぐに更新されるため、通常はユーザーがそれぞれの更新を確認する必要はありません。While your code runs, the screen updates quickly, and it is usually not necessary for the user to see each update. このコードを実行した後に画面を1回更新すると、パフォーマンスが向上します。Updating the screen once, after the code executes, improves performance.

  • Application displaystatusbarステータスバーをオフにします。Application.DisplayStatusBar Turn off the status bar. Application displaystatusbarFalseに設定されている場合、Excel はステータスバーを表示しません。If Application.DisplayStatusBar is set to False, Excel does not display the status bar. 画面が更新されていない場合でも、現在の操作の状態を表示できるように、ステータスバーの設定は画面更新設定とは別のものです。The status bar setting is separate from the screen updating setting so that you can still display the status of the current operation even while the screen is not updating. ただし、すべての操作の状態を表示する必要がない場合は、ステータスバーをオフにして、コードの実行中にパフォーマンスを向上させることができます。However, if you do not need to display the status of every operation, turning off the status bar while your code runs also improves performance.

  • 計算方法手動計算に切り替えます。Application.Calculation Switch to manual calculation. 計算xlcalculation ationmanualに設定されている場合、Excel は、ユーザーが明示的に計算を開始したときにのみブックを計算します。If Application.Calculation is set to xlCalculationManual, Excel only calculates the workbook when the user explicitly initiates the calculation. 自動計算モードの場合、Excel は計算するタイミングを決定します。In automatic calculation mode, Excel determines when to calculate. たとえば、数式に関連付けられたセル値が変更されるたびに、Excel によって数式が再計算されます。For example, every time a cell value that is related to a formula changes, Excel recalculates the formula. 計算モードを手動に切り替えた場合は、ブックを再計算する前に、式に関連付けられているすべてのセルが更新されるまで待つことができます。If you switch the calculation mode to manual, you can wait until all the cells associated with the formula are updated before recalculating the workbook. コードの実行中にブックを再計算する場合は、必要に応じて、パフォーマンスを向上させることができます。By only recalculating the workbook when necessary while your code runs, you can improve performance.

  • Application enableeventsはイベントをオフにします。Application.EnableEvents Turn off events. Application enableeventsFalseに設定されている場合、Excel はイベントを発生させません。If Application.EnableEvents is set to False, Excel does not raise events. Excel イベントをリッスンしているアドインがある場合、それらのアドインはイベントを記録するときにコンピューター上のリソースを消費します。If there are add-ins listening for Excel events, those add-ins consume resources on the computer as they record the events. コードの実行中に発生するイベントをアドインが記録する必要がない場合は、イベントを無効にするとパフォーマンスが向上します。If it is not necessary for the add-in to record the events that occur while your code runs, turning off events improves performance.

  • ActiveSheet DisplayPageBreaks改ページをオフにします。ActiveSheet.DisplayPageBreaks Turn off page breaks. ActiveSheetFalseに設定されている場合、Excel には改ページが表示されません。If ActiveSheet.DisplayPageBreaks is set to False, Excel does not display page breaks. 改ページの計算はコードの実行後に行うようにしてパフォーマンスの向上に努めます。It is not necessary to recalculate page breaks while your code runs, and calculating the page breaks after the code executes improves performance.

重要

コードの実行後は、この機能を元の状態に戻してください。Remember to restore this functionality to its original state after your code executes.

次の例は、VBA マクロの実行中に無効にできる機能を示しています。The following example shows the functionality that you can turn off while your VBA macro executes.

  ' Save the current state of Excel settings.
  screenUpdateState = Application.ScreenUpdating
  statusBarState = Application.DisplayStatusBar
  calcState = Application.Calculation
  eventsState = Application.EnableEvents
  ' Note: this is a sheet-level setting.
  displayPageBreakState = ActiveSheet.DisplayPageBreaks 

  ' Turn off Excel functionality to improve performance.
  Application.ScreenUpdating = False
  Application.DisplayStatusBar = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Note: this is a sheet-level setting.
  ActiveSheet.DisplayPageBreaks = False

  ' Insert your code here.

  ' Restore Excel settings to original state.
  Application.ScreenUpdating = screenUpdateState
  Application.DisplayStatusBar = statusBarState
  Application.Calculation = calcState
  Application.EnableEvents = eventsState
  ' Note: this is a sheet-level setting
  ActiveSheet.DisplayPageBreaks = displayPageBreaksState

1回の操作で大量のデータブロックの読み取りと書き込みを行うRead and write large blocks of data in a single operation

Excel とコードの間でデータを転送する回数を明示的に減らしてコードを最適化します。Optimize your code by explicitly reducing the number of times data is transferred between Excel and your code. 複数のセルを 1 つずつループで反復処理して値を取得または設定するのではなく、2 次元配列を含む Variant を使用して必要に応じて値を格納することで、セルの範囲全体から 1 行で値を取得または設定します。Instead of looping through cells one at a time to get or set a value, get or set the values in the entire range of cells in one line, using a variant containing a two-dimensional array to store values as needed. 次のコード例でこれら 2 つの方法を比較します。The following code examples compare these two methods.

次のコード例は、一度に1つのセルをループ処理して、セル A1: C10000 の値を取得および設定する、最適化されていないコードを示しています。The following code example shows non-optimized code that loops through cells one at a time to get and set the values of cells A1:C10000. これらのセルには数式は含まれません。These cells do not contain formulas.

  Dim DataRange as Range
  Dim Irow as Long
  Dim Icol as Integer 
  Dim MyVar as Double 
  Set DataRange=Range("A1:C10000") 

  For Irow=1 to 10000 
      For icol=1 to 3
          ' Read the values from the Excel grid 30,000 times.
          MyVar=DataRange(Irow,Icol) 
          If MyVar > 0 then 
              ' Change the value.
              MyVar=MyVar*Myvar 
              ' Write the values back into the Excel grid 30,000 times.
              DataRange(Irow,Icol)=MyVar
          End If 
      Next Icol 
  Next Irow

次のコード例は、配列を使用して、セル範囲 A1: C10000 all の値を同時に取得および設定する最適化されたコードを示しています。The following code example shows optimized code that uses an array to get and set the values of cells A1:C10000 all at the same time. これらのセルには数式は含まれません。These cells do not contain formulas.

  Dim DataRange As Variant
  Dim Irow As Long 
  Dim Icol As Integer 
  Dim MyVar As Double 
  ' Read all the values at once from the Excel grid and put them into an array.
  DataRange = Range("A1:C10000").Value2 

  For Irow = 1 To 10000 
      For Icol = 1 To 3 
          MyVar = DataRange(Irow, Icol) 
          If MyVar > 0 Then 
              ' Change the values in the array.
              MyVar=MyVar*Myvar 
              DataRange(Irow, Icol) = MyVar 
          End If 
      Next Icol 
  Next Irow 
  ' Write all the values back into the range at once.
  Range("A1:C10000").Value2 = DataRange 

使え.ではなく Value2。値または。Excel の範囲からデータを読み取るときのテキストUse .Value2 rather than .Value or .Text when reading data from an Excel range

  • .文字列は、セルの書式設定された値を返します。.Text returns the formatted value of a cell. これは低速で、ユーザーがズームし、精度を失う可能性がある場合に、# # # を返すことができます。This is slow, can return ### if the user zooms, and can lose precision.
  • .Valueは、日付または通貨の形式で指定されている場合に、vba の通貨または vba の日付変数を返します。.Value returns a VBA currency or VBA date variable if the range was formatted as Date or Currency. これは低速で、精度が失われる可能性があり、ワークシート関数を呼び出すときにエラーが発生する可能性があります。This is slow, can lose precision, and can cause errors when calling worksheet functions.
  • .Value2が高速で、Excel から取得されるデータが変更されません。.Value2 is fast and does not alter the data being retrieved from Excel.

オブジェクトの選択とアクティブ化を回避するAvoid selecting and activating objects

オブジェクトを選択してアクティブにすると、オブジェクトを直接参照するよりも、処理上の負荷が増加します。Selecting and activating objects is more processing intensive than referencing objects directly. 範囲図形などのオブジェクトを直接参照することによって、パフォーマンスを向上させることができます。By referencing an object such as a Range or a Shape directly, you can improve performance. 次のコード例で 2 つの方法を比較します。The following code examples compare the two methods.

次のコード例は、アクティブシートで各図形を選択し、テキストを "Hello" に変更する最適化されていないコードを示しています。The following code example shows non-optimized code that selects each Shape on the active sheet and changes the text to "Hello".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).Select
      Selection.Text = "Hello"
  Next i

次のコード例は、各図形を直接参照し、テキストを "Hello" に変更する最適化されたコードを示しています。The following code example shows optimized code that references each Shape directly and changes the text to "Hello".

  For i = 0 To ActiveSheet.Shapes.Count
      ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
  Next i

これらの追加 VBA パフォーマンスの最適化を使用するUse these additional VBA performance optimizations

次に示すパフォーマンスの最適化は VBA コードで利用できます。The following is a list of additional performance optimizations you can use in your VBA code:

  • 配列を Range に直接代入して結果を返します。Return results by assigning an array directly to a Range.

  • 変数を宣言するときは、データ型を明示的に指定します。データ型を明示的に指定すれば、データ型を判断するオーバーヘッドは発生しません。このオーバーヘッドは、コードの実行中にループ内で何回も発生することがあります。Declare variables with explicit types to avoid the overhead of determining the data type, possibly multiple times in a loop, during code execution.

  • コード内で頻繁に使用するシンプルな関数は、WorksheetFunction オブジェクトを使用するのではなく、VBA によって自分で実装します。For simple functions that you use frequently in your code, implement the functions yourself in VBA instead of using the WorksheetFunction object. 詳細については、「 VBA ユーザー定義関数を高速に使用する」を参照してください。For more information, see Use faster VBA user-defined functions.

  • Range.SpecialCells メソッドを使用して、コードで操作するセルの数を詳しく調べます。Use the Range.SpecialCells method to scope down the number of cells with which your code interacts.

  • XLL SDK の C API を使用して機能を実装した場合は、パフォーマンスの向上を考慮してください。Consider the performance gains if you implemented your functionality by using the C API in the XLL SDK. 詳細については、 Excel 2010 XLL SDK のドキュメントを参照してください。For more information, see the Excel 2010 XLL SDK Documentation.

Excel ファイル形式のパフォーマンスとサイズを考慮するConsider performance and size of Excel file formats

Excel 2007 以降、以前のバージョンと比べて、多種多様なファイル形式を利用できるようになりました。Starting in Excel 2007, Excel contains a wide variety of file formats compared to earlier versions. マクロ、テンプレート、アドイン、PDF、XPS ファイル形式のバリエーションを無視すると、3つの主要な形式は XLS、.xlsb、および .xlsx になります。Ignoring the Macro, Template, Add-in, PDF, and XPS file format variations, the three main formats are XLS, XLSB, and XLSX.

  • XLS 形式XLS format

    XLS 形式は以前のバージョンと同じ形式です。The XLS format is the same format as earlier versions. この形式で使用できる列数は 256、行数は 65,536 に制限されています。When you use this format, you are restricted to 256 columns and 65,536 rows. Excel 2007 または Excel 2010 のブックを XLS 形式で保存すると、互換性チェックが実行されます。When you save an Excel 2007 or Excel 2010 workbook in XLS format, Excel runs a compatibility check. ファイル サイズは以前のバージョンとほとんど変わりませんが (一部の追加情報が格納される場合もあります)、パフォーマンスは以前のバージョンよりも少し低下します。File size is almost the same as earlier versions (some additional information may be stored), and performance is slightly slower than earlier versions. セルの計算順序について Excel が行うマルチスレッド最適化は XLS 形式では保存されません。Any multi-threaded optimization Excel does with respect to cell calculation order is not saved in the XLS format. したがって、XLS 形式で保存し、閉じて、再度開いた後のブックの計算は低速になる可能性があります。Therefore, calculation of a workbook can be slower after saving the workbook in the XLS format, closing, and re-opening the workbook.

  • .xlsb 形式XLSB format

    XLSB は Excel 2007 以降で使用できるバイナリ形式です。XLSB is the binary format starting in Excel 2007. 多くのバイナリファイルを含む圧縮フォルダーとして構成されています。It is structured as a compressed folder that contains many binary files. XLS 形式よりもはるかにコンパクトですが、圧縮量はブックの内容によって異なります。It is much more compact than the XLS format, but the amount of compression depends on the contents of the workbook. たとえば、10 個のブックは 2 ~ 8 までのサイズ削減係数を示し、平均削減係数は 4 です。For example, ten workbooks show a size reduction factor ranging from two to eight with an average reduction factor of four. Excel 2007 以降では、ファイルを開く、および保存するときのパフォーマンスが XLS 形式よりも少しだけ低下しています。Starting in Excel 2007, opening and saving performance is only slightly slower than the XLS format.

  • .xlsx 形式XLSX format

    XLSX は Excel 2007 以降で使用できる XML 形式で、Excel 2007 以降の既定のファイル形式です。XLSX is the XML format starting in Excel 2007, and is the default format starting in Excel 2007. .xlsx 形式は、多くの XML ファイルを含む圧縮フォルダーです (ファイル名拡張子を .zip に変更した場合は、圧縮フォルダーを開いてそのコンテンツを調べることができます)。The XLSX format is a compressed folder that contains many XML files (if you change the file name extension to .zip, you can open the compressed folder and examine its contents). 一般に、XLSX 形式では XLSB 形式よりも多くのファイルが作成されますが (平均 1.5 倍)、ファイル数が多いにもかかわらず、サイズは XLS 形式よりも大幅に小さくなります。Typically, the XLSX format creates larger files than the XLSB format (1.5 times larger on average), but they are still significantly smaller than the XLS files. ファイルを開く、および保存するときの時間は XLSB ファイルよりも少し長くなります。You should expect opening and saving times to be slightly longer than for XLSB files.

ブックを開く、閉じる、および保存するOpen, close, and save workbooks

ブックを開く、閉じる、および保存する速度が、ブックの計算時よりも大幅に低下することがあります。You may find that opening, closing, and saving workbooks is much slower than calculating them. 一般的には、それだけブックのサイズが大きいことが理由として考えられますが、別の理由が存在することもあります。Sometimes this is just because you have a large workbook, but there can also be other reasons.

1 つ以上のブックを開く、および閉じる場合に、極端に時間がかかるときは、次のいずれかの原因が考えられます。If one or more of your workbooks open and close more slowly than is reasonable, it might be caused by one of the following issues.

  • 一時ファイルTemporary files

    一時ファイルは、windows の\\一時ディレクトリ (windows 95、windows 98、および windows ME の場合)、または\ドキュメントと設定\のユーザー\名ローカル\設定の一時ディレクトリに蓄積されることがあります (windows 2000 およびWindows XP)。Temporary files can accumulate in your \Windows\Temp directory (in Windows 95, Windows 98, and Windows ME), or your \Documents and Settings\User Name\Local Settings\Temp directory (in Windows 2000 and Windows XP). Excel では、ブックおよび開いているブックで使用されるコントロールに対して、これらのファイルが作成されます。Excel creates these files for the workbook and for controls that are used by open workbooks. また、ソフトウェア インストール プログラムも一時ファイルを作成します。Software installation programs also create temporary files. Excel が何らかの理由で応答を停止したときは、一時ファイルを削除することが必要となる場合があります。If Excel stops responding for any reason, you might need to delete these files.

    一時的なファイルが多すぎると問題が発生する可能性があるので、削除する必要があります。ただし、コンピューターを再起動する必要があるソフトウェアをインストールしていて、まだ実行していない場合は、一時ファイルを削除する前に再起動する必要があります。Too many temporary files can cause problems, so you should occasionally clean them out. However, if you have installed software that requires that you restart your computer, and you have not yet done so, you should restart before deleting the temporary files.

 Windows の [スタート] メニューから、[スタート]、[実行] の順にクリックして、一時ディレクトリを簡単に開くことができます。An easy way to open your temp directory is from the Windows Start menu: Click Start, and then click Run. テキストボックスに % temp% と入力し、[ OK] をクリックします。In the text box, type %temp%, and then click OK.

  • 共有ブックの変更を追跡するTracking changes in a shared workbook

    共有ブックの変更履歴を記録すると、ブックのファイルサイズが急速に増加します。Tracking changes in a shared workbook causes your workbook file-size to increase rapidly.

  • 断片化されたスワップファイルFragmented swap file

    Windows スワップ ファイルが配置されているディスクに十分な空き容量があり、ディスクが定期的に最適化されていることを確認します。Be sure that your Windows swap file is located on a disk that has a lot of space and that you defragment the disk periodically.

  • パスワードで保護された構造を持つブックWorkbook with password-protected structure

    構造がパスワードで保護されているブック ([ツール] メニューの > Protection > 保護ブックの > を入力します。オプションのパスワードを入力してください) は、オプションのパスワードを入力せずに保護されたものよりもずっと低速読み取り.A workbook that has its structure protected with a password (Tools menu > Protection > Protect Workbook > enter the optional password) opens and closes much slower than one that is protected without the optional password.

  • 使用範囲の問題Used range problems

    使用範囲のサイズが大きすぎると、特に、非表示の行や列の高さまたは幅が基準外であることが原因で使用範囲のサイズが増大している場合は、開くのに時間がかかったり、ファイル サイズが増加したりすることがあります。Oversized used ranges can cause slow opening and increased file size, especially if they are caused by hidden rows or columns that have non-standard height or width. 使用範囲の問題の詳細については、「使用範囲を最小化する」を参照してください。For more information about used range problems, see Minimize the used range.

  • ワークシート上のコントロール数が多いLarge number of controls on worksheets

    ワークシートで使用するコントロール (チェック ボックス、ハイパーリンクなど) の数が多くなると、一時ファイルの使用数に起因して、ブックを開くのに時間がかかることがあります。A large number of controls (check boxes, hyperlinks, and so on) on worksheets can slow down opening a workbook because of the number of temporary files that are used. また、一時ファイルの使用数は、WAN 上で (LAN 上でも) ブックを開いたり保存したりするときの問題の原因となることもあります。This might also cause problems opening or saving a workbook on a WAN (or even a LAN). こうした問題が発生する場合は、ブックの設計の見直しを検討する必要があります。If you have this problem, you should consider redesigning your workbook.

  • 他のブックへのリンク数が多いLarge number of links to other workbooks

    可能であれば、リンク先のブックを開いてから、リンク元のブックを開きます。If possible, open the workbooks that you are linking to before you open the workbook that contains the links. 通常、ブックを開く方が、閉じているブックからリンクを読み取るよりも高速です。Often it is faster to open a workbook than to read the links from a closed workbook.

  • ウイルス検索プログラムの設定Virus scanner settings

    ウイルススキャナーの設定によっては、特にサーバー上で開く、閉じる、または保存したときに問題が発生したり、遅くなったりすることがあります。Some virus scanner settings can cause problems or slowness with opening, closing, or saving, especially on a server. この問題が発生する可能性がある場合は、一時的にウイルス検索プログラムをオフにしてみてください。If you think that this might be the problem, try temporarily switching the virus scanner off.

  • 時間のかかる計算が原因でオープンと保存が遅くなるSlow calculation causing slow open and save

    場合によっては、ブックを開く、または保存するときに、ブックが再計算されることがあります。Under some circumstances, Excel recalculates your workbook when it opens or saves it. ブックの計算時間が長く、問題が発生している場合は、計算が [手動] に設定されていることを確認し、[保存の前に計算する] オプションをオフにすることを検討してください (ツール > オプション > 計算) を行います。If the calculation time for your workbook is long and is causing a problem, ensure that you have calculation set to manual, and consider turning off the calculate before save option (Tools > Options > Calculation).

  • ツールバーファイル (. xlb)Toolbar files (.xlb)

    ツール バー ファイルのサイズを確認します。Check the size of your toolbar file. 一般的なツール バー ファイルのサイズは 10 ~ 20 KB です。A typical toolbar file is between 10 KB and 20 KB. XLB ファイルは、Windows search を使用し*.xlbて検索することによって検索できます。You can find your XLB files by searching for *.xlb by using Windows search. ユーザーごとに一意の XLB ファイルがあります。Each user has a unique XLB file. ツール バーを追加、変更、またはカスタマイズすると、toolbar.xlb ファイルのサイズが増加します。Adding, changing, or customizing toolbars increases the size of your toolbar.xlb file. このファイルを削除すると、ツール バーのカスタマイズはすべて削除されます (ファイル名を "toolbar.OLD" にすると安心です)。Deleting the file removes all your toolbar customizations (renaming it "toolbar.OLD" is safer). 次回 Excel を開くと、新しい XLB ファイルが作成されます。A new XLB file is created the next time you open Excel.

追加のパフォーマンスの最適化を行うMake additional performance optimizations

次の領域でパフォーマンスを向上させることができます。You can make performance improvements in the following areas.

  • PivotTablesPivotTables

    ピボットテーブルを使用すると、大量のデータを効率的に要約できます。PivotTables provide an efficient way to summarize large amounts of data.

    • 最終結果としての合計Totals as final results. ブックの最終結果として、合計と小計を求める必要がある場合は、ピボットテーブルを使用します。If you need to produce totals and subtotals as part of the final results of your workbook, try using PivotTables.

    • 中間結果としての合計Totals as intermediate results. ピボットテーブルを使用すると、概要レポートを効果的に作成できますが、次の条件を満たすことを確認できない限り、計算チェーンで、ピボットテーブルの結果を中間合計として使用する数式は作成しないでください。PivotTables are a great way to produce summary reports, but try to avoid creating formulas that use PivotTable results as intermediate totals and subtotals in your calculation chain unless you can ensure the following conditions:

    • 計算中、ピボットテーブルが正しく更新されている。The PivotTable has been refreshed correctly during the calculation.

    • ピボットテーブルは変更されていないが、それでも情報は表示されている。The PivotTable has not been changed so that the information is still visible.

    それでも、ピボットテーブルを中間結果として使用したい場合は、GETPIVOTDATA 関数を使用します。If you still want to use PivotTables as intermediate results, use the GETPIVOTDATA function.

  • 条件付き書式とデータの入力規則Conditional formats and data validation

    条件付き書式とデータの入力規則は便利ですが、多用すると、計算速度が大きく低下する原因となります。Conditional formats and data validation are great, but using a lot of them can significantly slow down calculation. セルが表示されている場合は、すべての条件付き書式の数式が計算のたびに評価され、条件付き書式を含むセルの表示が更新されます。If the cell is displayed, every conditional format formula is evaluated at each calculation and when the display of the cell that contains the conditional format is refreshed. Excel オブジェクト モデルには Worksheet.EnableFormatConditionsCalculation プロパティがあり、条件付き書式の計算を有効または無効にできます。The Excel object model has a Worksheet.EnableFormatConditionsCalculation property so that you can enable or disable the calculation of conditional formats.

  • 定義された名前Defined names

    定義名は Excel の中で最も強力な機能の 1 つですが、定義名を使用すると、計算時間が長くなります。Defined names are one of the most powerful features in Excel, but they do take additional calculation time. 他のワークシートを参照する名前を使用すると、計算プロセスはより複雑になります。Using names that refer to other worksheets adds an additional level of complexity to the calculation process. また、入れ子になった名前 (他の名前を参照する名前) の使用も避ける必要があります。Also, you should try to avoid nested names (names that refer to other names).

    数式によって参照されている名前は、その数式が計算されるたびに計算されるため、計算量が多い数式や関数を定義名に含めないようにする必要があります。Because names are calculated every time a formula that refers to them is calculated, you should avoid putting calculation-intensive formulas or functions in defined names. このようなケースでは、計算量が多い数式や関数を別の場所の予備のセルに入れて、そのセルを参照すると、大幅に高速になる可能性があります (数式や関数を直接参照したり、名前を使用したりしません)。In these cases, it can be significantly faster to put your calculation-intensive formula or function in a spare cell somewhere and refer to that cell instead, either directly or by using a name.

  • たまにのみ使用される数式Formulas that are used only occasionally

    多くのブックに、数式やルックアップが大量に含まれていることがあり、計算用の適切な形状に入力データを挿入することに関係するものや、データのサイズや形状の変更を防ぐ対策として使用されるものがあります。Many workbooks contain a significant number of formulas and lookups that are concerned with getting the input data into the appropriate shape for the calculations, or are being used as defensive measures against changes in the size or shape of the data. 使用頻度が低い数式のブロックがある場合は、特殊な値をコピーおよび貼り付けて数式を一時的に排除することや、滅多に開かないブックに数式を移動することができます。When you have blocks of formulas that are used only occasionally, you can copy and paste special values to temporarily eliminate the formulas, or you can put them in a separate, rarely opened workbook. 数式が値に変換されたことに気付かなくて、ワークシート エラーが発生することが多くあるので、使用頻度が低い数式は別のブックに移動した方が望ましい場合があります。Because worksheet errors are often caused by not noticing that formulas have been converted to values, the separate workbook method may be preferable.

  • 十分なメモリを使用するUse enough memory

    32ビット版の excel では、最大 2 gb の ram を使用することも、大規模なアドレス認識の32ビットバージョンの excel 2013 および2016に最大 4 gb の ram を使用することもできます。The 32-bit version of Excel can use up to 2 GB of RAM or up to 4 GB of RAM for Large Address Aware 32-bit versions of Excel 2013 and 2016. ただし、Excel を実行するコンピューターにもメモリ リソースは必要です。However, the computer that is running Excel also requires memory resources. したがって、メモリの一部はオペレーティング システムや他のプログラムに割り当てられるので、コンピューターに 2 GB の RAM しか搭載されていない場合は、Excel だけで 2 GB をすべて利用することはできせん。Therefore, if you only have 2 GB of RAM on your computer, Excel cannot take advantage of the full 2 GB because a portion of the memory is allocated to the operating system and other programs that are running. 32ビットコンピューターでの Excel のパフォーマンスを最適化するには、コンピューターに少なくとも 3 GB の RAM を搭載することをお勧めします。To optimize the performance of Excel on a 32-bit computer, we recommend that the computer have at least 3 GB of RAM.

    64ビットバージョンの Excel には、2 gb または最大 4 gb の制限はありません。The 64-bit version of Excel does not have a 2 GB or up to 4 GB limit. 詳細については、「 excel のパフォーマンス: パフォーマンスと制限の改善点」の「大規模なデータセットと64ビットバージョンの excel」セクションを参照してください。For more information, see the "Large data sets and the 64-bit version of Excel" section in Excel performance: Performance and limit improvements.

結論Conclusion

この記事では、リンク、ルックアップ、数式、関数、VBA コードなどの Excel 機能を最適化して、一般的な障害物を回避し、パフォーマンスを向上させる方法について説明します。This article covered ways to optimize Excel functionality such as links, lookups, formulas, functions, and VBA code to avoid common obstructions and improve performance.

関連項目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.