Excel のパフォーマンス: パフォーマンスの問題を最適化するヒント
適用対象: Excel |Excel 2013 |Office 2016 |Vba
Excel で頻繁に発生する多くのパフォーマンス障害を最適化するには、次のヒントに従ってください。
参照とリンクを最適化する
参照とリンクの種類に関連するパフォーマンスを向上させる方法について説明します。
前方参照と後方参照を使用しない
わかりやすさを高め、エラーを回避するには、数式が他の数式またはセルに前方 (右または下) に参照されないように設計します。 通常、前方参照は計算のパフォーマンスに影響しませんが、ブックを初めて計算するという極端なケースで、計算を後回しにする必要がある数式が多数存在すると、適切な計算順序を確立するのに時間がかかる可能性があります。
反復による循環参照の使用を最小限に抑える
反復を使用した循環参照の計算は、複数の計算が必要であり、これらの計算はシングル スレッドであるため、低速です。 多くの場合、反復計算が不要になるように代数を使用して循環参照を "アンロール" できます。 たとえば、キャッシュ フローと利子計算では、利子の前にキャッシュ フローを計算し、利息を計算してから、利息を含むキャッシュ フローを計算します。
Excel では、依存関係を考慮せずに、シートごとの循環参照を計算します。 したがって、循環参照が複数のワークシートにまたがっていると、通常は計算が遅くなります。 不要な計算を実行しないように、循環計算を単一のワークシートに移すか、ワークシートの計算順序を最適化してみます。
反復計算を開始する前に、Excel ではブックを再計算して、循環参照とその依存関係をすべて識別する必要があります。 この処理には、計算を 2 ～ 3 回繰り返すのと同じだけの負荷がかかります。
循環参照とその依存関係が識別された後、Excel は、計算のたびに、循環参照内のすべてのセルだけでなく、循環参照チェーン内のセルに依存するセルと、揮発性セルとその依存関係も含めて計算する必要があります。 循環参照内のセルに依存する複雑な計算がある場合は、その計算を別の閉じているブックに切り離して、循環計算の収束後に開いて再計算を行うと、計算時間を短縮できます。
循環計算のセル数と、これらのセルによって取得される計算時間を減らすことが重要です。
ブック間のリンクを避ける
可能な場合は、ブック間リンクを避けてください。それらは遅く、簡単に壊れ、常に簡単に見つけて修正できるわけではありません。
通常、より小さいブックを使用するよりも、より小さいブックを使用する方が良いとは限りません。 これに対する一部の例外は、あまり再計算されないフロントエンドの計算が多く、別のブックに配置するのが理にかなっている場合や、RAM が不足している場合です。
閉じたブックで動作する単純な直接セル参照を使用してください。 これにより、任意のブックを再計算するときに、リンクされているすべてのブックの再計算を回避できます。 また、Excel が閉じたブックから読み取った値を確認することもできます。これは、ブックのデバッグと監査に頻繁に重要です。
ブック間のリンクをどうしても使用する必要がある場合は、閉じているブックではなく、ブックをすべて開くようにします。また、ブックを開くときは、リンク先のブックを開いてから、リンク元のブックを開いてください。
ワークシート間のリンクを最小化する
多数のワークシートを使用すると、ブックは使いやすくなりますが、一般に、他のワークシートへの参照を計算する方が、ワークシート内の参照を計算するよりも低速になります。
使用範囲を最小限に抑える
メモリを節約し、ファイル サイズを小さくするために、Excel は、使用されたワークシート上の領域に関する情報のみを格納しようとします。 この領域は "使用範囲" と呼ばれます。 場合によっては、現在の使用範囲と見なしている範囲を大幅に越えた場所で、さまざまな編集操作や書式設定操作を行っていることがあります。 こうしたことが原因で、パフォーマンスやファイルサイズ上の障害となることがあります。
Ctrl + End キーを使用して、ワークシートに表示される使用範囲をチェックできます。 これが過剰な場合は、下のすべての行と列を削除し、最後に使用した実際のセルの右側に移動し、ブックを保存することを検討する必要があります。 最初にバックアップ コピーを作成します。 このときに削除する領域が、数式に伴う範囲に含まれていたり、その範囲から参照されていたりすると、その範囲はサイズが縮小されるか、#N/A に変更されます。
追加データを許可する
データの行や列をワークシートに頻繁に追加する場合は、データを追加するたびに数式を探して変更するのではなく、新しいデータ領域を自動参照するように数式を設定する方法が必要です。
これを行うには、現在のデータ境界をはるかに超える範囲の数式を使用します。 ただし、特定の状況では計算が非効率的になる可能性があり、行と列を削除すると気付かなくても範囲が減少する可能性があるため、維持が困難です。
構造化テーブル参照を使用する (推奨)
Excel 2007 以降では、構造化テーブル参照を使用できます。これにより、参照先テーブルのサイズが増減すると、自動的に拡張および縮小されます。
これにはいくつかの利点があります。
列全体の参照と動的範囲の代替方法よりも、パフォーマンスの欠点が少なくなります。
1 つのワークシートに複数のデータ テーブルを含めるのは簡単です。
テーブルに埋め込まれた数式も、データに合わせて拡張および縮小します。
または、列と行の参照全体を使用します
別の方法として、列参照全体 ( 例: $A:$A) を使用します。 この参照は、列 A のすべての行を返します。そのため、必要なだけデータを追加でき、参照には常にデータが含まれます。
これには長所も短所もあります。
Excel の多数の組み込み関数 (SUM、SUMIF) は、列内の最後に使用された行を自動認識するので、全列の参照を効率的に計算します。 一方で、SUMPRODUCT などの配列計算関数は、全列の参照を処理することも、列内のすべてのセルを計算することもできません。
ユーザー定義関数は、列の最後に使用された行を自動的に認識しないため、列全体の参照を効率的に計算しない場合が多くなります。 ただし、最後に使用された行を認識するようにユーザー定義関数をプログラムするのは簡単です。
1 つのワークシートに複数のデータ テーブルがある場合、列参照全体を使用するのは困難です。
Excel 2007 以降のバージョンでは、配列数式は列全体の参照を処理できますが、これにより、列内のすべてのセル (空のセルを含む) の計算が強制されます。 これは、特に 100 万行の場合、計算に時間がかかる場合があります。
または、動的範囲を使用します。
名前付き範囲の定義で OFFSET 関数または INDEX 関数と COUNTA 関数を使用すると、名前付き範囲が動的に拡張および縮小することを参照する領域を作成できます。 たとえば、次のいずれかの数式を使用して定義された名前を作成します。
=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)
動的範囲の名前を数式で使用すると、新しい項目を含むように自動的に拡張されます。
OFFSET には再計算のたびに計算される揮発性関数であるという欠点があるため、動的範囲に対して INDEX数式を使用することをお勧めします。
動的範囲の数式内の COUNTA 関数は多数の行を調べる必要があるため、パフォーマンスが低下します。 このパフォーマンスの低下を最小限に抑えるには、数式の COUNTA 部分を別のセルまたは定義された名前に格納し、そのセルまたは名前をダイナミック レンジで参照します。
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 は揮発性であり、常にシングル スレッドを計算します。
動的範囲の長所と短所は次のとおりです。
動的範囲は、配列数式によって実行される計算数を適切に制限します。
1 つの列内で複数の動的範囲を使用するには、特殊な目的のカウント関数が必要です。
動的範囲を多数使用すると、パフォーマンスが低下する可能性があります。
ルックアップの計算時間を短縮する
Office 365 バージョン 1809 以降では、並べ替えなしのデータ上での完全一致について、同じテーブル範囲から複数の列 (または HLOOKUP を含む行) を検索するときの Excel の VLOOKUP、HLOOKUP、MATCH の速度が以前より大幅に改善されました。
とはいえ、以前のバージョンの Excel では、ルックアップは引き続き頻繁に重要な計算の障害となります。 幸い、ルックアップの計算時間はさまざまな方法によって短縮できます。 完全一致オプションを使用すると、関数の計算時間は、一致が見つかるまでにスキャンされるセルの数に比例します。 ルックアップの範囲が拡大すると、計算時間が大幅に増加する可能性があります。
並べ替えられたデータに対して VLOOKUP、HLOOKUP、および MATCH の近似一致オプションを使用すると、ルックアップはすばやく行われ、ルックアップする範囲の長さによってルックアップ時間が大幅に増加することはありません。 特性はバイナリ検索と同じです。
ルックアップ オプションについて
MATCH、VLOOKUP、HLOOKUP の一致型および範囲参照オプションを理解していることを確認します。
次のコード例は、MATCH 関数の構文です。 詳細については、WorksheetFunction オブジェクトの Match メソッドを参照してください。
MATCH(lookup value, lookup array, matchtype)
Matchtype=1 は、ルックアップ配列が昇順 (近似一致) で並べ替えられた場合に、ルックアップ値以下の最大一致を返します。 ルックアップ配列が昇順で並べ替えられない場合、MATCH は正しくない回答を返します。 既定のオプションは、 昇順で並べ替えられた近似一致です。
Matchtype=0 は、完全一致を要求し、データは並べ替えられていないことを想定します。
Matchtype=-1 は、ルックアップ配列が降順で並べ替えられている場合に、ルックアップ値以上の最小一致を返します (近似一致)。
次のコード例は、VLOOKUP および HLOOKUP 関数の構文です。 詳細については、WorksheetFunction オブジェクトの VLOOKUP および HLOOKUP メソッドを参照してください。
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=FALSE は、完全一致を要求し、データは並べ替えられていないことを想定します。
並べ替えられていないデータのルックアップは低速なるので、できるだけルックアップを実行しないでください。 データが並べ替えられても完全に一致する場合は、「 欠損値を持つ並べ替えられたデータに 2 つの参照を使用する」を参照してください。
VLOOKUP の代わりに INDEX と MATCH または OFFSET を使用する
VLOOKUP ではなく、INDEX および MATCH 関数を使用するようにします。 VLOOKUP の方が若干高速 (約 5% 高速) で、MATCH と INDEX(OFFSET) の組み合わせよりも単純で、メモリ使用量が少なくなりますが、MATCH と INDEX で提供される柔軟性を高めれば、多くの場合、時間を大幅に節約できます。 たとえば、完全一致を指定した MATCH の結果をセルに保管しておいて、複数の INDEX ステートメントで再使用できます。
INDEX 関数は高速で、再計算を高速化する不揮発性関数です。 OFFSET 関数も高速です。ただし、揮発性関数であり、計算チェーンの処理に要する時間が大幅に増加することがあります。
VLOOKUP を INDEX と MATCH に変換するのは簡単です。 次の 2 つのステートメントは、同じ回答を返します。
VLOOKUP(A1, Data!$A$2:$F$1000,3,False)
INDEX(Data!$A$2:$F$1000,MATCH(A1,$A$1:$A$1000,0),3)
ルックアップを高速化する
完全一致ルックアップは低速になることがあるので、次のオプションによってパフォーマンスを改善することを検討します。
ワークシートは 1 つだけ使用します。 ルックアップとデータを同じシートに保持する方が高速です。
可能であれば、最初に SORT によってデータを並べ替えてから (SORT は高速)、近似一致を使用します。
完全一致ルックアップを使用する必要がある場合は、スキャンするセルの範囲を最小にします。 多数の行または列を参照するのではなく、テーブルと構造化参照またはダイナミック レンジ名を使用します。 場合によっては、ルックアップの範囲の下限と上限を事前に計算することもできます。
欠損値を持つ並べ替えられたデータに対して 2 つの参照を使用する
2 つの近似一致は、数行を超える参照の 1 つの完全一致よりも大幅に高速です。 (損益分岐点は約 10 から 20 行です)。
データを並べ替えても、検索対象の値が参照範囲に存在することを確認できないため、近似一致を使用できない場合は、次の式を使用できます。
IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val, _
VLOOKUP(lookup_val, lookup_array, column, True), "notexist")
この数式の最初の部分は、ルックアップ列自体を近似ルックアップします。
VLOOKUP(lookup_val ,lookup_array,1,True)
次の数式を使用して、ルックアップ列の回答がルックアップ値と同じ (その場合は完全に一致する) 場合にチェックできます。
IF(VLOOKUP(lookup_val ,lookup_array,1,True)=lookup_val,
この数式が True を返す場合は、完全一致が見つかったため、もう一度近似検索を実行できますが、今度は目的の列から回答を返します。
VLOOKUP(lookup_val, lookup_array, column, True)
参照列の回答がルックアップ値と一致しなかった場合は、欠損値があり、数式は "notexist" を返します。
リスト内の最小値よりも小さな値をルックアップすると、エラーが発生します。 このエラーの処理には、IFERROR を使用するか、小さなテスト値をリストに追加します。
欠損値を持つソートされていないデータに IFERROR 関数を使用する
ソートされていないデータで完全一致検索を使用する必要があり、ルックアップ値が存在するかどうかわからない場合は、一致するものが見つからない場合に返される #N/A を処理する必要があります。 Excel 2007 以降では、シンプルで高速な IFERROR 関数を使用できます。
IF IFERROR(VLOOKUP(lookupval, table, 2 FALSE),0)
Excel 2007 よりも前のバージョンでは、2 つのルックアップを含む IF 関数を使用します。この関数はシンプルですが、低速です。
IF(ISNA(VLOOKUP(lookupval,table,2,FALSE)),0,_
VLOOKUP(lookupval,table,2,FALSE))
完全一致ルックアップを 2 回使用することを回避できます。そのためには、MATCH で完全一致ルックアップを 1 回使用し、その結果をセルに格納し、その結果をテストしてから INDEX を実行します。
In A1 =MATCH(lookupvalue,lookuparray,0)
In B1 =IF(ISNA(A1),0,INDEX(tablearray,A1,column))
2 つのセルを使用できない場合は、COUNTIF を使用します。 一般に、完全一致検索よりも高速です。
IF (COUNTIF(lookuparray,lookupvalue)=0, 0, _
VLOOKUP(lookupval, table, 2 FALSE))
複数の列で完全一致検索に MATCH と INDEX を使用する
通常、完全一致を指定した MATCH を保管して何回でも再使用できます。 たとえば、複数の結果列に対して完全一致ルックアップを実行する場合は、1 つの MATCH と多数の INDEX ステートメントを使用した方が、多数の VLOOKUP ステートメントを使用するよりも時間を節約できます。
結果 (stored_row) を格納する MATCH 用の列を追加し、結果列ごとに次の値を使用します。
INDEX(Lookup_Range,stored_row,column_number)
また、VLOOKUP を配列数式で使用することもできます。 (配列数式は、Ctrl + Shift + Enter キーを使用して入力する必要があります。Excel では、 { と } を追加して、これが配列式であることを示します)。
{VLOOKUP(lookupvalue,{4,2},FALSE)}
連続する行または列のセットに INDEX を使用する
1 回のルックアップ操作で多数のセルを返すこともできます。 連続する複数の列をルックアップするには、INDEX 関数を配列数式で使用して、一度に複数の列を返すことができます (0 を列番号として使用)。 また、INDEX 関数を使用して、一度に複数の行を返すこともできます。
{INDEX($A$1:$J$1000,stored_row,0)}
このステートメントは、前の MATCH ステートメントで作成した stored_row から列 A ～ J を返します。
MATCH を使用して四角形のセル ブロックを返す
MATCH 関数と OFFSET 関数を使用して、四角形のセル ブロックを返します。
2 次元参照に MATCH と INDEX を使用する
2 つの埋め込み MATCH関数 (1 つは行用、もう 1 つは列用) を使用して、テーブルの行と列に対して個別の参照を使用することで、2 次元テーブル参照を効率的に実行できます。
複数インデックス参照にサブセット範囲を使用する
大きなワークシートでは、国/地域の製品ボリュームを検索するなど、複数のインデックスを使用して検索する必要が多い場合があります。 このようなルックアップを実行するには、インデックスを連結し、連結されたルックアップ値を使用してルックアップを実行します。 ただし、次の 2 つの理由により、効率は低下します。
文字列の連結には大量の計算処理が必要である。
ルックアップの範囲が大きい。
多くの場合、検索のサブセット範囲を計算する方が効率的です (たとえば、国/地域の最初と最後の行を検索し、そのサブセット範囲内で製品を検索するなど)。
3 次元参照のオプションを検討する
行と列だけでなく、テーブルもルックアップする場合は、次のテクニックを使用できます。これらは、特に、Excel でテーブルをルックアップまたは選択する方法です。
検索する各テーブル (3 番目のディメンション) が、名前付き構造化テーブル、範囲名、または範囲を表すテキスト文字列のテーブルとして格納されている場合は、 CHOOSE 関数または INDIRECT 関数を使用できる場合があります。
CHOOSE と範囲名を使用すると効率的です。 CHOOSE は不揮発性ですが、比較的テーブル数が少ない場合に最適です。 この例では、 を動的に使用
TableLookup_Valueして、ルックアップ テーブルに使用する範囲名 (TableName1, TableName2, ...) を選択します。INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))次の例では 、INDIRECT 関数と を使用して、
TableLookup_Value参照テーブルに使用するシート名を動的に作成します。 この方法の利点は、シンプルであること、および大量のテーブルを処理できることです。 INDIRECT は揮発性のシングル スレッド関数であるため、データが変更されていない場合でも、すべての計算でルックアップが計算されます。 この方法の使用は遅いです。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 関数によって結果のテキストを範囲に変換することもできます。
INDEX(INDIRECT(VLOOKUP(TableLookup_Value,TableOfTAbles,1)),MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))
別のテクニックとして、個々のテーブルを識別する追加の列を持つ 1 つの巨大なテーブルに、すべてのテーブルを集約することもできます。 これらのテクニックは、前の例に示したマルチインデックス ルックアップで使用できます。
ワイルドカード参照を使用する
MATCH、VLOOKUP、および HLOOKUP 関数を使用すると、アルファベット順の完全一致でワイルドカード文字 ? (任意の 1 文字) と * (文字または任意の数の文字なし) を使用できます。 場合によっては、この方法によって複数の一致を回避することもできます。
配列の数式と SUMPRODUCT を最適化する
配列数式と SUMPRODUCT 関数は強力ですが、扱いに注意が必要です。 1 つの配列数式では、多くの計算が必要になる場合があります。
配列数式の計算速度を最適化する鍵は、配列数式で評価されるセルと式の数ができるだけ小さいことを確認することです。 配列数式は揮発性の数式に少し似ています。参照しているセルのいずれかが変更された場合、揮発性である場合、または再計算された場合、配列数式は数式内のすべてのセルを計算し、計算を行う必要があるすべての仮想セルを評価します。
配列数式の計算速度を最適化するには
式や範囲の参照を配列数式から別の補助的な列や行に移動します。 こうすると、Excel の高機能再計算プロセスをよりいっそう効果的に利用できます。
すべての行、または行や列を必要以上に参照しないでください。 配列数式では、空白セルや使用されていないセルも含め、すべてのセル参照が強制的に計算されます。 Excel 2007 以降では 100 万行まで使用できるので、全列を参照する配列数式は計算速度が著しく低下します。
Excel 2007 以降では、配列数式で評価するセルを最少数に維持できる構造化された参照を使用します。
Excel 2007 より前のバージョンでは、可能な限りダイナミック レンジ名を使用します。 動的範囲名は揮発性ですが、範囲のサイズを最小にできるので有効です。
行と列の両方を参照する配列数式は、矩形範囲を強制的に計算するので注意してください。
可能であれば、SUMPRODUCT を使用します。これは、同等の配列数式よりも少し高速です。
複数条件配列の数式に SUM を使用するためのオプションを検討する
可能であれば、配列数式ではなく、SUMIFS、COUNTIFS、および AVERAGEIFS 関数を常に使用する必要があります。計算速度が大幅に向上しています。 Excel 2016では、高速な MAXIFS および MINIFS 関数が導入されています。
Excel 2007 より前のバージョンでは、配列数式は、多くの場合、複数の条件を含む合計を計算するために使用されます。 これは、特に Excel で 条件付き合計ウィザード を使用する場合は比較的簡単ですが、時間がかかることがよくあります。 通常、同じ結果を得る方法ははるかに高速です。 複数条件の SUM が数個しかない場合は、同等の配列式よりもはるかに高速な DSUM 関数を使用できます。
配列数式を使用する必要がある場合は、次の方法で高速化できます。
セル数を最小限に抑えるには、ダイナミック レンジ名または構造化テーブル参照を使用します。
複数の条件を、各行の True または False を 返すヘルパー数式の列に分割し、 SUMIF または配列数式でヘルパー列を参照します。 これは、1 つの配列数式の計算数を減らすようには見えない場合があります。ただし、ほとんどの場合、スマート再計算プロセスでは、再計算する必要があるヘルパー列の数式のみを再計算できます。
すべての条件を単一の条件に連結し、SUMIF を使用することを検討します。
データを並べ替えることができる場合は、行のグループをカウントし、配列の数式をサブセット グループを確認するように制限します。
複数条件 SUMIFS、COUNTIFS、およびその他の IFS ファミリ関数に優先順位を付ける
これらの関数は、各条件を左から右に順番に評価します。 したがって、最も制限の厳しい条件を最初に配置する方が効率的であるため、後続の条件では行の数が最も少ないだけを確認する必要があります。
複数条件配列の数式に SUMPRODUCT を使用するためのオプションを検討する
Excel 2007 以降では、可能な限り SUMPRODUCT 数式ではなく、SUMIFS、COUNTIFS、AVERAGEIFS 関数、および MAXIFS 関数と MINIFS 関数Excel 2016常に使用する必要があります。
Excel 2007 よりも前のバージョンでは、SUM 配列数式ではなく、SUMPRODUCT を使用した方がいくつかの点で便利です。
SUMPRODUCT は、Ctrl + Shift + Enter キーを使用して配列を入力する必要はありません。
SUMPRODUCT は、通常、処理が少し高速です (5 ～ 10 パーセント)。
複数条件配列の数式には、次のように SUMPRODUCT を使用します。
SUMPRODUCT(--(Condition1),--(Condition2),RangetoSum)
この例では、 Condition1 や Condition2 は などの $A$1:$A$10000<=$Z4条件式です。 条件式の返す値は数字ではなく、True または False なので、これらを SUMPRODUCT 関数内で強制的に数字にする必要があります。 これを行うには、2 つのマイナス記号 (--) を使用するか、0 (+0) を追加するか、1 (x1) を乗算します。 を使用 -- すると、 +0 または x1 よりもわずかに高速です。
範囲または配列のサイズと形状は、条件式で使用するものと、合計する範囲 (RangetoSum) で使用するものとが同じである必要があり、列全体を含むことはできません。
また、コンマで区切るのではなく、 SUMPRODUCT 内の用語を直接乗算することもできます。
SUMPRODUCT((Condition1)*(Condition2)*RangetoSum)
これは通常、コンマ構文を使用するよりも若干遅く、合計する範囲にテキスト値が含まれている場合にエラーが発生します。 ただし、こちらの方が少し柔軟で、たとえば、条件に 1 列しか含まれていない場合でも、合計する範囲 (RangetoSum) には複数の列を含めることができます。
SUMPRODUCT を使用して範囲と配列を乗算および追加する
たとえば、加重平均の計算では、数字の範囲を別の数字の範囲で乗算し、その結果を加算する必要がありますが、そのようなケースでは、SUMPRODUCT のコンマ構文を使用すると、配列入力される SUM を使用するよりも、処理速度が 20 ～ 25 パーセント向上します。
{=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% しかかかりません。
潜在的な配列と関数の計算の障害物に注意する
Excel の計算エンジンは、配列数式と範囲を参照する関数を利用するように最適化されています。 ただし、これらの数式と関数を通常とは異なる方法で使用すると、場合によっては、計算時間が著しく増加することがあります。
配列数式と範囲関数が関係する計算上の問題を見つけた場合は、次の確認を行う必要があります。
参照が部分的に重複していないか。
配列数式と、範囲関数が参照するセルのブロックの一部が、別の配列数式や範囲関数の計算対象に含まれていないか。 時系列分析ではこうした状況が頻繁に発生します。
行によって参照する数式セットがあり、さらに、そのセットを列によって参照する別の数式セットがないか。
単一行の配列数式の大きなセットが対象としている列のブロックで、SUM 関数が各列の末尾で使用されていないか。
関数を効率的に使用する
関数は Excel の機能を大幅に拡張しますが、使用する方法は計算時間に影響する場合があります。
シングル スレッド関数を回避する
ほとんどのネイティブ Excel 関数は、マルチスレッド計算でうまく機能します。 ただし、可能な場合は、次のシングルスレッド関数を使用しないでください。
- VBA と Automation のユーザー定義関数 (UDF) が、XLL ベースの UDF はマルチスレッド化できます
- PHONETIC
- CELL ("format" or "address" のどちらかの引数が使用された場合)
- INDIRECT
- GETPIVOTDATA
- CUBEMEMBER
- CUBEVALUE
- CUBEMEMBERPROPERTY
- CUBESET
- CUBERANKEDMEMBER
- CUBEKPIMEMBER
- CUBESETCOUNT
- 5 番目のパラメーター () が
sheet_name指定されているアドレス - ピボットテーブルを参照するデータベース関数 (DSUM、DAVERAGE など)
- ERROR.TYPE
- HYPERLINK
範囲を処理する関数にテーブルを使用する
範囲を処理する SUM、SUMIF、および SUMIFS などの関数の場合、計算時間は、合計またはカウントする使用中のセルの数に比例します。 未使用のセルは検査されないため、列全体の参照は比較的効率的ですが、必要以上に使用されるセルを含めないようにすることをお勧めします。 テーブルを使用するか、サブセット範囲または動的範囲を計算します。
揮発性関数を減らす
揮発性関数を使用すると、計算のたびに再計算が必要な数式の数が増加するので、再計算が低速になる可能性があります。
一般に、揮発性関数の数を減らすには、OFFSET の代わりに INDEX を、INDIRECT の代わりに CHOOSE を使用します。 ただし、 OFFSET は高速な関数であり、多くの場合、高速な計算を行う創造的な方法で使用できます。
C または C++ のユーザー定義関数を使用する
C または C++ でプログラムされ、C API (XLL アドイン関数) を使用するユーザー定義関数は、通常、VBA または Automation (XLA または Automation アドイン) を使用して開発されたユーザー定義関数よりも高速に実行されます。 詳細については、「Developing Excel 2010 XLLs」を参照してください。
VBA ユーザー定義関数のパフォーマンスは、プログラミング方法と呼び出し方法に敏感です。
より高速な VBA ユーザー定義関数を使用する
通常、VBA ユーザー定義関数を使用するよりも、Excel 数式の計算とワークシート関数を使用する方が高速です。 ユーザー定義関数は呼び出すたびに小さなオーバーヘッドが発生し、さらに、Excel からユーザー定義関数に情報を転送するときには大きなオーバーヘッドが発生します。 ただし、ユーザー定義関数の設計と呼び出しが適切であれば、複雑な配列数式よりもはるかに高速になります。
ワークシート セルの参照をすべて、ユーザー定義関数の本体ではなく、ユーザー定義関数の入力パラメーターに設定します。このように設定すると、Application.Volatile を追加する無駄を省くことができます。
ユーザー定義関数を使用する数式が多数必要な場合は、手動計算モードであること、および VBA から計算が開始されていることを確認します。 VBA ユーザー定義関数の計算は、計算が VBA から呼び出されない場合 (自動モードや手動モードで F9 キーを押した場合など) は大幅に低速になります。 これは、Visual Basic エディター (Alt + F11) が開いている場合、または現在の Excel セッションで開かれている場合に特に当てはまります。
次のように、F9 キーをトラップして VBA 計算サブルーチンにリダイレクトできます。 このサブルーチンを Thisworkbook モジュールに追加します。
Private Sub Workbook_Open()
Application.OnKey "{F9}", "Recalc"
End Sub
このサブルーチンを標準モジュールに追加します。
Sub Recalc()
Application.Calculate
MsgBox "hello"
End Sub
オートメーション アドイン (Excel 2002 以降のバージョン) のユーザー定義関数は、統合エディターを使用しないため、Visual Basic エディターのオーバーヘッドは発生しません。 オートメーション アドインの Visual Basic 6 ユーザー定義関数の他のパフォーマンス特性は VBA 関数と同様です。
ユーザー定義関数が範囲内の各セルを処理する場合は、入力を範囲として宣言し、配列を含むバリアントに割り当て、その上でループします。 列の参照全体を効率的に処理する場合は、入力範囲のサブセットを作成し、この例のように、使用された範囲との交差部分で分割する必要があります。
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 からユーザー定義関数に転送するよりも効率的です。
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 ISEMPTY(Cell.Value) AND Len(Cell.formula)>0 then
時間オーバーヘッドは、ユーザー定義関数の呼び出しごとに、Excel から VBA へのデータ転送ごとに存在します。 場合によっては、1 つの複数セル配列数式のユーザー定義関数によって、複数の関数呼び出しを単一の関数 (結果の範囲を返す複数セル入力範囲を持つ) にまとめることで、これらのオーバーヘッドを最小限に抑えることができます。
SUM と SUMIF が参照するセル範囲を最小限に抑える
Excel の SUM および SUMIF 関数は、一般的に、大量のセルを対象として使用されます。 これらの関数の計算時間は、対象となるセルの数に比例するので、関数で参照するセルの範囲は最小にします。
ワイルドカード SUMIF、COUNTIF、SUMIFS、COUNTIFS、およびその他の IFS 関数を使用する
SUMIF、COUNTIF、SUMIFS、COUNTIFS、COUNTIFS、およびその他の IFS 関数の一部として、アルファベットの範囲の条件にワイルドカード文字 ? (任意の 1 文字) * と (文字または任意の数の文字なし) を使用します。
期間間および累積 SUM の方法を選択する
期間間または累積 SUM を実行する方法は 2 つあります。 累積 合計 する数値が列 A にあり、列 B に累積合計を含める必要があるとします。次のいずれかを実行できます。
などの
=SUM($A$1:$A2)数式を列 B に作成し、必要に応じて下にドラッグできます。 SUM の最初のセルは A1 に固定されていますが、最後のセルには行の相対参照が含まれるため、このセルの値は 1 行ずつ自動的に増加します。セル B1 や
=$B1+$A2セル B2 などの=$A1数式を作成し、必要に応じて下にドラッグできます。 この計算では、行の番号を直前の累積 SUM に加算して累積セルを求めます。
たとえば、行数が 1,000 の場合、1 番目の方法では計算を約 500,000 回行いますが、2 番目の方法では約 2,000 回です。
サブセットの合計を計算する
テーブルに複数の並べ替えられたインデックスがある場合 (たとえば、エリア内のサイト) では、 SUM または SUMIF 関数で使用する行 (または列) のサブセット範囲のアドレスを動的に計算することで、大幅な計算時間を節約できます。
行または列のサブセット範囲のアドレスを計算するには:
各サブセット ブロックの行数をカウントします。
各ブロックの行数を累積的に加算して基準行を決定します。
OFFSET に基準行と行数を指定して、行のサブセット ブロックのみを含む SUM または SUMIF にサブセット範囲を返します。
フィルター処理されたリストに対して SUBTOTAL を使用する
SUBTOTAL 関数を使用して、フィルター処理したリストに SUM を実行します。 SUBTOTAL 関数は、SUM 関数とは異なり、次の要素が無視されるので便利です。
リストのフィルター処理の結果、非表示になっている行。 Excel 2003 以降、SUBTOTAL はフィルター処理された行だけでなく、非表示の行もすべて無視します。
他の SUBTOTAL 関数。
AGGREGATE 関数を使用する
AGGREGATE 関数は、19 種類の集計方法 ( SUM、 MEDIAN、 PERCENTILE 、 LARGE など) を計算する強力で効率的な方法です。 AGGREGATE には、非表示またはフィルター処理された行、エラー値、入れ子になった SUBTOTAL 関数と AGGREGATE 関数を無視するためのオプションがあります。
DFunctions の使用を避ける
D 関数の DSUM、DCOUNT、DAVERAGE などは、同等の配列数式よりもはるかに高速です。 ただし、D 関数の欠点として、条件を別の範囲に指定する必要があり、それが、多くの状況で D 関数を使用および保守することは現実的でないと判断される原因となっています。 Excel 2007 以降では、D 関数ではなく、SUMIFS、COUNTIFS、および AVERAGEIFS 関数を常に使用する必要があります。
より高速な VBA マクロを作成する
より高速な VBA マクロを作成するには、次のヒントを使用します。
コードの実行中に要点以外のすべてをオフにする
VBA マクロのパフォーマンスを向上させるには、コードの実行中に不要な機能を明示的にオフにします。 多くの場合、コードの実行後に 1 つの再計算または 1 つの再描画が必要であり、パフォーマンスを向上させることができます。 コードが実行されたら、機能を元の状態に復元します。
次の機能は、通常、VBA マクロの実行中に無効にできます。
Application.ScreenUpdating 画面の更新をオフにします。 Application.ScreenUpdating が False に設定されている場合、Excel は画面を再描画しません。 コードが実行されている間、画面はすぐに更新されます。通常、ユーザーが各更新プログラムを表示する必要はありません。 コードの実行後に画面を 1 回更新すると、パフォーマンスが向上します。
Application.DisplayStatusBar ステータス バーをオフにします。 Application.DisplayStatusBar が False に設定されている場合、Excel ではステータス バーは表示されません。 ステータス バーの設定は、画面の更新設定とは別であるため、画面が更新されていない間でも現在の操作の状態を表示できます。 ただし、すべての操作の状態を表示する必要がない場合は、コードの実行中にステータス バーをオフにすると、パフォーマンスも向上します。
Application.Calculation 手動計算に切り替えます。 Application.Calculation が xlCalculationManual に設定されている場合、Excel では、ユーザーが明示的に計算を開始したときにのみブックが計算されます。 自動計算モードでは、Excel によって計算するタイミングが決まります。 たとえば、数式に関連するセル値が変更されるたびに、Excel によって数式が再計算されます。 計算モードを手動に切り替える場合は、数式に関連付けられているすべてのセルが更新されるまで待ってから、ブックを再計算できます。 コードの実行中に必要な場合にのみブックを再計算することで、パフォーマンスを向上させることができます。
Application.EnableEvents イベントを オフにします。 Application.EnableEvents が False に設定されている場合、Excel ではイベントは発生しません。 Excel イベントをリッスンしているアドインがある場合、それらのアドインは、イベントを記録する際にコンピューター上のリソースを使用します。 コードの実行中に発生したイベントをアドインが記録する必要がない場合は、イベントをオフにするとパフォーマンスが向上します。
ActiveSheet.DisplayPageBreaks 改ページをオフにします。 ActiveSheet.DisplayPageBreaks が False に設定されている場合、Excel では改ページは表示されません。 コードの実行中に改ページを再計算する必要はありません。また、コードの実行後に改ページを計算するとパフォーマンスが向上します。
重要
コードの実行後は、この機能を元の状態に戻してください。
次の例は、VBA マクロの実行中に無効にできる機能を示しています。
' 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 回の操作で大きなデータ ブロックの読み取りと書き込みを行う
Excel とコードの間でデータを転送する回数を明示的に減らしてコードを最適化します。 複数のセルを 1 つずつループで反復処理して値を取得または設定するのではなく、2 次元配列を含む Variant を使用して必要に応じて値を格納することで、セルの範囲全体から 1 行で値を取得または設定します。 次のコード例でこれら 2 つの方法を比較します。
次のコード例は、セル A1:C10000 の値を取得して設定するために、一度に 1 つずつセルをループする最適化されていないコードを示しています。 これらのセルには数式は含まれません。
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 の値を同時に取得および設定する最適化されたコードを示しています。 これらのセルには数式は含まれません。
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 範囲からデータを読み取る場合のテキスト
- .テキスト は、セルの書式設定された値を返します。 これは遅く、ユーザーがズームすると ### が返され、精度が失われる可能性があります。
- .範囲 が Date または Currency として書式設定されている場合、値は VBA 通貨または VBA 日付変数を返します。 これは遅く、精度を失う可能性があり、ワークシート関数を呼び出すときにエラーが発生する可能性があります。
- .Value2 は高速であり、Excel から取得されるデータは変更されません。
オブジェクトの選択とアクティブ化を避ける
オブジェクトを選択してアクティブにすると、オブジェクトを直接参照するよりも、処理上の負荷が増加します。 Range や Shape などのオブジェクトを直接参照することで、パフォーマンスを向上させることができます。 次のコード例で 2 つの方法を比較します。
次のコード例は、作業中のシートの各図形を選択し、テキストを "Hello" に変更する最適化されていないコードを示しています。
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).Select
Selection.Text = "Hello"
Next i
次のコード例は、各 Shape を直接参照し、テキストを "Hello" に変更する最適化されたコードを示しています。
For i = 0 To ActiveSheet.Shapes.Count
ActiveSheet.Shapes(i).TextEffect.Text = "Hello"
Next i
これらの追加の VBA パフォーマンスの最適化を使用する
次に示すパフォーマンスの最適化は VBA コードで利用できます。
配列を Range に直接代入して結果を返します。
変数を宣言するときは、データ型を明示的に指定します。データ型を明示的に指定すれば、データ型を判断するオーバーヘッドは発生しません。このオーバーヘッドは、コードの実行中にループ内で何回も発生することがあります。
コード内で頻繁に使用するシンプルな関数は、WorksheetFunction オブジェクトを使用するのではなく、VBA によって自分で実装します。 詳細については、「 高速 VBA ユーザー定義関数を使用する」を参照してください。
Range.SpecialCells メソッドを使用して、コードで操作するセルの数を詳しく調べます。
XLL SDK で C API を使用して機能を実装した場合は、パフォーマンスの向上を考慮してください。 詳細については、Excel 2010 XLL SDK のドキュメントを参照してください。
Excel ファイル形式のパフォーマンスとサイズを検討する
Excel 2007 以降、以前のバージョンと比べて、多種多様なファイル形式を利用できるようになりました。 マクロ、テンプレート、アドイン、PDF、XPS ファイル形式のバリエーションを無視すると、3 つのメイン形式は XLS、XLSB、XLSX です。
XLS 形式
XLS 形式は以前のバージョンと同じ形式です。 この形式で使用できる列数は 256、行数は 65,536 に制限されています。 Excel 2007 または Excel 2010 のブックを XLS 形式で保存すると、互換性チェックが実行されます。 ファイル サイズは以前のバージョンとほとんど変わりませんが (一部の追加情報が格納される場合もあります)、パフォーマンスは以前のバージョンよりも少し低下します。 セルの計算順序について Excel が行うマルチスレッド最適化は XLS 形式では保存されません。 したがって、XLS 形式で保存し、閉じて、再度開いた後のブックの計算は低速になる可能性があります。
XLSB 形式
XLSB は Excel 2007 以降で使用できるバイナリ形式です。 これは、多くのバイナリ ファイルを含む圧縮フォルダーとして構成されています。 XLS 形式よりもはるかにコンパクトですが、圧縮の量はブックの内容によって異なります。 たとえば、10 個のブックは 2 ～ 8 までのサイズ削減係数を示し、平均削減係数は 4 です。 Excel 2007 以降では、ファイルを開く、および保存するときのパフォーマンスが XLS 形式よりも少しだけ低下しています。
XLSX 形式
XLSX は Excel 2007 以降で使用できる XML 形式で、Excel 2007 以降の既定のファイル形式です。 XLSX 形式は、多数の XML ファイルを含む圧縮フォルダーです (ファイル名拡張子を .zip に変更すると、圧縮フォルダーを開き、その内容を調べることができます)。 一般に、XLSX 形式では XLSB 形式よりも多くのファイルが作成されますが (平均 1.5 倍)、ファイル数が多いにもかかわらず、サイズは XLS 形式よりも大幅に小さくなります。 ファイルを開く、および保存するときの時間は XLSB ファイルよりも少し長くなります。
ブックを開く、閉じる、保存する
ブックを開く、閉じる、および保存する速度が、ブックの計算時よりも大幅に低下することがあります。 一般的には、それだけブックのサイズが大きいことが理由として考えられますが、別の理由が存在することもあります。
1 つ以上のブックを開く、および閉じる場合に、極端に時間がかかるときは、次のいずれかの原因が考えられます。
一時ファイル
一時ファイルは、\Windows\Temp ディレクトリ (Windows 95、Windows 98、Windows ME) または \Documents and Settings\User Name\Local Settings\Temp ディレクトリ (Windows 2000 および Windows XP) に蓄積される可能性があります。 Excel では、ブックと、開いているブックで使用されるコントロール用にこれらのファイルが作成されます。 また、ソフトウェア インストール プログラムも一時ファイルを作成します。 Excel が何らかの理由で応答を停止したときは、一時ファイルを削除することが必要となる場合があります。
一時ファイルが多すぎると問題が発生する可能性があるため、クリーンすることがあります。ただし、コンピューターを再起動する必要があるソフトウェアをインストールしていて、まだインストールしていない場合は、一時ファイルを削除する前に再起動する必要があります。
一時ディレクトリを簡単に開くには、[Windows のスタート] メニューから [ スタート] をクリックし、[ 実行] をクリックします。 テキスト ボックスに 「%temp%」と入力し、[OK] をクリック します。
共有ブックの変更を追跡する
共有ブックの変更履歴を記録すると、ブックのファイルサイズが急速に増加します。
フラグメント化されたスワップ ファイル
Windows スワップ ファイルが配置されているディスクに十分な空き容量があり、ディスクが定期的に最適化されていることを確認します。
パスワードで保護された構造を持つブック
パスワードで保護された構造を持つブック ([ツール] メニュー>の [保護>] ブック>はオプションのパスワードを入力します) は、オプションのパスワードなしで保護されているブックよりもはるかに遅く開いて閉じます。
使用範囲の問題
使用範囲のサイズが大きすぎると、特に、非表示の行や列の高さまたは幅が基準外であることが原因で使用範囲のサイズが増大している場合は、開くのに時間がかかったり、ファイル サイズが増加したりすることがあります。 使用範囲の問題の詳細については、「使用範囲 を最小化する」を参照してください。
ワークシートのコントロールの数が多い
ワークシートで使用するコントロール (チェック ボックス、ハイパーリンクなど) の数が多くなると、一時ファイルの使用数に起因して、ブックを開くのに時間がかかることがあります。 また、一時ファイルの使用数は、WAN 上で (LAN 上でも) ブックを開いたり保存したりするときの問題の原因となることもあります。 こうした問題が発生する場合は、ブックの設計の見直しを検討する必要があります。
他のブックへの多数のリンク
可能であれば、リンク先のブックを開いてから、リンク元のブックを開きます。 通常、ブックを開く方が、閉じているブックからリンクを読み取るよりも高速です。
ウイルス スキャナーの設定
ウイルス スキャナーの設定によっては、特にサーバーで、開く、閉じる、または保存するときに問題が発生したり、遅くなったりする可能性があります。 これが問題の可能性があると思われる場合は、ウイルス スキャナーを一時的にオフにしてみてください。
計算が遅いと開き、保存が遅くなる
場合によっては、ブックを開く、または保存するときに、ブックが再計算されることがあります。 ブックの計算時間が長く、問題が発生している場合は、計算が手動に設定されていることを確認し、[保存前の計算] オプション ([ツール> オプションの計算]>) をオフにすることを検討してください。
ツール バー ファイル (.xlb)
ツール バー ファイルのサイズを確認します。 一般的なツール バー ファイルのサイズは 10 ～ 20 KB です。 WINDOWS 検索を使用して検索
*.xlbすることで、XLB ファイルを検索できます。 ユーザーごとに一意の XLB ファイルがあります。 ツール バーを追加、変更、またはカスタマイズすると、toolbar.xlb ファイルのサイズが増加します。 このファイルを削除すると、ツール バーのカスタマイズはすべて削除されます (ファイル名を "toolbar.OLD" にすると安心です)。 次回 Excel を開くと、新しい XLB ファイルが作成されます。
パフォーマンスの最適化を追加する
次の領域でパフォーマンスを向上させることができます。
PivotTables
ピボットテーブルを使用すると、大量のデータを効率的に要約できます。
最終的な結果としての合計。 ブックの最終結果として、合計と小計を求める必要がある場合は、ピボットテーブルを使用します。
中間結果としての合計。 ピボットテーブルを使用すると、概要レポートを効果的に作成できますが、次の条件を満たすことを確認できない限り、計算チェーンで、ピボットテーブルの結果を中間合計として使用する数式は作成しないでください。
計算中、ピボットテーブルが正しく更新されている。
ピボットテーブルは変更されていないが、それでも情報は表示されている。
それでも、ピボットテーブルを中間結果として使用したい場合は、GETPIVOTDATA 関数を使用します。
条件付き形式とデータ検証
条件付き書式とデータの入力規則は便利ですが、多用すると、計算速度が大きく低下する原因となります。 セルが表示されている場合は、各計算で、条件付き書式を含むセルの表示が更新されるときに、すべての条件付き書式の数式が評価されます。 Excel オブジェクト モデルには Worksheet.EnableFormatConditionsCalculation プロパティがあり、条件付き書式の計算を有効または無効にできます。
定義された名前
定義名は Excel の中で最も強力な機能の 1 つですが、定義名を使用すると、計算時間が長くなります。 他のワークシートを参照する名前を使用すると、計算プロセスはより複雑になります。 また、入れ子になった名前 (他の名前を参照する名前) の使用も避ける必要があります。
数式によって参照されている名前は、その数式が計算されるたびに計算されるため、計算量が多い数式や関数を定義名に含めないようにする必要があります。 このようなケースでは、計算量が多い数式や関数を別の場所の予備のセルに入れて、そのセルを参照すると、大幅に高速になる可能性があります (数式や関数を直接参照したり、名前を使用したりしません)。
場合によってのみ使用される数式
多くのブックに、数式やルックアップが大量に含まれていることがあり、計算用の適切な形状に入力データを挿入することに関係するものや、データのサイズや形状の変更を防ぐ対策として使用されるものがあります。 使用頻度が低い数式のブロックがある場合は、特殊な値をコピーおよび貼り付けて数式を一時的に排除することや、滅多に開かないブックに数式を移動することができます。 数式が値に変換されたことに気付かなくて、ワークシート エラーが発生することが多くあるので、使用頻度が低い数式は別のブックに移動した方が望ましい場合があります。
十分なメモリを使用する
Excel の 32 ビット バージョンでは、最大 2 GB の RAM または最大 4 GB の RAM を使用して、大規模アドレス対応 32 ビット バージョンの Excel 2013 および 2016 を使用できます。 ただし、Excel を実行するコンピューターにもメモリ リソースは必要です。 したがって、メモリの一部はオペレーティング システムや他のプログラムに割り当てられるので、コンピューターに 2 GB の RAM しか搭載されていない場合は、Excel だけで 2 GB をすべて利用することはできせん。 32 ビット コンピューターでの Excel のパフォーマンスを最適化するには、コンピューターに少なくとも 3 GB の RAM を搭載することをお勧めします。
64 ビット バージョンの Excel には、2 GB または最大 4 GB の制限はありません。 詳細については、「 Excel のパフォーマンス: パフォーマンスと制限の向上」の「大きなデータ セットと 64 ビット バージョンの Excel」セクションを参照してください。
まとめ
この記事では、一般的な障害を回避し、パフォーマンスを向上させるために、リンク、参照、数式、関数、VBA コードなどの Excel 機能を最適化する方法について説明しました。
関連項目
サポートとフィードバック
Office VBA またはこの説明書に関するご質問やフィードバックがありますか？ サポートの受け方およびフィードバックをお寄せいただく方法のガイダンスについては、Office VBA のサポートおよびフィードバックを参照してください。
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示