Excel 2007 におけるパフォーマンスの改善

  

Charles Williams (Decision Models Limited)

2006 年 10 月

適用対象:
   Microsoft Office Excel 2007
   Microsoft Office Excel 2003
   Microsoft Excel 2002、Microsoft Excel 2000

要約: Microsoft Office Excel 2007 でのワークシート容量の拡大と、ワークシートを設計および作成する際、計算パフォーマンスを改善するために利用できるテクニックについて学びます。

目次

はじめに
Excel 2007 の "大きなグリッド" と上限の拡大
計算速度が重要である理由
Excel の計算方式についての理解
ブック、ワークシート、および範囲の計算
計算オプションの制御
ブックの計算の高速化
計算でのボトルネックの特定と優先順位付け
Excel 2007 におけるパフォーマンスの改善
ボトルネックを最適化するためのヒント
ブックの開閉、保存、およびサイズ
まとめ
追加情報

はじめに

Microsoft Office Excel 2007 では、"大きなグリッド" (100 万行 x 16,000 列) を始め、各種の上限が拡大されているため、作成可能なワークシートのサイズが以前のバージョンの Excel と比べてかなり大きくなっています。Excel 2007 のワークシートあたりのセル数は、以前のバージョンの 1,000 倍を超えます。

Excel の以前のバージョンでは、作成されるワークシートでは多くの場合計算が遅く、ワークシートが大きくなるとさらに計算が遅くなるのが一般的でした。Excel 2007 の "大きなグリッド" では、パフォーマンスを重要視しています。計算が遅いと、ユーザーが集中力を保つのが難しくなり、ミスを犯しやすくなるためです。

Excel 2007 では容量の拡大に対応して、複数のプロセッサによる同時計算など、いくつかの機能を導入し、ワークシートの計算時間を大幅に短縮できるようにしています。しかしながら、Excel の計算速度を左右する最大の要因が、ワークシートの設計方法と作成方法であることに変わりはありません。

計算の遅いワークシートを修正して、計算速度を何十倍、何百倍、さらには何千倍にも高めることができます。この記事では、計算をスピードアップするために Excel 2007 の新機能を活用する方法を紹介し、ワークシートの計算でのボトルネックを特定、計測、および改善する方法を説明します。

**メモ:   **この記事は Microsoft Office Excel 2007 の Beta 2 バージョンで得られた結果を基にしているため、Excel 2007 製品版の内容、ユーザー インターフェイス、またはパフォーマンスを厳密には反映していない可能性があります。

Excel 2007 の "大きなグリッド" と上限の拡大

Excel 2007 の "大きなグリッド" では、1 ワークシートの行数が 65,536 行から 100 万行に、列数が 256 列 (IV) から 16,384 列 (XFD) に拡大されています。

既にお気付きかもしれませんが、Excel の最近のバージョンでは、メモリ容量が増えたために、大容量のブックで作業すると仕様上の各種の制限を超過してしまうことが多くなっています。Excel 2007 では、行および列の容量の大幅な増加に対応するため、次の制限にも多くの変更が加えられています。

• メモリ: 式およびピボット キャッシュに使用可能なメモリが、Microsoft Office Excel 2003 における 1 GB、Microsoft Excel 2002 における 128 メガバイト (MB)、および Microsoft Excel 2000 における 64 MB から、2 ギガバイト (GB) に増えています。
• スマート再計算: 完全計算ではなくスマート再計算によって処理できる依存関係が、1 つの領域に依存するセル数 8,000、依存関係のある領域数 64,000 という上限によって制限されるのではなく、使用可能なメモリによってのみ制限されるようになりました。
• 配列数式: 列全体の参照が可能になり、他のワークシートを参照する配列数式が、65,000 という上限によって制限されるのではなく、使用可能なメモリによって制限されるようになりました。
• ピボットテーブル: ピボットテーブル レポートに表示される最大行数は 100 万、最大列数は 16,000 となります。また、1 つのピボット フィールド内の一意の項目の最大数は 100 万、フィールド リスト内で表示されるフィールドの最大数は 16,000 となります。
• 並べ替え: レベルが 3 から 64 に増えています。
• オートフィルタ: ドロップダウン リストの項目が 1,000 から 10,000 に増えています。
• 数式の最大長: 1,000 から 8,000 に増えています。
• 数式の入れ子レベル: 7 から 64 に増えています。
• 関数の引数: 30 から 255 に増えています。
• セルあたりの条件付き書式: 3 という上限数による制限から使用可能なメモリによる制限に変更されています。
• ブック内の一意のセル スタイル: 4,000 から 64,000 に増えています。
• ブックあたりの一意の色: 56 から 43 億に増えています。
• セル内で表示および印刷が可能な文字数: 最大 32,000 文字に増えています。

これらについての詳しい情報と、Excel 2007 におけるその他の改良点については、David Gainer のブログ Microsoft Excel 2007 (英語) を参照してください。

言うまでもなく、こうした上限の拡大は、計算速度にも大きく影響します。

計算速度が重要である理由

計算速度が遅いと、生産性が低下し、ユーザー エラーが増加する原因になります。調査の結果、ユーザーの生産性と作業への集中度は、応答時間が長くなるにつれて低下することが明らかになっています。

Excel には主要な 2 つの計算モードがあり、これによって計算を実行するタイミングを制御できるようになっています。

• 自動計算: 変更を加えるたびに、数式が自動的に再計算されます。
• 手動計算: ユーザーが (たとえば F9 キーを押すなどの操作によって) 要求を行った場合にのみ、数式が再計算されます。

計算時間が 0.1 秒未満であれば、ユーザーはシステムが瞬時に応答していると感じます。自動計算は、データの入力中にも使用できます。

0.1 ～ 1 秒では、ユーザーは応答時間の遅延に気付くとはいえ、まだ思考の流れを保つことができます。

計算時間が長くなると、ユーザーはデータを入力するとき、手動計算に切り替えなければなりません。

1 ～ 10 秒になると、ユーザーはおそらく手動計算に切り替えます。反復的な作業では特に、ユーザー エラーや不快さのレベルが上がり始め、思考の流れを保つことが難しくなります。

計算時間が 10 秒を超えると、ユーザーはいらだち、待ち時間を利用して他の作業をするようになるのが普通です。本来の作業の流れに計算が入り込み、どこまで進んだかをユーザーが忘れてしまうと、問題が起きる原因になります。

Excel の計算方式についての理解

Excel の計算パフォーマンスを改善するには、使用可能な計算方式とその制御方法を理解する必要があります。

完全計算と再計算の依存関係

Excel のスマート再計算エンジンは、計算時間を最小限にするために、各数式の参照元と依存関係 (数式によって参照されるセル)、および最後の計算以降に加えられた変更を継続的に追跡します。そして再計算時に、次の要素だけを再計算します。

• セル、数式、値、名前のうち変更が行われたもの、または再計算が必要であるというフラグが設定されているもの
• 再計算が必要なセル、数式、名前、値に対する依存関係を持つセル
• 可変的な関数と条件付き書式

Excel は、計算する時点で前に計算済みのセルの値が変更されていない場合も、そのセルに依存するセルの計算は続けます。

計算と計算の間では、ユーザーは入力データのごく一部分、またはわずかな数式しか変更していないことが普通なので、一般にスマート再計算に要する時間は、すべての数式を完全に計算する場合と比べて数分の一程度にしかなりません。

手動計算モードでは、F9 キーを押してスマート再計算をトリガすることができます。すべての数式の完全計算を強制的に実行する場合は、Ctrl + Alt + F9 キーを押します。また、依存関係の完全な再作成と完全計算を強制的に実行する場合は、Shift + Ctrl + Alt + F9 キーを押します。

計算プロセス

他のセルを参照する Excel 数式は、参照先のセルの前後どちらに置いてもかまいません (下方参照または逆方向参照)。その理由は、Excel ではセルが固定的な順序 (行単位または列単位) では計算されないからです。固定的な順序の代わりに、Excel は計算するすべての数式のリスト (計算チェーン) および各数式の依存関係に関する情報に基づいて、計算順序を動的に決定します。

Excel の全体的な計算プロセスには、次の 3 つの異なるフェーズがあります。

1. 最初の計算チェーンを作成し、どこから計算を開始するかを決定します。このフェーズは、メモリにブックが読み込まれる時点で実行されます。
2. 依存関係を追跡し、計算されていないセルにフラグを付け、計算チェーンを更新します。このフェーズは、手動計算モードでも、各セルが入力されるかまたは変更されるたびに実行されます。通常、このフェーズは非常に速く実行されるので、ユーザーが気付くことはありません。
3. 数式を計算します。計算プロセスの一環として、Excel は今後の再計算を最適化するために、計算チェーンの順序変更と再構成を行います。

3 番目のフェーズは、計算または再計算のたびに実行されます。Excel は計算チェーン内の各数式を順に計算するように試みますが、まだ計算されていない数式に依存する数式があった場合は、その数式はチェーンの下の方へ移動され、後で再び計算されます。つまり、再計算ごとに 1 つの数式が複数回計算される場合があります。Excel 2000 では、ワークシートごとに 1 つの計算チェーンが保持されており、一連のワークシートがシート名のアルファベット順に計算されます。Excel 2002 以降のバージョンでは、単一のグローバルな計算チェーンがあり、これによって大部分のワークシートで計算速度が短縮されるようになっています。

Excel における計算プロセスの詳細については、Microsoft Excel 2002 における再計算 (英語) を参照してください。

ブックを 2 回目に計算するときは、1 回目よりも大幅に処理が速くなるのが普通です。この背景には、次のような理由があります。

• Excel は通常、変更されたセルと、それに依存するセルのみを再計算します。
• Excel は最後に使った計算シーケンスを保存して再利用するので、計算順序の決定に費やされる時間の大半を節約できます。
• マルチ コアのコンピュータでは、Excel 2007 は計算方法の最適化を試み、前回の計算結果に基づいて、各コアに計算を分散します。
• Excel セッションでは、Microsoft Windows と Excel の両方に関して、最近使ったデータおよびプログラムをキャッシュに格納し、アクセスを高速化します。

ブック、ワークシート、および範囲の計算

Excel のさまざまな計算方式を使用して、計算する対象を制御できます。

開いているすべてのブックの計算

再計算および完全計算を実行するたびに、現在開いているすべてのブックが計算され、単一のブック/ワークシート内または複数のブック/ワークシート間の依存関係が解決されます。

特定のワークシートの計算

Shift + F9 キーを使用して、選択しているワークシートだけを再計算することもできます。この場合、計算対象のシート内の依存関係だけが解決されます。

セル範囲の計算

Excel では、Microsoft Visual Basic for Applications (VBA) メソッドの Range.Calculate を使用して、特定のセルの範囲を計算することもできます。Range.Calculate の動作は、次に示すように、Excel の各バージョンによってかなり変更されています。

• Excel 2000: Range.Calculate は、すべての依存関係を無視し、左から右、上から下へ計算を実行します。
• Excel 2002 および Excel 2003: Range.Calculate は、計算対象の範囲内で依存関係を解決します。
• Excel 2007: このバージョンには、2 種類のメソッドがあります。Range.Calculate は、Excel 2002 および Excel 2003 と同様に動作します。一方、Range.CalculateRowMajorOrder は、Excel 2000 と同様に動作します。Range.CalculateRowMajorOrder は、計算する範囲内の依存関係の解決は行わないため、通常、かなり高速です。ただし、このメソッドは Range.Calculate と同じ結果を返さない場合があるため、使用するときは注意が必要です。詳細については、この記事の「Excel 2007 におけるパフォーマンスの改善」を参照してください。
• Range.Calculate は、Excel におけるパフォーマンス最適化に最も役立つ手段の 1 つです。これを使用してさまざまな数式の計算速度を測定して比較することができるからです。

可変的な関数

可変的な関数は、参照元が変更されていないように見える場合にも、再計算のたびに計算されます。可変的な関数が多く使用されている場合、再計算の速度は遅くなりますが、完全計算の速度には影響はありません。ユーザー定義の関数は、コードに Application.Volatile を含めることにより、可変的な関数にすることができます。

RAND()、NOW()、TODAY() など、Excel のいくつかの組み込み関数は、明らかに可変的な関数です。また、OFFSET()、CELL()、INDIRECT()、INFO() などは、一見そうは見えませんが、可変的な関数になります。

INDEX()、ROWS()、COLUMNS()、AREAS() などの関数は、従来は可変的なものとして示されていましたが、実際には可変的ではありません。

可変的なアクション

可変的なアクションとは、再計算をトリガするアクションです。次に例を挙げます。

• 自動計算モードのとき行または列の区切り線をクリックする。
• シート上の任意の場所で行、列、セルのいずれかを挿入または削除する。
• 定義された名前を追加、変更、または削除する。
• 自動計算モードのときワークシート名を変更するか、ワークシートの位置を変更する。
• (Excel 2003 または Excel 2007 の場合) 行をフィルタするか、表示/非表示を切り替える。
• 自動計算モードのときブックを開く (ブックの最後の計算が Excel の別のバージョンで行われていた場合は、通常、そのブックを開くと、完全計算が実行されます)。
• [ブックの保存前に再計算を行う] オプションが選択されている場合に、手動計算モードでブックを保存する。

数式および名前の評価が実行される条件

手動計算モードでも、次のいずれかの操作を行うと、数式または数式の要素は直ちに評価 (計算) されます。

• 数式を入力または変更する。
• 関数ウィザードを使用して数式を入力または変更する。
• 関数ウィザードで数式を引数として入力する。
• 数式バーで数式を選択し、F9 キーを押す (元に戻し、数式に戻る場合は Esc キーを押します)、または [数式の検証] をクリックする。

数式が次のいずれかの条件に当てはまるセルや数式を参照する (または、そのセルや数式に依存する) 場合、該当の数式は常に未計算としてフラグ付けされます。

• 入力が行われた。
• 変更が行われた。
• オートフィルタ リストであり、かつ、[基準] ドロップダウン リストが有効になっている。
• 未計算としてフラグ付けされている。

未計算としてフラグ付けされた数式は、その数式を含む範囲、ワークシート、ブック、Excel インスタンスが計算または再計算されるたびに評価されます。

定義された名前が評価される条件は、セル内の数式が評価される条件とは異なります。

• 定義された名前は、その名前を参照する数式が評価されるたびに評価されます。したがって、1 つの名前を複数の数式で使用すると、その名前が複数回にわたって評価される場合があります。
• 数式によって参照されていない名前は、完全計算が実行されるときにも計算されません。

データ テーブル

Excel のデータ テーブル ([データ] タブの [データ ツール] グループで [What-If 分析] をクリックし、続いて [データ テーブル] をクリックします) を、テーブル機能 ([ホーム] タブの [スタイル] グループで [テーブルとして書式設定] をクリックするか、または [挿入] タブの [テーブル] グループで [テーブル] をクリックします) と混同しないようにしてください。Excel のデータ テーブルは、テーブル内の異なる値ごとに、複数回にわたってブックを再計算します。Excel は、最初はブックを通常どおり計算します。その後、行の値と列の値のペアごとに、データ テーブル内の値の置き換え、再計算、および結果の保存を行います。

データ テーブルは、複数のバリエーションを計算し、各バリエーションによる結果を表示して比較するための便利な手段になります。[テーブル以外自動] 計算オプションを使用すると、Excel が計算のたびに複数の計算を自動的にトリガしないようにすることができ、同時に、テーブル以外のすべての依存数式の計算は実行させることができます。

計算オプションの制御

Excel には、計算方法を制御するための幅広いオプションがあります。リボンの [数式] タブの [計算] グループを使用すると、Excel 2007 で最もよく使用されるオプションの変更を行うことができます。

図 1. [数式] タブの [計算] グループ

Excel 2007 のその他の計算オプションを表示するには、Microsoft Office ボタン、[Excel オプション]、[数式] タブの順にクリックします。

図 2. [Excel オプション] の [数式] タブに含まれる計算オプション

多くの計算オプション ([自動]、[テーブル以外自動]、[手動]、[ブックの保存前に再計算を行う]) および反復に関する設定 ([反復計算を行う]、[最大反復回数]、[変化の最大値]) は、ブック レベルではなくアプリケーション レベルで動作します (これは、開いているすべてのブックで共通です)。

詳細な計算オプションを表示するには、Microsoft Office ボタン、[Excel オプション]、[詳細] の順にクリックします。

図 3. 詳細な計算オプション

以前のバージョンの Excel の場合は、[ツール] メニューの [オプション] を選択し、[計算] タブをクリックすると、すべての計算オプションが表示されます。

Excel を起動するとき、または Excel を実行しているがブックは開かれていないとき、初期の計算モードおよび反復計算に関する設定は、最初に開かれる、テンプレートやアドインが適用されていないブックから設定されます。つまり、それ以降に開かれたブックの計算設定は無視されます。もちろん、任意の時点で Excel の設定を手動で変更することも可能です。ブックを保存するとき、その時点での計算設定がブックに保存されます。

自動計算

自動計算モードでは、ブックに変更が行われるたびに、また、ブックが開かれるたびに、開いているすべてのブックが自動的に再計算されます。自動計算モードでユーザーがブックを開き、Excel によって再計算が実行された場合、そのブックは保存されてから何も変更されていないため、通常は再計算に気付くことはありません。

ただし、ブックを最後に計算したときに使用したバージョンよりも新しい Excel のバージョンでブックを開いた場合には (Excel 2003 で最後に計算を行ったブックを Excel 2007 で開いた場合など)、この計算に気付く場合があります。これは、Excel の計算エンジンがバージョン間で異なっており、Excel の以前のバージョンで保存されたブックを開いたとき、完全計算が実行されるためです。

手動計算

手動計算モードでは、ユーザーが F9 キーまたは Ctrl + Alt + F9 キーを押して再計算を要求した場合、またはブックを保存した場合にのみ、開いているすべてのブックで再計算が実行されます。ブックで再計算にかかる時間が 1 秒を超える場合は、手動計算モードに設定して、変更を行うたびに発生するわずらわしい遅延を回避する必要があります。

手動計算モードのブックで再計算が必要になると、ステータス バーに [計算] の項目が表示されます。ブックに循環参照が含まれていて、反復オプションが選択されている場合にも、ステータス バーには [計算] が表示されます。2007 より前のバージョンの Excel では、依存関係が多すぎる場合に [計算] が表示されます。

反復計算

ブック内で意図的に循環参照を使用する場合、反復計算に関する設定を使用して、そのブックを再計算する (反復する) 最大回数と、収束の基準 (変化の最大値: いつ停止させるか) を指定できます。通常は [反復] ボックスをオフにしておき、偶発的な循環参照が発生した場合は Excel に警告を表示させて、その参照の解決はさせないようにする必要があります。

ブックの計算の高速化

ここでは、ブックの計算を高速化するために使用できる手順および方法について説明します。

プロセッサ速度とマルチ コア

言うまでもなく、Intel や AMD の高速のプロセッサを使用すれば、Excel のほとんどのバージョンで計算速度が向上します。低価格プロセッサやミッド レンジのプロセッサよりも、レンジ最高水準のプロセッサを購入する方が、通常、快適なパフォーマンスが得られます。

Excel 2007 では、マルチ プロセッサ システムをサポートする新機能が提供されています。Excel 2007 のマルチ スレッド計算エンジンによってマルチ プロセッサ システムが効率的に使用され、ほとんどのブックでパフォーマンスが大幅に向上します。詳細については、この記事の「Excel 2007 におけるパフォーマンスの改善」を参照してください。

RAM

仮想メモリ スワップ ファイルへのページングは、非常に低速です。オペレーティング システム、Excel、およびブック用に十分な物理 RAM を装備しておく必要があります。計算中にハード ディスク アクティビティがしばしば行われるような環境では、RAM 容量を増やす必要があります。

前述したように、最近のバージョンの Excel は大容量のメモリを効率的に使用することができます。Excel 2007 では、最大 2 GB のブック メモリを使用して、単一のブックまたは複数のブックの組み合わせを処理できます。

効率的な計算のための基本的なガイドラインは、同時に開く必要のある最大数のブックの集合に対応する容量に、Excel およびオペレーティング システム用に 256 MB または 512 MB を加え、さらにそのほかに実行するアプリケーション用の追加分を加えた容量の RAM を搭載することです。Excel が使用している容量は、Windows タスク マネージャでチェックできます。

図 4. Windows XP のタスク マネージャでの Excel によるメモリ使用状況の表示

計算時間の測定

ブックの計算を高速化するには、計算時間の正確な計測が可能であることが必要です。 VBA の Time 関数よりも高速かつ正確なタイマが必要です。 次の例に示す MICROTIMER() 関数は、システムの高分解能タイマへの Windows API 呼び出しを使用します。 このタイマでは、時間の間隔をマイクロ秒の小数部分まで計測できます。 Windows はマルチ タスクのオペレーティング システムであり、また、最初の計算よりも 2 回目の計算の方が速く実行できるため、まったく同一の計測時間が繰り返し取得されることは通常ありません。 精度を最大限にするには、計算タスクの所要時間を何回か測定して結果の平均を取ります。

Visual Basic Editor がどのように VBA のユーザー定義関数のパフォーマンスに大きく影響するかについての詳細は、この記事の「より高速な VBA ユーザー定義関数」を参照してください。

Private Declare Function getFrequency Lib "kernel32" _
Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" _
Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
'
Function MicroTimer() As Double
'

' Returns seconds.
'
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
    '
    MicroTimer = 0

' Get frequency.
    If cyFrequency = 0 Then getFrequency cyFrequency

' Get ticks.
    getTickCount cyTicks1                            

' Seconds
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 
End Function

計算時間を測定するには、適切な計算メソッドを呼び出す必要があります。各サブルーチンを使用すれば、範囲の計算時間、シートまたは開いているすべてのブックの再計算時間、または開いているすべてのブックの完全計算の所要時間を計測できます。

必要なすべてのサブルーチンと関数を、標準的な VBA モジュールにコピーします。VBA エディタを開くには、Alt + F11 キーを押します。[挿入] メニューから [モジュール] を選択し、コードをモジュールにコピーします。

図 5. Excel 2007 の [マクロ] ウィンドウでの計算タイマの表示

Excel 2007 でサブルーチンを実行するには、Alt + F8 キーを押すか、[マクロの表示] をクリックします。実行するサブルーチンを選択し、[実行] をクリックします。

Sub RangeTimer()
    DoCalcTimer 1
End Sub
Sub SheetTimer()
    DoCalcTimer 2
End Sub
Sub RecalcTimer()
    DoCalcTimer 3
End Sub
Sub FullcalcTimer()
    DoCalcTimer 4
End Sub

Sub DoCalcTimer(jMethod As Long)
    Dim dTime As Double
    Dim dOvhd As Double
    Dim oRng As Range
    Dim oCell As Range
    Dim oArrRange As Range
    Dim sCalcType As String
    Dim lCalcSave As Long
    Dim bIterSave As Boolean
    '
    On Error GoTo Errhandl

' Initialize
    dTime = MicroTimer              

    ' Save calculation settings.
    lCalcSave = Application.Calculation
    bIterSave = Application.Iteration
    If Application.Calculation <> xlCalculationManual Then
        Application.Calculation = xlCalculationManual
    End If
    Select Case jMethod
    Case 1

        ' Switch off iteration.

        If Application.Iteration <> False Then
            Application.Iteration = False
        End if
        
        ' Max is used range.

        If Selection.Count > 1000 Then
            Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
        Else
            Set oRng = Selection
        End If

        ' Include array cells outside selection.

        For Each oCell In oRng
            If oCell.HasArray Then
                If oArrRange Is Nothing Then 
                    Set oArrRange = oCell.CurrentArray
                End If
                If Intersect(oCell, oArrRange) Is Nothing Then
                    Set oArrRange = oCell.CurrentArray
                    Set oRng = Union(oRng, oArrRange)
                End If
            End If
        Next oCell

        sCalcType = "Calculate " & CStr(oRng.Count) & _
            " Cell(s) in Selected Range: "
    Case 2
        sCalcType = "Recalculate Sheet " & ActiveSheet.Name & ": "
    Case 3
        sCalcType = "Recalculate open workbooks: "
    Case 4
        sCalcType = "Full Calculate open workbooks: "
    End Select

' Get start time.
    dTime = MicroTimer
    Select Case jMethod
    Case 1
        If Val(Application.Version) >= 12 Then
            oRng.CalculateRowMajorOrder
        Else
            oRng.Calculate
        End If
    Case 2
        ActiveSheet.Calculate
    Case 3
        Application.Calculate
    Case 4
        Application.CalculateFull
    End Select

' Calc duration.
    dTime = MicroTimer - dTime
    On Error GoTo 0

    dTime = Round(dTime, 5)
    MsgBox sCalcType & " " & CStr(dTime) & " Seconds", _
        vbOKOnly + vbInformation, "CalcTimer"

Finish:

    ' Restore calculation settings.
    If Application.Calculation <> lCalcSave Then
         Application.Calculation = lCalcSave
    End If
    If Application.Iteration <> bIterSave Then
         Application.Calculation = bIterSave
    End If
    Exit Sub
Errhandl:
    On Error GoTo 0
    MsgBox "Unable to Calculate " & sCalcType, _
        vbOKOnly + vbCritical, "CalcTimer"
    GoTo Finish
End Sub

計算でのボトルネックの特定と優先順位付け

計算が遅いブックでは、ごくわずかな問題領域すなわちボトルネックによって、計算時間の大半が消費されていることがほとんどです。このようなボトルネックがどこにあるかが定かでない場合は、ここで説明するドリル ダウン式のアプローチを使って特定できます。ボトルネックが既に特定できている場合は、各ボトルネックでの計算時間を計測して、ボトルネックを取り除く作業における優先順位を決める必要があります。

ドリル ダウン アプローチによるボトルネックの特定

ドリル ダウン アプローチでは、まずブックの計算時間を計測します。その後、各ワークシートの計算時間、計算の遅いシート上の数式ブロックの計算時間の順に計測を行います。各ステップは順番に実行し、すべての計算時間を記録しておく必要があります。

ドリル ダウン アプローチを使用してボトルネックを特定する

1. 1 つのブックだけが開かれており、他に実行されているタスクがないことを確認します。

2. 手動計算モードに設定します。

3. ブックのバックアップ コピーを作成します。

4. 計算タイマ マクロの入ったブックを開くか、マクロをブックに追加します。

5. ワークシートごとに Ctrl + End キーを押し、使用されている範囲をチェックします。

   これにより、最後に使用されたセルがどこにあるかがわかります。このセルが予想外に離れた場所にある場合は、余分な列と行を削除して、ブックを保存してください。詳細については、この記事の「使用範囲の最小化」を参照してください。

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

   ブック内のすべての数式の計算所要時間は、通常、最悪のケースにおける時間を示します。

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

   完全計算の直後の再計算は、通常、最良のケースの時間を示します。

8. 完全計算の所要時間に対する再計算の所要時間の比率を、ブックの可変値として計算します。

   これは、可変性を持つ数式と計算チェーンの評価が、どの程度までボトルネックになっているかを示すものです。

9. シートごとに SheetTimer マクロを順番に実行します。

   ブックを再計算したばかりなので、この操作により各ワークシートの再計算時間がわかり、どのワークシートに問題があるかを判別できます。

10. 選択した数式のブロックに対して、以下の手順で RangeTimer マクロを実行します。

    1. 問題のある各ワークシートで、列または行をいくつかのブロックに分割します。
    2. 各ブロックを順に選択し、そのブロックに対して RangeTimer マクロを実行します。
    3. 必要に応じて、各ブロックをさらにいくつかのブロックに分割し、同じ操作を繰り返します。

11. 特定されたボトルネック間で優先順位を付けます。

計算の高速化とボトルネックの削減

計算時間が浪費される要因は、数式の個数やブックのサイズではありません。セル参照と計算動作の回数、および使用されている関数の効率性が原因です。

ほとんどのワークシートは、絶対参照と相対参照が入り混じった数式をコピーすることによって構成されています。したがって、計算や参照の繰り返しや重複のある数式が大量に含まれているのが普通です。

複雑で長すぎる数式や配列数式の使用は控えるようにします。基本的に、複雑な計算を多く含めるよりも、行や列を多く使用する方が得策です。そうすれば、Excel 2007 のスマート再計算とマルチ スレッド計算によって計算が最適化される可能性が高くなります。また、コードを確認してデバッグすることも簡単になります。ブックの計算の高速化に役立つ原則を、次に示します。

第 1 の原則: 計算の重複、繰り返し、不要な計算を排除する

計算の重複、繰り返し、不要な計算をチェックします。続いて、こうしたボトルネックを含んだ状態で Excel が処理を実行する際に必要となるセル参照と計算の回数を概算します。その後、より少ない参照と計算で同じ処理を行うにはどうすればよいかを考えます。

この場合、通常は、次のような作業が必要になります。

• 各数式に含まれる参照の数を減らします。
• 繰り返し実行されている計算を任意の数のヘルパー セルに移動し、元の数式からそのヘルパー セルを参照するようにします。
• 追加の行および列を使用して、計算処理や中間結果の格納を行い、他の数式でそれを再利用できるようにします。

第 2 の原則: できる限り効率的な関数を使用する

関数または配列数式が関連するボトルネックが見つかった場合は、同一の処理を行うための、より効率的な方法がないかどうかを検討します。次に、検討の例を示します。

• 並べ替えたデータに対して検索を実行すれば、並べ替えていない状態でデータを検索するよりも何十倍、何百倍も効率が上がる可能性があります。
• VBA ユーザー定義関数は通常、Excel の組み込み関数よりも低速です (ただし、入念に作成された VBA 関数は、非常に高速である場合もあります)。
• SUM、SUMIF などの関数では、使用するセル数を最小限にします。計算時間は使用するセル数に比例するためです (使用しないセルは無視されます)。
• 低速な配列数式は、ユーザー定義関数に置き換えることを検討します。

第 3 の原則: スマート再計算を十分に活用する

Excel のスマート再計算を使用する領域を増やすことによって、再計算のたびに必要となる処理が少なくなります。

• INDIRECT、OFFSET などの可変的な関数は、他の方法と比べて著しく効率的である場合を除き、できる限り使用しないようにします (OFFSET は、使い方をうまく規定すると、非常に高速となる場合があります)。
• 配列数式および関数で使用する範囲のサイズは、最小限にします。
• 配列数式および長い数式は、個別のヘルパー列およびヘルパー行に分割します。

第 4 の原則: 変更を加えるたびに時間を計測し検証する

変更によって予想外の結果が生じることがあります。期待した結果が得られない場合や、予想以上に計算が低速となる場合があります。したがって、次の方法を使用して、変更の都度時間を計測し、結果を検証する必要があります。

1. 変更前に RangeTimer マクロを使用して、該当の数式で時間を計測します。
2. 変更を行います。
3. RangeTimer マクロを使用して、変更後の数式で時間を計測します。
4. 変更後の数式が、引き続き正しい結果を導き出しているかどうかを確認します。

原則の適用例

ここでは、上記の原則の適用によって計算が高速化される例を示します。

該当セルまでの合計

2000 個の数値が入った列について、それぞれのセルまでの合計 (SUM) を計算する必要があると仮定します。 列 A には数値、列 B および C にはそこまでの合計を入れる必要があります。

この数式は、非常に効率的な関数である SUM を使用して作成できます。

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

図 6. 該当セルまでの合計を計算する SUM 数式の例

次に、この数式を B2000 までコピーします。

この場合、SUM によって加算されるセル参照は、全部で何個になるでしょうか。B1 で参照されるセルは 1 つですが、B2000 では 2000 個のセルが参照されます。セルごとに平均 1000 の参照があり、参照は全部で 200 万回行われることになります。2000 個の数式を選択して RangeTimer マクロを使用すると、列 B に含まれる 2000 個の数式の計算所要時間は 80 ミリ秒であることがわかります。しかし、これらの計算の大部分は何回も重複しています。B2:B2000 の範囲に存在する各数式ごとに、SUM によって A2 への A1 の加算が行われています。

ここで、数式を次のように記述すると、重複をなくすことができます。

C1=A1
C2=C1+A1

この数式を C2000 までコピーします。

この場合、全部で何個のセル参照が加算されることになるでしょうか。最初の数式を除くと、数式ごとに 2 つのセル参照が使用されています。したがって、合計は 1999*2+1=3999 となり、セル参照は 500 分の 1 に減少しています。

RangeTimer を実行すると、列 B の 80 ミリ秒に対し、列 C に含まれる 2000 個の数式の計算所要時間は 3.7 ミリ秒であることがわかります。数式ごとにわずかなオーバーヘッドがあるため、この変更によって得られるパフォーマンス向上は、500 倍ではなく 80/3.7=22 倍となります。

エラー処理

集中的な計算を必要とする数式があり、エラーが発生した場合には、結果が 0 と表示されるようにしたいと仮定します (こうしたケースは完全一致の検索で多く発生します)。 これは、次のように、複数の方法によって記述することができます。

• 次のように 1 つの数式で記述します。この場合、計算は遅くなります。

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

• 次のように 2 つの数式で記述します。この場合、計算は速くなります。

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

• Excel 2007 では、簡素で高速な IFERROR 関数を使用して、次のように 1 つの数式で記述することができます。

  B1=IFERROR(time expensive formula,0)
  

一意の値の動的なカウント

図 7. 一意の値をカウントするデータのリストの例

さまざまに異なるデータを 列 A に含む 11,000 行のリストがあり、このリスト内の一意の項目数を動的に計算する (空白は無視する) 式が必要になったと仮定します。ここで、考えられる候補をいくつか示します。

配列数式

次のような配列数式を使用できます (Ctrl + Shift + Enter キーを使用)。

{=SUM(IF(LEN(A2:A11000)>0,1/COUNTIF(A2:A11000,A2:A11000)))}

RangeTimer によると、この場合 13.8 秒かかることがわかります。

SUMPRODUCT

SUMPRODUCT は通常、同等の機能を持つ配列数式よりも高速な計算が実行できます。

=SUMPRODUCT((A2:A11000<>"")/COUNTIF(A2:A11000,A2:A11000&""))

この数式は、10.0 秒かかります。この場合、パフォーマンスは 13.8/10.0=1.38 倍となり、向上はしていますが十分とは言えません。

ユーザー定義関数

次の例は、コレクションへのインデックスは一意でなければならないという事実を利用した VBA ユーザー定義関数を示しています。 ここで使用されている各種のテクニックについては、この記事の 「関数の効率的な使用」に記載されているユーザー定義関数についての説明を参照してください。

Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell
    
    COUNTU = colUniques.Count
End Function

この数式 =COUNTU(A2:A11000) の所要時間はわずか 0.061 秒で、13.8/0.061=226 倍もパフォーマンスが向上しています。

数式の列の加算

前のデータのサンプルを見ると、データが並べ替えられていることがわかります (Excel は 11,000 行を 0.5 秒で並べ替えます)。この並べ替えを実行する際に、該当する行のデータが直前の行のデータと同じであるかどうかをチェックする数式の列を追加します。直前のデータと違っていれば、この数式は 1 を返し、同じであれば 0 を返します。

セル B2 に次の数式を追加します。

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

この数式を下方向にコピーします。続いて、列 B を合計する数式を追加します。

=SUM(B2:B11000)

これらの数式の完全計算の所要時間は 0.027 秒です。パフォーマンスは 13.8/0.027=511 倍になります。

Excel 2007 におけるパフォーマンスの改善

ここでは、計算パフォーマンスを改善するために利用できる、Excel 2007 の複数の新機能について説明します。

マルチ スレッド計算

Excel 2007 では、マルチ プロセッサ (マルチ コア) で計算の分散ができるようになりました。Excel 2007 はブックを読み込むと、何個のプロセッサが使用できるかをオペレーティング システムから判断し、プロセッサごとに個別の計算スレッドを作成します。これらのスレッドは並列実行が可能です。このシステムの利点は、プロセッサの個数に応じて拡張性が非常にうまく発揮される点です。新型のコンピュータ システムの多くは、既に 2 つのプロセッサを搭載しています。また、Intel や AMD のロードマップによると、近い将来 4 つまたは 8 つのプロセッサを搭載したシステムが広く普及する見込みです。

マルチ コア搭載のシステムでは、ほとんどのブックで計算が大幅に高速化されます。高速化の度合いは、ブックに含まれる独立した計算ツリーの数によって左右されます。1 つの連続的な数式のチェーンを含むブックを作成した場合、マルチ スレッド計算 (MTC) によるパフォーマンスの向上は見られません。一方、複数の独立した数式のチェーンが含まれるブックでは、使用可能なプロセッサ数に比例してパフォーマンスが向上します。

840000 ～ 23000 個の数式を含むブックを対象に、Excel 2007 を使用してデュアル コア システムでテストを実行した結果、MTC の利用によるパフォーマンス向上率は、最高で 1.9 倍でした。向上がまったく見られないケースもありましたが、ブックが大きいほど向上率が高いことが判明しています。

次のように、Excel の一部の機能ではマルチ スレッド計算は使用されません。

• データ テーブルの計算 (ただし、テーブルへの構造化参照では MTC が使用されます)
• ユーザー定義関数 (ただし、XLL 関数はマルチ スレッド対応にすることができます)
• XLM 関数
• format2 オプションまたは address オプションを使用する INDIRECT 関数、CELL 関数
• ピポットテーブルまたはキューブを参照する GETPIVOTDATA などの関数
• Range.Calculate および Range.CalculateRowMajorOrder
• 循環参照ループのセル

複数のプロセッサを搭載したコンピュータ上で初めてブックを計算する場合、Excel によって依存関係の検証が行われるため、若干のオーバーヘッドが発生します。この結果、2 回目以降の計算ではパフォーマンスが最大限に向上します (ただし、プロセッサを 1 つしか搭載していない同じ処理速度のコンピュータで同じタスクを実行する場合に比較すると、1 回目の計算であっても、通常はパフォーマンスの向上が確認できます)。

このようなオーバーヘッドは、ブックを最後に保存したコンピュータよりプロセッサ数が多いコンピュータで、初めてそのブックを計算するときにも発生します。MTC がオフにされている場合、またはシングル プロセッサ システムで Excel 2007 を実行する場合には、MTC 機能によるパフォーマンスの変化はありません。Excel 2007 の MTC は、互換モードでも使用可能であり、Excel の以前のバージョンでブックを計算し保存した後でも、計算によって保存された情報を再利用することができます。

図 8. Excel 2007 での計算スレッド数の制御

同時に実行されるスレッド数を手動で指定できます。この数は、コンピュータに搭載されているプロセッサ数よりも大きくすることができます。この機能が役立つのは、たとえば、データベース サーバーへの長時間にわたる外部呼び出しに依存する XLL ユーザー定義関数が使用されている場合などです。データベース サーバーが複数の要求の処理を並行して実行できる場合には、シングル プロセッサ システムでも、マルチ スレッドを非常に効率よく利用できます。

MTC オプションを制御するには、Microsoft Office ボタンをクリックし、続いて [Excel オプション]、[詳細]、[数式] の順にクリックします。

メモリ容量の拡大と制限

Excel の以前のバージョンでは、スマート再計算機能で追跡される依存関係の数について、いくつかの制限がありました。これらの制限を超過すると、Excel は常に完全計算を実行し、ステータス バーには [計算] の項目が表示されました。

Excel 2007 では、これらの制限がなくなっています。2 GB の Windows メモリという全体的な制限の範囲内で、完全計算よりも著しく高速なスマート再計算を大容量の Excel ブックで常に使用することができます。

Workbook.ForceFullCalculation

Excel のオブジェクト モデルを使用することにより、ブックの新しいプロパティ Workbook.ForceFullCalculation (Beta 2 では使用できません) をプログラムで設定できます。このプロパティを True に設定すると、ブックを開くときに依存関係は読み込まれず、ブックの計算はすべて完全計算になります。

多数の複雑な依存関係を含んだブックにおいて、ブックを開くときに依存関係を読み込むのに長い時間がかかったり、完全計算よりも再計算に時間がかかるのであれば、このプロパティを使用して、依存関係の読み込みを強制的に停止し、常に完全計算を使用することができます。

SUMIFS、COUNTIFS、AVERAGEIFS

Excel 2007 には、複数の条件を使用して SUM、COUNT、または AVERAGE を実行できる 3 つの新しい関数があります。 Excel の以前のバージョンでは、複数の条件を使用するには、計算が遅くわかりづらい配列数式を使用するか、または SUMPRODUCT を使用する必要がありました。 新しい関数は、簡単に使えるだけでなく、計算も高速です。

SUMIFS(sum_range, criteria_range1, criteria1 [,criteria_range2, _
    criteria2...])
COUNTIFS(criteria_range1, criteria1 [,criteria_range2, criteria2...])
AVERAGEIFS(average_range, criteria_range1, criteria1 _
    [,criteria_range2, criteria2...])

これらの関数は、空白のセルを特殊な方法で処理することにより、完全な列参照 ($A:$A) を非常に効率よく処理します。テキスト セルを評価する条件では、ワイルドカード文字 * (任意の文字の集合) および ? (任意の 1 文字) を使用できます。これらの関数では、同等の機能を持つ配列数式よりもはるかに高速な計算が可能であるため、配列数式はできる限り、これらの関数で置き換えるようにしてください。

IFERROR

次に示す IFERROR 関数は、エラー チェックを簡素化すると共に高速化します。

IFERROR(Formula, value_if_error)

Excel の以前のバージョンでは、次のように数式を重複させることでエラーをトラップする数式が一般的でした。

=IF(ISERROR(VLOOKUP("Charles",$A$1:$C$10000,3,False),"NotFound", _ VLOOKUP("Charles",$A$1:$C$10000,3,False))

この数式を使用する場合、VLOOKUP でエラーが発生しなければ、Excel では処理を 2 回実行することになります。Excel 2007 では、次のように IFERROR を使用して、このような計算時間の重複を簡単に回避できます。

=IFERROR(VLOOKUP("Charles",$A$1:$C$10000,3,False),"NotFound")

また、この関数は理解しやすく、管理も簡単です。

名前付きテーブルと構造化参照

Excel 2007 では、数式とデータのブロックを定義する、名前付きテーブル (Excel 2007 Beta 2 では "Microsoft Office Excel テーブル" と呼ばれています) が導入されています。たとえば、Sales テーブル内の 2004 列を参照する場合に Sales[2004] などの構造化参照を使用することによって、名前付きテーブルとその列を数式で参照できます。これは、C2:C50 などの Excel の通常の参照を使用するよりも簡単な方法です。

このテクニックでの主な利点は、テーブルの行や列にユーザーがデータを追加した場合に、そのテーブルへの参照が自動的に調整される点です。構造化参照は、OFFSET などの可変的な関数と COUNTA などのカウント関数の組み合わせを必要としないので、動的な範囲を使用するよりも効率的です。もう 1 つの利点は、ワークシート内で複数の名前付きテーブルを使用し、それぞれの名前付きテーブルに対してオートフィルタを使用できる点です。

現時点で配列数式を使用している場合は、できる限り構造化参照を使用して、配列数式で計算されるセルの数を減らすようにしてください。

ユーザー定義関数

Excel 2007 のユーザー定義関数は、開発に使用された言語やアドイン方式に関係なく、関数の引数の個数や "大きなグリッド" を始め、Excel 2007 で引き上げられたさまざまな上限に対応するようになっています。"大きなグリッド" を完全にサポートする場合は、256 列または 65,536 行という上限を前提としたコードを使用しているユーザー定義関数を書き換えることになります。また、新しい Excel C API をサポートする場合は、XLL の変更を行います。

マルチ スレッド計算の利点を活用できる唯一のユーザー定義関数が、XLL です。マルチ スレッド再計算に対応するように XLL 関数を更新することで、XLL をさまざまなスレッドで同時に実行できるようになります。それ以外のすべてのユーザー定義関数 (VBA、自動化アドイン、XLM 関数、およびマルチ スレッドで動作するように更新されていない XLL) は、常にメイン スレッドで動作し、何個のプロセッサまたはスレッドが使用されていても、一度に 1 つずつしか実行できません。

Excel 2007 では、次の機能に対するサポートを提供するために新しい C API が提供されています。

• "大きなグリッド"
• マルチ スレッド計算
• より多くの関数の引数

これらの新機能を活用するには、XLL 関数を更新する必要があります。詳細については、Excel 2007 のアドイン (XLL) の開発 を参照してください。アドイン関数を更新しなくても、それらを引き続き使用することは可能ですが、Excel 2007 の新機能を活用することはできません。

範囲の計算

Excel 2007 には 2 つの Range 計算メソッドがあります。これらの計算メソッドを実行するための標準的なユーザー インターフェイスはありません。VBA または他のプログラミング言語を使用して呼び出しを実行する必要があります。これらのメソッドは、数式の他の部分は変更せずに一部のセル ブロックだけを計算したい場合に役立ちます。

Range.Calculate

Range.Calculate は、一度に 1 行ずつ、左から右、上から下へ範囲を計算し、その範囲内のすべての依存関係を解決します。このメソッドは Excel 2002 および Excel 2003 で使用されているものと同じです。ただし、Excel 2007 では手動計算モードでの反復計算を処理するように機能拡張が行われています。

Range.CalculateRowMajorOrder

Range.CalculateRowMajorOrder は、一度に 1 行ずつ、左から右、上から下へ範囲を計算しますが、依存関係をすべて無視します。これは Microsoft Excel 97 および Excel 2000 で使用されているメソッドと同じです。CalculateRowMajorOrder は依存関係を解決しないので、通常、Range.Calculate よりも著しく高速です。

数式のブロック内の依存関係が、常に左または上のセルへの逆方向参照であることが確かな場合は、シングル プロセッサ システム上で最速の Excel の計算メソッドとして Range.CalculateRowMajorOrder を使用できます。

Range.CalculateRowMajorOrder は、Excel のパフォーマンス最適化に最も役立つ手段の 1 つです。これを使用して、依存関係の作用は無視しながら、さまざまな数式の計算速度を測定して比較することができるからです。

Excel Services

Excel Services は、Microsoft Office SharePoint Server 2007 に含まれる新しいサーバー技術です。Excel Services を利用すると、時間のかかる計算をデスクトップからより高性能で強力なサーバーにオフロードすることができます。8 コアのサーバーによるマルチ スレッド計算により、パフォーマンスの大幅な向上が可能になります。Excel Services についての詳細は、Excel Services について学ぶ (英語) を参照してください。

ボトルネックを最適化するためのヒント

ここでは、発生することの多いさまざまなボトルネックを最適化するための具体的なヒントを示します。

下方参照と逆方向参照

明示性を高めてエラーを防ぐために、数式を作成するときは、他の数式またはセルを下方参照 (右または下へ向かっての参照) しないようにしてください。通常、下方参照は計算パフォーマンスに影響しませんが、ごくまれに、ブックの初回計算で、計算を遅延させざるを得ない数式が多く存在する場合には、適切な計算シーケンスを確立するのに長い時間がかることがあります。

ブック間のリンク

ブック間でリンクを設定することは、できる限り避けてください。処理に時間がかかるだけでなく、壊れやすく、エラーを発見して訂正することも必ずしも容易ではありません。

多数の小さいブックを使用するよりも、少数の大きいブックを使用する方が、通常は賢明です (例外的なケースとしては、めったに再計算されないフロント エンド計算が大量に存在し、それらを個別のブックに含めるのが合理的である場合や、RAM 容量が不十分である場合などがあります)。

閉じたブックに対して作用する、単純で直接的なセル参照を使用してみてください。そうすることで、いずれかのブックで再計算を行う際に、リンクされているすべてのブックで再計算が実行されることを回避できます。さらに、閉じたブックから Excel が読み取った値を確認することも可能になります。この確認はブックのデバッグや監査を行う際にしばしば重要になります。

やむを得ず、リンクが設定されたブックを使用する場合は、それらのブックを閉じるのではなく開いておくようにしてください。また、リンク元のブックを開く前に、リンク先のブックを開くようにします。

ワークシート間のリンク

多数のワークシートを使用するとブックが使いやすくなる場合がありますが、一般に、ワークシート内の参照を計算するよりも、他のワークシートへの参照を計算する方が処理速度は遅くなります。

Excel 97 および Excel 2000 では、ワークシートとブックの計算は、個々の計算チェーンのアルファベット名の順序に従って処理されます。これらのバージョンでは、ワークシート間の計算処理の流れに一致する順序で、ワークシートの名前を付けることが非常に重要です。

使用範囲の最小化

メモリを節約してファイル サイズを小さくするために、Excel は使用されたワークシート上の領域に関する情報のみを保存しようとします。こうした領域のことを "使用範囲" と呼びます。この使用範囲は、各種の編集処理および書式設定処理によって、ユーザーの想定する範囲以上に大きくなってしまうことがあります。その結果、パフォーマンスやファイル サイズに関するボトルネックが生じる場合があります。

Ctrl + End キーを使用すると、ワークシートの使用範囲をチェックできます。必要以上に範囲が広がっている場合には、最後に使用した実際のセルの下および右にあるすべての行と列を削除してから、ブックを保存することを検討してください。対処を行う場合は、事前に必ずバックアップ コピーを取っておくようにします。なお、削除された領域まで処理範囲が広がっている数式や、削除された領域を参照する範囲を持っている数式では、範囲が小さくなるか、または値が #N/A に変更されます。

データを追加する際の処理

ワークシートに後からデータの行や列を追加することが多い場合、そのたびに数式を見つけ出して変更するのではなく、Excel の数式に新しいデータ領域を自動的に参照させるための方法が必要になります。

この場合、現在のデータの境界よりも大きい範囲を数式に使用します。ただし、状況によっては、こうした処理の結果、計算がきわめて非効率的になることがあります。また、行や列の削除によって、気付かないうちに範囲が縮小される場合があるので、管理も容易ではありません。

Excel 2007 の構造化テーブル参照

Excel 2007 では、参照先テーブルのサイズの増減に応じて自動的に拡大縮小する、構造化テーブル参照を使用することができます。この方法には次のような利点があります。

• 列全体の参照や変動的な範囲を使用する方法と比べて、パフォーマンス上のデメリットが少ない。
• 1 つのワークシートで複数のデータ テーブルを簡単に使用できる。
• テーブルに埋め込まれた数式も、データに応じて拡大縮小処理される。

列および行全体の参照

別のアプローチは、$A:$A のような、列全体の参照を使用する方法です。この参照では列 A のすべての行が返されます。そのため、データを次々に追加しても、参照には常に必要なデータが含まれるようになります。

ただし、この方法には、利点と共にいくつかの制約もあります。

• Excel の多くの組み込み関数 (SUM、SUMIF) は、列の中で最後に使用された行を自動的に認識することにより、列全体の参照を効率的に計算します。しかし、SUMPRODUCT などの配列計算関数は、列全体の参照を処理することができないか、または列の全セルを計算することができません。
• ユーザー定義関数は、列で最後に使用された行を自動的には認識しないため、列全体の参照の計算が非効率的になる傾向があります。ただし、最後に使用された行を認識するようにユーザー定義関数をプログラミングすることは、簡単にできます。
• 1 つのワークシートに複数のデータ テーブルが存在する場合、列全体の参照を使用することは難しくなります。
• Excel 2007 より前のバージョンでは、配列数式は列全体の参照を処理できません。Excel 2007 では、配列数式は列全体の参照を処理できますが、その場合、空白のセルも含めて列のすべてのセルが強制的に計算されます。そのため、特に行数が 100 万行に上るようなケースでは、計算が極端に遅くなる可能性があります。

動的な範囲

名前付き範囲の定義で OFFSET および COUNTA 関数を使用することにより、その名前付き範囲が参照する領域を、動的に拡大縮小させることができます。 たとえば、次のように定義済みの名前を作成します。

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

この動的な範囲名を数式で使用すると、新しいエントリを含むように範囲が自動的に拡大されます。

この場合、OFFSET は可変的な関数であるために常に再計算され、また、OFFSET 内部の COUNTA 関数は大量の行を検証しなければならないので、パフォーマンスが低下することになります。 パフォーマンスへの影響を抑えるには、数式の COUNTA の部分を別のセルに格納し、そのセルを動的な範囲で参照します。

Counts!z1=COUNTA(Sheet1!$A:$A)
DynamicRange=OFFSET(Sheet1!$A$1,0,0,Counts!$Z$1,1)

このほか、INDIRECT などの関数を使用して、動的な範囲を作成することもできます。動的な範囲には、利点と共にいくつかの制約もあります。

• 動的な範囲は、配列数式によって実行される計算の回数を制限するために、非常に有効です。
• 1 つの列に対して動的な範囲を複数使用するには、専用のカウント関数が必要です。
• 動的な範囲を数多く使用すると、パフォーマンスが低下する可能性があります。

検索

検索は、計算での主要なボトルネックになりがちです。そこで、検索での計算時間を短縮するためのさまざまな方法が用意されています。完全一致オプションを使用した検索の場合、関数の計算時間は、一致が見つかるまでにスキャンされるセルの数に比例します。検索の対象範囲が大きいと、非常に長い時間がかかる場合があります。

データの並べ替えを行った後、VLOOKUP、HLOOKUP、MATCH で近似値も対象にした検索オプションを使用して検索を実行すると、処理速度は向上し、検索対象の範囲に影響される度合いも少なくなります (特性はバイナリ検索の場合と同じです)。

検索オプション

ここで、MATCH、VLOOKUP、および HLOOKUP の検索のタイプや範囲検索のオプションについて確認しておきましょう。

MATCH(lookup value, lookup array, matchtype)

• matchtype が 1 の場合は、検索配列が昇順に並べ替えられている状況で、検索値と等しい値か、または検索値より小さい値の中で最も大きい値を返します (近似値を含めた検索)。 これが既定のオプションです。
• matchtype が 0 の場合は、検索値に完全に一致する値が検索されます。これは、データが並べ替えられていないことを前提とします。
• matchtype が -1 の場合は、検索配列が降順に並べ替えられている状況で、検索値と等しい値か、または検索値より大きい値の中で最も小さい値を返します (近似値を含めた検索)。

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の場合は、検索値に完全に一致する値が検索されます。これは、データが並べ替えられていないことを前提とします。

処理が遅くなるため、並べ替えられていないデータを対象に検索を実行することは、できるだけ避けてください。並べ替えられたデータを対象に、完全一致の検索を実行したい場合は、この記事の「並べ替えられたデータに見つからない値がある場合」を参照してください。

VLOOKUP の代わりに INDEX と MATCH の組み合わせ、または OFFSET を使用する方法

VLOOKUP の代わりに、INDEX と MATCH を組み合わせて使用する方法について検討してみましょう。VLOOKUP は、MATCH と INDEX の組み合わせ、または OFFSET と比べると、若干 (約 5%) 高速であり、かつ、よりシンプルで、使用するメモリも少なくて済みます。しかし、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 つのワークシートを使用します。検索のための式とデータは、同じシートに入れておく方が高速になります。
• できる限り、事前にデータの並べ替えを行い、かつ、近似値を含めた検索を使用します。
• 完全一致検索を使用する必要がある場合は、スキャンするセルの範囲を最小限にします。行または列を広範囲にわたって参照するのではなく、動的な範囲の名前を使用します。状況によっては、検索の下限または上限を事前に計算しておくこともできます。

並べ替えられたデータに見つからない値がある場合

複数行にわたる範囲を検索する場合、完全一致検索 1 つを実行するよりも近似値を含めた検索 2 つを実行する方がはるかに高速に処理されます (10 ～ 20 行を目安に検討を行ってください)。

データを並べ替えることができても、検索する値が検索範囲内に存在するかどうかが定かではないために、近似値を含めた検索が実行できない場合には、次の数式を使用できます。

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

以下に抜粋する、この数式の最初の部分は、検索列そのものに対して近似値を含めた検索を実行します。

VLOOKUP(lookup_val ,lookup_array,1,True)

検索列から返された結果が検索値と一致することを示す数式は以下の部分になります。

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

完全一致が見つかったので、再び近似値を含めた検索を実行できます。ただし今回は、希望する列から結果を返します。

VLOOKUP(lookup_val, lookup_array, colnum, True)

検索列から返された結果が検索値と一致しない場合、それは存在しない値であり "notexist" が返されます。

リストの最小値よりも小さい値を検索するとエラーになる、という点に注意してください。このエラーを処理するには、IFERROR を使用するか、または非常に小さいテスト値をリストに追加します。

並べ替えられていないデータに見つからない値がある場合

並べ替えられていないデータに対して完全一致検索を使用する必要があり、検索値が存在するかどうかが定かでない場合、一致が見つからなかったときに返される #N/A を処理しなければならないことが多くあります。Excel 2007 では、次のような IFERROR 関数を使用できます。これは簡単かつ高速です。

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

以前のバージョンの Excel では、次のような、2 つの検索を含む IF 関数を使用する方法があります。この方法は簡単ですが、計算は遅くなります。

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

次のように完全 MATCH を 1 回だけ使用し、その結果を任意のセルに格納して、結果をテストしてから INDEX を実行することにより、2 つの完全検索の実行を回避できます。

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

2 つのセルを使用できない場合は、COUNTIF を使用します。これは通常、完全一致検索よりも高速です。

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

複数の列に対する完全一致検索

完全一致検索の MATCH ステートメントを保存しておき、何度も再利用することができます。たとえば、複数の結果列に対して完全検索を実行する場合、単一の MATCH ステートメントと複数の INDEX ステートメントの組み合わせを多数の VLOOKUP ステートメントの代わりに使用することで、大幅に時間を節約できます。

結果を格納するための MATCH 用の行 (stored_row) を 1 つ余分に追加して、それぞれの結果列について、次の関数を使用します。

INDEX(Lookup_Range,stored_row,column_number)

または、配列数式の VLOOKUP を使用することもできます。

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

連続する行または列の集合の検索

1 回の検索処理によって複数のセルを返すこともできます。いくつかの連続する列を検索したい場合は、INDEX 関数を配列数式で使用して、複数の列を一度に返すことができます (列番号として 0 を使用します)。また、INDEX 関数では、複数の行を一度に返すこともできます。

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

この場合、前回の MATCH ステートメントで作成した stored_row から、列 A ～ J が返されます。

セルのブロックの検索

MATCH および OFFSET 関数を使用して、セルのブロックを返すことができます。

2 次元検索

テーブルの行と列に個別の検索を使用することで、2 次元的なテーブル検索を効率的に実行するには、2 つの MATCH 関数 (行と列に対してそれぞれ 1 つずつ) を埋め込んだ INDEX 関数を使用します。

複数インデックス検索

大きなワークシートでは、たとえば特定の国における生産高を調べるときなど、複数のインデックスを使用した検索を実行しなければならないことがよくあります。単純なやり方としては、インデックスを連結し、連結された検索値を使用する、という方法があります。これは、次の 2 つの理由により非効率的であると言えます。

• 文字列の連結は集中的な計算を必要とする処理です。
• 検索範囲が広がることになります。

この場合、検索のサブセット範囲を計算する方が効率的です。たとえば、その国に関する最初と最後の行を探し出し、その範囲内で生産高を調べます。

3 次元検索

行、列に加えて、使用するテーブルを検索する必要がある場合は、以下に説明する、Excel によるテーブルの検索方法や選択方法に着目した各種のテクニックを利用できます。

検索したい各テーブル (3 つ目の次元) が、名前付き構造化テーブルの集合、範囲名、または範囲を表すテキスト文字列のテーブルとして保存されている場合は、INDIRECT 関数または CHOOSE 関数を使用できる可能性があります。

CHOOSE と範囲名を使用すると、非常に効率が上がることがあります。CHOOSE は可変的ではありませんが、テーブルの数が比較的少ない場合には最も適した関数です。

INDEX(CHOOSE(TableLookup_Value,TableName1,TableName2,TableName3), _
MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

上の例では、TableLookup_Value を動的に使用して、検索テーブルに使用する範囲名 (TableName1、TableName2、 ...) を選択しています。

INDEX(INDIRECT("Sheet" & TableLookup_Value & "!$B$2:$Z$1000"), _ MATCH(RowLookup_Value,$A$2:$A$1000),MATCH(colLookup_value,$B$1:$Z$1))

この例では、INDIRECT 関数および TableLookup_Value を使用して、検索テーブルに使用するシート名を動的に作成しています。この方法には単純であるというメリットがあり、多数のテーブルを処理することができます。しかし、INDIRECT は可変的な関数なので、データが変更されていない場合であっても、計算のたびに検索が処理されます。

次のように、VLOOKUP 関数を使用してシート名またはテーブルに使用するテキスト文字列を検索し、その後 INDIRECT 関数を使用してテキストを範囲に変換することもできます。

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

もう 1 つのテクニックは、1 つの大きなテーブルに個々のテーブルを示す列を追加して、すべてのテーブルを集約することです。これは、前の例で示した複数インデックス検索に使用できます。

ワイルドカード検索

MATCH、VLOOKUP、および HLOOKUP の各関数では、ワイルドカード文字 ? (任意の 1 文字) および * (文字なし、または任意の数の文字列) を、アルファベットの完全一致検索の中で使用できます。この方法は、複数の一致結果が返されることを回避する場合に使用されることがあります。

配列数式と SUMPRODUCT

配列数式と SUMPRODUCT 関数は非常に強力ですが、これらを使用するときは注意が必要です。状況によっては、1 つの配列数式で非常に大量の計算が必要になる可能性があります。

配列数式の計算速度を最適化するには、配列数式で評価されるセルおよび式の数をできる限り少なくすることが重要です。配列数式は、可変的な数式に類似した部分があります。参照するセルのいずれかが変化したり、可変的であったり、再計算されていたりすると、配列数式は数式内のすべてのセルを計算し、その計算に必要な仮想セルをすべて評価します。

配列数式の計算速度を最適化する方法は、次のとおりです。

• 配列数式から式および範囲の参照を取り出し、個別のヘルパー列およびヘルパー行に入れます。これにより、Excel のスマート再計算をはるかに効率よく活用できます。
• 全行を参照したり、必要以上に多くの行や列を参照したりしないようにします。配列数式は、空白のセルや使用されていないセルであっても、数式内のすべてのセル参照を強制的に計算します。Excel 2007 では 100 万行が使用可能なので、列全体を参照する配列数式はきわめて計算が遅くなります。
• Excel 2007 の構造化参照をできる限り使用して、配列数式によって評価されるセル数を最小限に抑えます。
• Excel 2007 を使用していない場合は、動的な範囲名をできる限り使用します。これらの範囲名は可変的ですが、範囲のサイズが最小限に保持される点で有益です。
• 行と列の両方を参照する配列数式に注意します。この場合、四角い領域が強制的に計算されます。
• 可能であれば SUMPRODUCT を使用します。同等の機能を持つ配列数式よりもやや高速です。

配列数式による複数の条件付きの合計

Excel 2007 では、可能であれば配列数式の代わりに SUMIFS、COUNTIFS、AVERAGEIFS の各関数を使用してください。これらの関数の方がはるかに計算が高速です。

Excel 2007 より前のバージョンでは、複数の条件付きの合計を計算する目的で、配列数式が多く使用されています。これは、特に Excel の条件付き合計式ウィザードを使用すればかなり簡単に実行できますが、しばしば非常に計算が遅くなります。通常は、同等の結果をより高速に得られる方法があります。複数の条件付きの合計の数がわずかである場合には、同等の機能を持つ配列数式よりもはるかに高速な DSUM 関数を使用できる可能性があります。

配列数式を使用する必要がある場合は、次の方法でスピードアップできます。

• 動的な範囲名または Excel 2007 の構造化テーブル参照を使用して、セル数を最小限に抑えます。
• 複数の条件をヘルパー数式 (各行に対して True または False を返します) の列に分割し、そのヘルパー列を SUMIF または配列数式で参照します。これは、1 つの配列数式の計算回数を削減する方法には見えないかもしれませんが、実際にほとんどの場合、スマート再計算プロセスによって、再計算を必要とするヘルパー列の数式だけが再計算されることになります。
• すべての条件を 1 つの条件に連結し、SUMIF を使用することを検討します。
• データの並べ替えが可能な場合は、行のグループをカウントし、配列数式でサブセット グループだけを処理することをお勧めします。

複数条件を持つ配列数式に対応する SUMPRODUCT の使用

Excel 2007 では、可能であれば SUMPRODUCT 数式の代わりに SUMIFS、COUNTIFS、および AVERAGEIFS 関数を使用してください。

以前のバージョンの場合、SUM 配列数式の代わりに SUMPRODUCT を使用すると次のような利点があります。

• Ctrl + Shift + Enter キーを使用して配列で入力する必要がありません。
• 通常、やや (5 ～ 10%) 高速です。

複数条件を持つ配列数式に対応する SUMPRODUCT は、次のように使用します。

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

この例では、Condition1 およびCondition2 は、$A$1:$A$10000<=$Z4 のような条件式です。条件式は数値ではなく True または False を返すので、SUMPRODUCT 関数の内部で強制的に数値に変換する必要があります。この場合、2 つのマイナス記号 (--) を使用するか、0 を加算 (+0) するか、または 1 で乗算 (*1) します。-- は、+0 や *1 よりもやや高速になります。

条件式で使用する範囲や配列のサイズと形状は、合計する範囲と同じでなければならず、また、列全体を含むことはできない点に注意してください。

次のように、SUMPRODUCT の内部の各項をカンマで区切るのではなく、直接乗算することもできます。

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

これは、カンマ構文を使用する場合よりもやや低速であり、また、合計する範囲にテキスト値が含まれているとエラーになります。しかし、条件に列が 1 つしかない場合には、合計する範囲に複数の列などが使用できるという点で、やや柔軟性の高い方法であると言えます。

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 つの数式はいずれも同一の結果を返しますが、3 番目の数式 (カンマ構文を使用した SUMPRODUCT) の計算時間は、他の 2 つの数式の 77% ほどです。

配列および関数の計算でのボトルネック

Excel の計算エンジンは、配列数式と、範囲を参照する関数向けに最適化されています。ただし、このような数式や関数が通常とは異なる配置を取っている場合は、常にとは限りませんが、計算が大幅に遅くなる要因になります。

配列数式および範囲を参照する関数にかかわる計算のボトルネックが見つかった場合は、次のような状態になっていないかどうかを確認してください。

• 参照が部分的に重複している。
• 数式や関数が、別の数式や関数によって計算されるセルのブロックの一部分を参照している。このような状況は、時系列分析で多く見られることがあります。
• ある数式の集合が行によって参照を行っている状況で、その集合を別の数式の集合が列によって参照している。
• 1 行の配列数式の大きな集合が列のブロックにまたがり、各列の一番下に SUM 関数がある。

関数の効率的な使用

関数は Excel の機能を大幅に強化しますが、その一方で、関数の使い方によって計算時間が左右されることも多くあります。

範囲を処理する関数

SUM、SUMIF、SUMIFS など、範囲を処理する関数では、計算時間は合計またはカウントの対象となるセル数に比例します。使用されないセルは検証されないので、列全体を参照しても問題はありませんが、使用されているセルは必要以上に多く含めないようにする方が得策です。テーブルを使用するか、サブセット範囲または動的な範囲を対象に計算してください。

可変的な関数

可変的な関数では、計算のたびに再計算を必要とする数式の数が増えるため、再計算が遅くなるおそれがあります。

一般に、OFFSET の代わりに INDEX、INDIRECT の代わりに CHOOSE をそれぞれ使用することで、可変的な関数を減らすことができます。ただし、OFFSET は処理の速い関数であるため、非常に高速な計算を実行するクリエイティブな方法においてはしばしば利用されます。

ユーザー定義関数

C または C++ でプログラミングされ、C API を使用するユーザー定義関数 (XLL アドイン関数) は、VBA または Automation を使用して開発されたユーザー定義関数 (XLA または Automation アドイン) よりも、一般に高速です。詳細については、Excel 2007 のアドイン (XLL) の開発を参照してください。

C による XLL アドイン関数と同一の、緊密に連結された API を使用する XLM 関数も高速です。VBA によるユーザー定義関数のパフォーマンスは、プログラミングの方法と呼び出しの方法によって大きく左右されます。

より高速な VBA ユーザー定義関数

通常は、VBA ユーザー定義関数を使用するよりも、Excel の数式計算およびワークシート関数を使用する方が高速です。その理由は、ユーザー定義関数を呼び出す際にわずかなオーバーヘッドが生じ、Excel からユーザー定義関数に情報を転送する際に著しいオーバーヘッドが生じるからです。ただし、ユーザー定義関数の設計と呼び出しを詳細に行うことで、複雑な配列数式よりもはるかに高速になる可能性があります。

ワークシートのセルへのすべての参照を、ユーザー定義関数の本体ではなく、その入力パラメータで指定するようにしてください。そうすれば、Application.Volatile を無駄に追加せずに済みます。

ユーザー定義関数を使用する数式を大量に使用する必要がある場合には、手動計算モードを使用し、VBA から計算が開始されるようにしてください。VBA から計算を呼び出さない場合 (たとえば自動計算モードの場合や、手動計算モードで F9 キーを押した場合など)、VBA によるユーザー定義関数の計算は非常に遅くなります。 ** Visual Basic Editorが開かれた場合や (Alt + F11 キーで起動されます)、現在の Excel セッションで既に開いた状態になっている場合には、特にその傾向が強くあります。

次のように F9 をトラップして、VBA 計算サブルーチンにリダイレクトすることができます。このサブルーチンを Thisworkbook モジュールに追加します。

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

次のサブルーチンを標準モジュールに追加します。

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

Automation アドイン のユーザー定義関数 (Excel 2002 以降のバージョンの場合) は、統合型エディタを使用しないので、Visual Basic Editor のようなオーバーヘッドは発生しません。Visual Basic 6 による Automation アドインのユーザー定義関数におけるその他のパフォーマンス特性は、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 つ使用することにより、このようなオーバーヘッドを最小限にできる場合があります。入力範囲が複数のセルで構成され、一連の結果を返す 1 つの関数に、複数の関数呼び出しを組み合わせます。

より高速な VBA マクロ

この記事では、VBA の実行速度よりも Excel の計算速度に重点を置いて解説していますが、ここで、VBA マクロを高速化するための基本的なヒントを紹介します。

• マクロの実行中は、画面更新と計算を無効にします。
• セル単位でループするのではなく、2 次元配列を含むバリアントで、範囲からのデータを取得します。
• Range オブジェクトなどの Excel オブジェクトは、選択したりアクティブ化するのではなく、直接的に参照します。
• 配列を Range に直接的に割り当てることにより、結果を返します。
• マクロが終了した時点で、画面更新と計算を有効にします。

次に、各列の数値を合計するマクロの例を示します (言うまでもなく、実際は、同じ処理を実行するには Excel の SUM 関数を使用した方がはるかに高速です)。

Option Explicit
Option Base 1
Sub myMacro()
' Sum each column of input data and store the result.

    Dim vData As Variant
    Dim dOutput() As Double
    Dim j As Long
    Dim k As Long

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' Assign the range to a variant containing an array.
    vData = Worksheets("InputData").Range("B2:K10000")

    ' Set up the result array.
    ReDim dOutput(1, UBound(vData, 2))

    ' Sum the numbers.
    For k = 1 To UBound(vData, 2)
        For j = 1 To UBound(vData, 1)
            If IsNumeric(vData(j, k)) Then
                dOutput(1, k) = dOutput(1, k) + vData(j, k)
            End If
        Next j
    Next k

    ' Return results by assigning the result array to the range.
    Worksheets("Results").Range("B2"). _
    Resize(1, UBound(dOutput, 2)) = dOutput

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
End Sub

SUM および SUMIF

Excel の SUM 関数と SUMIF 関数は、大量のセルに対して使用されることが頻繁にあります。これらの計算に要する時間は対象となるセルの数に比例するため、関数が参照するセルの範囲は最小限に抑えるようにします。

SUMIF および COUNTIF でのワイルドカードの使用

ワイルドカード文字 ? (任意の 1 文字) および * (文字なし、または任意の数の文字列) を、SUMIF および COUNTIF のアルファベット範囲に関する条件として使用できます。

該当セルまでの合計と累積的な合計

該当セルまでの合計や累積的な合計を計算するには、2 つの方法があります。累積的な合計の対象となる数値が列 A に格納されており、列 B で合計を示す場合は、次のいずれかの方法を使用できます。

• 列 B に=SUM($A$1:$A2) などの数式を作成し、適切な行まで下方向へドラッグします。SUM の開始セルは A1 に固定されますが、終了セルには相対的な行参照が指定されているので、値は行ごとに自動的に増加します。
• セル B 1 に=$A1 などの数式を、B 2 に=$B1+$A2 などの数式をそれぞれ作成し、適切な行まで下方向へドラッグします。各行の数値を直前の累積合計に加算することにより、それぞれのセルで累積的な処理が行われます。

1,000 行の場合、最初の方法では Excel は約 500,000 回の計算を実行します。一方、2 番目の方法では約 2,000 回の計算にしかなりません。

サブセットの合計

1 つのテーブルに対して並べ替えられた複数のインデックスがある場合 (たとえば地域内の拠点など)、以下のように、SUM 関数や SUMIF 関数で使用する行 (または列) のサブセット範囲のアドレスを動的に計算することにより、計算時間を大幅に節約できる可能性があります。

1. 各サブセット ブロックの行数をカウントします。
2. ブロックごとにカウントを累積的に加算し、開始行を判別します。
3. OFFSET で開始行とカウントを使用して、サブセット範囲を SUM または SUMIF に返し、行のサブセット ブロックのみを処理します。

小計

SUBTOTAL 関数は、SUM によって処理されたリストで使用します。SUBTOTAL 関数は SUM とは異なり、次の要素を無視するので便利です。

• リストをフィルタした結果としての非表示の行。Excel 2003 および Excel 2007 では、フィルタされた行だけでなく、非表示のすべての行を SUBTOTAL に無視させることができます。
• 他の SUBTOTAL 関数。

D 関数

DSUM、DCOUNT、DAVERAGE などの D 関数は、同等の機能を持つ配列数式よりも著しく高速です。D 関数の欠点は、条件が独立した範囲内に存在しなければならない点であり、このため、多くの状況で実用性を欠き管理も難しくなりがちです。Excel 2007 では、D 関数の代わりに SUMIFS、COUNTIFS、および AVERAGEIFS 関数を使用してください。

ピボットテーブル

ピボットテーブルは、大量のデータの概要を把握するための非常に効率的な手段になります。

最終結果としての合計

ブックの最終結果の一部分として合計および小計を算出する必要がある場合は、ピボットテーブルの使用を検討してください。

中間結果としての合計

ピボットテーブルは概略的なレポートを作成するための優れた手段ですが、次の条件が保証される場合を除いて、ピボットテーブルの結果を計算チェーンの中間的な合計および小計として使用する数式は作成しないでください。

• 計算中にピボットテーブルが適切に更新されているる。
• ピボットテーブルは変更されておらず、情報は依然として可視的である。

上記の条件が当てはまらない状況で、ピボットテーブルを中間結果として使用する必要がある場合は、GETPIVOTDATA 関数を利用してください。

反復のある循環参照

反復のある循環参照の計算は、複数の計算が必要になるため低速です。循環参照を "解決" して、反復計算を不要にできる場合が多くあります。たとえば、キャッシュ フローと利息を計算する場合、キャッシュ フローを計算してから利息を計算し、次に利息を含めたキャッシュ フローを計算します。

Excel は、依存関係を考慮せずに、循環参照をシートごとに計算します。したがって循環参照が複数のワークシートにまたがっている場合、計算が非常に低速になるのが普通です。循環参照を単一のワークシートに移動するか、ワークシートの計算シーケンスを最適化して、不要な計算を回避してください。

Excel は、反復計算を開始する前にブックを再計算し、すべての循環参照とその参照先を識別しなければなりません。このプロセスは計算の 2 ～ 3 回分の反復に相当します。

循環参照とその参照先を識別した後、Excel は反復のたびに循環参照のすべてのセルだけでなく、循環参照チェーンの中でそのセルに依存するセルを (可変的なセルとその参照先も含めて) 計算しなければなりません。したがって、循環参照のセルに関連付けられた複雑な計算を使用する場合は、個別の閉じたブックに計算を隔離し、循環参照が収束した後、そのブックを開いて再計算を実行する方が高速になります。

また、基本的な事項となりますが、循環参照に使用するセルの数と、それらのセルによって発生する計算時間を最小限に抑えることも重要です。

条件付き書式とデータの入力規則

条件付き書式とデータの入力規則は有益ですが、これらを使いすぎると計算が著しく低速になる場合があります。条件付き書式の数式は計算のたびに評価されるだけでなく、条件付き書式を含むセルの表示が更新されるたびに評価されます。Excel オブジェクト モデルには、条件付き書式の計算の有効化/無効化を設定できる Worksheet.EnableFormatConditionsCalculation プロパティがあります。

定義名

定義名は Excel の最も強力な機能の 1 つですが、その分だけ余計に計算時間がかかります。他のワークシートを参照する名前を使用すると、計算プロセスの複雑さのレベルが上がります。また、入れ子になった名前 (他の名前を参照する名前) は使用しないでください。

名前は、それを参照する数式が計算されるたびに計算されるので、集中的な計算を必要とする数式または関数を定義名に入れるのは避ける必要があります。このような場合には、追加のセルを用意して、集中的な計算を必要とする数式または関数をそこに格納し、定義名の代わりにそのセルを直接、または名前を使用して参照させます。これにより、計算がかなり高速化されます。

使用頻度の低い数式

ほとんどのブックには、入力データを計算に適した形式にしたり、データのサイズや形状が変更されないようにするための数式や検索が大量に含まれています。使用頻度の低い数式のブロックがある場合は、特定の値をコピー アンド ペーストして一時的に数式を除外するか、ほとんど開くことのない別のブックに数式を格納するとよいでしょう。数式が値に変換されていることに気付かず、そのためにワークシート エラーが発生するケースが多いため、別のブックを使用する方法の方が望ましいと考えられます。

ブックの開閉、保存、およびサイズ

ブックの開閉や保存が、そのブックの計算よりもはるかに遅いことがあります。単にブックが大きいことが原因の場合もありますが、それ以外の理由も考えられます。

Excel 2007 のファイル形式のパフォーマンスとサイズ

Excel 2007 では、ファイル形式が以前のバージョンと比べて豊富になっています。マクロ、テンプレート、アドイン、PDF、XPS などのファイル形式のバリエーションもありますが、特に重要な形式は XLS、XLSB、XLSX の 3 つです。

XLS 形式

XLS 形式は、以前のバージョンと同じファイル形式です。この形式を使用する場合、256 列、65,536 行の制限が適用されます。Excel 2007 ブックを XLS 形式で保存すると、Excel は互換性チェックを実行します。ファイル サイズは以前のバージョンとほぼ同じであり (いくつかの追加的な情報が保存される場合があります)、パフォーマンスは以前のバージョンよりやや低速になります。

XLSB 形式

XLSB は Excel 2007 のバイナリ形式です。この形式は、大量のバイナリ ファイルを含む圧縮フォルダとして構成されます。XLS 形式よりもはるかにコンパクトですが、圧縮量はブックの内容によってかなり差があります。たとえば、10 個のブックのサイズ減少率は 2 分の 1 ～ 8 分の 1 とばらつきがあり、平均減少率は 4 分の 1 です。Excel 2007 でブックを開くときと保存するときのパフォーマンスは、XLS 形式よりもやや遅い程度です。

XLSX 形式

XLSX は Excel 2007 の XML 形式です。これは大量の XML ファイルを含む圧縮フォルダです (ファイル名拡張子を .zip に変更すると、圧縮フォルダを開いて内容を検証できます)。通常、XLSX 形式で作成されるファイルは XLSB 形式よりも大きくなりますが (平均 1.5 倍)、それでも XLS ファイルに比較するとかなりコンパクトです。ブックの開閉に要する時間は、XLSB 形式よりもやや長くなります。

開閉の遅さ

ブックの開閉に異常に時間がかかる場合、次のいずれかの問題が原因と考えられます。

一時ファイル

\Windows\Temp ディレクトリ (Microsoft Windows 95、Microsoft Windows 98、および Microsoft Windows Me の場合)、または \Documents and Settings\User Name\Local Settings\Temp ディレクトリ (Microsoft Windows 2000 および Microsoft Windows XP の場合) に一時ファイルが蓄積される場合があります。Excel はブック用に (特に、開いたブック内で使用されるコントロール用に) これらのファイルを作成します。ソフトウェアのインストール プログラムも、一時ファイルを作成します。Excel が何らかの理由で応答しなくなったときに、こうしたファイルを削除することが必要になる場合があります。

一時ファイルは、あまりに増えすぎると問題が起こる可能性があるため、ときどき削除を行う必要があります。ただし、コンピュータの再起動を必要とするソフトウェアをインストールした後にまだ再起動を行っていない場合は、再起動後に一時ファイルを削除してください。

一時ファイルのディレクトリを簡単に開くには、Windows の [スタート] メニューで [スタート]、[ファイル名を指定して実行] の順にクリックし、テキスト ボックスに「%temp%」と入力して [OK] をクリックします。

共有ブック内の変更の追跡

共有ブック内の変更を追跡すると、ブックのファイル サイズが急速に増加します。

断片化されたスワップ ファイル

Windows のスワップ ファイルが存在するディスクに十分な空き容量があることを確認し、そのディスク上で定期的にデフラグを実行してください。

パスワードによって保護されたブック

ブックの構成がパスワードによって保護されている場合 ([ツール] メニューで [保護] をポイントし、[ブックの保護] をクリックしてオプション設定のパスワードを入力)、パスワードを使用せずに保護されているブックと比べて開閉がはるかに遅くなります。

使用範囲の問題

使用範囲が大きすぎると、ファイルを開く動作に時間がかかり、ファイル サイズが増加することがあります。標準外の高さ/幅を持つ非表示の行/列が原因となっている場合は、特にその傾向があります。使用範囲の問題についての詳細は、この記事の「使用範囲の最小化」 (「ボトルネックを最適化するためのヒント」) を参照してください。

ワークシート上の大量のコントロール

ワークシート上にコントロール (チェック ボックス、ハイパーリンクなど) が大量に存在する場合、使用される一時ファイルの数が原因で、ブックを開く動作が遅くなることがあります。この場合、WAN 経由や LAN 経由でブックを開いたり保存したりする際にも、問題が起こることがあります。解決にあたっては、ブックの再設計を検討してください。

他のブックへの大量のリンク

可能であれば、リンク先のブックを開いてから、リンクの含まれたブックを開くようにしてください。閉じているブックからリンクを読み取るよりも、ブックを開く方が速い場合が多くあります。

ウイルス スキャンの設定

特にサーバー上では、ある種のウイルス スキャン設定が原因で、開閉または保存が遅くなったり問題が生じたりする場合があります。これが原因と考えられる場合は、ウイルス スキャンを一時的に無効にしてください。

計算速度に起因する開く動作/保存動作の遅さ

ブックを開くまたは保存する際、Excel によって再計算が行われることがあります。ブックの計算時間が長いことが原因で問題が起きている場合は、計算を [手動] に設定していることを確認すると共に、[ブックの保存前に再計算を行う] オプション ([ツール] メニューで [オプション] を選択し、続いて [計算] を選択します) をオフにすることを検討してください。

ツールバー ファイル (.xlb)

ツールバー ファイルのサイズを確認してください。通常、ツールバー ファイルのサイズは 10 ～ 20 KB です。XLB ファイルを検索するには、Windows の検索機能を使用して *.xlb を検索します。ユーザーごとに固有の XLB ファイルがあります。各ユーザーがツールバーに対して追加、変更、またはカスタマイズが行われた場合、該当の XLB ファイルのサイズが増加します。このファイルを削除すると、ツールバーのカスタマイズ内容はすべて削除されます ("toolbar.OLD" のようにファイル名を変更しておくと安心です)。削除後は、次に Excel を開いた時点で、新しい XLB ファイルが作成されます。

まとめ

Excel 2007 を使用すると、従来よりもはるかに大きなワークシートを効率的に管理することか可能になるほか、計算速度も大幅に向上します。大きなワークシートを作成する場合、結果として計算が遅くなるような方法で作成してしまいがちです。ワークシートの計算処理が遅いと、計算中にユーザーが集中力を保つことが難しくなりエラーが増えます。

一連の簡単なテクニックを使用すれば、ほとんどの場合、計算の遅いワークシートを何十倍、何百倍にもスピードアップすることができます。ワークシートを設計および作成するときも、これらのテクニックを応用して計算速度を確保できます。

執筆者について

Charles Williams 氏は、Microsoft Excel とリレーショナル データベースに基づく高度なコンサルティング、意思決定支援ソリューション、およびツールを提供する企業である Decision Models を 1996 年に設立しました。広く普及している Excel パフォーマンス プロファイラおよびパフォーマンス ツールセット FastExcel の作成者であり、定義名の管理ユーティリティとして定評のある Name Manager の共同作成者でもあります。Excel の計算パフォーマンスと方法、メモリ使用、および VBA ユーザー定義関数についての詳細は、Decision Models の Web サイト (英語) を参照してください。

この記事は、A23 Consulting(英語) と共同で執筆しました。

追加情報

Excel 2007 についてさらに詳しく知るには、以下の情報を参照してください。

• Microsoft Office デベロッパー センター: Excel 2007 の開発者向け新機能 (英語)
• ブログ: Microsoft Excel 2007 (英語)