エクセルの高速化には？

質問

会社で他部門の在庫管理システムをエクセルで作ることになりそうなのですが、機種数200、部品点数各80、総部品点数10000くらいらしく、構想段階からかなり重たいブックになりそうな予感がしています。
そこで、高速化するためのコツをご教授願いたいのです。
質問番号：2539700　によれば、
・参照関数を極力避け、使うにしても参照範囲を限定する
・配列数式もできれば避ける
・ブック間参照を減らす
・VBAを使う
・アクセスでシステムを組む
といった回答がありましたが、そのほかに思いつくこととしては、ネストができるだけ浅くなるような関数を選択するというくらいしか思いつきません。あとはどのようなことが挙げられるでしょうか？
たとえば、普通に関数を使うのと、それを条件付き書式に組み入れるのとで比較すれば、後者の方が重くなります？
ユーザー関数を使えば書式は簡単になりますが、重さの点ではどうなのか、といった点も教えていただければ幸いです。
よろしくお願いします。

回答No.2

Wendy02 · ベストアンサー

こんにちは。

一般的な方法論というのは、あまり大した効果はないとは思います。

ワークシートでは、安定した値を保管できるものは、保管させればよいのです。だから、補助セル・補助列で演算して、それを参照させればよいわけです。その意味は、数式というのは、基本的には、参照セルの変化に対して、解を変化させます。しかし、そうでない関数もあります。

今さらの話ですが、
http://support.microsoft.com/default.aspx?scid=kb;ja;402644
[XL95]変更していないブックを閉じる際保存するか尋ねられる場合

たとえば、 RAND関数のように、値を保持しないものは、極力使わないようにします。
ただ、神経質になる必要はないし、VBAで、それを避けることも可能です。

上記のサイトの関数は、再計算関数とか揮発性関数と呼びますが、完全になくすことはできませんから、その場合に、数式の数を増やさないとか参照範囲を広げないとかします。

前の書き込みで、
=IF(COUNTIF(C:C,A1)>0,VLOOKUP(A1,C:C,2,0),"")

=IF(COUNTIF($C$1:$C$250,A1)>0,VLOOKUP(A1,$C$1:$C$250,2,0),"")

どちらが速いかは、どちらもデータが、250行目までなら、ほとんど同じです。
もともと、データのある場所までしか、データは存在していないし、データのない場所は、単に見かけのスペースだけですから。

ただし、メモリの使用率からすると、数式は文字列ですから、文字列を略したほうがよいです。
例
上記のサイトの関数は、再計算関数とか揮発性関数と呼びますが、完全になくすことはできませんから、その場合に、数式の数を増やさないとか参照範囲を広げないとかします。

= Offset(A1,0,1)
　　　↓
= Offset(A1,,1)

実務上では、実際に、配列数式を使わなくてもできるものがほとんどです。配列数式でないとできない種類のものは少ないです。専門のMODEとかFREQUENCYとかは、マクロで代用できますので、広範囲に使うなら、切り替えが必要です。

ブックを分ける方法もあります。片方をデータだけにして、別のブックから、ODBC や古典的方法でデータを取り出す方法でもよいのです。それが、ブックでなくても、mdb ファイルでも良いわけです。

それに、データベース関数もありますし、フィルタオプションやオートフィルタもよいです。

VBAのマクロというのは、数式と比較したら、100倍ぐらい遅いのですが、保有メモリに邪魔されることがないので、結果的には速くなるわけです。保有メモリに邪魔されると、半端ではなく遅いからです。Excel 2007 になると、特別に改善されているようです。

http://msdn.microsoft.com/ja-jp/library/aa730921.aspx
Excel 2007 におけるパフォーマンスの改善

書式は、スタイルで変更すると、負担が少ないといいます。また、書式は、こまめに設定するのではなく、まとめて設定したほうがよいです。

「ユーザー関数を使えば書式は簡単になりますが、重さの点ではどうなのか、」
たぶん、書式のユーザー定義だと思いますが、数を増やさないことですね。

お礼

ご丁寧な回答、ありがとうございます。
>補助セル・補助列で演算して、それを参照させればよいわけです。
私は補助列等はできるだけ避けて、書き込むセルの数を減らした方が高速化できるように思っていたのですが、ご紹介いただいたサイトを拝見すると逆効果のようですね (--;)
>どちらが速いかは、どちらもデータが、250行目までなら、ほとんど同じです。
もともと、データのある場所までしか、データは存在していないし、データのない場所は、単に見かけのスペースだけですから。
そうでしたか・・・
揮発性関数に関しては特に文字列を削減して
= Offset(A1,0,1)
　　　↓
= Offset(A1,,1)
ですね。これは役立ちそうです。

ありがとうございました。

回答No.1

FEX2053

これは「Excelの設定」になってしまい、「Bookの設定」ではない
ので、動作させるExcel全てに設定が必要（またはvbaでそういう
設定変更を動作させる必要）があるんですが、

「ツール」「オプション」の「計算方法タブ」で「計算方法」を
「手動」にする

という手があります。これをすると、[F9]を押さない限り全ての
再計算をやってくれません（当然計算結果も出てこない）。当然
計算をしない分処理速度は速いですし、入力が終わった時点で
[F9]を押して結果を待つ、というのは余りストレスにならないと
思います。

最後の手段として、覚えておくといいかもです。