関数が“揮発性”か“不揮発性”かを知る方法は？

質問

http://support.microsoft.com/default.aspx?scid=kb;JA;274500&Product=xlw2kINT
は、CELL、INDIRECT、INFO、NOW、OFFSET、RAND、TODAY関数が“揮発性”と言っています。

NOW、RAND、TODAY関数は、[再計算]する（TODAY関数の場合は日付が変わる）度にその返り値が変わるので“揮発性”であろうことは理解できます。

ところで、OFFSET関数の場合は[再計算]ではその返り値は変わらないけど、どのようにして当該関数が“揮発性”であることを納得できますか？
マクロは使わないことが前提として、ご回答ください。

ちなみに、
http://www.civil-design.net/free/data/excel/function/13.html
は、AREAS、CELL、COLUMNS、INDEX、INDIRECT、NOW、OFFSET、RAND、ROWS、TODAY関数が“揮発性”と言っています。OFFSETに似た INDEX関数も！（コレ、正しいですか？）

回答No.3

keithin · ベストアンサー

んーーー
もしかするとこういう言い方も出来ると思いますが，誤解から来た「納得」を納得して貰う方法は提示できません。

＞関数の返り値が前と異なっているので、「なるほど揮発性だわい」と得心が行きます。

あなたが着目した，関数の「計算結果が変わる」のは，単に計算の結果に過ぎません。
たとえば「外部ブックを参照している数式」は，外部ブックの参照元データに応じて「ブックを開く都度，計算結果が変わります」が，別にだからといってこれが「揮発性だ」という事にはなりません。

「揮発関数か否か」は，当該の関数が「再計算の必要があってもなくてもいつも再計算される」か，「必要なときだけ再計算され，必要ないときは黙って以前の計算結果を保持している」かという，エクセルの内部事情（設計）の問題です。
ある関数がいま再計算されたのかされなかったかは，ワークシートを眺めているだけでは確認する手段はありません。マクロを使えばある程度可能ですが。

ただし，機能を考えて見ればINDIRECTやOFFSET関数が「揮発性である必然性」は，ある程度理解することはできます。

たとえば，不揮発性関数の例として
=INDEX(B:B,3)
について見てみると，この関数は関数が引数として受け取っている「B:B」のセル範囲に「変化が起きた」（B列のセルに記入・削除された，B列のセルにある数式に再計算が走った）ときに，それらをトリガーとして再計算されます。
B列に変化が無く，代わりに隣のC列で何かしても，この関数は再計算されません。自分の再計算チェーン（参照元）に連ならないセルが変化しても，何もする必要が無いので，何もしないということです。

一方揮発性関数である
A1セルにC2と記入しておいて　=INDIRECT(A1)
とか
=OFFSET(A1,2,2)
は機能として，この式ではC2セルやC3セルの値を（再計算して）返す事が求められています。

しかし，これらの関数が調べて値を返すべきセルC2やC3は，引数として受け取っているセルとは全く無関係なセルです。（あるいはINDIRECT("C4")のように書かれたら，そもそもセルを引数で受け取っていません）
従ってこれらの関数は，通常の再計算チェーンによる再計算ルール（不揮発関数の再計算のシクミ）から離れ，とにかくブックの中で「再計算すべきトリガ」が発生したら念のため必ず再計算され，いつでも最新のC2やC3の値を表示します。これが「揮発性である」ということです。

お礼

懇切丁寧な解説、誠にありがとうございました。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

新規ブックのシート見出しをクリックして コードの表示
Private Sub Worksheet_Calculate()
　 　Cells(1, 10) = Cells(1, 10) + 1
End Sub
で 通常の計算が実行されると J1セルが+1される
揮発性は 一気に増える([F9]で再計算もしてみるとよいでしょう)
上記の場合はJ1セルは計算対象にしないような数式にしてください

VBAにあまり精通していないので、正解かどうかは判断できかねますm(_ _)m

お礼

私、VBAはカラッキシできませんが、ありがとうございました。

回答No.2

imogasi

Ｇｏｏｇｌｅで「エクセル関数　揮発性」で照会。
沢山きじがあって、拾い読みすればおおよそのことは判るだろう。難しいことを聞いている割には質問の前に、ＷＥＢ照会をしたのかな。
http://q.hatena.ne.jp/1137503207
http://www.relief.jp/itnote/archives/001519.php
http://www.civil-design.net/free/data/excel/function/13.html
など。
上記３番目で＞INDEX, AREAS, COLUMNS, ROWS関数について、訂正している。
ー
＃１で回答されているように、テストとして、新しい白紙のブックに、その関数１種けを、１箇所（セル）だけ利用し、名前をつけて保存し、改めてそのブックを開き、何もせず（セル選択さえしてはダメ）、保存すると、「変更しますか」が出るか出ないいかで、確認できるでしょう。
（またＶＢＡでユーザー定義関数など入れていないようにしないと（Ｖｏｌaｔｉｌｅなど入れているとテストにならないおそれあり）
ーー
過去にここの質問に答えているレベルでは、ＴＯＤＡＹ、ＮＯＷ、ＲＡＮＤ（ＢＥＴＷＥＥＮ）ぐらいは、考えておく必要があるが、その他は、自動再計算しても、差し障るような場面は考えにくい（むしろありがたい）。神経質になる必要は無いのではと思う。揮発性の概念は、一般の他言語を含めて関数一般の考察のもので、エクセル関数には、それを当てはめたに過ぎないのではと想像する。
＞変更してないのに保存確認メッセージが表示される」、などの解明・説明に役立つぐらいでは。

回答No.1

keithin

INDEX関数は揮発性関数ではありません。

＞どのようにして当該関数が“揮発性”であることを納得できますか？

簡易な検査方法としては，

実施例：
新しいブックを用意する
A1セルに
=TODAY()
のように揮発性関数(若しくは調べたい関数)を記入する
ブックを保存し，閉じる

改めてブックを開き，何もせずただ閉じる
その際に「保存しますか」を聞いてくれば，それは揮発性関数です。

補足

》 改めてブックを開き，何もせずただ閉じる
》 その際に「保存しますか」を聞いてくれば，それは揮発性関数です。

その方法は承知していました。

=NOW() や =RAND() もそうですが、これらは「改めてブックを開」いたときには当該関数の返り値が前と異なっているので、「なるほど揮発性だわい」と得心が行きます。
しかし、OFFSET関数の場合は、「改めてブックを開」いても変化が見えないので「何処が揮発性なの？」と思うのです。

「ＭＳが(説明や警告メッセージ等で)そう言っているから」でなく、「なるほど揮発性じゃわい」と“実感”できる方法をお尋ねした次第です。