CELL関数で悩む

EXCELにはCELL関数というものがあって、そのEXCELファイル自体のファイル名とシート名を取得することができる。

これが便利なのは、ファイル名に日付とかが入っていてその日付をEXCELファイル内で参照するような、手続き上仕方なく書かなくちゃいけないようなファイルに使えることだ。

日付だけじゃなくて例えばシート名に入っているキーワードでほぼ内容が決まってしまうようなシートが多数ある場合、このシート名からvlookupでテーブル参照するとすごく便利に使える。

ちなみにこの関数を使って、直接ファイル名やシート名を取得することもできる。以下の行を直接EXCELに貼り付けてみてほしい。

ファイル名：
=mid(cell("filename"),find("[",cell("filename"))+1,find("]",cell("filename"))-find("[",cell("filename"))-1)

シート名；
=right(cell("filename"),len(cell("filename"))-find("]",cell("filename")))

しかし、このCELL関数には大きな落とし穴があるのだ。

トロいのである。別のシートやブックで判定したファイル名やシート名を、切り替わったことに気づかずにそのまま返してくる。例を二つ挙げる。

まず、シートが切り替わったのに気づかずにいる例。

もうひとつはBOOKが変わったのに気づかない例。

バカでしょ？　この例はEXCEL2003だが、2010でも同じことが起こることを確認している。これにはとりあえずの回避策があって、それは[F9]キーを押下して再計算させることだ。これをやると「ああ、そうだったね」という感じで正しい値を表示する。

しかし、他人に渡すファイルの場合は怖いのでこの機能を使えない。いったんはこの機能でファイル名とシート名を取得したあとで、同じセルへ「値をコピー」して定数化している。面倒くさい。


これはバグだと言っていいと思う。これを放置しているのはMicrosoftの怠慢だ。

バグだと言い切るのには根拠がある。この現象はひとつのEXCELインスタンス、つまりひとつのEXE内でだけ起こるのだ。言い換えればひとつのWindowの中だけで起こる。ひとつのEXEの中でCELL関数が返す値をひとつしか持てないからこういうことが起こる。

だから、別のEXCEL.EXEを立ち上げて同様のことを試すと、それぞれのWindowで独立した値が管理される。


これ、中身の構造がわかっている人ならすぐに直せるんじゃないかと思うんだけどなぁ。

翌日追記：

CELL関数には2つ目のパラメータがあって、そこに例えば「$A$1」とでも入れておけばここに書いたような不具合は起こらないらしい。しかしそれはおかしいだろう。そんなパラメータ無しでもちゃんと動くようにしろよ。

$A$1を追加した例を置いておこう。

ファイル名：

=mid(cell("filename",$A$1),find("[",cell("filename",$A$1))+1,find("]",cell("filename",$A$1))-find("[",cell("filename",$A$1))-1)

シート名；

=right(cell("filename",$A$1),len(cell("filename",$A$1))-find("]",cell("filename",$A$1)))