Excelで再計算の発生頻度を少なくするためにに参考になりそうなこと。
VBAで細かく再計算を制御したい場合は、次の記事を参考にしてください。
重い場合は再計算を手動にする
入力が終わってから計算してくれればいいのに、再計算が重くてセルに入力する度に待たされてしまうということがあります。
その場合は、リボンの「計算」タブにある、「計算方法の設定」を「手動」にしてください。セルを書き換えたり、数式をコピーしても再計算されなくなります。これを「自動」に戻すか、となりにある「再計算実行(F9)」か「シート再計算(Shift+F9)」を実行すると数式が計算されます。
ただ、毎回これをやるのは面倒ですし、その都度結果を知りたいこともあります。次の項目からは再計算を自動にしたままで、無駄な再計算を発生させない方法を見ていきます。
再計算の発生条件
まず、再計算が発生する条件を確認してみます。
- セルの値を変更する
- Enterなどで確定した場合
- 入力前後で値が変わっていなくても対象になる
- Escでキャンセルすれば発生しない
- 再計算に関する設定など
- 数式タブの「再計算実行(F9)」「シート再計算(Shift+F9)」を実行する
- 再計算を「手動」モードから「自動」モードに切り替える
- ファイルを保存する(オプションで「ブックの保存前に再計算を行う」にチェックをしてあるとき)
- 行・列に関する操作
- 行または列を削除・挿入する
- 列幅・行間の自動調整を行う
- 行を非表示・再表示する(列では再計算は起こらない)
- ワークシートに関する操作
- ワークシートの名前を変更する
- 別のワークシートの順番を変更する
- オートフィルタでフィルタ操作を行う
- 定義された名前を追加、編集、または削除する
Excelは依存したセルのみ再計算をする
左記の条件に当てはまっても、すべての関数が再計算されるわけではありません。Excelは基本的に自分自身か、数式の中で指定されたセルに変化があった場合のみ再計算を行います。
実際に試してみたい人は、再計算に時間がかかる様にVLOOKUP(SUMIFやCOUNTIFでも構いません)を大量に並べたブックを作ってみてください。新しいシートを作ってそこに書き込んでも何も起きませんが、VLOOKUPで指定した座標を触った途端、再計算が始まります。
再計算の発生例
VLOOKUP関数とINDEX関数+MATCH関数を使って、どこで再計算が発生して、どこは再計算が起きないか見ていきます。
対象データ
| 列 | 内容 |
|---|---|
| A列 | 商品コード(JAN/ISBNなど) |
| B列 | 商品名 |
| C列 | 在庫数 |
関数
| 使用関数 | 指定範囲 | |
|---|---|---|
| 式A1 | VLOOKUP | A:B |
| 式A2 | VLOOKUP | A:C |
| 式B1 | MATCH | A:A |
| 式B2 | INDEX | 式B1 / B:B |
| 式B3 | INDEX | 式B1 / C:C |
商品コード(A列)を変更した場合
すべての数式が再計算されます。
VLOOKUPとMATCHは商品コードを直接参照しているため再計算が発生します。INDEXもMATCHを参照しているため連鎖的に再計算が必要になります。
商品名(B列)を変更した場合
VLOOKUPと式B2のINDEXに再計算が発生します。式B3のINDEXは再計算が不要です。
式A2のVLOOKUPは在庫数(C列)を取得したいので商品名(B列)は関係ないのですが、どうしても範囲内に商品名が入るので、再計算は避けられません。
在庫数(C列)を変更した場合
式A2のVLOOKUPと、式B3のINDEXにのみ再計算が発生します。
ここでもし式A1のVLOOKUPにA:C列を指定していると、こちらにも再計算が必要になります。計算速度が問題になる場合は、必要以上に広い範囲を指定しないようにしましょう。
揮発性関数
原則として、参照しているセルが変化しなければ再計算は発生しないのですが、「揮発性関数」という例外があります。
変更がある度に再計算が発生する
揮発性関数は先に説明した再計算の条件を満たす度に再計算されます。
一番分かりやすいのがRAND関数です。この関数は再計算の度に値が変わる関数です。他のセルを参照しない(というか引数がない)ので、普通の関数であれば自分自身が再計算の条件を満たさない限り再計算はされません。しかし、他のセルを編集したり、シートの順番を入れ替えたりしてみると、必ず値が変わっているのを確認できると思います。
揮発性関数の一覧
組み込みの揮発性関数は下に書いたものですべてです。
- 日付・時刻
- NOW
- TODAY
- 座標
- INDIRECT
- OFFSET
- 乱数
- RAND
- RANDBETWEEN
- 情報取得
- CELL(引数によって異なる)
- INFO(引数によって異なる)
揮発性関数と同じか参照しているセルにも連鎖する
揮発性関数単独ではそれほど計算に時間はかからないのですが、揮発性関数が含まれていたり、揮発性関数を参照しているセルも再計算の対象になるので注意が必要です。
VLOOKUPやSUMIFなどの重い関数に連鎖してしまうと、1セル入力すると再計算、オートフィルタを使うと再計算という様な、かなり鬱陶しい状態になってしまいます。なるべくセルを分けたり、非揮発性関数を使って重い処理に連鎖するのは避けるようにしましょう。
OFFSET関数を置き換える
OFFSET関数は揮発性関数なので、できれば座標を直接指定するか、INDEX関数(非揮発性関数)に置き換えた方が再計算は少なくて済みます。
OFFSET関数を使った動的範囲指定は本当に必要？
範囲の拡大縮小を組み込み関数で置き換えるのは難しいです。ただ、本当にOFFSETを使って動的に指定する必要があるのか考える必要はあります。
構造化参照を使う
テーブルを参照する場合は、構造化参照を使えばデータに合わせて自動的に範囲の拡大・縮小ができます。数式も見やすくなるので、使ったことのない人は一度試してみてください。
より広範囲を指定する(列単位指定)
次点で、列単位の座標指定(A:Cなど)ができないか考えてください。多くの場合、列方向に余分なデータは存在しない、あるいは、存在しても計算には影響しないと思います。
また、VLOOKUPの高速化に関するページでも触れましたが、ほとんどの関数は最終行を自動認識します。末尾の未使用セルは計算に使用されないので、列単位で指定しても速度的には変わりありません。むしろ、関数を使って最終行を求めると、かえって遅くなる可能性があります。
INDEX関数でリストから値を取り出す
リストから取り出すのにOFFSET関数を使っている例を見かけますが、完全にINDEXで置き換え可能です。
# A1:A10の中の3番目の要素を取り出す =INDEX(A1:A10,3) # A1:C10の5行目3列目の値を取り出す =INDEX(A1:C10,5,3)
MATCH関数などと組み合わせることもできます。
# A1:D10から行見出しが"商品5"、列見出しが"在庫数"の値を取り出す
=INDEX(A1:D10,MATCH("商品5",A1:A10,0),MATCH("在庫数",A1:D1,0))
INDEX関数で相対位置を指定する
INDEX関数で現在座標からの相対位置を指定する事もできます。
相対位置を指定する場合には、ROW関数やCOLUMN関数をしようします。セルが何行(列)目か調べる関数で、引数があればそのセルの座標、引数なしなら関数が入力されているセルの座標を元に結果を返します。
# 指令列で現在座標より任意の数上下に移動させる(上に1) # 数式と同じ列の指定も可能 =INDEX(A:A,ROW()-1) # 指令行で現在座標より任意の数左右に移動させる(左に1) # 横になっただけで上下の場合と同じ =INDEX(A:A,COLUMN()-2) # 上下左右に任意の数ずらす(上に1/左に2) # ずらした後の座標がINDEXへ渡した座標の範囲内に収まるようにしてください # 範囲外に出るとREFエラーになります =INDEX(A:F,ROW()-1, COLUMN()-2) # 全範囲を指定した場合 # 列名が具体的な座標を指定しているので、将来のバージョンで列数が拡大されると漏れが出ます # Excel 2003形式だと最大列数が少ないのでA:IVにしてください =INDEX(A:XFD,ROW()-1, COLUMN()-2)
数式が一気に複雑になりますが、特定範囲内(この例ではC2:E6)で相対座標を指定する方法です。
# 上に1/左に1ずらす =INDEX($C$2:$E$6,(ROW()-ROW($C$2)+1)-1,(COLUMN()-COLUMN($C$2)+1)-1)
コメント