揮発性関数という言葉、知っていますか？
動作の重いエクセルファイルも揮発性関数を正しく理解することでもっと軽快にできるかもしれません。
揮発性関数とはなにか
結論から言うと、
「シートに変更があった際に常に再計算される関数」のことです。
具体的には、
NOW、INDIRECT、TODAY、OFFSET、CELL、INFO、RANDBETWEEN、SUMIFです。
これだけだと、なんのことかわかりませんね。
揮発性関数を理解するには「再計算」を理解する必要があります。
再計算のしくみ
Excelでは計算に使用するセル(関数の参照先のセル)の値が変更されると、自動で再計算が行われます。
再計算は設定で手動にしない限りは自動で起動され、シートには常に最新の計算結果が表示されるというカラクリです。
下の画像で説明しましょう。
いま、D2のセルには「=B2+B3」という式が入っています。
5+1なのでD2の計算結果は「6」です。
ここで、もしB2のセルを4に書き換えたとしたら、D2の値は6から5に瞬時に変化します。きわめて当たり前に目にする風景ですね。
では、B4のセルを書き換えたらどうなるでしょうか？
これはD2のセルで参照していない値のため、表示が変わることはありません。
もっと言うと、Excelは再計算を行う必要がありません。
再計算を行う必要がない場合は、当然ながら再計算は行われません。
他のセルから参照先となっているセルが書き換わったかどうかのみが監視されているということです。
動作が重い原因
Excelの動作が重いのは、どうしてでしょうか。
これは大きく分けて2つあるでしょう。
- Excelシートに含まれるデータ量が極めて多くメモリを圧迫している
- 計算式が大量に再計算され、CPUの処理が追い付いていない
データの絶対量が多いことに対しては、データを分割したり、取得するデータが本当に必要なのか見直すなど抜本的な対策が必要になります。
一方で、今回の揮発性関数と関係が深いのは2の再計算です。
先ほど挙げた例では、計算式は1つで終わっていましたが、実際には2段階、3段階の再計算ということがあり得ます。
上の画像は、先ほどのD2セルを参照先にしたF2セルがある場合です。
もし、B2セルの値を書き換えると、
D2の再計算→F2の再計算
のように2段階の計算が必要になることが分かります。
しかし、この場合F2セルは「=B3+B4」と書いても結果は同じです。
そのように書けばB2セルを書き換えてもF2セルの再計算はしなくて済むため、再計算の量を減らすことができます。
無駄な再計算を増やさないことが重要
無駄に入り組んだり重複した計算式を増やすとExcelが演算する回数が増えてしまうので注意が必要です。
先ほどの例のように、単純な足し算などでは人間が認知できないほどの高速で終わるので気にしなくてOKですが、VLOOKUPなどの処理に時間がかかる関数を参照先として大量に持っていると再計算にはがぜん時間がかかります。
再計算が少なくなるように参照先を意識してファイルを作り上げることが肝心です
ですが、参照先を意識しても防げない再計算というのがあります。
もっといえば参照先がなくても常に再計算がかかってしまう関数があるのです。
お察しの通り、それが揮発性関数です。
揮発性関数は常に再計算されてしまう
揮発性関数は、参照先にかかわらず、シート上のどこかのセルが再計算されたり、ブックが開かれるたびに、常に再計算を行う関数です。
そのため、揮発性関数が多いシートを作成すると、操作のたびにExcelは再計算処理をはさむため、動作が重くなります。
軽快な動作をさせるためには、参照先を少なくするだけでなく、揮発性関数の使用を抑えることが不可欠です。
揮発性関数の種類
揮発性関数は、次の8つです。
- NOW
- INDIRECT
- TODAY
- OFFSET
- CELL
- INFO
- RANDBETWEEN
- SUMIF
しかし、なぜ揮発性関数というものがあるのでしょうか？
もっといえば、揮発性関数で常に再計算がされるのはなぜなのでしょうか？
揮発性関数が常に再計算される理由
揮発性関数が常に再計算される理由を考えてみましょう。
たとえば、TODAY関数はその日の日付をシリアルで返す関数です。
つまり、どこかのセルに連動してではなく、シートが変更されるたびに最新の状態にする必要があります。
こう言われると揮発性でなくてはならない必然性が理解できるのではないでしょうか。
TODAYは理解できた。
でも、よく使われるOFFSETが揮発性関数なのは直感的に理解しにくいな。
OFFSETが揮発性関数である理由
OFFSETが揮発性関数というのは、直感的には少し理解しづらいかもしれません。
まず、前提としてOFFSET関数とは以下のような関数です。
基準セルに対して指定した行数・列数だけずらしたセルの参照を返す
=OFFSET(基準, 行数, 列数, [高さ], [幅])
上の画像では、C2セルのOFFSET関数は、B2を基準として1行下のB3セルを参照しています。そのため、表示としてはB3と同じ「5」になっています。
ここで、OFFSETの参照先であるB2セルが書き換えられたらどうなるでしょうか？
もう、お気づきでしょうか。
そう、OFFSET関数自体の参照先はB2ですが、B2自体が書き換えられても計算結果には関係ないのです。
もっと重要な点があります。
逆に言えば、基準セルであるB2が更新されていなくても、B3が更新されたならOFFSET関数の返り値は変動しなくてはなりません。
まとめるとこうなります。
OFFEST関数の計算結果は、数式上の参照先でないセルの更新に依存しているため、揮発性で実装されている。
それでもOFFSETが揮発性だと納得できない？
ここまで説明すると、賢いあなたはこう思うかもしれません。
なぜMicrosoftはOFFSET関数において、
数式上の参照先でなく、実際の参照先を別に保持しないのか？
さすれば揮発性関数にせずとも済む話ではないか！
私も最初にOFFSETが揮発性と知ったときはそう思いました。
しかし、見落としていた点があります。
OFFSET関数のパラメータであるオフセット量は、かならずしも絶対値で指定されるわけではない
そうなんです。OFFSETの移動量そのものが別のセルの値によって決まるパターンが当然あり得ます。
こうなると、OFFSETの場合に実際の参照先を別に保持させるのがあまり現実的ではないということがわかってきます。
オフセット値の変動までを常に監視して不要な再計算を削減するよりも、都度再計算させたほうが、計算処理コスト的に得なのか
あるいは実装上の煩雑さを避けるためにそうしているのか（ちょっと考えただけでも場合分けが面倒そう）
Microsoftのみぞ知ることですが、そうした事情があることは想像に難くありません。
OFFSETとVLOOKUPの組み合わせは今すぐやめましょう
今回の記事のまとめです。
副題の通りですが、いくら便利なOFFSETだとしても動作の重いVLOOKUPなどと組み合わせるのは今すぐやめましょう。
重い関数に揮発性関数を絡めることで再計算を発生させまくる悪魔のようなExcelシートが生まれます。
これが数百行程度のデータであればマシンスペックが不足することもそうそうないのですが、こと製造業などでは操業データが何万行と含まれたExcelファイルを使うことも多々あります。
中途半端に詳しいレベルだと無駄にOFFSETとか使いたくなっちゃって、こういう激重Excelを発生させがちです
今回説明した揮発性関数の動作原理を知らずに、「OFFSETが便利！」ということだけを鵜呑みにしてしまうと逆に業務時間をひっ迫することになりかねません。
たとえば、Excel効率化の書籍というのは多くありますが、結構売れてそうな本でもOFFSETを推奨している例は多分にあるようです。
これに関してOFFSETの推奨が悪いことだと批判するつもりは一切ありません。
そもそもExcelで何万行も扱うこと自体が非効率と言われてしまえばその通りです（何万行もあるようなケースならそもそもAccessを使えという話になります）。
そうは言っても、会社でAccessが導入されてないケースは多いと思われます。
ここで申し上げたいのは、OFFSETが常に最適解ではないことを留意するのが最も重要だということです。
これに関連して、VLOOKUPを使うのをやめましょうって話もあるんですが、これについてはかなり長くなるので別の記事にしたためます。
揮発性関数はマイナーすぎる知識かもしれませんが、PCに無駄な処理をさせないという考え方は大事なポイントです。
こうした本質を抑えて、みなさんが少しでも快適にExcelを使えるようになることを願います。
コメント