Excelのすぐ再計算しちゃう揮発性関数について

遅まきながらようやく知った「常時再計算する関数（揮発性関数）」についてまとめてみました。

以下、それぞれについてもう少し詳しく書いてみます。

揮発性関数とは

揮発性関数とは、その引数 (関数式に組まれた参照セル) に変更がなくても、値が同じであると仮定できない関数のことです。通常、Excel はデータ処理量を抑えるため、値が入力されると入力されたセルに関係するセルのみ再計算をし、表示を更新しているそうです。この流れに従う通常の関数が非揮発性関数となります。

しかし、たとえば現在時刻を返すNOW関数の結果は時間と共に変化します。その為、Book上のいずれかのセルへの入力があると、NOW関数 の値は最新の現在時刻に変化します。これが揮発性関数です。同様に OFFSET関数 や INDIRECT関数 も、計算してみないと関わるセルが分からないので、値が入力されるたびに再計算を行います。

揮発性関数の注意点

前述の通り、Excel はデータ処理量を抑えるため、入力されたセルに関係するセルのみ再計算をして表示を更新しています。しかし、揮発性関数は入力されたセルの位置に関係なく、再計算を行います。その為、揮発性関数を多く使うと、再計算にかかる時間が長くなる可能性があります。

Microsoft Learn の記述

揮発性関数という言葉ですが、マイクロソフトの公式学習サイト Microsoft Learn の解説でも記載があり、公式に使われている言葉です。

Microsoft Learn へのリンク：揮発性関数と非揮発性関数

動作の遅れを検証

なるほど、INDIRECT関数は多用すると重くなるイメージはありましたが、OFFSET関数もなんですね。でも、再計算時間が長くなる関数の数は何個くらいからなんでしょうか？そこでExcel内のVBAでマクロを組み、検証して見ます。

検証に使ったPCは

2018年ごろに買った少し古いPCなので、スペック的にはやや劣り。

検証はVBAでセル入力100繰り返しを計測

評価はVBAでマクロを組んで行います。F列に別シートのデータを取得する揮発性関数（INDIRECT関数、OFFSET関数）がそれぞれ100個、1,000個、10,000個並んだファイルを作り、その部分には触らずG列に1から100までをVBAのマクロで自動入力させ、処理時間も計測させます。

比較には、F列を非揮発性関数のINDEX関数に置き換えたファイルも作り、こちらもVBAのマクロで時間を計測します。

VBAのコードはこちら・・・

' セル入力100回実行と計測のマクロ
Sub Function_MeasureInputTime()

    '使用する変数を定義
    Dim startTime As Double
    Dim i As Long
    Dim tmp As Variant
    
    'セル入力100回実行の処理コード
    startTime = Timer               'タイマー関数で開始時間を取得
    tmp = 0                         'セル入力値を0へ初期化

    For i = 1 To 100                '繰り返し処理を100回実施
        tmp = tmp + 1               '現在の値へ1を足す
        Cells(1 + i, 7).Value = tmp '指定セルへ現在の値を入力
    Next                            'Forへ戻る
    
    '終了時刻から開始時刻を引いた処理時間を動作確認ウィンドウへ表示
    Debug.Print "InputTime : " & Timer - startTime

End Sub

時間計測結果

◆VBAでセル入力を100回繰り返したときの処理時間

非揮発性関数であるINDEX関数であればほぼ一瞬で終わるのに対し、２つの揮発性関数は関数の数に応じて処理時間が増えるのが分かります。VBAを組んで処理を繰り返す場合は、出来るだけ揮発性関数を避けた方が良いですね。特にINDIRECT関数は重たい！

この表を100で割ってやれば、通常のセル入力１回の処理時間が分かります。

◆セル数値入力１回当たりの処理時間

手入力で使う分には、少し古いPCでも、100～1000個くらいは0.1秒程度なので大丈夫そうです。INDEX関数と違ってINDIRECT関数は後から参照先が広がっても対応でき便利なので、データまとめシートでは使いたいです。ただ、手入力のファイルでもINDIRECT関数を万単位で使うのは避けた方が良いですね。

そういえば

あ、ということは、Excel チュートリアル１で作った INDIRECT関数とADDRESS関数を組み合わせデータを並び替え は、INDEX関数版に置き換えが必要ですね。

これはいづれ直さねば・・・、今日はここまで。